DQL【重點(diǎn)】? ? ??

一、學(xué)習(xí)流程

1. 單表查詢：?jiǎn)伪聿樵兤邆€(gè)查詢命令執(zhí)行特征以及臨時(shí)表

2.多表查詢：連接查詢合并方案? / 聯(lián)合查詢合并方案

3.子查詢：【簡(jiǎn)單】依賴子查詢? ?/? 獨(dú)立子查詢

4.自關(guān)聯(lián)查詢：

5.查詢結(jié)果中解釋字段數(shù)據(jù)來源

6.特殊查詢案例:? ?行轉(zhuǎn)列查詢? ? /? ?列轉(zhuǎn)行查詢

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

MySql服務(wù)器管理規(guī)則

一、存儲(chǔ)引擎:

1.介紹：mysql服務(wù)器提供表文件管理方案

2.查看mysql服務(wù)器存在存儲(chǔ)引擎

show? engines

3.修改mysql服務(wù)器默認(rèn)存儲(chǔ)引擎

my.ini設(shè)置默認(rèn)存儲(chǔ)引擎? default-storage-engine=myisam

4.查看表文件依賴存儲(chǔ)引擎

show? create? table 表文件

5.修改表文件依賴存儲(chǔ)引擎

alter table 表文件? engine=新存儲(chǔ)引擎名

6. INNODB 與 MYISAM 區(qū)別

1）myisam：

? ? ? ? ? ? ? ? ? ? ***采用三個(gè)文件存儲(chǔ)信息? xx.frm----字段信息

? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?xx.myd---數(shù)據(jù)行

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? xx.myi----索引

? ? ? ? ? ? ? ? ? ? ***增加表文件操作速度，不支持事務(wù)管理

? ? ? ? ? ? ? ? ? ? ***如果表文件操作以查詢?yōu)橹鳎ㄗh采用myisam

2) innodb：

? ? ? ? ? ? ? ? ? ? ***采用frm文件存儲(chǔ)信息（字段，數(shù)據(jù)行，索引）

? ? ? ? ? ? ? ? ? ? ***增加表文件數(shù)據(jù)安全性。在九種存儲(chǔ)引擎中唯一支持事務(wù)管理

? ? ? ? ? ? ? ? ? ? ***如果表文件操作以修改為主，建議采用INNODB

二、約束管理方案

1.介紹：確保字段中內(nèi)容符合實(shí)際要求

2.分類：【背】

1）非空約束

2）唯一性約束

3）主鍵約束

4）外鍵約束

5)? 自定義約束

3.? 非空約束

1） 作用：要求管理字段下不允許存儲(chǔ)null值

2） 例子：

? ? ? ? ? ? ? ??create table student(

? ? ? ? ? ? ? ? ? ? ? ? ?sid? int ,

? ? ? ? ? ? ? ? ? ? ? ? ?sname varchar(20)? ?not null? #非空約束

? ? ? ? ? ? ? ? ? ? )

? ? ? ? ? ? ? ? ? ? insert into? student? ?values(1,"mike"); #ok

? ? ? ? ? ? ? ? ? ??insert into? student? ?values(null,'allen');#ok

? ? ? ? ? ? ? ? ? ? insert into? student? ?values(3,null); #error

4.唯一性約束:

1) 作用：要求字段下內(nèi)容不能出現(xiàn)重復(fù)值，但是允許出現(xiàn)多個(gè)null

2) 例子：

? ? ? ? ? ? create table student(

? ? ? ? ? ? ? ? ? ? ? sid? int ,

? ? ? ? ? ? ? ? ? ? ? sname varchar(20)? ?not null? #非空約束，

? ? ? ? ? ? ? ? ? ? ? email? ?varchar(20)? ?unique? ?#唯一性約束

? ? ? ? ? ? ? ? ? ?)

? ? ? ? ? ? ? ? ? ?insert into? student? ?values(1,"mike",'mike@163.com')#ok

? ? ? ? ? ? ? ? ? ? insert into? student? ?values(2,"allen",'mike@163.com')#error

? ? ? ? ? ? ? ? ???insert into? student? ?values(3,"smith",null)#ok

? ? ? ? ? ? ? ? ? ?insert into? student? ?values(4,"tom",null)#ok

? ? ? ? ? ? ? ? ? ? ? ? ? ??

5.主鍵約束:

1）作用：管理主鍵字段。確保主鍵字段下內(nèi)容不能出現(xiàn)重復(fù)值，也不能出現(xiàn)null

2）例子：

? ? ? ? ? ? ? ?create table student(

? ? ? ? ? ? ? ? ? ? ?sid int? ?primary key，? #主鍵約束

? ? ? ? ? ? ? ? ? ? ?sname varchar(20)

? ? ? ? ? ? ? ? )

? ? ? ? ? ? ? ??insert into student values(1,'mike') #ok

? ? ? ? ? ? ? ? insert into student values(1,'allen') #error

? ? ? ? ? ? ? ? insert into student values(null,'smith')#error

6.外鍵約束:

1)? 作用： 管理外鍵字段。要求外鍵字段值必須來自于一方表主鍵字段已經(jīng)存在的值同時(shí)允許外鍵字段存在多個(gè)null

2）外鍵約束綁定：

alter table 多方表? add? ?constraint? 約束對(duì)象名? foreign key(多方表外鍵字段)references? 一方表（一方表主鍵字段名）

三 、視圖(VIEW)-----【背】

1. 什么是視圖:

1)視圖是MySql服務(wù)器提供一個(gè)內(nèi)置管理對(duì)象

2)視圖對(duì)象內(nèi)部采用鍵值對(duì)(key-value)形式存儲(chǔ)

3)視圖中key存放一個(gè)查詢語(yǔ)句。value存放查詢語(yǔ)句對(duì)應(yīng)字段管理權(quán)

2.例子：? ? ? ? ? ? ? ? ? ? ? 視圖1對(duì)象

? ? ? ? ? ? ? ?key -----------------------------------------value

? ? ? ? select deptno? from dept? ? ? ? ? ? ? ? ? ? ? ? ? ? ?deptno? 使用權(quán)

3.視圖創(chuàng)建:

create view 視圖對(duì)象名? as? 查詢語(yǔ)句

4.視圖作用:[重點(diǎn)]

1)提高查詢語(yǔ)句復(fù)用性? ?select? * from view_1

2)分配開發(fā)人員對(duì)表文件字段使用權(quán)利

create view? view_2? as? ? ? ? ? ? ? ? ?select empno, ename,job,sal? ? ?from emp

插入

insert into? view_2(empno,ename,job,sal)

values(3999,'xxxx','xxx',4000); #OK

insert into? view_2(empno,ename,job,sal,comm)

values(4999,'xxxx','xxx',4000,100); #error

刪除

delete? from view_2? where empno>=9999 #ok

delete? from? view_2 where? deptno=10; #error

更新

update? view_2? set? ename='ssss',job='ccccc'? where empno=3999

update? view_2? set? ename='ssss',job='ccccc' ,hireDate='2009-08-13' where empno=3999

四、索引（index）-----SQL優(yōu)化【近兩年面試必考科目】

1.什么是索引：

1.索引存在硬盤中一個(gè)數(shù)組

2.數(shù)組有序存放表文件中某個(gè)字段下所有數(shù)據(jù)以及數(shù)據(jù)所在行數(shù)

3.如果表文件采用INNODB引擎管理，索引存在frm文件。

如果表文件采用myisam引擎管理，索引存在myi文件

2.索引作用：

減少遍歷臨時(shí)表中數(shù)據(jù)行次數(shù)，增加查詢速度

SELECT? *? ? ? ? ? ? ?FROM STUDENT WHERE AGE =20? #遍歷了5次

如果where使用字段上設(shè)置了索引。此時(shí)mysql服務(wù)器使用BTREE算法

對(duì)應(yīng)索引中定位滿足條件的數(shù)據(jù)行行數(shù)并返回給where

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??[21]

? ? ? ? ? ?[20---3,5]? ? ? ? ? ? 【22---4】

? ? ? ? ? ?[19---1行]? ? ? ? ? ? ? ? ? ? ? ? 【25---2】

3.? 索引操作:

1)查看表文件已近存在索引

show index from 表文件

2) 創(chuàng)建一個(gè)索引

create index? 索引名? on 表文件(字段)

3) 刪除索引

drop? index? 索引名? ?on? 表文件

4. 執(zhí)行計(jì)劃

1) 命令格式:? explain? 查詢語(yǔ)句

2) 執(zhí)行計(jì)劃type(查詢速度級(jí)別)：all < index? <range <ref <const

all: 最慢查詢速度。表示沒有通過索引幫助定位數(shù)據(jù)行。

? ? ? 采用全表掃描方式定位數(shù)據(jù)行，進(jìn)行SQL優(yōu)化時(shí)避免

? ? ? all級(jí)別

? ? ? explain select *? from emp where ename='smith'

index: 進(jìn)行SQL優(yōu)化時(shí)避免index級(jí)別。采用全表掃描方式

? ? ? ? ? 定位數(shù)據(jù)行，但是在SELECT抓取數(shù)據(jù)時(shí)從索引中得到結(jié)果，對(duì)于查詢速度有所幫助。但是這個(gè)幫助忽略不計(jì)

explain? select * from emp? ? #all? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

create index ename_index on emp(ename)

explain? select ename from emp

range:? ? 進(jìn)行SQL優(yōu)化時(shí)達(dá)到最低標(biāo)準(zhǔn).采用索引提供數(shù)據(jù)行位置同時(shí)一次返回多個(gè)數(shù)據(jù)行

explain select * from emp where sal<=1000? #all

create index sal_index? on emp(sal)

explain select * from emp where sal<=1000 #range

這個(gè)級(jí)別并不穩(wěn)定，如果通過索引返回行數(shù)達(dá)到總行數(shù)1/3

mysql服務(wù)器考慮性能與消耗關(guān)系，放棄使用索引

ref:?進(jìn)行SQL優(yōu)化時(shí)達(dá)到最高標(biāo)準(zhǔn)，采用索引提供數(shù)據(jù)行位置并且每次只能返回一行

explain? select * from emp where ename='smith' # all

create index ename_index on emp(ename)

explain? select * from emp where ename='smith' # ref

const:? ? 采用聚簇索引進(jìn)行定位，是最快查詢級(jí)別

5.索引失效:

1) 如果通過索引定位行數(shù)達(dá)到了總行數(shù)1/3時(shí)，必然導(dǎo)致索引失效

2) 如果在索引字段進(jìn)行數(shù)學(xué)運(yùn)算，必然導(dǎo)致索引失效

explain? select * from emp where sal =800? ? ? ? ?#ref

explain? select * from emp where sal+100=900? ?#all??

3)如果在索引字段進(jìn)行函數(shù)處理，必然導(dǎo)致索引失效? ? ? ? ? ? ? ? ??

explain select * from emp where ename='smith'? #ref

explain select * from emp where upper(ename)='SMITH'? #ALL

4) 前置模糊查詢可以達(dá)到range

explain? select * from emp where ename like 's%'? #range

后置模糊查詢和包含模糊查詢一定會(huì)導(dǎo)致索引失效? ? ? ? ? ? ? ? ? ? ?

explain? select * from emp where ename like '%s'? #all? ?abc? abd

explain? select * from emp where ename like '%s%' #all

5) 如果采用類型自動(dòng)轉(zhuǎn)換，必然導(dǎo)致索引失效

explain select * from emp where ename='100' #ref

explain select * from emp where ename=100? ?#all

五、事務(wù)（transaction）

1.什么是事務(wù)：

對(duì)于采用innodb管理的表文件，在進(jìn)行數(shù)據(jù)修改時(shí)（insert,update,delete）mysql服務(wù)器自動(dòng)對(duì)當(dāng)前表文件中數(shù)據(jù)進(jìn)行一次備份然后再進(jìn)行操作。這個(gè)備份就是事務(wù)

2.事務(wù)作用：

在操作之后，如果需要反悔?？梢酝ㄟ^事務(wù)覆蓋表文件實(shí)現(xiàn)操作撤銷

3.事務(wù)管理方式

1）mysql服務(wù)器自動(dòng)管理事務(wù)，如果當(dāng)前DML命令無法正常執(zhí)行。

? ? ?此時(shí)mysql服務(wù)器使用事務(wù)撤銷本次操作。如果當(dāng)前DML命令正常執(zhí)行，mysql服務(wù)器銷毀掉事務(wù)

2）手動(dòng)事務(wù)管理方式，要求mysql服務(wù)器將多個(gè)SQL命令產(chǎn)生備份交給同一個(gè)事務(wù)對(duì)象管理，操作完畢后由開發(fā)人員根據(jù)實(shí)際情況決定是否測(cè)校

start transaction;

delete from emp;? #emp_copy

delete from dept;? #dept_copy

rollback; #回滾

commit; #提交--事務(wù)管理對(duì)象將本次所有備份銷毀

六、事務(wù)使用原則(ACID)

1. A（原子性）：只有來自于同一個(gè)業(yè)務(wù)中SQL命令才應(yīng)該交給同一個(gè)事務(wù)管理對(duì)象管理

例子：撤銷部門20同時(shí)將部門20下職員開除

? ? ? ? ? ?start transaction；

? ? ? ? ? ?delete from emp where deptno=20;

? ? ? ? ? delete from dept where deptno=20;

? ? ? ? ? ?commit/rollback

? ? ? ? ? ?volaite

錯(cuò)誤使用?

start transaction；

1? delete from emp where deptno=20;

2 delete from dept where deptno=20;

3 insert? into book values(1,"java編程思想")；

commit/rollback

2.C.(一致性)? ? 一個(gè)業(yè)務(wù)中只要有一個(gè)分支任務(wù)執(zhí)行失敗或則返回。

此時(shí)就應(yīng)該將事務(wù)中所有sql操作都視為無效

3.D(持久性)? ? 在commit執(zhí)行之后，無法通過rollback撤銷操作

4.I(隔離性):? ? ?兩個(gè)事務(wù)對(duì)于同一個(gè)表操作應(yīng)該彼此相互影響

七、char 與? varchar區(qū)別? (面試?？碱}型--（HR邀約面試）)

1.char（m），固定不可變字符串

1） 固定，char類型可以接收字符個(gè)數(shù)是固定，賦值時(shí)不能超過這個(gè)個(gè)數(shù)

? ? ? ? ? ? ? ? ? ?create table test1(

? ? ? ? ? ? ? ? ? ? ? ? ? ? tname char(3)? ? ? ?# 可以存放3個(gè)英文字母，或則3個(gè)中文漢字

? ? ? ? ? ? ? ? ? ?)

? ? ? ? ? ? ? ?? ? insert into test1? values('abc');

? ? ? ? ? ? ? ? ? ?insert into test1? values('吃了嗎')

? ? ? ? ? ? ? ? ? ?insert into test1? values('abcd'); #error

2)不可變，在磁盤用于存儲(chǔ)字符空間是固定。

? ? ? ? ? ? ? ? ? tname? char(3)? ? ? ?'abc'? ?[a]? ?[b]? [c]

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?'ab'? ? ?[a]? ?[b]? [空格]

2. varchar(m): 固定可變字符串

1）固定，varchar類型可以接收字符個(gè)數(shù)是固定，賦值時(shí)不能超過

? ? ? ? ? ? ? ? ? ?create table test1(

? ? ? ? ? ? ? ?? ? ? ? ? ? ?tname varchar(3)? ? ? ?# 可以存放3個(gè)英文字母，或則3個(gè)中文漢字

? ? ? ? ? ? ? ?? ? ?)

? ? ? ? ? ?? ? ? insert into test1? values('abc');

? ? ? ? ? ? ? ? ?insert into test1? values('吃了嗎')

? ? ? ? ? ? ? ? ?insert into test1? values('abcd'); #error

2)? 可變： 根據(jù)實(shí)際接收字符個(gè)數(shù)減少磁盤空間

? ? ? ? ? ? ? ? ? ? ? ? tname? varchar(3)? ?'abc'? [a]? ?[b]? [c]

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 'ab'? ? [a]? ?[b]

最后奉上視頻教程??，視頻觀看效果更佳??！走過路過別忘素質(zhì)三連哦~~