[数据库笔记] SQL50题1 -10

0.建表和插入数据

（1）建表

CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

（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' , '女');
-- 课程表测试数据
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 Score VALUES('01' , '01' , 80);
INSERT INTO Score VALUES('01' , '02' , 90);
INSERT INTO Score VALUES('01' , '03' , 99);
INSERT INTO Score VALUES('02' , '01' , 70);
INSERT INTO Score VALUES('02' , '02' , 60);
INSERT INTO Score VALUES('02' , '03' , 80);
INSERT INTO Score VALUES('03' , '01' , 80);
INSERT INTO Score VALUES('03' , '02' , 80);
INSERT INTO Score VALUES('03' , '03' , 80);
INSERT INTO Score VALUES('04' , '01' , 50);
INSERT INTO Score VALUES('04' , '02' , 30);
INSERT INTO Score VALUES('04' , '03' , 20);
INSERT INTO Score VALUES('05' , '01' , 76);
INSERT INTO Score VALUES('05' , '02' , 87);
INSERT INTO Score VALUES('06' , '01' , 31);
INSERT INTO Score VALUES('06' , '03' , 34);
INSERT INTO Score VALUES('07' , '02' , 89);
INSERT INTO Score VALUES('07' , '03' , 98);

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

（1）先查询课程01

SELECT s_id, c_id, s_score FROM score WHERE c_id = "01";

（2）再查询课程02

SELECT s_id, c_id, s_score FROM score WHERE c_id = "02";

（3）连接

SELECT * FROM
(
SELECT s_id, c_id, s_score FROM score WHERE c_id = "01"
) AS a
INNER JOIN
(
SELECT s_id, c_id, s_score FROM score WHERE c_id = "02"
) AS b ON a.s_id=b.s_id
;

（4）加条件

SELECT a.s_id "s_no",c.s_name, a.s_score "01", b.s_score "02" FROM
(
SELECT s_id, c_id, s_score FROM score WHERE c_id = "01"
) AS a
INNER JOIN
(
SELECT s_id, c_id, s_score FROM score WHERE c_id = "02"
) AS b ON a.s_id=b.s_id
INNER JOIN student AS c ON c.`s_id`=a.s_id
WHERE a.s_score > b.s_score
;

2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

SELECT s_id, AVG(s_score)
FROM score
GROUP BY s_id HAVING AVG(s_score) > 60;

3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

（1）先查询所有信息

SELECT a.*, b.*
FROM Student AS a
LEFT JOIN score AS b ON a.`s_id`=b.s_id;

（2）统计

SELECT a.s_id, a.`s_name`, COUNT(b.c_id), SUM(b.s_score)
FROM student AS a 
LEFT JOIN score AS b ON a.s_id=b.s_id
GROUP BY s_id;

（3）去掉空值

SELECT a.s_id, a.`s_name`, COUNT(b.c_id), SUM(IFNULL(b.s_score, 0)) sum_score
FROM student AS a 
LEFT JOIN score AS b ON a.s_id=b.s_id
GROUP BY s_id;

4、查询姓“张”的老师的个数（不重要）

SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE "张%";

如果要求去掉重名

SELECT COUNT(DISTINCT t_name)
FROM teacher
WHERE t_name LIKE "张%";

5、查询没学过“张三”老师课的学生的学号、姓名（重点）

（1）方法1——嵌套子查询

-- 查询张三老师的id
SELECT t_id FROM teacher
WHERE t_name = "张三";

-- 查询张三老师教授的课程号
SELECT c_id FROM course
WHERE t_id = (
	SELECT t_id FROM teacher
	WHERE t_name = "张三"
);

-- 查询学过的
SELECT s_id FROM score
WHERE c_id = (
	SELECT c_id FROM course
	WHERE t_id = (
		SELECT t_id FROM teacher
		WHERE t_name = "张三"
	)
);


-- 查询没学过的
SELECT s_id, s_name FROM Student
WHERE s_id NOT IN (
	SELECT s_id FROM score
	WHERE c_id = (
		SELECT c_id FROM course
		WHERE t_id = (
			SELECT t_id FROM teacher
			WHERE t_name = "张三"
		)
	)
);

（2）方法2——连接查询

SELECT s_id, s_name FROM student
WHERE s_id NOT IN
( 
	SELECT s.s_id FROM score AS s
	INNER JOIN course AS c ON s.c_id=c.c_id
	INNER JOIN teacher AS t ON c.t_id=t.t_id
	WHERE t.t_name = "张三"
);

6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

SELECT st.s_id, st.s_name, s.c_id, c.c_name, c.t_id, t.t_name
FROM student AS st
INNER JOIN score AS s ON s.s_id=st.s_id
INNER JOIN course AS c ON s.c_id=c.c_id
INNER JOIN teacher AS t ON t.t_id=c.t_id
WHERE t.t_name = "张三"
ORDER BY st.s_id;

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

SELECT s_id, s_name FROM student
WHERE s_id IN (
	SELECT a.s_id FROM
		(SELECT * FROM score WHERE c_id="01") AS a
	INNER JOIN
		(SELECT * FROM score WHERE c_id="02") AS b
	ON a.s_id = b.s_id
)
;

8、查询课程编号为“02”的总成绩（不重点）

SELECT SUM(s_score)FROM score
WHERE c_id = "02";

9、查询所有课程成绩小于60分的学生的学号、姓名

SELECT s_id, s_name FROM student
WHERE s_id IN(
	SELECT s_id FROM score
	GROUP BY s_id HAVING MAX(s_score) < 60
);

10.查询没有学全所有课的学生的学号、姓名(重点)

SELECT st.s_id, st.s_name
FROM student AS st
LEFT JOIN score AS sc ON st.`s_id`=sc.s_id
GROUP BY st.s_id HAVING COUNT(DISTINCT sc.c_id)
<
(SELECT COUNT(DISTINCT c_id) FROM course)
;

参考：

https://zhuanlan.zhihu.com/p/43289968
https://www.bilibili.com/video/BV1q4411G7Lw