MySQL-子查询及连接

作者: abona | 来源:发表于2019-07-11 16:32 被阅读0次

子查询

当一个查询是另一个查询的条件时，称之为子查询。

子查询必须用括号括起来。
由比较运算符引发的子查询：
先新建一个数据表

CREATE TABLE students (
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    class_name VARCHAR(20) NOT NULL,
    teacher_name VARCHAR(20) NOT NULL,
    score SMALLINT UNSIGNED
);
-- 插入几条数据
INSERT INTO students VALUES 
(NULL,'张山','网络18-1班','陈老师',78), 
(NULL,'王五','微机18-1班','张老师',85),
(NULL,'赵六','设计18-1班','林老师',66),
(NULL,'陈七','网络18-1班','陈老师',90),
(NULL,'阿狗','电子18-1班','王老师',88),
(NULL,'狗腿','电子18-1班','王老师',95),
(NULL,'陈七','网络18-1班','陈老师',90),
(NULL,'小咪','设计18-1班','林老师',98),
(NULL,'富贵','微机18-1班','张老师',83),
(NULL,'王五','微机18-1班','张老师',85);

现在查出他们的平均成绩

SELECT ROUND(AVG(score),1) FROM students;
+---------------------+
| ROUND(AVG(score),1) |
+---------------------+
|                85.8 |
+---------------------+

查出比平均成绩高的学生

SELECT * FROM students WHERE score > (SELECT ROUND(AVG(score),1) FROM students);
+----+--------+---------------+--------------+-------+
| id | name   | class_name    | teacher_name | score |
+----+--------+---------------+--------------+-------+
|  4 | 陈七   | 网络18-1班    | 陈老师       |    90 |
|  5 | 阿狗   | 电子18-1班    | 王老师       |    88 |
|  6 | 狗腿   | 电子18-1班    | 王老师       |    95 |
|  7 | 陈七   | 网络18-1班    | 陈老师       |    90 |
|  8 | 小咪   | 设计18-1班    | 林老师       |    98 |
+----+--------+---------------+--------------+-------+

ANY SOME ALL关键字

对子查询中返回的多行结果进行处理

···	ANY	SOME	ALL
>、>=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			最大值

使用：查询

-- 先查询下林老师学生的分数
SELECT score FROM students WHERE teacher_name = '林老师';
+-------+
| score |
+-------+
|    66 |
|    98 |
+-------+
-- 找出比林老师学生分数高的学生
SELECT * FROM students 
WHERE score > (SELECT score FROM students WHERE teacher_name = '林老师');
-- 不使用关键字时会报错
-- ERROR 1242 (21000): Subquery returns more than 1 row
-- 返回结果超过一行，不知道该跟那个做对比了

-- 使用关键字, 图标中可以看到，使用 ‘>’ ANY会跟返回结果中值最小的对比 最小的分数为66
SELECT * FROM students 
WHERE score > ANY (SELECT score FROM students WHERE teacher_name = '林老师');
+----+--------+---------------+--------------+-------+
| id | name   | class_name    | teacher_name | score |
+----+--------+---------------+--------------+-------+
|  1 | 张山   | 网络18-1班    | 陈老师       |    78 |
|  2 | 王五   | 微机18-1班    | 张老师       |    85 |
|  4 | 陈七   | 网络18-1班    | 陈老师       |    90 |
|  5 | 阿狗   | 电子18-1班    | 王老师       |    88 |
|  6 | 狗腿   | 电子18-1班    | 王老师       |    95 |
|  7 | 陈七   | 网络18-1班    | 陈老师       |    90 |
|  8 | 小咪   | 设计18-1班    | 林老师       |    98 |
|  9 | 富贵   | 微机18-1班    | 张老师       |    83 |
| 10 | 王五   | 微机18-1班    | 张老师       |    85 |
+----+--------+---------------+--------------+-------+
-- 使用 ‘<’ ANY会跟最大的对比，最大分为98
SELECT * FROM students 
WHERE score < ANY (SELECT score FROM students WHERE teacher_name = '林老师');
+----+--------+---------------+--------------+-------+
| id | name   | class_name    | teacher_name | score |
+----+--------+---------------+--------------+-------+
|  1 | 张山   | 网络18-1班    | 陈老师       |    78 |
|  2 | 王五   | 微机18-1班    | 张老师       |    85 |
|  3 | 赵六   | 设计18-1班    | 林老师       |    66 |
|  4 | 陈七   | 网络18-1班    | 陈老师       |    90 |
|  5 | 阿狗   | 电子18-1班    | 王老师       |    88 |
|  6 | 狗腿   | 电子18-1班    | 王老师       |    95 |
|  7 | 陈七   | 网络18-1班    | 陈老师       |    90 |
|  9 | 富贵   | 微机18-1班    | 张老师       |    83 |
| 10 | 王五   | 微机18-1班    | 张老师       |    85 |
+----+--------+---------------+--------------+-------+

多表更新

-- 新建班级表
CREATE TABLE classes(
class_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(20) NOT NULL,
teacher_name VARCHAR(20) NOT NULL
);
/
-- 将学生表的班级及老师写入班级表
INSERT classes (class_name,teacher_name)
SELECT class_name,teacher_name 
FROM students GROUP BY class_name;
-- 查询结果
SELECT * FROM classes;
+----------+---------------+--------------+
| class_id | class_name    | teacher_name |
+----------+---------------+--------------+
|        1 | 微机18-1班    | 张老师       |
|        2 | 电子18-1班    | 王老师       |
|        3 | 网络18-1班    | 陈老师       |
|        4 | 设计18-1班    | 林老师       |
+----------+---------------+--------------+

-- 正文
-- 将学生表班级名更改成班级id
UPDATE students INNER JOIN (SELECT class_id,class_name FROM classes ) AS cl
ON students.class_name = cl.class_name SET students.class_name = cl.class_id; 
-- 查看结果
+----+--------+------------+--------------+-------+
| id | name   | class_name | teacher_name | score |
+----+--------+------------+--------------+-------+
|  1 | 张山   | 1          | 陈老师       |    78 |
|  2 | 王五   | 2          | 张老师       |    85 |
|  3 | 赵六   | 3          | 林老师       |    66 |
|  4 | 陈七   | 1          | 陈老师       |    90 |
|  5 | 阿狗   | 4          | 王老师       |    88 |
|  6 | 狗腿   | 4          | 王老师       |    95 |
|  7 | 陈七   | 1          | 陈老师       |    90 |
|  8 | 小咪   | 3          | 林老师       |    98 |
|  9 | 富贵   | 2          | 张老师       |    83 |
| 10 | 王五   | 2          | 张老师       |    85 |
+----+--------+------------+--------------+-------+
-- 另一种方法
-- 建表时直接写入
CREATE TABLE teachers (
teacher_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
teacher_name VARCHAR(20) NOT NULL
)
SELECT teacher_name  FROM students GROUP BY teacher_name;
-- 然后更新
UPDATE students INNER JOIN (SELECT * FROM  teachers) 
AS  t ON students.teacher_name = t.teacher_name 
SET students.teacher_name = t.teacher_id;
-- 最后更改下学生表字段名
ALTER TABLE students CHANGE class_name class_id SMALLINT 
UNSIGNED  NOT NULL;
ALTER TABLE students CHANGE teacher_name teacher_id SMALLINT UNSIGNED NOT NULL;

连接

内连接的连接查询结果集中仅包含满足条件的行，外连接(左、右)的连接查询结果集中既包含那些满足条件的行，还包含其中某个表的全部行

1、内连接

-- 写入两条数据
INSERT INTO students VALUES (NULL,'张伟','10','12',100),
(NULL,'刘六','12','10',100);
INSERT INTO classes VALUE (NULL,'机电18-1班','潘老师');
-- 连接查询
-- 显示班级
SELECT id,name,cl.class_name FROM students 
INNER JOIN classes AS cl ON students.class_id = cl.class_id;
+----+--------+---------------+
| id | name   | class_name    |
+----+--------+---------------+
|  1 | 张山   | 网络18-1班    |
|  2 | 王五   | 微机18-1班    |
|  3 | 赵六   | 设计18-1班    |
|  4 | 陈七   | 网络18-1班    |
|  5 | 阿狗   | 电子18-1班    |
|  6 | 狗腿   | 电子18-1班    |
|  7 | 陈七   | 网络18-1班    |
|  8 | 小咪   | 设计18-1班    |
|  9 | 富贵   | 微机18-1班    |
| 10 | 王五   | 微机18-1班    |
+----+--------+---------------+
-- 里面没有张伟跟刘六，是因为他们不符合条件 
-- 张伟和刘六的class_id 为10，12
+----------+---------------+--------------+
| class_id | class_name    | teacher_name |
+----------+---------------+--------------+
|        1 | 微机18-1班    | 张老师       |
|        2 | 电子18-1班    | 王老师       |
|        3 | 网络18-1班    | 陈老师       |
|        4 | 设计18-1班    | 林老师       |
|        5 | 机电18-1班    | 潘老师       |
+----------+---------------+--------------+
-- 表里是没有这两个class_id的，所以不会显示
-- 同理 没有学生的class_id为5，所以也不符合条件

2、左连接

-- 还是查询班级
SELECT id,name,cl.class_name FROM students 
LEFT JOIN classes AS cl ON students.class_id = cl.class_id;
+----+--------+---------------+
| id | name   | class_name    |
+----+--------+---------------+
|  2 | 王五   | 微机18-1班    |
|  9 | 富贵   | 微机18-1班    |
| 10 | 王五   | 微机18-1班    |
|  5 | 阿狗   | 电子18-1班    |
|  6 | 狗腿   | 电子18-1班    |
|  1 | 张山   | 网络18-1班    |
|  4 | 陈七   | 网络18-1班    |
|  7 | 陈七   | 网络18-1班    |
|  3 | 赵六   | 设计18-1班    |
|  8 | 小咪   | 设计18-1班    |
| 11 | 张伟   | NULL          |
| 12 | 刘六   | NULL          |
+----+--------+---------------+
-- 左连接说明JOIN左侧的所有记录均会被显示，无论其在右侧是否得到匹配
-- 刘六 张伟 没有班级，会显示为NULL

3、右连接

SELECT id,name,cl.class_name FROM students 
RIGHT JOIN classes AS cl ON students.class_id = cl.class_id;
+------+--------+---------------+
| id   | name   | class_name    |
+------+--------+---------------+
|    1 | 张山   | 网络18-1班    |
|    2 | 王五   | 微机18-1班    |
|    3 | 赵六   | 设计18-1班    |
|    4 | 陈七   | 网络18-1班    |
|    5 | 阿狗   | 电子18-1班    |
|    6 | 狗腿   | 电子18-1班    |
|    7 | 陈七   | 网络18-1班    |
|    8 | 小咪   | 设计18-1班    |
|    9 | 富贵   | 微机18-1班    |
|   10 | 王五   | 微机18-1班    |
| NULL | NULL   | 机电18-1班    |
+------+--------+---------------+

-- 右连接说明JOIN右侧的所有记录均会被显示，无论其在左侧是否得到匹配
-- 机电18-1班没有学生，会显示为NULL

无限级分类

-- 新建省份城市表
CREATE TABLE areas (
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
area_name VARCHAR(30) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL
);
-- 写入一些数据
INSERT INTO areas VALUES 
(NULL,'广东省',0),
(NULL,'江苏省',0),
(NULL,'山东省',0),
(NULL,'浙江省',0),
(NULL,'河南省',0),
(NULL,'广州市',1),
(NULL,'深圳市',1),
(NULL,'南京市',2),
(NULL,'济南市',3),
(NULL,'青岛市',3),
(NULL,'杭州市',4),
(NULL,'宁波市',4),
(NULL,'郑州市',5);

+----+-----------+-----------+
| id | area_name | parent_id |
+----+-----------+-----------+
|  1 | 广东省    |         0 |
|  2 | 江苏省    |         0 |
|  3 | 山东省    |         0 |
|  4 | 浙江省    |         0 |
|  5 | 河南省    |         0 |
|  6 | 广州市    |         1 |
|  7 | 深圳市    |         1 |
|  8 | 南京市    |         2 |
|  9 | 济南市    |         3 |
| 10 | 青岛市    |         3 |
| 11 | 杭州市    |         4 |
| 12 | 宁波市    |         4 |
| 13 | 郑州市    |         5 |
+----+-----------+-----------+
-- 查询城市所属省
SELECT a.id,a.area_name,p.area_name AS city 
FROM areas AS a LEFT JOIN (SELECT * FROM areas ) AS p ON a.id = p.parent_id;
+----+-----------+-----------+
| id | area_name | city      |
+----+-----------+-----------+
|  1 | 广东省    | 广州市    |
|  1 | 广东省    | 深圳市    |
|  2 | 江苏省    | 南京市    |
|  3 | 山东省    | 济南市    |
|  3 | 山东省    | 青岛市    |
|  4 | 浙江省    | 杭州市    |
|  4 | 浙江省    | 宁波市    |
|  5 | 河南省    | 郑州市    |
|  6 | 广州市    | NULL      |
|  7 | 深圳市    | NULL      |
|  8 | 南京市    | NULL      |
|  9 | 济南市    | NULL      |
| 10 | 青岛市    | NULL      |
| 11 | 杭州市    | NULL      |
| 12 | 宁波市    | NULL      |
| 13 | 郑州市    | NULL      |
+----+-----------+-----------+
-- 内连接
SELECT a.id,a.area_name,p.area_name AS city 
FROM areas AS a INNER JOIN (SELECT * FROM areas ) AS p ON a.id = p.parent_id;
+----+-----------+-----------+
| id | area_name | city      |
+----+-----------+-----------+
|  1 | 广东省    | 广州市    |
|  1 | 广东省    | 深圳市    |
|  2 | 江苏省    | 南京市    |
|  3 | 山东省    | 济南市    |
|  3 | 山东省    | 青岛市    |
|  4 | 浙江省    | 杭州市    |
|  4 | 浙江省    | 宁波市    |
|  5 | 河南省    | 郑州市    |
+----+-----------+-----------+

多表删除

-- 由于只有一张表，就用这一张演示，多表同理
-- 查看students
+----+--------+----------+------------+-------+
| id | name   | class_id | teacher_id | score |
+----+--------+----------+------------+-------+
|  1 | 张山   |        3 |          4 |    78 |
|  2 | 王五   |        1 |          1 |    85 |
|  3 | 赵六   |        4 |          2 |    66 |
|  4 | 陈七   |        3 |          4 |    90 |
|  5 | 阿狗   |        2 |          3 |    88 |
|  6 | 狗腿   |        2 |          3 |    95 |
|  7 | 陈七   |        3 |          4 |    90 |
|  8 | 小咪   |        4 |          2 |    98 |
|  9 | 富贵   |        1 |          1 |    83 |
| 10 | 王五   |        1 |          1 |    85 |
| 11 | 张伟   |       10 |         12 |   100 |
| 12 | 刘六   |       12 |         10 |   100 |
+----+--------+----------+------------+-------+
-- 会发现有几个重复的
-- 查看重复的用户
SELECT * FROM students GROUP BY name HAVING COUNT(name) > 1;
+----+--------+----------+------------+-------+
| id | name   | class_id | teacher_id | score |
+----+--------+----------+------------+-------+
|  2 | 王五   |        1 |          1 |    85 |
|  4 | 陈七   |        3 |          4 |    90 |
+----+--------+----------+------------+-------+
-- 接下来把存入时间晚的重复学生删除
DELETE t1 FROM students AS t1 
LEFT JOIN (SELECT * FROM students GROUP BY name HAVING COUNT(name)>1) AS t2 
ON t1.name = t2.name WHERE t1.id > t2.id;
+----+--------+----------+------------+-------+
| id | name   | class_id | teacher_id | score |
+----+--------+----------+------------+-------+
|  1 | 张山   |        3 |          4 |    78 |
|  2 | 王五   |        1 |          1 |    85 |
|  3 | 赵六   |        4 |          2 |    66 |
|  4 | 陈七   |        3 |          4 |    90 |
|  5 | 阿狗   |        2 |          3 |    88 |
|  6 | 狗腿   |        2 |          3 |    95 |
|  8 | 小咪   |        4 |          2 |    98 |
|  9 | 富贵   |        1 |          1 |    83 |
| 11 | 张伟   |       10 |         12 |   100 |
| 12 | 刘六   |       12 |         10 |   100 |
+----+--------+----------+------------+-------+
-- 🆗

MySQL-子查询及连接

子查询

ANY SOME ALL关键字

多表更新

连接

1、内连接

2、左连接

3、右连接

无限级分类

多表删除

为自己学习记录，错误之处请帮助指出，共同学习，谢谢

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读