【必知必會的MySQL知識】③DML語言

xiezhr發表於2023-05-02

前言

前面的兩篇文章中，我們已經對MySQL有了基本瞭解。
並且知道了怎麼用工具連線資料庫？怎麼建立資料庫？怎麼建立表？
這一篇呢我們就來看看怎麼在我們建立的表中插入資料、刪除資料和修改資料。也就是上一篇文章中提到的DML 資料操作語言

準備

根據上一章所說的，我們建立一個db_xiezhr 資料庫，並向資料庫中新增一張使用者資訊表。

① 透過命令列建庫，建表

mysql> create database if not exists db_xiezhr;
Query OK, 1 row affected (0.00 sec)

mysql> use db_xiezhr
Database changed
mysql> drop table if exists user_profile;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `user_profile` 
				   (`id` int NOT NULL,
				   `device_id` int NOT NULL,
				   `user_name` varchar(100) NOT NULL ,
				   `gender` varchar(14) NOT NULL,
				   `age` int ,
				   `user_email` varchar(50) ,
				   `user_zip` varchar(10),
				   `university` varchar(32) NOT NULL,
				   `province` varchar(32)  NOT NULL,);
Query OK, 0 rows affected (0.01 sec)

② 當然了，我們可以透過上一篇文章中說到的工具之一建庫、建表

這裡以sqlyog工具為例，建立db_xiezhr資料庫，並建立用user_profile戶資訊表

建立db_xiezhr資料庫
建立用user_profile戶資訊表

以上，我們資料庫和資料表就都已經建立成功了

插入資料

語法格式

#方式一：
INSERT INTO 表名 VALUES(值,...);
#方式二：
INSERT INTO 表名(欄位名,...) VALUES(值,...);
#方式三：
INSERT INTO 表名(欄位名,...) SELECT (欄位名,...) FROM 表名;

插入完整行資料

①簡單寫法

儲存到每個裡表中的資料在VALUES子句中給出，值得順序也必須要與定義時候的欄位順序一致，沒有的值需要使用NULL。
這種寫法簡單，但並不安全，應儘量避免使用。一旦表結構變化了，可能就會出問題

# 向user_profile使用者資訊表中新增一條資料
insert into user_profile 
values( 
	1,
	1,
	'張三',
	'male',
	'28',
	null,
	null,
	'北京大學',
	'BeiJing'
);

② 安全寫法

跟上面簡單寫法效果一樣，往使用者資訊表中插入一條資料
在表名後括號中明確給出了列名，在插入資料時，將用VALUES列表中的相應值填入對應的列名中。第一個列名對應第一個值，第二個列名對應第二個值......
因為提供了列名，所以值也不需要按照表中的次序來插入。即使表結構變化了，sql語句也能正常執行。
不需要插入的列user_email和user_zip 列，值也可以不用寫出來了，也比較靈活

INSERT INTO `db_xiezhr`.`user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    1,
    1,
    '張三',
    'male',
    '28',
    '北京大學',
    'BeiJing'
  ) ;

注：

不管使用哪種insert語法，都必須給出values的正確數目。
如果不提供列名，則必須給每個列都提供一個值
如果提供列名，則必須對每個寫出的列提供一個值
在insert語法中省略某列時，需要具備兩個條件，一是該列被定義為允許NULL值二是表定義中給出了預設值

插入多行資料

這裡你可能說我多寫幾條sql語句不就可以了麼，哈哈，這也可以實現了麼？

INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    2,
    2,
    '李四',
    'male',
    '36',
    '天津大學',
    'TianJin'
  ) ;
  
INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    3,
    3,
    '王五',
    'female',
    '25',
    '天津大學',
    'TianJin'
  ) ;

但這裡提供另一種語法,一條語句就可以完成


INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    2,
    2,
    '李四',
    'male',
    '36',
    '天津大學',
    'TianJin'
  ),
  (
    3,
    3,
    '王五',
    'female',
    '25',
    '天津大學',
    'TianJin'
  ) ;

將檢索出來的資料插入表

這裡有兩張表，一張是user_profile，資料如下
在這裡插入圖片描述
一張表是tmp_user_profile，資料如下

我們需要將tmp_user_profile 表中資料插入到user_profile 表中,可以透過如下指令碼實現

INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
SELECT 
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province` 
  FROM tmp_user_profile;

執行完上面指令碼後，user_profile表中資料
在這裡插入圖片描述

更新資料

準備兩張表

下面我們建兩張表，一張表為 product 表，用來存放產品資訊，其中有產品價格欄位 saleprice；另外一張表是 product_price 表。

# 產品資訊表product
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+--------+
| id | productid | productname                                | saleprice | author |
+----+-----------+--------------------------------------------+-----------+--------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       100 | xiezhr |
|  2 | 10002     | 公眾號XiezhrSpace【MySQL從入門到放棄】     |        99 | xiezhr |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr |
|  5 | 10005     | 公眾號XiezhrSpace【Idea從入門到上癮】      |       200 | xiezhr |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+--------+
6 rows in set (0.02 sec)

# 產品價格表product_price 
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |  NULL |
|  2 | 10002     |  NULL |
|  3 | 10003     |  NULL |
|  4 | 10004     |  NULL |
|  5 | 10005     |  NULL |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

語法

# 1、單表更新
UPDATE 表名 SET 列 = 值,... WHERE 查詢條件;

# 2、根據一張表更新另一張表
# ① 使用update
UPDATE 
  表1 別名,
  表2 別名 
SET
  列 = 值,
  ...
WHERE 連線條件 AND 篩選條件 ;
# ②透過INNER JOIN
UPDATE
  表1 別名
INNER JOIN 表2 別名
ON 連線條件 AND 篩選條件
SET
  列 = 值,
  ...；
# ③ 透過LEFT JOIN
UPDATE
  表1 別名
LEFT JOIN  表2 別名
ON 連線條件 AND 篩選條件
SET
  列 = 值,
  ...；
# ③ 透過子查詢
UPDATE
  表2 別名
SET 列 = (SELECT 表示式 FROM 表1 WHERE 連線條件 AND 篩選條件)；

# 2、同時更新兩張表
UPDATE
  表1
INNER JOIN 表2
ON 連線條件 AND 篩選條件
SET 表1.列=值1,
	表2.列=值2;

實踐操作

① 將產品資訊表product 中10001 號產品價格更新為999

mysql> UPDATE product t SET t.`saleprice` =999 WHERE t.`productid` = '10001';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+--------+
| id | productid | productname                                | saleprice | author |
+----+-----------+--------------------------------------------+-----------+--------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       999 | xiezhr |
|  2 | 10002     | 公眾號XiezhrSpace【MySQL從入門到放棄】     |        99 | xiezhr |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr |
|  5 | 10005     | 公眾號XiezhrSpace【Idea從入門到上癮】      |       200 | xiezhr |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+--------+
6 rows in set (0.00 sec)

② 將 product_price 表中的價格欄位 price 更新為 product 表中價格欄位 price 的 80%。

# 使用“UPDATE table1 t1,table2,...,table n”的方式來多表更新
mysql> UPDATE product t1, product_price t2 SET t2.price = t1.`saleprice` * 0.8 WHERE t1.productid= t2.productId;
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 2

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

# 透過INNER JOIN
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
#  透過LEFT JOIN
mysql> UPDATE product t1 LEFT JOIN product_price t2 ON t1.productid= t2.productid SET t2.price = t1.`saleprice` * 0.8 WHERE t1.productid='10001';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |  NULL |
|  3 | 10003     |  NULL |
|  4 | 10004     |  NULL |
|  5 | 10005     |  NULL |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
# 透過子查詢
mysql> UPDATE product_price t2 SET t2.price=(SELECT t1.`saleprice` *0.8 FROM product t1 WHERE t1.productid = t2.productid);
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Rows matched: 6  Changed: 5  Warnings: 2

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

③ 同時更新兩張表(正式開發中用得比較少)

兩張表做關聯，同時更新了 product_price 表的 price 欄位和 product 表的 author兩個欄位。

mysql> UPDATE product t1 INNER JOIN product_price t2 ON t1.productid= t2.productid SET t2.price = t1.`saleprice` * 0.8, t1.`author` = 'xiezhr001';
Query OK, 5 rows affected, 2 warnings (0.00 sec)
Rows matched: 10  Changed: 5  Warnings: 2

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       100 | xiezhr001 |
|  2 | 10002     | 公眾號XiezhrSpace【MySQL從入門到放棄】     |        99 | xiezhr001 |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr001 |
|  5 | 10005     | 公眾號XiezhrSpace【Idea從入門到上癮】      |       200 | xiezhr001 |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
6 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

刪除資料

語法

# 1、單表刪除 
DELETE FROM 表名 【WHERE 篩選條件 】;

# 2、多表刪除（級聯刪除）
① 關聯刪除
DELETE 
  表1的別名,
  表2的別名 
FROM
  表1 別名,
  表2 別名 
WHERE 連線條件 AND 篩選條件 ;
② 內連線、左右連線刪除
DELETE 
  表1的別名,
  表2的別名 
FROM
  表1 別名 
INNER | LEFT | RIGHT JOIN 表2 別名 ON 連線條件 
WHERE 篩選條件 ;

實踐操作

① 單表刪除

# 刪除產品id為10005的產品資訊
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       100 | xiezhr001 |
|  2 | 10002     | 公眾號XiezhrSpace【MySQL從入門到放棄】     |        99 | xiezhr001 |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr001 |
|  5 | 10005     | 公眾號XiezhrSpace【Idea從入門到上癮】      |       200 | xiezhr001 |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
6 rows in set (0.00 sec)

mysql> delete from product where productid = '10005';
Query OK, 1 row affected (0.02 sec)

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       100 | xiezhr001 |
|  2 | 10002     | 公眾號XiezhrSpace【MySQL從入門到放棄】     |        99 | xiezhr001 |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
5 rows in set (0.00 sec)

② 多表級聯刪除

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       100 | xiezhr001 |
|  2 | 10002     | 公眾號XiezhrSpace【MySQL從入門到放棄】     |        99 | xiezhr001 |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
5 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

mysql> DELETE  t1,  t2 FROM  product t1,  product_price t2 WHERE t1.`productid` = t2.`productid` AND t1.`productid` = '10002';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       100 | xiezhr001 |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】    |       100 | xiezhr001 |
|  3 | 10003     | 公眾號XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> DELETE   t1,  t2 FROM  product_price t2 INNER JOIN product t1 ON t1.`productid` = t2.`productid` WHERE t1.`productid` = '10003';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from product;
+----+-----------+-------------------------------------------+-----------+-----------+
| id | productid | productname                               | saleprice | author    |
+----+-----------+-------------------------------------------+-----------+-----------+
|  1 | 10001     | 公眾號XiezhrSpace【Oralce從入門到放棄】   |       100 | xiezhr001 |
|  4 | 10004     | 公眾號XiezhrSpace【Java從入門到精通】     |       150 | xiezhr001 |
|  6 | 10006     | 公眾號XiezhrSpace【如何快速搭建個人部落格】 |       120 | xiezhr    |
+----+-----------+-------------------------------------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
4 rows in set (0.00 sec)

小結

這篇文章主要說了MySQL中單表多表的增刪改，在update、delete 使用的時候一定要細心
除非打算更新刪除表中所有資料，否則絕對不要使用不帶where子句的update或delete語句
保證每個表都有主鍵
在對update或delete語句使用where子句之前，先用select進行查詢測試，保證過濾出來的記錄是正確的；
update或delete語句執行前，儘量做好資料備份

【必知必會的MySQL知識】④DCL語言
2023-05-03
MySql
【必知必會的MySQL知識】⑤DQL語言
2023-05-04
MySql
【必知必會的MySQL知識】①初探MySQL
2023-05-01
MySql
【必知必會的MySQL知識】②使用MySQL
2023-05-02
MySql
【必知必會的MySQL知識】mysql5.7安裝教程
2023-05-04
MySql
MySQL 必知必會
2019-04-13
MySql
MySQL必知必會筆記——查詢的基礎知識
2021-06-11
MySql筆記
MYSQL中的鎖必知必會
2020-12-21
MySql
mysql必知必會筆記
2020-02-10
MySql筆記
01-mysql必知必會
2021-09-21
MySql
Mysql必知必會練習
2020-09-26
MySql
Java 基礎知識點（必知必會其一）
2015-03-04
Java
Docker 必知必會1----初識
2024-05-07
Docker
大前端開發人員必知必會的HTTP知識
2019-04-09
前端HTTP
必須懂的mysql知識
2018-12-27
MySql
《MySQL必知必會》萬用字元 ( like , % , _ ,)
2020-08-16
MySql字元
MySQL必知必會》正規表示式
2020-08-23
MySql
MySQL必知必會詳細總結
2020-12-10
MySql
MySQL必知必會(1-12章)
2020-09-23
MySql
《Mysql必知必會》讀書筆記
2018-03-25
MySql筆記
git必會必知
2019-04-08
Git
Activity 必知必會
2019-03-04
Redis 必知必會
2019-03-13
Redis
Elasticsearch必知必會的乾貨知識二：ES索引操作技巧
2020-07-27
Elasticsearch索引
必知必會——SQL語句基本語法整理
2024-03-14
SQL
HTTP 必知必會的那些
2019-03-04
HTTP
《MySQL 必知必會》C25-C28
2020-11-02
MySql
ThreadLocal必知必會
2020-05-22
thread
Linux必會必知
2019-01-19
Linux
JSON 必知必會
2018-04-04
JSON
Megaraid 必知必會
2010-12-06
AI
notion database 必知必會
2024-03-13
Database
精選Spring Boot三十五道必知必會知識點！
2019-03-13
Spring Boot
Nacos必知必會：這些知識點你一定要掌握！
2023-05-15
12.MySQL必知必會之分組資料
2018-05-29
MySql
運維必知必會的監控知識體系全梳理總結
2017-09-06
運維
必備知識
2013-08-21
面試必知的web知識點
2019-07-25
面試Web

【必知必會的MySQL知識】③DML語言

前言

準備

插入資料

語法格式

插入完整行資料

插入多行資料

將檢索出來的資料插入表

更新資料

準備兩張表

語法

實踐操作

刪除資料

語法

實踐操作

小結

相關文章