MySQL数据库查询练习------使用的是MySQL5.7.31
一、数据库数据准备
课程表
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course` (
`Cid` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`Cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`Tid` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
INSERT INTO `Course` VALUES ('01', '语文', '02');
INSERT INTO `Course` VALUES ('02', '数学', '01');
INSERT INTO `Course` VALUES ('03', '英语', '03');
成绩表
DROP TABLE IF EXISTS `SC`;
CREATE TABLE `SC` (
`Sid` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`Cid` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
INSERT INTO `SC` VALUES ('01', '01', 80.0);
INSERT INTO `SC` VALUES ('01', '02', 90.0);
INSERT INTO `SC` VALUES ('01', '03', 99.0);
INSERT INTO `SC` VALUES ('02', '01', 70.0);
INSERT INTO `SC` VALUES ('02', '02', 60.0);
INSERT INTO `SC` VALUES ('02', '03', 80.0);
INSERT INTO `SC` VALUES ('03', '01', 80.0);
INSERT INTO `SC` VALUES ('03', '02', 80.0);
INSERT INTO `SC` VALUES ('03', '03', 80.0);
INSERT INTO `SC` VALUES ('04', '01', 50.0);
INSERT INTO `SC` VALUES ('04', '02', 30.0);
INSERT INTO `SC` VALUES ('04', '03', 20.0);
INSERT INTO `SC` VALUES ('05', '01', 76.0);
INSERT INTO `SC` VALUES ('05', '02', 87.0);
INSERT INTO `SC` VALUES ('06', '01', 31.0);
INSERT INTO `SC` VALUES ('06', '03', 34.0);
INSERT INTO `SC` VALUES ('07', '02', 89.0);
INSERT INTO `SC` VALUES ('07', '03', 98.0);
学生表
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student` (
`Sid` varchar(6) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`Sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`Sage` datetime(0) NULL DEFAULT NULL,
`Ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
INSERT INTO `Student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `Student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `Student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `Student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `Student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');
教师表
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher` (
`Tid` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
INSERT INTO `Teacher` VALUES ('01', '张三');
INSERT INTO `Teacher` VALUES ('02', '李四');
INSERT INTO `Teacher` VALUES ('03', '王五');
二、试题
1、查询" 01 “课程比” 02 "课程成绩高的学生的信息、课程分数
查询课程01的学生ID和对应的分数
mysql> SELECT sid,score FROM SC WHERE cid=01;
+------+-------+
| sid | score |
+------+-------+
| 01 | 80.0 |
| 02 | 70.0 |
| 03 | 80.0 |
| 04 | 50.0 |
| 05 | 76.0 |
| 06 | 31.0 |
+------+-------+
6 rows in set (0.00 sec)
查询课程02的学生ID和对应的分数
mysql> SELECT sid,score FROM SC WHERE cid=02;
+------+-------+
| sid | score |
+------+-------+
| 01 | 90.0 |
| 02 | 60.0 |
| 03 | 80.0 |
| 04 | 30.0 |
| 05 | 87.0 |
| 07 | 89.0 |
+------+-------+
6 rows in set (0.00 sec)
以上的两个结果集连接，连接条件a.sid = b.sid和a.score > b.score，查出学生ID、01课程分数、02课程分数
mysql> SELECT
a.sid AS sid,
a.score AS score_01,
b.score AS score_02
FROM
( SELECT sid, score FROM SC WHERE cid = 01 ) AS a,
( SELECT sid, score FROM SC WHERE cid = 02 ) AS b
WHERE
a.sid = b.sid
AND a.score > b.score;
+------+----------+----------+
| sid | score_01 | score_02 |
+------+----------+----------+
| 02 | 70.0 | 60.0 |
| 04 | 50.0 | 30.0 |
+------+----------+----------+
2 rows in set (0.00 sec)
以上一个结果集连接学生表，连接条件s.sid = a.sid，查出学生基本信息、01课程分数、02课程分数
mysql> SELECT
s.*,
a.score AS score_01,
b.score AS score_02
FROM
Student AS s,
( SELECT sid,score FROM SC WHERE cid = 01 ) AS a,
( SELECT sid,score FROM SC WHERE cid = 02 ) AS b
WHERE
a.sid = b.sid
AND a.score > b.score
AND s.sid = a.sid;
+------+--------+---------------------+------+----------+----------+
| Sid | Sname | Sage | Ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 |
+------+--------+---------------------+------+----------+----------+
2 rows in set (0.00 sec)
2、查询平均成绩大于等于60分的同学的学生编号、学生姓名、平均成绩
连接学生表和成绩表，查询学生成绩
mysql> SELECT
s.sid,
s.sname,
sc.score
FROM
Student AS s,
SC AS sc
WHERE
s.sid = sc.sid;
+------+--------+-------+
| sid | sname | score |
+------+--------+-------+
| 01 | 赵雷 | 80.0 |
| 01 | 赵雷 | 90.0 |
| 01 | 赵雷 | 99.0 |
| 02 | 钱电 | 70.0 |
| 02 | 钱电 | 60.0 |
| 02 | 钱电 | 80.0 |
| 03 | 孙风 | 80.0 |
| 03 | 孙风 | 80.0 |
| 03 | 孙风 | 80.0 |
| 04 | 李云 | 50.0 |
| 04 | 李云 | 30.0 |
| 04 | 李云 | 20.0 |
| 05 | 周梅 | 76.0 |
| 05 | 周梅 | 87.0 |
| 06 | 吴兰 | 31.0 |
| 06 | 吴兰 | 34.0 |
| 07 | 郑竹 | 89.0 |
| 07 | 郑竹 | 98.0 |
+------+--------+-------+
18 rows in set (0.00 sec)
按学生成绩分组，求出平均成绩，加上过滤条件HAVING avg_score > 60
mysql> SELECT
any_value(s.sid) AS sid,
any_value(s.sname) AS sname,
avg( score ) AS avg_score
FROM
Student AS s,
SC AS sc
WHERE
s.sid = sc.sid
GROUP BY
s.sid
HAVING
avg_score > 60;
+------+--------+-----------+
| sid | sname | avg_score |
+------+--------+-----------+
| 01 | 赵雷 | 89.66667 |
| 02 | 钱电 | 70.00000 |
| 03 | 孙风 | 80.00000 |
| 05 | 周梅 | 81.50000 |
| 07 | 郑竹 | 93.50000 |
+------+--------+-----------+
5 rows in set (0.00 sec)
3、查询在 SC 表存在成绩的学生信息
查询存在成绩的学生ID
mysql> SELECT sid FROM SC WHERE score IS NOT NULL;
+------+
| sid |
+------+
| 01 |
| 01 |
| 01 |
| 02 |
| 02 |
| 02 |
| 03 |
| 03 |
| 03 |
| 04 |
| 04 |
| 04 |
| 05 |
| 05 |
| 06 |
| 06 |
| 07 |
| 07 |
+------+
18 rows in set (0.00 sec)
以上结果集作为条件集合匹配学生表，查询学生基本信息
mysql> SELECT
*
FROM
Student
WHERE
sid IN ( SELECT sid FROM SC WHERE score IS NOT NULL );
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
查询学生的课程信息
mysql> SELECT
s.sid,
s.sname,
cid,
score
FROM
Student AS s
LEFT JOIN SC AS sc ON s.sid = sc.sid;
+------+--------+------+-------+
| sid | sname | cid | score |
+------+--------+------+-------+
| 01 | 赵雷 | 01 | 80.0 |
| 01 | 赵雷 | 02 | 90.0 |
| 01 | 赵雷 | 03 | 99.0 |
| 02 | 钱电 | 01 | 70.0 |
| 02 | 钱电 | 02 | 60.0 |
| 02 | 钱电 | 03 | 80.0 |
| 03 | 孙风 | 01 | 80.0 |
| 03 | 孙风 | 02 | 80.0 |
| 03 | 孙风 | 03 | 80.0 |
| 04 | 李云 | 01 | 50.0 |
| 04 | 李云 | 02 | 30.0 |
| 04 | 李云 | 03 | 20.0 |
| 05 | 周梅 | 01 | 76.0 |
| 05 | 周梅 | 02 | 87.0 |
| 06 | 吴兰 | 01 | 31.0 |
| 06 | 吴兰 | 03 | 34.0 |
| 07 | 郑竹 | 02 | 89.0 |
| 07 | 郑竹 | 03 | 98.0 |
| 08 | 王菊 | NULL | NULL |
+------+--------+------+-------+
19 rows in set (0.00 sec)
添加分组统计
mysql> SELECT
any_value ( s.sid ) AS sid,
any_value ( s.sname ) AS sname,
count( sc.cid ) AS 选课总数,
sum( sc.score ) AS 总成绩
FROM
Student AS s
LEFT JOIN SC AS sc ON s.sid = sc.sid
GROUP BY
s.sid;
+------+--------+--------------+-----------+
| sid | sname | 选课总数 | 总成绩 |
+------+--------+--------------+-----------+
| 01 | 赵雷 | 3 | 269.0 |
| 02 | 钱电 | 3 | 210.0 |
| 03 | 孙风 | 3 | 240.0 |
| 04 | 李云 | 3 | 100.0 |
| 05 | 周梅 | 2 | 163.0 |
| 06 | 吴兰 | 2 | 65.0 |
| 07 | 郑竹 | 2 | 187.0 |
| 08 | 王菊 | 0 | NULL |
+------+--------+--------------+-----------+
8 rows in set (0.00 sec)
5、查学生信息、总成绩、选课总数、各科成绩
mysql> SELECT
any_value ( s.sid ) AS sid,
any_value ( s.sname ) AS sname,
count( * ) AS 选课总数,
sum( score ) AS 总成绩,
sum( CASE WHEN cid = 01 THEN score ELSE NULL END ) AS score_01,
sum( CASE WHEN cid = 02 THEN score ELSE NULL END ) AS score_02,
sum( CASE WHEN cid = 03 THEN score ELSE NULL END ) AS score_03
FROM
Student AS s,
SC AS sc
WHERE
s.sid = sc.sid
GROUP BY
s.sid;
+------+--------+--------------+-----------+----------+----------+----------+
| sid | sname | 选课总数 | 总成绩 | score_01 | score_02 | score_03 |
+------+--------+--------------+-----------+----------+----------+----------+
| 01 | 赵雷 | 3 | 269.0 | 80.0 | 90.0 | 99.0 |
| 02 | 钱电 | 3 | 210.0 | 70.0 | 60.0 | 80.0 |
| 03 | 孙风 | 3 | 240.0 | 80.0 | 80.0 | 80.0 |
| 04 | 李云 | 3 | 100.0 | 50.0 | 30.0 | 20.0 |
| 05 | 周梅 | 2 | 163.0 | 76.0 | 87.0 | NULL |
| 06 | 吴兰 | 2 | 65.0 | 31.0 | NULL | 34.0 |
| 07 | 郑竹 | 2 | 187.0 | NULL | 89.0 | 98.0 |
+------+--------+--------------+-----------+----------+----------+----------+
7 rows in set (0.00 sec)
版权声明：本文为sanjun333原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。