引言

MySQL是一個廣泛使用的開源關(guān)系數(shù)據(jù)庫管理系統(tǒng)（RDBMS）。它使用了結(jié)構(gòu)化查詢語言（SQL）來管理存儲在其系統(tǒng)中的數(shù)據(jù)。SQL是一種用于插入、更新、刪除和檢索數(shù)據(jù)庫中數(shù)據(jù)的標(biāo)準(zhǔn)語言。

MySQL的主要特性包括：

1. 性能優(yōu)化：MySQL具有優(yōu)秀的性能、可靠性和可擴(kuò)展性，可以處理大量數(shù)據(jù)，非常適合大型和中型應(yīng)用。

2. 全文搜索：MySQL支持全文搜索，這是一種非常有效的搜索技術(shù)，特別是在有大量文本數(shù)據(jù)的情況下。

3. 復(fù)制：MySQL支持主-從復(fù)制，這使得數(shù)據(jù)可以在多個服務(wù)器之間復(fù)制，提供了數(shù)據(jù)備份、故障恢復(fù)和負(fù)載均衡的功能。

4. 安全：MySQL提供了多層的安全防護(hù)，包括密碼加密、網(wǎng)絡(luò)訪問控制、SSL加密等。

5. 可擴(kuò)展性：MySQL可以根據(jù)需要輕松擴(kuò)展其功能和性能。

6. 跨平臺：MySQL可以在多種操作系統(tǒng)上運(yùn)行，包括Linux、Windows、Mac OS等。

1. SQL

全稱 Structured Query Language，結(jié)構(gòu)化查詢語言。操作關(guān)系型數(shù)據(jù)庫的編程語言，定義了一套操作關(guān)系型數(shù)據(jù)庫統(tǒng)一標(biāo)準(zhǔn) 。

1.1 SQL通用語法

注釋：

• 單行注釋：-- 注釋內(nèi)容 或 # 注釋內(nèi)容
• 多行注釋：/* 注釋內(nèi)容 */

1.2 SQL分類

主要分為四類：DDL、DML、DQL、DCL。

1. 數(shù)據(jù)定義語言（DDL, Data Definition Language）：這種類型的SQL用于定義或修改數(shù)據(jù)庫的結(jié)構(gòu)。主要的DDL語句包括 CREATE、ALTER 和 DROP。

• CREATE：用于創(chuàng)建新的數(shù)據(jù)庫、表、視圖等。
• ALTER：用于修改已存在的數(shù)據(jù)庫對象，例如添加表的新列。
• DROP：用于刪除數(shù)據(jù)庫對象。

2. 數(shù)據(jù)操縱語言（DML, Data Manipulation Language）：這類SQL用于檢索、插入、更新、刪除數(shù)據(jù)庫中的數(shù)據(jù)。主要的DML語句包括 SELECT、INSERT、UPDATE 和DELETE。

• SELECT：用于從一個或多個表中檢索數(shù)據(jù)。
• INSERT：用于在表中插入新的數(shù)據(jù)。
• UPDATE：用于更新表中的數(shù)據(jù)。
• DELETE：用于從表中刪除數(shù)據(jù)。

3. 數(shù)據(jù)查詢語言（DQL, Data Query Language）是SQL（Structured Query Language）的一部分，主要用于查詢和獲取數(shù)據(jù)庫中的數(shù)據(jù)。在大多數(shù)情況下，DQL實際上只包含一個SQL命令，

• SELECT：用于查詢和獲取數(shù)據(jù)庫中的數(shù)據(jù)

4. 數(shù)據(jù)控制語言（DCL, Data Control Language）：這種類型的SQL用于控制對數(shù)據(jù)庫的訪問。主要的DCL語句包括 GRANT 和 REVOKE。

• GRANT：用于給用戶賦予數(shù)據(jù)庫的權(quán)限。
• REVOKE：用于收回用戶的數(shù)據(jù)庫權(quán)限。

1.3 DDL

1.3.1 數(shù)據(jù)庫操作

1). 查詢所有數(shù)據(jù)庫

show databases ;

2). 查詢當(dāng)前數(shù)據(jù)庫

select database() ;

3). 創(chuàng)建數(shù)據(jù)庫

create database [ if not exists ] 數(shù)據(jù)庫名 [ default charset 字符集 ] [ collate 排序規(guī)則 ] ;

4). 刪除數(shù)據(jù)庫

drop database [ if exists ] 數(shù)據(jù)庫名 ;

5). 切換數(shù)據(jù)庫

use 數(shù)據(jù)庫名 ;

我們要操作某一個數(shù)據(jù)庫下的表時，就需要通過該指令，切換到對應(yīng)的數(shù)據(jù)庫下，否則是不能操作的。
比如，切換到test數(shù)據(jù)，執(zhí)行如下SQL：

use test;

1.3.2 表操作

1.3.2.1 表操作-查詢創(chuàng)建

1). 查詢當(dāng)前數(shù)據(jù)庫所有表

show tables;

2). 查看指定表結(jié)構(gòu)

desc 表名 ;

3). 查詢指定表的建表語句

show create table 表名 ;

4). 創(chuàng)建表結(jié)構(gòu)

CREATE TABLE 表名(
字段1 字段1類型 [ COMMENT 字段1注釋 ],
字段2 字段2類型 [COMMENT 字段2注釋 ],
字段3 字段3類型 [COMMENT 字段3注釋 ],
......
字段n 字段n類型 [COMMENT 字段n注釋 ]
) [ COMMENT 表注釋 ] ;

注意: […] 內(nèi)為可選參數(shù)，最后一個字段后面沒有逗號

1.3.2.2 表操作-數(shù)據(jù)類型

1). 數(shù)值類型

1. 整數(shù)類型：整數(shù)類型包括 TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, BIGINT。它們的主要區(qū)別在于存儲的數(shù)值范圍和存儲空間大小。

2. 浮點(diǎn)數(shù)類型：浮點(diǎn)數(shù)類型包括 FLOAT 和 DOUBLE。FLOAT 通常用于小數(shù)點(diǎn)后取數(shù)較少的小數(shù)，DOUBLE 用于小數(shù)點(diǎn)后取數(shù)較多的小數(shù)。

2). 字符串類型
char 與 varchar 都可以描述字符串，char是定長字符串，指定長度多長，就占用多少個字符，和字段值的長度無關(guān) 。而varchar是變長字符串，指定的長度為最大占用長度 。相對來說，char的性能會更高些。

如：
1). 用戶名 username ------> 長度不定, 最長不會超過50
username varchar(50)
2). 性別 gender ---------> 存儲值, 不是男,就是女
gender char(1)
3). 手機(jī)號 phone --------> 固定長度為11
phone char(11)

3). 日期時間類型

1. DATE：這種類型用于表示日期。它的格式為’YYYY-MM-DD’，范圍從 ‘1000-01-01’ 到 ‘9999-12-31’。

2. TIME：這種類型用于表示時間。它的格式為 ‘HH:MM:SS’，范圍從 ‘-838:59:59’ 到 ‘838:59:59’。

3. DATETIME：這種類型用于表示日期和時間。它的格式為 ‘YYYY-MM-DD HH:MM:SS’，范圍從 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。

4. TIMESTAMP：這種類型也用于表示日期和時間，但它的范圍更小，從 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。TIMESTAMP 類型的一個特殊之處在于，它依賴于具體的時區(qū)設(shè)置。

5. YEAR：這種類型用于表示年份，格式為 ‘YYYY’，范圍從 1901 到 2155，以及 0000。

6. DATETIME(精度) 和 TIMESTAMP(精度)：這兩種類型在MySQL 5.6.4及以后的版本中提供，允許用戶指定一個小數(shù)秒精度。例如，‘DATETIME(3)’ 或 ‘TIMESTAMP(3)’ 會包含到毫秒的時間，格式為 ‘YYYY-MM-DD HH:MM:SS.SSS’。

1.3.2.3 表操作-修改

1). 添加字段

ALTER TABLE 表名 ADD 字段名 類型 (長度) [ COMMENT 注釋 ] [ 約束 ];

2). 修改數(shù)據(jù)類型

ALTER TABLE 表名 MODIFY 字段名 新數(shù)據(jù)類型 (長度);

3). 修改字段名和字段類型

ALTER TABLE 表名 CHANGE 舊字段名 新字段名 類型 (長度) [ COMMENT 注釋 ] [ 約束 ];

4). 刪除字段

ALTER TABLE 表名 DROP 字段名;

5). 修改表名

ALTER TABLE 表名 RENAME TO 新表名;

1.3.2.3 表操作-刪除

1). 刪除表

DROP TABLE [ IF EXISTS ] 表名;

2). 刪除指定表, 并重新創(chuàng)建表

TRUNCATE TABLE 表名;

1.4 DML

DML英文全稱是Data Manipulation Language(數(shù)據(jù)操作語言)，用來對數(shù)據(jù)庫中表的數(shù)據(jù)記錄進(jìn)行增、刪、改操作。

• 添加數(shù)據(jù)（INSERT）
• 修改數(shù)據(jù)（UPDATE）
• 刪除數(shù)據(jù)（DELETE）

1.4.1 添加數(shù)據(jù)

1). 給指定字段添加數(shù)據(jù)

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

2). 給全部字段添加數(shù)據(jù)

INSERT INTO 表名 VALUES (值1, 值2, ...);

3). 批量添加數(shù)據(jù)

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

注意事項:

• 插入數(shù)據(jù)時，指定的字段順序需要與值的順序是一一對應(yīng)的。
• 字符串和日期型數(shù)據(jù)應(yīng)該包含在引號中。
• 插入的數(shù)據(jù)大小，應(yīng)該在字段的規(guī)定范圍內(nèi)。

1.4.2 修改數(shù)據(jù)

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 條件 ] ;

注意事項:
修改語句的條件可以有，也可以沒有，如果沒有條件，則會修改整張表的所有數(shù)據(jù)。

1.4.3 刪除數(shù)據(jù)

DELETE FROM 表名 [ WHERE 條件 ] ;

注意事項:

• DELETE 語句的條件可以有，也可以沒有，如果沒有條件，則會刪除整張表的所有數(shù)據(jù)。
• DELETE 語句不能刪除某一個字段的值(可以使用UPDATE，將該字段值置為NULL即可)。
• 當(dāng)進(jìn)行刪除全部數(shù)據(jù)操作時，datagrip會提示我們，詢問是否確認(rèn)刪除，我們直接點(diǎn)擊
  Execute即可。

1.5 DQL

查詢關(guān)鍵字: SELECT

1.5.1 基本語法

DQL 查詢語句，語法結(jié)構(gòu)如下：

SELECT
字段列表
FROM
表名列表
WHERE
條件列表
GROUP BY
分組字段列表
HAVING
分組后條件列表
ORDER BY
排序字段列表
LIMIT
分頁參數(shù)

1.5.2 基礎(chǔ)查詢

1). 查詢多個字段

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

SELECT * FROM 表名 ;

注意 : * 號代表查詢所有字段，在實際開發(fā)中盡量少用（不直觀、影響效率）。

2). 字段設(shè)置別名

SELECT 字段1 [ AS 別名1 ] , 字段2 [ AS 別名2 ] ... FROM 表名;

SELECT 字段1 [ 別名1 ] , 字段2 [ 別名2 ] ... FROM 表名;

3). 去除重復(fù)記錄

SELECT DISTINCT 字段列表 FROM 表名;

1.5.3 條件查詢

1). 語法

SELECT 字段列表 FROM 表名 WHERE 條件列表 ;

2). 條件
常用的比較運(yùn)算符如下:

常用的邏輯運(yùn)算符如下:

1.5.4 聚合函數(shù)

1). 介紹
將一列數(shù)據(jù)作為一個整體，進(jìn)行縱向計算 。

2). 常見的聚合函數(shù)

3). 語法

SELECT 聚合函數(shù)(字段列表) FROM 表名 ;

注意 : NULL值是不參與所有聚合函數(shù)運(yùn)算的。

1.5.5 分組查詢

1). 語法

SELECT 字段列表 FROM 表名 [ WHERE 條件 ] GROUP BY 分組字段名 [ HAVING 分組后過濾條件 ];

2). where與having區(qū)別

• 執(zhí)行時機(jī)不同：where是分組之前進(jìn)行過濾，不滿足where條件，不參與分組；而having是分組之后對結(jié)果進(jìn)行過濾。
• 判斷條件不同：where不能對聚合函數(shù)進(jìn)行判斷，而having可以。

注意事項:

• 分組之后，查詢的字段一般為聚合函數(shù)和分組字段，查詢其他字段無任何意義。
• 執(zhí)行順序: where > 聚合函數(shù) > having 。
• 支持多字段分組, 具體語法為 : group by columnA,columnB

1.5.6 排序查詢

1). 語法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ; 1

2). 排序方式

• ASC : 升序(默認(rèn)值)
• DESC: 降序

注意事項：

• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序，當(dāng)?shù)谝粋€字段值相同時，才會根據(jù)第二個字段進(jìn)行排序 ;

1.5.7 分頁查詢

1). 語法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查詢記錄數(shù) ;

注意事項:

• 起始索引從0開始，起始索引 = （查詢頁碼 - 1）* 每頁顯示記錄數(shù)。
• 分頁查詢是數(shù)據(jù)庫的方言，不同的數(shù)據(jù)庫有不同的實現(xiàn)，MySQL中是LIMIT。
• 如果查詢的是第一頁數(shù)據(jù)，起始索引可以省略，直接簡寫為 limit 10。

1.5.8 執(zhí)行順序

DQL語句的執(zhí)行順序為：
from … where … group by …
having … select … order by … limit …

1.6 DCL

DCL英文全稱是Data Control Language(數(shù)據(jù)控制語言)，用來管理數(shù)據(jù)庫用戶、控制數(shù)據(jù)庫的訪問權(quán)限。

1.6.1 管理用戶

1). 查詢用戶

select * from mysql.user;

其中 Host代表當(dāng)前用戶訪問的主機(jī), 如果為localhost, 僅代表只能夠在當(dāng)前本機(jī)訪問，是不可以遠(yuǎn)程訪問的。 User代表的是訪問該數(shù)據(jù)庫的用戶名。在MySQL中需要通過Host和User來唯一標(biāo)識一個用戶。

2). 創(chuàng)建用戶

CREATE USER '用戶名'@'主機(jī)名' IDENTIFIED BY '密碼';

3). 修改用戶密碼

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

4). 刪除用戶

DROP USER '用戶名'@'主機(jī)名' ;

注意事項:

• 在MySQL中需要通過用戶名@主機(jī)名的方式，來唯一標(biāo)識一個用戶。
• 主機(jī)名可以使用 % 通配。
• 這類SQL開發(fā)人員操作的比較少，主要是DBA（ Database Administrator 數(shù)據(jù)庫管理員）使用。

1.6.2 權(quán)限控制

MySQL中定義了很多種權(quán)限，但是常用的就以下幾種：

上述只是簡單羅列了常見的幾種權(quán)限描述，其他權(quán)限描述及含義，可以直接參考官方文檔。

1). 查詢權(quán)限

SHOW GRANTS FOR '用戶名'@'主機(jī)名' ;

2). 授予權(quán)限

GRANT 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 TO '用戶名'@'主機(jī)名';

3). 撤銷權(quán)限

REVOKE 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 FROM '用戶名'@'主機(jī)名';

注意事項：

• 多個權(quán)限之間，使用逗號分隔
• 授權(quán)時， 數(shù)據(jù)庫名和表名可以使用 * 進(jìn)行通配，代表所有。

2. 函數(shù)

2.1 字符串函數(shù)

2.2 數(shù)值函數(shù)

常見的數(shù)值函數(shù)如下：

2.3 日期函數(shù)

常見的日期函數(shù)如下：

2.4 流程函數(shù)

流程函數(shù)也是很常用的一類函數(shù)，可以在SQL語句中實現(xiàn)條件篩選，從而提高語句的效率。

3. 約束

3.1 概述

概念：約束是作用于表中字段上的規(guī)則，用于限制存儲在表中的數(shù)據(jù)。
目的：保證數(shù)據(jù)庫中數(shù)據(jù)的正確、有效性和完整性。
分類:

注意：約束是作用于表中字段上的，可以在創(chuàng)建表/修改表的時候添加約束。

3.2 約束演示

案例需求： 根據(jù)需求，完成表結(jié)構(gòu)的創(chuàng)建。需求如下：

對應(yīng)的建表語句為：

CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一標(biāo)識',
name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
age int check (age > 0 && age <= 120) COMMENT '年齡' ,
status char(1) default '1' COMMENT '狀態(tài)',
gender char(1) COMMENT '性別'
);

在為字段添加約束時，我們只需要在字段之后加上約束的關(guān)鍵字即可，需要關(guān)注其語法。我們執(zhí)行上面的SQL把表結(jié)構(gòu)創(chuàng)建完成，然后接下來，就可以通過一組數(shù)據(jù)進(jìn)行測試，從而驗證一下，約束是否可以生效。

insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'),
('Tom2',25,'0','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values (null,19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');
insert into tb_user(name,age,gender) values ('Tom5',120,'男');

上面，我們是通過編寫SQL語句的形式來完成約束的指定，那加入我們是通過圖形化界面來創(chuàng)建表結(jié)構(gòu)時，又該如何來指定約束呢？ 只需要在創(chuàng)建表的時候，根據(jù)我們的需要選擇對應(yīng)的約束即可。

3.3 外鍵約束

3.3.1 介紹

外鍵：用來讓兩張表的數(shù)據(jù)之間建立連接，從而保證數(shù)據(jù)的一致性和完整性。
我們來看一個例子：

左側(cè)的emp表是員工表，里面存儲員工的基本信息，包含員工的ID、姓名、年齡、職位、薪資、入職日期、上級主管ID、部門ID，在員工的信息中存儲的是部門的ID dept_id，而這個部門的ID是關(guān)聯(lián)的部門表dept的主鍵id，那emp表的dept_id就是外鍵,關(guān)聯(lián)的是另一張表的主鍵。

注意：目前上述兩張表，只是在邏輯上存在這樣一層關(guān)系；在數(shù)據(jù)庫層面，并未建立外鍵關(guān)聯(lián)，所以是無法保證數(shù)據(jù)的一致性和完整性的。想解決這個問題就得通過數(shù)據(jù)庫的外鍵約束

3.3.2 語法

1). 添加外鍵

CREATE TABLE 表名(
字段名 數(shù)據(jù)類型,
...
[CONSTRAINT] [外鍵名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名)
REFERENCES 主表 (主表列名) ;

案例:
為emp表的dept_id字段添加外鍵約束,關(guān)聯(lián)dept表的主鍵id。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) referencesdept(id);

添加了外鍵約束之后，我們再到dept表(父表)刪除id為1的記錄，然后看一下會發(fā)生什么現(xiàn)象。 此時將會報錯，不能刪除或更新父表記錄，因為存在外鍵約束。

2). 刪除外鍵

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

3.3.3 刪除/更新行為

添加了外鍵之后，再刪除父表數(shù)據(jù)時產(chǎn)生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:

具體語法為:

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

4. 多表查詢

4.1 多表關(guān)系

項目開發(fā)中，在進(jìn)行數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計時，會根據(jù)業(yè)務(wù)需求及業(yè)務(wù)模塊之間的關(guān)系，分析并設(shè)計表結(jié)
構(gòu)，由于業(yè)務(wù)之間相互關(guān)聯(lián)，所以各個表結(jié)構(gòu)之間也存在著各種聯(lián)系，基本上分為三種：

• 一對多(多對一)
• 多對多
• 一對一

4.1.1 一對多

• 案例: 部門 與 員工的關(guān)系
• 關(guān)系: 一個部門對應(yīng)多個員工，一個員工對應(yīng)一個部門
• 實現(xiàn): 在多的一方建立外鍵，指向一的一方的主鍵

4.1.2 多對多

• 案例: 學(xué)生 與 課程的關(guān)系
• 關(guān)系: 一個學(xué)生可以選修多門課程，一門課程也可以供多個學(xué)生選擇
• 實現(xiàn): 建立第三張中間表，中間表至少包含兩個外鍵，分別關(guān)聯(lián)兩方主鍵

對應(yīng)的SQL腳本:

create table student(
	id int auto_increment primary key comment '主鍵ID',
	name varchar(10) comment '姓名',
	no varchar(10) comment '學(xué)號'
) comment '學(xué)生表';
insert into student values (null, '黛綺絲', '2000100101'),(null, '謝遜',
'2000100102'),(null, '殷天正', '2000100103'),(null, '韋一笑', '2000100104');

create table course(
	id int auto_increment primary key comment '主鍵ID',
	name varchar(10) comment '課程名稱'
) comment '課程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,
(null, 'Hadoop');

create table student_course(
	id int auto_increment comment '主鍵' primary key,
	studentid int not null comment '學(xué)生ID',
	courseid int not null comment '課程ID',
	constraint fk_courseid foreign key (courseid) references course (id),
	constraint fk_studentid foreign key (studentid) references student (id)
)comment '學(xué)生課程中間表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),
(null,2,3),(null,3,4);

4.1.3 一對一

• 案例: 用戶 與 用戶詳情的關(guān)系
• 關(guān)系: 一對一關(guān)系，多用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率
• 實現(xiàn): 在任意一方加入外鍵，關(guān)聯(lián)另外一方的主鍵，并且設(shè)置外鍵為唯一的(UNIQUE)

對應(yīng)的SQL腳本:

create table tb_user(
	id int auto_increment primary key comment '主鍵ID',
	name varchar(10) comment '姓名',
	age int comment '年齡',
	gender char(1) comment '1: 男 , 2: 女',
	phone char(11) comment '手機(jī)號'
) comment '用戶基本信息表';

create table tb_user_edu(
	id int auto_increment primary key comment '主鍵ID',
	degree varchar(20) comment '學(xué)歷',
	major varchar(50) comment '專業(yè)',
	primaryschool varchar(50) comment '小學(xué)',
	middleschool varchar(50) comment '中學(xué)',
	university varchar(50) comment '大學(xué)',
	userid int unique comment '用戶ID',
	constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用戶教育信息表';
insert into tb_user(id, name, age, gender, phone) values
(null,'黃渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'碼云',55,'1','18800008888'),
(null,'李彥宏',50,'1','18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,'本科','舞蹈','靜安區(qū)第一小學(xué)','靜安區(qū)第一中學(xué)','北京舞蹈學(xué)院',1),
(null,'碩士','表演','朝陽區(qū)第一小學(xué)','朝陽區(qū)第一中學(xué)','北京電影學(xué)院',2),
(null,'本科','英語','杭州市第一小學(xué)','杭州市第一中學(xué)','杭州師范大學(xué)',3),
(null,'本科','應(yīng)用數(shù)學(xué)','陽泉第一小學(xué)','陽泉區(qū)第一中學(xué)','清華大學(xué)',4);

4.2 多表查詢概述

4.2.1 數(shù)據(jù)準(zhǔn)備

1). 刪除之前 emp, dept表的測試數(shù)據(jù)
2). 執(zhí)行如下腳本，創(chuàng)建emp表與dept表并插入測試數(shù)據(jù)

-- 創(chuàng)建dept表，并插入數(shù)據(jù)
create table dept(
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '部門名稱'
)comment '部門表';
INSERT INTO dept (id, name) VALUES (1, '研發(fā)部'), (2, '市場部'),(3, '財務(wù)部'), (4,
'銷售部'), (5, '總經(jīng)辦'), (6, '人事部');

-- 創(chuàng)建emp表，并插入數(shù)據(jù)
create table emp(
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '姓名',
	age int comment '年齡',
	job varchar(20) comment '職位',
	salary int comment '薪資',
	entrydate date comment '入職時間',
	managerid int comment '直屬領(lǐng)導(dǎo)ID',
	dept_id int comment '部門ID'
)comment '員工表';

-- 添加外鍵
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),
(2, '張無忌', 20, '項目經(jīng)理',12500, '2005-12-05', 1,1),
(3, '楊逍', 33, '開發(fā)', 8400,'2000-11-03', 2,1),
(4, '韋一笑', 48, '開發(fā)',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '開發(fā)',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序員鼓勵師',6600, '2004-10-12', 2,1),
(7, '滅絕', 60, '財務(wù)總監(jiān)',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '會計',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出納',5250, '2009-05-13', 7,3),
(10, '趙敏', 20, '市場部總監(jiān)',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '職員',3750, '2006-10-03', 10,2),
(12, '鶴筆翁', 19, '職員',3750, '2007-05-09', 10,2),
(13, '方東白', 19, '職員',5500, '2009-02-12', 10,2),
(14, '張三豐', 88, '銷售總監(jiān)',14000, '2004-10-12', 1,4),
(15, '俞蓮舟', 38, '銷售',4600, '2004-10-12', 14,4),
(16, '宋遠(yuǎn)橋', 40, '銷售',4600, '2004-10-12', 14,4),
(17, '陳友諒', 42, null,2000, '2011-10-12', 1,null);

4.2.2 概述

多表查詢就是指從多張表中查詢數(shù)據(jù)。
原來查詢單表數(shù)據(jù)，執(zhí)行的SQL形式為：select * from emp;
那么我們要執(zhí)行多表查詢，就只需要使用逗號分隔多張表即可，如： select * from emp , dept; 具體的執(zhí)行結(jié)果如下:

此時,我們看到查詢結(jié)果中包含了大量的結(jié)果集，總共102條記錄，而這其實就是員工表emp所有的記錄(17) 與 部門表dept所有記錄(6) 的所有組合情況，這種現(xiàn)象稱之為笛卡爾積。

在SQL語句中，如何來去除無效的笛卡爾積呢？ 我們可以給多表查詢加上連接查詢的條件即可。

select * from emp , dept where emp.dept_id = dept.id;

而由于id為17的員工，沒有dept_id字段值，所以在多表查詢時，根據(jù)連接查詢的條件并沒有查詢到。

4.2.3 分類

• 連接查詢

• • 內(nèi)連接：相當(dāng)于查詢A、B交集部分?jǐn)?shù)據(jù)
• • 外連接：
• • 左外連接：查詢左表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
• • 右外連接：查詢右表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
• • 自連接：當(dāng)前表與自身的連接查詢，自連接必須使用表別名

• 子查詢
  

4.3 內(nèi)連接

內(nèi)連接查詢的是兩張表交集部分的數(shù)據(jù)。(也就是綠色部分的數(shù)據(jù))
內(nèi)連接的語法分為兩種: 隱式內(nèi)連接、顯式內(nèi)連接。

1). 隱式內(nèi)連接

SELECT 字段列表 FROM 表1 , 表2 WHERE 條件 ... ;

2). 顯式內(nèi)連接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;

4.4 外連接

外連接分為兩種，分別是：左外連接 和 右外連接。具體的語法結(jié)構(gòu)為：
1). 左外連接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ; 1

2). 右外連接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;

右外連接相當(dāng)于查詢表2(右表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。

4.5 自連接

4.5.1 自連接查詢

自連接查詢，顧名思義，就是自己連接自己，也就是把一張表連接查詢多次。我們先來學(xué)習(xí)一下自連接的查詢語法：

SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

而對于自連接查詢，可以是內(nèi)連接查詢，也可以是外連接查詢。

注意事項:
在自連接查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、返回的字段，到底是哪一張表的字段。

4.5.2 聯(lián)合查詢

對于union查詢，就是把多次查詢的結(jié)果合并起來，形成一個新的查詢結(jié)果集。

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

• 對于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也需要保持一致。
• union all 會將全部的數(shù)據(jù)直接合并在一起，union 會對合并之后的數(shù)據(jù)去重。

4.6 子查詢

4.6.1 概述

1). 概念
SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查詢外部的語句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個。

2). 分類
根據(jù)子查詢結(jié)果不同，分為：
A. 標(biāo)量子查詢（子查詢結(jié)果為單個值）
B. 列子查詢(子查詢結(jié)果為一列)
C. 行子查詢(子查詢結(jié)果為一行)
D. 表子查詢(子查詢結(jié)果為多行多列)

根據(jù)子查詢位置，分為：
A. WHERE之后
B. FROM之后
C. SELECT之后

4.6.2 標(biāo)量子查詢

子查詢返回的結(jié)果是單個值（數(shù)字、字符串、日期等），最簡單的形式，這種子查詢稱為標(biāo)量子查詢。常用的操作符：= <> > >= < <=

4.6.3 列子查詢

子查詢返回的結(jié)果是一列（可以是多行），這種子查詢稱為列子查詢。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

4.6.4 行子查詢

子查詢返回的結(jié)果是一行（可以是多列），這種子查詢稱為行子查詢。
常用的操作符：= 、<> 、IN 、NOT IN

4.6.5 表子查詢

子查詢返回的結(jié)果是多行多列，這種子查詢稱為表子查詢。
常用的操作符：IN

5. 事務(wù)

5.1 事務(wù)簡介

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

就比如: 張三給李四轉(zhuǎn)賬1000塊錢，張三銀行賬戶的錢減少1000，而李四銀行賬戶的錢要增加1000。 這一組操作就必須在一個事務(wù)的范圍內(nèi)，要么都成功，要么都失敗。

注意： 默認(rèn)MySQL的事務(wù)是自動提交的，也就是說，當(dāng)執(zhí)行完一條DML語句時，MySQL會立即隱
式的提交事務(wù)。

5.2 事務(wù)操作

數(shù)據(jù)準(zhǔn)備：

drop table if exists account;
create table account(
	id int primary key AUTO_INCREMENT comment 'ID',
	name varchar(10) comment '姓名',
	money double(10,2) comment '余額'
) comment '賬戶表';
insert into account(name, money) VALUES ('張三',2000), ('李四',2000);

5.2.1 未控制事務(wù)

1). 測試正常情況

-- 1. 查詢張三余額
select * from account where name = '張三';
-- 2. 張三的余額減少1000
update account set money = money - 1000 where name = '張三';
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';

測試完畢之后檢查數(shù)據(jù)的狀態(tài), 可以看到數(shù)據(jù)操作前后是一致的。

2). 測試異常情況

-- 1. 查詢張三余額
select * from account where name = '張三';
-- 2. 張三的余額減少1000
update account set money = money - 1000 where name = '張三';
出錯了....
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';

我們把數(shù)據(jù)都恢復(fù)到2000， 然后再次一次性執(zhí)行上述的SQL語句(出錯了… 這句話不符合SQL語法,執(zhí)行就會報錯)，檢查最終的數(shù)據(jù)情況, 發(fā)現(xiàn)數(shù)據(jù)在操作前后不一致了。

5.2.2 控制事務(wù)一

1). 查看/設(shè)置事務(wù)提交方式

SELECT @@autocommit ;
SET @@autocommit = 0 ;

2). 提交事務(wù)

COMMIT;

3). 回滾事務(wù)

ROLLBACK;

注意：上述的這種方式，我們是修改了事務(wù)的自動提交行為, 把默認(rèn)的自動提交修改為了手動提交, 此時我們執(zhí)行的DML語句都不會提交, 需要手動的執(zhí)行commit進(jìn)行提交。

5.2.3 控制事務(wù)二

1). 開啟事務(wù)

START TRANSACTION 或 BEGIN ;

2). 提交事務(wù)

COMMIT;

3). 回滾事務(wù)

ROLLBACK;

轉(zhuǎn)賬案例：

-- 開啟事務(wù)
start transaction
-- 1. 查詢張三余額
select * from account where name = '張三';
-- 2. 張三的余額減少1000
update account set money = money - 1000 where name = '張三';
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常執(zhí)行完畢, 則提交事務(wù)
commit;
-- 如果執(zhí)行過程中報錯, 則回滾事務(wù)
-- rollback;

5.3 事務(wù)四大特性

• 原子性（Atomicity）：事務(wù)是不可分割的最小操作單元，要么全部成功，要么全部失敗。
• 一致性（Consistency）：事務(wù)完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。
• 隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的獨(dú)立環(huán)境下運(yùn)行。
• 持久性（Durability）：事務(wù)一旦提交或回滾，它對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久的。

5.4 并發(fā)事務(wù)問題

1). 贓讀：一個事務(wù)讀到另外一個事務(wù)還沒有提交的數(shù)據(jù)。

比如B讀取到了A未提交的數(shù)據(jù)。

2). 不可重復(fù)讀：一個事務(wù)先后讀取同一條記錄，但兩次讀取的數(shù)據(jù)不同，稱之為不可重復(fù)讀。

事務(wù)A兩次讀取同一條記錄，但是讀取到的數(shù)據(jù)卻是不一樣的。

3). 幻讀：一個事務(wù)按照條件查詢數(shù)據(jù)時，沒有對應(yīng)的數(shù)據(jù)行，但是在插入數(shù)據(jù)時，又發(fā)現(xiàn)這行數(shù)據(jù)
已經(jīng)存在，好像出現(xiàn)了 “幻影”。

5.5 事務(wù)隔離級別

為了解決并發(fā)事務(wù)所引發(fā)的問題，在數(shù)據(jù)庫中引入了事務(wù)隔離級別。主要有以下幾種：

1). 查看事務(wù)隔離級別

SELECT @@TRANSACTION_ISOLATION;

2). 設(shè)置事務(wù)隔離級別

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事務(wù)隔離級別越高，數(shù)據(jù)越安全，但是性能越低。文章來源地址http://www.zghlxwxcb.cn/news/detail-575327.html

到了這里，關(guān)于MySQL數(shù)據(jù)庫詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！