《PHP+MySQL動態(tài)網站開發(fā)》課件 第7章 MySQL多表操作

第7章MySQL多表操作《PHP+MySQL動態(tài)網站開發(fā)》學習目標/Target

熟悉數據表的聯系，能夠說出一對一、一對多和多對多聯系的區(qū)別。

熟悉數據庫設計范式，能夠運用范式合理設計數據庫。

掌握去除查詢結果中的重復數據的方法，能夠利用DISTINCT實現去重查詢。

掌握運算符的用法，能夠在SQL語句中使用運算符查詢數據。學習目標/Target

掌握分組、排序和限量的使用，能夠對查詢結果實現分組、排序和限量操作。

掌握聯合查詢的使用，能夠根據不同場景靈活使用聯合查詢。

掌握聚合函數的用法，能夠用聚合函數統計數據。

掌握連接查詢操作，能夠根據不同場景使用交叉連接查詢、內連接查詢和外連

接查詢。學習目標/Target

掌握子查詢的使用，能夠根據不同的需求使用標量子查詢、列子查詢、行子查詢、

表子查詢和EXISTS子查詢。

熟悉外鍵約束的概念，能夠說明外鍵約束的作用。

掌握數據表中外鍵約束的使用，能夠正確添加、刪除外鍵約束，并完成關聯表中

數據的添加、更新和刪除操作。

熟悉子查詢的概念，能夠區(qū)分每種子查詢的作用。章節(jié)概述/Summary在前面的章節(jié)中，已經講解了MySQL的基礎知識和查詢語法，然而在實際開發(fā)中，業(yè)務邏輯較為復雜，通常都需要對多張表進行關聯操作，才能滿足需求。本章將講解數據表的聯系、數據庫設計范式、數據進階操作、聯合查詢、連接查詢，以及子查詢和外鍵約束的使用。目錄/Contents7.17.27.3數據表的聯系數據庫設計范式數據進階操作7.4聯合查詢目錄/Contents7.57.67.7連接查詢子查詢外鍵約束數據表的聯系7.1

先定一個小目標！熟悉數據表的一對一聯系，能夠說出一對一聯系的概念。7.1.1一對一一對一：即一張數據表中的一條數據只與另外一張表中的某一條數據對應。實現一對一的聯系：將一張數據表拆分成兩張表，即將頻繁使用的字段和不常用的字段進行垂直分割，使用相同的主鍵對應。學生信息表示例學號姓名性別年齡身高體重籍貫民族1張三男20175140河北漢族2李四女21168100山東漢族3王五男22170130陜西漢族姓名、性別和年齡為頻繁使用字段，其他字段是不常用字段。7.1.1一對一根據一對一聯系拆分學生信息表學號姓名性別年齡1張三男202李四女213王五男22學號身高體重籍貫民族1175140河北漢族2168100山東漢族3170130陜西漢族學生表（1）：學生表（2）：7.1.1一對一7.1.2一對多

先定一個小目標！熟悉數據表的一對多聯系，能夠說出一對多聯系的概念。7.1.2一對多一對多：一張數據表中的一條數據與另外一張表中的多條數據對應。反過來是多對一。一對多設計數據表示例班級編號班級名班主任1軟件班張老師2設計班王老師學號姓名性別班級編號1張三男12李四女13王五男2班級表學生表7.1.3多對多

先定一個小目標！熟悉數據表的多對多聯系，能夠說出多對多聯系的概念。7.1.3多對多多對多：一張數據表中的多條數據與另外一張表中的多條數據對應。需要借助第三張表，將多對多聯系變成多個多對一的聯系。多對多設計數據表示例課程編號課程名1計算機2數據庫課程表學號姓名性別1張三男2李四女3王五男編號學號課程編號111221312432學生表學生選課表數據庫設計范式7.2

先定一個小目標！熟悉第一范式，能夠合理運用第一范式設計數據表。7.2.1第一范式第一范式（1NF）是指數據表的每一列都是不可分割的基本數據項，同一列中不能有多個值，即實體中的某個屬性不能有多個值，或不能有重復的屬性。第一范式遵從原子性，屬性不可再分。7.2.1第一范式不滿足第一范式的情況：將用戶信息和聯系方式信息保存在一張數據表中。編號聯系方式1張三郵箱：zhangsan@example.test，手機號：189000000002李四郵箱：lisi@example.test，手機號17300000000編號姓名郵箱手機號手機號1張三zhangsan/p>

2李四lisi@15900000000173000000007.2.1第一范式聯系方式包含了多個值，可以細分有兩個手機號屬性為了滿足第一范式，應將用戶及其聯系方式分成兩個表保存，兩個表之間存在一對多的聯系。7.2.1第一范式滿足第一范式的情況：用戶編號用戶名1張三2李四編號用戶編號聯系方式具體值11郵箱zhangsan@example.test21手機號1890000000032郵箱lisi@example.test42手機號1590000000052手機戶表聯系方式表7.2.1第一范式

先定一個小目標！熟悉第二范式，能夠合理運用第二范式設計數據表。7.2.2第二范式第二范式（2NF）是在第一范式的基礎上建立起來的，即滿足第二范式之前必須滿足第一范式。第二范式要求實體的屬性完全依賴主鍵，對于復合主鍵而言，不能僅依賴主鍵的一部分。第二范式遵從唯一性，非主鍵字段需完全依賴主鍵。7.2.2第二范式不滿足第二范式的情況：訂單編號訂單商品購買件數下單時間1鉛筆32023-01-2008:30:152鋼筆22023-01-2109:00:153圓珠筆12023-01-2209:30:15用戶編號訂單編號用戶名付款狀態(tài)11張三已支付12張三未支付23李四已支付訂單表用戶表7.2.2第二范式用戶編號和訂單編號組成了復合主鍵，付款狀態(tài)完全依賴該復合主鍵，而用戶名只依賴用戶編號采用上述方式設計的用戶表存在以下問題：插入異常：如果一個用戶沒有下過訂單，則該用戶無法插入。刪除異常：如果刪除一個用戶所有的訂單，則該用戶會被刪除。更新異常：由于用戶名冗余，修改一個用戶時需要修改多條數據。如果稍有不慎，漏改某些數據，會出現更新異常。7.2.2第二范式為了滿足第二范式，將復合主鍵移動到訂單表中。7.2.2第二范式滿足第二范式的情況：用戶編號用戶名1張三2李四訂單編號用戶編號訂單商品購買件數下單時間付款狀態(tài)11鉛筆32023-01-2008:30:15已支付21鋼筆22023-01-2109:00:15未支付32圓珠筆12023-01-2209:30:15已支付用戶表訂單表7.2.2第二范式

先定一個小目標！熟悉第三范式，能夠合理運用第三范式設計數據表。7.2.3第三范式第三范式（3NF）是在第二范式的基礎上建立起來的，即滿足第三范式之前必須滿足第二范式。第三范式要求一個數據表中每一列數據都與主鍵直接相關，而不能間接相關。第三范式要求非主鍵字段不能相互依賴。7.2.3第三范式不滿足第三范式的情況：用戶編號用戶名用戶等級享受折扣1張三10.952李四10.953王五20.857.2.3第三范式用戶享受折扣與用戶等級相關，兩者存在依賴采用上述方式設計的用戶表存在以下問題：插入異常：如果新插入用戶的等級在1、2之外，其享受的折扣無從參考。刪除異常：如果刪除某個等級下所有的用戶，該等級對應的折扣也被刪除。更新異常：如果修改某個用戶的等級，該用戶享受的折扣必須隨之修改；如果修改某個等級對應的折扣，因為折扣存在冗余，容易出現漏改的情況。7.2.3第三范式為了滿足第三范式，將用戶等級與享受折扣拆分到單獨的折扣表中。7.2.3第三范式滿足第三范式的情況：用戶編號用戶名用戶等級1張三12李四13王五2用戶等級享受折扣10.9520.85用戶表折扣表7.2.3第三范式

先定一個小目標！熟悉逆規(guī)范化，能夠合理運用數據庫設計范式設計數據表。7.2.4逆規(guī)范化逆規(guī)范化是一種反范式的設計，其目的主要是提高查詢效率。范式雖然減少了數據冗余，但是增加了表的數量，這會使查詢變得復雜，尤其是在連接多張表查詢數據時，會使查詢性能降低。7.2.4逆規(guī)范化逆規(guī)范化設計商品表和訂單表：商品編號商品名稱商品價格商品銷量1鉛筆252鋼筆8913圓珠筆101訂單編號商品編號購買件數下單時間1132023-01-2008:30:152122023-01-2109:00:153212023-01-2209:30:154312023-01-2309:59:15商品表訂單表7.2.4逆規(guī)范化數據進階操作7.3

先定一個小目標！7.3.1去除查詢結果中的重復數據掌握去除重復數據的語法，能夠對查詢結果去重。使用SELECT語句的查詢選項DISTINCT可以實現去重查詢。帶有查詢選項的SELECT語句的語法格式如下：SELECT[查詢選項]字段名[,...]FROM數據表名稱;查詢選項為可選項，取值為ALL或DISTINCT。ALL為默認值，表示保留所有查詢到的數據。DISTINCT表示去除重復數據，只保留一條數據。7.3.1去除查詢結果中的重復數據注意：當查詢的字段有多個時，只有所有字段的值完全相同，才會被認為是重復數據。7.3.1去除查詢結果中的重復數據演示普通查詢和使用查詢選項DISTINCT的查詢對比：SELECTkeywordFROMmy_goods;+----------+|keyword|+----------+|文具||文具||文具||電子產品||電子產品|……省略部分數據|服裝||服裝|+----------+SELECTDISTINCTkeywordFROMmy_goods;+----------+|keyword|+----------+|文具

||電子產品||服裝

|+----------+7.3.1去除查詢結果中的重復數據

先定一個小目標！掌握運算符的用法，能夠在SQL語句中使用運算符查詢數據。7.3.2運算符1.比較運算符比較運算符通常用于對數據進行限定。比較運算符的比較結果有3種，分別為1、0和NULL，其中1表示TRUE（真），0表示FALSE（假），NULL表示未知。常用的比較運算符：運算符描述=運算符左右兩側的操作數相等<=>作用與“=”的類似，但它可以進行NULL值比較>

運算符左側操作數大于右側操作數<

運算符左側操作數小于右側操作數>=運算符左側操作數大于或等于右側操作數<=運算符左側操作數小于或等于右側操作數<>或!=運算符左右兩側的操作數不相等1/27.3.2運算符運算符描述BETWEEN…AND…數據在某個范圍內（含最小值和最大值）NOTBETWEEN…AND…數據不在某個范圍內（含最小值和最大值）IS判斷一個數據是TRUE、FALSE或NULL，若是則返回1，否則返回0ISNOT判斷一個數據不是TRUE、FALSE或NULL，若不是返回1，否則返回0ISNULL判斷一個數據是NULL，若是則返回1，否則返回0ISNOTNULL判斷一個數據不是NULL，若不是則返回1，否則返回0LIKE獲取匹配到的數據，模糊匹配NOTLIKE獲取匹配不到的數據，模糊匹配REGEXP獲取正則表達式匹配查詢的數據2/27.3.2運算符使用BETWEEN...AND...獲取my_goods表中價格在2000~6000范圍內的商品的信息：mysql>SELECTid,name,priceFROMmy_goodsWHEREpriceBETWEEN2000AND6000;+----+-------------------------+---------+|id|name|price|+----+-------------------------+---------+|4|超薄筆記本Pro12

|5999.00||8|辦公計算機天逸510Pro|2000.00|+----+-------------------------+---------+7.3.2運算符2.邏輯運算符邏輯運算符通常用于邏輯判斷，它經常與比較運算符結合使用。邏輯判斷的結果有3種，分別為1、0或NULL，其中1表示TRUE（真），0表示FALSE（假），NULL表示未知。常用的邏輯運算符：運算符描述AND或&&邏輯與，若操作數全部為TRUE，則結果為1，否則結果為0OR或||邏輯或，操作數中若有一個為TRUE，則結果為1；若都不為TRUE，則結果為0NOT或!邏輯非，返回和操作數相反的結果XOR邏輯異或，若操作數一個為TRUE，一個為FALSE，則結果為1；若操作數全部為TRUE或全部為FALSE，則結果為07.3.2運算符使用AND運算符查詢my_goods表中關鍵詞為“電子產品”且評分為5的商品：mysql>SELECTid,name,priceFROMmy_goods->WHEREkeyword='電子產品'ANDscore=5;+----+------------------+---------+|id|name|price|+----+------------------+---------+|5|華為P50智能手機|1999.00|+----+------------------+---------+7.3.2運算符

先定一個小目標！掌握聚合函數的用法，能夠用聚合函數統計數據。7.3.3聚合函數MySQL提供的聚合函數可用來統計數據。例如，獲取每個商品分類的商品數量和平均價格、商品的最高價格和最低價格等。聚合函數用于完成聚合操作。聚合操作是指對一組值進行運算，獲得一個運算結果。7.3.3聚合函數常用的聚合函數：聚合函數功能描述COUNT()用于統計查詢的總記錄數，參數可以是字段名或者*SUM()用于對指定字段中的值進行累加AVG()用于計算某一列數值的平均值MAX()用于查詢某一列數值中的最大值MIN()用于查詢某一列數值中的最小值GROUP_CONCAT()使用指定分隔符將某一列的值連接成字符串JSON_ARRAYAGG()將結果集作為單個JSON數組返回JSON_OBJECTAGG()將結果集作為單個JSON對象返回7.3.3聚合函數1.COUNT()函數COUNT()函數用于統計查詢的總記錄數，使用COUNT()函數查詢數據的語法格式：SELECTCOUNT(*|字段名)FROM數據表名稱;SELECTSUM(字段名)FROM數據表名稱;2.SUM()函數SUM()函數用于對指定字段中的值進行累加，并且在數據累加時會忽略字段中的NULL值。使用SUM()函數查詢數據的語法格式：7.3.3聚合函數如果想要統計的字段中包含NULL值時，可以先借助IFNULL()函數，將NULL值轉換為0再進行計算。語法格式如下：3.AVG()函數AVG()函數用于計算某一列數值的平均值，并且在計算時會忽略字段中的NULL值，即只對非NULL的數值進行累加，然后用累加和除以非NULL的行數計算出平均值。使用AVG()函數查詢數據的基本語法格式：SELECTAVG(字段名)FROM數據表名稱;SELECTAVG(IFNULL(sal,0))FROM數據表名稱;7.3.3聚合函數MIN()函數用于查詢某一列數值中的最小值，基本語法格式：4.MAX()函數和MIN()函數MAX()函數用于查詢某一列數值中的最大值，基本語法格式：SELECTMAX(字段名)FROM數據表名稱;SELECTMIN(字段名)FROM數據表名稱;使用聚合函數單獨獲取my_goods表中商品最高和最低的價格：SELECTMAX(price),MIN(price)FROMmy_goods;7.3.3聚合函數5.GROUP_CONCAT()函數GROUP_CONCAT()函數使用指定分隔符將某一列的值連接成字符串，通常用于將分組查詢的結果進行字符串拼接。使用GROUP_CONCAT()函數查詢數據的語法格式：SELECTGROUP_CONCAT(字段名[ORDERBY字段名][SEPARATOR分隔符])FROM數據表名稱;7.3.3聚合函數6.JSON_ARRAYAGG()函數和JSON_OBJECTAGG()函數JSON_ARRAYAGG()函數的參數可以是一個字段或表達式，返回值為一個JSON數組；JSON_OBJECTAGG()函數將兩個字段名或表達式作為參數，基本語法格式如下。其中參數1表示“鍵”，參數2表示“鍵”對應的值，并返回一個包含鍵值對的JSON對象。將id字段的結果集作為JSON數組返回，將id和name字段作為JSON對象返回：SELECTJSON_ARRAYAGG(id)AS'[編號]',JSON_OBJECTAGG(id,name)AS'{編號：名稱}'FROMmy_goods\G7.3.3聚合函數SELECTJSON_ARRAYAGG(參數1,參數2)FROM數據表名稱;多學一招：在查詢中使用別名為字段設置別名，只需在字段名后面添加“AS別名”即可。為字段設置別名的語法格式：SELECT字段名1[AS]字段別名1,字段名2[AS]字段別名2,...FROM數據表名稱;在查詢中使用別名獲取分類id為3或6的商品的最低價格：SELECTcategory_idcid,MIN(price)min_priceFROMmy_goodsGROUPBYcidHAVINGcid=3ORcid=6;多學一招：在查詢中使用別名為數據表設置別名的基本語法格式：SELECT數據表別名.字段名[,...]FROM數據表名稱[AS]數據表別名;在查詢中使用別名為商品表設置別名：SELECTg.category_idcid,MIN(price)min_priceFROMmy_goodsgGROUPBYcidHAVINGcid=3ORcid=6;

先定一個小目標！掌握分組的使用，能夠對查詢結果進行分組操作。7.3.4分組1.分組查詢在查詢數據時，在WHERE子句后面添加GROUPBY即可根據指定的字段進行分組。分組的語法格式：SELECT[查詢選項]*|{字段名[,...]}FROM數據表名稱[WHERE條件表達式]GROUPBY字段名[,...];對my_goods數據表中的keyword字段進行分組：SELECTkeywordFROMmy_goodsGROUPBYkeyword;通過聚合函數MAX()獲取每個“分類id”下商品的最高價格：SELECTcategory_id,MAX(price)FROMmy_goodsGROUPBYcategory_id;7.3.4分組2.回溯統計回溯統計用于對數據進行分析，當進行分組查詢后，MySQL會自動對分組的字段進行一次新的統計，并產生一個新的統計數據，該數據對應的分組字段值為NULL?；厮萁y計的語法格式：SELECT[查詢選項]*|{字段名[,…]}FROM數據表名稱[WHERE條件表達式]GROUPBY字段名[,…]WITHROLLUP;統計my_goods表中每個“分類id”下的商品數量，并對統計的結果進行回溯統計：SELECTcategory_id,COUNT(*)FROMmy_goodsGROUPBYcategory_idWITHROLLUP;對多個分組進行回溯統計：SELECTscore,comment_count,COUNT(*)FROMmy_goodsGROUPBYscore,comment_countWITHROLLUP;7.3.4分組3.分組后進行條件篩選HAVING是MySQL中用于對分組結果進行條件篩選的關鍵字，它通常與GROUPBY一起使用。GROUPBY結合HAVING查詢的語法格式：SELECT[查詢選項]*|{字段名[,…]}FROM數據表名稱[WHERE條件表達式]GROUPBY字段名[,…]HAVING條件表達式;HAVING和WHERE在實際使用時的區(qū)別：WHERE操作是從數據表中獲取數據，將數據從磁盤存儲到內存中，而HAVING是對已存放到內存中的數據進行操作。HAVING位于GROUPBY子句后，而WHERE位于GROUPBY子句之前。HAVING后面可以使用聚合函數，而WHERE后面不可以使用聚合函數。7.3.4分組根據評分字段score和評論數字段comment_count進行分組統計，獲取分組后含有兩件商品的商品id：mysql>SELECTscore,comment_count,GROUP_CONCAT(id)

->FROMmy_goodsGROUPBYscore,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分組

先定一個小目標！掌握排序的使用，能夠對查詢結果實現排序操作。7.3.5排序通過ORDERBY實現排序查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱ORDERBY字段名1[ASC|DESC][,字段名2[ASC|DESC]...];如果不指定排序方式，默認按照ASC（升序）方式進行排序。7.3.5排序查詢my_goods表，將商品分類category_id字段的值升序排序，再按照price字段的值降序排序：mysql>SELECTcategory_id,id,name,priceFROMmy_goods->ORDERBYcategory_id,priceDESC;+-------------+----------+-----------------+----------+|category_id|id|name|price

|+-------------+----------+-----------------+----------+|

3|

2|鋼筆T1616

|15.00||

3|

3|碳素筆GP1008

|1.00||

3|1|2H鉛筆S30804

|

0.50|省略部分數據……|15|9|收腰風衣中長款|299.00||

16|10|薄毛衣聯名款|48.00|+-------------+----------+-----------------+----------+7.3.5排序當使用utf8mb4字符集時，如果排序字段的值為中文，默認不會按照中文拼音首字母的順序排序。若要強制字段按中文拼音首字母的順序排序，可以使用CONVERT(字段名USINGgbk)函數將字段的字符集指定為gbk。按照商品名稱的中文拼音首字母的順序排序：mysql>SELECTid,nameFROMmy_goodsORDERBYCONVERT(nameUSINGgbk)ASC;+----+-------------------------+|id|name

|+----+-------------------------+|1|2H鉛筆S30804

||8|辦公計算機天逸510Pro

|省略部分數據……|6|桌面音箱BMS10

|+----+------------------------+按照中文拼音排序多學一招：按照中文拼音排序

先定一個小目標！7.3.6限量掌握限量的使用，能夠對查詢結果實現限量操作。在SELECT語句中使用LIMIT的基本語法格式：SELECT[查詢選項]*|{字段名[,...]}FROM數據表名稱[WHERE條件表達式]LIMIT[OFFSET,]記錄數;OFFSET為可選項，如果不指定OFFSET的值，默認值為0，表示從第一條數據開始獲取。OFFSET值為1則從第二條數據開始獲取，以此類推。記錄數表示查詢結果中的最大條數限制。7.3.6限量查詢my_goods數據表中價格最高的一件商品：SELECTid,name,priceFROMmy_goodsORDERBYpriceDESCLIMIT1;查詢my_goods數據表中從第一條數據開始的5條數據：SELECTid,name,priceFROMmy_goodsLIMIT0,5;7.3.6限量聯合查詢7.4

先定一個小目標！掌握聯合查詢的使用，能夠根據不同場景靈活使用聯合查詢。7.4.1聯合查詢概述聯合查詢是一種多表查詢方式，它在保證多個SELECT語句的查詢字段數相同的情況下，合并多個查詢的結果。聯合查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱1...UNION[ALL|DISTINCT]SELECT*|{字段名[,...]}FROM數據表名稱2...;ALL關鍵字表示保留所有的查詢結果。DISTINCT關鍵字為默認值，表示去除查詢結果中完全重復的數據。7.4.1聯合查詢概述注意：參與聯合查詢的SELECT語句的字段數量必須一致，聯合查詢結果中的列來源于第一條SELECT語句的字段。即使UNION后的SELECT語句查詢的字段與第一條SELECT語句查詢的字段的表達含義或數據類型不同，MySQL也僅會根據第一條SELECT語句查詢的字段出現的順序，對結果進行合并。7.4.1聯合查詢概述以聯合查詢的方式獲取category_id為9的商品的id、name和price字段，以及category_id為6的商品的id、name和keyword字段：SELECTid,name,priceFROMmy_goodsWHEREcategory_id=9UNIONSELECTid,name,keywordFROMmy_goodsWHEREcategory_id=6;+----+---------------------+----------+|id

|name

|price

|+----+---------------------+----------+|

7|頭戴耳機StarY360|109.00

||5|華為P50智能手機|電子產品|+----+---------------------+----------+7.4.1聯合查詢概述查詢結果字段只有id、name和price

先定一個小目標！掌握聯合查詢并排序的使用，能夠對聯合查詢的結果排序。7.4.2聯合查詢并排序若要對聯合查詢的數據排序，需要使用括號“()”對每一條SELECT語句進行標識，在SELECT語句內或在聯合查詢的最后添加ORDERBY語句。要讓排序生效，必須要在ORDERBY后添加LIMIT限定聯合查詢返回結果集的數量。LIMIT后的記錄數根據實際需求進行設置：若設置的記錄數小于數據表記錄數，則會以設置的記錄數為準。若設置的記錄數大于或等于數據表記錄數，則以數據表記錄數為準。7.4.2聯合查詢并排序使用聯合查詢對my_goods表中category_id為3的商品按價格升序排序，category_id不為3的商品按價格降序排序：(SELECTid,name,priceFROMmy_goodsWHEREcategory_id<>3ORDERBYpriceDESCLIMIT7)UNION(SELECTid,name,priceFROMmy_goodsWHEREcategory_id=3ORDERBYpriceASCLIMIT3);7.4.2聯合查詢并排序連接查詢7.5

先定一個小目標！掌握交叉連接查詢操作，能夠使用交叉連接查詢數據。7.5.1交叉連接查詢什么是交叉連接查詢？7.5.1交叉連接查詢交叉連接查詢返回的結果是被連接的兩個數據表中所有數據行的乘積。例如，數據表A有3個字段、4條數據，數據表B有5個字段、10條數據，那么交叉連接后的結果是40（4×10）條數據，每條數據中有8（5+3）個字段。7.5.1交叉連接查詢交叉連接查詢的示意圖：7.5.1交叉連接查詢交叉連接的語法格式：交叉連接的簡寫語法：SELECT*|{字段名[,...]}FROM數據表名稱1CROSSJOIN數據表名稱2;SELECT*|{字段名[,...]}FROM數據表名稱1,數據表名稱2;7.5.1交叉連接查詢將商品分類表my_goods_category和商品表my_goods進行交叉連接查詢：SELECTc.idcid,cname,g.idgid,gnameFROMmy_goods_categoryAScCROSSJOINmy_goodsASg;7.5.1交叉連接查詢

先定一個小目標！掌握內連接查詢操作，能夠使用內連接查詢數據。7.5.2內連接查詢什么是內連接查詢？7.5.2內連接查詢內連接查詢是將一張數據表中的每一行數據按照指定條件與另外一張數據表進行匹配，如果匹配成功，則返回參與內連接查詢的兩張數據表中符合連接條件的數據，如果匹配失敗，則不保留數據。7.5.2內連接查詢內連接查詢示意圖：7.5.2內連接查詢內連接查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱1[INNER]JOIN數據表名稱2ON連接條件;將商品表my_goods和商品分類表my_goods_category進行內連接查詢：SELECTg.idgid,gname,c.idcid,cnameFROMmy_goodsgJOIN

my_goods_categorycON

g.category_id=c.id;7.5.2內連接查詢

先定一個小目標！掌握外連接查詢操作，能夠使用外連接查詢數據。7.5.3外連接查詢什么是外連接查詢？7.5.3外連接查詢內連接查詢的返回結果是符合連接條件的數據，然而有時除了要查詢出符合連接條件的數據外，還需要查詢出其中一張數據表中符合連接條件之外的其他數據，此時就需要使用外連接查詢。7.5.3外連接查詢外連接查詢的語法格式：SELECT數據表名稱.字段名[,...]FROM數據表名稱1LEFT|RIGHT[OUTER]JOIN數據表名稱2ON連接條件;數據表名稱1一般稱為左表，數據表名稱2一般稱為右表。7.5.3外連接查詢使用左外連接查詢和右外連接查詢的區(qū)別：左外連接查詢：返回左表中的所有數據和右表中符合連接條件的數據。右外連接查詢：返回右表中的所有數據和左表中符合連接條件的數據。外連接查詢左外連接（LEFTJOIN）查詢右外連接（RIGHTJOIN）查詢7.5.3外連接查詢1.左外連接查詢左外連接查詢是用左表的數據匹配右表的數據，查詢的結果包括左表中的所有數據，以及右表中符合連接條件的數據。如果左表的某條數據在右表中不存在，則右表中對應字段的值顯示為NULL。左外連接查詢示意圖：7.5.3外連接查詢將my_goods表作為查詢中的左表，查詢評分為5的商品名稱及對應的分類名稱：SELECTg.idgid,gname,c.idcid,cnameFROMmy_goodsgLEFTJOINmy_goods_categorycONg.category_id=c.idANDg.score=5;7.5.3外連接查詢2.右外連接查詢右外連接查詢是用右表的數據匹配左表的數據，查詢的結果包括右表中的所有數據，以及左表中符合連接條件的數據。如果右表的某條數據在左表中不存在，則左表中對應字段的值顯示為NULL。右外連接查詢示意圖：7.5.3外連接查詢使用右外連接查詢，將my_goods_category表作為查詢中的右表，查詢評分為5的商品對應分類名稱：SELECTg.idgid,gname,c.idcid,cnameFROMmy_goodsgRIGHTJOINmy_goods_categorycONc.id=g.category_idANDg.score=5;7.5.3外連接查詢多學一招：USING關鍵字USING關鍵字使用連接查詢時，如果數據表連接的字段同名，則連接時的匹配條件可以使用USING關鍵字。USING關鍵字的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱1[CROSS|INNER|LEFT|RIGHT]JOIN數據表名稱2USING(同名的連接字段列表);使用USING關鍵字查詢鋼筆T1616所在的分類下有哪些商品：SELECTDISTINCTg1.id,FROMmy_goodsg1JOINmy_goodsg2USING(category_id)WHERE='鋼筆T1616';子查詢7.6

先定一個小目標！掌握子查詢的使用，能夠根據不同的需求使用標量子查詢、列子查詢、行子查詢、表子查詢和EXISTS子查詢。7.6.1子查詢的分類子查詢的實際操作是將一條查詢語句嵌套到另一條查詢語句中作為一個條件，以便更準確地篩選出需要的數據，例如，在SQL語句A（A可以是SELECT語句、INSERT語句、UPDATE語句或DELETE語句）中嵌入查詢語句B，將查詢語句B作為執(zhí)行所需的條件或查詢的數據源。7.6.1子查詢的分類子查詢標量子查詢EXISTS子查詢列子查詢行子查詢表子查詢7.6.1子查詢的分類1.標量子查詢標量子查詢是指子查詢返回的結果為單個數據，即一行一列數據。標量子查詢位于WHERE之后，通常與運算符=、<>、>、>=、<、<=結合使用。在SELECT語句中使用標量子查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱WHERE字段名{=|<>|>|>=|<|<=}(SELECT字段名FROM數據表名稱[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);7.6.1子查詢的分類利用標量子查詢的方式，從my_goods_category表中獲取商品名稱為鋼筆T1616的分類名稱：SELECTnameFROMmy_goods_categoryWHEREid=(SELECTcategory_idFROMmy_goodsWHEREname='鋼筆T1616');7.6.1子查詢的分類2.列子查詢列子查詢是一種返回結果為一列多行數據的子查詢。列子查詢位于WHERE之后，通常與運算符IN、NOTIN結合使用，其中，IN表示指定的條件是否在子查詢返回的結果集中；NOTIN表示指定的條件是否不在子查詢返回的結果集中。在SELECT語句中使用列子查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱WHERE字段名{IN|NOTIN}(SELECT字段名FROM數據表名稱[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);7.6.1子查詢的分類利用列子查詢的方式，從my_goods_category表中獲取添加了商品的商品分類的名稱：①先通過子查詢返回category_id的值。②使用IN關鍵字根據category_id的值查詢商品分類名稱的信息。SELECTnameFROMmy_goods_categoryWHEREidIN(SELECTDISTINCTcategory_idFROMmy_goods);7.6.1子查詢的分類3.行子查詢行子查詢是一種返回結果為一行多列數據的子查詢，位于WHERE之后，通常與比較運算符、IN和NOTIN結合使用。在SELECT語句中使用行子查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱WHERE(字段名1[,…]){比較運算符|IN|NOTIN}(SELECT字段名[,…]FROM數據表名稱[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);7.6.1子查詢的分類不同運算符的行比較：不同運算符的行比較邏輯關系等價于(a,b)=(x,y)(a=x)AND(b=y)(a,b)<=>(x,y)(a<=>x)AND(b<=>y)(a,b)<>(x,y)或(a,b)!=(x,y)(a<>x)OR(b<>y)(a,b)>(x,y)(a>x)OR((a=x)AND(b>y))(a,b)>=(x,y)(a>x)OR((a=x)AND(b>=y))(a,b)<(x,y)(a<x)OR((a=x)AND(b<y))(a,b)<=(x,y)(a<x)OR((a=x)AND(b<=y))7.6.1子查詢的分類利用行子查詢的方式從my_goods表中獲取價格最高且評分最低的商品信息：先通過子查詢返回price最高且score最低的商品的price和score的值；根據返回的值篩選出對應的商品信息。SELECTid,name,price,score,contentFROMmy_goodsWHERE(price,score)=(SELECTMAX(price),MIN(score)FROMmy_goods);7.6.1子查詢的分類4.表子查詢表子查詢是一種返回結果為多行多列數據的子查詢，其返回結果可以是一行一列、一列多行、一行多列或多行多列。表子查詢多位于FROM關鍵字之后。在SELECT語句中使用表子查詢的語法格式：SELECT*|{字段名[,...]}FROM(表子查詢)[AS]別名[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT];從my_goods表中獲取每個商品分類下價格最高的商品信息：SELECTa.id,,a.price,a.category_idFROMmy_goodsa,(SELECTcategory_id,MAX(price)max_priceFROMmy_goodsGROUPBYcategory_id)bWHEREa.category_id=b.category_idANDa.price=b.max_price;7.6.1子查詢的分類5.EXISTS子查詢EXISTS子查詢用于判斷子查詢語句是否有返回的結果，若有結果則返回1；否則返回0。EXISTS子查詢位于WHERE之后。在SELECT語句中使用EXISTS子查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱WHEREEXISTS(SELECT*FROM數據表名稱[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);7.6.1子查詢的分類如果my_goods_category表中存在名稱為“廚具”的分類，則將my_goods表中id等于5的商品名稱修改為電飯煲，將其價格修改為400，將其分類修改為廚具對應的id。UPDATEmy_goodsSETname='電飯煲',price=400,category_id=(SELECTidFROMmy_goods_categoryWHEREname='廚具')WHEREEXISTS(SELECTidFROMmy_goods_categoryWHEREname='廚具')ANDid=5;7.6.1子查詢的分類

先定一個小目標！掌握子查詢關鍵字的使用，能夠使用ANY和ALL關鍵字與子查詢結合使用。7.6.2子查詢關鍵字在子查詢中，不僅可以使用比較運算符，還可以使用MySQL提供的一些特定關鍵字，常用的子查詢關鍵字還有ANY和ALL。帶ANY、ALL關鍵字的子查詢不能使用運算符<=>。如果子查詢結果中某條數據的值為NULL，那么這條數據不參與匹配。7.6.2子查詢關鍵字1.ANY關鍵字結合子查詢ANY關鍵字表示“任意一個”，必須和比較運算符一起使用。ANY關鍵字結合子查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱WHERE字段名比較運算符ANY(SELECT字段名FROM數據表名稱[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);從my_goods_category表中獲取價格小于200的商品的分類名稱：SELECTnameFROMmy_goods_categoryWHEREid=ANY(SELECTDISTINCTcategory_idFROMmy_goodsWHEREprice<200);7.6.2子查詢關鍵字2.ALL關鍵字結合子查詢ALL關鍵字表示“所有”。ALL關鍵字結合子查詢時，表示與子查詢返回的所有值進行比較，只有全部符合ALL子查詢的結果時，才返回1，否則返回0。ALL關鍵字結合子查詢的語法格式：SELECT*|{字段名[,...]}FROM數據表名稱WHERE字段名比較運算符ALL(SELECT字段名FROM數據表名稱[WHERE][GROUPBY][HAVING][ORDERBY][LIMIT]);從my_goods表中獲取category_id為3且商品價格全部小于category_id為8的商品的價格的信息：SELECTid,name,price,keywordFROMmy_goodsWHEREcategory_id=3ANDprice<ALL(SELECTDISTINCTpriceFROMmy_goodsWHEREcategory_id=8);7.6.2子查詢關鍵字多學一招：SOME關鍵字SOME關鍵字SOME關鍵字和ANY關鍵字在語法含義上相同，但NOTSOME和NOTANY在語法含義上不同。前者僅用于否定部分內容，而后者用于否定全部內容，相當于NOTALL。外鍵約束7.7

先定一個小目標！熟悉外鍵約束的概念，能夠說明外鍵約束的作用。7.7.1外鍵約束概述外鍵約束是指在一張數據表中引用另一張數據表中的一列或多列，被引用的列應設置了主鍵約束或唯一性約束，從而保證數據的一致性和完整性。在使用了外鍵約束時，被引用的表稱為主表；外鍵所在的表稱為從表。7.7.1外鍵約束概述學生表student和專業(yè)表majors數據之間的關聯：7.7.1外鍵約束概述

先定一個小目標！掌握添加外鍵約束的語法，能夠正確添加外鍵約束。7.7.2添加外鍵約束外鍵約束可以在創(chuàng)建數據表時添加，也可以在修改數據表時添加，添加外鍵約束的語法格式：#創(chuàng)建數據表時添加外鍵約束CREATETABLE數據表名稱(字段名1數據類型,…[CONSTRAINT[外鍵約束名稱]]FOREIGNKEY(外鍵字段名)REFERENCES主表(主鍵字段名)[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]);#給已存在的數據表添加外鍵約束ALTERTABLE從數據表名稱ADD[CONSTRAINT[外鍵約束名稱]]FOREIGNKEY(外鍵字段名)REFERENCES主表(主鍵字段名)[ONDELETE{CASCADE|SETNULL|NOACTION|RESTRICT|SETDEFAULT}][ONUPDATE{CASCADE|SETNULL|NOACTION|RESTRICT|SETDEFAULT}];7.7.2添加外鍵約束ONDELETE與ONUPDATE用于設置主表中的數據被刪除或修改時，從表對應數據的處理辦法，ONDELETE與ONUPDATE的各參數的具體說明：參數說明RESTRICT默認值，拒絕主表刪除或更新外鍵關聯的字段CASCADE在主表中刪除或更新數據時，自動刪除或更新從表中對應的數據SETNULL在主表中刪除或更新數據時，使用NULL值替換從表中對應的數據（不適用于設置了非空約束的字段）NOACTION拒絕主表刪除或更新外鍵關聯的字段SETDEFAULT為字段設置默認值，但InnoDB存儲引擎目前不支持7.7.2添加外鍵約束創(chuàng)建student數據表，在創(chuàng)建時添加外鍵約束：CREATETABLEstudent(

idINTPRIMARYKEYAUTO_INCREMENT,

nameVARCHAR(32)NOTNULL,

midINTNOTNULL,

CONSTRAINTm_id

FOREIGNKEY(mid)REFERENCESmajors(id)

ONDELETERESTRICTONUPDATECASCADE);7.7.2添加外鍵約束創(chuàng)建maj