一、INSERT INTO SELECT

1.什么是 INSERT INTO SELECT ？

INSERT INTO SELECT 允许我们从一个表中选择数据，并将其插入到另一个表中。这个功能非常适用于需要从一个表中复制数据到另一个表的情况，或者将查询结果存储到新的表中。

2.INSERT INTO SELECT的基本语法格式

1
2
3
4
INSERT INTO 目标表 (列1, 列2, 列3, ...)
SELECT 列1, 列2, 列3, ...
FROM 源表
WHERE 条件;

3.INSERT INTO SELECT的基本使用

例如：我有两张表，分别是 employees 和 new_hires表，employees 表用于存储雇员的基本信息，new_hires 表是一个暂存表，用于存储最近招聘的新员工信息。

employees 表结构：

new_hires 表结构与 employess 表结构相同

现在我想要将 2020 年之后的员工信息添加到 new_hires表中

我们可以使用 WHERE 条件查询

1
2
3
4
INSERT INTO new_hires(employee_id, first_name, last_name, hire_date)
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2020-01-01';

我们也可以使用子查询（只用来演示如何在当中使用子查询）

1
2
3
INSERT INTO new_hires(employee_id, first_name, last_name, hire_date)
SELECT employee_id, first_name, last_name, hire_date
FROM (SELECT * FROM employees WHERE hire_date < '2020-01-01') AS subquery;

例如：我有三张表，分别是customers表、orders表和new_customers表，customers表用于存储顾客信息，orders表用于存储顾客下的订单信息，new_customers表用于暂存新的顾客信息

customers 表结构：

new_customers 表结构与 customers 表结构相同。

orders 表结构：

现在我想要将购买日期在2020年之后的订单中的顾客信息添加到 new_customers 表中

我们可以使用 连接 以使用 oders 表中的 日期 

1
2
3
4
5
INSERT INTO new_customers (customer_id, customer_name, email)
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.purchase_date > '2020-01-01';

二、批量更新

例如：我有一个员工表，我想给2020年之后入职的员工增加 10% 的薪水，给2020年之前入职的员工增加 20% 的薪水。

我们可以用 case when then

1
2
3
4
5
6
UPDATE employees
SET salary = 
    CASE 
        WHEN hire_date >= '2020-01-01' THEN salary * 1.1  -- 入职日期在 2020 年之后，增加 10%
        ELSE salary * 1.2  -- 入职日期在 2020 年之前，增加 20%
    END;

更新后数据

我们还可以用where 限定 增薪 在 id 1,2,3,5中

1
2
3
4
5
6
7
UPDATE employees
SET salary = 
    CASE 
        WHEN hire_date >= '2020-01-01' THEN salary * 1.1  -- 入职日期在 2020 年之后，增加 10%
        ELSE salary * 1.2  -- 入职日期在 2020 年之前，增加 20%
    END
WHERE employee_id in (1,2,3,5)

可以看到除了 id 为4的员工都进行了 增薪

三、多表联查

表别名

  • 给表起一个短一点的名字
  • 在多表关联查询时，如果多个表之间存在同名的列，则必须使用表别名来限定列的使用。
  • 在from子句中给表起别名，现起现用。
  • 由于from子句最先执行，故一旦在from子句中为表指定了别名，则必须在剩下的子句中都使用表别名（不同名的列可以不用表别名限定，但最好用），不允许再使用原来的名字。

部门表：

员工表：

员工工资表：

1.内连接

内连接（Inner Join）用于合并两个表中符合指定条件的行。它返回两个表中符合条件的交集。

例如：我想要找出每个员工所在部门的名称以及其工资

1
2
3
4
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, s.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN salaries s ON e.employee_id = s.employee_id;

可以看到除了id为4的员工，其他的员工信息都查询出来了

因为内连接的特性，只有那些在 employees、departments 和 salaries 表中都有匹配值的行才会被包含在结果中

2.左连接

左连接（Left Join）用于从左表（左侧的表）中选择所有的行，同时根据指定的条件将右表（右侧的表）中的匹配行连接起来。如果右表中没有与左表匹配的行，则生成的结果集中右侧的所有列都将包含 NULL 值。

例如：我想要找出所有员工的信息，包括他们所在部门的名称

1
2
3
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

可以看到因为没有为id为4的员工分配部门，作为右表的部门表没有与左表匹配的行，于是为NULL

如果只想要在左表有而右表没有的数据，可以像这样

1
2
3
4
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id is NULL

3.右连接

右连接（Right Join）与左连接类似，不同之处在于右连接会选择右表中的所有行，并将左表中与之匹配的行连接起来。如果左表中没有与右表匹配的行，则生成的结果集中左侧的所有列都将包含 NULL 值。

例如：我想要找出所有部门的信息，以及分配给该部门的员工的名字和姓氏

1
2
3
SELECT e.first_name, e.last_name, d.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d ON d.department_id = e.department_id;

可以看到因为没有为id为3的部门分配员工，作为左表的员工表没有与右表匹配的行，于是为NULL

4.上下表连接

上下连接（Full Outer Join）会选择两个表中的所有行，并将它们连接起来。如果某个表中的行在另一个表中没有匹配行，则在结果集中将包含 NULL 值。这种连接可以用于找出两个表之间的所有匹配行，并显示它们的数据。

我们在 salarie 表中为一个不存在的员工添加工资信息

现在我想要找出每个员工所在部门的名称以及其工资

由于 MYSQL 并不支持 Full Outer Join 所以我们使用 UNION 模拟实现

UNION 会将重复数据去除，如果不想去除可以使用 UNION ALL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT e.first_name, e.last_name, d.department_name, s.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN salaries s ON e.employee_id = s.employee_id
UNION
SELECT e.first_name, e.last_name, d.department_name, s.salary
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
LEFT JOIN salaries s ON e.employee_id = s.employee_id
UNION
SELECT e.first_name, e.last_name, d.department_name, s.salary
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
RIGHT JOIN salaries s ON e.employee_id = s.employee_id;

转换为Full Outer Join语法为

1
2
3
4
SELECT e.first_name, e.last_name, d.department_name, s.salary
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id
FULL OUTER JOIN salaries s ON e.employee_id = s.employee_id