本篇文章小编给大家分享一下Mysql联表update数据代码示例解析,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
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个表:
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表,因为我们只想更新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;
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。现在
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的区别),因为它们的绩效数据在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
示例
# 单表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=cor.order_no JOIN bbs_uhome_card_activate ca on cor.card_no=ca.card_no set co.create_user=ca.create_user