mysql数据库基础练习（最全50个Mysql数据库查询练习题）

mysql数据库基础练习

最全50个Mysql数据库查询练习题

此数据库查询语句是网络上50个数据库查询练习题目，网上有些版本是oracle语句写的，大多数公司还是用免费的mysql数据库，以下都是mysql版本，全部都有验证过。

表名和字段

–1.学生表
Student(s#, sname, sage,ssex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c#,cname,t#) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t#,tname) –教师编号,教师姓名
–4.成绩表
Sc(s#,c#,score) –学生编号,课程编号,分数

测试数据

用数据库可视化工具做练习非常方便，推荐使用sqlyog，软件图标是一只海豚。

在新连接种填上本机地址，用户名，密码和端口就直接连上mysql。

 

 

 

 

 

 

所有测试数据如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

# --插入学生表测试数据 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 "课程成绩高的学生的信息及课程分数

?

1 2 3 4 5 6

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 "课程的情况

?

1 2 3 4 5 6

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 sno2 IS NOT NULL

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

?

1 2 3 4 5

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 分的同学的学生编号和学生姓名和平均成绩

?

1 2 3 4 5

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 )

?

1 2 3 4 5

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就需要把其中一个字段改名

?

1 2 3 4 5 6

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. 查询「李」姓老师的数量

SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%'

6. 查询学过「张三」老师授课的同学的信息

?

1 2 3 4 5

# 张三老师是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='张三')))

# 7. 查询没有学全所有课程的同学的信息

SELECT `s#`,COUNT(`c#`) AS course_num FROM sc GROUP BY `s#`
    HAVING course_num < (SELECT COUNT(*) FROM course)

# 8. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

?

1 2 3 4

SELECT * FROM student WHERE `s#` IN   (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN     (SELECT `c#` FROM sc WHERE `s#`=01))   AND `s#`!= 01

# 9. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

?

1 2 3 4 5 6 7

SELECT `s#` FROM   (SELECT * FROM sc     LEFT JOIN   (SELECT `c#` AS cno FROM sc WHERE `s#` =01) a     ON sc.`c#` = a.cno) AS b GROUP BY `s#`    HAVING COUNT(b.`s#`) = (SELECT COUNT(`c#`) AS cno FROM sc WHERE `s#` =01)

# 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

?

1 2 3 4 5 6

# 张三是01 # 01老师是教数学，c#是02 SELECT * FROM student WHERE `s#` NOT IN   (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN     (SELECT `c#` FROM course WHERE `t#` IN       (SELECT `t#` FROM teacher WHERE tname = '张三')))

# 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

?

1 2 3 4 5 6 7 8

SELECT `s#`, sname, avg_score FROM   (SELECT `s#`, sname FROM student WHERE `s#` IN     (SELECT a.`s#` FROM       (SELECT `s#`,COUNT(`c#`) AS num FROM sc WHERE score <60 GROUP BY `s#`) a       WHERE num >=2)) AS b     LEFT JOIN   (SELECT `s#` AS sno ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS c     ON b.`s#` = c.sno

# 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

?

1 2 3 4 5 6 7

SELECT `s#`, sname, score FROM    student AS a     LEFT JOIN   (SELECT `s#` AS sno,`c#`,score FROM sc WHERE `c#`= 01 AND score <60 )b     ON a.`s#`= b.sno   WHERE score IS NOT NULL   ORDER BY score DESC

# 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#` ORDER BY avg_score DESC

# 14. 查询各科成绩最高分、最低分和平均分：
# 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
# 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
# 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

?

1 2 3 4 5 6 7 8 9 10 11

SELECT DISTINCT a.`c#`,cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 FROM sc a LEFT JOIN course ON a.`c#`=course.`c#` LEFT JOIN (SELECT `c#`, MAX(score)最高分, MIN(score)最低分, AVG(score)平均分 FROM sc GROUP BY `c#`)b ON a.`c#`=b.`c#` LEFT JOIN (SELECT `c#`, ROUND( r1 /cnt * 100, 2 ) AS 及格率 FROM   (SELECT `c#`, (SUM(CASE WHEN score >=60 THEN 1 ELSE 0 END)*1.00) AS r1 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) c1) c ON a.`c#`=c.`c#` LEFT JOIN (SELECT `c#`, ROUND( r2 /cnt * 100, 2 ) AS 中等率 FROM   (SELECT `c#`, (SUM(CASE WHEN score >=70 AND score<80 THEN 1 ELSE 0 END)*1.00) AS r2 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) d1) d ON a.`c#`=d.`c#`  LEFT JOIN (SELECT `c#`, ROUND( r3 /cnt * 100, 2 ) AS 优良率 FROM   (SELECT `c#`, (SUM(CASE WHEN score >=80 AND score<90 THEN 1 ELSE 0 END)*1.00) AS r3 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) e1) e ON a.`c#`=e.`c#` LEFT JOIN (SELECT `c#`, ROUND( r4 /cnt * 100, 2 ) AS 优秀率 FROM   (SELECT `c#`, (SUM(CASE WHEN score >=90 THEN 1 ELSE 0 END)*1.00) AS r4 , COUNT(*) AS cnt FROM 标签： mysql 数据库 查询 上一篇下一篇 猜您喜欢 mysql支持存储表情（MySQL如何插入Emoji表情） mysql新增字段语句（关于Mysql update修改多个字段and的语法问题详析） druid数据库连接池原理详解（阿里巴巴Druid，轻松实现MySQL数据库加密！） zabbix如何配置监控（zabbix监控mysql的实例方法） phpmysql完全学习手册教程（Windows下搭建PHP开发环境Apache+PHP+MySQL） mysql用户删除了如何设置（MySQL两种删除用户语句的区别delete user和drop user） mysql缓存是什么（详解mysql查询缓存简单使用） mysql中null的用法（为什么mysql字段要使用NOT NULL） mysql的使用步骤（MySQL infobright的安装步骤） mysql8.0.23的安装步骤（mysql 8.0.22 下载安装配置方法图文教程） 网友很惭愧，自己写了很多年的字，到头来还不如一名小学生写的好（自己写了很多年的字） 中华第一楷 张瑞龄 86岁高龄，苦练楷书71年，一幅字卖593万（中华第一楷张瑞龄） 冯骥才 年意（冯骥才年意） （） 百事大吉蓝底 绿底手机高清壁纸（绿底手机高清壁纸） 蓝底证件照怎么制作 证件照换底色 换尺寸快速搞定（蓝底证件照怎么制作） 热门推荐 1System.Action委托 2html怎么引入sass（利用CSS的Sass预处理器框架来制作居中效果） 3dedecms点赞功能怎么做呢（dedecms实现任意页面调用当前会员信息的方法） 4自己在做项目过程中的php知识（PHP+Oracle本地开发环境搭建方法详解） 5centos系统mysql安装及连接教程（CentOS8安装SQLServer2019的过程） 6mysqlcount使用方法（详解 MySQL中count函数的正确使用方法） 7dedecms 数据转移（dedecms5.7 通过替换文件升级后 所有档案列表为空的解决方法） 8随意化快排python算法（python快排算法详解） © 2021 开心学习