最全50个数据库查询练习题，Mysql数据库版本，全部都验证
过
此数据库查询语句是⽹络上50个数据库查询练习题⽬，⽹上有些版本是oracle语句写的，⼤多数公司还是⽤免费的mysql数据库，以下都是mysql版本，全部都有验证过。
表名和字段
–1.学⽣表
Student(s#, sname, sage,ssex) –学⽣编号，学⽣姓名, 出⽣年⽉，学⽣性别
–2.课程表
Course(c#,cname,t#) – –课程编号, 课程名称, 教师编号
–3.教师表fategrandorder樱花动漫
Teacher(t#,tname) –教师编号，教师姓名
–4.成绩表
Sc(s#,c#,score) –学⽣编号，课程编号，分数
测试数据
⽤数据库可视化⼯具做练习⾮常⽅便，推荐使⽤sqlyog，软件图标是⼀只海豚。
在新连接种填上本机地址，⽤户名，密码和端⼝就直接连上mysql。
所有测试数据如下：
# --插⼊学⽣表测试数据
INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '⼥');
INSERT INTO student VALUES('06' , '吴兰' , '1992-03-01' , '⼥');
INSERT INTO student VALUES('07' , '郑⽵' , '1989-07-01' , '⼥');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '⼥');
# --插⼊课程表测试数据
INSERT INTO course VALUES('01' , '语⽂' , '02');
INSERT INTO course VALUES('02' , '数学' , '01');
INSERT INTO course VALUES('03' , '英语' , '03');
# --插⼊教师表测试数据
INSERT INTO teacher VALUES('01' , '张三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');
# --插⼊成绩表测试数据
INSERT INTO sc VALUES('01' , '01' , 80);
编程好学吗去极客时间INSERT INTO sc VALUES('01' , '02' , 90);
INSERT INTO sc VALUES('01' , '03' , 99);
INSERT INTO sc VALUES('02' , '01' , 70);
INSERT INTO sc VALUES('02' , '02' , 60);
INSERT INTO sc VALUES('02' , '03' , 80);
INSERT INTO sc VALUES('03' , '01' , 80);
INSERT INTO sc VALUES('03' , '02' , 80);
INSERT INTO sc VALUES('03' , '03' , 80);
INSERT INTO sc VALUES('04' , '01' , 50);
INSERT INTO sc VALUES('04' , '02' , 30);
INSERT INTO sc VALUES('04' , '03' , 20);
INSERT INTO sc VALUES('05' , '01' , 76);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , 34);
INSERT INTO sc VALUES('07' , '02' , 89);
INSERT INTO sc VALUES('07' , '03' , 98);
最后是50个数据库查询练习，已经验证过，是mysql版本的。
# 1. 查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数
SELECT * FROM
(SELECT `s#` AS sno1, `c#`AS cno1, score FROM sc WHERE `c#`=01) a LEFT JOIN
(SELECT `s#` AS sno2, `c#`AS cno2, score FROM sc WHERE `c#`=02) b ON a.sno1 = b.sno2
WHERE a.score > b.score
# 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT * FROM
(SELECT `s#` AS sno1, `c#`AS cno1, score FROM sc WHERE `c#`=01) a
LEFT JOIN
maven主要作用
(SELECT `s#` AS sno2, `c#`AS cno2, score FROM sc WHERE `c#`=02) b
ON a.sno1 = b.sno2
matlab 求解方程组WHERE sno2 IS NOT NULL
# 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显⽰为 null )
SELECT * FROM
(SELECT `s#` AS sno1, `c#`AS cno1, score FROM sc WHERE `c#`=01) a
LEFT JOIN
(SELECT `s#` AS sno2, `c#`AS cno2, score FROM sc WHERE `c#`=02) b
ON a.sno1 = b.sno2
# 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM
sc WHERE `c#`='02' AND `s#` NOT IN (SELECT `s#` FROM sc WHERE `c#`='01')
# 2. 查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
SELECT a.`s#`,b.`sname`, a.avg_score FROM
(SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS a
LEFT JOIN student AS b
ON a.`s#` = b.`s#`
WHERE a.avg_score >=60
# 3. 查询在 SC 表存在成绩的学⽣信息
SELECT * FROM student WHERE `s#` IN (SELECT DISTINCT `s#` FROM sc)
# 4. 查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显⽰为 NULL )
SELECT `s#` ,sname , course_num , score_sum FROM
(SELECT `s#`, sname FROM student ) AS a
LEFT JOIN
(SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b ON a.`s#` = b.sno
# 4.1 查有成绩的学⽣信息
# 在最外⾯⼀层select的时候，不可以⽤函数
# 如果两张表连接之后，有相同的字段，这时候select就需要把其中⼀个字段改名
SELECT `s#` ,sname , course_num , score_sum FROM
(SELECT `s#`, sname FROM student ) AS a
LEFT JOIN
(SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b ON a.`s#` = b.sno
WHERE course_num IS NOT NULL
# 5. 查询「李」姓⽼师的数量
mysql面试题测试>淘宝购物车代码SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%'
# 6. 查询学过「张三」⽼师授课的同学的信息
# 张三⽼师是01号
SELECT * FROM student WHERE `s#` IN
(SELECT `s#` FROM sc WHERE `c#` =
(SELECT `c#` FROM course WHERE `t#` =
(SELECT `t#` FROM teacher WHERE tname='张三')))
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论