mysql/数据库查询.md

<h2><center>数据库查询</center></h2>

------

## 一：基本查询

### 1. 简介

- 单表查询
- 简单查询
- 通过条件查询
- 查询排序
- 限制查询记录数
- 使用集合函数查询
- 分组查询
- 使用正则表达式查询

### 2. 案例

创建案例所需表：company.employee1

```
	雇员编号	 id				        int
	雇员姓名	 name			       varchar(30)
	雇员性别	 sex				   enum
	雇用时期	 hire_date			   date
	职位		  post					varchar(50)
	职位描述	 job_description	    varchar(100)
	薪水		   salary				double(15,2)
	办公室		  office			    int
	部门编号	 dep_id				   int
```

```SQL
mysql> create table company.employee1(
    -> id int primary key auto_increment not null,
    -> name varchar(30) not null,
    -> sex enum('m','f') default 'm' not null,
    -> hire_date date not null,
    -> hire_date varchar(50) not null,
    -> job_description varchar(100),
    -> salary double(15,2) not null,
    -> office int,
    -> dep_id int 
    -> );
Query OK, 0 rows affected (0.00 sec)
```

插入模拟数据：

```SQL
mysql> insert into company.employee1(name,sex,hire_date,post,job_description,salary,office,dep_id) values
    -> ('jack','m','20180202','instructor','teach',5000,501,100),
    -> ('tom','m','20180203','instructor','teach',5500,501,100),
    -> ('robin','m','20180202','instructor','teach',8000,501,100),
    -> ('alice','f','20180202','instructor','teach',7200,501,100),
    -> ('','m','20180202','hr','hrcc',600,502,101),
    -> ('harry','m','20180202','hr',NULL,6000,502,101),
    -> ('emma','f','20180206','sale','salecc',20000,503,102),
    -> ('christine','f','20180205','sale','salecc',2200,503,102),
    -> ('zhuzhu','m','20180205','sale',NULL,2200,503,102),
    -> ('gougou','m','20180205','sale','',2200,503,102);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
```

语法格式：

​	select   字段名称,字段名称2......    from  表名  [条件]

#### 简单查询

```SQL
mysql> select * from employee1;
+----+-----------+-----+------------+-----------------+----------+--------+--------+------------+
| id | name      | sex | hire_date  | job_description | salary   | office | dep_id | post       |
+----+-----------+-----+------------+-----------------+----------+--------+--------+------------+
|  1 | jack      | m   | 2018-02-02 | teach           |  5000.00 |    501 |    100 | instructor |
|  2 | tom       | m   | 2018-02-03 | teach           |  5500.00 |    501 |    100 | instructor |
|  3 | robin     | m   | 2018-02-02 | teach           |  8000.00 |    501 |    100 | instructor |
|  4 | alice     | f   | 2018-02-02 | teach           |  7200.00 |    501 |    100 | instructor |
|  5 |           | m   | 2018-02-02 | hrcc            |   600.00 |    502 |    101 | hr         |
|  6 | harry     | m   | 2018-02-02 | NULL            |  6000.00 |    502 |    101 | hr         |
|  7 | emma      | f   | 2018-02-06 | salecc          | 20000.00 |    503 |    102 | sale       |
|  8 | christine | f   | 2018-02-05 | salecc          |  2200.00 |    503 |    102 | sale       |
|  9 | zhuzhu    | m   | 2018-02-05 | NULL            |  2200.00 |    503 |    102 | sale       |
| 10 | gougou    | m   | 2018-02-05 |                 |  2200.00 |    503 |    102 | sale       |
+----+-----------+-----+------------+-----------------+----------+--------+--------+------------+
10 rows in set (0.00 sec)

mysql> select name,salary,dep_id from employee1 where id <= 5;
+-------+---------+--------+
| name  | salary  | dep_id |
+-------+---------+--------+
| jack  | 5000.00 |    100 |
| tom   | 5500.00 |    100 |
| robin | 8000.00 |    100 |
| alice | 7200.00 |    100 |
|       |  600.00 |    101 |
+-------+---------+--------+
5 rows in set (0.00 sec)
```

#### 避免重复

不能部分使用DISTINCT，通常仅用于某一字段

```SQL
mysql> select post from employee1;
+------------+
| post       |
+------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr         |
| hr         |
| sale       |
| sale       |
| sale       |
| sale       |
+------------+
10 rows in set (0.00 sec)

mysql> select distinct post from employee1;
+------------+
| post       |
+------------+
| instructor |
| hr         |
| sale       |
+------------+
3 rows in set (0.00 sec)
```

#### 四则运算查询

```SQL
mysql> select name,salary,salary*14 from employee1;
+-----------+----------+-----------+
| name      | salary   | salary*14 |
+-----------+----------+-----------+
| jack      |  5000.00 |  70000.00 |
| tom       |  5500.00 |  77000.00 |
| robin     |  8000.00 | 112000.00 |
| alice     |  7200.00 | 100800.00 |
|           |   600.00 |   8400.00 |
| harry     |  6000.00 |  84000.00 |
| emma      | 20000.00 | 280000.00 |
| christine |  2200.00 |  30800.00 |
| zhuzhu    |  2200.00 |  30800.00 |
| gougou    |  2200.00 |  30800.00 |
+-----------+----------+-----------+
10 rows in set (0.00 sec)

mysql> select name,salary,salary*14 as Annual_salary from employee1;
+-----------+----------+---------------+
| name      | salary   | Annual_salary |
+-----------+----------+---------------+
| jack      |  5000.00 |      70000.00 |
| tom       |  5500.00 |      77000.00 |
| robin     |  8000.00 |     112000.00 |
| alice     |  7200.00 |     100800.00 |
|           |   600.00 |       8400.00 |
| harry     |  6000.00 |      84000.00 |
| emma      | 20000.00 |     280000.00 |
| christine |  2200.00 |      30800.00 |
| zhuzhu    |  2200.00 |      30800.00 |
| gougou    |  2200.00 |      30800.00 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)

mysql> select name,salary,salary*14 Annual_salary from employee1;
+-----------+----------+---------------+
| name      | salary   | Annual_salary |
+-----------+----------+---------------+
| jack      |  5000.00 |      70000.00 |
| tom       |  5500.00 |      77000.00 |
| robin     |  8000.00 |     112000.00 |
| alice     |  7200.00 |     100800.00 |
|           |   600.00 |       8400.00 |
| harry     |  6000.00 |      84000.00 |
| emma      | 20000.00 |     280000.00 |
| christine |  2200.00 |      30800.00 |
| zhuzhu    |  2200.00 |      30800.00 |
| gougou    |  2200.00 |      30800.00 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)
```

#### 定义显示格式

CONCAT() 函数用于连接字符串

```SQL
mysql> select concat(name,'s annual salary:',salary*14) as Annual_salary from employee1;
+-----------------------------------+
| Annual_salary                     |
+-----------------------------------+
| jacks annual salary:70000.00      |
| toms annual salary:77000.00       |
| robins annual salary:112000.00    |
| alices annual salary:100800.00    |
| s annual salary:8400.00           |
| harrys annual salary:84000.00     |
| emmas annual salary:280000.00     |
| christines annual salary:30800.00 |
| zhuzhus annual salary:30800.00    |
| gougous annual salary:30800.00    |
+-----------------------------------+
10 rows in set (0.00 sec)
```

#### 单条件查询

```SQL
mysql> select name,post from employee1 where post='hr';
+-------+------+
| name  | post |
+-------+------+
|       | hr   |
| harry | hr   |
+-------+------+
2 rows in set (0.00 sec)
```

#### 多条件查询

```SQL
mysql> select name,salary from employee1 where post='hr' and salary>10000;
Empty set (0.00 sec)

mysql> select * from employee1 where salary>5000 and salary<10000 or dep_id=102;
+----+-----------+-----+------------+-----------------+----------+--------+--------+------------+
| id | name      | sex | hire_date  | job_description | salary   | office | dep_id | post       |
+----+-----------+-----+------------+-----------------+----------+--------+--------+------------+
|  2 | tom       | m   | 2018-02-03 | teach           |  5500.00 |    501 |    100 | instructor |
|  3 | robin     | m   | 2018-02-02 | teach           |  8000.00 |    501 |    100 | instructor |
|  4 | alice     | f   | 2018-02-02 | teach           |  7200.00 |    501 |    100 | instructor |
|  6 | harry     | m   | 2018-02-02 | NULL            |  6000.00 |    502 |    101 | hr         |
|  7 | emma      | f   | 2018-02-06 | salecc          | 20000.00 |    503 |    102 | sale       |
|  8 | christine | f   | 2018-02-05 | salecc          |  2200.00 |    503 |    102 | sale       |
|  9 | zhuzhu    | m   | 2018-02-05 | NULL            |  2200.00 |    503 |    102 | sale       |
| 10 | gougou    | m   | 2018-02-05 |                 |  2200.00 |    503 |    102 | sale       |
+----+-----------+-----+------------+-----------------+----------+--------+--------+------------+
8 rows in set (0.00 sec)
```

#### 关键字

BETWEEN AND

```SQL
mysql> select name,salary from employee1 where salary between 5000 and 15000;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| tom   | 5500.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
5 rows in set (0.00 sec)

mysql> select name,salary from employee1 where salary not between 5000 and 15000;
+-----------+----------+
| name      | salary   |
+-----------+----------+
|           |   600.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
5 rows in set (0.00 sec)
```

IS NULL

```SQL
mysql> select name,job_description from employee1 where job_description is null;
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| harry  | NULL            |
| zhuzhu | NULL            |
+--------+-----------------+
2 rows in set (0.00 sec)

mysql> select name,job_description from employee1 where job_description is not null;
+-----------+-----------------+
| name      | job_description |
+-----------+-----------------+
| jack      | teach           |
| tom       | teach           |
| robin     | teach           |
| alice     | teach           |
|           | hrcc            |
| emma      | salecc          |
| christine | salecc          |
| gougou    |                 |
+-----------+-----------------+
8 rows in set (0.00 sec)

mysql> select name,job_description from employee1 where job_description='';
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| gougou |                 |
+--------+-----------------+
1 row in set (0.00 sec)
```

注意：（NULL说明）

- 等价于没有任何值、是未知数
- NULL与0、空字符串、空格都不同,NULL没有分配存储空间
- 对空值做加、减、乘、除等运算操作，结果仍为空
- 比较时使用关键字用“is null”和“is not null”
- 排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前

#### IN集合查询

```SQL
mysql> select name,salary from employee1 where salary=4000 or salary=5000 or salary=6000 or salary=9000; 
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)

mysql> select name,salary from employee1 where salary in (4000,5000,6000,9000);
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)

mysql> select name,salary from employee1 where salary not in (4000,5000,6000,9000);
+-----------+----------+
| name      | salary   |
+-----------+----------+
| tom       |  5500.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
|           |   600.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
8 rows in set (0.00 sec)
```

#### 模糊查询

关键字LIKE

通配符%：所有字符

通配符_： 一个字符

```SQL
mysql> select * from employee1 where name like 'al%';
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
| id | name  | sex | hire_date  | job_description | salary  | office | dep_id | post       |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
|  4 | alice | f   | 2018-02-02 | teach           | 7200.00 |    501 |    100 | instructor |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
1 row in set (0.00 sec)

mysql> select * from employee1 where name like 'al___';
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
| id | name  | sex | hire_date  | job_description | salary  | office | dep_id | post       |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
|  4 | alice | f   | 2018-02-02 | teach           | 7200.00 |    501 |    100 | instructor |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
1 row in set (0.00 sec)
```

#### 排序查询

```SQL
mysql> select name,salary from employee1 order by salary;			//升序
+-----------+----------+
| name      | salary   |
+-----------+----------+
|           |   600.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
| jack      |  5000.00 |
| tom       |  5500.00 |
| harry     |  6000.00 |
| alice     |  7200.00 |
| robin     |  8000.00 |
| emma      | 20000.00 |
+-----------+----------+
10 rows in set (0.00 sec)

mysql> select name,salary from employee1 order by salary desc;   //降序
+-----------+----------+
| name      | salary   |
+-----------+----------+
| emma      | 20000.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
| harry     |  6000.00 |
| tom       |  5500.00 |
| jack      |  5000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
|           |   600.00 |
+-----------+----------+
10 rows in set (0.00 sec)

mysql> select name,salary from employee1 order by salary desc limit 3;   //控制显示前3行
+-------+----------+
| name  | salary   |
+-------+----------+
| emma  | 20000.00 |
| robin |  8000.00 |
| alice |  7200.00 |
+-------+----------+
3 rows in set (0.00 sec)

mysql> select name,salary from employee1 order by salary desc limit 1,3;   //从序号1开始显示三行的内容
+-------+---------+
| name  | salary  |
+-------+---------+
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
3 rows in set (0.00 sec)
```

注意：

- ascending    美音 /ə'sɛndɪŋ/   升序
- descending  美音 /dɪ'sɛndɪŋ/  降序

#### 集合函数查询

count：可以查看共有多少条记录

```SQL
mysql> select count(*) from employee1;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> select count(name) from employee1;
+-------------+
| count(name) |
+-------------+
|          10 |
+-------------+
1 row in set (0.01 sec)
```

max：查看最大值

```SQL
mysql> select max(salary) from employee1;
+-------------+
| max(salary) |
+-------------+
|    20000.00 |
+-------------+
1 row in set (0.00 sec)
```

min：查看最小值

```SQL
mysql> select min(salary) from employee1;
+-------------+
| min(salary) |
+-------------+
|      600.00 |
+-------------+
1 row in set (0.00 sec)
```

avg：查看平均值

```SQL
mysql> select avg(salary) from employee1;
+-------------+
| avg(salary) |
+-------------+
| 5890.000000 |
+-------------+
1 row in set (0.00 sec)
```

sum：求和

sale这个部门的总工资

```SQL
mysql> select concat("Total Department Wages:",sum(salary)) from employee1 where post='sale';
+-----------------------------------------------+
| concat("Total Department Wages:",sum(salary)) |
+-----------------------------------------------+
| Total Department Wages:26600.00               |
+-----------------------------------------------+
1 row in set (0.00 sec)
```

获取薪水最高的这个人的详细信息

```SQL
mysql> select * from employee1 where salary=(select max(salary) from employee1);
+----+------+-----+------------+-----------------+----------+--------+--------+------+
| id | name | sex | hire_date  | job_description | salary   | office | dep_id | post |
+----+------+-----+------------+-----------------+----------+--------+--------+------+
|  7 | emma | f   | 2018-02-06 | salecc          | 20000.00 |    503 |    102 | sale |
+----+------+-----+------------+-----------------+----------+--------+--------+------+
1 row in set (0.00 sec)
```

#### 分组查询

GROUP BY和GROUP_CONCAT()函数一起使用

获取部门ID相同的员工并把名字拼接到一起

```SQL
mysql> select dep_id,group_concat(name) from employee1 group by dep_id;
+--------+------------------------------+
| dep_id | group_concat(name)           |
+--------+------------------------------+
|    100 | jack,tom,robin,alice         |
|    101 | ,harry                       |
|    102 | emma,christine,zhuzhu,gougou |
+--------+------------------------------+
3 rows in set (0.00 sec)
```

GROUP BY和集合函数一起使用

获取部门最高薪资

```SQL
mysql> select post,max(salary) from employee1 group by post;
+------------+-------------+
| post       | max(salary) |
+------------+-------------+
| hr         |     6000.00 |
| instructor |     8000.00 |
| sale       |    20000.00 |
+------------+-------------+
3 rows in set (0.00 sec)
```

#### 正则查询

```SQL
以什么开头
mysql> select * from employee1 where name regexp '^ali';
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
| id | name  | sex | hire_date  | job_description | salary  | office | dep_id | post       |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
|  4 | alice | f   | 2018-02-02 | teach           | 7200.00 |    501 |    100 | instructor |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
1 row in set (0.00 sec)

以什么开头
mysql> select * from employee1 where name regexp 'ce$';
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
| id | name  | sex | hire_date  | job_description | salary  | office | dep_id | post       |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
|  4 | alice | f   | 2018-02-02 | teach           | 7200.00 |    501 |    100 | instructor |
+----+-------+-----+------------+-----------------+---------+--------+--------+------------+
1 row in set (0.00 sec)

连续出现n次
mysql> select * from employee1 where name regexp 'm{2}';
+----+------+-----+------------+-----------------+----------+--------+--------+------+
| id | name | sex | hire_date  | job_description | salary   | office | dep_id | post |
+----+------+-----+------------+-----------------+----------+--------+--------+------+
|  7 | emma | f   | 2018-02-06 | salecc          | 20000.00 |    503 |    102 | sale |
+----+------+-----+------------+-----------------+----------+--------+--------+------+
1 row in set (0.00 sec)
```



## 二：多表联合查询

### 1.数据准备

**表：company.employee2**

创建表：

```SQL
mysql> create table employee2(
    -> emp_id int auto_increment primary key not null,
    -> emp_name varchar(50),
    -> age int,
    -> dept_id int
    -> );
Query OK, 0 rows affected (0.00 sec)
```

查看表结构：

```SQL
mysql> desc employee2;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| emp_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| emp_name | varchar(50) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
| dept_id  | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
```

插入模拟数据：

```SQL
mysql> insert into employee2(emp_name,age,dept_id) values
    -> ('',19,200),
    -> ('tom',26,201),
    -> ('jack',30,201),
    -> ('alice',24,202),
    -> ('robin',40,200),
    -> ('wxin',16,200),
    -> ('natasha',28,204);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0
```

查看数据：

```SQL
mysql> select * from employee2;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 |          |   19 |     200 |
|      2 | tom      |   26 |     201 |
|      3 | jack     |   30 |     201 |
|      4 | alice    |   24 |     202 |
|      5 | robin    |   40 |     200 |
|      6 | wxin     |   16 |     200 |
|      7 | natasha  |   28 |     204 |
+--------+----------+------+---------+
7 rows in set (0.00 sec)
```

**表：company.department2**

创建表：

```SQL
mysql> create table department2(
    -> dept_id int,
    -> dept_name varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)
```

查看表结构：

```SQL
mysql> desc department2;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| dept_id   | int(11)      | YES  |     | NULL    |       |
| dept_name | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```

模拟插入数据：

```SQL
mysql> insert into department2 values
    -> (200,'hr'),
    -> (201,'it'),
    -> (202,'sale'),
    -> (203,'fd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
```

查看数据：

```SQL
sql> select * from department2;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
|     202 | sale      |
|     203 | fd        |
+---------+-----------+
4 rows in set (0.00 sec)
```

### 2. 多表的连接查询

- 交叉连接：生成笛卡尔积，它不使用任何匹配条件；交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合
- 内连接：只连接匹配的行
- 外连接：
  1. 左连接：会显示左边表内所有的值，不论在右边表内匹不匹配
  2. 右连接：会显示右边表内所有的值，不论在左边表内匹不匹配
- 全外连接：包含左、右两个表的全部行

**交叉连接**

```SQL
mysql> select employee2.emp_name,employee2.age,employee2.dept_id,department2.dept_name from employee2,department2;
+----------+------+---------+-----------+
| emp_name | age  | dept_id | dept_name |
+----------+------+---------+-----------+
|          |   19 |     200 | hr        |
|          |   19 |     200 | it        |
|          |   19 |     200 | sale      |
|          |   19 |     200 | fd        |
| tom      |   26 |     201 | hr        |
| tom      |   26 |     201 | it        |
| tom      |   26 |     201 | sale      |
| tom      |   26 |     201 | fd        |
| jack     |   30 |     201 | hr        |
| jack     |   30 |     201 | it        |
| jack     |   30 |     201 | sale      |
| jack     |   30 |     201 | fd        |
| alice    |   24 |     202 | hr        |
| alice    |   24 |     202 | it        |
| alice    |   24 |     202 | sale      |
| alice    |   24 |     202 | fd        |
| robin    |   40 |     200 | hr        |
| robin    |   40 |     200 | it        |
| robin    |   40 |     200 | sale      |
| robin    |   40 |     200 | fd        |
| wxin     |   16 |     200 | hr        |
| wxin     |   16 |     200 | it        |
| wxin     |   16 |     200 | sale      |
| wxin     |   16 |     200 | fd        |
| natasha  |   28 |     204 | hr        |
| natasha  |   28 |     204 | it        |
| natasha  |   28 |     204 | sale      |
| natasha  |   28 |     204 | fd        |
+----------+------+---------+-----------+
28 rows in set (0.00 sec)
```

**内连接**

获取有部门的员工 (部门表中没有natasha所在的部门)

```SQL
mysql> select employee2.emp_name,employee2.age,employee2.dept_id,department2.dept_name from employee2,department2 where employee2.dept_id=department2.dept_id;
+----------+------+---------+-----------+
| emp_name | age  | dept_id | dept_name |
+----------+------+---------+-----------+
|          |   19 |     200 | hr        |
| tom      |   26 |     201 | it        |
| jack     |   30 |     201 | it        |
| alice    |   24 |     202 | sale      |
| robin    |   40 |     200 | hr        |
| wxin     |   16 |     200 | hr        |
+----------+------+---------+-----------+
6 rows in set (0.00 sec)

mysql> select employee2.emp_name,department2.dept_name from employee2 inner join department2 on employee2.dept_id=department2.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
|          | hr        |
| tom      | it        |
| jack     | it        |
| alice    | sale      |
| robin    | hr        |
| wxin     | hr        |
+----------+-----------+
6 rows in set (0.00 sec)
```

**外连接**

语法：

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

注意：

​	先用谁谁就是左

**左连接 left join**

```SQL
找出所有员工及所属的部门，包括没有部门的员工
mysql> select emp_id,emp_name,dept_name from employee2 left join department2 on employee2.dept_id=department2.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 |          | hr        |
|      5 | robin    | hr        |
|      6 | wxin     | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      7 | natasha  | NULL      |
+--------+----------+-----------+
7 rows in set (0.00 sec)
```

**右连接 right join**

```SQL
找出所有部门包含的员工，包括空部门
mysql> select emp_id,emp_name,dept_name from employee2 right join department2 on employee2.dept_id=department2.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 |          | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      5 | robin    | hr        |
|      6 | wxin     | hr        |
|   NULL | NULL     | fd        |
+--------+----------+-----------+
7 rows in set (0.00 sec)
```

**全外连接**

```
mysql> select * from employee2 full join department2;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 |          |   19 |     200 |     200 | hr        |
|      1 |          |   19 |     200 |     201 | it        |
|      1 |          |   19 |     200 |     202 | sale      |
|      1 |          |   19 |     200 |     203 | fd        |
|      2 | tom      |   26 |     201 |     200 | hr        |
|      2 | tom      |   26 |     201 |     201 | it        |
|      2 | tom      |   26 |     201 |     202 | sale      |
|      2 | tom      |   26 |     201 |     203 | fd        |
|      3 | jack     |   30 |     201 |     200 | hr        |
|      3 | jack     |   30 |     201 |     201 | it        |
|      3 | jack     |   30 |     201 |     202 | sale      |
|      3 | jack     |   30 |     201 |     203 | fd        |
|      4 | alice    |   24 |     202 |     200 | hr        |
|      4 | alice    |   24 |     202 |     201 | it        |
|      4 | alice    |   24 |     202 |     202 | sale      |
|      4 | alice    |   24 |     202 |     203 | fd        |
|      5 | robin    |   40 |     200 |     200 | hr        |
|      5 | robin    |   40 |     200 |     201 | it        |
|      5 | robin    |   40 |     200 |     202 | sale      |
|      5 | robin    |   40 |     200 |     203 | fd        |
|      6 | wxin     |   16 |     200 |     200 | hr        |
|      6 | wxin     |   16 |     200 |     201 | it        |
|      6 | wxin     |   16 |     200 |     202 | sale      |
|      6 | wxin     |   16 |     200 |     203 | fd        |
|      7 | natasha  |   28 |     204 |     200 | hr        |
|      7 | natasha  |   28 |     204 |     201 | it        |
|      7 | natasha  |   28 |     204 |     202 | sale      |
|      7 | natasha  |   28 |     204 |     203 | fd        |
+--------+----------+------+---------+---------+-----------+
28 rows in set (0.00 sec)
```

### 3. 复合条件连接查询

#### 案例一

​	找出公司所有部门中年龄大于25岁的员工

​	以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25

```SQL
mysql> select emp_id,emp_name,dept_name from employee2,department2 where employee2.dept_id=department2.dept_id and age>25;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      5 | robin    | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
+--------+----------+-----------+
3 rows in set (0.00 sec)
```

#### 案例二

​	以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示

```SQL
mysql> select emp_id,emp_name,dept_name from employee2,department2 where employee2.dept_id=department2.dept_id order by age asc;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      6 | wxin     | hr        |
|      1 |          | hr        |
|      4 | alice    | sale      |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      5 | robin    | hr        |
+--------+----------+-----------+
6 rows in set (0.00 sec)
```

### 4. 子查询

​	子查询是将一个查询语句嵌套在另一个查询语句中

​	内层查询语句的查询结果，可以为外层查询语句提供查询条件

​	子查询中可以包含：IN、NOT IN等关键字；还可以包含比较运算符：= 、 !=、> 、<等

#### 案例一

​	带IN关键字的子查询；查询employee表，但dept_id必须在department表中出现过

```SQL
ysql> select * from employee2 where dept_id in (select dept_id from department2);
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 |          |   19 |     200 |
|      2 | tom      |   26 |     201 |
|      3 | jack     |   30 |     201 |
|      4 | alice    |   24 |     202 |
|      5 | robin    |   40 |     200 |
|      6 | wxin     |   16 |     200 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)
```

#### 案例二

​	带比较运算符的子查询；查询年龄大于等于25岁员工所在部门（查询老龄化的部门）

```SQL
mysql> select dept_id,dept_name from department2 where dept_id in (select distinct dept_id from employee2 where age>=25);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     201 | it        |
|     200 | hr        |
+---------+-----------+
2 rows in set (0.00 sec)
```