MYSQL回顾（多表查询相关）

前言

简单的数据我们可以直接从一个表中获取，但在真实的项目中查询符合条件的数据通常需要牵扯到多张表，这就不得不使用多表查询。多表查询分为多表连接查询、符合条件链接查询、子查询。多表连接查询包括内连接、外连接、全连接。符合条件连接查询本质上是多表连接查询+过滤条件。子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果作为外层查询语句的数据源。

准备

# 建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

# 插入数据
insert into department values
(1,'技术'),
(2,'财务'),
(3,'法律’);

# 此处省略员工表数据...

多表连接查询

语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

交叉连接

在介绍多表查询的时候，有必要先介绍下交叉连接，如下select * from employee, department;查询语句就是交叉连接查询，可以看出，同一个数据在在employee表和department表交叉连接之后产生了重复记录，其重复个数取决于department表的记录个数。所以最后交叉连接之后的记录个数是：count(employee) * count(department)，即笛卡尔积。通常情况下，笛卡尔积的结果在工作中无实际意义，我们需要在笛卡尔积的基础上进行筛选，找到employee.dep_id = department.id的那条记录。

mysql> select * from employee, department;

+----+-------+------+--------+----------+-----------+--------+------+--------+
| id | name  | age  | sex    | position | salary    | dep_id | id   | name   |
+----+-------+------+--------+----------+-----------+--------+------+--------+
|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    1 | 技术   |
|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    2 | 财务   |
|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    1 | 技术   |
|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    2 | 财务   |
|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    1 | 技术   |
|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    2 | 财务   |
|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    3 | 法律   |
|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    1 | 技术   |
|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    2 | 财务   |
|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    3 | 法律   |
|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    1 | 技术   |
|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    2 | 财务   |
|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    3 | 法律   |
|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |    1 | 技术   |
|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |    2 | 财务   |
|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |    3 | 法律   |
|  7 | emmy  |   27 | female | RD       |      9999 |      1 |    1 | 技术   |
|  7 | emmy  |   27 | female | RD       |      9999 |      1 |    2 | 财务   |
|  7 | emmy  |   27 | female | RD       |      9999 |      1 |    3 | 法律   |
|  8 | emmy  |   23 | female | finance  |      5000 |      2 |    1 | 技术   |
|  8 | emmy  |   23 | female | finance  |      5000 |      2 |    2 | 财务   |
|  8 | emmy  |   23 | female | finance  |      5000 |      2 |    3 | 法律   |
|  9 | lucy  |   45 | female | finance  |     10000 |      2 |    1 | 技术   |
|  9 | lucy  |   45 | female | finance  |     10000 |      2 |    2 | 财务   |
|  9 | lucy  |   45 | female | finance  |     10000 |      2 |    3 | 法律   |
| 10 | emmi  |   20 | female | finance  |     20000 |      2 |    1 | 技术   |
| 10 | emmi  |   20 | female | finance  |     20000 |      2 |    2 | 财务   |
| 10 | emmi  |   20 | female | finance  |     20000 |      2 |    3 | 法律   |
| 11 | james |   18 | male   | NULL     |      3000 |   NULL |    1 | 技术   |
| 11 | james |   18 | male   | NULL     |      3000 |   NULL |    2 | 财务   |
| 11 | james |   18 | male   | NULL     |      3000 |   NULL |    3 | 法律   |
+----+-------+------+--------+----------+-----------+--------+------+--------+
33 rows in set (0.00 sec)

内连接

内连接只会连接两张表匹配的行，即取交集。找两张表公共部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果


mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;

+----+-------+------+--------+--------+

| id | name  | age  | sex    | name   |

+----+-------+------+--------+--------+

|  1 | jack  |   20 | male   | 法律   |

|  2 | mark  |   22 | male   | 法律   |

|  3 | hank  |   25 | male   | 法律   |

|  4 | nick  |   39 | male   | 法律   |

|  5 | jenny |   26 | female | 法律   |

|  6 | tony  |   35 | male   | 技术   |

|  7 | emmy  |   27 | female | 技术   |

|  8 | emmy  |   23 | female | 财务   |

|  9 | lucy  |   45 | female | 财务   |

| 10 | emmi  |   20 | female | 财务   |

+----+-------+------+--------+--------+

10 rows in set (0.00 sec)

上述内连接查询语句等同于：

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name 
from employee,department 
where employee.dep_id=department.id;

外连接

外连接分为左连接、右连接、全外连接

左连接

左连接在内连接的基础上优先显示左表全部记录。即左连接=内连接+左表未符合条件的记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+-------+-------------+
| id | name  | depart_name |
+----+-------+-------------+
|  6 | tony  | 技术        |
|  7 | emmy  | 技术        |
|  8 | emmy  | 财务        |
|  9 | lucy  | 财务        |
| 10 | emmi  | 财务        |
|  1 | jack  | 法律        |
|  2 | mark  | 法律        |
|  3 | hank  | 法律        |
|  4 | nick  | 法律        |
|  5 | jenny | 法律        |
| 11 | james | NULL        |
+----+-------+-------------+
11 rows in set (0.00 sec)

右连接

又连接在内连接的基础上优先显示右表的内容。即右连接==内连接+右表未符合条件的记录

#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-------+-------------+
| id   | name  | depart_name |
+------+-------+-------------+
|    1 | jack  | 法律        |
|    2 | mark  | 法律        |
|    3 | hank  | 法律        |
|    4 | nick  | 法律        |
|    5 | jenny | 法律        |
|    6 | tony  | 技术        |
|    7 | emmy  | 技术        |
|    8 | emmy  | 财务        |
|    9 | lucy  | 财务        |
|   10 | emmi  | 财务        |
+------+-------+-------------+
10 rows in set (0.00 sec)

可以发现，左表(employee表)的第11条记录没有被查询出来

全外连接

全外连接会在内连接查询的基础上显示左表和右表的全部记录

mysql> select * from employee left join department on employee.dep_id = department.id
    -> union
    -> select * from employee right join department on employee.dep_id = department.id
    -> ;
+------+-------+------+--------+----------+-----------+--------+------+--------+
| id   | name  | age  | sex    | position | salary    | dep_id | id   | name   |
+------+-------+------+--------+----------+-----------+--------+------+--------+
|    6 | tony  |   35 | male   | RD       | 100000000 |      1 |    1 | 技术   |
|    7 | emmy  |   27 | female | RD       |      9999 |      1 |    1 | 技术   |
|    8 | emmy  |   23 | female | finance  |      5000 |      2 |    2 | 财务   |
|    9 | lucy  |   45 | female | finance  |     10000 |      2 |    2 | 财务   |
|   10 | emmi  |   20 | female | finance  |     20000 |      2 |    2 | 财务   |
|    1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|    2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|    3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    3 | 法律   |
|    4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    3 | 法律   |
|    5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    3 | 法律   |
|   11 | james |   18 | male   | NULL     |      3000 |   NULL | NULL | NULL   |
| NULL | NULL  | NULL | NULL   | NULL     |      NULL |   NULL | NULL | 人力   |
+------+-------+------+--------+----------+-----------+--------+------+--------+
12 rows in set (0.00 sec)

符合条件链接查询

mysql> select employee.name, employee.age, department.name from employee inner join department
    -> on employee.dep_id=department.id
    -> where age > 30
    -> order by age asc;
+------+------+--------+
| name | age  | name   |
+------+------+--------+
| tony |   35 | 技术   |
| nick |   39 | 法律   |
| lucy |   45 | 财务   |
+------+------+--------+
3 rows in set (0.00 sec)

子查询

子查询是将一个查询语句的嵌套在另一个查询语句中
内层查询语句的查询结果作为外层查询语句的数据源
子查询中可以包含 IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字
注意被嵌套的查询语句需要用( )包裹

查询员工部门平均年龄大于30的部门信息

mysql> select * from department
    -> where id in
    -> (select dep_id from employee group by dep_id having avg(age) > 30);
+------+--------+
| id   | name   |
+------+--------+
|    1 | 技术   |
+------+--------+
1 row in set (0.00 sec)

查询技术部员工姓名

mysql> select name from employee
    -> where dep_id in
    -> (select id from department where name="技术");
+------+
| name |
+------+
| tony |
| emmy |
+------+
2 rows in set (0.00 sec)

查询无员工的部门名（子查询得到的是所有人的部门id，需要disctinct去除）

mysql> select name from department
    -> where id not in
    -> (select distinct dep_id from employee);

带比较运算符的子查询

查询大于所有人平均年龄的员工名和年龄

mysql> select * from employee where age > (select avg(age) from employee);
+----+------+------+--------+----------+-----------+--------+
| id | name | age  | sex    | position | salary    | dep_id |
+----+------+------+--------+----------+-----------+--------+
|  4 | nick |   39 | male   | lawyer   |   4438890 |      3 |
|  6 | tony |   35 | male   | RD       | 100000000 |      1 |
|  9 | lucy |   45 | female | finance  |     10000 |      2 |
+----+------+------+--------+----------+-----------+--------+
3 rows in set (0.00 sec)

不能这样：在前面没有group by的时后面不能使用分组函数
mysql> select * from employee where age > avg(age);
ERROR 1111 (HY000): Invalid use of group function

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);

MYSQL中可以把一个查询语句用括号括起来使用as起一个别名当做一个表使用
查询每个职位最新入职的员工

SELECT
    *
FROM
    emp AS t1
INNER JOIN (
    SELECT
        post,
        max(hire_date) max_date
    FROM
        emp
    GROUP BY
        post
) AS t2 ON t1.post = t2.post
WHERE
    t1.hire_date = t2.max_date;

查询语句关键字执行顺序

一个完整的mysql的查询语句如下：

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

其关键字执行顺序如下：

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

最后编辑于：2020.01.26 11:17:19

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,125评论 6赞 498
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,293评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,054评论 0赞 351
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,077评论 1赞 291
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,096评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,062评论 1赞 295
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,988评论 3赞 417
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,817评论 0赞 273
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,266评论 1赞 310
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,486评论 2赞 331
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,646评论 1赞 347
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,375评论 5赞 342
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,974评论 3赞 325
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,621评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,796评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,642评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,538评论 2赞 352