SQL 語法 UPDATE

測試環境 CentOS 8 (虛擬機)

這邊來使用一下 SQL UPDATE 的語法

先建立一個資料庫 (sbtest) , 與 tables (employee) 格式為 NName VARCHAR(20), Dept VARCHAR(20), jobTitle VARCHAR(20) 各 20 個字元.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE sbtest;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> USE sbtest;
Database changed

MariaDB [sbtest]> CREATE TABLE employee (Name VARCHAR(20),Dept VARCHAR(20),jobTitle VARCHAR(20));
Query OK, 0 rows affected (0.005 sec)

MariaDB [sbtest]> DESCRIBE employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Name     | varchar(20) | YES  |     | NULL    |       |
| Dept     | varchar(20) | YES  |     | NULL    |       |
| jobTitle | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.003 sec)

新增資料如下.

MariaDB [sbtest]> INSERT INTO employee VALUES ('Ben','Testing','Engineer') , ('Afa','Power','PM'), ('Boss','Testing','Manager') , ('Cars','Senior Engineer' ,'Testing');
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

透過 SELECT 來檢視資料.

MariaDB [sbtest]> SELECT * FROM employee;
+------+-----------------+----------+
| Name | Dept            | jobTitle |
+------+-----------------+----------+
| Ben  | Testing         | Engineer |
| Afa  | Power           | PM       |
| Boss | Testing         | Manager  |
| Cars | Senior Engineer | Testing  |
+------+-----------------+----------+
4 rows in set (0.000 sec)

UPDATE

Ben jobtile 邊更為資深工程師 Senior Engineer,這時候我們可以透過 UPDATE 來更新資料 (搭配 WHERE NAME 來使用).

MariaDB [sbtest]> UPDATE employee SET jobTitle='Senior Engineer' WHERE name='Ben';
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [sbtest]> SELECT * FROM employee;
+------+-----------------+-----------------+
| Name | Dept            | jobTitle        |
+------+-----------------+-----------------+
| Ben  | Testing         | Senior Engineer |
| Afa  | Power           | PM              |
| Boss | Testing         | Manager         |
| Cars | Senior Engineer | Testing         |
+------+-----------------+-----------------+
4 rows in set (0.000 sec)

如果是同時要更新多欄位,直接指定欄位與數值即可.

MariaDB [sbtest]> UPDATE employee SET Dept='Hardware Testing' WHERE Dept='Testing';
Query OK, 2 rows affected (0.001 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [sbtest]> SELECT * FROM employee;
+------+------------------+-----------------+
| Name | Dept             | jobTitle        |
+------+------------------+-----------------+
| Ben  | Hardware Testing | Senior Engineer |
| Afa  | Power            | PM              |
| Boss | Hardware Testing | Manager         |
| Cars | Senior Engineer  | Testing         |
+------+------------------+-----------------+
4 rows in set (0.000 sec)

如果是要變更原資料的部分字串時,可以透過 REPLACE 函數來使用.

MariaDB [sbtest]> UPDATE employee SET Dept=REPLACE(Dept , 'Testing' , 'Validation' );
Query OK, 2 rows affected (0.001 sec)
Rows matched: 4  Changed: 2  Warnings: 0

MariaDB [sbtest]> SELECT * FROM employee;
+------+---------------------+-----------------+
| Name | Dept                | jobTitle        |
+------+---------------------+-----------------+
| Ben  | Hardware Validation | Senior Engineer |
| Afa  | Power               | PM              |
| Boss | Hardware Validation | Manager         |
| Cars | Senior Engineer     | Testing         |
+------+---------------------+-----------------+
4 rows in set (0.000 sec)