當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

mysql between and的用法的意思_从入门到入土：MySQL完整学习指南，包教包会！

發(fā)布時(shí)間：2025/3/20 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql between and的用法的意思_从入门到入土：MySQL完整学习指南，包教包会！小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

精彩推薦

一百期Java面試題匯總SpringBoot內(nèi)容聚合IntelliJ IDEA內(nèi)容聚合Mybatis內(nèi)容聚合

一SQL 介紹

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

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

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

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

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

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

2.3 表

表是一種結(jié)構(gòu)化的文件可以用來存儲(chǔ)特定類型的數(shù)據(jù)；這么說你可能沒聽懂，我們?cè)倥e個(gè)例子，表就像圖書館里面的圖書架子，每個(gè)書架都放了好多書；再不懂也沒關(guān)系，你總用過excel吧，excel表格里面的標(biāo)簽頁(yè) (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ù)庫(kù)的表跟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ù)庫(kù)，以后的示例基本是使用mysql數(shù)據(jù)庫(kù)管理系統(tǒng)；

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

建表語(yǔ)句：

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)組成的語(yǔ)句，關(guān)鍵字是數(shù)據(jù)庫(kù)的保留字，用戶不能將其當(dāng)作建表的表名，字段等；表中的數(shù)據(jù)檢索使用select關(guān)鍵字作為開頭進(jìn)行查詢數(shù)據(jù)庫(kù)表的信息；

3.2 檢索單個(gè)字段

語(yǔ)句示例：

SELECT?name?FROM?student?

查詢結(jié)果：

youku1
youku2
jeff
smile

語(yǔ)句分析：

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

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

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

3.4 檢索多個(gè)字段

語(yǔ)句示例：

SELECT?name,age?FROM?student;

語(yǔ)句結(jié)果：

youku1?18
youku2?23
jeff?25
smile?17

語(yǔ)句分析：

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

3.5 檢索所有字段

語(yǔ)句示例：

SELECT?*?FROM?student;

語(yǔ)句結(jié)果：

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

語(yǔ)句分析：

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

3.6 distinct

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

SELECT?DISTINCT?name,?age?FROM?student;

語(yǔ)句結(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ù)

語(yǔ)句結(jié)果：

youku1
youku2

3.8偏移

示例：

SELECT?name?FROM?student?LIMIT?1?OFFSET?1;

語(yǔ)句分析

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

mysql和MariaDB簡(jiǎn)化形式:

SELECT?name?FROM?student?LIMIT?1,2;

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

語(yǔ)句結(jié)果：

youku2
jeff

四排序檢索

4.1 ORDER BY 子句

示例：

SELECT?name,age?FROM?student?ORDER?BY?age

語(yǔ)句分析；

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

語(yǔ)句結(jié)果：

smile?17
youku1?18
youku2?23
jeff?25

4.2 多列排序

示例：

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

語(yǔ)句分析：

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

語(yǔ)句結(jié)果：

jeff?25
youku2?23
youku1?18
smile?17

4.3 按位排序

語(yǔ)句示例：

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 語(yǔ)句中過濾條件(filter condition)的關(guān)鍵字是 WHERE，其使用方式是跟在表名之后；

5.1 WHERE語(yǔ)句操作符

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

操作符說明

=	等于
>	大于
<	小于
!=	不等于
<>	不等于
>=	大于等于
<=	小于等于
!<	不小于
!>	不大于
BETWEEN	在中間
IS NULL	為空

5.2 單條件過濾

示例：

SELECT?*?FROM?student?WHERE?name?=?'jeff';

語(yǔ)句分析;

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

語(yǔ)句結(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';

語(yǔ)句分析：

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

語(yǔ)句結(jié)果：

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

OR示例：

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

語(yǔ)句分析：

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

OR 和 AND 示例：

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

語(yǔ)句分析：

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

5.4 范圍查詢

示例：

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

語(yǔ)句分析

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

語(yǔ)句結(jié)果：

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

5.5 空值查詢

示例：

SELECT?*?FROM?student?WHERE??age?IS?NULL;

語(yǔ)句分析：

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

5.6 IN 操作

示例：

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

語(yǔ)句分析：

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

語(yǔ)句結(jié)果：

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

5.7 NOT 操作符

示例：

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

語(yǔ)句分析：

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

六通配符檢索

6.1 通配符的介紹

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

6.2 通配符 %

示例：

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

語(yǔ)句分析：

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

語(yǔ)句結(jié)果：

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

示例：

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

語(yǔ)句分析：

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

語(yǔ)句結(jié)果;

4?smile?17?高三學(xué)子

6.3 通配符 _

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

示例：

SELECT?*?FROM?student?WHERE?name?LIKE?'youku_'?;

語(yǔ)句分析：

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

語(yǔ)句結(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'?;

語(yǔ)句分析：

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

語(yǔ)句結(jié)果：

你好啊youku1327今天心情怎么樣

7.2 去除空白字符串

語(yǔ)句示例：

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

語(yǔ)句分析：

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

語(yǔ)句結(jié)果;

??????哥，今天管飽

7.3 別名

語(yǔ)句示例：

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

語(yǔ)句分析：

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

7.4 計(jì)算

操作符說明

*	乘
+	加
-	減
/	除

語(yǔ)句示例：

SELECT?2?*?8;?

語(yǔ)句結(jié)果：

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

先聲明一下，下面的庫(kù)表只是簡(jiǎn)易的學(xué)習(xí)示例，不是生產(chǎn)的設(shè)計(jì)，不要深究，此文我們的目的是學(xué)習(xí)sql的檢索不是庫(kù)表設(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的行；

語(yǔ)句示例：

SELECT?AVG(price)?FROM?product;

語(yǔ)句結(jié)果：

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

8.2 count()

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

語(yǔ)句示例：

SELECT?count(*)?FROM?product;

語(yǔ)句結(jié)果：

語(yǔ)句分析：

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

8.3 max()

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

語(yǔ)句示例：

SELECT?max(price)?FROM?product;

語(yǔ)句結(jié)果：

語(yǔ)句分析：

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

8.4 min()

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

語(yǔ)句示例：

SELECT?min(price)?FROM?product;

語(yǔ)句結(jié)果：

語(yǔ)句分析：

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

8.5 sum()

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

語(yǔ)句示例：

SELECT?sum(price)?FROM?product;

語(yǔ)句結(jié)果：

語(yǔ)句分析：

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

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

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

9.1 分組求和

語(yǔ)句示例：

SELECT?sum(price)?FROM?product?GROUP?BY?productName;

語(yǔ)句結(jié)果：

4
5
3

語(yǔ)句分析：

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

9.2 分組過濾

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

語(yǔ)句分析

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

9.3 分組排序

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

1
2

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

十子查詢

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

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

zxzxz

語(yǔ)句分析：

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

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

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

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

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

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

語(yǔ)句分析：

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

注意點(diǎn) ：簡(jiǎ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é)果跟之前的簡(jiǎn)單聯(lián)結(jié)一致；

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

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

語(yǔ)句分析：

跟之前的簡(jiǎn)單聯(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é)

語(yǔ)句示例：

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é)

語(yǔ)句示例：

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 語(yǔ)句，其查詢的結(jié)構(gòu)是一致的，返回查詢結(jié)果，通常我們稱為復(fù)合操作或者并(union)

語(yǔ)句示例：

SELECT
?userId?
FROM
?customer?UNION
SELECT
?userId?
FROM
?`order`

返回結(jié)果：

1
2

語(yǔ)句分析：

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

語(yǔ)句示例：

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

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

1
2
1
2
1

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

十三插入

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

建表語(yǔ)句：

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ù)

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

2?zszxz?1327

語(yǔ)句分析：

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

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

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

3?zszxz?

語(yǔ)句分析：

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

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

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

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

4?smile?

語(yǔ)句分析：

插入數(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ù)庫(kù)管理系統(tǒng)支持不同，具體的看下文；

語(yǔ)句示例：

SELECT?id?,?`name`??INTO?student_copy?FROM?student;

語(yǔ)句分析

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

語(yǔ)句示例：

CREATE?TABLE?student_copy?AS?
SELECT?*?FROM?student;

語(yǔ)句結(jié)果：

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

語(yǔ)句分析：

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

十四更新

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

14.1 更新所有行

語(yǔ)句示例：

UPDATE?student_copy?set?age?=?Null;

語(yǔ)句結(jié)果：

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

語(yǔ)句分析：

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

14.2 更新特定的行

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

4?smile?18?高三學(xué)子

語(yǔ)句分析：

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

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

語(yǔ)句示例：

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

語(yǔ)句分析：

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

語(yǔ)句示例：

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

語(yǔ)句結(jié)果：

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

語(yǔ)句分析更新 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 的更新查詢姿勢(shì)；

十五刪除表

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

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

DELETE?from?student_copy;

語(yǔ)句分析

刪除全部行來自 student_copy 表；

15.2 刪除特定的行

語(yǔ)句示例：

DELETE?from?student?WHERE?id?=?'4';

語(yǔ)句分析：

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

15.3 更新和刪除的建議

每次進(jìn)行操作前檢查是否丟失 where 子句；

每次操作前最好先使用 select 語(yǔ)句驗(yàn)證；

十六 SQL 分類操作

16.1 SQL 分類

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

DDL：數(shù)據(jù)定義語(yǔ)言(Data Define Language)，即定義數(shù)據(jù)的結(jié)構(gòu)。比如：create，drop，alter操作

DML：數(shù)據(jù)管理語(yǔ)言(Data Manage Language)，即增刪改查。比如：insert，delete，update，select。這個(gè)也在之前的文章詳細(xì)寫過，不再提及；

DCL：數(shù)據(jù)控制語(yǔ)言(Data Control Language)，對(duì)權(quán)限、事務(wù)等的控制。比如：grant(授權(quán))revoke(取回權(quán)限)，commit，roolback等; 事物在上篇已經(jīng)說明，不會(huì)在本篇提及；

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

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

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

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

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

SELECT DATABASE();

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

SHOW PROCESSLIST;

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

SHOW VARIABLES;

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

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

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

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

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

drop database zszxz;

16.3 建表語(yǔ)句

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

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

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

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

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

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

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ù)庫(kù)的表

SHOW TABLES FROM 數(shù)據(jù)庫(kù)名稱;
示例：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ù)庫(kù)名.]表名 add [column] 字段數(shù)據(jù)類型;
示例：alter table `order` add column `year` year ;

增加到第一列

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

增加一列到指定字段名后

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

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

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

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

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

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

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

修改表字段的名稱

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

添加主鍵

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

添加唯一鍵

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

添加索引

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

刪除一列

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

刪除索引

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

刪除主鍵

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

刪除外鍵

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

十七視圖

17.1 視圖的概念

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

簡(jiǎn)化查詢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ì)是查詢語(yǔ)句，故視圖可以嵌套，可以與其它表進(jìn)行聯(lián)結(jié)；
視圖不能有索引和觸發(fā)器

17.3 視圖語(yǔ)句

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

創(chuàng)建視圖與創(chuàng)建表類似， create view 語(yǔ)句用于創(chuàng)建視圖
顯示視圖創(chuàng)建語(yǔ)句使用 show create view viewName ；
刪除視圖使用語(yǔ)句 drop view viewName
更新視圖使用語(yǔ)句 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);

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

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

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

十八儲(chǔ)存過程

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

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

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

18.2 存儲(chǔ)過程語(yǔ)法

創(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?語(yǔ)句為變量賦值。
set?@var?:=?20
select?sum(price)?into?total?from?table_name

if 語(yǔ)句

f?條件?then
????表達(dá)式???
[elseif?條件?then
????表達(dá)式]
...
[else
????表達(dá)式]
end?if;

case語(yǔ)句

CASE 值 WHEN 匹配值 THEN 結(jié)果
[WHEN 匹配值 THEN 結(jié)果]
......
[ELSE 結(jié)果]
END

while語(yǔ)句

[開始標(biāo)簽:]while?條件?do
????循環(huán)體
[結(jié)尾標(biāo)簽]????
end?while?;

loop 語(yǔ)句

[開始標(biāo)簽:] loop
語(yǔ)句體
[結(jié)尾標(biāo)簽]
end loop;

iterate/ leave語(yǔ)句

其是通過標(biāo)簽可以實(shí)現(xiàn)；iterate 表示迭代， leave表示離開

repeat語(yǔ)句

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ì)的所有訂單名稱,示例如下，跟普通的查詢語(yǔ)句沒區(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 語(yǔ)句示例

上節(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 語(yǔ)句示例

對(duì) 變量 var 進(jìn)行判斷，如果 var <7 就執(zhí)行查詢價(jià)格語(yǔ)句，并且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語(yǔ)句示例

如下語(yǔ)句實(shí)現(xiàn)的效果與上面if語(yǔ)句實(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語(yǔ)句

如果 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 語(yǔ)句使用方式一樣；

十九游標(biāo)

19.1 游標(biāo)的概念

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

19.2 游標(biāo)的語(yǔ)法

首先需要定義游標(biāo)；declare 游標(biāo)名稱 cursor for ?查詢語(yǔ)句；
其次，打開游標(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ù)

簡(jiǎn)單的使用游標(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ù)之前檢測(cè)所有的入?yún)⑹欠穸际切?#xff0c;此時(shí)就可以用觸發(fā)器來檢測(cè)；經(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ā)器的基本語(yǔ)法

創(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í)摫?#xff0c;記錄者被插入數(shù)據(jù)的行；故我們能在NEW表中獲取每次插入的數(shù)據(jù)；

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

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

--?檢測(cè)插入觸發(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語(yǔ)句進(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í)行語(yǔ)句則需要加上；

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ù)庫(kù)，故如果需要查看用戶信息，則需要進(jìn)入MYSQL庫(kù)；

21.1查看用戶信息

user表儲(chǔ)存了所有的登陸賬號(hào)；使用mysql庫(kù)查詢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 庫(kù)中的所有表的查詢權(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庫(kù)里面所有表的查詢操作；

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ù)庫(kù)連接，安全認(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)；

可以使用語(yǔ)句 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ù)加上讀鎖；

使用語(yǔ)法示例如下

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

23.4 寫鎖

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

格式示例

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

23.5 鎖粒度

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

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

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

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

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

還有鎖的其它分類也會(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://juejin.im/post/5b82e0196fb9a019f47d1823

23.6 引擎簡(jiǎn)介

在上面的圖例中可以看見MYSQL支持多種引擎，當(dāng)然遠(yuǎn)不止圖中顯示的引擎數(shù)量；我們主流使用的引擎就是 InnoDB,其次是 MyISAM，特殊情況下會(huì)使用到Memory；引擎的知識(shí)是一本書都無法概括的內(nèi)容，知識(shí)追尋者在這邊給小伙伴們做個(gè)簡(jiǎn)介，有個(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ā)生了鎖等待；簡(jiǎn)單理解就是小孩子搶玩具，誰(shuí)先搶到誰(shuí) 先玩，沒搶到的玩具的孩子只能等待搶到玩具孩子玩膩了再給你，瞬間淚奔有木有，就是這么殘酷，當(dāng)然MYSQL 沒這么殘忍其還是有一個(gè)保留參數(shù) innodb_lock_wait_timeout 指定死鎖的時(shí)間，如果超過死鎖等待時(shí)間就是報(bào)異常；

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

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

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

session B 執(zhí)行如下語(yǔ)句 , 跟上面的語(yǔ)句一樣，由于 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)鎖等待語(yǔ)句

show??VARIABLES?like??'innodb_lock_wait_timeout'

二十五死鎖

25.1 死鎖的產(chǎn)生

死鎖就是兩個(gè)以上的會(huì)話在搶占資源過程中，產(chǎn)生相互等待的情況；有點(diǎn)繞是不是，其實(shí)很簡(jiǎn)單死鎖是建立在鎖等待的基礎(chǔ)上，session A 獲取 id = 1 的寫鎖， session B 獲取 id =2 的寫鎖，此時(shí)由于索引不同，故不會(huì)長(zhǎng)生鎖等待現(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í)行語(yǔ)句，獲取 id =1 的寫鎖權(quán)限；

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

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

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

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

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

session B 執(zhí)行語(yǔ)句，嘗試獲取 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í)行得語(yǔ)句
***?(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í)行得語(yǔ)句
***?(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 ?代表記錄鎖；

可以看見上面得語(yǔ)句 (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)生死鎖；
避免交叉使用相同的鎖

END

我知道你 “在看”

總結(jié)

以上是生活随笔為你收集整理的mysql between and的用法的意思_从入门到入土：MySQL完整学习指南，包教包会！的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： linux rar解压_linux命令
下一篇： java set第n位_数据结构与算法—

3atv精品不卡视频,97人人超碰国产精品最新,中文字幕av一区二区三区人妻少妇,久久久精品波多野结衣,日韩一区二区三区精品

数据库

mysql between and的用法的意思_从入门到入土：MySQL完整学习指南，包教包会！

一SQL 介紹

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

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

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

2.3 表

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

2.5 行

2.6 主鍵

2.7 行號(hào)

三 基本檢索

3.1 select 關(guān)鍵字

3.2 檢索單個(gè)字段

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

3.4 檢索多個(gè)字段

3.5 檢索所有字段

3.6 distinct

3.7 限制條數(shù)

3.8偏移

四 排序檢索

4.1 ORDER BY 子句

4.2 多列排序

4.3 按位排序

五 過濾檢索

5.1 WHERE語(yǔ)句操作符

5.2 單條件過濾

5.3 多條件過濾

5.4 范圍查詢

5.5 空值查詢

5.6 IN 操作

5.7 NOT 操作符

六 通配符檢索

6.1 通配符的介紹

6.2 通配符 %

6.3 通配符 _

6.4 通配符 []

七 字段基本操作

7.1 字段拼接

7.2 去除空白字符串

7.3 別名

7.4 計(jì)算

8.1 avg()

8.2 count()

8.3 max()

8.4 min()

8.5 sum()

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

9.1 分組求和

9.2 分組過濾

9.3 分組排序

十 子查詢

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

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

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

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

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

十二 組合查詢

十三 插入

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

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

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

13.4 復(fù)制表

十四 更新

14.1 更新所有行

14.2 更新特定的行

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

十五 刪除表

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

15.2 刪除特定的行

15.3 更新和刪除的建議

十六 SQL 分類操作

16.1 SQL 分類

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

16.3 建表語(yǔ)句

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

十七 視圖

17.1 視圖的概念

17.2 視圖的規(guī)范

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

三基本檢索

四排序檢索

五過濾檢索

六通配符檢索

七字段基本操作

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

十子查詢

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

十二組合查詢

十三插入

十四更新

十五刪除表

十七視圖

十八儲(chǔ)存過程

十九游標(biāo)

二十觸發(fā)器

二十一用戶操作

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

二十四鎖等待

二十五死鎖