经典SQL练习题（一）

》关于MySQL的基础语法可参考：ytraister的码云课件、菜鸟教程SQL
》本文练习题来自该文章：经典SQL练习题

做题前先要下载好MySQL，并开启服务

关于如何下载安装参考之前编写的blog：MySQL的windows安装方法

cmd 管理员权限 >net start mysql # 启动mysql服务
>mysql -uroot -p # 运行/登录mysql
>show databases; # 查看所有数据库
>create database 数据库名称 # 创建某个数据库
>use 数据库名称; # 使用某个数据库

本人对练习题中的 sql导入语句 进行整合，自行复制导入库即可，下载链接：蓝奏云

如果遇到语法错误，如：group by语法报错时，如下错误：
MySQL_group by报错 [ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause]
则参考该文修改下配置：
https://www.cnblogs.com/wuaihua/p/12969684.html

》各数据表运行查询结果如下：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| course         |
| score          |
| student        |
| teacher        |
+----------------+

mysql> select * from course;
+-------+------------+--------+
| CNO   | CNAME      	| TNO |
+-------+------------+--------+
| 3-105 | 计算机导论 	| 825 |
| 3-245 | 操作系统  		| 804 |
| 6-166 | 数据电路   	| 856 |
| 9-888 | 高等数学   	| 100 |
+-------+------------+--------+

mysql> select * from score;
+-----+-------+--------+
| SNO | CNO   | DEGREE |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 103 | 3-105 |   92.0 | 
| 105 | 3-105 |   88.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
| 101 | 6-166 |   85.0 |
| 107 | 6-106 |   79.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+

mysql> select * from student;
+-----+-------+------+------------+-------+
| SNO | SNAME | SSEX | SBIRTHDAY  | CLASS |
+-----+-------+------+------------+-------+
| 108 | 曾华  | 男   | 1977-09-01 | 95033 |
| 105 | 匡明  | 男   | 1975-10-02 | 95031 |
| 107 | 王丽  | 女   | 1976-01-23 | 95033 |
| 101 | 李军  | 男   | 1976-02-20 | 95033 |
| 109 | 王芳  | 女   | 1975-02-10 | 95031 |
| 103 | 陆君  | 男   | 1974-06-03 | 95031 |
| 104 | 高园  | 女   | 1977-01-03 | 95031 |
+-----+-------+------+------------+-------+

mysql> select * from teacher;
+-----+-------+------+------------+--------+----------------+
| TNO | TNAME | TSEX | tbirthday  | PROF   	| DEPART     	|
+-----+-------+------+------------+--------+----------------+
| 804 | 李诚  | 男   | 1958-12-02 | 副教授 	| 计算机系   	|
| 856 | 张旭  | 男   | 1969-03-12 | 讲师  	| 电子工程系 	|
| 825 | 王萍  | 女   | 1972-05-05 | 助教   	| 计算机系   	|
| 831 | 刘冰  | 女   | 1977-08-14 | 助教   	| 电子工程系 	|
+-----+-------+------+------------+--------+----------------+

》本文选取其中具有代表性、容易出错，且自己也不会的几道题（附有本人编写的语句和答案）

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数

自编写：
select cno,round(avg(degree),2) avg from score group by cno having cno like '3-%' and count(sno)>=5;
答案：
select avg(degree),cno from score where cno like '3%' group by cno having count(sno)>= 5;

• 运行结果和答案一致，答案更加简便

16、查询所有学生的Sname、Cname和Degree列

自编写：
select stu.sname,c.cname,sc.degree from student stu left join score sc on stu.sno=sc.sno left join course c on sc.cno=c.cno order by c.cname desc, sc.degree desc;
答案：
select a.sname,b.cname,c.degree from student a join (course b,score c) on a.sno=c.sno and b.cno =c.cno;

• 运行结果和答案一致，答案更加简便

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

自编写：
select * from score where degree>(select degree from score where cno='3-105' and sno=109) and cno='3-105' order by degree desc;
答案：
select a.* from score a join score b where a.cno='3-105' and a.degree>b.degree and b.sno='109' and b.cno='3-105';

• 运行结果和答案一致，答案更加简便

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录（不会！！！！！！）

答案：
select * from score a where a.sno in(select sno from score group by sno having count(cno) > 1) and a.degree < (select max(degree) from score b where b.sno = a.sno) order by cno;

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列（不会！！！！！！）

答案：
select s1.sno,s1.sname,s1.sbirthday from student s1 where s1.sno!='108' and year(sbirthday)=( select year(sbirthday) from student s2 where s2.sno='108' );

• 剔除了sno=108的同学（查询不包括他本人）

23、查询“张旭“教师任课的学生成绩

自编写：
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));
答案：
select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where x.tno=y.tno and y.tname='张旭');

24、查询选修某课程的同学人数多于5人的教师姓名

自编写：
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(cno)>5));
答案：
select teacher.tname from teacher,course,score where score.cno = (select score.cno from score group by score.cno having count(*) >= 5) and course.cno = score.cno and course.tno = teacher.tno group by teacher.tname;

• 自编写的结果可行，看看答案写法；而该答案剪切自评论区的，答案和自己写的一样

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序

自编写：
select degree from score where cno='3-105' and degree>(select degree from score where cno='3-245');
# 报错： Subquery returns more than 1 row
# 上网查，该报错表示子查询返回了多行数据，修改后如下：
select degree from score where cno='3-105' and degree>any(select degree from score where cno='3-245');
# 根据题目要求，改为：
select cno,sno,degree from score where cno='3-105' and degree>any(select degree from score where cno='3-245') order by degree desc;
答案：
跟自己的一样`

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree

自编写：
select a.* from (select * from score where cno='3-105') a inner join (select * from score where cno='3-245') b on a.sno=b.sno and a.degree>b.degree;

31、查询所有教师和同学的name、sex和birthday（不会！！！！！！）

答案：
select sname as name, ssex as sex, sbirthday as birthday from student union select tname as name, tsex as sex, tbirthday as birthday from teacher;

• sql中union的用法，参考：https://www.w3school.com.cn/sql/sql_union.asp

32、查询所有“女”教师和“女”同学的name、sex和birthday（不会！！！！！！）

答案：
select s.sname name,s.ssex sex,s.sbirthday birthday from student s where s.ssex='女' union select t.tname name,t.tsex sex,t.tbirthday birthday from teacher t where t.tsex='女';

• sql中union的用法，参考：https://www.w3school.com.cn/sql/sql_union.asp

33、查询成绩比该课程平均成绩低的同学的成绩表

自编写：
select * from score where cno in (select distinct(cno) from score) and degree<(select round(avg(degree),2) avg from score where cno in (select distinct(cno) from score));
# 出现sno=107的同学刚好6-106课程成绩=79.0，且是等于平均分的，也被算进去了...
答案：
写法一：
select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
写法二：
select a.* from score a,(select cno,round(avg(degree),2) avg from score group by cno) b where a.cno=b.cno and a.degree<b.avg;

36、查询至少有2名男生的班号

自编写：
select class from student group by class having count(ssex='男')>=2;
答案：
select class from student a where ssex='男' group by class having count(ssex)>1;

• 我的答案更好点

38、查询Student表中每个学生的姓名和年龄（不会！！！！！！）

答案：
select sname,year(now())-year(sbirthday) as age from student;

• 没想到用year()、now() 之前也很少用过，现在知道了

39、查询Student表中最大和最小的Sbirthday日期值

自编写：
select sbirthday from student where sbirthday = (select max(sbirthday) from student) or sbirthday=(select min(sbirthday) from student);
答案：
select sname,sbirthday as birthday from student where sbirthday=(select max(sbirthday) from student) union select sname,sbirthday as birthday from student where sbirthday=(select min(sbirthday) from student);

• 结果一样，答案的写法更加严谨（用到合并union）

41、查询“男”教师及其所上的课程

自编写：
select t.tno,t.tname,t.tsex,c.cname from teacher t inner join course c where t.tno=c.tno and t.tsex='男';
答案：
select a.tname,b.cname from teacher a join course b using(tno) where a.tsex='男';

结果一样，答案用到两表等值连接的简便写法：using() 类似a.tno=b.tno

• 拓展：
  但如果用select * 来查全部数据时，使用a.tno=b.tno结果会有两个tno；而使用using()的结果不会出现两个tno。具体可以执行以下语句：
  select * from teacher t inner join course c where t.tno=c.tno and t.tsex='男';
select * from teacher a join course b using(tno) where a.tsex='男';

42、查询最高分同学的Sno、Cno和Degree列

自编写：
select cno,max(degree) from score group by cno having max(degree);
# 没筛选出sno...
答案：
# 答案只给出所有成绩中的一个最高分，而不是各科成绩的最高分（题目也有歧义），最后自己整理的结果：
select a.* from score a,(select cno,max(degree) max from score group by cno) b where a.cno=b.cno and a.degree=b.max order by cno;