MySQL查询练习题

说明

工作多年，对mysql查询语句的使用一直停留在将就用，遇到难一点的、复杂一点的就束手无策的状态。这几天闲着没事做，找了一些练习题做下，感觉对select语句稍微熟悉一点了。原文转载自其他地方，但原文没有答案，所以写此文记录一下。

### 数据准备
/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

从init.sql文件中导入数据

#准备表、记录
mysql> create database db1;
mysql> use db1;
mysql> source /root/init.sql

！！！重中之重：练习之前务必搞清楚sql逻辑查询语句的执行顺序
链接

练习题

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号，姓名，选课数，总成绩

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级

11、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录
 
13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名

题目

参考答案

SELECT c.cname, t.tname
FROM course c
LEFT JOIN teacher t
ON c.teacher_id = t.tid

SELECT gender, COUNT(sid)
FROM student 
GROUP BY gender

SELECT s.sname
FROM student s
LEFT JOIN score sc 
ON sc.student_id = s.sid
LEFT JOIN course c
ON c.cid = sc.course_id
WHERE c.cname = "物理"
AND sc.num = 100

SELECT s.sname 姓名, AVG(sc.num) 平均成绩
FROM student s
LEFT JOIN score sc
ON sc.student_id = s.sid
GROUP BY sc.student_id HAVING AVG(sc.num) > 80;

SELECT s.sid 学号, s.sname 姓名, COUNT(sc.course_id) 选课总数, SUM(sc.num) 总成绩
FROM student s
LEFT JOIN score sc
ON sc.student_id = s.sid
GROUP BY s.sid;

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

SELECT s.sname
FROM student s
WHERE NOT EXISTS (
        SELECT *
        FROM score sc
        INNER JOIN course c
        ON sc.course_id = c.cid
        INNER JOIN teacher t
        ON t.tid = c.teacher_id
        WHERE t.tname = "李平老师"
        AND sc.student_id = s.sid
)

SELECT s.sname, wl.num 物理成绩, sw.num 生物成绩
FROM student s
LEFT JOIN (SELECT sc.student_id, sc.num
    FROM score sc
    INNER JOIN course c
    ON c.cid = sc.course_id
    AND c.cname = "物理") wl
ON wl.student_id = s.sid
LEFT JOIN (SELECT sc.student_id, sc.num
    FROM score sc
    INNER JOIN course c
    ON c.cid = sc.course_id
    AND c.cname = "生物" ) sw
ON sw.student_id = wl.student_id
WHERE wl.num > sw.num;

SELECT s.sname
FROM student s
LEFT JOIN score sc 
ON s.sid = sc.student_id
INNER JOIN (SELECT cid
       FROM course
           WHERE cname IN ("物理", "体育")) cc
ON cc.cid = sc.course_id
GROUP BY s.sid HAVING COUNT(sc.course_id) != 2;

SELECT s.sname 姓名, cl.caption 班级
FROM class cl
LEFT JOIN student s
ON cl.cid = s.class_id
LEFT JOIN score sc
ON s.sid = sc.student_id
WHERE sc.num < 60
GROUP BY sc.student_id 
HAVING SUM(sc.course_id) >=2;

SELECT s.sname 姓名
FROM course c
INNER JOIN score sc
ON sc.course_id = c.cid
INNER JOIN student s
ON s.sid = sc.student_id
GROUP BY sc.student_id 
HAVING COUNT(sc.course_id) = (SELECT COUNT(1) FROM course)

SELECT sc.* 
FROM score sc 
WHERE sc.course_id IN 
(
   SELECT c.cid
   FROM teacher t
   LEFT JOIN course c
   ON c.teacher_id = t.tid
   WHERE t.tname = "李平老师"
)

SELECT c.cid, c.cname
FROM course c
LEFT JOIN score sc
ON sc.course_id = c.cid
GROUP BY c.cid 
HAVING COUNT(sc.student_id) = (SELECT COUNT(1) FROM student);
# 查询结果为空

SELECT c.cname 课程, COUNT(sc.course_id) 被选修次数
FROM course c
LEFT JOIN score sc
ON sc.course_id = c.cid
GROUP BY c.cid;

SELECT s.sname 姓名, s.sid 学号
FROM student s
INNER JOIN score sc
ON s.sid = sc.student_id
GROUP BY s.sid 
HAVING COUNT(sc.course_id) = 1;

SELECT DISTINCT num
FROM score
ORDER BY num DESC;

SELECT s.sname 姓名, AVG(sc.num) 平均成绩
FROM student s
LEFT JOIN score sc
ON sc.student_id = s.sid 
GROUP BY s.sid 
HAVING AVG(sc.num) > 85;

SELECT s.sname 姓名, sc.num 生物成绩
FROM course c
LEFT JOIN score sc
ON sc.course_id = c.cid
LEFT JOIN student s
ON s.sid = sc.student_id 
WHERE c.cname = "生物"
AND sc.num < 60;


SELECT s.sname 姓名, AVG(sc.num) 李平老师课程平均分
FROM teacher t
LEFT JOIN course c
ON c.teacher_id = t.tid
INNER JOIN score sc
ON sc.course_id = c.cid
LEFT JOIN student s
ON s.sid = sc.student_id
WHERE t.tname = "李平老师"
GROUP BY sc.student_id 
HAVING AVG(sc.num) = (SELECT AVG(sc.num)
    FROM teacher t
    LEFT JOIN course c
    ON c.teacher_id = t.tid
    INNER JOIN score sc
    ON sc.course_id = c.cid
    WHERE t.tname = "李平老师"
    GROUP BY sc.student_id
    ORDER BY AVG(sc.num) DESC 
    LIMIT 1);

SELECT c.cname 课程, top.t1 第一名成绩, top.t2 第二名成绩, GROUP_CONCAT(s.sname) 前两名
FROM (SELECT f1.course_id, f1.mnum t1, MAX(sc.num) t2
    FROM (SELECT course_id, MAX(num) mnum
    FROM score 
    GROUP BY course_id) f1
    LEFT JOIN score sc
    ON sc.course_id = f1.course_id
    WHERE sc.num != f1.mnum
    GROUP BY sc.course_id) top
INNER JOIN score sc2
ON sc2.course_id = top.course_id
LEFT JOIN student s
ON sc2.student_id = s.sid
LEFT JOIN course c
ON c.cid = sc2.course_id
WHERE sc2.num = top.t1 OR sc2.num = top.t2
GROUP BY top.course_id;

SELECT s.sname, sc1.course_id, sc2.course_id, sc1.num
FROM score sc1
LEFT JOIN student s
ON s.sid = sc1.student_id
LEFT JOIN score sc2
ON sc1.num = sc2.num
AND s.sid = sc2.student_id
WHERE sc1.course_id != sc2.course_id
PS: 有点问题，两条记录重复了


SELECT s.sname, GROUP_CONCAT(c2.cname)
FROM student s
LEFT JOIN score sc2
ON s.sid = sc2.student_id
LEFT JOIN course c2
ON c2.cid = sc2.course_id
WHERE NOT EXISTS (SELECT c.cid
          FROM teacher t
          LEFT JOIN course c
          ON t.tid = c.teacher_id
          LEFT JOIN score sc
          ON sc.course_id = c.cid
          WHERE t.tname = "李平老师"
          AND s.sid = sc.student_id)
GROUP BY s.sid;

SELECT s.sid 学号, s.sname 姓名
FROM score sc
LEFT JOIN student s
ON s.sid = sc.student_id
WHERE EXISTS (SELECT course_id
FROM score 
WHERE student_id = 1
AND course_id = sc.course_id
AND student_id != sc.student_id)
GROUP BY sc.student_id;

SELECT sc.course_id, MAX(sc.num), GROUP_CONCAT(s.sname)
FROM score sc 
INNER JOIN course c2
ON c2.cid = sc.course_id
INNER JOIN
(SELECT t.tid
FROM course c
LEFT JOIN teacher t
ON t.tid = c.teacher_id
GROUP BY t.tid 
HAVING COUNT(c.cid) = (SELECT COUNT(cid)
FROM course 
GROUP BY teacher_id
ORDER BY COUNT(cid) DESC
LIMIT 1)) t2 
ON c2.teacher_id = t2.tid
INNER JOIN student s
ON s.sid = sc.student_id
GROUP BY sc.course_id;

最后编辑于：2021.02.09 17:32:49

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 160,706评论 4赞 366
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 68,002评论 1赞 301
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 110,462评论 0赞 250
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,375评论 0赞 216
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,763评论 3赞 294
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,849评论 1赞 224
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 32,033评论 2赞 317
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,768评论 0赞 204
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,490评论 1赞 246
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,734评论 2赞 253
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,204评论 1赞 264
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,566评论 3赞 260
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,227评论 3赞 241
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,137评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,934评论 0赞 201
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,926评论 2赞 283
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,774评论 2赞 274

MySQL查询练习题

说明

练习题

参考答案

推荐阅读更多精彩内容