mysql常见sql题_SQL常见试题

下文SQL查询涉及到的数据库表有四张，分别为：学生表(student)、成绩表(score)、课程表(course)和教师表(teacher)

一、简单查询

1. 查询姓“猴”的学生名单

SELECT * FROM student WHERE 姓名 LIKE '猴%';

2. 查询姓名中最后一个字是“猴“的学生名单

SELECT * FROM student WHERE 姓名 LIKE '%猴';

3. 查询姓名中带“猴”的学生名单

SELECT * FROM student WHERE 姓名 LIKE '%猴%';

4. 查询姓“孟”老师的个数

SELECT count(*) FROM teacher WHERE 教师姓名 LIKE '孟%';

二、汇总分析

1. 查询课程编号为“0002”的总成绩

SELECT sum(成绩) as 总成绩 FROM score where 课程号='0002';

2. 查询选了课程的学生人数

SELECT count(DISTINCT 学号) as 选课人数 FROM score;

3. 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分

SELECT 课程号,max(成绩) as 最高分,min(成绩) as 最低分

FROM score

GROUP BY 课程号;

4. 查询每门课程被选修的学生数

SELECT 课程号,count(学号) as 选修学生数

FROM score

GROUP BY 课程号;

5. 查询男生、女生人数

SELECT 性别,count(学号) as 人数

FROM student

GROUP BY 性别;

6. 查询平均成绩大于60分学生的学号和平均成绩

SELECT 学号,avg(成绩) as 平均成绩

FROM score

GROUP BY 学号

HAVING avg(成绩)>60;

7. 查询至少选修两门课程的学生学号

SELECT 学号,count(课程号) as 选修课程数

FROM score

GROUP BY 学号

HAVING count(课程号)>=2;

8. 查询同名同姓(相同姓名)学生名单并统计同名人数

SELECT 姓名,count(学号) as 同名人数

FROM student

GROUP BY 姓名

HAVING count(学号)>1;

9. 查询不及格的课程并按课程号从大到小排列

SELECT 课程号,成绩

FROM score

WHERE 成绩<60

ORDER BY 课程号 desc;

10. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT 课程号,avg(成绩) as 平均成绩

FROM score

GROUP BY 课程号

ORDER BY 平均成绩 asc,课程号 desc;

11. 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

SELECT 学号

FROM score

WHERE 课程号='0004'

AND 成绩<60

ORDER BY 成绩 desc;

12. 统计每门课程的学生选修人数(超过2人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

SELECT 课程号,count(学号) as 选修人数

FROM score

GROUP BY 课程号

HAVING count(学号)>2

ORDER BY 选修人数 desc,课程号 asc;

13. 查询两门以上不及格课程的同学的学号，以及不及格课程及的平均成绩

SELECT 学号,avg(成绩) as 平均成绩

FROM score

WHERE 成绩<60

GROUP BY 学号

HAVING count(课程号)>2;

三、复杂查询

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

SELECT 学号,姓名

FROM student

WHERE 学号 in(

SELECT 学号

FROM score

GROUP BY 学号

HAVING max(成绩)<60);

2. 查询没有学全所有课的学生的学号、姓名

SELECT 学号,姓名

FROM student

WHERE 学号 not in(

SELECT 学号

FROM score

GROUP BY 学号

HAVING count(课程号)=(SELECT count(*) FROM course)

);

3. 查询出只选修了两门课程的全部学生的学号和姓名

SELECT 学号,姓名

FROM student

WHERE 学号 in(

SELECT 学号

FROM score

GROUP BY 学号

HAVING count(课程号)=2);

4. 1990年出生的学生名单

这里要用到日期函数

SELECT 学号,姓名

FROM student

WHERE year(出生日期)=1990;topN问题：分组取每组最大值、最小值，每组最大的N条(top N)记录。

5. 按课程号分组取成绩最大值所在行的数据

(知识点：关联子查询)

SELECT * FROM score as a

WHERE 成绩 = (SELECT max(成绩)

FROM score as b

WHERE a.课程号=b.课程号

GROUP BY 课程号);

6. 按课程号分组取成绩最小值所在行的数据

SELECT * FROM score as a

WHERE 成绩 = (SELECT min(成绩)

FROM score as b

WHERE a.课程号=b.课程号

GROUP BY 课程号);

7. 查询各科成绩前两名的记录

(知识点：union all)

(SELECT * FROM score

WHERE 课程号='0001'

ORDER BY 成绩 desc

limit 2)

UNION ALL

(SELECT * FROM score

WHERE 课程号='0002'

ORDER BY 成绩 desc

limit 2)

UNION ALL

(SELECT * FROM score

WHERE 课程号='0003'

ORDER BY 成绩 desc

limit 2);

四、多表查询

1) 下面是学生的名单，表名为“学生表”；近视学生的名单，表名为“近视学生表”。

1. 请问不是近视眼的学生都有谁？

SELECT a.学号,a.姓名

FROM 学生表 as a

left join 近视学生表 as b

on a.学号= b.学生学号

WHERE b.学生学号 is null;

2) 某网站包含两个表，顾客姓名表(表名Customers)和 购买记录表(表名Orders)

1. 找出所有从不订购任何东西的顾客

SELECT a.ID,a.`Name`

FROM Customers as a

left join Orders as b

on a.ID= b.Customerid

WHERE b.CustomerId is null;

3) 下面SQL查询涉及到的数据库表有四张，分别为：学生表(student)、成绩表(score)、课程表(course)和教师表(teacher)

1. 查询所有学生的学号、姓名、选课数、总成绩

SELECT a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

FROM student as a

left join score as b

on a.学号= b.学号

GROUP BY a.学号;

2. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT a.学号,a.姓名,avg(b.成绩) as 平均成绩

FROM student as a

left join score as b

on a.学号= b.学号

GROUP BY a.学号

HAVING avg(b.成绩)>85;

3. 查询学生的选课情况：学号，姓名，课程号，课程名称

SELECT a.学号,a.姓名,c.课程号,c.课程名称

FROM student as a

left join score as b

on a.学号= b.学号

left join course as c

on b.课程号=c.课程号;

4. 查询出每门课程的及格人数和不及格人数

SELECT 课程号,

sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,

sum(case when 成绩<60 then 1 else 0 end) as 不及格人数

FROM score

GROUP BY 课程号;

5. 使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

SELECT c.课程号,c.课程名称,

sum(case when b.成绩 between 85 and 100 then 1 else 0 end) as '[100-85]人数',

sum(case when b.成绩 >= 70 and b.成绩 < 85 then 1 else 0 end) as '[85-70]人数',

sum(case when b.成绩 >= 60 and b.成绩 < 70 then 1 else 0 end) as '[70-60]人数',

sum(case when b.成绩 < 60 then 1 else 0 end) as '[<60]人数'

FROM score as b

right join course as c

on b.课程号=c.课程号

GROUP BY c.课程号,c.课程名称;

6. 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

SELECT a.学号,a.姓名

FROM student as a

inner join score as b

on a.学号=b.学号

WHERE b.课程号='0003'

and b.成绩>80 ;

7. 下面是学生的成绩表(表名score，列名：学号、课程号、成绩)

使用sql实现将该表行转列为下面的表结构

SELECT 学号,

sum(case when 课程号='0001' then 成绩 else 0 end) as '课程号0001',

sum(case when 课程号='0002' then 成绩 else 0 end) as '课程号0002',

sum(case when 课程号='0003' then 成绩 else 0 end) as '课程号0003'

FROM score

GROUP BY 学号;

SQL联结总结为下面图片

五、如何提高SQL查询效率

1. select子句中尽量避免使用*

在实际业务中使用的数据量非常大，甚至达到千万级以上，使用*导致数据库计算量过大，占用过多内存空间，另外，如果select * 用于多表联结，会造成更大的成本开销。

2. where子句比较符号左侧避免函数

尽量避免在where条件子句中，比较符号的左侧出现表达式、函数等操作。因为这会导致数据库引擎进行全表扫描，从而增加运行时间。

3. 尽量避免使用in和not in

in和not in也会导致数据库进行全表搜索，增加运行时间。

4. 尽量避免使用or

or同样会导致数据库进项全表搜索，从而增加运行时间，可考虑使用union替换。

5. 使用limit子句限制返回的数据行数

limit用于分页查询时越往后翻性能越差。