日韩黑丝制服一区视频播放|日韩欧美人妻丝袜视频在线观看|九九影院一级蜜桃|亚洲中文在线导航|青草草视频在线观看|婷婷五月色伊人网站|日本一区二区在线|国产AV一二三四区毛片|正在播放久草视频|亚洲色图精品一区

搜索
分享

從入門到入土：MySQL完整學(xué)習(xí)指南，包教包會(huì)！

 O聽_海_軒O 2021-04-30

 圖片

 一 SQL 介紹

SQL（Structured Query Language）,語義是結(jié)構(gòu)化語言， 是一門 ANSI 的標(biāo)準(zhǔn)計(jì)算機(jī)語言，用來訪問和操作數(shù)據(jù)庫系統(tǒng)；

二 數(shù)據(jù)庫介紹

2.1 數(shù)據(jù)庫

我們已經(jīng)知道了SQL是操作數(shù)據(jù)庫的語言，那么數(shù)據(jù)庫是什么？數(shù)據(jù)庫（database）是 保存一個(gè)文件或者一組文件的容器；這樣說你可能還不懂，可以比喻為數(shù)據(jù)是一個(gè)圖書館，圖書館里面收藏有好多書，我們可以認(rèn)為數(shù)據(jù)庫就是能夠存儲(chǔ)數(shù)據(jù)的容器；其實(shí)數(shù)據(jù)庫很常見，你每天都在操作數(shù)據(jù)庫，比如你看手機(jī)，上網(wǎng)，微信聊天，其實(shí)背后都在操作數(shù)據(jù)庫，只是展現(xiàn)操作數(shù)據(jù)庫的形式不一樣而已；

2.2 數(shù)據(jù)庫管理系統(tǒng)

數(shù)據(jù)庫里面有好多的數(shù)據(jù)，就像圖書管一樣有好多的書，書是由圖書管理員進(jìn)行管理，那么什么東西能夠管理數(shù)據(jù)庫呢？沒錯(cuò)就是數(shù)據(jù)庫管理系統(tǒng)（Database Manage System），比如 mysql, Access、DB2、Informix、 Server、Oracle、Sybase以及其他數(shù)據(jù)庫系統(tǒng)；

2.3 表

表是一種結(jié)構(gòu)化的文件可以用來存儲(chǔ)特定類型的數(shù)據(jù)；這么說你可能沒聽懂，我們?cè)倥e個(gè)例子，表就像圖書館里面的圖書架子，每個(gè)書架都放了好多書；再不懂也沒關(guān)系，你總用過excel吧，excel表格里面的標(biāo)簽頁 （sheet） 我們就可類比數(shù)據(jù)表，表存放的就是特定類型的數(shù)據(jù)，跟excel的sheet一樣；重點(diǎn)是每個(gè)表的表名是唯一，不可重復(fù)；

2.4 列和數(shù)據(jù)類型

列是表中的一個(gè)字段，一個(gè)表由多個(gè)列組成；每個(gè)列都由特定的數(shù)據(jù)類型，只能存放指定數(shù)據(jù)類型的數(shù)據(jù)；聽不懂沒關(guān)系，我們類比excel，excel中的sheet也是由多個(gè)列組成，每個(gè)列都有指定的數(shù)據(jù)類型，比如文本，數(shù)字，日期類型；那數(shù)據(jù)庫的表跟excel的結(jié)果是類似的；

數(shù)據(jù)類型就是限定表中的每個(gè)列只能存儲(chǔ)特定類型的數(shù)據(jù)，常見的數(shù)據(jù)類型有整型，數(shù)字，文本，字符串，日期等等。

2.5 行

行就是表中的一條記錄，類比excel，很容易理解；

2.6 主鍵

主鍵就是每行的唯一標(biāo)識(shí)，其特性是主鍵，不能為空，不能重復(fù)，不能修改；主鍵我們可以類比身份證號(hào)，每個(gè)身份證號(hào)都是唯一不可重復(fù)的；

2.7 行號(hào)

行號(hào)指表中每個(gè)行的行號(hào)；

圖片
在這里插入圖片描述

三 基本檢索

如果是初學(xué)者，建議去網(wǎng)上尋找安裝Mysql的文章安裝，以及使用navicat連接數(shù)據(jù)庫，以后的示例基本是使用mysql數(shù)據(jù)庫管理系統(tǒng)；

需要建立一張學(xué)生表，列分別是id，名稱，年齡，學(xué)生信息；本示例中文章篇幅原因SQL注釋略；

建表語句：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中的數(shù)據(jù)：

INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (1, 'youku1', 18, '大一新生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (2, 'youku2', 23, '畢業(yè)生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (3, 'jeff', 25, '社會(huì)人士');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (4, 'smile', 17, '高三學(xué)子');

3.1 select 關(guān)鍵字

sql 都是由許多關(guān)鍵字（keyword）組成的語句，關(guān)鍵字是數(shù)據(jù)庫的保留字，用戶不能將其當(dāng)作建表的表名，字段等；表中的數(shù)據(jù)檢索使用select關(guān)鍵字作為開頭進(jìn)行查詢數(shù)據(jù)庫表的信息；

3.2 檢索單個(gè)字段

語句示例：

SELECT name FROM student 

查詢結(jié)果：

youku1
youku2
jeff
smile

語句分析：

select 是 查詢 的意思 ， name 代表表中的字段 ，from 表示來自哪張表，其后面的student就是表；連起來的意思就是查詢字段是name的數(shù)據(jù)來自表student；

3.3 sql語句注意點(diǎn)

多sql語句一起執(zhí)行使用封號(hào)（;）隔開，在拼寫語句時(shí)表的關(guān)鍵字都建議使用大寫，表的字段和表名使用小寫；為了容易閱讀建議將sql分成多行（由于文章篇幅原因就不分了）；sql語言也是使用英文字母，不要開中文，以免造成符號(hào)錯(cuò)誤不容易發(fā)現(xiàn)；sql語句默認(rèn)不區(qū)分大小寫；

3.4 檢索多個(gè)字段

語句示例：

SELECT name,age FROM student;

語句結(jié)果：

youku1 18
youku2 23
jeff 25
smile 17

語句分析：

查詢字段 名字，年齡來自表student；多個(gè)列檢索時(shí)使用逗號(hào)（,）隔開；

3.5 檢索所有字段

語句示例：

SELECT * FROM student;

語句結(jié)果：

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生
3 jeff 25 社會(huì)人士
4 smile 17 高三學(xué)子

語句分析：

通配符 （*）表示返回表中的所有列，如果不是必須建議不要使用通配符，會(huì)影響數(shù)據(jù)庫性能；

3.6 distinct

distinct 表示區(qū)分，意指檢索出來的行是唯一（去重），其放在列的最前面；如果使用了關(guān)鍵字distinct，其作用于后面的所有列（由于本示例數(shù)據(jù)沒有重復(fù)示例，結(jié)果就不是很清晰）；

SELECT DISTINCT name, age FROM student;

語句結(jié)果：

youku1 18
youku2 23
jeff 25
smile 17

3.7 限制條數(shù)

access 和 sql server ：

SELECT TOP 2 name FROM student

TOP 2 表示限制返回前2行

postgresql 、SQLite和 mysql：

SELECT name FROM student LIMIT 2;

limit 2 表示限制返回前2行；

DB2：

SELECT name FROM student FETCH FIRST 2 ROWS ONLY

FETCH FIRST 2 ROWS ONLY 表示只抓取前2行數(shù)據(jù)

語句結(jié)果：

youku1
youku2

3.8偏移

示例：

SELECT name FROM student LIMIT 1 OFFSET 1;

語句分析

表示查詢列名稱來自學(xué)生表 限制條數(shù)1，偏移值1；意思就是查詢學(xué)生表中的第二行數(shù)據(jù)；offset表示跳躍或者偏移；

mysql和MariaDB簡化形式:

SELECT name FROM student LIMIT 1,2;

表示查詢字段名稱來自學(xué)生表，限制2條，偏移1條；注意順序；

語句結(jié)果：

youku2
jeff

四 排序檢索

4.1 ORDER BY 子句

示例：

SELECT name,age FROM student ORDER BY age

語句分析；

檢索字段名稱，年齡來自學(xué)生表按照列年齡排序；注意默認(rèn)是升序，ORDER BY 子句通常在語句末尾；

語句結(jié)果：

smile 17
youku1 18
youku2 23
jeff 25

4.2 多列排序

示例：

SELECT name,age FROM student ORDER BY age DESC, name ASC;

語句分析：

查詢名稱，年齡來自學(xué)生表，按照年齡降序，名稱升序進(jìn)行排序；關(guān)鍵字 DESC(descending) 意指降序，字母默認(rèn)Z-A; ASC（ascending）意指升序，字母默認(rèn)A-Z；多列情況下，每個(gè)列后面指定使用DESC，使用逗號(hào)（,）隔開，如果不寫，默認(rèn)升序；

語句結(jié)果：

jeff 25
youku2 23
youku1 18
smile 17

4.3 按位排序

語句示例：

SELECT name,age FROM student ORDER BY 2 DESC, 1 ASC;

按位指查詢字段的位置，2 對(duì)應(yīng)字段age,1對(duì)應(yīng)字段name；結(jié)果和4.2一致；

五 過濾檢索

sql 語句中過濾條件（filter condition）的關(guān)鍵字是 WHERE，其使用方式是跟在表名之后；

5.1 WHERE語句操作符

根據(jù)不同的數(shù)據(jù)庫管理系統(tǒng)，其支持的操作符略有不同，所以下文較多重復(fù)意思的操作符，應(yīng)查閱官方文檔說明，哪些操作符是支持使用的數(shù)據(jù)庫管理系統(tǒng)；

操作符說明
=等于
>大于
<小于
!=不等于
<>不等于
>=大于等于
<=小于等于
!<不小于
!>不大于
BETWEEN在中間
IS NULL為空

5.2 單條件過濾

示例：

SELECT * FROM student WHERE name = 'jeff';

語句分析;

查詢所有字段來自學(xué)生表 條件是 學(xué)生名稱是 jeff；注意 非表字段，表名，關(guān)鍵字，使用2個(gè)單引號(hào)（''）括起來，里面存放的是我們輸入的數(shù)據(jù)；

語句結(jié)果;

3 jeff 25 社會(huì)人士

5.3 多條件過濾

多條件過濾時(shí)使用 AND 或者 OR 子句；AND連接表達(dá)式表示過濾條件都為真的數(shù)據(jù)；OR連接表達(dá)式表示匹配過濾條件任意一個(gè)；

AND示例：

SELECT * FROM student WHERE age >= '18' AND age <= '23';

語句分析：

查詢所有字段來自學(xué)生表，條件是 學(xué)生年齡大于等于18 并且 學(xué)生年齡小于 23；

語句結(jié)果：

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生

OR示例：

SELECT * FROM student WHERE age >= '18' OR age <= '23';

語句分析：

檢索所有字段來自學(xué)生表，條件是 學(xué)生年齡大于等于18，或者學(xué)生年齡小于23；

OR 和 AND 示例：

SELECT * FROM student WHERE age >= '18' AND (age <= '23' OR id >=2);

語句分析：

在使用OR 和 AND 的時(shí)候應(yīng)該明確過濾條件，然后使用小括號(hào)括起來，由于數(shù)據(jù)庫管理系統(tǒng)是根據(jù)順序執(zhí)行，如果不使用小括號(hào)很容易造成語義錯(cuò)誤；查詢所有字段來自學(xué)生表 過濾條件 年齡大于 18 并且 （年齡小于23 或者 id 大于2）的數(shù)據(jù)；

5.4 范圍查詢

示例：

SELECT * FROM student WHERE  age BETWEEN '18' And '23';

語句分析

BETWEEN 表示范圍查詢，查詢所有字段來自學(xué)生表，過濾條件學(xué)生年齡在18至23之間；

語句結(jié)果：

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生

5.5 空值查詢

示例：

SELECT * FROM student WHERE  age IS NULL;

語句分析：

查詢所有字段來自學(xué)生表，過濾條件 學(xué)生年齡 為空；數(shù)據(jù)庫表不填充數(shù)據(jù)默認(rèn)為空（NULL），當(dāng)然你也可以給指定類型的列設(shè)置默認(rèn)值；

5.6 IN 操作

示例：

SELECT * FROM student WHERE  age IN (18,23,25);

語句分析：

查詢所有字段來自學(xué)生表 ，過濾條件 年齡 是 18 或者 23 或者 25 ；IN是個(gè)范圍查詢，匹配小括號(hào)中指定的任意值，其功能跟OR類似，一個(gè)IN 就相當(dāng)于好多個(gè)OR；

語句結(jié)果：

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生
3 jeff 25 社會(huì)人士

5.7 NOT 操作符

示例：

SELECT * FROM student WHERE  NOT age='25';

語句分析：

NOT 操作符表是否定；其跟在WHERE后面功能類似<>;

六 通配符檢索

6.1 通配符的介紹

通配符是組成匹配模式的特殊字符串；如果有用過正則表達(dá)式的同學(xué)肯定不陌生，這個(gè)與其類似；檢索文本的通配符是用在關(guān)鍵詞Like 后面；

6.2 通配符 %

示例：

SELECT * FROM student WHERE name LIKE 'you%' ;

語句分析：

查詢所有字段來自學(xué)生表，過濾條件 名字匹配以you開頭的字符串后面匹配任意個(gè)任意字符；%通配符代表任意個(gè)任意字符串,0也算在內(nèi)，但不包括null；

語句結(jié)果：

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生

示例：

SELECT * FROM student WHERE name LIKE '%i%' ;

語句分析：

查詢所有列來自學(xué)生表，過濾條件學(xué)生的名稱匹配中間必須出現(xiàn)一次字母i，字母i的前后可以匹配任意個(gè)任意字符串；

語句結(jié)果;

4 smile 17 高三學(xué)子

6.3 通配符 _

通配符 _ 代表匹配一個(gè)字符串；在Access數(shù)據(jù)庫中不是 _ 而是 ?；

示例：

SELECT * FROM student WHERE name LIKE 'youku_' ;

語句分析：

查詢所有列來自學(xué)生表，過濾條件 學(xué)生名稱 匹配模式 youku 后面一個(gè)為任意字符串；

語句結(jié)果;

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生

6.4 通配符 []

通配符 [] 表示匹配指定一個(gè)位置一個(gè)字符；其里面可以存放多個(gè)字符，關(guān)系是or，模式匹配時(shí)只占用一個(gè)位置，Access,SQL SERVER 支持；

七 字段基本操作

7.1 字段拼接

示例：

SELECT concat('你好啊',name,'327今天心情怎么樣') FROM student WHERE id = '1' ;

語句分析：

函數(shù) concat 就是將多個(gè)字符子串拼接成一個(gè)字符串；不同的數(shù)據(jù)庫管理系統(tǒng)其使用的方式略有差別，應(yīng)查閱官方文檔；在 mysql 中使用 concat函數(shù)；在postgresql中使用 || ；在 Access和 sql server中使用 +;

語句結(jié)果：

你好啊youku1327今天心情怎么樣

7.2 去除空白字符串

語句示例：

SELECT  RTRIM('      哥，今天管飽        ') FROM student WHERE id = '1' ;

語句分析：

RTRIM(STR) 函數(shù)是去掉右邊的字符串；TRIM(STR)是去掉字符串兩邊的空白字符；LTRIM(STR)是去掉字符串左邊的空白字符；

語句結(jié)果;

      哥，今天管飽

7.3 別名

語句示例：

SELECT name as student_name FROM student WHERE  id = '1' ;

語句分析：

別名（alias）是可以給字段，或者表起別名；當(dāng)多表操作出現(xiàn)重復(fù)字段時(shí)使用別名是個(gè)很好的選擇；別名可以使用AS關(guān)鍵字，雖然其是可省略，但平時(shí)我們最好加上它增強(qiáng)閱讀性；

7.4 計(jì)算

操作符說明
*乘
+加
-減
/除

語句示例：

SELECT 2 * 8; 

語句結(jié)果：

16

# 八 聚集函數(shù)的使用

先聲明一下，下面的庫表只是簡易的學(xué)習(xí)示例，不是生產(chǎn)的設(shè)計(jì)，不要深究，此文我們的目的是學(xué)習(xí)sql的檢索不是庫表設(shè)計(jì)；初學(xué)者最好跟著作者的文章一步一步敲一遍，如果沒有使用過sql的可以查閱作者SQL系列專欄；

顧客表

CREATE TABLE `customer` (
  `userId` int(11) NOT NULL AUTO_INCREMENT COMMENT '顧客id',
  `userName` varchar(255) DEFAULT NULL COMMENT '顧客名稱',
  `telephone` varchar(255) DEFAULT NULL COMMENT '顧客電話',
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`customer`(`userId`, `userName`, `telephone`) VALUES (1, 'zxzxz', '1327');
INSERT INTO `springboot`.`customer`(`userId`, `userName`, `telephone`) VALUES (2, 'youku1327', '1996');

商品表

CREATE TABLE `product` (
  `productId` int(11) NOT NULL AUTO_INCREMENT COMMENT '產(chǎn)品id',
  `productName` varchar(255) DEFAULT NULL COMMENT '產(chǎn)品名稱',
  `price` varchar(255) DEFAULT NULL COMMENT '產(chǎn)品價(jià)格',
  PRIMARY KEY (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (1, '蘋果', '5');
INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (2, '梨', '4');
INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (3, '香蕉', '3');

訂單表

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `userId` int(11) DEFAULT NULL COMMENT '客戶id',
  `productId` int(11) DEFAULT NULL COMMENT '產(chǎn)品id',
  `orderName` varchar(255) DEFAULT NULL COMMENT '訂單名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (1, 1, 1, '乖乖訂單');
INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (2, 2, 2, '悅悅訂單');
INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (3, 1, 3, '香香訂單');

聚集函數(shù)的定義就是講一些行的數(shù)據(jù)運(yùn)行某些函數(shù)，返回一個(gè)期望值；下面講述的是開發(fā)中經(jīng)常使用到的聚集函數(shù)；

8.1 avg()

avg函數(shù)也就是計(jì)算行的數(shù)量，通過計(jì)算這些行的特定列值和，計(jì)算出平均值(特定列值之和/行數(shù)=平均值)；使用時(shí)注意其會(huì)忽略列值為NULL的行；

語句示例：

SELECT AVG(price) FROM product;

語句結(jié)果：

4

語句分析：查詢價(jià)格平均值來自商品表（5+4+3）/3=4；

8.2 count()

count函數(shù)用于計(jì)算行數(shù)，其中count(*)計(jì)算所有行的數(shù)目，count('column')會(huì)忽略column為NULL的行數(shù)；

語句示例：

SELECT count(*) FROM product;

語句結(jié)果：

3

語句分析：

查詢總行數(shù)來自商品表；

8.3 max()

max函數(shù)返回特定列值的最大值；忽略特定列為NULL的行；

語句示例：

SELECT max(price) FROM product;

語句結(jié)果：

5

語句分析：

查詢價(jià)格的最大值來自商品表；

8.4 min()

返回特定列的最小值；忽略特定列為NULL的行；

語句示例：

SELECT min(price) FROM product;

語句結(jié)果：

3

語句分析：

查詢價(jià)格的最小值來自商品表；

8.5 sum()

返回特定列的和；忽略特定列為NULL的行；

語句示例：

SELECT sum(price) FROM product;

語句結(jié)果：

12

語句分析：

查詢價(jià)格的總和來自商品表；

九 分組數(shù)據(jù)

分組定義就是按照特定的列進(jìn)行分組查詢，使用 GROUP BY 子句進(jìn)行分組查詢；注意點(diǎn)：SELEC后面的列必須出現(xiàn)在group by 子句后面，否則報(bào)語法錯(cuò)誤；通常 group by 子句的位置是where 條件之后，order by 子句之前；

9.1 分組求和

語句示例：

SELECT sum(price) FROM product GROUP BY productName;

語句結(jié)果：

4
5
3

語句分析：

先根據(jù)商品名稱分為三組 蘋果 ，梨 ， 香蕉 ；再根據(jù)不同的分組求和，因?yàn)槲覀儽碇械臄?shù)據(jù)只有這三條所以就是每行的值；

9.2 分組過濾

語句示例：

SELECT count(*) FROM `order` GROUP BY userId HAVING count(*) > 1;

語句結(jié)果：

2

語句分析

查詢 條數(shù)來自 訂單表 根據(jù)客戶id分組，過濾條件 條數(shù)大于2；注意 having 與 where其實(shí)差別不大，通常我們講where當(dāng)作標(biāo)準(zhǔn)的過濾條件，having用作分組過濾條件；注意有的數(shù)據(jù)庫管理系統(tǒng)having不支持別名作為分組過濾條件中的一部分；

9.3 分組排序

語句示例：

SELECT count(*) as count FROM `order` GROUP BY userId ORDER BY count;

語句結(jié)果：

1
2

語句分析查詢 行數(shù) 來自 訂單表 根據(jù) 客戶id分組，根據(jù) 行數(shù)排序；注意點(diǎn)是經(jīng)過分組后結(jié)果看似經(jīng)過排序，其實(shí)并不能確保是排序后的結(jié)果，所以要排序一定要使用order by子句；

十 子查詢

子查詢的定義是在查詢中嵌套查詢；注意子查詢只能返回單列，若企圖返回多列會(huì)報(bào)語法錯(cuò)誤；

語句示例：

SELECT
 userName 
FROM
 customer 
WHERE
 userId = ( SELECT userId FROM `order` WHERE orderName = '乖乖訂單' )

語句結(jié)果：

zxzxz

語句分析：

是執(zhí)行語句 【SELECT userId FROM order WHERE orderName = '乖乖訂單' 】得到結(jié)果 userId = '1' ;然后執(zhí)行語句 【 SELECT userName FROM customer WHERE userId = '1'】；

十一 聯(lián)結(jié)表

聯(lián)結(jié)表也就是我們通常意義上的關(guān)聯(lián)表查詢，主要功能是能在多表中使用一條sql檢索出期望值，但實(shí)際庫表中是存在的，只在查詢期間存在；其主要分為內(nèi)聯(lián)結(jié)和外連接使用的 join  關(guān)鍵字；聯(lián)結(jié)表會(huì)返回一對(duì)多，一對(duì)一，多對(duì)多關(guān)系；聯(lián)結(jié)表不建議超過三張表以上；

11.1 簡單聯(lián)結(jié)

語句示例：

SELECT
 userName,
 orderName 
FROM
 customer,
 `order` 
WHERE
 customer.userId = `order`.userId;

語句結(jié)果：

zxzxz 乖乖訂單
youku1327 悅悅訂單
zxzxz 香香訂單

語句分析 ：

查詢 用戶名來自用戶表，查詢訂單名稱來自訂單表，根據(jù) 訂單表的客戶id 等于 客戶表的客戶id做為聯(lián)結(jié)條件；也就是說會(huì)查詢出兩張表根據(jù)userId為等值條件的 userName 和 orderName 的 數(shù)據(jù);

注意點(diǎn) ：簡單聯(lián)結(jié)中where子句后面 必須 要帶上 兩張表的聯(lián)結(jié)關(guān)系，否則會(huì)出現(xiàn)笛卡爾集（比如3行數(shù)據(jù)聯(lián)結(jié)另一張表3行數(shù)據(jù)會(huì)產(chǎn)生3*3=9條）

11.2 內(nèi)聯(lián)結(jié)

內(nèi)連接（inner join） 又稱等值聯(lián)結(jié)，其查詢結(jié)果跟之前的簡單聯(lián)結(jié)一致；

語句示例：

SELECT
 userName,
 orderName 
FROM
 customer
 INNER JOIN `order` ON ( customer.userId = `order`.userId );

語句結(jié)果：

zxzxz 乖乖訂單
youku1327 悅悅訂單
zxzxz 香香訂單

語句分析：

跟之前的簡單聯(lián)結(jié)稍微不同的是 等值條件 是放在 on 關(guān)鍵字后面，在等值條件后面還可以進(jìn)行 where 子句過濾條件查詢；

11.3 自然聯(lián)結(jié)

自然聯(lián)結(jié)與標(biāo)準(zhǔn)的聯(lián)結(jié)不同就是只返回值唯一的列，不會(huì)返回重復(fù)的列；

自然聯(lián)結(jié)示例：

SELECT
 userName,
 orderName 
FROM
 customer
 INNER JOIN `order` ON ( customer.userId = `order`.userId );

自然聯(lián)結(jié)結(jié)果

zxzxz 乖乖訂單
youku1327 悅悅訂單
zxzxz 香香訂單

非自然聯(lián)結(jié)示例：

SELECT
 * 
FROM
 customersqL
 INNER JOIN `order` ON ( customer.userId = `order`.userId ); 

非自然聯(lián)結(jié)結(jié)果：

1 zxzxz 1327 1 1 1 乖乖訂單
2 youku1327 1996 2 2 2 悅悅訂單
1 zxzxz 1327 3 1 3 香香訂單

重復(fù)列是 userId;

11.4 外聯(lián)結(jié)

右外聯(lián)結(jié)

語句示例：

SELECT
 * 
FROM
 `order`
 RIGHT OUTER JOIN customer ON ( customer.userId = `order`.userId ); 

右外聯(lián)結(jié)是指 相對(duì)于 OUTER JOIN 右邊的表，那么這會(huì)查詢出右邊表的所有數(shù)據(jù) 和根據(jù)等值條件匹配左邊表的數(shù)據(jù)，如果左邊表的數(shù)據(jù)不匹配，那么其返回列的值是NULL充當(dāng)；

左外聯(lián)結(jié)

語句示例：

SELECT
 * 
FROM
 customer
 LEFT OUTER JOIN   `order` ON ( customer.userId = `order`.userId ); 

左外聯(lián)結(jié)是指 相對(duì)于 OUTER JOIN 左邊的表，那么這會(huì)查詢出左邊表的所有數(shù)據(jù) 和根據(jù)等值條件匹配右邊表的數(shù)據(jù)，如果右邊表的數(shù)據(jù)不匹配，那么其返回列的值是NULL充當(dāng)；

區(qū)別：

左外聯(lián)結(jié)和右外聯(lián)結(jié)其實(shí)沒什么不同，只是查詢表順序不一致，我們通過置換 表的相對(duì)位置就可以查詢出一樣的結(jié)果；

十二 組合查詢

組合查詢是指可以執(zhí)行多條SELECT 語句，其查詢的結(jié)構(gòu)是一致的，返回查詢結(jié)果，通常我們稱為復(fù)合操作或者并(union)

語句示例：

SELECT
 userId 
FROM
 customer UNION
SELECT
 userId 
FROM
 `order`

返回結(jié)果：

1
2

語句分析：

union 關(guān)聯(lián)的字段或者聚合函數(shù)在兩張表中必須是相同的，其默認(rèn)會(huì)講結(jié)果進(jìn)行去重處理；如果不去重可以使用 union all

語句示例：

SELECT
 userId 
FROM
 customer UNION ALL
SELECT
 userId 
FROM
 `order`

執(zhí)行結(jié)果：

1
2
1
2
1

語句分析：等同于講客戶表和訂單表的用戶id都合并為一個(gè)并集查詢出來，而且不去重；如果對(duì)組合語句進(jìn)行排序，默認(rèn)是會(huì)作用于組合后的數(shù)據(jù)字段排序，而不是作用于其中的一條查詢語句；

十三 插入

插入數(shù)據(jù)庫記錄也就是使用 INSERT 關(guān)鍵字，能將一條語句插入數(shù)據(jù)庫，高級(jí)的可以組合 SELECT 關(guān)鍵字 實(shí)現(xiàn) 插入查詢的結(jié)果集，插入整張表；

建表語句：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶id',
  `name` varchar(255) DEFAULT NULL COMMENT '用戶名',
  `telephone` varchar(255) DEFAULT NULL COMMENT '用戶電話',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (1, 'youku1', 18, '大一新生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (2, 'youku2', 23, '畢業(yè)生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (3, 'jeff', 25, '社會(huì)人士');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (4, 'smile', 17, '高三學(xué)子');

13.1 插入一條完整數(shù)據(jù)

語句示例：

INSERT INTO `user` ( id, `name`, telephone) VALUES ('2','zszxz','1327');

語句結(jié)果：

2 zszxz 1327

語句分析：

插入 數(shù)據(jù) 到 user 表 字段分別是 id, name , telephone; 值 分別是 2 , zszxz , 1327; 這是插一條完整的語句，雖然INTO可以忽略不寫，但不建議這么做，原因是在數(shù)據(jù)庫管理系統(tǒng)間會(huì)出現(xiàn)移植性問題；還有字段也可以忽略不寫，但也不建議這么做，這容易造成插入數(shù)據(jù)出錯(cuò)；字段的位置和值的位置是一 一對(duì)應(yīng)；如果有的位置沒值可以使用NULL代替；

13.2 插入部分?jǐn)?shù)據(jù)

語句示例：

INSERT INTO `user` ( id, `name`) VALUES ('3','zszxz');

語句結(jié)果：

3 zszxz 

語句分析：

插入數(shù)據(jù)到user表，字段分別是 id , name ; 值分別是，3，zszxz; 可以看見我們沒有插入telephone字段；

13.3 插入檢索數(shù)據(jù)

插入檢索的數(shù)據(jù)也就是能將查詢的結(jié)果插入進(jìn)另一張表；我們可以使用 INSERT SELECT 關(guān)鍵組合成一條語句實(shí)現(xiàn)；

語句示例：

INSERT INTO `user` ( id, `name`) 
SELECT id, `name` FROM student WHERE id = '4';

語句結(jié)果：

4 smile 

語句分析：

插入數(shù)據(jù)到 user 表， 字段分別是 id, name ,值是查詢字段 id ,name 來自 student 表，條件是 id 等于 4；可以看見我們插入數(shù)據(jù)的列根查詢的列名稱是匹配對(duì)應(yīng)的，其實(shí)只要列順序一致即可，不過為了不出錯(cuò)，建議使用名稱匹配；

13.4 復(fù)制表

復(fù)制表即，檢索一張表的數(shù)據(jù)全部插入另一張表；有兩種方法，但是不同的數(shù)據(jù)庫管理系統(tǒng)支持不同，具體的看下文；

語句示例：

SELECT id , `name`  INTO student_copy FROM student;

語句分析

查詢字段 id, name  插入 student_copy 表，來自 student 表；注意 這條語句會(huì)幫我們自動(dòng)創(chuàng)建表 student_copy，由于作者使用的是mysql做演示，這條sql執(zhí)行失敗，原因是其不支持這種方式；如果是想復(fù)制整張表可以使用通配符 * ；

語句示例：

CREATE TABLE student_copy AS 
SELECT * FROM student;

語句結(jié)果：

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生
3 jeff 25 社會(huì)人士
4 smile 17 高三學(xué)子

語句分析：

創(chuàng)建表  student_copy 數(shù)據(jù)結(jié)構(gòu)來源 查詢 所有字段來自 student 表；

十四 更新

更新數(shù)據(jù)庫的行使用 UPDATE 關(guān)鍵字；更新操作是個(gè)很危險(xiǎn)的操作，在每次執(zhí)行前都應(yīng)該檢查是否丟了 where 子句；

14.1 更新所有行

語句示例：

UPDATE student_copy set age = Null;

語句結(jié)果：

1 youku1  大一新生
2 youku2  畢業(yè)生
3 jeff  社會(huì)人士
4 smile  高三學(xué)子

語句分析：

更新 student_copy 表， 設(shè)置 字段 age 值為null；可以看見表中所有的學(xué)生年齡都是Null; 如果有多個(gè)字段需要更新，使用 逗號(hào)隔開；

14.2 更新特定的行

語句示例：

UPDATE student_copy set age = '18' WHERE id = '4';

語句結(jié)果：

4 smile 18 高三學(xué)子

語句分析：

更新 student_copy 設(shè)置 學(xué)生的年齡是 18 條件是 id 等于 4；

14.3 更新來自查詢的結(jié)果集

語句示例：

UPDATE student_copy set age= student.age, name = student.name
FROM student
WHERE student.id = student_copy.id;

語句分析：

更新 student_copy 表 設(shè)置 age 是 student 表的 age，name 是 student 表的 name 條件是 student 的id 等于 student_copy 表的 id;  遺憾的是 mysql 數(shù)據(jù)庫管理系統(tǒng)又執(zhí)行失敗了，其不支持這種方法更新，如果是postgresql就支持，其他數(shù)據(jù)庫應(yīng)查閱官方文檔查看是否支持這種方式更新；

語句示例：

UPDATE student_copy INNER JOIN student on  student.id = student_copy.id 
SET student_copy.age= student.age, student_copy.name = student.name;

語句結(jié)果：

1 youku1 18 大一新生
2 youku2 23 畢業(yè)生
3 jeff 25 社會(huì)人士
4 smile 17 高三學(xué)子

語句分析更新 student_copy 關(guān)聯(lián) student 條件 是 student 的 id 等于 student_copy 表的id ; 設(shè)置 student_copy 表的 age 等于 student 的 age ; 設(shè)置 student_copy 表的 name 等于 student 的 name ；這才是正確進(jìn)入Mysql 的更新查詢姿勢；

十五 刪除表

刪除表中的行可以使用 DELETE 關(guān)鍵字 ，可以刪除特定的行或者全部；使用時(shí)請(qǐng)先看是否丟了where子句；

15.1 刪除整張表數(shù)據(jù) 

DELETE from student_copy;

語句分析

刪除 全部行 來自 student_copy 表；

15.2 刪除特定的行

語句示例：

DELETE from student WHERE id = '4';

語句分析：

刪除 行 來自 student 表?xiàng)l件時(shí) id 等于 4；

15.3 更新和刪除的建議

  1. 每次進(jìn)行操作前檢查是否丟失 where 子句；
  2. 每次操作前最好先使用 select 語句驗(yàn)證；

十六 SQL 分類操作

16.1 SQL 分類

sql 對(duì)數(shù)據(jù)庫的操作分為如下三種類型，如果都學(xué)會(huì)這三種SQL語言熟練對(duì)數(shù)據(jù)庫操作，說明你已經(jīng)對(duì)數(shù)據(jù)庫登堂入室，如果再學(xué)會(huì)數(shù)據(jù)庫高級(jí)操作，說明你對(duì)數(shù)據(jù)庫就有一定的使用經(jīng)驗(yàn)，如果你還學(xué)會(huì)對(duì)數(shù)據(jù)庫進(jìn)行優(yōu)化，分表分庫，讀寫分離等操作，說明你使用數(shù)據(jù)庫已經(jīng)到專家級(jí)別；

  1. DDL：數(shù)據(jù)定義語言（Data Define Language），即定義數(shù)據(jù)的結(jié)構(gòu)。比如：create，drop，alter操作
  2. DML：數(shù)據(jù)管理語言（Data Manage Language），即增刪改查。比如：insert，delete，update，select。這個(gè)也在之前的文章詳細(xì)寫過，不再提及；
  3. DCL：數(shù)據(jù)控制語言（Data Control Language），對(duì)權(quán)限、事務(wù)等的控制。比如：grant（授權(quán)）revoke（取回權(quán)限），commit，roolback等; 事物在上篇已經(jīng)說明，不會(huì)在本篇提及；

16.2 數(shù)據(jù)庫基操

數(shù)據(jù)庫的基本操作如下，也就是我們?nèi)粘Ｊ褂玫牟僮?/p>

  1. 連接數(shù)據(jù)庫：mysql -h 地址 -P 端口 -u 用戶名 -p 密碼；

    mysql -h 192.168.0.127 -P 3306 -u root -p root

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

    SELECT DATABASE();

  3. 顯示用戶活動(dòng)線程：

    SHOW PROCESSLIST;

  4. 顯示系統(tǒng)變量：

    SHOW VARIABLES;

  5. 顯示當(dāng)前時(shí)間，用戶，數(shù)據(jù)庫版本號(hào)

    SELECT now(), user(), version();

  6. 創(chuàng)建數(shù)據(jù)庫：CREATE DATABASE[ IF NOT EXISTS] 數(shù)據(jù)庫名 [數(shù)據(jù)庫選項(xiàng)]

    數(shù)據(jù)庫選項(xiàng)：
    CHARACTER SET 字符集名稱
    COLLATE 排序規(guī)則名稱
    示例：create database zszxz;

  7. 刪除數(shù)據(jù)庫：DROP DATABASE [ IF EXISTS] 數(shù)據(jù)庫名;

    drop  database zszxz;

16.3 建表語句

數(shù)據(jù)庫表的日常操作如下

CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [庫名.]表名 ( 表的結(jié)構(gòu)定義 ) [ 表選項(xiàng)]

其中  TEMPORARY 表示臨時(shí)表；中括號(hào)內(nèi)容都表示可選，在正規(guī)的數(shù)據(jù)庫版本管理開發(fā)會(huì)經(jīng)常使用到；

字段的修飾如下 數(shù)據(jù)類型

  • 非空|空約束：[NOT NULL | NULL]
  • 默認(rèn)值：[DEFAULT default_value]
  • 自動(dòng)增長：[AUTO_INCREMENT]
  • 唯一鍵|主鍵：[UNIQUE [KEY] | [PRIMARY] KEY]
  • 備注：[COMMENT 'string']

表選項(xiàng)一般就是指定數(shù)據(jù)庫引擎和字符集：

ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顧客表';

示例

CREATE TABLE IF NOT EXISTS `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`customer_name` varchar(255) DEFAULT NULL COMMENT '顧客名稱',
`gender` varchar(255) DEFAULT NULL COMMENT '性別',
`telephone` varchar(255) DEFAULT NULL COMMENT '電話號(hào)碼',
`register_time` timestamp NULL DEFAULT NULL COMMENT '注冊(cè)時(shí)間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顧客表';

16.4 修改表結(jié)構(gòu)

查看所有表

SHOW TABLES

查看指定數(shù)據(jù)庫的表

SHOW TABLES FROM 數(shù)據(jù)庫名稱;
示例：SHOW TABLES FROM zszxz;

刪除表

DROP TABLE[ IF EXISTS] 表名;
示例: drop table op;

清空表(清除數(shù)據(jù))

TRUNCATE [TABLE] 表名

復(fù)制表結(jié)構(gòu)

CREATE TABLE 表名 LIKE 要復(fù)制的表名;
示例: create table op like `order`;

復(fù)制表結(jié)構(gòu)和數(shù)據(jù)

CREATE TABLE 表名 [AS] SELECT * FROM 要復(fù)制的表名;
示例: CREATE TABLE op AS SELECT * FROM `order`;

常見的alter操作如下：

增加一列(追加至末尾)

alter table [數(shù)據(jù)庫名.]表名 add [column] 字段 數(shù)據(jù)類型;
示例：alter table `order` add column `year` year ;

增加到第一列

alter table [數(shù)據(jù)庫名.]表名 add [column] 字段 數(shù)據(jù)類型 first;

增加一列到指定字段名后

alter table [數(shù)據(jù)庫名.]表名 add [column] 字段 數(shù)據(jù)類型 after 另一個(gè)字段;

修改字段名的 數(shù)據(jù)類型

alter table [數(shù)據(jù)庫名.]表名稱 modify [column] 字段名 新的數(shù)據(jù)類型;
示例：altert table `order` modify column `gender` tinyint;

修改表字段的數(shù)據(jù)類型，并且移動(dòng)至第一列

alter table [數(shù)據(jù)庫名.]表名稱 modify [column] 字段名 數(shù)據(jù)類型 first;

修改表字段的數(shù)據(jù)類型，并且移動(dòng)至指定字段后面

alter table [數(shù)據(jù)庫名.]表名稱 modify [column] 字段名 數(shù)據(jù)類型 after 另一個(gè)字段名;

修改表字段的名稱

alter table [數(shù)據(jù)庫名.]表名稱 change [column] 舊字段名 新的字段名 數(shù)據(jù)類型;

添加主鍵

alter table [數(shù)據(jù)庫名.]表名稱 ADD PRIMARY KEY(字段名);
示例：altert table `order` add primary key(`id`)

添加唯一鍵

alter table [數(shù)據(jù)庫名.]表名稱 ADD UNIQUE [索引名] (字段名)

添加索引

alter table [數(shù)據(jù)庫名.]表名稱 ADD INDEX [索引名] (字段名)

刪除一列

alter table [數(shù)據(jù)庫名.]表名稱 drop [column] 字段名;
示例：altert table `order` drop column `gender`;

刪除索引

alter table [數(shù)據(jù)庫名.]表名稱 DROP INDEX 索引名

刪除主鍵

alter table [數(shù)據(jù)庫名.]表名稱 DROP PRIMARY KEY

刪除外鍵

alter table [數(shù)據(jù)庫名.]表名稱 DROP FOREIGN KEY 外鍵

十七 視圖

17.1 視圖的概念

視圖其實(shí)就是一張?zhí)摫恚浔举|(zhì)上SQL的檢索語句，所以其不儲(chǔ)存任何的數(shù)據(jù)成分；我們使用視圖有什么好處呢？

  • 簡化查詢SQL，直接對(duì)視圖進(jìn)行查詢，不用管視圖具體生成的細(xì)節(jié)；
  • 可以使用表的部分成為視圖，保護(hù)數(shù)據(jù)，開放用戶權(quán)限時(shí)，可以只開放視圖，而不開放實(shí)體表；
  • 修改數(shù)據(jù)結(jié)構(gòu)，可以直接對(duì)已有的表建立視圖，使用不同的表名，字段名稱；

我們對(duì)視圖的操作只能停留在查詢上，如果是單表生成的視圖還可以進(jìn)行插入數(shù)據(jù)；如果是多表關(guān)聯(lián)生成的視圖，插入不會(huì)起作用；切記任何時(shí)候進(jìn)行關(guān)聯(lián)的時(shí)候如果關(guān)聯(lián)3張表以上就是不符合規(guī)范，嚴(yán)重的拖累查詢性能，視圖也是如此，使用復(fù)雜的嵌套視圖和多表關(guān)聯(lián)也會(huì)極大的降低查詢性能；

17.2 視圖的規(guī)范

  • 視圖是虛表，其有表的部分特性；視圖名 唯一 ，與表名類似；
  • 如果非管理員用戶，創(chuàng)建視圖必須有創(chuàng)建權(quán)限；
  • 視圖本質(zhì)是查詢語句，故視圖可以嵌套，可以與其它表進(jìn)行聯(lián)結(jié)；
  • 視圖不能有索引和觸發(fā)器

17.3 視圖語句

小伙伴們要記得使用視圖之前要看看自己的MYSQL版本，5.0以上支持；

  • 創(chuàng)建視圖 與創(chuàng)建表類似 ， create view 語句用于創(chuàng)建視圖
  • 顯示視圖創(chuàng)建語句 使用 show create view viewName ；
  • 刪除視圖 使用語句 drop view viewName
  • 更新視圖 使用語句 create or replace view

17.4 視圖操作

我們的準(zhǔn)備表如下

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '訂單編號(hào)',
  `order_name` varchar(255) DEFAULT NULL COMMENT '訂單名稱',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `year` year(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

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

INSERT INTO `zszxz`.`order`(`id`, `order_name`, `create_time`, `year`) VALUES (1, '知識(shí)追尋者的訂單', '2020-03-04 11:01:25', 2019);
INSERT INTO `zszxz`.`order`(`id`, `order_name`, `create_time`, `year`) VALUES (2, '小天使的訂單', '2020-03-04 11:01:25', 2020);
INSERT INTO `zszxz`.`order`(`id`, `order_name`, `create_time`, `year`) VALUES (3, '流浪者的訂單', '2020-03-04 11:02:50', 2020);

新建簡單的視圖示例如下，使用 order 表 的 id , order_name , year 三個(gè)字段組成視圖；as 后面就是查詢語句，也可以是子查詢，多表關(guān)聯(lián)等復(fù)雜的查詢語句；

create view `zszxz_order` as SELECT `id` , `order_name`, `year` from `order`

查詢視圖，其使用本質(zhì)與查詢表一樣；示例如下

SELECT * from `zszxz_order`

向視圖中插入數(shù)據(jù)，插入的數(shù)據(jù)實(shí)際在實(shí)體表 order 表中切記；

INSERT INTO `zszxz_order`(`order_name`, `year`) VALUES ('小凡汐的訂單', 2020);

刪除視圖

drop view `zszxz_order`

17.5 小結(jié)

視圖的本質(zhì)上查詢語句，故可以對(duì)一些簡單的數(shù)據(jù)統(tǒng)計(jì)做成視圖是個(gè)不錯(cuò)的選擇；其次如果是開放權(quán)限給第三方公司，使用視圖查詢部分實(shí)體表的數(shù)據(jù)作為開放的表也是對(duì)視圖的合理應(yīng)用；最后，也可以將簡單的表聯(lián)結(jié)做成視圖，簡化開發(fā)；

由于視圖的本質(zhì)是查詢語句，你可以理解為其前世就是查詢，今生就是虛表，徒有其“表”，名不副實(shí)，只擁有表的部分功能；

十八 儲(chǔ)存過程

18.1 儲(chǔ)存過程的概念

我們經(jīng)常使用的SQL查詢語句都是單條語句，如果要使用多條語句達(dá)到一個(gè)目的就顯得力不從心了，儲(chǔ)存過程就是使用多條語句完成業(yè)務(wù)的操作，你可以理解為linux腳本編程類似，window的批處理文件那樣；簡單的定義儲(chǔ)存過程就是多條SQL的集合；

我們使用儲(chǔ)存過程能夠簡化復(fù)雜的單條SQL，相比于單條復(fù)雜的SQL極大提高了性能；如果表結(jié)構(gòu)發(fā)生變化只需要改變儲(chǔ)存過程使用到SQL語句的表名，如果業(yè)務(wù)邏輯發(fā)生變化，只需要跳轉(zhuǎn)儲(chǔ)存過程即可，具有很強(qiáng)的靈活性；建立一次儲(chǔ)存過程，即可使用，不用反復(fù)建立，保證開發(fā)人員使用到都是相同的儲(chǔ)存過程，保證數(shù)據(jù)可靠性；總之使用儲(chǔ)存過程，簡單，靈活，安全可靠，性能好；

18.2 存儲(chǔ)過程語法

  • 創(chuàng)建存儲(chǔ)過程
Create PROCEDURE 儲(chǔ)存過程名稱 （參數(shù)列表）
begin
 過程體
end;
  • 參數(shù)列表
IN 表示輸入; 示例 IN var1 Decimal(6,2)

OUT 表示輸出;示例 IN var2 Decimal(6,2)

INOUT 表示輸入輸出；示例 IN var3 Decimal(6,2)
  • 變量
declare 變量名稱 變量類型 [default value]
  • 執(zhí)行存儲(chǔ)過程
call 儲(chǔ)存過程名稱
  • 刪除儲(chǔ)存過程
DROP PROCEDURE 儲(chǔ)存過程名稱
  • 賦值
使用 set 和 select into 語句為變量賦值。
set @var := 20
select sum(price) into total from table_name
  • if 語句
f 條件 then
    表達(dá)式   
[elseif 條件 then
    表達(dá)式]
...
[else
    表達(dá)式]
end if;
  • case語句
CASE 值 WHEN 匹配值 THEN 結(jié)果
[WHEN 匹配值 THEN 結(jié)果]
......
[ELSE 結(jié)果]
END
  • while語句
[開始標(biāo)簽:]while 條件 do
    循環(huán)體
[結(jié)尾標(biāo)簽]    
end while ;
  • loop 語句
[開始標(biāo)簽:] loop
語句體
[結(jié)尾標(biāo)簽]
end loop;
  • iterate/ leave語句
其是通過標(biāo)簽可以實(shí)現(xiàn)；iterate 表示迭代， leave表示離開
  • repeat語句
repeat
--循環(huán)體
until 循環(huán)條件  
end repeat;

tip : 如果你是用命令行學(xué)習(xí)，在寫多行SQL的時(shí)候 使用 // 可以實(shí)現(xiàn)換行喲！

18.3 儲(chǔ)存過程示例

我們準(zhǔn)備表如下，一個(gè)訂單明細(xì)表；

CREATE TABLE `oder_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `detail_name` varchar(255) DEFAULT NULL COMMENT '訂單明細(xì)',
  `price` decimal(10,2) DEFAULT NULL COMMENT '價(jià)格',
  `oid` int(11) DEFAULT NULL COMMENT '訂單id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='訂單明細(xì)表';

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

INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1, '毛巾', 20.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2, '牙膏', 15.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3, '杯子', 5.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4, '毛巾', 15.00, 2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5, '杯子', 15.00, 2);

無參儲(chǔ)存過程

查看訂單明細(xì)的所有訂單名稱,示例如下，跟普通的查詢語句沒區(qū)別；

create procedure slelect_detail()
begin
 select detail_name from oder_detail;
end; 

我們?cè)僬{(diào)用儲(chǔ)存過程

call slelect_detail();

此時(shí)就會(huì)打印內(nèi)容如下

毛巾
牙膏
杯子
毛巾
杯子

刪除儲(chǔ)存過程

drop procedure slelect_detail;

帶入?yún)?chǔ)存過程示例

現(xiàn)在我們需要查詢oid為動(dòng)態(tài)的所有訂單明細(xì)名稱，由于考慮到oid為動(dòng)態(tài)，就是需要用戶自己輸入，故將oid作為入?yún)?

create procedure slelect_detail(IN order_id INT)
begin
 select detail_name from oder_detail where oid = order_id;
end;

調(diào)用儲(chǔ)存過程，只查詢oid為1的用戶的訂單明細(xì)名稱

call slelect_detail(1);

打印內(nèi)容

毛巾
牙膏
杯子

刪除儲(chǔ)存過程

drop procedure slelect_detail;

帶入?yún)⒑统鰠⒌拇鎯?chǔ)過程示例

查詢?nèi)我庥脩舻挠唵蚊骷?xì)的所有金額；定義入?yún)⒂唵蝘d 為 order_id ， 輸出總金額為 total；

create procedure slelect_toatal_money(IN order_id INT, OUT total DECIMAL(8,2))
begin
 select sum(price) into total from oder_detail where oid = order_id;
end;

調(diào)用儲(chǔ)存過程示例

call slelect_toatal_money(1,@total);

查詢 order_id 為1 總金額示例

SELECT @total;

輸出為 40；

刪除儲(chǔ)存過程

drop procedure slelect_toatal_money;

18.4 if 語句示例

上節(jié)的儲(chǔ)存過程都是單條SQL，這次我們開始使用控制流程，實(shí)現(xiàn)復(fù)雜的儲(chǔ)存過程；

知識(shí)追尋者對(duì)輸入的 order_id 自動(dòng)加5 ，然后判斷 var 是否 小于7 ，如果是就查詢訂單明細(xì)價(jià)格，否則查詢訂單明細(xì)價(jià)格總和；

create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定義變量
declare var  int;
-- 賦值
set var= order_id+5;
-- if 判斷
if var<7 then
select price  from oder_detail where oid = order_id;
else
select sum(price)  from oder_detail where oid = order_id;
end if;
end;

調(diào)用

call slelect_toatal_money(1);

輸出

price
20
15
5

調(diào)用

call slelect_toatal_money(2);

輸出

sum(price)
30

18.5 while 語句示例

對(duì) 變量 var 進(jìn)行判斷，如果 var <7 就執(zhí)行 查詢價(jià)格語句，并且var 進(jìn)行自增；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定義變量
 declare var  int;
 -- 賦值
 set var= order_id+5;
 -- while
 while var<7 do
  select price  from oder_detail where oid = order_id;
  set var = var + 1;
 end while;
end;

調(diào)用示例

call slelect_toatal_money(1);

輸出

price 
20
15
5

18.6 case語句示例

如下語句實(shí)現(xiàn)的效果與上面if語句實(shí)現(xiàn)效果一致；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定義變量
 declare var  int;
 -- 賦值
 set var:= order_id;
 -- case 判匹配
 case var  
 when 1 then 
  select price  from oder_detail where oid = order_id;
 when 2 then
  select sum(price)  from oder_detail where oid = order_id;
 end case;
end;

調(diào)用示例

call slelect_toatal_money(2);

輸出

sum(price)
30

將參數(shù)改為1試試結(jié)果

18.7 loop語句

如果 var 小于3 就 計(jì)算 價(jià)格 + var 的值；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定義變量
 declare var  int;
 -- 賦值
 set var:= order_id;
 -- loop
 select_loop :  loop
  select price+var  from oder_detail where oid = order_id;
  set var = var +1;
  -- 跳出循環(huán)
  if var > 3 then
   leave select_loop;
  end if;
 end loop;
end;

調(diào)用示例

call slelect_toatal_money(1);

會(huì)輸出三組結(jié)果

18.8 repeat

repeat 與 while 的不同之處就是 ，while 在 執(zhí)行之前檢查條件，其實(shí)執(zhí)行之后檢查條件；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定義變量
 declare var  int;
 -- 賦值
 set var= order_id+5;
 -- repeat循環(huán)
 repeat
  select price  from oder_detail where oid = order_id;
  set var = var + 1;
  until var>7
 end repeat;
end;

調(diào)用示例

call slelect_toatal_money(1);

此時(shí)會(huì)輸出2組相同結(jié)果；

price
20
15
5

tip: loop, while , repeat , iterate 都是循環(huán)，loop,while, repeat 功能幾乎相同；iterate可以通過標(biāo)簽的形式調(diào)用 循環(huán)，與 leave 語句使用方式一樣；

十九 游標(biāo)

19.1 游標(biāo)的概念

游標(biāo)的本質(zhì)就是查詢后的結(jié)果集；當(dāng)我們對(duì)查詢的結(jié)果集進(jìn)行前一行或者后一行類似的操作時(shí)就可以使用到游標(biāo)

19.2 游標(biāo)的語法

  • 首先需要 定義游標(biāo)；declare 游標(biāo)名稱 cursor for  查詢語句；
  • 其次，打開游標(biāo)；open 游標(biāo)名稱
  • 然后，對(duì)查詢的結(jié)果集 即游標(biāo)進(jìn)行 檢索行至變量提供使用
  • 最后關(guān)閉游標(biāo)；close 游標(biāo)名稱

19.3 使用游標(biāo)

準(zhǔn)備的表

CREATE TABLE `oder_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `detail_name` varchar(255) DEFAULT NULL COMMENT '訂單明細(xì)',
  `price` decimal(10,2) DEFAULT NULL COMMENT '價(jià)格',
  `oid` int(11) DEFAULT NULL COMMENT '訂單id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='訂單明細(xì)表';

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

INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1, '毛巾', 20.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2, '牙膏', 15.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3, '杯子', 5.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4, '毛巾', 15.00, 2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5, '杯子', 15.00, 2);

簡單的使用游標(biāo)

查詢oid為1 的訂單明細(xì)名稱 的結(jié)果集作為游標(biāo)；

打開游標(biāo)后抓取每行將結(jié)果賦值給變量name

CREATE PROCEDURE printName()
BEGIN
 -- 訂單名稱
    declare name varchar(20);
    -- 創(chuàng)建游標(biāo)
    declare cur cursor for select detail_name from oder_detail where oid = '1';
    -- 打開游標(biāo)
    open cur;
  fetch cur into name;
  select name;
    -- 關(guān)閉游標(biāo)
    close cur;
END;

調(diào)用儲(chǔ)存過程

call printName;

打印結(jié)果如下，只有一條數(shù)據(jù)，說明上述方式只在游標(biāo)中抓取到一條數(shù)據(jù)，而且是表里面行號(hào)最小的行；

name
------
毛巾

在循環(huán)中使用游標(biāo)

將 查詢oid為1的 結(jié)果集賦值給游標(biāo)；通過游標(biāo)抓取每行 將 訂單明細(xì)名稱和價(jià)格分別賦值給變量 name 和 detail_price;  在 循環(huán)無法繼續(xù)時(shí) 會(huì)出現(xiàn) SQLSTATE '02000' ; 即此通過 變量 continue 時(shí)設(shè)置 done 為1 代表 true，此時(shí)循環(huán)結(jié)束，跳出循環(huán)；

drop procedure if exists  printDetail;
CREATE PROCEDURE printDetail()
BEGIN
 -- 訂單名稱
    declare name varchar(20);
  -- 價(jià)格
    declare detail_price decimal(8,2);
    -- 結(jié)束標(biāo)志變量（默認(rèn)為假）
    declare done boolean default 0;
    -- 創(chuàng)建游標(biāo)
    declare cur cursor for select detail_name,price from oder_detail where oid = '1';
    -- 指定游標(biāo)循環(huán)結(jié)束時(shí)的返回值
    declare continue HANDLER for SQLSTATE '02000' set done = 1;
    -- 打開游標(biāo)
    open cur;
    -- 循環(huán)游標(biāo)數(shù)據(jù)
    detail_loop:loop
   -- 根據(jù)游標(biāo)當(dāng)前指向的一條數(shù)據(jù)
   fetch cur into name,detail_price;
   select name , detail_price;
   -- 判斷游標(biāo)的循環(huán)是否結(jié)束
   if done then
     -- 跳出游標(biāo)循環(huán)
     leave detail_loop;    
   end if;
  -- 結(jié)束游標(biāo)循環(huán)
    end loop;
    -- 關(guān)閉游標(biāo)
    close cur;
END;

調(diào)用儲(chǔ)存過程

-- 調(diào)用存儲(chǔ)過程
call printDetail();

美中不足的是會(huì)多遍歷最后一行，如果要精細(xì)處理還是需要自定義標(biāo)志位進(jìn)行跳出循環(huán)；

二十 觸發(fā)器

20.1觸發(fā)器的概念

觸發(fā)器是指當(dāng)表發(fā)生改變的時(shí)候觸發(fā)的動(dòng)作；聽起來有點(diǎn)抽象，舉個(gè)栗子，當(dāng)你往表中插入數(shù)據(jù)的時(shí)候，此時(shí)表發(fā)生了改變，現(xiàn)在想要在每次插入數(shù)據(jù)之前檢測所有的入?yún)⑹欠穸际切?，此時(shí)就可以用觸發(fā)器來檢測；經(jīng)過上面的分析知道使用一個(gè)基本的觸發(fā)器，至少表要發(fā)生改變，還要滿足一個(gè)被觸發(fā)的事件；

表發(fā)生改變通常指 增刪改，其動(dòng)作可以發(fā)生在增刪改 之前或者之后；觸發(fā)事件就是我們需要寫的儲(chǔ)存過程；

  • update (after/ before)
  • insert (after/ before)
  • delete (after/ before)

20.2 觸發(fā)器的基本語法

  • 創(chuàng)建觸發(fā)器：create trigger 觸發(fā)器名稱 觸發(fā)動(dòng)作  on 表名 for each row [觸發(fā)事件]
  • 刪除觸發(fā)器：drop trigger 觸發(fā)器名稱；
  • 查看觸發(fā)器：show triggers;

tip : 觸發(fā)器是依賴于表創(chuàng)建，沒有表就沒有觸發(fā)器，比如視圖，臨時(shí)表都不是真實(shí)的表，它們是沒有觸發(fā)器；一般來說每個(gè)表都有觸發(fā)器的限制，一般最多支持6個(gè)不同類型的觸發(fā)器；由于使用觸發(fā)器會(huì)頻繁的改變表的每行，故其十分影響性能，特別對(duì)一些更新頻率比較快的大表，如果設(shè)置觸發(fā)器就非常占用系統(tǒng)資源；一般來說觸發(fā)器用在表變動(dòng)較小的小表, 不使用觸發(fā)器就立即刪除；

20.3 insert 觸發(fā)器示例

創(chuàng)建觸發(fā)器; 創(chuàng)建一個(gè)觸發(fā)器 getPrice 作用于 oder_detail 表的每行，每當(dāng) 插入數(shù)據(jù)之后就查詢這條訂單明細(xì)的價(jià)格賦值給變量 @price ；小伙伴可能會(huì)疑惑 NEW 是何物，其是一張?zhí)摫恚涗浾弑徊迦霐?shù)據(jù)的行；故我們能在NEW表中獲取每次插入的數(shù)據(jù)；

-- insert 觸發(fā)器
CREATE TRIGGER getPrice AFTER INSERT ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;

檢測插入觸發(fā)器; 插入一條數(shù)據(jù)，使用查詢語句查詢變量 顯示為 20；

-- 檢測插入觸發(fā)器

INSERT INTO `oder_detail`( `detail_name`, `price`, `oid`) VALUES ( '臉盆', 20.00, 2);

select @price;

刪除觸發(fā)器;

-- 刪除觸發(fā)器
drop trigger getPrice;

20.4 update 觸發(fā)器示例

將插入后觸發(fā)器改為更新后的觸發(fā)器如下 ， 只需要改動(dòng) after insert 為 after update 即可；

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;

將之前的插入的SQL語句進(jìn)行修改價(jià)格,并查詢價(jià)格,此時(shí)價(jià)格為30；NEW虛表儲(chǔ)存的是即將更新的數(shù)據(jù)；

UPDATE `oder_detail` SET `price` = 30.00 WHERE `id` = 6;

select @price;

刪除觸發(fā)器

-- 刪除觸發(fā)器
drop trigger getPrice;

將 更新觸發(fā)器的NEW表改為OLD表

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT OLD.price INTO @price;

更新價(jià)格為40

UPDATE `oder_detail` SET `price` = 40.00 WHERE `id` = 6;

此時(shí)查詢 價(jià)格為30，說明OLD表觸發(fā)的是原始數(shù)據(jù)值；

select @price;

tip ：更新觸發(fā)器主要是要搞懂OLD存放原始數(shù)據(jù)，NEW存放即將更新的數(shù)據(jù)；NEW表可以設(shè)置更改值，二OLD表是只讀；

20.5 delete 觸發(fā)器

將 更新觸發(fā)器改為 delete 觸發(fā)器, 之前我們省略了 begin, end 如果是多條執(zhí)行語句則需要加上；

CREATE TRIGGER getPrice AFTER delete ON oder_detail FOR EACH ROW
begin 
SELECT OLD.price INTO @price;
end;

刪除之前的SQL數(shù)據(jù)

delete from oder_detail where `id` = 6;

查詢價(jià)格為40，OLD表存放的是將要被刪除的數(shù)據(jù)；

select @price;

二十一 用戶操作

有關(guān)用戶賬號(hào)的信息儲(chǔ)存mysql的MYSQL數(shù)據(jù)庫，故如果需要查看用戶信息，則需要進(jìn)入MYSQL庫；

21.1查看用戶信息

user表儲(chǔ)存了所有的登陸賬號(hào)；使用mysql庫查詢user表中的user；

use mysql;
select `user` from user;

打印

user
----
root
mysql.session
mysql.sys
root

21.2 創(chuàng)建用戶

CREATE USER 用戶名 IDENTIFIED BY [PASSWORD] 密碼

示例：創(chuàng)建用戶 zszxz ,并指定密碼為 zszxz；

create user zszxz IDENTIFIED by 'zszxz';

21.3 重名名用戶

rename user 舊的用戶名 to 新的用戶名;

示例重命名 用戶 zszxz 為 lsc

rename user zszxz to lsc;

21.4 刪除用戶

drop user 用戶名

示例：刪除用戶lsc

drop user lsc;

21.5 更改密碼

SET PASSWORD FOR 用戶名 = PASSWORD('密碼')

示例：為用戶 zszxz 更改密碼為 lsc

SET PASSWORD FOR zszxz = PASSWORD('lsc')

二十二 權(quán)限操作

22.1 查看用戶權(quán)限

SHOW GRANTS FOR 用戶名

示例：查看用戶zszxz 擁有的權(quán)限

SHOW GRANTS FOR zszxz

打印

Grant for zszxz@%
----------------
GRANT USAGE ON *.* TO 'zszxz'@'%'

查詢出一條權(quán)限，但 USAGE 表示 根本沒有權(quán)限；

22.2 授予權(quán)限

GRANT 權(quán)限 ON 表名 TO 用戶名 [IDENTIFIED BY [PASSWORD] 'password']

常見的權(quán)限 all, create, drop, insert, update, delete,select；

示例 給用戶zszxz 分配 zszxz 庫中的所有表的查詢權(quán)限；

grant select on zszxz.* to zszxz;

再看下 zszxz變成 2 條

Grant for zszxz@%
----------------
GRANT USAGE ON *.* TO 'zszxz'@'%'
GRANT SELECT ON `zszxz`.* TO 'zszxz'@'%'

22.3 撤銷權(quán)限

REVOKE 權(quán)限列表 ON 表名 FROM 用戶名；

示例：撤銷 用戶 zszxz 對(duì) zszxz庫里面所有表的查詢操作；

revoke select on  zszxz.* from zszxz

22.4 權(quán)限列表

使用授權(quán)，撤銷權(quán)限時(shí)可以參考如下權(quán)限列表；

ALL --- 除GRANT OPTION外的所有權(quán)限
ALTER ---使用ALTER TABLE
ALTER ROUTINE ---使用ALTER PROCEDURE和DROP PROCEDURE
CREATE ---使用CREATE TABLE
CREATE ROUTINE ---使用CREATE PROCEDURE
CREATE TEMPORARY TABLES ---使用CREATE TEMPORARY TABLE
CREATE USER ---使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW ---使用CREATE VIEW
DELETE ---使用DELETE
DROP ---使用DROP TABLE
EXECUTE ---使用CALL和存儲(chǔ)過程
FILE ---使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION ---使用GRANT和REVOKE
INDEX ---使用CREATE INDEX和DROP INDEX
INSERT ---使用INSERT
LOCK TABLES ---使用LOCK TABLES
PROCESS ---使用SHOW FULL PROCESSLIST
RELOAD ---使用FLUSH
REPLICATION CLIENT ---服務(wù)器位置的訪問
REPLICATION SLAVE ---由復(fù)制從屬使用
SELECT ---使用SELECT
SHOW DATABASES ---使用SHOW DATABASES
SHOW VIEW ---使用SHOW CREATE VIEW
SHUTDOWN ---使用mysqladmin shutdown（用來關(guān)閉MySQL）
SUPER ---使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。還允許mysqladmin調(diào)試登錄
UPDATE ---使用UPDATE
USAGE ---無訪問權(quán)限

二十三 mysql 架構(gòu)與鎖

23.1 MYSQL架構(gòu)概覽

MYSQL 的層級(jí)大概可以分為3類；第一層 為 連接層，只要負(fù)責(zé)MYSQL的數(shù)據(jù)庫連接，安全認(rèn)證的功能；

第二層是MYSQL的核心層面，其主要功能包括，MYSQL的查詢，緩存，執(zhí)行計(jì)劃，優(yōu)化等都在第二層實(shí)現(xiàn)；

第三層是引擎層，為MYSQL指定不同的引擎將達(dá)到不同的數(shù)據(jù)操作效果；

圖片

23.2 Query Cache

MYSQL 的 Query Cache 是 基于 hash 值計(jì)算進(jìn)行匹配的緩存機(jī)制；通常在大數(shù)據(jù)量的情況下如果開啟Query Cache 會(huì) 頻繁的計(jì)算Hash ，會(huì)增加性能的消耗，得不償失，生產(chǎn)環(huán)境中建議關(guān)閉該選項(xiàng)；

可以使用 語句 show VARIABLES like '%query_cache%' 查看 Query Chach 是否關(guān)閉；我們主要關(guān)注的是 參數(shù)

query_cache_type 是否關(guān)閉 ，如果是OFF就是關(guān)閉狀態(tài)，ON是開啟狀態(tài)；而不是 query_cache_size參數(shù)，其是緩存分配大小；更多關(guān)于 Query Chach 的內(nèi)容可以參考如下文章

https://blog.csdn.net/dongnan591172113/article/details/52084041

https://www.jianshu.com/p/3ab10180fbd5

23.3 讀鎖

關(guān)于鎖的知識(shí)希望讀者學(xué)習(xí)過高并發(fā)相關(guān)知識(shí)，對(duì)所有的鎖分類有個(gè)清晰的認(rèn)識(shí)，學(xué)習(xí)本篇關(guān)于鎖的概念將不會(huì)遇到阻礙；在MYSQL中根據(jù)不同的引擎，主要會(huì)出現(xiàn)三類鎖的情況，即 表鎖，讀鎖 和寫鎖；讀鎖很好理解，在MYSQL 中 讀鎖也是共享鎖, 即 多用戶狀態(tài)下同一時(shí)間對(duì)資源的讀取是互不影響，但不能對(duì)數(shù)據(jù)進(jìn)行修改等操作；

一般情況下我們手動(dòng)給一條或者某個(gè)范圍內(nèi)(一般使用在儲(chǔ)存過程)的數(shù)據(jù)加上讀鎖；

使用語法示例如下

SELECT 字段 from 表名 [where 條件]  lock in share mode;

23.4 寫鎖

寫鎖是排他鎖，也稱為獨(dú)占鎖；使用的情況一般是寫入數(shù)據(jù)的情況下，一個(gè)用戶如果獲得寫鎖，其它用戶將不能獲取寫鎖或者讀鎖，直到該用戶執(zhí)行完操作并釋放鎖；其使用方式為在執(zhí)行語句后加上for update 語句即可

格式示例

SELECT 字段 from 表名 [where 條件] for update;

23.5 鎖粒度

鎖粒度是指對(duì)資源鎖定范圍的一個(gè)程度，使用不同的鎖定策略達(dá)到并發(fā)性能較優(yōu)的結(jié)果；通常鎖粒度使用策略情況分為，行鎖，表鎖，頁鎖的情況；

**表鎖：**即對(duì)整張表進(jìn)行加鎖，其性能開銷較小，加鎖的速度較快，但缺點(diǎn)也很明顯，其鎖粒度大，并發(fā)低；如果要手動(dòng)加表鎖，語法示例 lock tables 表名，釋放鎖 unlock tables 表名；

行鎖：即對(duì)行進(jìn)行鎖定，能夠最大支持并發(fā)量，故鎖粒度最小，但其枷鎖速度慢，性能消耗大，會(huì)出現(xiàn)死鎖；行鎖的種類又有 記錄鎖（主鍵或者唯一索引都屬于記錄鎖），間隙鎖（GAP），記錄鎖和間隙鎖的組合（next -key lock）；間隙鎖一般用于查詢條件是范圍情況下，而非相等條件；

頁鎖：通常情況下遇不到頁鎖，其開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間；

Tip: MyISAM和 Memory 引擎 支持表鎖，其會(huì)自動(dòng)給SELECT,UPDATE,INSERT,DELETE 自動(dòng)加表鎖；InnoDB 支持表鎖和行鎖，對(duì)于UPDATE, INSERT ,DELETE 語句 InnoDB 會(huì)自動(dòng)給數(shù)據(jù)加排他鎖，SELECT語句不加鎖；

還有鎖的其它分類也會(huì)使用到比如樂觀鎖（基于版本號(hào)實(shí)現(xiàn)），注意點(diǎn)是條件必須是主鍵，讀取時(shí)將數(shù)據(jù)版本號(hào)讀出，更新數(shù)據(jù)時(shí)，版本號(hào)加1；將查詢的數(shù)據(jù)進(jìn)行對(duì)比，如果版本號(hào)不一致就是過期數(shù)據(jù)；

查詢示例

select id,value,version from 表名 where id = #{id}

更新示例

update 表名
set value=2,version=version+1
where id=#{id} and version=#{version}

悲觀鎖（如表鎖，行鎖，讀寫鎖都是悲觀鎖）；

如果看了知識(shí)追尋者寫的鎖知識(shí)還有困惑可以參考如下鏈接

https:///post/5b82e0196fb9a019f47d1823

23.6 引擎簡介

在上面的圖例中可以看見MYSQL支持多種引擎，當(dāng)然遠(yuǎn)不止圖中顯示的引擎數(shù)量；我們主流使用的引擎就是 InnoDB,其次是 MyISAM，特殊情況下會(huì)使用到Memory；引擎的知識(shí)是一本書都無法概括的內(nèi)容，知識(shí)追尋者在這邊給小伙伴們做個(gè)簡介，有個(gè)大概的了解就好；

InnoDB 是使用最廣泛的引擎，也是最重要的引擎，讀者有必要了解其儲(chǔ)存性能；InnoDB 是 可重復(fù)讀的事物隔離級(jí)別，但其實(shí)現(xiàn)了next key lock ，防止的幻讀出現(xiàn)；其基于聚簇索引實(shí)現(xiàn)；其組要組成結(jié)構(gòu)為內(nèi)存結(jié)構(gòu)，線程，磁盤文件組；

MyISAM在早期版本是MYSQL的默認(rèn)引擎，在MYSQL5.1之后不再使用；其不支持事物，不支持行鎖，默認(rèn)表鎖，并發(fā)量低，；

Menory引擎故名思意，其儲(chǔ)存內(nèi)容都是存放在引擎當(dāng)中，支持Hash和Btree索引，其數(shù)據(jù)讀取快，但缺點(diǎn)也很明顯，服務(wù)器如果發(fā)生故障重啟后就會(huì)造成數(shù)據(jù)丟失；

二十四 鎖等待

鎖等待的意思非常好理解，就是session （事物會(huì)話，開啟一個(gè)事物代表一個(gè)會(huì)話）A 對(duì) 某行數(shù)據(jù)獲取獨(dú)占鎖（在這邊一般就是寫鎖），然后session B 對(duì)相同的行進(jìn)行獲取獨(dú)占鎖就發(fā)生了鎖等待；簡單理解就是 小孩子搶玩具，誰先搶到 誰 先玩，沒搶到的玩具的孩子只能 等待 搶到玩具孩子玩膩了再給你，瞬間淚奔有木有，就是這么殘酷，當(dāng)然MYSQL 沒 這么殘忍 其 還是有一個(gè)保留參數(shù) innodb_lock_wait_timeout 指定死鎖 的時(shí)間，如果超過 死鎖等待時(shí)間就是報(bào)異常；

知識(shí)追尋者 做個(gè)實(shí)驗(yàn)：

session A 執(zhí)行如下語句，開啟事物，更新索引為1 的語句；此時(shí) session A  獲取了 id= 1 這條 語句的 寫鎖權(quán)限；

BEGIN;
update  `order` set `year`= '2022' where id = '1';

session B 執(zhí)行如下 語句 , 跟 上面的語句一樣 ，由于 id =1 這條數(shù)據(jù)的寫鎖已經(jīng)被session A 獲取，故會(huì)發(fā)生鎖等待的情況；

BEGIN;
update  `order` set `year`= '2022' where id = '1';

知識(shí)追尋者這邊默認(rèn)等待了50秒 就報(bào)了如下異常

Lock wait timeout exceeded; try restarting transaction

查看 默認(rèn)鎖等待 語句

show  VARIABLES like  'innodb_lock_wait_timeout'

二十五 死鎖

25.1 死鎖的產(chǎn)生

死鎖 就是 兩個(gè)以上的會(huì)話 在 搶占 資源過程中 ，產(chǎn)生相互等待的情況；有點(diǎn)繞是不是，其實(shí)很簡單 死鎖是建立在 鎖等待的基礎(chǔ)上，session A 獲取 id = 1 的寫鎖 ， session B 獲取 id =2 的寫鎖 ，此時(shí)由于索引不同，故不會(huì)長生鎖等待現(xiàn)象 ；當(dāng) session A 嘗試 獲取 id =2 的 寫鎖時(shí) ，由于 id = 2  寫鎖已經(jīng)被 session B 獲取 ，故產(chǎn)生鎖等待；當(dāng) session B 嘗試 獲取 id = 1 的寫鎖時(shí)  ，由于id =1  寫鎖已經(jīng)被 session A 獲取, 此時(shí) 產(chǎn)生鎖等待；由于 session A 與 session B 同時(shí) 都在 鎖 等待狀態(tài)，產(chǎn)生了等待對(duì)方釋放鎖，故會(huì)產(chǎn)生死鎖；

知識(shí)追尋者做個(gè)試驗(yàn)

session A 執(zhí)行語句， 獲取 id =1 的 寫鎖權(quán)限；

BEGIN;
update  `order` set `year`= '2022' where id = '1';

session B 執(zhí)行語句， 獲取 id =2 的 寫鎖權(quán)限；

BEGIN;
update `order` set `year`= '2022' where id = '2';

session A 執(zhí)行語句， 嘗試獲取 id =2 的 寫鎖權(quán)限，進(jìn)入鎖等待狀態(tài)

update `order` set `year`= '2022' where id = '2';

session B 執(zhí)行語句， 嘗試獲取 id =1 的 寫鎖權(quán)限，進(jìn)入鎖等待狀態(tài)

update  `order` set `year`= '2022' where id = '1';

當(dāng)  B 進(jìn)入 鎖等待后就直接報(bào)死鎖異常

Deadlock found when trying to get lock; try restarting transaction

25.2 查看死鎖

可以使用 show engine innodb status 查看死鎖

......
*** (1) TRANSACTION: // 事物A
TRANSACTION 253507, ACTIVE 474 sec starting index read
mysql tables in use 1, locked 1 // 已經(jīng)使用一個(gè)鎖
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17001, OS thread handle 139824777217792, query id 2191731 ......
root updating
update `order` set `year`= '2022' where id = '2'//執(zhí)行得語句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: // 等待鎖釋放獲取鎖
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253507 lock_mode X locks rec but not gap waiting
.....

*** (2) TRANSACTION: // 事物 B
TRANSACTION 253508, ACTIVE 425 sec starting index read
mysql tables in use 1, locked 1 // 已經(jīng)使用一個(gè)鎖
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17002, OS thread handle 139824778569472, query id 2191735 ......
root updating
update  `order` set `year`= '2022' where id = '1'//執(zhí)行得語句
*** (2) HOLDS THE LOCK(S): //持有鎖
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap
......

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 等待鎖釋放獲取鎖
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap waiting
......

不得不說下字母代表鎖得類型如下

  • 共享鎖（S）
  • 排他鎖（X）
  • 意向共享（IS）
  • 意向排他（IX）
  • gap lock（GK）， 間隙鎖，鎖定一個(gè)范圍，不包括當(dāng)前記錄本身；
  • RECORD LOCKS  代表記錄鎖；

可以看見上面得語句 （1） 代表 事物A ，MYSQL 線程id 17001 ；(2) 代表事物B, MYSQL 線程id 17002 ； 事物 A 與B 都在等待 對(duì)方釋放鎖 ，產(chǎn)生了死鎖；

Tip；查看表鎖 : show status like 'table%';

如何解決死鎖，知識(shí)追尋者這邊給個(gè)思路：

查找到死鎖線程，殺死MYSQL死鎖的線程(kill命令)；

如果事物未提交，直接回滾事物；

25.3 如何避免死鎖

  • 在死鎖容易產(chǎn)生得表使用表鎖不會(huì)產(chǎn)生死鎖；
  • 避免交叉使用相同的鎖
程序員GitHub
程序員GitHub
專注于分享GitHub上Python，Java，Go，前端開發(fā)等優(yōu)質(zhì)的學(xué)習(xí)資源，并分享程序員圈的新鮮趣事，熱門干貨，職場感悟。
42篇原創(chuàng)內(nèi)容
公眾號(hào)

    本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。
    轉(zhuǎn)藏 分享 獻(xiàn)花（0）

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多