小小程知识库
xxcheng
2023-07-21
目录

多表查询

数据表信息说明：

  • SQL 下载地址：点击下载 (opens new window)
  • employees 表 有107 条记录，包括一条 department_id 为 NULL
  • departments 表有 27 条记录
  • employees 表和 departments 有两个同名字段 department_id 和 manager_id

# 需求分析

员工信息在 A 表，包括部门编号，部门详细信息在 B 表，包括编号和地址，想要知道某个员工的姓名和部门地址需要先查询 A 表然后再通过获取的部门编号去 B 表查询部门地址。

# 初步探索

尝试在 FROM 中同时使用 employees 表和 departments 表直接查询

mysql> SELECT COUNT(*) count
    -> FROM employees,departments;
+-------+
| count |
+-------+
|  2889 |
+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

共获取到 2889 条记录，因为上面的查询中没有限制条件，将两张所有的记录进行了 交叉连接，产生了 笛卡尔积错误，返回的记录数 = 表 A 记录数 × 表 B 记录数

使用有效的约束条件可以避免 笛卡尔积错误

mysql> SELECT COUNT(*) count
    -> FROM employees e,departments d
    -> WHERE e.department_id=d.department_id;
+-------+
| count |
+-------+
|   106 |
+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

但是，这里统计出来的是 106 条数据，而 employees 表中是 107 条数据，因为 employees 表中有一条数据的 department_id 为 NULL，无法与 departments 表中任何一条数据匹配，所以只有 106 条数据，这里使用的多表查询是 SQL-92 标准的 内连接。

之前学习过 可以在 SELECT 语句中给字段名取别名，同样的也可以给表名取别名，上面示例中，分别给 employees 和 departments 取了别名，然后可以在 WHERE 语句中使用，以及后面学习的 JOIN...ON 语句，同时一旦给表名取了别名，就无法使用表名的名字了。

多表查询不仅仅局限连接两张表

SELECT *
FROM table_1,table_2,talbe_3
WHERE table_1.field_1=table_2.field_1 and table_2.field_1=table_3.field_1;
1
2
3

对 n 个表进行多表查询，至少需要 n-1 个条件。

# 不同角度对多表查询的分类

具体可以分为三大类：

  • 等值连接和非等值连接；
  • 自连接和非自连接；
  • 内连接 和 外连接；

# 等值连接和非等值连接

就是约束条件是否可以使用等号相等起来的，上面的 e.department_id=d.department_id 就是一个 等值连接，而比如 e.department_id IN(1,2,3) 是 非等值连接。非重点

# 自连接和非自连接

多表查询时，连接的其他表是自己本身的表还是非自身的表。上面的通过 A 表的部门编号再查询到 B 表中的部门地址是 非自连接，而比如查询自己的上级，可能就可能还是在本表查询，是 自连接。非重点

# 内连接和外连接

# 内连接

合并具有同一列两个以上的表，结构集中只包括满足所有条件要求的记录集合；

INNER_JOIN

这就是内连接，红色部分表示结果集。

# 外连接

合并具有同一列两个以上的表，结果集中包括不满足条件的记录；

上面 内连接 的示意图中，只要有结果集的记录是示意图红色之外的，就是 外连接。

根据返回的不同的结果集，还分为 左外连接、右外连接 和 满外连接，固定的是左边的表是原来的表，右边的表是其他被连接的表。

  • 左外连接是以左边的表为主表，右边的表为从表，结果是主表的数据，在从表没有匹配到的结果设为 NULL

    LEFT_JOIN

  • 右外连接是以右边的表为主表，左边的表为从表，结果是主表的数据，主表在从表没有匹配到的结果设为 NULL

    RIGHT_JOIN

  • 满外连接的结果是主从表匹配的结果 + 主表没有匹配到的结果 + 从表没有匹配到的结果

    FULL_OUTER_JOIN

上面这三种 外连接，内连接是它们的 子集。

# 不同 SQL 下的实现

SQL 标准主要分为 SQL92 和 SQL99

  • SQL92，又叫做 SQL-2，语法简单，但是语句长；
  • SQL99，又叫做 SQL-3，语法复杂，但是可读性强；

# SQL92

对于内连接，就是简单的在 FROM 语句选择多个表，然后 WHERE 语句中写全约束条件。就是上面一些示例中的使用这种实现。

对于外连接，使用 + 实现，但是MySQL 不支持这种标准的外连接写法，就不深入探究了。

# SQL99

使用 JOIN...ON... 关键字实现多表查询

SQL99 语法的 内连接、外连接 的关键字如下：

  • INNER JOIN...ON... 内连接

    INNER 关键字可以省略

    INNER JOIN ...ON...

  • LEFT OUTER JOIN...ON... 左外连接

    OUTER 关键字可以省略

    LEFT JOIN...ON...

  • RIGHT OUTER JOIN...ON... 右外连接

    OUTER 关键字可以省略

    RIGHT JOIN...ON...

  • FULL OUTER JOIN...ON... 满外连接

    OUTER 关键字可以省略

    FULL JOIN...ON...

    可惜的是，MySQL 不支持这种写法的满外连接，但是可以结合 UNION 关键字实现 满外连接

SQL99 语法的 内连接、外连接 具体实现在下面单独列出，并且再结合排除的形式组成七种连接。

# SQL99 JOIN...ON... 关键字的七种连接

需用到的数据表下载：点击下载 (opens new window)

七种连接示意图

Visual_SQL_JOINS_orig

# 前置知识：UNION 和 UNION ALL 关键字

使用这两个关键字可以将两个 SELECT 语句的结果集，合并为一个结果集。其中，两个 SELECT 语句的结果集的 列数 和 数据类型 必须相同。

这两个关键字之间的差别在合并后的结果集，UNION 关键字可以实现两个结果集的的去重，但是这样子会导致效率低，而 UNION ALL 关键字不会对去重，但是所需的资源比 UNION 少。

# 内连接

INNER_JOIN

mysql> SELECT table_a.`PK` "index",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> JOIN table_b b
    -> ON table_a.PK=b.PK;
+-------+------------+----------+
| index | one        | two      |
+-------+------------+----------+
|     1 | FOX        | TROT     |
|     2 | COP        | CAR      |
|     3 | TAXI       | CAB      |
|     6 | WASHINGTON | MONUMENT |
|     7 | DELL       | PC       |
+-------+------------+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 左外连接

LEFT_JOIN

mysql> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> LEFT JOIN table_b b
    -> ON table_a.PK=b.PK;
+-------+---------+------------+----------+
| index | index_2 | one        | two      |
+-------+---------+------------+----------+
|     1 |       1 | FOX        | TROT     |
|     2 |       2 | COP        | CAR      |
|     3 |       3 | TAXI       | CAB      |
|     4 |    NULL | LINCOLN    | NULL     |
|     5 |    NULL | ARIZONA    | NULL     |
|     6 |       6 | WASHINGTON | MONUMENT |
|     7 |       7 | DELL       | PC       |
|    10 |    NULL | LUCENT     | NULL     |
+-------+---------+------------+----------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 右外连接

RIGHT_JOIN

mysql> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> RIGHT JOIN table_b b
    -> ON table_a.PK=b.PK;
+-------+---------+------------+-----------+
| index | index_2 | one        | two       |
+-------+---------+------------+-----------+
|     1 |       1 | FOX        | TROT      |
|     2 |       2 | COP        | CAR       |
|     3 |       3 | TAXI       | CAB       |
|     6 |       6 | WASHINGTON | MONUMENT  |
|     7 |       7 | DELL       | PC        |
|  NULL |       8 | NULL       | MICROSOFT |
|  NULL |       9 | NULL       | APPLE     |
|  NULL |      11 | NULL       | SCOTCH    |
+-------+---------+------------+-----------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 满外连接

FULL_OUTER_JOIN

# UNION ALL 实现

mysql> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> LEFT JOIN table_b b
    -> ON table_a.PK=b.PK
    -> UNION ALL
    -> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> RIGHT JOIN table_b b
    -> ON table_a.PK=b.PK;
+-------+---------+------------+-----------+
| index | index_2 | one        | two       |
+-------+---------+------------+-----------+
|     1 |       1 | FOX        | TROT      |
|     2 |       2 | COP        | CAR       |
|     3 |       3 | TAXI       | CAB       |
|     4 |    NULL | LINCOLN    | NULL      |
|     5 |    NULL | ARIZONA    | NULL      |
|     6 |       6 | WASHINGTON | MONUMENT  |
|     7 |       7 | DELL       | PC        |
|    10 |    NULL | LUCENT     | NULL      |
|     1 |       1 | FOX        | TROT      |
|     2 |       2 | COP        | CAR       |
|     3 |       3 | TAXI       | CAB       |
|     6 |       6 | WASHINGTON | MONUMENT  |
|     7 |       7 | DELL       | PC        |
|  NULL |       8 | NULL       | MICROSOFT |
|  NULL |       9 | NULL       | APPLE     |
|  NULL |      11 | NULL       | SCOTCH    |
+-------+---------+------------+-----------+
16 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# UNION 实现

mysql> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> LEFT JOIN table_b b
    -> ON table_a.PK=b.PK
    -> UNION
    -> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> RIGHT JOIN table_b b
    -> ON table_a.PK=b.PK;
+-------+---------+------------+-----------+
| index | index_2 | one        | two       |
+-------+---------+------------+-----------+
|     1 |       1 | FOX        | TROT      |
|     2 |       2 | COP        | CAR       |
|     3 |       3 | TAXI       | CAB       |
|     4 |    NULL | LINCOLN    | NULL      |
|     5 |    NULL | ARIZONA    | NULL      |
|     6 |       6 | WASHINGTON | MONUMENT  |
|     7 |       7 | DELL       | PC        |
|    10 |    NULL | LUCENT     | NULL      |
|  NULL |       8 | NULL       | MICROSOFT |
|  NULL |       9 | NULL       | APPLE     |
|  NULL |      11 | NULL       | SCOTCH    |
+-------+---------+------------+-----------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 左外连接不包括内连接

LEFT_EXCLUDING_JOIN

mysql> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> LEFT JOIN table_b b
    -> ON table_a.PK=b.PK
    -> WHERE b.`PK` IS NULL;
+-------+---------+---------+------+
| index | index_2 | one     | two  |
+-------+---------+---------+------+
|     4 |    NULL | LINCOLN | NULL |
|     5 |    NULL | ARIZONA | NULL |
|    10 |    NULL | LUCENT  | NULL |
+-------+---------+---------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

# 右外连接不包括内连接

RIGHT_EXCLUDING_JOIN

mysql> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> RIGHT JOIN table_b b
    -> ON table_a.PK=b.PK
    -> WHERE table_a.`PK` IS NULL;
+-------+---------+------+-----------+
| index | index_2 | one  | two       |
+-------+---------+------+-----------+
|  NULL |       8 | NULL | MICROSOFT |
|  NULL |       9 | NULL | APPLE     |
|  NULL |      11 | NULL | SCOTCH    |
+-------+---------+------+-----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

# 满外连接不包括内连接

OUTER_EXCLUDING_JOIN

mysql> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> LEFT JOIN table_b b
    -> ON table_a.PK=b.PK
    -> WHERE b.`PK` IS NULL
    -> UNION ALL
    -> SELECT table_a.`PK` "index",b.`PK` "index_2",table_a.`VALUE` "one",b.`VALUE` "two"
    -> FROM table_a
    -> RIGHT JOIN table_b b
    -> ON table_a.PK=b.PK
    -> WHERE table_a.`PK` IS NULL;
+-------+---------+---------+-----------+
| index | index_2 | one     | two       |
+-------+---------+---------+-----------+
|     4 |    NULL | LINCOLN | NULL      |
|     5 |    NULL | ARIZONA | NULL      |
|    10 |    NULL | LUCENT  | NULL      |
|  NULL |       8 | NULL    | MICROSOFT |
|  NULL |       9 | NULL    | APPLE     |
|  NULL |      11 | NULL    | SCOTCH    |
+-------+---------+---------+-----------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

上面的实现的，使用的两个结果集没有重复的记录，使用 UNION 或者 UNION ALL 的结果集是一样的，所以这里使用 UNION ALL 更高效。

# SQL99 语言的新特性

# 自然连接：NATURAL JOIN...

自动查询两张表同名字段，然后进行 等值连接

用于 内连接

mysql> SELECT e.employee_id,last_name,d.department_name
    -> FROM employees e
    -> NATURAL JOIN departments d;
+-------------+------------+-----------------+
| employee_id | last_name  | department_name |
+-------------+------------+-----------------+
|         202 | Fay        | Marketing       |
|         115 | Khoo       | Purchasing      |
|         116 | Baida      | Purchasing      |
-- ... ...
|         112 | Urman      | Finance         |
|         113 | Popp       | Finance         |
|         206 | Gietz      | Accounting      |
+-------------+------------+-----------------+
32 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

等效操作：

mysql> SELECT e.employee_id,last_name,d.department_name
    -> FROM employees e
    -> JOIN departments d
    -> ON d.department_id=e.department_id
    -> AND d.manager_id=e.manager_id;
+-------------+------------+-----------------+
| employee_id | last_name  | department_name |
+-------------+------------+-----------------+
|         202 | Fay        | Marketing       |
|         115 | Khoo       | Purchasing      |
|         116 | Baida      | Purchasing      |
-- ... ...
|         112 | Urman      | Finance         |
|         113 | Popp       | Finance         |
|         206 | Gietz      | Accounting      |
+-------------+------------+-----------------+
32 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这种操作不够灵活，有些时候想查询的并不是自己想要的结果，就比如上面这种，想要查询的是每个员工对应的是哪个部门。

# USING：JOIN...USING()

与上面不同的是，我们手动选择比较哪些同名字段，然后进行 等值连接

用于 内连接

mysql> SELECT e.employee_id,last_name,d.department_name
    -> FROM employees e
    -> JOIN departments d
    -> USING(department_id);
+-------------+-------------+------------------+
| employee_id | last_name   | department_name  |
+-------------+-------------+------------------+
|         200 | Whalen      | Administration   |
|         201 | Hartstein   | Marketing        |
|         202 | Fay         | Marketing        |
-- ... ...
|         113 | Popp        | Finance          |
|         205 | Higgins     | Accounting       |
|         206 | Gietz       | Accounting       |
+-------------+-------------+------------------+
106 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

等效操作：

mysql> SELECT e.employee_id,last_name,d.department_name
    -> FROM employees e
    -> JOIN departments d
    -> ON e.department_id=d.department_id;
+-------------+-------------+------------------+
| employee_id | last_name   | department_name  |
+-------------+-------------+------------------+
|         200 | Whalen      | Administration   |
|         201 | Hartstein   | Marketing        |
|         202 | Fay         | Marketing        |
-- ... ...
|         113 | Popp        | Finance          |
|         205 | Higgins     | Accounting       |
|         206 | Gietz       | Accounting       |
+-------------+-------------+------------------+
106 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

相对灵活，但无法 自连接

# 练习

# 1. 显示所有员工的姓名，部门号和部门名称

mysql> SELECT e.last_name,e.department_id,d.department_name
    -> FROM employees e
    -> LEFT JOIN departments d
    -> ON d.department_id=e.department_id;
+-------------+---------------+------------------+
| last_name   | department_id | department_name  |
+-------------+---------------+------------------+
| King        |            90 | Executive        |
| Kochhar     |            90 | Executive        |
| De Haan     |            90 | Executive        |
| Hunold      |            60 | IT               |
| Ernst       |            60 | IT               |
| Austin      |            60 | IT               |
-- ... ...
| Baer        |            70 | Public Relations |
| Higgins     |           110 | Accounting       |
| Gietz       |           110 | Accounting       |
+-------------+---------------+------------------+
107 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 2. 查询90号部门员工的 job_id 和 90 号部门的 location_id

mysql> SELECT e.last_name,e.job_id,d.location_id,e.department_id
    -> FROM employees e
    -> JOIN departments d
    -> ON e.department_id=d.department_id
    -> WHERE e.department_id=90;
+-----------+---------+-------------+---------------+
| last_name | job_id  | location_id | department_id |
+-----------+---------+-------------+---------------+
| King      | AD_PRES |        1700 |            90 |
| Kochhar   | AD_VP   |        1700 |            90 |
| De Haan   | AD_VP   |        1700 |            90 |
+-----------+---------+-------------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

# 3. 选择所有有奖金的员工的 last_name 、department_name、location_id、city

需要考虑有的员工没有没有分配部门

mysql> SELECT e.last_name,d.department_name,l.location_id,l.city
    -> FROM employees e
    -> LEFT JOIN departments d
    -> ON d.department_id=e.department_id
    -> LEFT JOIN locations l
    -> ON l.location_id=d.location_id
    -> WHERE e.commission_pct IS NOT NULL;
+------------+-----------------+-------------+--------+
| last_name  | department_name | location_id | city   |
+------------+-----------------+-------------+--------+
| Russell    | Sales           |        2500 | Oxford |
| Partners   | Sales           |        2500 | Oxford |
| Errazuriz  | Sales           |        2500 | Oxford |
-- ... ...
| Taylor     | Sales           |        2500 | Oxford |
| Livingston | Sales           |        2500 | Oxford |
| Grant      | NULL            |        NULL | NULL   |
| Johnson    | Sales           |        2500 | Oxford |
+------------+-----------------+-------------+--------+
35 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 4. 选择 city 在 Toronto 工作的员工的 last_name、job_id、department_id、department_name

mysql> SELECT e.last_name,e.job_id,d.department_id,d.department_name
    -> FROM employees e
    -> JOIN departments d
    -> ON e.department_id=d.department_id
    -> JOIN locations l
    -> ON d.location_id=l.location_id
    -> AND l.city='Toronto';
+-----------+--------+---------------+-----------------+
| last_name | job_id | department_id | department_name |
+-----------+--------+---------------+-----------------+
| Hartstein | MK_MAN |            20 | Marketing       |
| Fay       | MK_REP |            20 | Marketing       |
+-----------+--------+---------------+-----------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 5. 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为 Executive

mysql> SELECT e.last_name,d.department_name,e.salary,l.street_address,j.job_id
    -> FROM employees e
    -> JOIN departments d
    -> ON d.department_id=e.department_id
    -> AND d.department_name='Executive'
    -> LEFT JOIN locations l
    -> ON l.location_id=d.location_id
    -> LEFT JOIN jobs j
    -> ON e.job_id=j.job_id;
+-----------+-----------------+----------+-----------------+---------+
| last_name | department_name | salary   | street_address  | job_id  |
+-----------+-----------------+----------+-----------------+---------+
| King      | Executive       | 24000.00 | 2004 Charade Rd | AD_PRES |
| Kochhar   | Executive       | 17000.00 | 2004 Charade Rd | AD_VP   |
| De Haan   | Executive       | 17000.00 | 2004 Charade Rd | AD_VP   |
+-----------+-----------------+----------+-----------------+---------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 6. 选择指定员工的姓名、员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees	Emp#	manager	Mgr#
kochhar		101	king	100
1
2
mysql> SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
    -> FROM employees e
    -> LEFT JOIN employees m
    -> ON e.manager_id=m.employee_id;
+-------------+------+-----------+------+
| employees   | Emp# | manager   | Mgr# |
+-------------+------+-----------+------+
| King        |  100 | NULL      | NULL |
| Kochhar     |  101 | King      |  100 |
| De Haan     |  102 | King      |  100 |
-- ... ...
| Baer        |  204 | Kochhar   |  101 |
| Higgins     |  205 | Kochhar   |  101 |
| Gietz       |  206 | Higgins   |  205 |
+-------------+------+-----------+------+
107 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 7. 查询哪些部门没有员工

mysql> SELECT d.department_name,d.department_id
    -> FROM departments d
    -> LEFT JOIN employees e
    -> ON d.department_id=e.department_id
    -> WHERE e.department_id IS NULL;
+----------------------+---------------+
| department_name      | department_id |
+----------------------+---------------+
| Treasury             |           120 |
| Corporate Tax        |           130 |
| Control And Credit   |           140 |
| Shareholder Services |           150 |
| Benefits             |           160 |
| Manufacturing        |           170 |
| Construction         |           180 |
| Contracting          |           190 |
| Operations           |           200 |
| IT Support           |           210 |
| NOC                  |           220 |
| IT Helpdesk          |           230 |
| Government Sales     |           240 |
| Retail Sales         |           250 |
| Recruiting           |           260 |
| Payroll              |           270 |
+----------------------+---------------+
16 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 8. 查询哪个城市没有部门

mysql> SELECT l.city
    -> FROM locations l
    -> LEFT JOIN departments d
    -> ON d.location_id=l.location_id
    -> WHERE d.location_id IS NULL;
+-----------------+
| city            |
+-----------------+
| Roma            |
| Venice          |
| Tokyo           |
| Hiroshima       |
| South Brunswick |
| Whitehorse      |
| Beijing         |
| Bombay          |
| Sydney          |
| Singapore       |
| Stretford       |
| Sao Paulo       |
| Geneva          |
| Bern            |
| Utrecht         |
| Mexico City     |
+-----------------+
16 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 9. 查询部门名为 Sales 或 IT 的员工信息

mysql> SELECT e.employee_id,e.last_name
    -> FROM departments d
    -> JOIN employees e
    -> ON e.department_id=d.department_id
    -> WHERE d.department_name IN('Sales','IT');
+-------------+------------+
| employee_id | last_name  |
+-------------+------------+
|         103 | Hunold     |
|         104 | Ernst      |
|         105 | Austin     |
-- ... ...
|         176 | Taylor     |
|         177 | Livingston |
|         179 | Johnson    |
+-------------+------------+
39 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 参考连接

上次更新: 2024/01/04, 18:32:19
排序与分页
单行函数

← 单行函数→

最近更新
01
Go：GMP模型深入理解
01-10
02
rpc学习：进阶到gRPC
01-04
03
配置
12-12
更多文章>
Theme by Vdoing | Copyright © 2019-2024 xxcheng | 浙ICP备19024050号