回顧

連接查詢：多張表連接到一起，不管記錄數(shù)如何，字段數(shù)一定會(huì)增加

分類(lèi)：內(nèi)連接，外連接，自然連接和交叉連接

交叉連接：cross join(笛卡爾積)

內(nèi)連接：inner join，左右兩張表中有連接條件匹配(不匹配的忽略)

外連接：outer(left/right) join，主表的記錄一定會(huì)存在，匹配了就保留副表字段數(shù)據(jù)，沒(méi)匹配到副表字段置空

自然連接：natural join，自動(dòng)匹配(相同的字段名)，using關(guān)鍵字

PHP操作mysql：

PHP充當(dāng)客戶端：開(kāi)啟mysql擴(kuò)展

連接認(rèn)證：mysql_connect

發(fā)送SQL獲取結(jié)果：mysql_query

解析結(jié)果集：mysql_fetch系統(tǒng)

釋放資源：mysql_free_result和mysql_close

錯(cuò)誤處理：mysql_errno和mysql_error

外鍵

外鍵：foreign key，外面的鍵(鍵不在自己表中)：如果一張表中有一個(gè)字段(非主鍵)指向另外一個(gè)表的主鍵那么該字段稱(chēng)之為外鍵

增加外鍵

外鍵可以在創(chuàng)建表的時(shí)候或者創(chuàng)建表之后增加(但是要考慮數(shù)據(jù)的問(wèn)題)

一張表可以有多個(gè)外鍵

創(chuàng)建表的時(shí)候增加外鍵：在所有的表字段之后，使用foreign key(外鍵字段) references 外部表(主鍵字段)

在新增表之后增加外鍵：修改表結(jié)構(gòu)

alter table 表名 add [constraint 外鍵名字] foreign key(外鍵字段) references 父表(主鍵字段);

修改外鍵&&刪除外鍵

外鍵不可修改，只能先刪除后新增

刪除外鍵語(yǔ)法

alter table 表名 drop foreign key 外鍵名; -- 一張表中可以有多個(gè)外鍵，但是名字不能相同

外鍵作用

外鍵默認(rèn)的作用有兩點(diǎn)：一個(gè)對(duì)父表，一個(gè)對(duì)子表(外鍵字段所在的表)

對(duì)子表約束：子表數(shù)據(jù)進(jìn)行寫(xiě)操作(新增和更新)的時(shí)候，如果對(duì)應(yīng)的外鍵字段在父表中找不到對(duì)應(yīng)的匹配，那么會(huì)操作失敗(約束子表數(shù)據(jù)操作)

對(duì)父表約束：父表數(shù)據(jù)進(jìn)行寫(xiě)操作(刪除和修改：都必須涉及到主鍵本身)，如果對(duì)應(yīng)的主鍵在子表中已經(jīng)被數(shù)據(jù)引用，那么就不允許操作

外鍵條件

1. 外鍵要存在：首先必須保證表的存儲(chǔ)引擎是innodb(默認(rèn)的存儲(chǔ)引擎)：如果不是innodb存儲(chǔ)引擎，那么外鍵可以創(chuàng)建成功，但是沒(méi)有約束效果

2. 外鍵字段的字段類(lèi)型(列類(lèi)型)必須與父表的主鍵類(lèi)型完全一致

3. 一張表中的外鍵名字不能重復(fù)

4. 增加外鍵的字段(數(shù)據(jù)已存在)，必須保證數(shù)據(jù)與父表主鍵要求對(duì)應(yīng)

外鍵約束

所謂外鍵約束：就是外鍵的作用

之前所講的外鍵作用：是默認(rèn)的作用；其實(shí)可以通過(guò)對(duì)外鍵的需求，進(jìn)行定制操作

外鍵約束有三種約束模式：都是針對(duì)父表的約束

district：嚴(yán)格模式(默認(rèn)的)，父表不能刪除或者更新一個(gè)已經(jīng)被子表數(shù)據(jù)引用的記錄

cascade：級(jí)聯(lián)模式：父表的操作，對(duì)應(yīng)子表關(guān)聯(lián)的數(shù)據(jù)也跟著被更新

set null：置空模式：父表的操作之后，子表對(duì)應(yīng)的數(shù)據(jù)(外鍵字段)被置空

通常的一個(gè)合理做法(約束模式)：刪除的時(shí)候子表置空，更新的時(shí)候子表級(jí)聯(lián)操作

指定模式的語(yǔ)法

foreign key(外鍵字段) references 父表(主鍵字段) on delete set null on update cascade

更新操作：級(jí)聯(lián)更新

刪除操作：置空

刪除置空的前提條件：外鍵字段允許為空(如果不滿足條件，外鍵無(wú)法創(chuàng)建)

外鍵雖然很強(qiáng)大，能夠進(jìn)行各種約束：但是對(duì)于PHP來(lái)講，外鍵的約束降低了PHP對(duì)數(shù)據(jù)的可控性：通常在實(shí)際開(kāi)發(fā)中，很少使用外鍵來(lái)處理

聯(lián)合查詢

聯(lián)合查詢：將多次查詢(select語(yǔ)句)，在記錄上進(jìn)行拼接(字段不會(huì)增加)

基本語(yǔ)法

多條select語(yǔ)句構(gòu)成：每一條select語(yǔ)句獲取的字段必須嚴(yán)格一致(但是字段類(lèi)型無(wú)關(guān))

select 語(yǔ)句1

union

select 語(yǔ)句2

union選項(xiàng)：與select選項(xiàng)一樣有兩個(gè)：

all：保留所有(不管重復(fù))

district：去重(整個(gè)重復(fù)) 默認(rèn)的

聯(lián)合查詢只要求字段一樣，跟數(shù)據(jù)類(lèi)型無(wú)關(guān)

意義

聯(lián)合查詢的意義分為兩種：

1. 查詢同一張表，但是需求不同：如查詢學(xué)生信息，男生身高升序，女生身高降序

2. 多表查詢：多張表的結(jié)構(gòu)是完全一樣的，保存的數(shù)據(jù)(結(jié)構(gòu))也是一樣的

order by使用

在聯(lián)合查詢中：order by不能直接使用，需要對(duì)查詢語(yǔ)句使用括號(hào)才行

若要order by生效，必須搭配limit：limit使用限定的最大數(shù)即可

子查詢

子查詢：sub query，查詢是在某個(gè)查詢結(jié)果之上進(jìn)行的(一條select語(yǔ)句內(nèi)部包含了另外一條select語(yǔ)句)

子查詢分類(lèi)

子查詢有兩種分類(lèi)方式：按位置分類(lèi)，按結(jié)果分類(lèi)

按位置分類(lèi)：子查詢(select語(yǔ)句)在外部查詢(select語(yǔ)句)中出現(xiàn)的位置

from子查詢：子查詢跟在from之后

where子查詢：子查詢出現(xiàn)在where條件中

exists子查詢：子查詢出現(xiàn)在exists里面

按結(jié)果分類(lèi)：根據(jù)子查詢得到的數(shù)據(jù)進(jìn)行分類(lèi)(理論上講任何一個(gè)查詢得到的結(jié)果都可以理解為二維表)

標(biāo)量子查詢：子查詢的結(jié)果是一行一列

列子查詢：子查詢的結(jié)果是一列多行

行子查詢：子查詢的結(jié)果是多行一列(多行多列)

    上面幾個(gè)出現(xiàn)的位置都是在where之后

表子查詢：子查詢得到的結(jié)果是多行多列(出現(xiàn)的位置是在from之后)

標(biāo)量子查詢

需求：知道班級(jí)名字為PHP0710，想獲取該班的所有學(xué)生

1. 確定數(shù)據(jù)源：獲取所有學(xué)生

select * from my_student where c_id = ?;

2. 獲取班級(jí)id：可以通過(guò)班級(jí)名字確定

select id from my_class where c_name = 'PHP0710'; -- id一定只有一個(gè)值(一行一列)

標(biāo)量子查詢實(shí)現(xiàn)

列子查詢

需求：查詢所有在讀班級(jí)的學(xué)生(班級(jí)表中存在的班級(jí))

1. 確定數(shù)據(jù)源：學(xué)生

select * from my_student where c_id in (?);

2. 確定有效的班級(jí)的id：所有班級(jí)id

select id from my_class;

列子查詢

列子查詢返回的結(jié)果會(huì)比較多：一列多行，需要使用in作為條件匹配：其實(shí)在mysql中還有幾個(gè)類(lèi)似的條件：any，some，all

=any <===> in; -- 其中一個(gè)即可

any <===> some; -- any跟some是一樣

all <===> 全部

肯定結(jié)果

否定結(jié)果

行子查詢

行子查詢：返回的結(jié)果可以是多行多列(一行多列)

需求：要求查詢整個(gè)學(xué)生中，年齡最大且身高是最高的學(xué)生

1. 確定數(shù)據(jù)源

select * from my_student where age = ? and height = ?;

2. 確定最大的年齡和最高的身高

select max(age), max(height) from my_student;

行子查詢：需要構(gòu)造行元素：行元素由多個(gè)字段構(gòu)成

表子查詢

表子查詢：子查詢返回的結(jié)果是多行多列的二維表，子查詢返回的結(jié)果是當(dāng)做二維表來(lái)使用

需求：找出每個(gè)班中最高的一個(gè)學(xué)生

1. 確定數(shù)據(jù)源：先將學(xué)生按照身高進(jìn)行降序排序

select * from my_student order by height desc;

2. 從每個(gè)班選出第一個(gè)學(xué)生
   select * from (數(shù)據(jù)源) group by c_id; -- 每班選出第一個(gè)學(xué)生

表子查詢：from子查詢：得到的結(jié)果作為from的數(shù)據(jù)源

exists子查詢

exists：是否存在的意思，exists子查詢就是用來(lái)判斷某些條件是否滿足(跨表)，exists是接在where之后：exists返回的結(jié)果只有0和1

需求：查詢所有學(xué)生：前提條件是班級(jí)存在

1. 確定數(shù)據(jù)源

select * from my_student where ?;

2. 確定條件是否滿足

exists (select * from my_class); -- 是否成立

exists子查詢

視圖

視圖：view，是一種結(jié)構(gòu)(有行有列)但是沒(méi)結(jié)果(結(jié)構(gòu)中不存放真實(shí)數(shù)據(jù))的虛擬表，虛擬表的結(jié)構(gòu)來(lái)源不是自己定義，而是從對(duì)應(yīng)的基表中產(chǎn)生(視圖的數(shù)據(jù)來(lái)源)

創(chuàng)建視圖

基本語(yǔ)法

create view 視圖名字 as select語(yǔ)句; -- select語(yǔ)句可以是普通查詢，也可以是連接查詢，可以是聯(lián)合查詢，可以是子查詢

創(chuàng)建單表視圖：基表只有一個(gè)

創(chuàng)建多表視圖：基表來(lái)源至少兩個(gè)

查看視圖

查看視圖：查看視圖的結(jié)構(gòu)(show tables; desc 以及show create table)

視圖比表還是有一個(gè)關(guān)鍵字的區(qū)別：view，查看“表(視圖)”的創(chuàng)建語(yǔ)句的時(shí)候可以使用view關(guān)鍵字

視圖一旦創(chuàng)建：系統(tǒng)會(huì)在視圖對(duì)應(yīng)的數(shù)據(jù)庫(kù)文件夾下創(chuàng)建一個(gè)對(duì)應(yīng)的結(jié)構(gòu)文件：frm文件

使用視圖

使用視圖主要是為了查詢：將視圖當(dāng)做表一樣查詢即可

視圖的執(zhí)行：其實(shí)本質(zhì)就是執(zhí)行封裝的select語(yǔ)句

修改視圖

視圖本身不可修改，但是視圖的來(lái)源是可以修改的

修改視圖：修改視圖本身的來(lái)源(select語(yǔ)句)

alter view 視圖名字 as 新的select語(yǔ)句

刪除視圖

drop view 視圖名字；

視圖意義

1. 視圖可以節(jié)省SQL語(yǔ)句：將一條復(fù)雜的查詢語(yǔ)句使用視圖進(jìn)行保存：以后可以直接對(duì)視圖進(jìn)行操作

2. 數(shù)據(jù)安全：視圖操作主要是針對(duì)查詢的，如果對(duì)視圖結(jié)構(gòu)進(jìn)行處理(刪除)，不會(huì)影響表數(shù)據(jù)(相對(duì)安全)

3. 視圖往往是在大項(xiàng)目中使用，而且是多系統(tǒng)使用：可以對(duì)外提供有用的數(shù)據(jù)，但是隱藏關(guān)鍵(無(wú)用)的數(shù)據(jù)：數(shù)據(jù)安全

4. 視圖可以對(duì)外提供友好型：不同的視圖提供不同的數(shù)據(jù)，對(duì)外好像專(zhuān)門(mén)設(shè)計(jì)

5. 視圖可以更好(容易)的進(jìn)行權(quán)限控制

視圖數(shù)據(jù)操作

視圖是的確可以進(jìn)行數(shù)據(jù)寫(xiě)操作的：但是有很多限制

將數(shù)據(jù)直接在視圖上進(jìn)行操作

新增數(shù)據(jù)

數(shù)據(jù)新增就是直接對(duì)視圖進(jìn)行數(shù)據(jù)新增

1. 多表視圖不能新增數(shù)據(jù)

2. 可以向單表視圖插入數(shù)據(jù)：但是視圖中包含的字段必須有基表所有不能為空(或者沒(méi)有默認(rèn)值)字段

3. 視圖是可以向基表中插入數(shù)據(jù)

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

多表視圖不能刪除數(shù)據(jù)

單表視圖可以刪除數(shù)據(jù)

更新數(shù)據(jù)

理論上不論單表視圖還是多表視圖都可以更新數(shù)據(jù)

更新限制：with check option，如果對(duì)視圖在新增的時(shí)候，限定了某個(gè)字段有限制：那么在對(duì)視圖進(jìn)行數(shù)據(jù)更新操作時(shí)，系統(tǒng)會(huì)進(jìn)行驗(yàn)證：要保證更新之后，數(shù)據(jù)
依然可以被視圖查詢出來(lái)，否則不讓更新

視圖算法

視圖算法：系統(tǒng)對(duì)視圖以及外部查詢視圖的select語(yǔ)句的一種解析方式

視圖算法分為三種：

undefined：未定義的(默認(rèn)的)，這不是一種實(shí)際使用算法，是一種推卸責(zé)任的算法：告訴系統(tǒng)，視圖沒(méi)有定義算法，系統(tǒng)看著辦

temptable：臨時(shí)表算法：系統(tǒng)應(yīng)該先執(zhí)行視圖的select語(yǔ)句，后執(zhí)行外部查詢語(yǔ)句

merge：合并算法：系統(tǒng)應(yīng)該先將視圖對(duì)應(yīng)的select語(yǔ)句與外部查詢視圖的select語(yǔ)句進(jìn)行合并，然后執(zhí)行(效率高：常態(tài))

算法指定：在創(chuàng)建視圖的時(shí)候

create algorithm = 指定算法 view 視圖名字 as select語(yǔ)句;

視圖算法選擇：如果視圖的select語(yǔ)句中會(huì)包含一個(gè)查詢子句(五子句)，而且很有可能順序比外部的查詢語(yǔ)句要靠后，一定要使用算法temptable，其他情況
可以不用指定(使用默認(rèn)即可)

數(shù)據(jù)備份與還原

備份：將當(dāng)前已有的數(shù)據(jù)或記錄保留

還原：將已經(jīng)保留的數(shù)據(jù)恢復(fù)到對(duì)應(yīng)的表中

為什么要做備份還原？

1. 防止數(shù)據(jù)丟失：被盜，誤操作

2. 保護(hù)數(shù)據(jù)記錄

數(shù)據(jù)備份還原的方式有很多種：數(shù)據(jù)表備份，單表數(shù)據(jù)備份，SQL備份，增量備份

數(shù)據(jù)表備份

不需要通過(guò)SQL來(lái)備份：直接進(jìn)入到數(shù)據(jù)庫(kù)文件夾復(fù)制對(duì)應(yīng)的表結(jié)構(gòu)以及數(shù)據(jù)文件，以后還原的時(shí)候，直接將備份的內(nèi)容放進(jìn)去即可

數(shù)據(jù)表備份有前提條件：根據(jù)不同的存儲(chǔ)引擎有不同的區(qū)別

存儲(chǔ)引擎：mysql進(jìn)行數(shù)據(jù)存儲(chǔ)的方式：主要是兩種innodb和myisam(免費(fèi))

對(duì)比myisam和innodb：數(shù)據(jù)存儲(chǔ)方式

innodb：只有表結(jié)構(gòu)，數(shù)據(jù)全部存儲(chǔ)到ibdata1文件中

myisam：表，數(shù)據(jù)和索引全部單獨(dú)分開(kāi)存儲(chǔ)

這種文件備份通常適用于myisam存儲(chǔ)引擎：直接復(fù)制三個(gè)文件即可，然后放到對(duì)應(yīng)的數(shù)據(jù)庫(kù)下既可以使用

單表數(shù)據(jù)備份

每次只能備份一張表，只能備份數(shù)據(jù)(表結(jié)構(gòu)不能備份)

通常的使用：將表中的數(shù)據(jù)進(jìn)行導(dǎo)出文件

備份：從表中選出一部分?jǐn)?shù)據(jù)保存到外部文件中(outfile)

select */字段列表 into outfile 文件所在路徑 from 數(shù)據(jù)源; -- 前提：外部文件不存在

高級(jí)備份：自己指定字段和行的處理方式

select */字段列表 into outfile 文件所在路徑 fields字段處理 lines行處理 from 數(shù)據(jù)源;

fields：字段處理

enclosed by：字段用什么內(nèi)容包裹，默認(rèn)是''字符串

terminated by：字段以什么結(jié)束，默認(rèn)是"\t"，tab鍵

escaped by：特殊符號(hào)用什么方式處理，默認(rèn)是"\\"，使用反斜杠轉(zhuǎn)義

lines：行處理

starting by：每行以什么開(kāi)始，默認(rèn)是''，空字符串

terminated by：每行以什么結(jié)束，默認(rèn)是"\r\n"，換行符

數(shù)據(jù)還原：將一個(gè)在外部保存的數(shù)據(jù)重新恢復(fù)到表中(如果表結(jié)構(gòu)不存在，那么sorry)

load data infile 文件所在路徑 into table 表名[(字段列表)] fields 字段處理 lines 行處理; -- 怎么備份的怎么還原

SQL備份

備份的是SQL語(yǔ)句：系統(tǒng)會(huì)對(duì)表結(jié)構(gòu)以及數(shù)據(jù)進(jìn)行處理，變成對(duì)應(yīng)的SQL語(yǔ)句，然后進(jìn)行備份：還原的時(shí)候只要執(zhí)行SQL指令即可(主要就是針對(duì)表結(jié)構(gòu))

備份：mysql沒(méi)有提供備份指令：需要利用mysql提供的軟件：mysqldump.exe

mysqldump.exe 也是一種客戶端，需要操作服務(wù)器：必須連接認(rèn)證

mysqldump/mysqldump.exe -hPup 數(shù)據(jù)庫(kù)名字 [數(shù)據(jù)表名字1[數(shù)據(jù)表名字2...]] > 外部文件目錄(建議使用.sql)

單表備份

整庫(kù)備份

mysqldump/mysqldump.exe -hPup 數(shù)據(jù)庫(kù)名字 > 外部文件目錄(建議使用.sql)

SQL還原數(shù)據(jù)：兩種方式還原

方案1：使用mysql.exe客戶端還原

mysql.exe/mysql -hPup 數(shù)據(jù)庫(kù)名字 < 備份文件目錄

方案2：使用SQL指令還原

source 備份文件所在路徑;

SQL備份優(yōu)缺點(diǎn)：

1.優(yōu)點(diǎn)：可以備份結(jié)構(gòu)

2.缺點(diǎn)：會(huì)浪費(fèi)空間(會(huì)額外的增加SQL指令)

增量備份

不是針對(duì)數(shù)據(jù)或者SQL指令進(jìn)行備份：是針對(duì)mysql服務(wù)器的日志文件進(jìn)行備份

增量備份：指定時(shí)間段開(kāi)始備份，備份數(shù)據(jù)不會(huì)重復(fù)，而且所有的操作都會(huì)備份(大項(xiàng)目都用增量備份)

day 6