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

標籤：MySQL

上一篇：解決資料庫報錯Error 1390: Prepared statement contains too many placeholders的問題

下一篇：解決資料庫報錯Error 1390: Prepared statement contains too many placeholders的問題