MySQL经典50题（含很多难题与耗时间）自己写的答案，日期，视图，子查询【诗书画唱】
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex char(2));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score int);
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
--drop table SC
select * from SC
select * from Student
select * from Teacher
2，3，4，6，13，16，20，28，29，30，31，47，49（简单题）
【我做了1-20，28，29，30，31，47-50共28题，以后有空再做别的题】
1.查询“某1”（比如01）课程比“某2”（比如02）课程成绩高的所有学生的学号；
--【因为这里的有些表的内容不完整，所以最好不要用inner。
--同时两表查询查询的列名要明确Student.sid，不然会报错。这里没必要用外键，用也可以。
--下面的语法也是可以用来多表查询。
--对于复杂的SQL的子查询的题目，就是要创建视图】
【下面的语句要一条一条地执行】
--1
create view view1 as (select Student.sid,cid, SC.score from Student left join SC on Student.sid=SC.sid where cid = '01' group by Student.sid )
--2
create view view2 as (select Student.sid,cid, SC.score from Student left join SC on Student.sid=SC.sid where cid = '02' )
--【个人的理解和解析：下面的别名可以用英文，也可用中文，可以在视图查询中直接当列名调用，有时不用加单引号
--创建视图时，取得别名就成了视图的列名，要调用列名。个人的思路：创建两个视图，利用inner只会查出两张表的特性，创建新的我想要的视图】
--3
create view view3 as (select view1.sid as '学生学号' ,view1.sid as '课程编号为01', view1.score as'课程编号为01的成绩',
view2.sid as '课程编号为02', view2.score as'课程编号为02的成绩'
from view1 inner join view2 on view1.sid =view2.sid)
--【下面的别名可以用英文，也可用中文，可以在视图查询中直接当列名调用，有时不用加单引号。select 学生学号 from view3，创建视图时，取得别名就成了视图的列名，要调用列名。】
--4
select 学生学号 ,课程编号为01的成绩,课程编号为02的成绩 from view3 where 课程编号为01的成绩>课程编号为02的成绩
--【或简写成select 学生学号 from view3 where 课程编号为01的成绩>课程编号为02的成绩
或写成select * from view3 where 课程编号为01的成绩>课程编号为02的成绩，更加直观，具体全面，方便检查对错】
2.查询平均成绩大于60分的同学的学号和平均成绩；
select Student.sid as '学号',sname as '姓名' ,avg(score) as '平均成绩' from Student left join SC on
Student.sid=SC.sid group by sname having avg(score)>60
3.查询所有同学的学号、姓名、选课数、总成绩
select Student.sid as ' 学号',sname as '姓名' ,count(cid) as '选课数',sum(score) as '总成绩'
from Student left join SC on Student.sid=SC.sid group by sname
--【因为有一名同学没参加考试所以Student.sid不可以写成SC.sid，不然会查出Null的内容】
--【用上inner和下面的语句的话，只会查出参加考试的。有时要用查询单表的语句来检查对错等。select SC.sid as ' 学号',sname as '姓名' ,count(cid) as '选课数',sum(score) as '总成绩'
--from Student , SC where Student.sid=SC.sid group by sname 】
--select * from Student
4.查询姓“李”的老师的个数；
select count(tname) as '姓“李”的老师的个数'
from Teacher where tname like '李%'
--select * from Teacher
5.查询没学过“张三”老师课的同学的学号、姓名；
--create view viewZ as
【下面是尝试的过程，有些语句有用，有些对，有些错。总之尝试后基本都容易离正确，最优等更进一步，可以获得很多有用的东西和内容。
select distinct Student.sid as ' 学号',sname as '姓名' ,tname
where Student.sid != (
select distinct Student.sid as ' 学号',sname as '姓名' ,tname
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where tname='张三') and select distinct Student.sid as ' 学号',sname as '姓名' ,concat(tname) from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid group by sname】
【个人的总结：要取反的范围查询（即XXX不在YYY的情况），一般都会用 where 列名 not in(SQL多表查询语句等)】
select Student.sid as ' 学号',sname as '姓名' from Student
where sid not in
(
select distinct Student.sid
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where tname='张三'
)
--select viewZ.姓名 , count(viewZ.tname) from viewZ group by 学号
--create view viewZS as
--select distinct Student.sid as ' 学号',sname as '姓名' ,tname
--from Student left join SC on Student.sid=SC.sid left
--join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
--select viewNZS.学号,viewNZS.姓名 ,viewNZS.tname ,viewNZS.tname from viewNZS right join viewZS on viewNZS.学号=viewZS.学号
6.查询学过“语文”并且也学过编号“数学”课程的同学的学号、姓名；
【select * from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
--为四表查询，功能多其常用，更广用，所以创建为视图 view4Table，会少很多的代码，
--但最好重命名列名等，复制列名重复。但一般来说还时建有当前要用的列名的视图
】
--drop view view1;
--drop view view2;
--drop view view3
--【- 下面的语句会报的错：Duplicate column name 'sid',重复列名“sid”】
--create view view4Table as (select distinct * from Student left join SC on Student.sid=SC.sid left
--join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid)
--1
create view viewYuWen as
select Student.sid as ' 学号',sname as '姓名'
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where cname='语文'
--2
create view viewMath as
select Student.sid as ' 学号',sname as '姓名'
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where cname='数学'
--3
select viewMath.学号 as '学号',viewMath.姓名 as'姓名' from viewYuWen inner join viewMath on viewMath.学号=viewYuWen.学号
7.查询学过“张三”老师所教的所有课的同学的学号、姓名；
【个人的总结：SQL语句的查询等最重要的一点等就是分析数据的规律等
，简单规律的数据，有时可以用很简单的语句，但有时最好是些更加通用更多种情况，
考虑情况等可能更全面的语句。这里数据特殊（一门老师教一们）所以只要写
select Student.sid as ' 学号',sname as '姓名'
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where tname='张三'
就可以了】
--1
create view viewTC as
select cname,Teacher.tname as '老师名字'
from Student inner join SC on Student.sid=SC.sid inner
join Course on Course.cid=SC.cid inner join Teacher on Teacher.tid=Course.tid group by cname
--2
【下面重命名的cname（可改为'张三教过的所有课的名字'）为张三教过的所有课的名字】
create view viewTAll as
select cname as cname from viewTC where viewTC.老师名字='张三'
--3
select Student.sid as ' 学号',sname as '姓名'
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid where cname in(select cname from viewTAll)
8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
【下面的视图之前创建了就直接调用视图，没有就创建】
【这里的每个老师只教一们课】
【个人总结：有时多表查询的子查询太复杂时要用视图才有用，不报错和简单。查出为空的可能原因是数据的成绩被之前的
Update语句改成一样了。这里可以删除表，建表，插入语句，来检验一下】
--1
create view view1 as (select Student.sid,cid, SC.score from Student left join SC on Student.sid=SC.sid where cid = '01' group by Student.sid )
--2
create view view2 as (select Student.sid,cid, SC.score from Student left join SC on Student.sid=SC.sid where cid = '02' )
--3
create view view3 as (select view1.sid as '学生学号' ,view1.sid as '课程编号为01', view1.score as'课程编号为01的成绩',
view2.sid as '课程编号为02', view2.score as'课程编号为02的成绩'
from view1 inner join view2 on view1.sid =view2.sid)
--【下面的别名可以用英文，也可用中文，可以在视图查询中直接当列名调用，有时不用加单引号。
--select 学生学号 from view3，创建视图时，取得别名就成了视图的列名，要调用列名。
--】
--4
select 学生学号 ,课程编号为01的成绩,课程编号为02的成绩 from view3 where 课程编号为01的成绩<课程编号为02的成绩
9.查询所有课程成绩小于60分的同学的学号、姓名；
【所有课程成绩小于60分等价于所有课程成绩平均分小于60分,用视图可以少写很多代码，
并且逻辑更明确清晰等】
【create view viewSCavg as
select Student.sid as '学号',sname as '姓名' ,avg(score) as '平均成绩' from Student left join SC on
Student.sid=SC.sid group by sname 】
select Student.sid as ' 学号',sname as '姓名' , avg(score)
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
group by sname
having avg(score) <60
10.查询没有学全所有课的同学的学号、姓名；
【自己的转换的思路：每个同学上过的课程数<所有课的数目】
【很多时候要看成一个整体，更便于更深程度的思考】
--【drop view viewSCOne】
--1
create view viewSCOne as
(select Student.sid as ' 学号',sname as '姓名' , count(cname) as '每个同学上过的课程数' from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
group by sname)
--【drop view viewSCAll
--create view viewSCAll as
--(select count(cname) as '所有课的数目' from Course )】
--2
select 学号,姓名 as '姓名' , 每个同学上过的课程数 from viewSCOne
where 每个同学上过的课程数 <(select count(cname) as '所有课的数目' from Course )
11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
【select Student.sid,cname from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where Student.sid=01】
【个人总结：having后面的列名要在select 后面出现】
——————
select distinct Student.sid as ' 学号',sname as '姓名' ,cname as '其中的相同的一门课' from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid group by Student.sid
having 其中的相同的一门课 in (select cname from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where Student.sid=01)
12.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
【自己的思路：先用group by sid
分出所有的sid，之后用
having count(*)=(select count(*) from SC where sid='01') and sid != '01'
判断出除了sid为01，并且课程数目和sid为01的课程数目一致的sid。
之后用select sid from SC where cid not in (select cid from SC where sid='01')
判断出不包括sid为01的课程cid的id。
之后又用一个select sid from SC where sid not in，双重否定为肯定，
综上select Student.sid as ' 学号',sname as '姓名' from Student where sid in
得到课程数目一致，并且cid有相同的sid，加上数据的特殊性，每个人同一门不能报两次，
最终得到的就是和"01"号的同学学习的课程完全相同的其他同学的学号和姓名。
这其实是一个逻辑题，理解后，找到可以确定范围的限制条件就很容易，可以类比地去做。】
select Student.sid as ' 学号',sname as '姓名' from Student where sid in
(select sid from SC where sid not in
(select sid from SC where cid not in (select cid from SC where sid='01'))
group by sid
having count(*)=(select count(*) from SC where sid='01') and sid != '01');
--select concat(cname) from viewTT group by sname
--select Student.sid as ' 学号',sname as '姓名',
【
下面是我尝试过的目前不可行的方案：
select distinct Student.sid as ' 学号',sname ,cname
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid group by Student.sid 】
【create view viewALLSC as
select cname from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid group by Student.sid
having cname not in (select cname from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
where Student.sid=01)
select distinct Student.sid as ' 学号',sname ,cname
from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid group by Student.sid
having cname not in( select cname from
viewALLSC)】
13.把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
--1
create view viewSC as
select avg(score) as score from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid where tname ='张三'
--2
create view viewT as
select distinct Course.cid as cid from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid where tname ='张三'
--3
update SC set score = (select score from viewSC)
where SC.cid=(select cid from viewT)
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select Student.sid as ' 学号',sname as '姓名' , count(cname) ,tname from Student left join SC on Student.sid=SC.sid left
join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
group by sname having tname!='张三' or count(cname)=0
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select Student.sid, Student.sname, AVG(SC.score) from Student,SC
where
student.sid = SC.sid and SC.score<60
group by SC.sid
having count(*)>=2;
【
我对
having count(*)>=2;的翻译：前面的多表表的查询出的不及格的课程数为2，或大于2】
16、检索"01"课程分数小于60，按分数降序排列的学生信息
select * from Student,SC,Course
where
student.sid = SC.sid and SC.score<60
group by SC.sid
having cid='01' order by score desc ;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及每个学生的所有课程的平均成绩
【个人的总结：having后面跟着的列名等，select的后面等，基本也要有等。
想要把创建的两个视图连起来，最好是两个视图的列名和数量一样。
】
--1
create view avgViewA as
select Student.sid as '学号',sname as '姓名' ,cname,score, avg(score) as 'avg' from Student left join SC on
Student.sid=SC.sid left join Course on Course.cid=SC.cid group by Student.sid
--2
create view avgCA as
select Student.sid as '学号',sname as '姓名' ,cname ,score, avg(score) as 'avg' from Student left join SC on
Student.sid=SC.sid left join Course on Course.cid=SC.cid group by score
--3
select avgCA.学号 as '学号',avgCA.姓名 as '姓名' ,avgCA.cname ,avgCA.score, avgViewA.avg from avgCA left join avgViewA on avgCA.学号=avgViewA.学号
order by avgViewA.avg desc
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率(scor>=60)，
中等率(score>=80)，优良率(score>=90)，优秀率(score>=95)
——————
--及格率(score>=60)部分：
create view viewJG as
select cname,score from Course left join SC on SC.cid=Course.cid where score>=60
--select * from ViewJG
--【select cname, count(cname) ,score from Student left join SC on Student.sid=SC.sid left
--join Course on Course.cid=SC.cid left join Teacher on Teacher.tid=Course.tid
--group by score having score>=60】
--drop view viewYJG
create view viewYJG as
select cname,count(cname) as c from viewJG group by cname
create view viewCJG as
select cname,score ,count(cname) as c from Course left join SC on SC.cid=Course.cid group by cname
--drop view viewJGL
create view viewJGL as
(select viewYJG .cname as cname , viewYJG.c/viewCJG.c as'各科及格率'
from viewYJG left join viewCJG on viewCJG.cname=viewYJG.cname)
——————
--中等率(score>=80)：
create view viewJG2 as
select cname,score from Course left join SC on SC.cid=Course.cid where score>=80
create view viewYJG2 as
select cname,count(cname) as c from viewJG2 group by cname
create view viewCJG2 as
select cname,score ,count(cname) as c from Course left join SC on SC.cid=Course.cid group by cname
create view viewJGL2 as
(select viewYJG2 .cname as cname , viewYJG2.c/viewCJG2.c as'各科中等率'
from viewYJG2 left join viewCJG2 on viewCJG2.cname=viewYJG2.cname)
--优良率(score>=90)：
create view viewJG3 as
select cname,score from Course left join SC on SC.cid=Course.cid where score>=90
--drop view viewYJG
create view viewYJG3 as
select cname,count(cname) as c from viewJG3 group by cname
create view viewCJG3 as
select cname,score ,count(cname) as c from Course left join SC on SC.cid=Course.cid group by cname
--drop view viewJGL3
create view viewJGL3 as
(select viewYJG3 .cname as cname , viewYJG3.c/viewCJG3.c as'各科优良率'
from viewYJG3 left join viewCJG3 on viewCJG3.cname=viewYJG3.cname)
优秀率(score>=95)：
create view viewJG4 as
select cname,score from Course left join SC on SC.cid=Course.cid where score>=95
--drop view viewYJG
create view viewYJG4 as
select cname,count(cname) as c from viewJG4 group by cname
create view viewCJG4 as
select cname,score ,count(cname) as c from Course left join SC on SC.cid=Course.cid group by cname
--drop view viewJGL
create view viewJGL4 as
(select viewYJG4 .cname as cname , viewYJG4.c/viewCJG4.c as'各科优秀率'
from viewYJG4 left join viewCJG4 on viewCJG4.cname=viewYJG4.cname)
--select cname, 各科及格率 from viewJGL
--drop view viewZB
create view viewZB as
select Course.cid,cname,max(score),min(score),avg(score)
from Student inner join SC on Student.sid=SC.sid inner
join Course on Course.cid=SC.cid inner join Teacher on Teacher.tid=Course.tid
group by Course.cid
--【查不出的表示没有达标的科目】
select * from viewZB left join viewJGL on viewJGL.cname=viewZB .cname
left join viewJGL2 on viewJGL2.cname=viewZB .cname
left join viewJGL3 on viewJGL3.cname=viewZB .cname
left join viewJGL4 on viewJGL.cname=viewZB .cname
19.按各科平均成绩从低到高和及格率的百分数从高到低顺序
--1按各科平均成绩从低到高:
select cname,avg(score) from Student inner join SC on SC.sid=Student.sid
inner
join Course on Course.cid=SC.cid group by cname order by avg(score)
--2及格率的百分数从高到低顺序:
create view viewJG7 as
select cname,score from Course left join SC on SC.cid=Course.cid where score>=60
--
--drop view viewYJG
create view viewYJG7 as
select cname,count(cname) as c from viewJG group by cname
--
create view viewCJG7 as
select cname,score ,count(cname) as c from Course left join SC on SC.cid=Course.cid group by cname
--drop view viewJGL
create view viewJGL7 as
(select viewYJG7 .cname as cname , viewYJG7.c/viewCJG7.c as'各科及格率'
from viewYJG7 left join viewCJG7 on viewCJG7.cname=viewYJG7.cname) order by 各科及格率 desc
--
select * from viewJGL7
20、查询学生的总成绩并进行排名
select sname,sum(score) as'总成绩' from Student left join SC on SC.sid=Student.sid group by sname order by sum(score) desc
21、查询不同老师所教不同课程平均分从高到低显示
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
26.查询每门课程被选修的学生数
27.查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
select ssex ,count(sname) from Student group by ssex
29、查询名字中含有"风"字的学生信息
select * from Student where sname like'%风%'
30、查询同名同性学生名单，并统计同名人数
--drop view viewN
--drop view viewV
----
create view viewN as
select sname ,count(sname)-1 as s ,ssex from Student group by sname having ssex='男'
-----
create view viewV as
select sname ,count(sname)-1 as s,ssex from Student group by sname having ssex='女'
----
select viewV.sname as '同名同性学生名单',count( viewV .s ) as '同名人数' from viewN inner join viewV on viewV.sname=viewN.sname
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select sname ,Sage from Student where Sage like'1990%'
32.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
37.查询不及格的课程，并按课程号从大到小排列
38.查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
40.查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
42、查询每门功课成绩最好的前两名
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄
47、查询本周过生日的学生
【YEARWEEK 用法 用于返回该时间是这一年的第几周。
DATE_FORMAT 用法 用于以不同的格式显示日期时间数据】
--select sname ,Sage,now() from Student where Month(Sage)=Month(now()) and Day(Sage)= Day(now())
select
sname
from
Student
WHERE
YEARWEEK( DATE_FORMAT( sage, '%Y %m %d' ) ) = YEARWEEK( NOW( ) )
48、查询下周过生日的学生
select
sname
from
Student
WHERE
YEARWEEK( DATE_FORMAT( sage, '%Y %m %d' ) ) = YEARWEEK( NOW( ) )+1
49、查询本月过生日的学生
【MONTH 函数 用于返回该时间的月份】
select
sname
from
Student
WHERE
MONTH( DATE_FORMAT( sage, '%Y %m %d' ) ) = MONTH( NOW( ) )
50、查询下月过生日的学生
SELECT
sname
FROM
student
WHERE
MONTH( DATE_FORMAT( sage, '%Y %m %d' ) ) = MONTH( NOW( ) )+1
https://www.cnblogs.com/wobu/p/9645203.html
