Mysql联表更新数据代码示例解析

zhaorong · 发表于 2020-11-18 15:30:46

本篇文章小编给大家分享一下Mysql联表更新数据代码示例解析文章代码介绍的很详细小编觉得挺不
错的现在分享给大家供大家参考有需要的小伙伴们可以来看看。

1.MySQL UPDATE JOIN语法

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

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

复制代码

更详细地看看MySQLUPDATE JOIN语法：

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

2.样品

首先我们将在这些示例中使用一个新的示例数据库 empdb 示例数据库包含2个表：
员工表将存储在员工编号姓名工作表现和工资的数据。
优点表存储员工绩效和绩效百分比。
以下语句在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示例

假设想根据员工的工作表现来调整员工的工资。

因此，优秀百分比存储在优点表中必须使用更新INNER JOIN语句根据存储在优点表中的比例来调整员工表中员工的工资。
员工和优点表之间以是绩效与绩效相关联的。请参见以下查询：
上面查询语句的工作原理是什么？
我们仅在UPDATE子句之后指定employees表因为我们只想更新employees表中的数据。
对于雇员表中的每一行查询根据优点表中性能列中的值来检查雇员表中的性能列中的值如果找
到一个匹配则获得优点表中的百分比并更新雇员表中的薪金列。

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表中的所有记录都被更新如果需
要绩效等级大于1的员工才更新薪资那么sql可以这样写：

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

复制代码

2.2使用LEFT JOIN的MySQL UPDATE JOIN示例

假设公司又雇用了替代新员工：

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

复制代码

因为这些员工是新员工所以他们的绩效performance 数据不可用或为NULL。现在

员工表中的数据如下所示：

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之左联接右联接内部联接的区别
因为它们的绩效数据在优点表中不可用。这就是为什么要使用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

复制代码

示例

# 单表join
update bbs_uhome_card_activate ca INNER JOIN bbs_uhome_card_rules cr on ca.card_brach
=cr.card_bach set ca.create_user=cr.create_user;
# 多表join
UPDATE bbs_uhome_card_order co INNER JOIN bbs_uhome_card_order_record cor on co.order_no=c
or.order_no JOIN bbs_uhome_card_activate ca on cor.card_no=ca.card_no set co.c
reate_user=ca.create_user

复制代码

		自动登录	找回密码
密码			注册

[编程和数据库] Mysql联表更新数据代码示例解析