總結零散的 MySQL 基礎知識

來自：CSDN（作者：Sicimike）

原文連結（底部連結可直達）：

https://blog.csdn.net/Baisitao_/article/details/104714764

前言

在日常開發中，一些不常用且又比較基礎的知識，過了一段時間之後，總是容易忘記或者變得有點模稜兩可。本篇主要記錄一些關於MySQL資料庫比較基礎的知識，以便日後快速查看。

SQL命令

SQL命令分可以分為四組：DDL、DML、DCL和TCL。四組中包含的命令分別如下

DDL

DDL是數據定義語言（Data Definition Language）的簡稱，它處理資料庫schemas和描述數據應如何駐留在資料庫中。

CREATE：創建資料庫及其對象（如表，索引，視圖，存儲過程，函數和觸發器）TRUNCATE：從表中刪除所有記錄，包括為記錄分配的所有空間都將被刪除

# 建表
CREATE TABLE sicimike (
  id int(4) primary key auto_increment COMMENT '主鍵ID',
  name varchar(10) unique,
  age int(3) default 0,
  identity_card varchar(18)
  # PRIMARY KEY (id) // 也可以通過這種方式設置主鍵
  # UNIQUE KEY (name) // 也可以通過這種方式設置唯一鍵
  # key/index (identity_card, col1...) // 也可以通過這種方式創建索引
) ENGINE = InnoDB;

# 設置主鍵
alter table sicimike add primary key(id);

# 刪除主鍵
alter table sicimike drop primary key;

# 設置唯一鍵
alter table sicimike add unique key(column_name);

# 刪除唯一鍵
alter table sicimike drop index column_name;

# 創建索引
alter table sicimike add [unique/fulltext/spatial] index/key index_name (identity_card[(len)] [asc/desc])[using btree/hash]
create [unique/fulltext/spatial] index index_name on sicimike(identity_card[(len)] [asc/desc])[using btree/hash]
example：alter table sicimike add index idx_na(name, age);

# 刪除索引
alter table sicimike drop key/index identity_card;
drop index index_name on sicimike;

# 查看索引
show index from sicimike;

# 查看列
desc sicimike;

# 新增列
alter table sicimike add column column_name varchar(30);

# 刪除列
alter table sicimike drop column column_name;

# 修改列名
alter table sicimike change column_name new_name varchar(30);

# 修改列屬性
alter table sicimike modify column_name varchar(22);

# 查看建表信息
show create table sicimike;

# 添加表注釋
alter table sicimike comment '表注釋';

# 添加欄位注釋
alter table sicimike modify column column_name varchar(10) comment '姓名';

DML
DML是數據操縱語言（Data Manipulation Language）的簡稱，包括最常見的SQL語句，例如SELECT，INSERT，UPDATE，DELETE等，它用於存儲，修改，檢索和刪除資料庫中的數據。

-- 查詢從第11條數據開始的連續5條數據
select * from sicimike limit 10, 5

默認情況下，MySQL中的分組（group by）語句，不要求select返回的列，必須是分組的列或者是一個聚合函數。如果select查詢的列不是分組的列，也不是聚合函數，則會返回該分組中第一條記錄的數據。對比下面兩條SQL語句，第二條SQL語句中，cname既不是分組的列，也不是以聚合函數的形式出現。所以在liming這個分組中，cname取的是第一條數據。

mysql> select * from c;
++--++
| CNO | CNAME | CTEACHER |
++--++
| 1 | 數學 | liming |
| 2 | 語文 | liming |
| 3 | 歷史 | xueyou |
| 4 | 物理 | guorong |
| 5 | 化學 | liming |
++--++
5 rows in set (0.00 sec)

mysql> select cteacher, count(cteacher), cname from c group by cteacher;
++--+--+
| cteacher | count(cteacher) | cname |
++--+--+
| guorong |               1 | 物理 |
| liming |               3 | 數學 |
| xueyou |               1 | 歷史 |
++--+--+
3 rows in set (0.00 sec)

having關鍵字用於對分組後的數據進行篩選，功能相當於分組之前的where，不過要求更嚴格。過濾條件要麼是一個聚合函數( ... having count(x) > 1)，要麼是出現在select後面的列(select col1, col2 ... group by x having col1 > 1)

update tableA a inner join tableB b on a.xxx = b.xxx set a.col1 = xxx, b.col1 = xxx where ...

delete a, b from tableA a inner join tableB b on a.xxx = b.xxx where a.col1 = xxx and b.col1 = xxx

DCLDCL是數據控制語言（Data Control Language）的簡稱，它包含諸如GRANT之類的命令，並且主要涉及資料庫系統的權限，權限和其他控制項。REVOKE：撤消用戶使用GRANT命令賦予的訪問權限TCLTCL是事務控制語言（Transaction Control Language）的簡稱，用於處理資料庫中的事務範式資料庫規範化，又稱正規化、標準化，是資料庫設計的一系列原理和技術，以減少資料庫中數據冗餘，增進數據的一致性。關係模型的發明者埃德加·科德最早提出這一概念，並於1970年代初定義了第一範式、第二範式和第三範式的概念，還與Raymond F. Boyce於1974年共同定義了第三範式的改進範式——BC範式。
除外還包括針對多值依賴的第四範式，連接依賴的第五範式、DK範式和第六範式。現在資料庫設計最多滿足3NF，普遍認為範式過高，雖然具有對數據關係更好的約束性，但也導致數據關係表增加而令資料庫IO更易繁忙，原來交由資料庫處理的關係約束現更多在資料庫使用程序中完成。第一範式定義：資料庫中的所有欄位（列）都是單一屬性，不可再分的。這個單一屬性由基本的數據類型所構成，如整型、浮點型、字符串等。上表不滿足第一範式，其中的地址列是可以再拆分的，可以拆分成省、市、區等第二範式定義：資料庫中的表不存在非關鍵欄位對任一關鍵字欄位的部分函數依賴部分函數依賴是指存在著組合關鍵字中的某一關鍵字決定非關鍵字的情況第二範式在滿足了第一範式的基礎上，消除非主鍵列對聯合主鍵的部分依賴上面這張表中想要設置主鍵，只能是商品名稱和供應商名稱一起組成聯合主鍵。但是價格和分類只依賴於商品名稱，供應商電話只依賴於供應商名稱，所以上面的表不滿足第二範式，可以改成如下形式：第三範式定義：所有非主鍵屬性都只和候選鍵有相關性，也就是說非主鍵屬性之間應該是獨立無關的。第三範式是在滿足了第二範式的基礎上，消除列與列之間的傳遞依賴。在上面的表中，商品的分類描述依賴分類，而分類依賴商品名稱，而不是分類描述直接依賴商品名稱。這樣就形成了傳遞依賴，所以不符合第三範式。可以改成如下形式資料庫設計時，遵循範式和反範式一直以來是一個頗受爭議的問題。遵循範式對數據關係更好的約束性，並且減少數據冗餘，可以更好地保證數據一致性。而反範式則是為了獲得更好地性能。所以範式還是反範式並沒有明確的標準，適合自己業務場景的才是最好的。反範式設計時，需要考慮以下幾個問題，分別是插入異常、更新異常和刪除異常。插入異常：如果某個實體隨著另一個實體的存在而存在，即缺少某個實體是無法表示這個實體，那麼這個表就存在插入異常。更新異常：如果更改表所對應的某個實體實例的單獨屬性時，需要將多行更新，那麼就說明這個表存在更新異常刪除異常：如果刪除表的某一行來表示某實體實例失效時，導致另一個不同實體實例信息丟失，那麼這個表就存在刪除異常如果可樂第二製造廠這個供應商尚未開始供貨，表中就不存在第二條記錄，也就無法記錄供應商的電話，這樣就存在插入異常；如果需要把可樂的價格提高，需要更新表中的多條記錄，這樣就存在更新異常；如果刪除可樂第二製造廠的供貨信息，那麼該供應商的電話也就丟失了，這樣就存在刪除異常。一般存在插入異常的表，都會存在更新異常和刪除異常。橫表縱表

# 橫表
CREATE TABLE `table_h2z` (
`name` varchar(32) DEFAULT NULL,
`chinese` int(11) DEFAULT NULL,
`math` int(11) DEFAULT NULL,
`english` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `table_h2z` */
insert into `table_h2z`(`name`,`chinese`,`math`,`english`) values
('mike',45,43,87),
('lily',53,64,88),
('lucy',57,75,75);

# 縱表
CREATE TABLE `table_z2h` (
  `name` varchar(32) DEFAULT NULL,
  `subject` varchar(8) NOT NULL DEFAULT '',
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `table_z2h` */
insert into `table_z2h`(`name`,`subject`,`score`) values
('mike','chinese',45),
('lily','chinese',53),
('lucy','chinese',57),
('mike','math',43),
('lily','math',64),
('lucy','math',75),
('mike','english',87),
('lily','english',88),
('lucy','english',75);

橫錶轉縱表

SELECT NAME, 'chinese' AS `subject`, chinese AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'math' AS `subject`, math AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'english' AS `subject`, english AS `score` FROM table_h2z

+-+----+--+
| name | subject | score |
+-+----+--+
| mike | chinese | 45 |
| lily | chinese | 53 |
| lucy | chinese | 57 |
| mike | math | 43 |
| lily | math | 64 |
| lucy | math | 75 |
| mike | english | 87 |
| lily | english | 88 |
| lucy | english | 75 |
+-+----+--+
9 rows in set (0.00 sec)

SELECT NAME,
 SUM(CASE `subject` WHEN 'chinese' THEN score ELSE 0 END) AS chinese,
 SUM(CASE `subject` WHEN 'math' THEN score ELSE 0 END) AS math,
 SUM(CASE `subject` WHEN 'english' THEN score ELSE 0 END) AS english
FROM table_z2h
GROUP BY NAME

+-+----+-+----+
| name | chinese | math | english |
+-+----+-+----+
| lily | 53 |   64 | 88 |
| lucy |      57 | 75 |      75 |
| mike | 45 |   43 | 87 |
+-+----+-+----+
3 rows in set (0.00 sec)

參考https://www.w3schools.in/mysql/ddl-dml-dcl/

喜歡我可以給我設為星標哦