SQL练习题（4），持续更新中

声明：所有题目均是百度搜索，再由自己整理出来的，并非自己出的题。

1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

student 表

name	course	score
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

解法一：

每门课都大于80分，也就是说学生的所有课的最小分数大于80分MIN（score）>80再根据名称进行排序，HAVING在分组的后的结果种进行筛选

SELECT NAME FROM `student` GROUP BY NAME HAVING MIN(score)>80;

解法二：

先查出每个学生的姓名和每门课程的最小分的结果集

再在查出的结果集的基础上查询姓名，并使用大于80分的条件进行筛选

SELECT NAME FROM (
SELECT NAME, MIN(score) AS msc FROM `student` GROUP BY NAME) AS t1 
WHERE t1.msc>80;

解法三：

先查出有任一门课程分数小于80分的学生姓名的结果集

再排除掉上述结果集里的姓名，便查出大于80分的学生姓名，需用DISTINCT去重

SELECT DISTINCT NAME FROM `student` WHERE NAME NOT IN (
SELECT NAME FROM `student` WHERE score<80);

解法四：

使用NOT EXISTS特性，不关心子查询中的结果，

如果子查询有返回记录，那么就排除当前记录，

如果子查询没有返回记录，那么就输出当前记录。

SELECT DISTINCT NAME FROM `student`t1 WHERE NOT EXISTS (
SELECT * FROM `student` t2 WHERE t1.name=t2.name AND t2.score<80);

解法五：

将score<80作为单独的一列，满足条件为1，不满足为0

再通过相加可以得到和，满足条件和=0也就是每一门分数都大于80才会得0.

SELECT NAME FROM `student` GROUP BY NAME HAVING SUM(score<80)=0;

解法六：

查出所有记录条数

查出大于80分的记录条数

再使用两个记录条数比对，查两个的并集

SELECT * FROM
(SELECT NAME,COUNT(1) AS c1 FROM `student` GROUP BY NAME) t1,
(SELECT NAME,COUNT(1) AS c2 FROM `student` WHERE score>80 GROUP BY NAME) t2
WHERE t1.name=t2.name AND t1.c1=t2.c2;

解法七：

1.与解法六思路类似，在分组的结果上使用每个学生的分数记录条数和大于80分的记录条数进行比较，相等则说明都每门课程大于80分。

SELECT NAME FROM `student` GROUP BY NAME HAVING COUNT(1)=SUM(IF (score>80,1,0));

解法八：

左连接查询，通过连接表查询出t2表中王五的数据为null

再根据条件筛选出t2表中名字为null的

SELECT DISTINCT t1.`name` FROM `student` AS t1

LEFT JOIN (SELECT * FROM `testscore` WHERE score<=80) AS t2

ON t1.name=t2.name

WHERE t2.name IS NULL;

（求最高分成绩的学生信息）思路

-- 先找出最高分，查出来的结MAX(score)=100

SELECT MAX(score) FROM `student`

-- 再找出所有分数等于最高分的成绩

SELECT * FROM `student` WHERE score=100

-- 然后两条语句结合 (不相关子查询)

SELECT * FROM `testscore` WHERE score=(SELECT MAX(score) FROM `testscore`);

（求 每个同学最高分的成绩）

解法一：

SELECT NAME,MAX(score) FROM `student` GROUP BY NAME;

解法二：

相关子查询:依赖外部查询的数据，外部查询每执行一次，子查询就执行一次

SELECT * FROM `student` t1 WHERE t1.`score`=(
SELECT MAX(score) FROM `student` t2 WHERE t2.`name`=t1.`name`);

-- （求大于平均分的成绩信息）

SELECT * FROM `student` WHERE score > (SELECT AVG(score) FROM `student`);

2. 现有学生表如下:

自动编号 学号 姓名 课程编号 课程名称 分数