《MySQL 基礎篇》六：表和資料的管理

ACatSmiling發表於2024-09-22
MySql

Author: ACatSmiling

Since: 2024-09-20

建立和管理表

基礎知識

一條資料儲存的過程

儲存資料是處理資料的第一步。只有正確地把資料儲存起來，才能進行有效的處理和分析。否則，只能是一團亂麻，無從下手。

那麼，怎樣才能把使用者各種經營相關的、紛繁複雜的資料，有序、高效地儲存起來呢？ 在 MySQL 中，一個完整的資料儲存過程總共有 4 步，分別是建立資料庫、確認欄位、建立資料表、插入資料。

image-20230430234823812

從系統架構的層次上看，MySQL 資料庫系統從大到小依次是資料庫伺服器、資料庫、資料表、資料表的行與列。

識別符號命名規則

  • 資料庫名、表名不得超過 30 個字元，變數名限制為 29 個。
  • 必須只能包含 A ~ Z，a ~ z，0 ~ 9，_ 共 63 個字元。
  • 資料庫名、表名、欄位名等物件名中間不要包含空格。
  • 同一個 MySQL 軟體中，資料庫不能同名；同一個庫中，表不能重名；同一個表中，欄位不能重名，應避免欄位和保留字、資料庫系統或常用方法名衝突。如果需要使用，在 SQL 語句中使用 `（著重號）引起來。
  • 保持欄位名和型別的一致性：在命名欄位併為其指定資料型別的時候一定要保證一致性，假如資料型別在一個表裡是整數，那在另一個表裡也應該是整數。

建立和管理資料庫

建立資料庫

# 方式一：建立資料庫
mysql> CREATE DATABASE 資料庫名;

# 方式二：建立資料庫並指定字符集
mysql> CREATE DATABASE 資料庫名 CHARACTER SET 字符集;

# 方式三：判斷資料庫是否已經存在, 不存在則建立資料庫（推薦）
mysql> CREATE DATABASE IF NOT EXISTS 資料庫名;

如果 MySQL 中已經存在相關的資料庫，則忽略建立語句，不再建立資料庫。

注意：DATABASE 不能改名。一些視覺化工具可以改名，它是建新庫，把所有表複製到新庫，再刪舊庫完成的。

使用資料庫

# 檢視當前所有的資料庫
mysql> SHOW DATABASES;

# 檢視當前正在使用的資料庫
mysql> SELECT DATABASE();

# 檢視指定庫下所有的表
mysql> SHOW TABLES FROM 資料庫名;

# 檢視資料庫的建立資訊
mysql> SHOW CREATE DATABASE 資料庫名;
mysql> SHOW CREATE DATABASE 資料庫名\G

# 檢視某張表屬於哪個庫
mysql> SELECT table_schema FROM information_schema.TABLES WHERE table_name = 表名;

# 使用/切換資料庫
mysql> USE 資料庫名;

要操作表格和資料之前必須先說明是對哪個資料庫進行操作，否則就要對所有物件加上 "資料庫名."。

修改資料庫

# 更改字符集，比如：gbk、utf8 等
mysql> ALTER DATABASE 資料庫名 CHARACTER SET 字符集;

刪除資料庫

# 刪除指定資料庫
mysql> DROP DATABASE 資料庫名;

# 刪除指定資料庫（推薦）
mysql> DROP DATABASE IF EXISTS 資料庫名;

建立表

建立方式一

必備條件：

  • CREATE TABLE 許可權；
  • 儲存空間。

語法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
欄位1, 資料型別 [約束條件] [預設值],
欄位2, 資料型別 [約束條件] [預設值],
欄位3, 資料型別 [約束條件] [預設值],
……
[表約束條件]
);

加上了 IF NOT EXISTS 關鍵字，則表示：如果當前資料庫中不存在要建立的資料表，則建立資料表；如果當前資料庫中已經存在要建立的資料表，則忽略建表語句，不再建立資料表。

必須指定：

  • 表名；
  • 列名或欄位名，資料型別，長度。

可選指定：

  • 約束條件；
  • 預設值。

示例：

-- 建立表
CREATE TABLE emp (
    -- int 型別
    emp_id INT,
    -- 最多儲存 20 箇中英文字元
    emp_name VARCHAR(20),
    -- 總位數不超過 15 位
    salary DOUBLE,
    -- 日期型別
    birthday DATE
);

CREATE TABLE dept(
    -- int 型別，自增
    deptno INT(2) AUTO_INCREMENT,
    dname VARCHAR(14),
    loc VARCHAR(13),
    -- 主鍵
    PRIMARY KEY (deptno)
);

在 MySQL 8.x 版本中，不再推薦為 INT 型別指定顯示長度，並在未來的版本中可能去掉這樣的語法。

建立方式二

使用AS subquery選項，將建立表和插入資料結合起來：

image-20230502230312325

  • 指定的列和子查詢中的列要一一對應。
  • 透過列名和預設值定義列。

示例：

mysql> CREATE TABLE emp1 AS SELECT * FROM employees;

# 建立的 emp2 是空表
mysql> CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1 = 2;

mysql> CREATE TABLE dept80 AS SELECT employee_id, last_name, salary * 12 ANNSAL, hire_date FROM employees WHERE department_id = 80;

檢視資料表結構

在 MySQL 中建立好資料表之後，可以檢視資料表的結構。MySQL 支援使用DESCRIBE/DESC語句檢視資料表結構，也支援使用SHOW CREATE TABLE語句檢視資料表結構。

語法格式如下：

SHOW CREATE TABLE 表名\G

使用 SHOW CREATE TABLE 語句不僅可以檢視錶建立時的詳細語句，還可以檢視儲存引擎和字元編碼。

修改表

修改表指的是修改資料庫中已經存在的資料表的結構。

使用ALTER TABLE語句可以實現：

  • 向已有的表中新增列。
  • 修改現有表中的列。
  • 刪除現有表中的列。
  • 重新命名現有表中的列。

追加一個列

語法：

ALTER TABLE 表名 ADD [COLUMN] 欄位名 欄位型別 [FIRST|AFTER 欄位名];

修改一個列

可以修改列的資料型別，長度、預設值和位置。

語法：

ALTER TABLE 表名 MODIFY [COLUMN] 欄位名1 欄位型別 [DEFAULT 預設值] [FIRST|AFTER 欄位名2];

對預設值的修改隻影響今後對錶的修改。

重新命名一個列

語法：

ALTER TABLE 表名 CHANGE [column] 列名 新列名 新資料型別;

刪除一個列

語法：

ALTER TABLE 表名 DROP [COLUMN] 欄位名;

重新命名錶

# 方式一
mysql> RENAME TABLE emp TO myemp;

# 方式二
mysql> ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略

刪除表

在 MySQL 中，當一張資料表沒有與其他任何資料表形成關聯關係時，可以將當前資料表直接刪除。

  • 資料和結構都被刪除。
  • 所有正在執行的相關事務被提交。
  • 所有相關索引被刪除。

語法：

DROP TABLE [IF EXISTS] 資料表1 [, 資料表2, …, 資料表n];
  • IF EXISTS 的含義為：如果當前資料庫中存在相應的資料表，則刪除資料表；如果當前資料庫中不存在相應的資料表，則忽略刪除語句，不再執行刪除資料表的操作。

注意：DROP TABLE 語句不能回滾。

清空表

TRUNCATE TABLE語句：

  • 刪除表中所有的資料。
  • 釋放表的儲存空間。

語法：

TRUNCATE TABLE 表名;

注意：TRUNCATE 語句不能回滾，而使用 DELETE 語句刪除資料，可以回滾。

阿里開發規範：
【參考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日誌資源少，但 TRUNCATE 無事務且不觸發 TRIGGER，有可能造成事故，故不建議在開發程式碼中使用此語句。

說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。

阿里開發規範擴充套件

擴充 1：阿里巴巴《Java 開發手冊》之 MySQL 欄位命名。

  • 【 強制 】表名、欄位名必須使用小寫字母或數字，禁止出現數字開頭，禁止兩個下劃線中間只出現數字。資料庫欄位名的修改代價很大，因為無法進行預釋出，所以欄位名稱需要慎重考慮。

    • 正例：aliyun_admin，rdc_config，level3_name。
    • 反例：AliyunAdmin，rdcConfig，level_3_name。

  • 【 強制 】禁用保留字，如 desc、range、match、delayed 等，請參考 MySQL 官方保留字。

  • 【 強制 】表必備三欄位：id，gmt_create，gmt_modified。

    • 說明：其中 id 必為主鍵，型別為 BIGINT UNSIGNED、單表時自增、步長為 1。gmt_create，gmt_modified 的型別均為 DATETIME 型別，前者現在時表示主動式建立，後者過去分詞表示被動式更新。

  • 【 推薦 】表的命名最好是遵循 "業務名稱_表的作用"。

  • 【 推薦 】庫名與應用名稱儘量一致。

  • 【參考】合適的字元儲存長度，不但節約資料庫表空間、節約索引儲存，更重要的是提升檢索速度。

    • 正例：無符號值可以避免誤存負數，且擴大了表示範圍。

      image-20230503005713335

擴充 2：如何理解清空表、刪除表等操作需謹慎？！

表刪除操作將把表的定義和表中的資料一起刪除，並且 MySQL 在執行刪除操作時，不會有任何的確認資訊提示，因此執行刪除操時應當慎重。在刪除表前，最好對錶中的資料進行備份，這樣當操作失誤時可以對資料進行恢復，以免造成無法挽回的後果。

同樣的，在使用 ALTER TABLE 進行表的基本修改操作時，在執行操作過程之前，也應該確保對資料進行完整的備份，因為資料庫的改變是無法撤銷的，如果新增了一個不需要的欄位，可以將其刪除；相同的，如果刪除了一個需要的列，該列下面的所有資料都將會丟失。

擴充 3：MySQL 8.0 新特性 — DDL 的原子化。

在 MySQL 8.0 版本中，InnoDB 表的 DDL 支援事務完整性，即 DDL 操作要麼成功要麼回滾 。DDL 操作回滾日誌寫入到 data dictionary 資料字典表 mysql.innodb_ddl_log（該表是隱藏的表，透過 SHOW TABLES 無法看到）中，用於回滾操作。透過設定引數，可將 DDL 操作日誌列印輸出到 MySQL 錯誤日誌中。

示例：

mysql> CREATE DATABASE mytest;

mysql> USE mytest;

mysql> CREATE TABLE book1(
        	book_id INT,
        	book_name VARCHAR(255)
	   );

mysql> SHOW TABLES;

在 MySQL 5.7 版本中，刪除資料表 book1 和資料表 book2，結果如下：

mysql> DROP TABLE book1, book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

mysql> SHOW TABLES;
Empty set (0.00 sec)

從結果可以看出，雖然刪除操作時報錯了，但是仍然刪除了資料表 book1。

在 MySQL 8.0 版本中，刪除資料表 book1 和資料表 book2，結果如下：

mysql> mysql> DROP TABLE book1, book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

mysql> SHOW tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1            |
+------------------+
1 row in set (0.00 sec)

從結果可以看出，資料表 book1 並沒有被刪除。

資料處理之增刪改

插入資料

實際問題

image-20230503102507812

使用INSERT語句向表中插入資料。

方式一：VALUES 的方式新增

情況 1：為表的所有欄位按預設順序插入資料。

INSERT INTO 表名
VALUES (value1, value2, …);

值列表中需要為表的每一個欄位指定值，並且值的順序必須和資料表中欄位定義時的順序相同。

情況 2：為表的指定欄位插入資料。

INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);

為表的指定欄位插入資料，就是在 INSERT 語句中只向部分欄位中插入值，而其他欄位的值為表定義時的預設值。

在 INSERT 子句中隨意列出列名，但是一旦列出，VALUES 中要插入的 value1，...，valuen 需要與 column1，...，columnn 列一一對應。如果型別不同，將無法插入，並且 MySQL 會產生錯誤。

情況 3：同時插入多條記錄。

INSERT 語句可以同時向資料表中插入多條記錄，插入時指定多個值列表，每個值列表之間用逗號分隔開，基本語法格式如下：

INSERT INTO table_name
VALUES
(value1 [, value2, …, valuen]),
(value1 [, value2, …, valuen]),
……
(value1 [, value2, …, valuen]);

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [, value2, …, valuen]),
(value1 [, value2, …, valuen]),
……
(value1 [, value2, …, valuen]);

使用 INSERT 同時插入多條記錄時，MySQL 會返回一些在執行單行插入時沒有的額外資訊，這些資訊的含義如下：

  • Records：表明插入的記錄條數。
  • Duplicates：表明插入時被忽略的記錄，原因可能是這些記錄包含了重複的主鍵值。
  • Warnings：表明有問題的資料值，例如發生資料型別轉換。

一個同時插入多行記錄的 INSERT 語句等同於多個單行插入的 INSERT 語句，但是多行的 INSERT 語句在處理過程中效率更高。因為 MySQL 執行單條 INSERT 語句插入多行資料比使用多條 INSERT 語句快，所以在插入多條記錄時最好選擇使用單條 INSERT 語句的方式插入。

VALUES 也可以寫成 VALUE，但是 VALUES 是標準寫法。

字元和日期型資料應包含在單引號中。

方式二：將查詢結果插入到表中

INSERT 還可以將 SELECT 語句查詢的結果插入到表中，此時不需要把每一條記錄的值一個一個輸入，只需要使用一條 INSERT 語句和一條 SELECT 語句組成的組合語句，即可快速地從一個或多個表中向一個表中插入多行。

語法：

INSERT INTO 目標表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition];
  • 在 INSERT 語句中加入子查詢。
  • 不必書寫 VALUES 子句。
  • 子查詢中的值列表應與 INSERT 子句中的列名對應。

更新資料

image-20230503112310054

使用UPDATE語句更新資料。

語法：

UPDATE table_name
SET column1 = value1, column2 = value2, …, column = valuen
[WHERE condition];
  • 可以一次更新多條資料。
  • 如果需要回滾資料，需要保證在 DML 前，關閉自動提交：SET AUTOCOMMIT = FALSE;。
  • 使用 WHERE 子句指定需要更新的資料。
  • 如果省略 WHERE 子句，則表中的所有資料都將被更新。

連表更新：

UPDATE hse_alratk.`work_ticket` ticket
 	INNER JOIN hse_alratk.`work_ticket_operation` operation ON ticket.id = operation.work_ticket
     	and operation.is_audit_complete = 1
     	and operation.operation_code = 'sign'
     	and operation.is_deleted = 0
     	and ticket.is_deleted = 0
SET ticket.actual_start_time = operation.updated_dt;
 
UPDATE hse_alratk.`work_task` task
    INNER JOIN (
     	SELECT work_task, MIN(actual_start_time) AS min_start_time
     	FROM hse_alratk.`work_ticket`
     	WHERE actual_start_time IS NOT NULL AND is_deleted = 0
     	GROUP BY work_task
    ) a ON a.work_task = task.id AND task.is_deleted = 0
SET task.actual_start_time = a.min_start_time;

刪除資料

使用DELETE語句從表中刪除資料。

語法：

DELETE FROM 表名 [WHERE <condition>];

如果沒有 WHERE 子句，DELETE 語句將刪除表中的所有記錄。

MySQL 8.0 新特性：計算列

簡單來說，計算列就是某一列的值是透過別的列計算得來的。例如，a 列值為 1，b 列值為 2，c 列不需要手動插入，定義 a + b 的結果為 c 的值，那麼 c 就是計算列，是透過別的列計算得來的。

在 MySQL 8.0 中，CREATE TABLE 和 ALTER TABLE 中都支援增加計算列。

示例：定義資料表 tb1，然後定義欄位 id、欄位 a、欄位 b 和欄位 c，其中欄位 c 為計算列，用於計算 a + b 的值。

# 建立測試表 tb1
mysql> CREATE TABLE tb1(
    -> 		id INT,
    -> 		a INT,
    -> 		b INT,
    -> 		c INT GENERATED ALWAYS AS (a + b) VIRTUAL
    -> );
Query OK, 0 rows affected (0.03 sec)
    
# 插入資料
mysql> INSERT INTO tb1(a, b) VALUES (100, 200);
Query OK, 1 row affected (0.01 sec)

# 查詢
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  100 |  200 |  300 |
+------+------+------+------+
1 row in set (0.00 sec)

# 更新
mysql> UPDATE tb1 SET a = 500;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 計算列同步更新
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  500 |  200 |  700 |
+------+------+------+------+
1 row in set (0.00 sec)

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md

相關文章