最全面的手撕面试SQL语句

  每一条语句都认真编写与调试，我把建库建表插入数据一同整理了，方便各位调试与练习，但是难免不了存在纰漏，感谢大家的指正与理解！觉的写的不错的小伙伴儿，一键三连支持一下，后期会有持续更新！！抱拳了罒ω罒

1. 创建数据库stu_test2

 CREATE DATABASE stu_test2;
 USE stu_test2;
 SHOW TABLES ;

2. 分别创建四个数据表

 # 学生信息表
 CREATE TABLE Student(
 s_no INT, # 学号
 sname VARCHAR(20), # 姓名
 sage INT, # 年龄
 sex VARCHAR(20) # 性别
 );

 # 课程表
 CREATE TABLE Course(
 c_no INT, # 课程号
 cname VARCHAR(20), # 课程名
 t_no INT # 任课老师编号
 );

 # 分数表
 CREATE TABLE Sc(
 s_no INT, # 学号
 c_no INT, # 课程号
 score INT # 分数
 );

 # 教师信息表
 CREATE TABLE Teacher(
 t_no INT, # 教师编号
 tname VARCHAR(20) # 教师姓名
 )

3. 分别向四个数据表插入数据

 INSERT INTO Student VALUES
 (1001,'刘一',18,'男'),
 (1002,'钱二',19,'女'),
 (1003,'张三',17,'男'),
 (1004,'李四',18,'女'),
 (1005,'王五',17,'男'),
 (1006,'赵六',19,'女');
 
 INSERT INTO Course VALUES
 (2001,'语文',1 ),
 (2002,'数学',2 ),
 (2003,'英语',3 ),
 (2004,'物理',4 );
 
 INSERT INTO Sc VALUES
 (1001,2001,56),
 (1001,2002,78),
 (1001,2003,67),
 (1001,2004,58),
 (1002,2001,79),
 (1002,2002,81),
 (1002,2003,92),
 (1002,2004,68),
 (1003,2001,91),
 (1003,2002,47),
 (1003,2003,88),
 (1003,2004,56),
 (1004,2002,88),
 (1004,2003,90),
 (1004,2004,93),
 (1005,2001,46),
 (1005,2003,78),
 (1005,2004,53),
 (1006,2001,35),
 (1006,2002,68),
 (1006,2004,71);
 
 INSERT INTO Teacher VALUES
 (1,'叶平' ),
 (2,'贺高' ),
 (3,'杨艳' ),
 (4,'周磊' );

4. 重点题目练习

 # 1、查询每门课程被选修的学生数
 SELECT c_no,COUNT(1) FROM sc GROUP BY c_no
 
 # 2、查询男生、女生人数
 SELECT sex ,COUNT(1)FROM student GROUP BY sex
 
 # 3、查询姓“张”的学生名单
 SELECT * FROM student WHERE sname LIKE '张%'
 
 # 4、查询同名同性学生名单，并统计同名人数。
 SELECT * ,COUNT(1)FROM student GROUP BY sname HAVING COUNT(1) > 1
 
 # 5、查询2003年出生的学生名单
 SELECT * FROM student WHERE sage = YEAR(CURDATE()) - 2003
 
 # 6、查询不及格的课程，并按课程号从大到小排序。
 SELECT * FROM sc WHERE score < 60 ORDER BY c_no DESC
 
 # 7、检索至少选修两门课程的学生学号。
 SELECT s_no FROM Sc GROUP BY s_no HAVING COUNT(*)>2;
 
 # 8、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分。
 SELECT c_no 课程ID ,MAX(score)最高分,MIN(score)最低分 
 FROM sc GROUP BY c_no;
 
 # 9、查询课程平均分从高到低显示。
 SELECT AVG(score) avg_score FROM sc 
 GROUP BY c_no 
 ORDER BY avg_score DESC;
 
 # 10、查询姓李的老师的个数。
 SELECT COUNT(1)FROM teacher WHERE tname LIKE '李%'
 
 # 11、删除“叶平”老师课的sc表记录。
 DELETE FROM sc 
 WHERE c_no =(SELECT c_no FROM teacher,course 
 WHERE teacher.t_no = course.t_no AND tname = '叶平')
 
 # 12、向sc表中插入一些记录：没有上过编号“2003”课程的同学学号和分数
 INSERT INTO Sc SELECT s_no,2003,0 FROM Student 
 WHERE s_no NOT IN (SELECT s_no FROM Sc WHERE c_no='2003');
 
 # 13、查询出只选修了两门课程的全部学生的学号和姓名
 SELECT student.s_no,sname FROM sc,student 
 WHERE sc.s_no = student.s_no GROUP BY s_no HAVING COUNT(1) = 2
 
 # 14、查询所有同学的学号、姓名、选课数、总成绩。注意：*和1的区别
 SELECT p.s_no,sname,COUNT(1),SUM(score) FROM student p,sc t 
  WHERE p.s_no = t.s_no GROUP BY s_no
 
 # 15、查询每门课程的平均成绩，结果按平均成绩升序排列，
 平均成绩相同时，按课程号降序排列。
 SELECT c_no ,AVG(score)FROM Sc GROUP BY c_no 
 ORDER BY AVG(score) ASC,c_no DESC;
 
 # 16、查询平均成绩大于85的所有学生的学号，姓名和平均成绩
 SELECT student.s_no ,sname,AVG(score)FROM sc,student 
 WHERE sc.s_no = student.s_no GROUP BY s_no 
 HAVING AVG(score) > 85 ;
 
 # 17、查询课程名称为“英语”且分数低于60的学生姓名和分数
 SELECT sname ,score FROM student,sc,course 
 WHERE student.s_no = sc.s_no AND sc.c_no = course.c_no 
 AND cname = '英语' AND score < 60
 
 # 18、查询所有学生的选课情况
 SELECT student.s_no,sname,cname FROM student,sc,course 
 WHERE student.s_no=sc.s_no AND sc.c_no=course.c_no;
 
 # 19、查询课程编号为2003且课程成绩在80分以上的学生的学号和姓名。
 SELECT student.s_no,sname FROM sc,student 
 WHERE sc.s_no =student.s_no AND c_no = 2003 AND score > 80

# 20、求选修了课程的学生人数。
 SELECT COUNT(DISTINCT(s_no))FROM sc

 # 21、查询每门课程最好的前两名。
 SELECT * FROM sc a 
 WHERE (SELECT COUNT(DISTINCT score) FROM sc b 
 WHERE a.c_no = b.c_no AND b.score >= a.score) <= 2 
 ORDER BY a.c_no

 # 22、查询全部学生都选修的课程的课程号和课程名。
 SELECT course.c_no,cname FROM sc,course 
 WHERE sc.c_no = course.c_no GROUP BY c_no 
 HAVING COUNT(1) >= (SELECT COUNT(1) FROM student)
 
 # 23、查询两门及以上不及格课程的同学的学号及其平均成绩。
 SELECT s_no,AVG(score) FROM sc 
 WHERE s_no IN 
 (SELECT s_no FROM sc WHERE score < 60 GROUP BY s_no 
 HAVING COUNT(1) >= 2) GROUP BY s_no

 #24、查询所有课程成绩小于60分的同学的学号、姓名。
 SELECT s_no,sname FROM student 
 WHERE s_no NOT IN (SELECT DISTINCT(s_no) FROM sc WHERE score > 60)

 # 25、查询没有学全所有课的同学的学号、姓名。
 SELECT s_no,sname FROM student WHERE s_no IN
 (SELECT s_no FROM sc GROUP BY s_no 
 HAVING COUNT(1) < (SELECT COUNT(1) FROM course))

 # 26、查询“2001”课程比“2002”课程成绩高的所有学生的学号。
 SELECT p.s_no FROM (SELECT * FROM sc WHERE c_no = 2001) p,
 (SELECT * FROM sc WHERE c_no = 2002) t 
 WHERE p.s_no = t.s_no AND p.score > t.score

 # 27、查询平均成绩大于60分的同学的学号和平均成绩。
 SELECT s_no,AVG(score) FROM Sc GROUP BY s_no HAVING AVG(score)>60;

 # 28、查询没学过“叶平”老师课的同学的学号、姓名
 SELECT s_no,sname FROM student 
 WHERE s_no NOT IN (SELECT DISTINCT(s_no) FROM sc,teacher,course 
 WHERE tname = '叶平' AND teacher.t_no = course.t_no 
 AND sc.c_no=course.c_no)

 # 29、查询学过“2001”并且也学过编号“2002”课程的同学的学号、姓名。
 SELECT p.s_no,sname FROM (SELECT s_no FROM sc WHERE c_no = 2001)p, 
 (SELECT s_no FROM sc WHERE c_no = 2002)t, student 
 WHERE p.s_no = t.s_no AND p.s_no = student.s_no

 # 30、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
 SELECT DISTINCT student.s_no,sname FROM student,sc 
 WHERE student.s_no = sc.s_no AND c_no IN 
 (SELECT c_no FROM sc WHERE s_no = 1001 )

 # 31、把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩。
 # [坑](https://blog.csdn.net/u012767761/article/details/84997962)
 UPDATE sc SET score = (SELECT p.id FROM (SELECT AVG(score) id 
 FROM sc,course,teacher 
 WHERE teacher.t_no = course.t_no AND course.c_no = sc.c_no 
 AND teacher.tname = '叶平' ) AS p )
 WHERE c_no = (SELECT c_no FROM course,teacher 
 WHERE teacher.t_no = course.t_no AND teacher.tname = '叶平' )

 # 32、查询学习过“1005”号同学的所有课程的其他同学学号和姓名。
 SELECT sc.s_no,sname FROM sc,student 
 WHERE sc.s_no = student.s_no AND student.s_no != 1005 
 AND c_no IN (SELECT c_no FROM sc WHERE s_no = 1005) 
 GROUP BY s_no HAVING COUNT(1) = 
 (SELECT COUNT(1)FROM sc WHERE s_no = 1005) 

 # 33、统计各科成绩，各分数段人数：
 课程ID，课程名称，【100-85】，【85-70】，【70-60】，【<60】
 SELECT sc.c_no,cname,
 COUNT(CASE WHEN score > 85 AND score <= 100 THEN 1 END) '【100-85】',
 COUNT(CASE WHEN score > 70 AND score <= 85 THEN 1 END) '【85-70】',
 COUNT(CASE WHEN score > 60 AND score <= 70 THEN 1 END) '【70-60】',
 COUNT(CASE WHEN score <= 60 THEN 1 END) '【<60】'
 FROM sc,course WHERE sc.c_no = course.c_no GROUP BY c_no

 # 34、查询选修了“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩。
 SELECT sname,score FROM student,sc 
 WHERE student.s_no = sc.s_no AND c_no = 
 (SELECT c_no FROM teacher,course WHERE teacher.t_no = course.t_no 
 AND tname = '杨艳') ORDER BY score DESC LIMIT 1;

 # 35、修改18题的表结构，如下：
 可参考：https://blog.csdn.net/ZYC88888/article/details/87925897
 SELECT s_no,sname,
 SUM(IF(a.cname = '语文',1,0)) '语文',
 SUM(IF(a.cname = '数学',1,0))'数学',
 SUM(IF(a.cname = '英语',1,0))'英语',
 SUM(IF(a.cname = '物理',1,0))'物理'
 FROM (SELECT student.s_no,student.sname,course.cname FROM 
 student,sc,course WHERE student.s_no=sc.s_no 
 AND sc.c_no=course.c_no) a GROUP BY s_no;

参考文章：https://www.cnblogs.com/qluzzh/p/10782993.html