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

更详细地看看mysql update join语法:

首先,在update子句之后,指定主表(t1)和希望主表连接表(t2)。

第二,指定一种要使用的连接,即inner joinleft 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 表中员工的工资。
employeesmerits 表之间以是 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 语句(为什么不能,可参考),因为它们的绩效数据在 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

到此这篇关于mysql联表update数据的示例详解的文章就介绍到这了,更多相关mysql联表update数据内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐