您的位置:首页 > 数据库 > > 正文

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

更多 时间:2022-01-14 02:15:52 类别:数据库 浏览量:2847

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。

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

 

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

 

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

 

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

 

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

 

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

 

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

mysql数据库基础练习(最全50个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
  • 上一篇:详解从vue的组件传值着手观察者模式(详解从vue的组件传值着手观察者模式)
  • 下一篇:有哪些优秀的web服务器(几款比较好的WEB服务器软件推荐)
  • 您可能感兴趣