MySQL - 數據庫基礎操作

SQL語句

結構化查詢語言(Structured Query Language)，在關系型數據庫上執行數據操作、數據檢索以及數據維護的標準語言。

分類

DDL
- 數據定義語言(Data Definition Language)，定義對數據庫對象(庫、表、列、索引)的操作。
DML
- 數據操作語言(Data Manipulation Language)，定義對數據庫記錄的操作。
DQL
- 數據查詢語言（Data Query Language）。
DCL
- 數據控制語言(Data Control Language)，定義對數據庫、表、字段、用戶的訪問權限和安全級別。

書寫規范

在數據庫系統中，SQL語句不區分大小寫(關鍵字建議用大寫) 。
字符串常量區分大小寫。
SQL語句可單行或多行書寫，以分號“;”結尾。
關鍵詞不能跨多行或簡寫。
用空格和縮進來提高語句的可讀性。
子句通常位于獨立行，便于編輯，提高可讀性。
注釋
- SQL標準：多行注釋使用”/**/“；單行使用“-- ”。
- MySQL：“#”。

數據庫操作

編碼問題

早期MySQL為了兼容像中文這種符號，提供了utf8編碼。
現在因為表情包已經需要使用第四個字節存儲，所以，utf8不建議使用了，建議使用另一種真正的Unicode編碼 — utf8mb4。

MySQL自帶的數據庫

Information_schema
- 主要存儲了系統中的一些數據庫對象信息，如用戶表信息、列信息、權限信
  息、字符集信息、分區信息等。(數據字典表)
performance_schema
- 主要存儲數據庫服務器的性能參數。
mysql
- 存儲了系統的用戶權限信息及幫助信息。
sys
- MySQL5.7新增，之前版本需要手工導入。這個庫是通過視圖的形式把information_schema和performance_schema結合起來，查詢出更加令人容易理解的數據。
test
- 系統自動創建的測試數據庫，任何用戶都可以使用。

創建數據庫

-- 創建數據庫。
CREATE DATABASE 數據庫名稱;

-- 創建數據庫，同時指定編碼。
CREATE DATABASE 數據庫名稱 DEFAULT CHARACTER SET 編碼;

-- 創建數據庫，同時指定編碼和排序規則。
CREATE DATABASE 數據庫名稱 DEFAULT CHARACTER SET 編碼 COLLATE 排序規則;

查詢當前處于哪一個數據庫

SELECT DATABASE();

查看數據庫版本

SELECT VERSION();

查看當前用戶

SELECT USER();

以查詢SQL語句的方式查看數據庫

SHOW CREATE DATABASE 數據庫名;

刪除數據庫

DROP DATABASE 數據庫名;-- 一般加上if exists語句，意為如果存在才刪除，可以防止報錯。
DROP DATABASE IF EXISTS 數據庫名;

數據庫表操作

創建表

-- 語法。
CREATE TABLE 表名 (字段1 數據類型 [約束條件],字段2  數據類型 [約束條件],……字段n 數據類型 [約束條件]
);-- 同樣可以添加判斷語句。
CREATE TABLE IF NOT EXISTS 表名 (字段1 數據類型 [約束條件],字段2  數據類型 [約束條件],……字段n 數據類型 [約束條件]
);

查詢表中的所有數據

SELECT * FROM 表名;

向表中插入數據

INSERT INTO 表名 VALUES(字段1數據, 字段2數據,  ……,  字段n數據);
-- 這種數據添加方式具有一定缺點，數據的順序和數量要和字段一樣。
INSERT INTO 表名(字段1, 字段n) VALUES(字段1數據, 字段n數據);
-- 這樣可以只用輸入前面寫了字段的數據，沒寫的其他字段有默認值的取默認值，沒有的默認為NULL。

查看表結構

DESC 表名;
-- 此處desc是describe的縮寫。
-- 故可以這樣寫：
DESCRIBE 表名;

以查詢SQL語句的方式查看表結構

SHOW CREATE TABLE 表名;

在查詢語句后面以 “\G” 結尾，表示縱向顯示每行記錄。但如果以\G結尾，就不必在語句后加分號了。

刪除數據庫表

DROP TABLE 表名;
-- 仍然可以加上判斷語句
DROP TABLE IF EXISTS 表名;

數據類型

數據庫用來存儲數據的，而數據存在很多類型，因此MySQL數據提供了很多類型，供開發者使用。

MySQL中的主要數據類型

文本（Text）

數據類型	描述
CHAR(size)	保存固定長度的字符串（可包含字母、數字以及特殊字符）。在括號中指定字符串的長度。最多 255 個字符。
VARCHAR(size)	保存可變長度的字符串（可包含字母、數字以及特殊字符）。在括號中指定字符串的最大長度。最多 255 個字符。如果值的長度大于 255，則被轉換為 TEXT 類型。
TINYTEXT	存放最大長度為 255 個字符的字符串。
TEXT	存放最大長度為 65535 個字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65535 字節的數據。
MEDIUMTEXT	存放最大長度為 16777215 個字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16777215 字節的數據。
LONGTEXT	存放最大長度為 4294967295 個字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4294967295 字節的數據。
ENUM(x,y,z,etc.)	允許你輸入可能值的列表。可以在 ENUM 列表中列出最大 65535 個值。如果列表中不存在插入的值，則插入空值。這些值是按照輸入的順序存儲的。
SET	與 ENUM 類似， SET 最多只能包含 64 個列表項，不過 SET 可存儲一個以上的值。

數字（Number）

數據類型	描述
TINYINT(size)	-128 到 127 常規。 0 到 255 無符號。在括號中規定最大位數。
SMALLINT(size)	-32768 到 32767 常規。 0 到 65535 無符號。在括號中規定最大位數。
MEDIUMINT(size)	-8388608 到 8388607 普通。 0 到 16777215 無符號。在括號中規定最大位數。
INT(size)	-2147483648 到 2147483647 常規。 0 到 4294967295 無符號。在括號中規定最大位數。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常規。 0 到18446744073709551615 無符號。在括號中規定最大位數。
FLOAT(size,d)	帶有浮動小數點的小數字。在括號中規定最大位數。在 d 參數中規定小數點右側的最大位數。
DOUBLE(size,d)	帶有浮動小數點的大數字。在括號中規定最大位數。在 d 參數中規定小數點右側的最大位數。
DECIMAL(size,d)	作為字符串存儲的 DOUBLE 類型，允許固定的小數點。

日期/時間（Date）

數據類型	描述
DATE()	日期。格式： YYYY-MM-DD 注釋：支持的范圍是從 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()	日期和時間的組合。格式： YYYY-MM-DD HH:MM:SS 注釋：支持的范圍是’1000-01-01 00:00:00’ 到 ‘9999-12- 31 23:59:59’
TIMESTAMP()	時間戳。 TIMESTAMP 值使用 Unix 紀元(‘1970-01-01 00:00:00’ UTC) 至今的描述來存儲。格式： YYYY-MM-DD HH:MM:SS 注釋：支持的范圍是從 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME()	時間。格式： HH:MM:SS 注釋：支持的范圍是從 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()	2 位或 4 位格式的年。注釋： 4 位格式所允許的值： 1901 到 2155。 2 位格式所允許的值： 70 到69，表示從 1970 到 2069

數據庫約束

約束是在表上強制執行的數據校驗規則。

大部分數據庫支持下面五類完整性約束:

主鍵約束

從功能上看相當于非空且唯一，一個表中只允許一個主鍵，主鍵是表中唯一確定一行數據的字段。常在表中添加id字段作為主鍵。
一般建議主鍵采用“int類型”（id），一般建議由數據庫自身維護這個字段的值。
當建立主鍵約束時，MySQL為主鍵創建對應的索引，稱為主鍵索引，主鍵約束名總為PRIMARY。

創建方式

CREATE TABLE 表名 (id int PRIMARY KEY
);
-- 創建后id字段的值強制非空且唯一，不滿足條件的數據插入行為將會報錯。

-- 我們一般將id作為主鍵，賦予自增的能力。
CREATE TABLE 表名 (id int PRIMARY KEY AUTO_INCREMENT
);
-- 這樣寫，表示插入一條數據，該數據的id字段默認為上一條數據的id值加1。需要注意的是，既使數據添加失敗，id字段也會自增。
-- 自增情況下，存在自增的字段在添加數據時可以直接寫DEFAULT或者NULL。

-- 除上述主鍵約束寫法外，還存在一種寫法。
CREATE TABLE 表名 (id int AUTO_INCREMENT,PRIMARY KEY(id)
);

唯一約束
- 唯一約束的作用，是保證該字段的值是唯一的。
- 唯一性約束條件的字段允許出現一個NULL。
- 同一張表內可建多個唯一約束。
- 唯一約束可由多列組合而成。
- 建唯一約束時MySQL會為之建立對應的索引，稱為唯一索引。
- 如果不給唯一約束起名，該唯一約束默認與列名相同。
- 兩種創建方式
  - ```
  CREATE TABLE 表名 (字段 數據類型 UNIQUE
  );
```
- ```
CREATE TABLE 表名 (字段 數據類型,UNIQUE(字段)
);
```

默認值約束

給予給定的字段默認值。

創建方式

CREATE TABLE 表名 (字段 數據類型 DEFAULT 默認值
);

非空約束

不允許字段的值為空。

創建方式

CREATE TABLE 表名 (字段 數據類型 NOT NULL
);

外鍵約束
- 用于確保數據完整性和實現表之間關系的一種數據庫對象。

MySQL8以后支持檢查約束，之前的版本不生效，寫了也不報錯。

檢查約束

添加數據時檢查添加數據是否符合給定條件，不符合不允許添加。

兩種創建方式

CREATE TABLE 表名 (字段 數據類型 CHECK(字段 IN (數據1, 數據2, ……))
);

CREATE TABLE 表名 (字段 數據類型,字段 ENUM(數據1, 數據2, ……)
);

修改表的結構

修改字段（列）數據類型

ALTER TABLE 表名 MODIFY 字段 數據類型;
-- 注意存在數據的情況下，修改不一定能成功，因為存在修改的數據類型與表中該字段中存在的數據的數據類型不符等情況。

在表中增加字段（列）

ALTER TABLE 表名 ADD 字段 數據類型;

刪除字段（列）

ALTER TABLE 表名 DROP 字段;

修改字段（列）名

ALTER TABLE 表名 CHANGE 舊字段名 新字段名 數據類型;

更改表名（兩種方案）

ALTER TABLE 舊表名 RENAME 新表名;

RENAME TABLE 舊表名 TO 新表名;

復制表結構和內容

復制表結構（兩種方法）

-- 在create table語句的末尾添加like子句，可以將源表的表結構復制到新表中。
CREATE TABLE 新表 LIKE 源表;

-- 在create table語句的末尾添加一個select語句，可以實現將想要的源表的表記錄及其字段結構拷貝到新表中。
CREATE TABLE 新表 SELECT */關鍵字 FROM 源表;

復制表內容

-- 已經存在一張機構一致的表，復制數據。
INSERT INTO 新表 SELECT * FROM 原表;

注意

如果直接復制表結構，則會默認將約束也一起復制。
如果復制表結構的同時，復制數據，則不會復制約束。

數據庫字典

由information_schema數據庫負責維護。

tables
- 存放數據庫里所有的數據表、以及每個表所在數據庫。
schemata
- 存放數據庫里所有的數據庫信息。
views
- 存放數據庫里所有的視圖信息。
columns
- 存放數據庫里所有的列信息。
triggers
- 存放數據庫里所有的觸發器。
routines
- 存放數據庫里所有存儲過程和函數。
key_column_usage
- 存放數據庫所有的主外鍵。
table_constraints
- 存放數據庫全部約束。
statistics
- 存放了數據表的索引。