MySQL开发知识：MySQL数据库子查询 sub query

— 子查询得到的结果只有一个值
select * from 数据源 where 条件判断 =/<> (select 字段名 form 数据源 where 条件判断);

— 知道学生的id，查询所在班级名字
— 主查询：班级，子查询：班级id

mysql> select * from my_student;
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+

mysql> select * from my_class;
+—-+——–+
| id | name |
+—-+——–+
| 1 | 一班 |
| 3 | 三班 |
| 2 | 二班 |
+—-+——–+

select class_id from my_student where id = 1;
+———-+
| class_id |
+———-+
| 1 |
+———-+

select * from my_class where id = (
select class_id from my_student where id = 1
);
+—-+——–+
| id | name |
+—-+——–+
| 1 | 一班 |
+—-+——–+

— 获取有学生的班级名字
— 1、找到学生表中的所有班级id
— 2、找出班级表中对应的名字

select distinct(class_id) from my_student;
+———-+
| class_id |
+———-+
| 1 |
| 2 |
+———-+

select name from my_class where id in (
select distinct(class_id) from my_student
);
+——–+
| name |
+——–+
| 一班 |
| 二班 |
+——–+

— 错误示例
select * from my_student having age = max(age) and class_id = max(class_id);
— 1、having在group by之后，代表group by执行了一次，聚合函数使用
— 2、group by 一旦执行，结果就是只返回一行记录，第一行

select max(age), max(class_id) from my_student;
+———-+—————+
| max(age) | max(class_id) |
+———-+—————+
| 22 | 2 |
+———-+—————+

select * from my_student where (age, class_id) = (
select max(age), max(class_id) from my_student
);
Empty set (0.01 sec)

select * from my_student where (age, class_id) = (
select max(age), min(class_id) from my_student
);
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 5 | 关羽 | 1 | 22 | 2 |
+—-+——–+———-+——+——–+

select 字段表 from (表子查询) as 别名 [where] [group by] [having] [order by] [limit]

— 错误示例
select * from my_student group by class_id having age = max(age);
将每个班年龄最大的学生排在最前面 order by
针对结果进行group by 保留每组第一条数据
—
select * from (
select * from my_student order by age desc
) as t group by t.class_id;
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 3 | 王五 | 2 | 20 | 2 |
+—-+——–+———-+——+——–+

where exists (查询语句)
— 永远为真
where 1;

— 查询有学生的所有班级
select * from my_class as c where exists (
select id from my_student as s where s.class_id = c.id
);
+—-+——–+
| id | name |
+—-+——–+
| 1 | 一班 |
| 2 | 二班 |
+—-+——–+

mysql> select * from my_student;
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+

mysql> select id from my_class;
+—-+
| id |
+—-+
| 1 |
| 3 |
| 2 |
+—-+

select * from my_student where class_id in (select id from my_class);
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+

— 查询结果中有任意一个匹配即可，等价于in
主查询 where 条件 any (列子查询)

select * from my_student where class_id = any (select id from my_class);
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+

— 不等于任意一个
主查询 where 条件 any <> (列子查询)
select * from my_student where class_id <> any (select id from my_class);
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+

— 等于其中所有
=all(列子查询)
select * from my_student where class_id = all (select id from my_class);
Empty set (0.00 sec)
select * from my_class where id = all (select class_id from my_student);
Empty set (0.00 sec)
— 不等于其中所有
<>all(列子查询)
select * from my_student where class_id <> all (select id from my_class);
Empty set (0.00 sec)
select * from my_class where id <> all (select class_id from my_student);
+—-+——–+
| id | name |
+—-+——–+
| 3 | 三班 |
+—-+——–+

mysql> select * from my_student;
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | NULL | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+

mysql> select * from my_student where class_id = any (select id from my_class);
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+

mysql> select * from my_student where class_id <> any (select id from my_class);
+—-+——–+———-+——+——–+
| id | name | class_id | age | gender |
+—-+——–+———-+——+——–+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 6 | 曹操 | 1 | 20 | NULL |
+—-+——–+———-+——+——–+