MYSQL45道练习题---持续更新中

  • 开发
  • 12

来源： Mysql_45道练习题 - 简书

共四张表：

①、course表： CId：课程id        Cname：课程名称        TId：老师id

②、student学生表：        SId：学生id        Sname：学生名称        Sage：年龄        Ssex：性别

③、teacher教师表：        TId：教师id        Tname：教师名称

④、sc学生-课程表：        SId:学生Id        CId：课程id        score：成绩

导入sql文件：


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
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_general_ci 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;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
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;

-- ----------------------------
-- Records of sc
-- ----------------------------
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);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SId` varchar(10) 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;

-- ----------------------------
-- Records of student
-- ----------------------------
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 ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES ('11', '李四', '2017-12-30 00:00:00', '女');
INSERT INTO `student` VALUES ('12', '赵六', '2017-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('13', '孙七', '2018-01-01 00:00:00', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
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;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;

题目1：

        查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数：

select * 
from sc a 
left join student d
    on a.sid=d.sid 
inner join sc b 
    on a.sid=b.sid and a.cid='01' and b.cid='02'
where a.score>b.score;

我自己写的、太冗余：

1、
   先找出cid为01课程的
 select * from sc where cid = 01
2、
    再找出cid课程为02课程的
select * from sc where cid = 02
3、表连接

sleect sid,score from 
( select * from sc where cid = 01) m1
join
(select * from sc where cid = 02) m2
on m1.sid = m2.sid and m1.score>m2.score

改进后：

select * from
student s join sc as m1
on s.sid = m1.sid
join sc as m2
on m1.sid = m2.sid and m1.cid = '01' and m2.cid = '02'
where m1.score > m2.score ;

题目2：

        查询同时选了" 01 "课程和" 02 "课程的学生

select * 
from sc a 
inner join sc b 
    on a.sid=b.sid and a.cid='01' and b.cid='02';
select 
	sid
from
	sc
where
	sid in (select sid from sc where cid = '01')
	and
	sid in (select sid from sc where cid = '02');
select 
	m1.sid
from 
	(select sid from sc where cid = '01') as m1
join
	(select sid from sc where cid = '02') as m2
on 
	m1.sid = m2.sid;
mysql> select
    -> m1.sid
    -> from
    -> (select sid from sc where cid = '01') as m1
    -> join
    -> (select sid from sc where cid = '02') as m2
    -> on
    -> m1.sid = m2.sid;
+------+
| sid  |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
+------+
5 rows in set (0.00 sec)

mysql> select
    -> sid
    -> from
    -> sc
    -> where
    -> sid in (select sid from sc where cid = '01')
    -> and
    -> sid in (select sid from sc where cid = '02');
+------+
| sid  |
+------+
| 01   |
| 01   |
| 01   |
| 02   |
| 02   |
| 02   |
| 03   |
| 03   |
| 03   |
| 04   |
| 04   |
| 04   |
| 05   |
| 05   |
+------+
14 rows in set (0.00 sec)

mysql> select sid from sc where cid = '01';
+------+
| sid  |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 06   |
+------+
6 rows in set (0.00 sec)

mysql> select sid from sc where cid = '02';
+------+
| sid  |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 07   |
+------+
6 rows in set (0.00 sec)

题目3：

        查询选了" 01 "课程但可能没有选择" 02 "课程的学生(不存在时显示为 null )

错误写法：

mysql> select *
    -> from sc a
    -> left join sc b
    ->     on a.sid=b.sid  and  a.cid='01' and b.cid='02'
    -> ;
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 01   | 02   |  90.0 | NULL | NULL |  NULL |
| 01   | 03   |  99.0 | NULL | NULL |  NULL |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 02   | 02   |  60.0 | NULL | NULL |  NULL |
| 02   | 03   |  80.0 | NULL | NULL |  NULL |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 03   | 02   |  80.0 | NULL | NULL |  NULL |
| 03   | 03   |  80.0 | NULL | NULL |  NULL |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 04   | 02   |  30.0 | NULL | NULL |  NULL |
| 04   | 03   |  20.0 | NULL | NULL |  NULL |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 05   | 02   |  87.0 | NULL | NULL |  NULL |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
| 06   | 03   |  34.0 | NULL | NULL |  NULL |
| 07   | 02   |  89.0 | NULL | NULL |  NULL |
| 07   | 03   |  98.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
18 rows in set (0.00 sec)

mysql> select *
    -> from sc a
    -> left join sc b
    ->     on a.sid=b.sid  and b.cid='02'
    -> where a.cid='01';
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
6 rows in set (0.00 sec)

mysql> select *
    -> from sc a
    -> left join sc b
    ->     on a.sid=b.sid  and b.cid='02';
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 01   | 02   |  90.0 | 01   | 02   |  90.0 |
| 01   | 03   |  99.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 02   | 02   |  60.0 | 02   | 02   |  60.0 |
| 02   | 03   |  80.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 03   | 02   |  80.0 | 03   | 02   |  80.0 |
| 03   | 03   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 04   | 02   |  30.0 | 04   | 02   |  30.0 |
| 04   | 03   |  20.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 05   | 02   |  87.0 | 05   | 02   |  87.0 |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
| 06   | 03   |  34.0 | NULL | NULL |  NULL |
| 07   | 02   |  89.0 | 07   | 02   |  89.0 |
| 07   | 03   |  98.0 | 07   | 02   |  89.0 |
+------+------+-------+------+------+-------+
18 rows in set (0.00 sec)

mysql> select *
    -> from sc a
    -> left join sc b
    ->     on a.sid=b.sid  and  a.cid='01' and b.cid='02'
    -> where a.cid='01';
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
6 rows in set (0.00 sec)

mysql> select *
    -> from sc a
    -> left join sc b
    ->     on a.sid=b.sid  and  a.cid='01'  ;
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 03   |  99.0 |
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 01   | 01   |  80.0 | 01   | 01   |  80.0 |
| 01   | 02   |  90.0 | NULL | NULL |  NULL |
| 01   | 03   |  99.0 | NULL | NULL |  NULL |
| 02   | 01   |  70.0 | 02   | 03   |  80.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 02   | 01   |  70.0 | 02   | 01   |  70.0 |
| 02   | 02   |  60.0 | NULL | NULL |  NULL |
| 02   | 03   |  80.0 | NULL | NULL |  NULL |
| 03   | 01   |  80.0 | 03   | 03   |  80.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 03   | 01   |  80.0 | 03   | 01   |  80.0 |
| 03   | 02   |  80.0 | NULL | NULL |  NULL |
| 03   | 03   |  80.0 | NULL | NULL |  NULL |
| 04   | 01   |  50.0 | 04   | 03   |  20.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 04   | 01   |  50.0 | 04   | 01   |  50.0 |
| 04   | 02   |  30.0 | NULL | NULL |  NULL |
| 04   | 03   |  20.0 | NULL | NULL |  NULL |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 05   | 01   |  76.0 | 05   | 01   |  76.0 |
| 05   | 02   |  87.0 | NULL | NULL |  NULL |
| 06   | 01   |  31.0 | 06   | 03   |  34.0 |
| 06   | 01   |  31.0 | 06   | 01   |  31.0 |
| 06   | 03   |  34.0 | NULL | NULL |  NULL |
| 07   | 02   |  89.0 | NULL | NULL |  NULL |
| 07   | 03   |  98.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
28 rows in set (0.00 sec)

mysql> select *
    -> from sc a
    ->  join sc b
    ->     on a.sid=b.sid  and  a.cid='01' and b.cid='02'
    -> where a.cid='01';
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
+------+------+-------+------+------+-------+
5 rows in set (0.00 sec)

mysql> select *
    -> from sc a
    ->  join sc b
    ->     on a.sid=b.sid  and  a.cid='01' and b.cid='02';
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
+------+------+-------+------+------+-------+
5 rows in set (0.00 sec)

mysql>

阅读全部

相关推荐

  4. SQL持续更新中

    2024-04-23 15:00:03       28 阅读

  5. 笔试刷题（持续更新）| Leetcode 45，1190

    2024-04-23 15:00:03       36 阅读

  6. 【MySQL】MySQL运维SQL（持续更新。。。）

    2024-04-23 15:00:03       35 阅读

  8. Git命令(持续更新中...)

    2024-04-23 15:00:03       22 阅读

  9. （持续更新中）DRF相关

    2024-04-23 15:00:03       21 阅读

  10. VScode使用持续更新中。。。

    2024-04-23 15:00:03       17 阅读

最近更新

  4. TCP协议是安全的吗?

    2024-04-23 15:00:03       19 阅读

  12. 阿里云服务器执行yum，一直下载docker-ce-stable失败

    2024-04-23 15:00:03       19 阅读

  13. 【Python教程】压缩PDF文件大小

    2024-04-23 15:00:03       20 阅读

  18. 通过文章id递归查询所有评论（xml）

    2024-04-23 15:00:03       20 阅读

热门阅读

  1. Rust 语言中的跨平台 GUI 库

    2024-04-23 15:00:03       11 阅读

  6. docker快速使用简介

    2024-04-23 15:00:03       12 阅读

  8. docker 安装 jenkins

    2024-04-23 15:00:03       13 阅读

  12. C# 调整图像的亮度简单示例

    2024-04-23 15:00:03       13 阅读

  14. HX519 是一款防倒流数据线芯片

    2024-04-23 15:00:03       13 阅读

  17. Python机器学习库中Scikit-learn和TensorFlow如何选择？

    2024-04-23 15:00:03       13 阅读

  19. 数据结构：哈密顿回路基础

    2024-04-23 15:00:03       14 阅读

  20. 架构权衡评估方法（ATAM）：一种用于软件架构评估的方法，全称为Architecture Tradeoff Analysis Method

    2024-04-23 15:00:03       11 阅读

  22. 用户权限—— u+s\g+s\o+t三个特殊权限说明

    2024-04-23 15:00:03       13 阅读

  29. [Unity]动态修改URP资源的相关参数

    2024-04-23 15:00:03       12 阅读