《PHP+MySQL動(dòng)態(tài)網(wǎng)站開(kāi)發(fā)》 課件-第7章 MySQL多表操作

第7章MySQL多表操作《PHP+MySQL動(dòng)態(tài)網(wǎng)站開(kāi)發(fā)》學(xué)習(xí)目標(biāo)/Target理解數(shù)據(jù)庫(kù)設(shè)計(jì)范式和表關(guān)系掌握對(duì)數(shù)據(jù)的分組、排序等操作掌握多表之間的連接查詢、聯(lián)合查詢的使用掌握子查詢的使用掌握外鍵的添加、使用以及刪除等操作章節(jié)概述/Summary在前面的章節(jié)中，已學(xué)習(xí)了MySQL的基本知識(shí)和查詢語(yǔ)法，然而在實(shí)際開(kāi)發(fā)中，業(yè)務(wù)邏輯較為復(fù)雜，通常都需要進(jìn)行多張表的關(guān)聯(lián)操作，才能滿足需求。本章將學(xué)習(xí)數(shù)據(jù)庫(kù)的設(shè)計(jì)范式、數(shù)據(jù)的分組和排序、多表聯(lián)合查詢，以及子查詢和外鍵的使用。目錄/Contents01020304數(shù)據(jù)庫(kù)設(shè)計(jì)范式表關(guān)系數(shù)據(jù)查詢聯(lián)合查詢目錄/Contents050607連接查詢子查詢外鍵數(shù)據(jù)庫(kù)設(shè)計(jì)范式7.17.1.1第一范式

先定一個(gè)小目標(biāo)！熟悉第一范式，能夠說(shuō)出第一范式的具體要求，能夠分析數(shù)據(jù)表是否符合第一范式7.1.1第一范式第一范式（1NF）：遵從原子性，屬性不可再分，數(shù)據(jù)庫(kù)表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值。7.1.1第一范式不滿足第一范式的數(shù)據(jù)表示例編號(hào)聯(lián)系方式1張三郵箱：zhangsan@,手機(jī)號(hào)：189000000002李四郵箱：lisi@,手機(jī)號(hào)17300000000編號(hào)用戶名郵箱手機(jī)號(hào)手機(jī)號(hào)1張三zhangsan/p>

2李四

lisi@1590000000017300000000用戶聯(lián)系方式表：7.1.1第一范式滿足第一范式的數(shù)據(jù)表示例用戶編號(hào)用戶名1張三2李四編號(hào)用戶編號(hào)聯(lián)系方式具體值11郵箱zhangsan@21手機(jī)號(hào)1890000000032郵箱lisi@42手機(jī)號(hào)1590000000052手機(jī)戶表：聯(lián)系方式表：7.1.2第二范式

先定一個(gè)小目標(biāo)！熟悉第二范式，能夠說(shuō)出第二范式的具體要求，能夠分析數(shù)據(jù)表是否符合第二范式7.1.2第二范式第二范式（2NF）：遵從唯一性，非主鍵字段需完全依賴主鍵，在第一范式的基礎(chǔ)上建立的，滿足第二范式必須先滿足第一范式。7.1.2第二范式不滿足第二范式的數(shù)據(jù)表示例訂單表：訂單編號(hào)訂單商品購(gòu)買(mǎi)件數(shù)下單時(shí)間1鉛筆32020-01-2008:30:152鋼筆22020-01-2109:00:153圓珠筆12020-01-2209:30:15用戶編號(hào)訂單編號(hào)用戶名付款狀態(tài)11張三已支付12張三未支付23李四已支付用戶表：7.1.2第二范式使用上述方式設(shè)計(jì)的用戶表存在的問(wèn)題：插入異常：如果用戶沒(méi)有下過(guò)訂單，則該用戶無(wú)法插入。刪除異常：刪除用戶所有的訂單，該用戶也會(huì)被刪除。更新異常：由于用戶名冗余，修改用戶時(shí)需要修改多條記錄。如果漏改某些記錄，會(huì)出現(xiàn)更新異常。7.1.2第二范式滿足第二范式的數(shù)據(jù)表示例用戶編號(hào)用戶名1張三2李四訂單編號(hào)用戶編號(hào)訂單商品購(gòu)買(mǎi)件數(shù)下單時(shí)間付款狀態(tài)11鉛筆32020-01-2008:30:15已支付21鋼筆22020-01-2109:00:15未支付32圓珠筆12020-01-2209:30:15已支付用戶表：訂單表：7.1.3第三范式

先定一個(gè)小目標(biāo)！熟悉第三范式，能夠說(shuō)出第三范式的具體要求，能夠分析數(shù)據(jù)表是否符合第三范式7.1.3第三范式第三范式（3NF）：非主鍵字段不能相互依賴，在第二范式的基礎(chǔ)上建立起來(lái)的，數(shù)據(jù)表中每一列數(shù)據(jù)都和主鍵直接相關(guān)，不能間接相關(guān)。7.1.3第三范式不滿足第三范式的數(shù)據(jù)表示例用戶編號(hào)用戶名用戶等級(jí)享受折扣1張三10.952李四10.953王五20.85用戶表：使用上述方式設(shè)計(jì)的用戶表存在的問(wèn)題：插入異常：新插入用戶的等級(jí)如果在1、2之外，其享受的折扣無(wú)處參考。刪除異常：如果刪除某個(gè)等級(jí)下所有的用戶，該等級(jí)對(duì)應(yīng)的折扣也被刪除。更新異常：如果修改某個(gè)用戶的等級(jí)，折扣也必須隨之修改；如果修改某個(gè)等級(jí)的折扣，又因?yàn)檎劭鄞嬖谌哂啵菀装l(fā)生漏改。7.1.3第三范式滿足第三范式的數(shù)據(jù)表示例用戶編號(hào)用戶名用戶等級(jí)1張三12李四13王五2用戶等級(jí)享受折扣10.9520.85用戶表：折扣表：7.1.4逆規(guī)范化

先定一個(gè)小目標(biāo)！熟悉逆規(guī)范化，能夠說(shuō)出范式的局限性，能夠說(shuō)出什么情況下需要逆規(guī)范化7.1.4逆規(guī)范化逆規(guī)范化：反范式的設(shè)計(jì)，主要目的是提高查詢效率。使用范式導(dǎo)致的問(wèn)題：表數(shù)量增加，連接查詢時(shí)性能降低。例如，查詢訂單表統(tǒng)計(jì)商品銷量，數(shù)據(jù)量很大時(shí)需要很多時(shí)間計(jì)算。解決方法：在表中適當(dāng)添加冗余字段，解決連接查詢問(wèn)題。例如，在商品表增加銷量字段，商品被購(gòu)買(mǎi)時(shí)就更新銷量。7.1.4逆規(guī)范化逆規(guī)范化數(shù)據(jù)表示例商品編號(hào)商品名稱商品價(jià)格商品銷量1鉛筆252鋼筆8913圓珠筆101訂單編號(hào)商品編號(hào)購(gòu)買(mǎi)件數(shù)下單時(shí)間1132020-01-2008:30:152122020-01-2109:00:153212020-01-2209:30:154312020-01-2309:59:15商品表：訂單表：表關(guān)系7.27.2.1一對(duì)一

先定一個(gè)小目標(biāo)！熟悉一對(duì)一的表關(guān)系，能夠?qū)?shù)據(jù)表拆分成一對(duì)一關(guān)系7.2.1一對(duì)一一對(duì)一：表中的一條記錄與另外一張表中有且僅有一條記錄有關(guān)系。實(shí)現(xiàn)一對(duì)一表關(guān)系：一張表拆分成兩個(gè)表，將頻繁使用的字段和生僻字段進(jìn)行垂直分割，使用相同的主鍵對(duì)應(yīng)。拆分前數(shù)據(jù)表示例學(xué)號(hào)姓名性別年齡身高體重籍貫政治面貌1張三男20165140河北團(tuán)員2李四女21168100山東團(tuán)員3小明男22170130陜西團(tuán)員姓名、性別和年齡為頻繁使用字段7.2.1一對(duì)一一對(duì)一設(shè)計(jì)數(shù)據(jù)表示例學(xué)號(hào)姓名性別年齡1張三男202李四女213小明男22學(xué)號(hào)身高體重籍貫政治面貌1165140河北團(tuán)員2168100山東團(tuán)員3170130陜西團(tuán)員學(xué)生表：學(xué)生信息表：7.2.2一對(duì)多

先定一個(gè)小目標(biāo)！熟悉一對(duì)多的表關(guān)系，能夠舉例說(shuō)明哪些表符合一對(duì)多關(guān)系7.2.2一對(duì)多一對(duì)多：表中的一條記錄與另外一張表中多條記錄對(duì)應(yīng)，也叫多對(duì)一。一對(duì)多設(shè)計(jì)數(shù)據(jù)表示例班級(jí)編號(hào)班級(jí)名班主任1軟件班張老師2設(shè)計(jì)班王老師學(xué)號(hào)姓名性別班級(jí)號(hào)1張三男12李四女13小明男2班級(jí)表：學(xué)生表：7.2.3多對(duì)多

先定一個(gè)小目標(biāo)！熟悉多對(duì)多的表關(guān)系，能夠舉例說(shuō)明哪些表符合多對(duì)多關(guān)系7.2.3多對(duì)多多對(duì)多：表中的多條記錄與另外一張表中多條記錄對(duì)應(yīng)，需要借助第三張表，將多對(duì)多關(guān)系變成多個(gè)多對(duì)一的關(guān)系。多對(duì)多設(shè)計(jì)數(shù)據(jù)表示例課程編號(hào)課程名1計(jì)算機(jī)2數(shù)據(jù)庫(kù)課程表：7.2.3多對(duì)多多對(duì)多設(shè)計(jì)數(shù)據(jù)表示例學(xué)號(hào)姓名性別班級(jí)號(hào)1張三男12李四女13小明男2編號(hào)學(xué)號(hào)課程編號(hào)111221312432學(xué)生表：學(xué)生選課表：7.3數(shù)據(jù)查詢7.3.1查詢選項(xiàng)

先定一個(gè)小目標(biāo)！掌握查詢選項(xiàng)，能夠在查詢時(shí)指定返回的字段，并去除重復(fù)記錄7.3.1查詢選項(xiàng)去除重復(fù)記錄：使用SELECT語(yǔ)句的選項(xiàng)進(jìn)行篩選。SELECTselect選項(xiàng)字段列表FROM數(shù)據(jù)表;語(yǔ)法格式select選項(xiàng)默認(rèn)值為ALL，表示保存所有查詢到的記錄select選項(xiàng)值為DISTINCT，表示去除重復(fù)記錄，只保留一條7.3.1查詢選項(xiàng)查詢?nèi)坑涗洿a示例去除重復(fù)項(xiàng)代碼示例7.3.2運(yùn)算符

先定一個(gè)小目標(biāo)！掌握運(yùn)算符的使用，能夠利用比較運(yùn)算符、邏輯運(yùn)算符對(duì)數(shù)據(jù)進(jìn)行運(yùn)算7.3.2運(yùn)算符比較運(yùn)算符比較運(yùn)算符：在條件表達(dá)式中對(duì)結(jié)果進(jìn)行限定。比較運(yùn)算符的結(jié)果值：1（TRUE，表示為真）、0（FALSE，表示為假）或NULL。7.3.2運(yùn)算符比較運(yùn)算符運(yùn)算符描述=用于相等比較<=>可以進(jìn)行NULL值比較的相等運(yùn)算符>

表示大于比較<

表示小于比較>=表示大于等于比較<=表示小于等于比較<>、!=表示不等于比較BRTWEEN…AND…比較數(shù)據(jù)是否在閉區(qū)間范圍內(nèi)，若在返回1，不在返回0（1/2）7.3.2運(yùn)算符比較運(yùn)算符運(yùn)算符描述NOTBRTWEEN…AND…比較數(shù)據(jù)是否不在閉區(qū)間范圍內(nèi)，若不在返回1，若在返回0IS比較數(shù)據(jù)是否是TRUE、FALSE或UNKNOWN，若是返回1，若不是返回0ISNOT比較數(shù)據(jù)是否不是TRUE、FALSE或UNKNOWN，若不是返回1，若是返回0ISNULL比較數(shù)據(jù)是否是NULL，若是返回1，若不是返回0ISNOTNULL比較數(shù)據(jù)是否不是NULL，若不是返回1，若是返回0LIKE'匹配模式'獲取匹配到的數(shù)據(jù)NOTLIKE'匹配模式'獲取匹配不到的數(shù)據(jù)（2/2）7.3.2運(yùn)算符案例：比較運(yùn)算符的使用代碼示例#查詢my_goods表中價(jià)格在2000到6000的商品，商品信息包括id、name和pricemysql>SELECT`id`,`name`,`price`FROM`my_goods`->WHERE`price`BETWEEN2000AND6000;+----+------------+---------+|id|name|price|+----+------------+---------+|4|液晶顯示器|5999.00||8|辦公計(jì)算機(jī)|2000.00|+----+------------+---------+7.3.2運(yùn)算符邏輯運(yùn)算符邏輯運(yùn)算符：在條件表達(dá)式中用于邏輯判斷、可與比較運(yùn)算符結(jié)合使用。邏輯運(yùn)算符的結(jié)果值：1（TRUE，表示為真）、0（FALSE，表示為假）或NULL。運(yùn)算符描述AND或&&邏輯與，若操作數(shù)全部為真，則結(jié)果為1，否則為0OR或||邏輯或，若操作數(shù)中只要有一個(gè)為真，則結(jié)果為1，否則為0NOT或!邏輯非，若操作數(shù)為0，則結(jié)果為1，否則為0XOR邏輯異或，若操作數(shù)一個(gè)為真，一個(gè)為假，則結(jié)果為1；若操作數(shù)全部為真或全部為假，則結(jié)果為07.3.2運(yùn)算符案例：邏輯運(yùn)算符的使用代碼示例#查詢my_goods表中關(guān)鍵詞為電子產(chǎn)品的5星商品，商品信息包括id、name和pricemysql>SELECT`id`,`name`,`price`FROM`my_goods`

->WHERE`keyword`='電子產(chǎn)品'&&`score`=5;+----+---------+---------+|id|name|price|+----+---------+---------+|5|智能手機(jī)|1999.00|+----+---------+---------+7.3.3分組

先定一個(gè)小目標(biāo)！掌握分組操作，能夠在查詢時(shí)利用聚合函數(shù)進(jìn)行分組、分組并排序、回溯統(tǒng)計(jì)、數(shù)據(jù)篩選7.3.3分組分組分組：在WHERE條件后面添加GROUPBY。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]GROUPBY字段名;語(yǔ)法格式注意：對(duì)數(shù)據(jù)分組后，SELECT獲取的字段列表只能是GROUPBY分組的字段或使用了聚合函數(shù)的非分組字段。7.3.3分組分組常用聚合函數(shù)：函數(shù)名描述COUNT()返回參數(shù)字段的數(shù)量，不統(tǒng)計(jì)為NULL的記錄SUM()返回參數(shù)字段之和AVG()返回參數(shù)字段的平均值MAX()返回參數(shù)字段的最大值MIN()返回參數(shù)字段的最小值GROUP_CONCAT()返回復(fù)合條件的參數(shù)字段值的連接字符串7.3.3分組分組使用聚合函數(shù)：使用MAX()獲取每個(gè)分類下商品的最高價(jià)格。mysql>SELECT`category_id`,MAX(`price`)FROM`my_goods`GROUPBY`category_id`;+-------------+------------+|category_id|MAX(price)|+-------------+------------+|3|15.00||6|1999.00||8|69.00||9|109.00||10|2000.00||12|5999.00||15|299.00||16|48.00|+-------------+------------+7.3.3分組回溯統(tǒng)計(jì)回溯統(tǒng)計(jì)：系統(tǒng)自動(dòng)對(duì)分組字段向上進(jìn)行一次新的統(tǒng)計(jì)并產(chǎn)生一個(gè)新的統(tǒng)計(jì)數(shù)據(jù)，該數(shù)據(jù)對(duì)應(yīng)的分組字段值為NULL。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]GROUPBY字段名1[,字段名2…]WITHROLLUP;語(yǔ)法格式7.3.3分組案例：?jiǎn)巫侄位厮萁y(tǒng)計(jì)代碼示例mysql>SELECT`category_id`,COUNT(*)FROM`my_goods`GROUPBY`category_id`WITHROLLUP;+-------------+----------+|category_id|COUNT(*)|+-------------+----------+|3|3||6|1||8|1||9|1||10|1||12|1||15|1||16|1||NULL|10|對(duì)分組的回溯統(tǒng)計(jì)+-------------+----------+7.3.3分組案例：多分組回溯統(tǒng)計(jì)代碼示例mysql>SELECT`score`,`comment_count`,COUNT(*)FROM`my_goods`GROUPBY`score`,`comment_count`WITHROLLUP;+-------+-------------------+-----------+|score|comment_count|COUNT(*)|+-------+-------------------+-----------+|2.50|200|1||2.50|NULL|1||3.90|500|2||3.90|NULL|2||4.50|1000|1||4.50|NULL|1||4.80|6000|1||4.80|98000|1||4.80|NULL|2||4.90|40000|2||4.90|NULL|2||5.00|98000|2||5.00|NULL|2||NULL|NULL|10|+-------+-------------------+-----------+7.3.3分組排序分組排序：為指定字段進(jìn)行升序或降序排序的功能，默認(rèn)為升序。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]GROUPBY字段名[ASC|DESC];語(yǔ)法格式7.3.3分組案例：分組排序代碼示例STEP01mysql>SELECT`category_id`,GROUP_CONCAT(`id`),GROUP_CONCAT(`name`)->FROM`my_goods`GROUPBY`category_id`DESC;+-------------+-----------------------+---------------------------+|category_id|GROUP_CONCAT(id)|GROUP_CONCAT(name)|+-------------+-----------------------+---------------------------+|16|10|薄毛衣||15|9|收腰風(fēng)衣||12|4|液晶顯示器||10|8|辦公計(jì)算機(jī)||9|7|頭戴耳機(jī)||8|6|桌面音箱||6|5|智能手機(jī)||3|1,2,3|2B鉛筆,鋼筆,碳素筆|+-------------+------------------------+---------------------------+7.3.3分組HAVING子句篩選分組結(jié)果：對(duì)查詢的數(shù)據(jù)執(zhí)行分組操作時(shí)，可以利用HAVING根據(jù)條件進(jìn)行數(shù)據(jù)篩選。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]GROUPBY字段名HAVING條件表達(dá)式;語(yǔ)法格式7.3.3分組HAVING子句HAVING和WHERE區(qū)別：WHERE操作是從數(shù)據(jù)表中獲取數(shù)據(jù)，將數(shù)據(jù)從磁盤(pán)存儲(chǔ)到內(nèi)存中，HAVING是對(duì)已存放到內(nèi)存中的數(shù)據(jù)進(jìn)行操作。HAVING位于GROUPBY子句后，WHERE位于GROUPBY子句之前。HAVING關(guān)鍵字后可以使用聚合函數(shù)，WHERE不可以。7.3.3分組案例：分組篩選代碼示例STEP01mysql>SELECT`score`,`comment_count`,GROUP_CONCAT(`id`)->FROM`my_goods`GROUPBY`score`,`comment_count`->HAVINGCOUNT(*)=2;+-------+-------------------+----------------------+|score|comment_count|GROUP_CONCAT(id)|+-------+-------------------+----------------------+|3.90|500|2,7||4.90|40000|1,9||5.00|98000|3,5|+-------+-------------------+----------------------+7.3.4排序

先定一個(gè)小目標(biāo)！掌握排序操作，能夠?qū)⒉樵兘Y(jié)果按照升序或降序進(jìn)行排列7.3.4排序STEP02SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]ORDERBY字段名1[,字段名2…][ASC|DESC];語(yǔ)法格式兩種排序方式：?jiǎn)巫侄闻判蚝投嘧侄闻判颉?.3.4排序案例：?jiǎn)巫侄闻判虼a示例STEP02mysql>SELECT`id`,`name,`price`FROM`my_goods`ORDERBY`price`DESC;+----+------------+---------+|id|name|price|+----+------------+---------+|4|液晶顯示器|5999.00||8|辦公計(jì)算機(jī)|2000.00||5|智能手機(jī)|1999.00||9|收腰風(fēng)衣|299.00||7|頭戴耳機(jī)|109.00||6|桌面音箱|69.00||10|薄毛衣|48.00||2|鋼筆|15.00||3|碳素筆|1.00||1|2B鉛筆|0.50|+----+-----------+---------+7.3.4排序案例：多字段排序代碼示例STEP02mysql>SELECT`category_id`,`id`,`name`,`price`FROM`my_goods`ORDERBY`category_id`,`price`DESC;+-------------+----+----------+---------+|category_id|id|name|price|+-------------+----+----------+---------+|3|2|鋼筆|15.00||3|3|碳素筆|1.00||3|1|2B鉛筆|0.50||6|5|智能手機(jī)|1999.00||8|6|桌面音箱|69.00||9|7|頭戴耳機(jī)|109.00||10|8|辦公計(jì)算機(jī)|2000.00||12|4|液晶顯示器|5999.00||15|9|收腰風(fēng)衣|299.00||16|10|薄毛衣|48.00|+-------------+----+----------+---------+7.3.4排序STEP02中文排序使用“CONVERT(字段名USINGgbk)”函數(shù)強(qiáng)制讓指定的字段按中文拼音順序排序。案例：代碼示例mysql>mysql>SELECT`id`,`name`FROM`my_goods`ORDERBYCONVERT(`name`USINGgbk)ASC;+----+------------+|id|name|+----+------------+|1|2B鉛筆||8|辦公計(jì)算機(jī)||10|薄毛衣||4|液晶顯示器|+----+------------+7.3.5限量

先定一個(gè)小目標(biāo)！掌握限量操作，能夠通過(guò)LIMIT子句限制查詢結(jié)果返回的條數(shù)7.3.5限量限定記錄的數(shù)量：使用LIMIT關(guān)鍵字限定記錄的數(shù)量，也可以指定從哪一條記錄開(kāi)始查詢。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]LIMIT[OFFSET,]記錄數(shù);語(yǔ)法格式記錄數(shù)：表示限定獲取的最大記錄數(shù)量可選項(xiàng)OFFSET：表示偏移量，設(shè)置從哪條記錄開(kāi)始，默認(rèn)第1條記錄的偏移量值為0，第2條值為1，以此類推。7.3.5限量案例：限量查詢代碼示例STEP01mysql>SELECT`id`,`name`,`price`FROM`my_goods`->ORDERBY`price`DESCLIMIT1;+----+------------+---------+|id|name|price|+----+------------+---------+|4|液晶顯示器|5999.00|+----+------------+---------+7.3.5限量案例：指定區(qū)間代碼示例STEP01mysql>SELECT`id`,`name`,`price`FROM`my_goods`LIMIT0,5;+----+-----------+---------+|id|name|price|+----+-----------+---------+|1|2B鉛筆|0.50||2|鋼筆|15.00||3|碳素筆|1.00||4|液晶顯示器|5999.00||5|智能手機(jī)|1999.00|+----+------------+---------+7.4聯(lián)合查詢7.4.1什么是聯(lián)合查詢

先定一個(gè)小目標(biāo)！熟悉什么是聯(lián)合查詢，能夠?qū)⒍鄠€(gè)SELECT查詢結(jié)果合并到一起返回7.4.1什么是聯(lián)合查詢聯(lián)合查詢：多表查詢的一種方式，在多個(gè)SELECT語(yǔ)句的查詢字段數(shù)相同時(shí)，合并多個(gè)查詢結(jié)果。語(yǔ)法格式SELECT…UNION[ALL|DISTINCT]SELECT…[UNION[ALL|DISTINCT]SELECT…];UNION：實(shí)現(xiàn)聯(lián)合查詢的關(guān)鍵字聯(lián)合查詢的選項(xiàng)：ALL表示保留所有查詢結(jié)果；DISTINCT默認(rèn)值表示去除完全重復(fù)的記錄7.4.1什么是聯(lián)合查詢案例：聯(lián)合查詢的使用代碼示例mysql>SELECT`id`,`name`,`price`FROM`my_goods`WHERE`category_id`=9->UNION->SELECT`id`,`name`,`keyword`FROM`my_goods`WHERE`category_id`=6;+----+---------+----------+|id|name|price|+----+---------+----------+|7|頭戴耳機(jī)|109.00||5|智能手機(jī)|電子產(chǎn)品|+----+---------+----------+7.4.2聯(lián)合查詢并排序

先定一個(gè)小目標(biāo)！掌握聯(lián)合查詢并排序，能夠?qū)⒙?lián)合查詢后的結(jié)果按照升序或降序返回7.4.2聯(lián)合查詢并排序?qū)崿F(xiàn)方式：使用圓括號(hào)（）包裹SELECT語(yǔ)句，在SELECT語(yǔ)句內(nèi)或在聯(lián)合查詢的最后添加ORDERBY語(yǔ)句，在ORDERBY后添加LIMIT限定聯(lián)合查詢排序的數(shù)量，推薦使用大于表記錄數(shù)的任意值。代碼示例mysql>(SELECT`id`,`name`,`price`FROM`my_goods`WHERE`category_id`<>3->ORDERBY`price`DESCLIMIT7)->UNION->(SELECT`id`,`name`,`price`FROM`my_goods`WHERE`category_id`=3->ORDERBY`price`ASCLIMIT3);7.5連接查詢7.5.1交叉連接

先定一個(gè)小目標(biāo)！掌握交叉連接查詢，能夠?qū)蓮埍磉M(jìn)行交叉連接，返回查詢結(jié)果7.5.1交叉連接交叉連接：返回被連接的兩個(gè)表中所有數(shù)據(jù)行的乘積。表13個(gè)字段、4條記錄表25個(gè)字段、10條記錄交叉連接結(jié)果：3+5個(gè)字段、4×10條記錄7.5.1交叉連接語(yǔ)法格式SELECT查詢字段FROM表1CROSSJOIN表2;代碼示例mysql>SELECTc.`id`cid,c.`name`cname,g.`id`gid,g.`name`gname->FROM`my_goods_category`ASc->CROSSJOIN`my_goods`ASg;7.5.2內(nèi)連接

先定一個(gè)小目標(biāo)！掌握內(nèi)連接查詢，能夠?qū)蓮埍磉M(jìn)行內(nèi)連接，返回查詢結(jié)果7.5.2內(nèi)連接內(nèi)連接：根據(jù)匹配條件返回第1個(gè)表與第2個(gè)表所有匹配成功的記錄。結(jié)果：3條記錄7.5.2內(nèi)連接語(yǔ)法格式SELECT查詢字段FROM表1[INNER]JOIN表2ON匹配條件;代碼示例mysql>SELECTg.`id`gid,g.`name`gname,c.`id`cid,c.`name`cname->FROM`my_goods`gJOIN`my_goods_category`c->ONg.`category_id`=c.`id`;7.5.3外連接

先定一個(gè)小目標(biāo)！掌握外連接查詢，能夠?qū)蓮埍磉M(jìn)行左外連接或右外連接查詢，返回查詢結(jié)果7.5.3外連接結(jié)果：左表中的記錄和交集部分左外連接左外連接：也稱左連接，返回連接關(guān)鍵字（LEFTJOIN）左表的所有記錄和右表符合連接條件的記錄。左表右表7.5.3外連接語(yǔ)法格式SELECT查詢字段FROM表1LEFT[OUTER]JOIN表2ON匹配條件;代碼示例mysql>SELECTg.`id`gid,g.`name`gname,c.`id`cid,c.`name`cname->FROM`my_goods`gLEFTJOIN`my_goods_category`c->ONg.`category_id`=c.`id`ANDg.`score`=5;7.5.3外連接結(jié)果：右表中的記錄和交集部分右外連接右外連接：也稱右連接，返回連接關(guān)鍵字（RIGHTJOIN）右表所有的記錄和左表符合連接條件的記錄。右表左表7.5.3外連接語(yǔ)法格式SELECT查詢字段FROM表1RIGHT[OUTER]JOIN表2ON匹配條件;代碼示例mysql>SELECTg.`id`gid,g.`name`gname,c.`id`cid,c.`name`cname->FROM`my_goods`gRIGHTJOIN`my_goods_category`c->ONc.`id`=g.`category_id`ANDg.`score`=5;7.5.4自然連接

先定一個(gè)小目標(biāo)！掌握自然連接查詢，能夠?qū)蓮埍磉M(jìn)行自然連接查詢，返回查詢結(jié)果7.5.4自然連接語(yǔ)法格式SELECT查詢字段FROM表1NATURALJOIN表2;自然連接（NATURALJOIN）可以自動(dòng)尋找連接條件。7.5.4自然連接案例：自然連接代碼示例mysql>CREATETABLE`my_student`(->`id`int(11)NOTNULLAUTO_INCREMENT,->`name`varchar(50)NOTNULL,->`c_id`int(11)DEFAULTNULLCOMMENT'課程id',->PRIMARYKEY(`id`)->)ENGINE=InnoDBDEFAULTCHARSET=utf8;mysql>INSERTINTO`my_student`VALUES('1','小明','1');mysql>INSERTINTO`my_student`VALUES('2','張三','1');mysql>INSERTINTO`my_student`VALUES('3','李四','2');STEP01創(chuàng)建my_student數(shù)據(jù)表并插入數(shù)據(jù)。7.5.4自然連接案例：自然連接代碼示例mysql>CREATETABLE`my_course`(->`c_id`int(11)NOTNULL,->`c_name`varchar(50)NOTNULL->)ENGINE=InnoDBDEFAULTCHARSET=utf8;mysql>INSERTINTO`my_course`VALUES('1','軟件工程');mysql>INSERTINTO`my_course`VALUES('2','頁(yè)面設(shè)計(jì)');創(chuàng)建my_course數(shù)據(jù)表并插入數(shù)據(jù)。STEP027.5.4自然連接案例：自然連接代碼示例SELECT*FROM`my_student`NATURALJOIN`my_course`;+------+----+------+----------+|c_id|id|name|c_name|+------+----+------+----------+|1|1|小明|軟件工程||1|2|張三|軟件工程||2|3|李四|頁(yè)面設(shè)計(jì)|+------+----+------+----------+使用自然連接查詢。STEP037.5.5USING關(guān)鍵字

先定一個(gè)小目標(biāo)！掌握USING關(guān)鍵字的使用，能夠使用USING關(guān)鍵字代替ON來(lái)設(shè)置連接使用的字段7.5.5USING關(guān)鍵字語(yǔ)法格式SELECT查詢字段FROM表1[CROSS|INNER|LEFT|RIGHT]JOIN表2USING(同名的連接字段列表);USING關(guān)鍵字的使用場(chǎng)景：使用連接查詢時(shí)如果數(shù)據(jù)表的字段同名，匹配條件可以使用USING代替ON。7.5.5USING關(guān)鍵字案例：USING關(guān)鍵字使用代碼示例mysql>SELECTDISTINCTg1.`id`,g1.`name`FROM`my_goods`g1->JOIN`my_goods`g2->USING(`category_id`)WHEREg2.`name`='鋼筆';+----+--------+|id|name|+----+--------+|1|2B鉛筆||3|碳素筆||2|鋼筆|+----+--------+STEP037.6子查詢7.6.1子查詢分類

先定一個(gè)小目標(biāo)！熟悉子查詢分類，能夠說(shuō)出子查詢的分類方式和類型7.6.1子查詢分類按功能劃分：標(biāo)量子查詢列子查詢行子查詢表子查詢按出現(xiàn)位置劃分：WHERE子查詢FROM子查詢7.6.2標(biāo)量子查詢

先定一個(gè)小目標(biāo)！掌握標(biāo)量子查詢，能夠通過(guò)標(biāo)量子查詢的方式查詢數(shù)據(jù)7.6.2標(biāo)量子查詢標(biāo)量子查詢：返回的結(jié)果是一個(gè)數(shù)據(jù)，即一行一列。語(yǔ)法格式WHERE條件判斷{=|<>}(SELECT字段名FROM數(shù)據(jù)源[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);利用比較運(yùn)算符=或<>判斷子查詢語(yǔ)句返回的數(shù)據(jù)是否與指定的條件相等或不等7.6.2標(biāo)量子查詢案例：標(biāo)量子查詢使用代碼示例mysql>SELECTnameFROM`my_goods_category`->WHERE`id`=(SELECT`category_id`FROM`my_goods`->WHERE`name`='智能手機(jī)');+------+|name|+------+|手機(jī)|+------+7.6.3列子查詢

先定一個(gè)小目標(biāo)！掌握列子查詢，能夠通過(guò)列子查詢的方式查詢數(shù)據(jù)7.6.3列子查詢列子查詢：返回的結(jié)果是一個(gè)字段符合條件的所有數(shù)據(jù)，即一列多行。語(yǔ)法格式WHERE條件判斷{IN|NOTIN}(SELECT字段名FROM數(shù)據(jù)源[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);利用比較運(yùn)算函數(shù)IN()或NOTIN()判斷指定的條件是否在子查詢語(yǔ)句返回的結(jié)果集中7.6.3列子查詢案例：列子查詢使用代碼示例mysql>SELECT`name`FROM`my_goods_category`->WHERE`id`IN(SELECTDISTINCT`category_id`FROM`my_goods`);+-------+|name|+-------+|文具||顯示器||手機(jī)||音箱|+-------+7.6.4行子查詢

先定一個(gè)小目標(biāo)！掌握行子查詢，能夠通過(guò)行子查詢的方式查詢數(shù)據(jù)7.6.4行子查詢行子查詢：返回的結(jié)果是一條包含多個(gè)字段的記錄時(shí)，即一行多列。語(yǔ)法格式WHERE(指定字段名1,指定字段名2,…)=(SELECT字段列表FROM數(shù)據(jù)源);利用比較運(yùn)算函數(shù)=表示行子查詢返回的一條記錄與指定的條件進(jìn)行比較7.6.4行子查詢不同運(yùn)算符的行比較含義：不同運(yùn)算符的行比較描述(a,b)=(x,y)表達(dá)的含義等價(jià)于(a=x)AND(b=y)(a,b)<=>(x,y)表達(dá)的含義等價(jià)于(a<=>x)AND(b<=>y)(a,b)<>(x,y)或(a,b)!=(x,y)表達(dá)的含義等價(jià)于(a<>x)OR(b<>y)(a,b)>(x,y)表達(dá)的含義等價(jià)于(a>x)OR((a=x)AND(b>y))(a,b)>=(x,y)表達(dá)的含義等價(jià)于(a>x)OR((a=x)AND(b>=y))(a,b)<(x,y)表達(dá)的含義等價(jià)于(a<x)OR((a=x)AND(b<y))(a,b)<=(x,y)表達(dá)的含義等價(jià)于(a<x)OR((a=x)AND(b<=y))7.6.4行子查詢案例：行子查詢使用代碼示例mysql>SELECT`id`,`name`,`price`,`score`,`content`FROM`my_goods`->WHERE(`price`,`score`)=(SELECTMAX(`price`),MIN(`score`)->FROM`my_goods`);+----+----------+---------+-------+---------+|id|name|price|score|content|+----+----------+---------+-------+---------+|4|液晶顯示器|5999.00|2.50|畫(huà)質(zhì)清晰|+----+----------+---------+-------+---------+7.6.5表子查詢

先定一個(gè)小目標(biāo)！掌握表子查詢，能夠通過(guò)表子查詢的方式查詢數(shù)據(jù)7.6.5表子查詢表子查詢：子查詢的返回結(jié)果用于FROM數(shù)據(jù)源，它是一個(gè)符合二維表結(jié)構(gòu)的數(shù)據(jù)，可以是一行一列、一列多行、一行多列或多行多列。語(yǔ)法格式SELECT字段列表FROM(SELECT語(yǔ)句)[AS]別名[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);7.6.5表子查詢案例：表子查詢使用代碼示例mysql>SELECTa.`id`,a.`name`,a.`price`,a.`category_id`->FROM`my_goods`a,->(SELECT`category_id`,MAX(`price`)max_priceFROM`my_goods`->GROUPBY`category_id`)b->WHEREa.`category_id`=b.`category_id`ANDa.`price`=b.`max_price`;7.6.6子查詢關(guān)鍵字

先定一個(gè)小目標(biāo)！掌握子查詢關(guān)鍵字，能夠利用EXISTS關(guān)鍵字判斷子查詢語(yǔ)句是否有返回結(jié)果7.6.6子查詢關(guān)鍵字EXISTS關(guān)鍵字：判斷子查詢的結(jié)果，返回兩個(gè)結(jié)果值：0表示不成立，1表示成立。語(yǔ)法格式WHEREEXISTS(子查詢語(yǔ)句);7.6.6子查詢關(guān)鍵字案例：子查詢關(guān)鍵字使用代碼示例mysql>UPDATE`my_goods`SET`name`='電飯煲',`price`='599',->`category_id`=(SELECT`id`FROM`my_goods_category`->WHERE`name`='廚具')->WHEREEXISTS(SELECT`id`FROM`my_goods_category`->WHERE`name`='廚具')->AND`id`=5;QueryOK,0rowsaffected(0.01sec)Rowmatched:0Changed:0Warnings:0子查詢無(wú)結(jié)果，則EXIST()的返回結(jié)果為0。UPDATE語(yǔ)句的更新條件不滿足，不會(huì)進(jìn)行更新。7.7外鍵7.7.1什么是外鍵

先定一個(gè)小目標(biāo)！熟悉什么是外鍵，能夠利用外鍵為多個(gè)數(shù)據(jù)表建立表關(guān)系7.7.1什么是外鍵外鍵：在一個(gè)表中引用另一個(gè)表中的一列或多列，被引用的列應(yīng)具有主鍵約束或唯一性約束，從而保證數(shù)據(jù)的一致性和完整性。被引用的表稱為主表；引用外鍵的表稱為從表。語(yǔ)法格式[CONSTRAINT外鍵名]FOREIGNKEY[索引名](當(dāng)前表字段名)REFERENCES

外部表(主鍵字段)7.7.1什么是外鍵案例：創(chuàng)建外鍵代碼示例mysql>CREATETABLE`majors`(->`id`INTPRIMARYKEYAUTO_INCREMENT,->`name`VARCHAR(32)NOTNULLUNIQUE->)DEFAULTCHARSET=utf8;創(chuàng)建主表。STEP017.7.1什么是外鍵案例：創(chuàng)建外鍵代碼示例mysql>CREATETABLE`student`(->`id`INTPRIMARYKEYAUTO_INCREMENT,->`name`VARCHAR(32)NOTNULL,->`mid`INTNOTNULL,->CONSTRAINT`m_id`FOREIGNKEY(`mid`)REFERENCESmajors(`id`)->)DEFAULTCHARSET=utf8;創(chuàng)建從表，添加外鍵約束。STEP027.7.1什么是外鍵案例：創(chuàng)建外鍵代碼示例mysql>DESC`student`;+-------+------------------+------+-----+---------+-----------------+|Field|Type|Null|Key|Default|Extra|+-------+------------------+------+-----+---------+-----------------+|id|int(10)unsigned|NO|PRI|NULL|auto_increment||name|varchar(32)|NO||NULL|||mid|int(10)unsigned|NO|MUL|NULL||+-------+------------------+------+-----+---------+-----------------+查看外鍵信息。STEP037.7.2添加外鍵約束

先定一個(gè)小目標(biāo)！掌握外鍵約束的添加，能夠利用外鍵約束確保數(shù)據(jù)表的完整性，能夠在更新數(shù)據(jù)和刪除數(shù)據(jù)時(shí)對(duì)外鍵進(jìn)行限制7.7.2添加外鍵約束外鍵約束：外鍵對(duì)主表（外鍵指向的表）和從表（外鍵所在的表）里的數(shù)據(jù)產(chǎn)生約束效果，主要針對(duì)數(shù)據(jù)的插入、更新和刪除操作。語(yǔ)法格式[CONSTRAINT外鍵名]FOREIGNKEY[索引名](當(dāng)前表字段名)REFERENCES外部表(主鍵字段)[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]注意：只有InnoDB存儲(chǔ)引擎支持外鍵約束，建立外鍵關(guān)系的兩個(gè)數(shù)據(jù)表的字段數(shù)據(jù)類型必須相似。如INT和TINYINT類型的字段可以建立外鍵關(guān)系，INT和CHAR類型的字段不可以建立外鍵關(guān)系。7.7.2添加外鍵約束外鍵約束參數(shù)說(shuō)明：參數(shù)名稱功能描述RESTRICT默認(rèn)值，拒絕主表刪除或修改外鍵關(guān)聯(lián)字段CASCADE主表中刪除或更新記錄時(shí)，同時(shí)自動(dòng)刪除或更新從表中對(duì)應(yīng)的記錄SETNULL主表中刪除或更新記錄時(shí)，使用NULL值替換從表中對(duì)應(yīng)的記錄（不適用與NOUNULL字段）NOACTION與默認(rèn)值RESTRICT相同，拒絕主表刪除或修改外鍵關(guān)聯(lián)字段SETDEFAULT設(shè)默認(rèn)值，但I(xiàn)nnoDB目前不支持7.7.2添加外鍵約束案例：外鍵約束代碼示例mysql>CREATETABLE`student`(->`id`INTPRIMARYKEYAUTO_INCREMENT,->`name`VARCHAR(