MYSQL（基本篇）——一篇文章帶你走進MYSQL的奇妙世界

MYSQL算是我們程式員必不可少的一份求職工具了

無論在什麼崗位，我們都可以看到應聘要求上所書寫的”精通MYSQL等資料庫及優化“

那麼我們今天就先來瞭解一下MYSQL的整體佈局並且帶你掌握MYSQL的基本內容，希望能為你帶來幫助！

MYSQL基本佈局

基礎篇

• MYSQL概述
• SQL語法
• 函數
• 約束
• 多表查詢
• 事務

進階篇

• 存儲引擎
• 索引
• SQL優化
• 視圖/存儲過程/觸發器
• 鎖
• InnoDB核心
• MYSQL管理

運維篇

• 日誌
• 主從複製
• 分庫分表
• 讀寫分離

# 即使看到這麼多內容，也不要太過害怕
# 我們這篇文章只為大家介紹到基礎篇的內容，而我們如果不深入的話，學習到進階篇已經是足夠工作使用的了

MYSQL概述

我們先來整體瞭解一下MYSQL

資料庫相關概念

資料庫整體而言，分為三個部分：

目前主流關係型資料庫

目前主流關係型資料庫有四種：

• ORACLE
• MYSQL
• SQL Server
• PostgreSQL

# 註意：我們所學習的SQL語句是針對所有關係型資料庫，所以不存在你學習了MYSQL卻在工作中遇到ORACLE卻無法使用的情況

資料庫的啟動和連接

資料庫的正常啟動一般在cmd命令行中：

# 啟動：
net start mysql80
# 關閉：
net stop mysql80
#客戶端連接：
在電腦中找到客戶端命令行，並輸入密碼即可

但我們的日常使用中，使用cmd命令行是極其不美觀且不實用的，我們一般使用軟體代替：

• 這裡我們推薦使用DataGrip軟體
• 但下載只有30天試用，需要收費，可以到其他網站找到破解版，這裡暫不做內容講解

關係型資料庫

關係型資料庫概念：

• 建立在關係模型基礎上，由多張相互連接的二維表組成的資料庫

特點：

• 使用表存儲數據，格式統一，便於維護
• 使用SQL語言操作，標誌統一，使用方便

數據模型

數據模型示意圖：

• 客戶端 -> DBMS -> 資料庫 -> 表

註意：

• DBMS可以操作多個資料庫
• 單個資料庫可以存在多個表

SQL數據類型

我們在講解SQL語法之前需要先來瞭解一下SQL中的數據類型：

數值類型

註意：
TINYINT 可以用於一些非常小的數值中，因為只占用1byte，可以減少記憶體使用
SIGNED  可以用於非負數的情況下，增大數值

字元串類型

註意：
CHAR 屬於定長字元串，長度不可改變，未書寫長度用空格代替，但性能好
VARCHAR 屬於變長字元串，長度可變，但性能較差

日期類型

SQL語法

SQL語法分為多個部分，讓我們來逐步瞭解：

SQL通用語法

SQL通用語法適用於所有SQL語句中：

• SQL語句可以單行或多行書寫，以分號結尾
• SQL語句可以使用空格/縮進來增強語句的可讀性
• MYSQL資料庫的SQL語句不區分大小寫，但關鍵字建議大寫
• 註釋：#單行註釋，/**/多行註釋

SQL語句分類

SQL語句主要分為四種：

DDL講解

Data Definition Language：數據定義語言，用來定義資料庫對象(資料庫，表，欄位) 。

1. 庫操作：

查詢：

# 查詢所有資料庫
SHOW DATABASES;
# 查詢當前資料庫
SELECT DATABASE();

創建：

# 創建資料庫
CREATE DATABASE [IF NOT EXISTS] 資料庫名 [DEFAULT CHARSET 字元集] [COLLATE 排序規則]；
/*
註意：
[IF NOT EXISTS]：當資料庫不存在時創建資料庫（防止重覆創建）
*/

刪除：

# 刪除資料庫
DROP DATABASE [IF EXISTS] 資料庫名;
註意：
[IF EXISTS]：當資料庫存在時刪除資料庫（防止出錯）
*/

使用：

# 使用/轉換資料庫
USE 資料庫名;

2. 表操作：

創建：

# 創建表
CREATE TABLE 表名(
	欄位1 欄位1類型[COMMENT 欄位1註釋]，
    欄位2 欄位2類型[COMMENT 欄位2註釋]，
    欄位3 欄位3類型[COMMENT 欄位3註釋]，
    欄位4 欄位4類型[COMMENT 欄位4註釋]
)[COMMENT 表註釋];
/*
註意：
數據之間用“，”隔開，最後一個沒有“，”
*/

查詢：

# 查詢表
SHOW TABLES;

查詢表結構：

# 查詢表結構
DESC 表名;

查詢指定表的創表語句：

# 查詢指定表的創表語句
SHOW CREATE TABLE 表名;

添加欄位：

# 添加欄位
ALTER TABLE 表名 ADD 欄位名 類型（長度） [COMMENT 註釋][約束];

修改數據類型：

# 修改數據類型
ALTER TABLE 表名 MODIFY 欄位名 新數據類型（長度）;

修改欄位名和欄位類型：

# 修改欄位名和欄位類型
ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 類型（長度） [COMMENT 註釋][約束];

刪除欄位：

# 刪除欄位
ALTER TABLE 表名 DROP 欄位名;

修改表名：

# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

刪除表：

# 刪除表
DROP TABLE[IF EXISTS] 表名;
# 刪除表並重建該表
TRUNCATE TABLE 表名;
/*
註意：
無論哪種方法，裡面數據都會被全部刪除
*/

3. 案例展示：

設計一張員工信息表，要求如下：

• 編號（純數字）
• 員工工號（字元串，長度不超過10）
• 員工姓名（字元串，長度不超過10）
• 性別（男/女，存放一個字）
• 年齡（正常人年齡，沒有負數）
• 身份證號（二代身份證均為18位，且包含X）
• 入職時間（年月日即可）

代碼展示：

create table emp(
    id int comment "編號",
    empid varchar(10) comment "員工工號",
    empname varchar(10) comment "員工姓名",
    sex char(1) comment "性別",
    age tinyint unsigned comment "年齡",
    identify char(18) comment "身份證號",
    dateenter data comment "入職時間"
) comment "員工表";

DML講解

Data Manipulation Language：數據操作語言，用來對資料庫中表的數據記錄進行增、刪、改操作。

• 增加數據（INSERT）
• 修改數據（UPDATE）
• 刪除數據（DELETE）

1. 增加數據：

# 給指定欄位增加數據
INSERT INTO 表名 (欄位名1, 欄位名2, ...) VALUES (值1, 值2, ...);
# 給全部欄位增加數據
INSERT INTO 表名 VALUES (值1, 值2, ...);
# 批量增加數據
INSERT INTO 表名 (欄位名1, 欄位名2, ...) VALUES (值1, 值2, ...)，(值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...)，(值1, 值2, ...);

註意：

• 插入數據時，指定的欄位順序需要和值的順序一一對應
• 字元串和日期數據應包含在引號中
• 插入的數據大小，應該在欄位的規定範圍內

2. 修改數據：

# 修改數據
UPDATE 表名 SET 欄位1 = 值1，欄位2 = 值2,....[WHERE 條件]；
/*
條件例如： id=1，sex='女'
*/

註意：

• 修改語句的條件可以有可以沒有，若沒有條件，修改整張表

3. 刪除數據：

# 刪除數據
DELETE FROM 表名 [WHERER 條件];
/*
條件例如： id=1，sex='女'
*/

註意：

• 刪除語句的條件可以有可以沒有，若沒有條件，刪除整張表
• DELETE不能特定刪除某一個欄位，如有需要採用UPDATE使該值為NULL

DQL講解

Data Query Language：數據查詢語言，用來查詢資料庫中表的記錄。

• 查詢關鍵字: SELECT

首先我們給出DQL的整體語法：

SELECT
欄位列表
FROM
表名列表
WHERE
條件列表
GROUP BY
分組欄位列表
HAVING
分組後條件列表
ORDER BY
排序欄位列表
LIMIT
分頁參數

接下來我們將一一講解上述語法

1. 基本查詢：

# 查詢多個欄位
SELECT 欄位1，欄位2，欄位3，... FROM 表名;

# 查詢所有欄位
SELECT * FROM 表名;

# 查詢時設置別名（在查詢結果中的最上方類別會變成別名），AS可省略
SELECT 欄位1 AS '別名'...FROM 表名;

# 去除重覆記錄（去除相同元素）
SELECT DISTINCT 欄位列表 FROM 列表;

2. 條件查詢：

# 條件查詢語法：
SELECT 欄位列表 FROM 表名 WHERE 列表條件;

比較運算符列表條件：

邏輯運算符列表條件：

3. 分組查詢：

分組查詢常常和聚合函數一同使用，所以我們先來講解一下聚合函數

介紹：

• 聚合函數是指將一列數據作為一個整體，進行縱向計算

語法：

SELECT 聚合函數（欄位列表） FROM 表名;

常見聚合函數：

介紹完聚合函數，我們來講解分組查詢：

# 分組查詢
SELECT 欄位列表 FROM 表名[WHERE 條件] GROUP BY 分組欄位名[HAVING 分組後過濾條件];

這裡講解一下WHERE和HAVING的區別：

• WHERE 和 HAVING 都是過濾條件，但過濾的時機不同
• WHERE：在分組之前進行過濾，被過濾掉的數據不參與分組
• HAVING：在分組之後對結果進行過濾，可以對聚合函數進行過濾

註意：

• 執行順序：WHERE > 聚合函數 > HAVING
• 分組之後，查詢的欄位一般為聚合函數和分組欄位，查詢其他欄位沒有意義！

4. 排序查詢：

SELECT 欄位列表 FROM 表名 ORDER BY 欄位1 排列方式1，欄位2 排列方式2 ...；

排列方式只有兩種：

• ASC：升序（預設，可不寫）
• DESC：降序

註意：

• 若為多欄位排序，僅當第一個欄位相同時，才會根據第二個欄位進行排序

5. 分頁查詢：

我們在網頁中經常看到的翻頁操作中的當前頁面內容就是通過分頁查詢做到的：

SELECT 欄位列表 FROM 表名 LIMIT 起始索引，查詢記錄數;

註意：

• 起始索引從0開始，起始索引 = （查詢頁碼-1）*每頁顯示記錄數
• 分頁查詢是資料庫的方言，不同的資料庫有不同的實現方法（MYSQL中是LIMIT）
• 如果查詢的是第一頁數據，起始索引可以省略，直接寫查詢記錄數即可

6. 執行順序：

在上面我們已經介紹了DQL的所有語法，下麵我們將給出DQL的執行順序：

FROM
表名列表

WHERE
條件列表

GROUP BY
分組欄位列表
HAVING
分組後條件列表

SELECT
欄位列表

ORDER BY
排序欄位列表

LIMIT
分頁參數

DCL講解

Data Control Language：數據控制語言，用來管理資料庫用戶、控制資料庫的訪問許可權。

1. 管理用戶：

管理用戶主要是針對用戶的操作，我們分為四小部分講解：

• 查詢用戶：

# 我們在MYSQL中存在mysql資料庫，在庫中存在user表，我們的用戶都存儲在裡面
USE mysql；
SELECT * FROM user；

• 創建用戶：

# 創建用戶
CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';
/*
用戶名可以隨機定義
主機名分為 localhost本機 和 %任意機型
*/

• 修改用戶密碼：

ALTER USER '用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼';

• 刪除用戶：

DROP USER '用戶名'@'主機名';

註意：

• 主機名可以使用%通配
• 這類SQL開發人員操作較少，主要由DBA（資料庫管理員）使用

2. 許可權控制：

我們所創建的用戶在開始時只有連接資料庫的許可權，所以其他許可權都需要我們去控制

但是在瞭解如何控制許可權之前，我們需要先來瞭解資料庫都具備什麼許可權：

好的，接下來讓我們來講解許可權控制語句：

• 查詢許可權：

# 查詢該用戶的許可權
SHOW GRANTS FOR '用戶名'@'主機名';

• 授予許可權：

# 授予該用戶許可權
GRANT 許可權列表 ON 資料庫名.表名 TO '用戶名'@'主機名';

• 撤銷許可權：

REVOKE 許可權列表 ON 資料庫名.表名 FROM '用戶名'@'主機名';

註意：

• 多個許可權之間用逗號隔開
• 授權時，資料庫名和表名可以採用*進行通配，代表所有

函數

函數：

• 是指一段可以直接被另一段程式調用的程式或代碼。

函數大致被分為四種：

• 字元串函數
• 數值函數
• 日期函數
• 流程函數

函數的統一測試標準：

• SELECT 函數（函數參數）

字元串函數

數值函數

日期函數

流程函數

約束

概念：

• 約束是作用於表中欄位上的規則，用來限制存儲在表中的數據

目的：

• 保證資料庫中的數據的正確，有效性和完整性

約束類型

註意：

• 約束是作用於表中欄位上的，可以在創建表/修改表的時候加上約束

外鍵綁定

定義：

• 用來讓兩張表的數據之間建立連接，從而保證數據的一致性和完整性。

添加外鍵：

-- 創建表時添加外鍵
CREATE TABLE 表名（
	欄位名 數據類型，
	...
	[CONSTRAINT] [外鍵名稱] FOREIGN (外鍵欄位名) REFERENCES 主表（主表列名）
）;

-- 外部修改時添加外鍵
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位名) REFERENCES 主表（主表列名）;

刪除外鍵：

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

外鍵約束

外鍵同樣也具有約束條件：

外鍵約束條件修改代碼：

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位) REFERENCES 主表名（主表欄位名）
	ON UPDATE 修改時行為 ON DELETE 刪除時行為

多表查詢

多表關係概述：

• 在項目開發中，在進行資料庫表的結構設計時，會根據業務需求以及業務模塊之間的關係，分析並設計表結構，由於業務之間相互關聯，所有各個表之間也存在著各種聯繫

表與表的關係大致分為三種：

• 一對多
  • 關係：一個部門對應多個員工，一個員工對應一個部門
  • 實現：在多的一方建立外鍵，指向一的一方的主鍵
• 多對多
  • 關係：一個學生選擇多個課程，一個課程有多個學生選擇
  • 實現：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵
• 一對一
  • 關係：一對一關係常用於單表內容過多，進行拆分使用
  • 實現：在任意一方加入外鍵，關聯另外一方的主鍵，並且設置外鍵為唯一的（UNIQUE）

多表查詢概述：

• 指從多張表中查詢數據
• 當我們同時查詢兩張表時，會出現笛卡爾積現象，我們的目的就是消除多餘的笛卡爾積

多表查詢分為以下幾種：

• 內連接
• 外連接
• 自連接
• 聯合查詢
• 子查詢

內連接

內連接查詢的是兩張表交集的部分

-- 隱式內連接
SELECT 欄位列表 FROM 表1，表2 WHERE 連接條件...;

-- 顯式內連接
SELECT 欄位列表 FROM [INNER] JOIN 表2 ON 連接條件...;

外連接

外連接查詢一方的所有內容和所需要查詢的另一方內容

-- 左連接：包含左表A的所有內容和左表和右表的交集部分的數據
SELECT 欄位列表 FROM 表A LEFT [OUTER] JOIN 表B ON 條件...;

-- 右連接：包含右表B的所有內容和左表和右表的交集部分的數據
SELECT 欄位列表 FROM 表A RIGHT [OUTER] JOIN 表B ON 條件...;

自連接

自連接負責查詢自我內容

-- 只有一張表，但註意需要有不同的別名，負責進行比較和篩選
SELECT 欄位列表 FROM 表A 別名A JOIN 表A 別名B ON 條件...;

聯合查詢

聯合查詢負責把兩個查詢內容結合起來

SELECT 欄位列表 FROM 表A...
UNION [ALL]
SELECT 欄位列表 FROM 表B...;

註意：

• 對於聯合查詢的多張表的列數必須保持一致，欄位類型也需要保持一致
• UNION ALL會將所有內容都合併在一起，UNION會對合併的內容進行數據去重

子查詢

概念：

• SQL語句中嵌套SELECT語句，稱為嵌套語句，也被稱為子查詢

SELECT * FROM T1 WHERE column1 = (SELECT column1 FROM T2)

• T1,T2分別表示兩個表

• column表示表中的元素，進行比較

• 子查詢外部的語句可以是INSERT/DELETE/SELECT語句中的任意一個

子查詢可以大概分為四種：

• 標準子查詢
• 列子查詢
• 行子查詢
• 表子查詢

標準子查詢

概念：

• 子查詢返回的結果是單個值（數字、字元串、日期等），最簡單的形式，這種子查詢稱為標量子查詢。

常用操作符：

• = <> > >= < <=

列子查詢

概念：

• 子查詢返回的結果是一列（可以是多行），這種子查詢稱為列子查詢。

常用操作符：

• IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符解釋：

行子查詢

概念：

• 子查詢返回的結果是一行（可以是多列），這種子查詢被稱為行子查詢

常用操作符：

• = 、<> 、IN 、NOT IN

表子查詢

概念：

• 子查詢返回的結果是多行多列，這種子查詢稱為表子查詢

常用操作符：

• IN

事務

定義：

• 事務是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作都作為一個整體一起向系統提交或撤銷操作請求，即這些操作要麼同時成功，要麼同時失敗

註意：

• 預設MYSQL的事務是自動提交的，也就是說每執行一條DML語句，MYSQL就會立即隱式的提交事務

事務操作

事務操作分為幾個部分：

• 查看事務提交方法

-- 查看事務提交方法
SELECT @@autocommit
-- 自動：1
-- 手動：0
-- 系統設置為1自動提交

• 設置事務提交方法

-- 設置事務提交方法
SET @@autocommit = 0/1;
-- 我們可以設置為0（因為預設為1）
-- 在設置為0後，後續操作不會影響數據，必須手動提交

• 開啟事務

-- 開啟事務
START TRANSACTION 或 BEGIN
--效果和@@autocommit = 0一樣

• 提交事務

-- 當數據無法自動提交或開啟事務後，我們需要手動提交，只有手動提交後，信息才會上傳
COMMIT

• 回滾事務

-- 當事務中途出現錯誤（無法完全執行事務），我們常常採用回滾事務，讓全部回到事務進行前
ROLLBACK

事務四大特性

事務具有四大特性，被稱為ACID：

• 原子性（Atomicity）：事務是不可分割的最小操作單元，要麼全部成功，要麼全部失敗
• 一致性（Consistency）：事務完成時，必須使所有的數據都保持一致狀態
• 隔離性（Isolation）：資料庫系統提供的隔離機制，保證事務在不受外部併發操作影響的獨立環境下運行
• 持久性（Durability）：事務一旦提交或滾回，它對資料庫的改變就是永久的

併發事務問題

併發事務常常出現三種問題：

事務隔離級別

事務隔離級別分為四種：

註意：

• √：會發生

• ×：不會發生

• 性能：從上往下越來越差

• 約束：從上往下越來越好

下麵給出兩條代碼來查看和設置事務隔離級別：

-- 查看事務隔離級別
SELECT @@TRANSACTION_ISOLATION

-- 設置事務隔離級別
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL 
{Read uncommitted|Read committed|Repeatable Read|Serializable}
-- SESSION ：僅針對當前事務
-- GLOBAL：針對所有事務

結束語

好的，關於MYSQL的基本內容就介紹到這裡，希望能為你帶來幫助！

附錄

我在之前學習過MYSQL，所以基礎篇基本沒有練習題和實例，

後面的進階篇我會詳細書寫每個例子包括練習，如果上面的內容有你不太明白的地方，這裡推薦跟著老師的課程學習

這篇文章是屬於學習筆記，主要是學習B站黑馬程式員鄧老師的MYSQL課程

下麵為大家附上連接：黑馬程式員 MySQL資料庫入門到精通，從mysql安裝到mysql高級、mysql優化全囊括_嗶哩嗶哩_bilibili