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

• 2022 年 7 月 30 日
• 筆記
• 數據庫

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