最近闲来无事,网上很流行的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;
说说你的看法,欢迎在下方评论留言 .