子查询
当一个查询是另一个查询的条件时，称之为子查询。
- 子查询必须用括号括起来。
由比较运算符引发的子查询：
先新建一个数据表
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 |
+----+--------+----------+------------+-------+
-- 🆗
网友评论