详解50道经典sql语句练习题

最近闲来无事,网上很流行的sql练习五十题,给自己充充电,我用的是Navicat(12.0.18).sql语句是从简单到难逐步提高,很适合大家学习,在写sql中,也有我的一些注解, 希望对大家有帮助,不多废话,直接上代码.
创建测试数据

# 创建student表：
CREATE TABLE student(
    id BIGINT(20) UNSIGNED  NOT NULL PRIMARY KEY,
    name NVARCHAR(4) NOT NULL, 
    birthday DATETIME NOT NULL, 
    sex NCHAR(2) NOT NULL
);

INSERT INTO student VALUES(1, '赵雷' , '1990-01-01' , '男');
INSERT INTO student VALUES(2, '钱电' , '1990-12-21' , '男');
INSERT INTO student VALUES(3, '孙风' , '1990-05-20' , '男');
INSERT INTO student VALUES(4, '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES(5, '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES(6, '吴兰' , '1992-03-01' , '女');
INSERT INTO student VALUES(7, '郑竹' , '1989-07-01' , '女');
INSERT INTO student VALUES(8, '张三' , '2017-12-20' , '女');
INSERT INTO student VALUES(9, '李四' , '2017-12-25' , '女');
INSERT INTO student VALUES(10, '李四' , '2017-12-30' , '女');
INSERT INTO student VALUES(11, '赵六' , '2017-01-01' , '女');
INSERT INTO student VALUES(12, '孙七' , '2018-01-01' , '女');

# 创建course表 课程表
CREATE TABLE course(
    id BIGINT(20) UNSIGNED  NOT NULL PRIMARY KEY,
    name NVARCHAR(4) NOT NULL,
    teacher_id BIGINT(20) UNSIGNED 
);
INSERT INTO course VALUES(1, '语文' , 2);
INSERT INTO course VALUES(2, '数学' , 1);
INSERT INTO course VALUES(3, '英语' , 3);

# 创建老师表
CREATE TABLE teacher(
    id BIGINT(20) UNSIGNED  NOT NULL PRIMARY KEY,
    name NVARCHAR(4)
);
INSERT INTO teacher VALUES(1, '张三');
INSERT INTO teacher VALUES(2, '李四');
INSERT INTO teacher VALUES(3, '王五');

# 创建grade表（成绩表）

# DECIMAL 数据类型是总位数为 precision 且小数点后位数为 scale 的小数。 
# [折叠/展开部分] 语法
# DECIMAL [ ( precision [ , scale ] ) ]
CREATE TABLE grade(
    id BIGINT(20) UNSIGNED  NOT NULL PRIMARY KEY,
    student_id BIGINT(20) UNSIGNED  NOT NULL,
    course_id BIGINT(20) UNSIGNED NOT NULL,
    score DECIMAL(5,2)
);
INSERT INTO grade VALUES(1, 1, 1, 80);
INSERT INTO grade VALUES(2, 1, 2, 90);
INSERT INTO grade VALUES(3, 1, 3, 99);
INSERT INTO grade VALUES(4, 2, 1, 70);
INSERT INTO grade VALUES(5, 2, 2, 60);
INSERT INTO grade VALUES(6, 2, 3, 80);
INSERT INTO grade VALUES(7, 3, 1, 80);
INSERT INTO grade VALUES(8, 3, 2, 80);
INSERT INTO grade VALUES(9, 3, 3, 80);
INSERT INTO grade VALUES(10, 4, 1, 50);
INSERT INTO grade VALUES(11, 4, 2, 30);
INSERT INTO grade VALUES(12, 4, 3, 20);
INSERT INTO grade VALUES(13, 5, 1, 76);
INSERT INTO grade VALUES(14, 5, 2, 87);
INSERT INTO grade VALUES(15, 6, 1, 31);
INSERT INTO grade VALUES(16, 6, 3, 34);
INSERT INTO grade VALUES(17, 7, 2, 89);
INSERT INTO grade VALUES(18, 7, 3, 98);

# 1.查询「李」姓老师的数量 用到的是条件查询中的模糊查询
select count(*) from teacher t  where t.`name` like '李%';

# 2.查询 1990 年出生的学生名单 第一个是我直接的想法.快就完事了,但是不严谨
select * from student WHERE birthday like '1990%' ;
SELECT * FROM student WHERE YEAR(birthday) = 1990;

# 3.查询名字中含有「风」字的学生信息 条件查询中的模糊查询
select * from student where name like '%风%';

# 4.求每门课程的学生人数 用到了聚合查询 分组查询 
select c.name , COUNT(*) from  course c  , grade g  where c.id = g.course_id GROUP BY c.name  ; 

# 5.查询本月过生日的学生
select * from student where month(now()) = month(birthday);
# 延伸 如果是查询某一个月过生日的同学 
select * from student where month(birthday)=1;

# 6.查询下月过生日的学生
select  * from student where month(now())+1  = month(birthday);

# 7.查询本周过生日的学生
# week() 返回参数的星期数  WEEKOFYEAR() 返回日期参数是日历上的第几周 (1-53)
# 第一个是我写的,看完答案,我觉得第二个更合适一些 , 但是查询结果是么有什么问题的
select * from student where week(now()) = week(birthday);
SELECT * FROM student WHERE WEEKOFYEAR(NOW()) = WEEKOFYEAR(birthday);

# 8.查询下周过生日的学生
select * from student where WEEKOFYEAR(NOW())+1 = WEEKOFYEAR(birthday);

# 9.查询各学生的年龄，只按年份来算
SELECT *, YEAR(NOW()) - YEAR(birthday) AS 年龄 FROM student;

# 10.按照出生日期来算，当前月日小于出生年月的月日则，年龄减一
# TIMESTAMPDIFF函数的使用
# 以下示例将以月份值的形式返回2018-01-01和2018-06-01的差值：
#SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-06-01') result;
#显示结果为: 5 MONTH是可以替换的year ,week , day... 都可以
# 这个是我查找 https://www.w3cschool.cn/sql/82rg1ozi.html找到的
SELECT *, TIMESTAMPDIFF(YEAR, birthday, NOW()) AS 年龄 FROM student;

# 11.检索至少选修两门课程的学生学号 分组查询, having后面可以使用聚合函数(统计函数)
select g.student_id , COUNT(g.course_id)  from grade  g GROUP BY g.student_id HAVING COUNT(g.course_id) >=2  ;

# 12.统计每门课程的学生选修人数（超过5人的课程才统计）
select c.`name` , COUNT(g.course_id)  from course c JOIN  grade g on c.teacher_id = g.course_id GROUP BY g.course_id HAVING COUNT(g.course_id)>5 ; 

# 13.查询存在不及格成绩的课程
select c.`name` , g.score  from grade g RIGHT JOIN course c on g.course_id = c.id where   g.score< 60   ; 
#  链接给的答案 
SELECT course_id FROM grade GROUP BY course_id HAVING COUNT(score < 60) > 0;

# 14.查询男生、女生人数
select sex , COUNT(*) from student GROUP BY sex ; 

# 15.查询每门课程被选修的学生数
select c.`name` , COUNT(*) from course c LEFT JOIN grade g on c.id = g.course_id  GROUP BY  g.course_id; 
#链接给出的答案
SELECT course_id, COUNT(student_id) FROM grade   GROUP BY course_id;

# 16.查询在grade表存在成绩的学生信息
select s.* , g.* from student s   , grade g  where s.id = g.student_id ; 
#链接给出的答案
SELECT * FROM student WHERE id IN ( SELECT student_id FROM grade);

# 17.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c.`name` ,AVG(g.score)  from course c  LEFT JOIN  grade g  on c.id = g.course_id GROUP BY c.`name` ORDER BY AVG(g.score) desc , c.id ; 
# 链接给出的答案
SELECT course_id, AVG(score) FROM grade GROUP BY course_id  ORDER BY AVG(score) DESC, course_id;

# 18.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select g.id , g.course_id, gr.course_id , g.score  from grade g , grade gr where g.course_id !=gr.course_id and g.score = gr.score and g.student_id  =gr.student_id ; 
#19. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select s.id , s.name ,g.course_id, g.score  from student s LEFT join grade g  on s.id = g.student_id ; 
#20. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select c.id , s.`name` ,g.id 学号, g.score from course c  join grade g  on c.id = g.course_id join student s on g.student_id  = s.id where  c.id = 1 and g.score >=80;

说说你的看法,欢迎在下方评论留言 .