当前位置：网站首页>Tutorial on principles and applications of database system (027) -- MySQL modifying data in tables (III): update

Tutorial on principles and applications of database system (027) -- MySQL modifying data in tables (III): update

2022-07-21 01:26:00 【Rsda DBA_ WGX】

Database system principle and Application Tutorial （027）—— MySQL Modify the data in the table （ 3、 ... and ）： Change （update）

Catalog

Use UPDATE The command can update the data in the table , The syntax format of the command is as follows ：

UPDATE  Table name 
    SET  Name  =  expression  [,  Name  =  expression  ...]
    [WHERE  Conditions ]
    [ORDER BY ...]
    [LIMIT row_count]
/*  explain ： （1） Use  set  Parameter specifies a new data value for a field . （2）ORDER BY： Update the rows in the specified order . （3）LIMIT： Limit the number of rows that can be updated . */

UPDATE  surface 1 join  surface 2 on  surface 1. Name  =  surface 2. Name 
    SET  Name  =  expression  [,  Name  =  expression  ...]
    [WHERE  Conditions ]
/*  explain ： （1） utilize   surface 2  Data update in   surface 1, It is required that two tables must have associated fields . （2） surface 1  and   surface 2  The associated condition is   surface 1. Name  =  surface 2. Name . */

One 、 Update the data values in the table that meet the specified conditions

emp The data in the table are as follows ：

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id |
+------+--------------+--------+---------------------+---------+-------------+---------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3265.00 | 13072664666 | D02     |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 3980.00 | 13603733333 | D03     |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 |    NULL | 13603735555 | D03     |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 |    NULL | 13603736666 | D03     |
+------+--------------+--------+---------------------+---------+-------------+---------+
8 rows in set (0.18 sec)

1、 Update the data values of all records in the table that meet the specified conditions

for example ：

（1） hold salary Of the two records listed as empty salary Set to 3000

mysql> update emp set salary = 3000 where salary is null;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id |
+------+--------------+--------+---------------------+---------+-------------+---------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3265.00 | 13072664666 | D02     |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 3980.00 | 13603733333 | D03     |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 | 3000.00 | 13603735555 | D03     |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 | 3000.00 | 13603736666 | D03     |
+------+--------------+--------+---------------------+---------+-------------+---------+
8 rows in set (0.00 sec)

（2） hold 【D02】 Employees of the Department salary increase 20%

mysql> update emp set salary = salary * 1.2 where dept_id='D02';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id |
+------+--------------+--------+---------------------+---------+-------------+---------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3918.00 | 13072664666 | D02     |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 3980.00 | 13603733333 | D03     |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 | 3000.00 | 13603735555 | D03     |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 | 3000.00 | 13603736666 | D03     |
+------+--------------+--------+---------------------+---------+-------------+---------+
8 rows in set (0.00 sec)

2、 Update the data value of the specified number of records

（1） hold salary The smallest two records salary increase 1000

mysql> update emp set salary = salary + 1000 order by salary limit 2;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id |
+------+--------------+--------+---------------------+---------+-------------+---------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3918.00 | 13072664666 | D02     |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 3980.00 | 13603733333 | D03     |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603735555 | D03     |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603736666 | D03     |
+------+--------------+--------+---------------------+---------+-------------+---------+
8 rows in set (0.00 sec)

（2） hold 【D03】 department salary Lowest employee salary increase 1000

mysql> update emp set salary = salary + 1000 where dept_id = 'D03' order by salary limit 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id |
+------+--------------+--------+---------------------+---------+-------------+---------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3918.00 | 13072664666 | D02     |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 4980.00 | 13603733333 | D03     |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603735555 | D03     |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603736666 | D03     |
+------+--------------+--------+---------------------+---------+-------------+---------+
8 rows in set (0.00 sec)

Two 、 Update the current table with data from another table

The syntax is as follows ：

UPDATE  surface 1 join  surface 2 on  surface 1. Name  =  surface 2. Name 
    SET  Name  =  expression  [,  Name  =  expression  ...]
    [WHERE  Conditions ]
/*  explain ： （1） utilize   surface 2  Data update in   surface 1, It is required that two tables must have associated fields . （2） surface 1  and   surface 2  The associated condition is   surface 1. Name  =  surface 2. Name . */

for example ： There are two data sheets as follows ：

mysql> select * from dept;
+---------+-----------+--------------+
| dept_id | dept_name | phone        |
+---------+-----------+--------------+
| D01     |  Finance Department     | 0373-3087111 |
| D02     |  The sales department     | 0373-3087222 |
| D03     |  R & D department     | 0373-3087333 |
| D04     |  After sales department     | 0373-3087444 |
| D05     |  Public relations     | NULL         |
+---------+-----------+--------------+
5 rows in set (0.01 sec)

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id |
+------+--------------+--------+---------------------+---------+-------------+---------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3918.00 | 13072664666 | D02     |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 4980.00 | 13603733333 | D03     |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603735555 | D03     |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603736666 | D03     |
+------+--------------+--------+---------------------+---------+-------------+---------+
8 rows in set (0.00 sec)

（1） stay emp Add columns to the table dept_name

mysql> alter table emp add dept_name char(20);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+-----------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id | dept_name |
+------+--------------+--------+---------------------+---------+-------------+---------+-----------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     | NULL      |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3918.00 | 13072664666 | D02     | NULL      |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     | NULL      |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     | NULL      |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     | NULL      |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 4980.00 | 13603733333 | D03     | NULL      |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603735555 | D03     | NULL      |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603736666 | D03     | NULL      |
+------+--------------+--------+---------------------+---------+-------------+---------+-----------+
8 rows in set (0.00 sec)

（2） use dept Column in table dept_name to update emp Tabular dept_name Column 

/* update emp e join dept d on e.dept_id = d.dept_id set e.dept_name = d.dept_name ; */

mysql> update emp e join dept d on e.dept_id = d.dept_id
    -> set e.dept_name = d.dept_name 
    -> ;
Query OK, 8 rows affected (0.06 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> select * from emp;
+------+--------------+--------+---------------------+---------+-------------+---------+-----------+
| e_id | e_name       | gender | jobtime             | salary  | phone       | dept_id | dept_name |
+------+--------------+--------+---------------------+---------+-------------+---------+-----------+
| 1001 |  zhang          |  male      | 2018-06-25 00:00:00 | 4950.00 | 13637354888 | D01     |  Finance Department     |
| 1003 |  Liu Jingjing        |  Woman      | 2020-07-15 00:00:00 | 3918.00 | 13072664666 | D02     |  The sales department     |
| 1005 |  Ouyang Honghai      |  male      | 2018-04-28 00:00:00 | 4821.00 | 13937356588 | D03     |  R & D department     |
| 2001 |  Guan yu          |  male      | 2020-07-30 00:00:00 | 4850.00 | 13603731111 | D03     |  R & D department     |
| 2002 |  Liu Bei          |  Woman      | 2018-07-18 00:00:00 | 4250.00 | 13603732222 | D03     |  R & D department     |
| 2003 |  Zhang Fei          |  Woman      | 2018-06-20 00:00:00 | 4980.00 | 13603733333 | D03     |  R & D department     |
| 3001 |  Lin Chong          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603735555 | D03     |  R & D department     |
| 3002 |  Song Jiang          |  male      | 2022-07-15 00:00:00 | 4000.00 | 13603736666 | D03     |  R & D department     |
+------+--------------+--------+---------------------+---------+-------------+---------+-----------+
8 rows in set (0.00 sec)
原网站

版权声明
本文为[Rsda DBA_ WGX]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/202/202207200143492326.html

边栏推荐

猜你喜欢

随机推荐