[Mysql] 1.MySQL查询练习

2019.11.21更新：重新看了下之前的东西，发现几个错误，做一个更正

---

最近在初学MySQL,在网上找相关查询语句练习的时候发现一些答案有一些问题，这边做出了更正，当然也不能保证完全正确，有更好的写法也欢迎交流。:-)

前提：

设有一数据库包括：学生表（Student）、课程表（Course）、成绩表（Score）、教师信息表（Teacher）

#以下的语句可以直接复制使用

#学生表（Student）

CREATE TABLE student
(
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5)
);

#课程表（Course）

CREATE TABLE course
(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL
);

#成绩表（Score）

CREATE TABLE score
(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL
);

#教师信息表（Teacher）

CREATE TABLE teacher
(
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL,
tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL,
prof VARCHAR(6),
depart VARCHAR(10) NOT NULL
);

#插入学生名单

INSERT INTO student VALUES
('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');

#插入课程名单

INSERT INTO course VALUES
('3-105', '计算机导论','825'),
('3-245', '操作系统','804'),
('6-166', '数字电路','856'),
('9-888', '高等数学','831');

#插入成绩表

INSERT INTO score (sno,cno,degree) VALUES
('103', '3-245','86'),
('105', '3-245','75'),
('109', '3-245','68'),
('103', '3-105','92'),
('105', '3-105','88'),
('109', '3-105','76'),
('101', '3-105','64'),
('107', '3-105','91'),
('108', '3-105','78'),
('101', '6-166','85'),
('107', '6-166','79'),
('108', '6-166','81');

#插入教师表

INSERT INTO teacher VALUES
('804','李诚','男','1958-12-02','副教授','计算机系'),
('856','张旭','男','1969-03-12','讲师','电子工程系'),
('825','王萍','女','1972-05-05','助教','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系');

1.查询student表中的所有记录的Sname、Ssex和Class列。
SELECT sname, ssex, class FROM student;

2.查询教师所有的单位即不重复的depart列。
# DISTINCT的使用
SELECT DISTINCT depart FROM teacher;

3.查询student表的所有记录。
SELECT * FROM student;

4.查询score表中成绩在60到80之间的所有记录。
# BETWEEN A AND B的使用,包含A B两个端点值
SELECT * FROM score
WHERE degree BETWEEN 60 AND 80;

5.查询score表中成绩为85，86或88的记录。
# IN的使用
SELECT * FROM score
WHERE degree IN ('85','86','88');

6.查询student表中“95031”班或性别为“女”的同学记录。 
# OR的使用
# 2019.11.21更新：这次在做的时候写了个union，属实憨批，运行时间翻了三倍，不要用union
SELECT * FROM student
WHERE class = '95301' OR ssex = '女';

7.以class降序查询student表的所有记录。
# ORDER BY及ASC DESC的使用，且ORDER BY 要放在查询的最后
SELECT * FROM student
ORDER BY class desc;

8.以cno升序、degree降序查询score表的所有记录。
SELECT * FROM score
ORDER BY cno asc, degree desc;

9.查询“95031”班的学生人数。
# 2019.11.21更新：COUNT的使用
# 有主键多列：count(主键列名) > count(1) > count(*)
# 无主键多列：count(1) > count(一般列名) > count(*)
# 原答案:
SELECT COUNT(*) FROM student
WHERE class = '95031';
# 新答案：
SELECT COUNT(1) FROM student
WHERE class = '95031';

10.查询score表中的最高分的学生学号和课程号。
# MAX函数及子查询的使用
SELECT sno, cno FROM score a
WHERE degree = (select MAX(degree) FROM score);

11.查询‘3-105’号课程的平均分。
SELECT CNO, AVG(degree) FROM score
WHERE cno = '3-105'

12.查询score表中至少有5名学生选修的，并以3开头的课程的平均分数。
# AVG,MIN,MAX等聚合函数要与GROUP BY…HAVING 的使用。
SELECT cno, AVG(degree) FROM score
GROUP BY cno
HAVING count(*) >= 5 AND cno like '3%';
# 或者这样也可以，这个更快
SELECT cno, AVG(degree) FROM score
WHERE cno like '3%'
GROUP BY cno
HAVING count(*) >= 5;
# 这样不行，WHERE后的条件不能用聚合函数，这点非常重要！
SELECT cno, AVG(degree) FROM score
WHERE count(*) >= 5
GROUP BY cno
HAVING cno like '3%';
# 2019.11.21更新：HAVING和WHERE两者很接近，之前有段时间陷入了只要HAVING的东西就一定要SELECT出来，后来突然发现并没有必要，当一个特殊的WHERE就好

13.查询最低分大于70，最高分小于90的sno列。
# AVG,MIN,MAX等聚合函数要与GROUP BY…HAVING 的使用。
SELECT sno, degree FROM score
GROUP BY sno
HAVING MAX(degree) < 90 AND MIN(degree) > 70;

14.查询所有学生的sname、cno和degree列。
# 两表之间有共同元素时采用JOIN连接两表
SELECT a.sname, b.cno, b.degree FROM student a
JOIN score b ON a.sno = b.sno; 

15.查询所有学生的sname、cname和degree列。
# 此处SELECT FORM score, 因为其与另两表均有共同元素
SELECT b.sname, c.cname, a.degree FROM score a 
JOIN student b ON a.sno = b.sno 
JOIN course c ON a.cno = c.cno

16.查询“95033”班学生的平均分。
SELECT cno, AVG(degree) FROM score 
WHERE sno IN (SELECT sno FROM student WHERE class = '95033')
GROUP BY cno;

17.假设使用如下命令建立了一个grade表：
# 有一些字段为Mysql的保留字段 如RANK 要加上``引用起来才可以，不然会报错，使用时正常使用即可。
CREATE TABLE grade(
low INT(3),
upp INT(3), 
`Rank` CHAR(1)
);
INSERT INTO grade VALUES
(90,100,'A'),
(80,89,'B'),
(70,79,'C'),
(60,69,'D'),
(0,59,'E');

查询所有同学的Sno、Cno和rank列。
# JOIN..ON不仅可以可以用作A.a = B.b 同样也可以这样使用
SELECT a.sno, a.cno, a.degree, b.rank FROM score a
JOIN grade b ON
a.degree BETWEEN b.low AND b.upp

18.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM score
WHERE cno = '3-105'
AND degree > (SELECT degree FROM score WHERE cno = '3-105' AND sno = 109)

19.查询score中选学多门课程的同学中分数为非最高分成绩的记录。
# 要与最高分比较可以用所要的JOIN..ON最高分生成的表，并判断两个分数的关系
SELECT a.sno, a.cno, a.degree FROM score a
JOIN (SELECT cno, MAX(degree) standard FROM score GROUP BY cno) b
ON a.cno = b.cno
WHERE a.degree < b.standard 

20.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105')

21.查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列。
SELECT a.sno, a.sname, a.sbirthday FROM student a
WHERE YEAR(a.sbirthday) = (SELECT YEAR(b.sbirthday) FROM student b WHERE b.sno = '108' )

22.查询“张旭“教师任的学生成绩。
SELECT b.sno, a.cno, b.degree, c.tname FROM course a
JOIN score b ON a.cno = b.cno
JOIN teacher c ON a.tno = c.tno
WHERE c.tno = (SELECT tno FROM teacher WHERE tname = '张旭')
2019.11.21更新：一个更快的答案
SELECT sc.*, t1.cname FROM score sc 
RIGHT JOIN 
( SELECT co.*, te.tname FROM course co LEFT JOIN teacher te ON co.tno = te.tno WHERE te.tname = '张旭' ) t1 
ON sc.cno = t1.cno

23.查询选修某课程的同学人数多于5人的教师姓名。
SELECT a.tno, b.tname, COUNT(c.cno) number FROM course a
JOIN teacher b ON a.tno = b.tno
JOIN score c ON a.cno = c.cno
GROUP BY a.tno
HAVING COUNT(c.cno) > 5

24.查询95033班和95031班全体学生的记录。
SELECT * FROM student
WHERE class IN ('95033', '95031')

25.查询存在有85分以上成绩的课程cno。
SELECT b.cno, b.cname, a.degree FROM score a
JOIN course b ON a.cno = b.cno
GROUP BY b.cno
HAVING MAX(a.degree) > 85

26.查询出“计算机系“教师所教课程的成绩表及对应教师信息。
SELECT b.depart, b.tname, a.cno, a.cname, c.sno, c.degree FROM course a
JOIN teacher b ON a.tno = b.tno
JOIN score c ON a.cno = c.cno
WHERE b.depart = '计算机系'

27.查询属于“计算机系”与“电子工程系“的不同职称的教师的tname和prof。
SELECT tname, prof, depart FROM teacher
WHERE depart = '计算机系' OR depart = '电子工程系'
GROUP BY prof
HAVING COUNT(prof) = 1

28.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree。
SELECT * FROM score
WHERE cno = '3-105'
AND degree > (SELECT MIN(degree) FROM score WHERE cno = '3-245')

29.查询所有教师和同学的name、sex和birthday。
SELECT tname name, tsex sex, tbirthday birthday FROM teacher
UNION
SELECT sname name, ssex sex, sbirthday birthday FROM student

30.查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM score a
JOIN (SELECT cno, AVG(degree) standard FROM score GROUP BY cno) b ON a.cno = b.cno
WHERE a.degree < b.standard

31.查询所有任课教师的tname和depart。
# 2019.11.21更新：之前做错了，更正一下
# 原答案：
SELECT tname, depart FROM teacher
WHERE tno IN (SELECT tno FROM course)
# 新答案：
SELECT tname, depart FROM teacher te 
WHERE tno IN ( SELECT t1.tno FROM ( SELECT sc.cno, co.tno FROM score sc LEFT JOIN course co ON sc.cno = co.cno ) t1 )


32.查询所有未讲课的教师的tname和depart。
# 2019.11.21更新：之前做错了，更正一下
# 原答案：
SELECT tname, depart FROM teacher
WHERE tno NOT IN (SELECT tno FROM course)
# 新答案：
SELECT tname, depart FROM teacher te 
WHERE tno IN ( SELECT t1.tno FROM ( SELECT sc.cno, co.tno FROM score sc LEFT JOIN course co ON sc.cno = co.cno ) t1 )

33.查询至少有2名男生的班号。
# 2019.11.21更新：同理，更正一下
# 原答案：
SELECT class, COUNT(*) FROM student
WHERE ssex = '男'
GROUP BY class
HAVING COUNT(*) >= 2
# 新答案：
SELECT class, COUNT(*) FROM student
WHERE ssex = '男'
GROUP BY class
HAVING COUNT(1) >= 2

34.查询不姓王的同学信息。
SELECT * FROM student
WHERE sname NOT LIKE "王%"

35.查询student表中每个学生的姓名和年龄。
SELECT sname, (YEAR(NOW())-YEAR(sbirthday)) age FROM student

36.查询student表中最大和最小的sbirthday日期值。
SELECT MAX(YEAR(sbirthday)) youngest, MIN(YEAR(sbirthday)) oldest FROM student

37.以班号和年龄从大到小的顺序查询student表中的全部记录。
SELECT * FROM student
ORDER BY class DESC, sbirthday ASC

38.查询“男”教师及其所上的课程。
SELECT a.tno, a.tname, a.tsex, b.cname FROM teacher a
JOIN course b ON a.tno = b.tno
WHERE a.tsex = '男'

39.查询最高分同学的sno、cno和degree列。
SELECT sno, cno, MAX(degree) FROM score
GROUP BY cno

40.查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT a.sno, a.cno, b.cname, a.degree FROM score a
JOIN course b ON a.cno = b.cno
JOIN student c ON a.sno = c.sno
WHERE b.cname = '计算机导论'
AND c.ssex = '男'

41.查询和“李军”同性别的所有同学的sname。
SELECT sname FROM student
WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军')
AND sname != '李军'

42.查询和“李军”同性别并同班的同学sname。
SELECT sname FROM student
WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军')
AND class = (SELECT class FROM student WHERE sname = '李军')
AND sname != '李军'