sql查询练习题

版本

mysql5.7

建表

CREATE TABLE student (
	Sno VARCHAR ( 10 ) PRIMARY KEY NOT NULL UNIQUE,
	sname VARCHAR ( 10 ) NOT NULL,
	sbirth DATETIME NOT NULL,
	ssex VARCHAR ( 10 ) NOT NULL,
	Sdept varchar(10) NOT NULL
);

CREATE TABLE teacher (
	Tno VARCHAR ( 10 ) PRIMARY KEY NOT NULL UNIQUE,
	Tname VARCHAR ( 10 ) NOT NULL UNIQUE
);

CREATE TABLE course (
	Cno VARCHAR ( 10 ) PRIMARY KEY NOT NULL UNIQUE,
	Cname VARCHAR ( 10 ) NOT NULL UNIQUE,
	Tno VARCHAR(10) NOT NULL,
	FOREIGN KEY course(Tno) REFERENCES Teacher(Tno)
);
CREATE TABLE sc (
	Sno VARCHAR ( 10 ) NOT NULL,
	Cno VARCHAR ( 10 ) NOT NULL,
	Score INT,
	PRIMARY KEY(Sno, CNo),
	FOREIGN KEY sc(Sno) REFERENCES student(Sno),
	FOREIGN KEY sc(Cno) REFERENCES course(cno)
);

插入数据

# --插入学生表测试数据
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' , '女', '侦查系');

# --插入课程表测试数据
INSERT INTO course VALUES('01' , '语文' , '02');
INSERT INTO course VALUES('02' , '数学' , '01');
INSERT INTO course VALUES('03' , '英语' , '03');

# --插入教师表测试数据
INSERT INTO teacher VALUES('01' , '张三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');

# --插入成绩表测试数据
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' , NULL);
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' , NULL);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , NULL);
INSERT INTO sc VALUES('07' , '02' , 89);
INSERT INTO sc VALUES('07' , '03' , 98);

查询练习

临时去除ONLY_FULL_GROUP_BY

https://www.cnblogs.com/Wayou/p/mysql_group_by_issue.html

set @@GLOBAL.sql_mode='';
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

1、查询平均成绩大于等于 60 分的学号和平均成绩
SELECT Sno,AVG(Score) as avgScore from sc GROUP BY Sno HAVING avgScore >= 60;

如果要一起查姓名：
##先过滤sc表里的成绩再和student链接查数据
SELECT student.Sno,student.sname,a.av from student 
INNER JOIN (
	SELECT Sno,AVG(Score)AS av  from sc GROUP BY Sno HAVING AVG(Score)>=60
	)a
on student.Sno = a.Sno;

或者直接连接：
SELECT a.Sno,a.sname,AVG(b.score) as avgScore
FROM student a JOIN sc b
on a.Sno = b.Sno
GROUP BY a.Sno and a.sname
HAVING avgScore>=60

2、查询在 SC 表有成绩的学生信息
SELECT DISTINCT sc.Sno,student.sname FROM sc INNER JOIN student on sc.Sno = student.Sno WHERE sc.Score > 0;
或者连接起来查
SELECT a.Sno,a.sname from student a 
JOIN(SELECT DISTINCT Sno from sc WHERE Score > 0)b on a.Sno=b.Sno


3、查询姓“李”的老师的信息
SELECT * FROM teacher WHERE Tname REGEXP "^李";
SELECT * FROM teacher WHERE Tname LIKE "李%";
SELECT * FROM teacher WHERE Tname LIKE CONCAT("李","%")

4、统计姓“李”的老师的人数。
SELECT COUNT(Tname) num  FROM teacher WHERE Tname LIKE "李%";

5、查询所有学生的平均成绩并以降序显示。
SELECT Sno,AVG(Score) score FROM sc GROUP BY Sno ORDER BY score DESC;

SELECT a.Sno,a.sname,b.score from student a
JOIN(SELECT Sno,AVG(Score) score FROM sc GROUP BY Sno)b
on a.Sno = b.Sno
ORDER BY score DESC

6、统计男生和女生的人数。
SELECT SUM(CASE WHEN ssex="男" THEN 1 ELSE 0 END) "男生人数",SUM(CASE WHEN ssex="女" THEN 1 ELSE 0 END) "女生人数"FROM student

7、统计侦查系男生和女生的人数。
SELECT SUM(CASE WHEN ssex="男" && Sdept = "侦查系" THEN 1 ELSE 0 END) "男生人数",SUM(CASE WHEN ssex="女" && Sdept = "侦查系" THEN 1 ELSE 0 END) "女生人数"  FROM student
或者先筛表
SELECT SUM(CASE WHEN ssex="男" THEN 1 ELSE 0 END) "男生人数",SUM(CASE WHEN ssex="女" THEN 1 ELSE 0 END) "女生人数" FROM 
(SELECT * FROM student WHERE Sdept="侦查系") a

8、查询姓名中含有“雷”字的学生信息。
SELECT * FROM student WHERE sname REGEXP "雷"
SELECT * FROM student WHERE sname LIKE "%雷%"

9、查询现年龄大于30岁的学生信息。
SELECT * FROM student WHERE YEAR(CURRENT_DATE())-YEAR(sbirth)>30

10、查询平均成绩大于80的学生学号和姓名。
SELECT student.Sno,student.sname from student 
INNER JOIN (
	SELECT Sno,AVG(Score)AS av  from sc GROUP BY Sno HAVING AVG(Score)>=80
	)a
on student.Sno = a.Sno;

*11、查询平均成绩大于80的学生学号、姓名和平均成绩。（派生表）
SELECT student.Sno,student.sname,AVG(sc.Score) from student,sc 
WHERE student.Sno = sc.Sno GROUP BY student.Sno HAVING AVG(sc.Score)>80

12、统计选修了语文课的学生人数。
SELECT SUM(CASE WHEN Cno="02" && Score>0 THEN 1 ELSE 0 END) FROM sc
SELECT COUNT(Cno) FROM sc where Cno="02" && Score>0

13、统计至少选修了2号课和3号课的学生人数。
SELECT COUNT(Sno) FROM sc WHERE Cno = "02" and Sno in (SELECT Sno FROM sc WHERE Cno="03")

14、统计至少选修了3门课程的学生人数。
SELECT Sno,COUNT(Cno)FROM sc GROUP BY Sno HAVING COUNT(Cno)=3;
#问题：怎么排除成绩为null的情况

15、统计每一门选修人数，并显示超过5人的课程。
SELECT Cno,COUNT(Cno) FROM sc GROUP BY Cno HAVING COUNT(Cno)>5

16、在SC中插入一条学号为07的学生选修01号课程的记录。
INSERT INTO sc(Sno,Cno) VALUES("07","01");

17、删除所有男生的选课课程成绩。
DELETE FROM sc WHERE Sno in (SELECT Sno FROM student WHERE ssex="男")

18、将所有女生的成绩修改成NULL。
UPDATE sc SET Score = NULL WHERE Sno IN (SELECT Sno FROM student WHERE ssex="女")

19、查询所有学生的学号、性别、选修课程以及成绩。
SELECT a.Sno,a.ssex,b.Cno,b.Score
FROM student as a
LEFT JOIN sc as b
ON a.Sno = b.Sno

20、查询每门课的最高成绩。
SELECT Cno,MAX(Score) FROM sc GROUP BY Cno

21、查询每门课的平均成绩。
SELECT Cno,AVG(Score) FROM sc GROUP BY Cno

22、查询数学成绩低于90的学生信息。
SELECT * FROM student WHERE Sno IN (SELECT Sno FROM sc WHERE Cno="01"&&Score<90)