MySQL update使用select的结果

柔情只为你懂 2021-09-12 01:44 378阅读 0赞

### 1. MySQL UPDATE JOIN语法 ###

在MySQL中，可以在 UPDATE语句 中使用`JOIN`子句执行跨表更新。MySQL UPDATE JOIN的语法如下：

UPDATE T1
    [INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2. C1
    SET T1.C2 = T2.C2, 
        T2.C3 = expr
    WHERE condition

更详细地看看MySQL `UPDATE JOIN`语法：

*  首先，在`UPDATE`子句之后，指定主表(`T1`)和希望主表连接表(`T2`)。
 *  第二，指定一种要使用的连接，即`INNER JOIN`或`LEFT JOIN`和连接条件。`JOIN`子句必须出现在`UPDATE`子句之后。
 *  第三，要为要更新的`T1`和/或`T2`表中的列分配新值。
 *  第四，WHERE子句中的条件用于指定要更新的行。

### 2. 示例 ###

首先，我们将在这些例子中使用一个新的示例数据库(`empdb`)。示例数据库包含2个表：

*  `employees`表将存储在员工编号，姓名，工作表现和工资的数据。
 *  `merits`表存储员工绩效和绩效百分比。  
    以下语句在 `empdb` 示例数据库中创建表并导入数据：

CREATE DATABASE IF NOT EXISTS empdb;
    
    USE empdb;
    -- create tables
    CREATE TABLE merits (
        performance INT(11) NOT NULL,
        percentage FLOAT NOT NULL,
        PRIMARY KEY (performance)
    );
    
    CREATE TABLE employees (
        emp_id INT(11) NOT NULL AUTO_INCREMENT,
        emp_name VARCHAR(255) NOT NULL,
        performance INT(11) DEFAULT NULL,
        salary FLOAT DEFAULT NULL,
        PRIMARY KEY (emp_id),
        CONSTRAINT fk_performance FOREIGN KEY (performance)
            REFERENCES merits (performance)
    );
    
    -- insert data for merits table
    INSERT INTO merits(performance,percentage)
    VALUES(1,0),
          (2,0.01),
          (3,0.03),
          (4,0.05),
          (5,0.08);
    
    -- insert data for employees table
    INSERT INTO employees(emp_name,performance,salary)      
    VALUES('Mary Doe', 1, 50000),
          ('Cindy Minsu', 3, 65000),
          ('Sue Greenspan', 4, 75000),
          ('Grace Dell', 5, 125000),
          ('Nancy Johnson', 3, 85000),
          ('John Doe', 2, 45000),
          ('Lily Bush', 3, 55000);

#### 2.1 使用INNER JOIN子句的MySQL UPDATE JOIN示例 ####

假设想根据员工的工作表现来调整员工的工资。  
因此，优点百分比存储在 `merits` 表中，必须使用 `UPDATE INNER JOIN` 语句根据存储在 `merits` 表中的百分比来调整 `employees` 表中员工的工资。  
`employees` 和 `merits` 表之间以是 `performance` 字段相关联的。 请参阅以下查询：

UPDATE employees
            INNER JOIN
        merits ON employees.performance = merits.performance 
    SET 
        salary = salary + salary * percentage;

上面查询语句的工作原理是什么？  
我们仅在 `UPDATE` 子句之后指定 `employees` 表，因为我们只想更新 `employees` 表中的数据。  
对于 `employees` 表中的每一行，查询根据 `merits` 表中 `performance` 列中的值来检查 `employees` 表中的 `performance` 列中的值。 如果找到一个匹配，它将获得 `merits` 表中的百分比，并更新 `employees` 表中的 `salary`列。

mysql> select * from employees; -- 更新之前的数据
    +--------+---------------+-------------+--------+
    | emp_id | emp_name      | performance | salary |
    +--------+---------------+-------------+--------+
    |      1 | Mary Doe      |           1 |  50000 |
    |      2 | Cindy Minsu   |           3 |  65000 |
    |      3 | Sue Greenspan |           4 |  75000 |
    |      4 | Grace Dell    |           5 | 125000 |
    |      5 | Nancy Johnson |           3 |  85000 |
    |      6 | John Doe      |           2 |  45000 |
    |      7 | Lily Bush     |           3 |  55000 |
    +--------+---------------+-------------+--------+
    7 rows in set
    
    mysql> UPDATE employees
            INNER JOIN
        merits ON employees.performance = merits.performance 
    SET 
        salary = salary + salary * percentage; -- 执行连接更新
    Query OK, 6 rows affected
    Rows matched: 7  Changed: 6  Warnings: 0
    
    mysql> select * from employees; -- 更新之后的数据
    +--------+---------------+-------------+--------+
    | emp_id | emp_name      | performance | salary |
    +--------+---------------+-------------+--------+
    |      1 | Mary Doe      |           1 |  50000 |
    |      2 | Cindy Minsu   |           3 |  66950 |
    |      3 | Sue Greenspan |           4 |  78750 |
    |      4 | Grace Dell    |           5 | 135000 |
    |      5 | Nancy Johnson |           3 |  87550 |
    |      6 | John Doe      |           2 |  45450 |
    |      7 | Lily Bush     |           3 |  56650 |
    +--------+---------------+-------------+--------+
    7 rows in set

因为省略了 `UPDATE` 语句中的 `WHERE` 子句，所以 `employees`表中的所有记录都被更新。如果需要 `performance` 等级大于1的员工才更新薪资，那么 `sql` 可以这样写：

UPDATE employees
            INNER JOIN
        merits ON employees.performance = merits.performance 
    SET 
        salary = salary + salary * percentage
    WHERE employees.performance > 1;

#### 使用LEFT JOIN的MySQL UPDATE JOIN示例 ####

假设公司又雇用了两名新员工：

INSERT INTO employees(emp_name,performance,salary)
    VALUES('Jack William',NULL,43000),
          ('Ricky Bond',NULL,52000);

因为这些员工是新员工，所以他们的绩效(`performance`)数据不可用或为`NULL`。现在  
`employees` 表中的数据，如下所示：

mysql> SELECT * FROM employees;
    +--------+---------------+-------------+--------+
    | emp_id | emp_name      | performance | salary |
    +--------+---------------+-------------+--------+
    |      1 | Mary Doe      |           1 |  50000 |
    |      2 | Cindy Minsu   |           3 |  66950 |
    |      3 | Sue Greenspan |           4 |  78750 |
    |      4 | Grace Dell    |           5 | 135000 |
    |      5 | Nancy Johnson |           3 |  87550 |
    |      6 | John Doe      |           2 |  45450 |
    |      7 | Lily Bush     |           3 |  56650 |
    |      8 | Jack William  | NULL        |  43000 |
    |      9 | Ricky Bond    | NULL        |  52000 |
    +--------+---------------+-------------+--------+
    9 rows in set

要计算新员工的工资，不能使用 `UPDATE INNER JOIN` 语句（为什么不能，可参考[sql之left join、right join、inner join的区别][sql_left join_right join_inner join]），因为它们的绩效数据在 `merits`表中不可用。这就是为什么要使用 `UPDATE LEFT JOIN` 来实现了。  
当 `UPDATE LEFT JOIN` 语句在另一个表中没有相应行时，就会更新表中的一行。  
例如，可以使用以下语句将新雇员的工资增加1.5％：

UPDATE employees
            LEFT JOIN
        merits ON employees.performance = merits.performance 
    SET 
        salary = salary + salary * 0.015
    WHERE
        merits.percentage IS NULL;

执行结果如下：

mysql> UPDATE employees
            LEFT JOIN
        merits ON employees.performance = merits.performance 
    SET 
        salary = salary + salary * 0.015
    WHERE
        merits.percentage IS NULL;
    Query OK, 2 rows affected
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> select * from employees;
    +--------+---------------+-------------+--------+
    | emp_id | emp_name      | performance | salary |
    +--------+---------------+-------------+--------+
    |      1 | Mary Doe      |           1 |  50000 |
    |      2 | Cindy Minsu   |           3 |  66950 |
    |      3 | Sue Greenspan |           4 |  78750 |
    |      4 | Grace Dell    |           5 | 135000 |
    |      5 | Nancy Johnson |           3 |  87550 |
    |      6 | John Doe      |           2 |  45450 |
    |      7 | Lily Bush     |           3 |  56650 |
    |      8 | Jack William  | NULL        |  43645 |
    |      9 | Ricky Bond    | NULL        |  52780 |
    +--------+---------------+-------------+--------+
    9 rows in set

### 扩展阅读 ###

[sql之left join、right join、inner join的区别][sql_left join_right join_inner join]

[sql_left join_right join_inner join]: https://www.jianshu.com/p/8edeed16cdd6