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

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

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

2李四

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

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

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

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

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

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

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

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

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

表示大于比較<

表示小于比較>=表示大于等于比較<=表示小于等于比較<>、!=表示不等于比較BRTWEEN…AND…比較數(shù)據(jù)是否在閉區(qū)間范圍內(nèi)，若在返回1，不在返回0（1/2）7.3.2運算符比較運算符運算符描述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運算符案例：比較運算符的使用代碼示例#查詢my_goods表中價格在2000到6000的商品，商品信息包括id、name和pricemysql>SELECT`id`,`name`,`price`FROM`my_goods`->WHERE`price`BETWEEN2000AND6000;+----+------------+---------+|id|name|price|+----+------------+---------+|4|液晶顯示器|5999.00||8|辦公計算機|2000.00|+----+------------+---------+7.3.2運算符邏輯運算符邏輯運算符：在條件表達式中用于邏輯判斷、可與比較運算符結(jié)合使用。邏輯運算符的結(jié)果值：1（TRUE，表示為真）、0（FALSE，表示為假）或NULL。運算符描述AND或&&邏輯與，若操作數(shù)全部為真，則結(jié)果為1，否則為0OR或||邏輯或，若操作數(shù)中只要有一個為真，則結(jié)果為1，否則為0NOT或!邏輯非，若操作數(shù)為0，則結(jié)果為1，否則為0XOR邏輯異或，若操作數(shù)一個為真，一個為假，則結(jié)果為1；若操作數(shù)全部為真或全部為假，則結(jié)果為07.3.2運算符案例：邏輯運算符的使用代碼示例#查詢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|智能手機|1999.00|+----+---------+---------+7.3.3分組

先定一個小目標！掌握分組操作，能夠在查詢時利用聚合函數(shù)進行分組、分組并排序、回溯統(tǒng)計、數(shù)據(jù)篩選7.3.3分組分組分組：在WHERE條件后面添加GROUPBY。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]GROUPBY字段名;語法格式注意：對數(shù)據(jù)分組后，SELECT獲取的字段列表只能是GROUPBY分組的字段或使用了聚合函數(shù)的非分組字段。7.3.3分組分組常用聚合函數(shù)：函數(shù)名描述COUNT()返回參數(shù)字段的數(shù)量，不統(tǒng)計為NULL的記錄SUM()返回參數(shù)字段之和AVG()返回參數(shù)字段的平均值MAX()返回參數(shù)字段的最大值MIN()返回參數(shù)字段的最小值GROUP_CONCAT()返回復合條件的參數(shù)字段值的連接字符串7.3.3分組分組使用聚合函數(shù)：使用MAX()獲取每個分類下商品的最高價格。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)計回溯統(tǒng)計：系統(tǒng)自動對分組字段向上進行一次新的統(tǒng)計并產(chǎn)生一個新的統(tǒng)計數(shù)據(jù)，該數(shù)據(jù)對應(yīng)的分組字段值為NULL。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]GROUPBY字段名1[,字段名2…]WITHROLLUP;語法格式7.3.3分組案例：單字段回溯統(tǒng)計代碼示例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|對分組的回溯統(tǒng)計+-------------+----------+7.3.3分組案例：多分組回溯統(tǒng)計代碼示例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分組排序分組排序：為指定字段進行升序或降序排序的功能，默認為升序。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]GROUPBY字段名[ASC|DESC];語法格式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|收腰風衣||12|4|液晶顯示器||10|8|辦公計算機||9|7|頭戴耳機||8|6|桌面音箱||6|5|智能手機||3|1,2,3|2B鉛筆,鋼筆,碳素筆|+-------------+------------------------+---------------------------+7.3.3分組HAVING子句篩選分組結(jié)果：對查詢的數(shù)據(jù)執(zhí)行分組操作時，可以利用HAVING根據(jù)條件進行數(shù)據(jù)篩選。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]GROUPBY字段名HAVING條件表達式;語法格式7.3.3分組HAVING子句HAVING和WHERE區(qū)別：WHERE操作是從數(shù)據(jù)表中獲取數(shù)據(jù)，將數(shù)據(jù)從磁盤存儲到內(nèi)存中，HAVING是對已存放到內(nèi)存中的數(shù)據(jù)進行操作。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排序

先定一個小目標！掌握排序操作，能夠?qū)⒉樵兘Y(jié)果按照升序或降序進行排列7.3.4排序STEP02SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]ORDERBY字段名1[,字段名2…][ASC|DESC];語法格式兩種排序方式：單字段排序和多字段排序。7.3.4排序案例：單字段排序代碼示例STEP02mysql>SELECT`id`,`name,`price`FROM`my_goods`ORDERBY`price`DESC;+----+------------+---------+|id|name|price|+----+------------+---------+|4|液晶顯示器|5999.00||8|辦公計算機|2000.00||5|智能手機|1999.00||9|收腰風衣|299.00||7|頭戴耳機|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|智能手機|1999.00||8|6|桌面音箱|69.00||9|7|頭戴耳機|109.00||10|8|辦公計算機|2000.00||12|4|液晶顯示器|5999.00||15|9|收腰風衣|299.00||16|10|薄毛衣|48.00|+-------------+----+----------+---------+7.3.4排序STEP02中文排序使用“CONVERT(字段名USINGgbk)”函數(shù)強制讓指定的字段按中文拼音順序排序。案例：代碼示例mysql>mysql>SELECT`id`,`name`FROM`my_goods`ORDERBYCONVERT(`name`USINGgbk)ASC;+----+------------+|id|name|+----+------------+|1|2B鉛筆||8|辦公計算機||10|薄毛衣||4|液晶顯示器|+----+------------+7.3.5限量

先定一個小目標！掌握限量操作，能夠通過LIMIT子句限制查詢結(jié)果返回的條數(shù)7.3.5限量限定記錄的數(shù)量：使用LIMIT關(guān)鍵字限定記錄的數(shù)量，也可以指定從哪一條記錄開始查詢。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]LIMIT[OFFSET,]記錄數(shù);語法格式記錄數(shù)：表示限定獲取的最大記錄數(shù)量可選項OFFSET：表示偏移量，設(shè)置從哪條記錄開始，默認第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|智能手機|1999.00|+----+------------+---------+7.4聯(lián)合查詢7.4.1什么是聯(lián)合查詢

先定一個小目標！熟悉什么是聯(lián)合查詢，能夠?qū)⒍鄠€SELECT查詢結(jié)果合并到一起返回7.4.1什么是聯(lián)合查詢聯(lián)合查詢：多表查詢的一種方式，在多個SELECT語句的查詢字段數(shù)相同時，合并多個查詢結(jié)果。語法格式SELECT…UNION[ALL|DISTINCT]SELECT…[UNION[ALL|DISTINCT]SELECT…];UNION：實現(xiàn)聯(lián)合查詢的關(guān)鍵字聯(lián)合查詢的選項：ALL表示保留所有查詢結(jié)果；DISTINCT默認值表示去除完全重復的記錄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|頭戴耳機|109.00||5|智能手機|電子產(chǎn)品|+----+---------+----------+7.4.2聯(lián)合查詢并排序

先定一個小目標！掌握聯(lián)合查詢并排序，能夠?qū)⒙?lián)合查詢后的結(jié)果按照升序或降序返回7.4.2聯(lián)合查詢并排序?qū)崿F(xiàn)方式：使用圓括號（）包裹SELECT語句，在SELECT語句內(nèi)或在聯(lián)合查詢的最后添加ORDERBY語句，在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交叉連接

先定一個小目標！掌握交叉連接查詢，能夠?qū)蓮埍磉M行交叉連接，返回查詢結(jié)果7.5.1交叉連接交叉連接：返回被連接的兩個表中所有數(shù)據(jù)行的乘積。表13個字段、4條記錄表25個字段、10條記錄交叉連接結(jié)果：3+5個字段、4×10條記錄7.5.1交叉連接語法格式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)連接

先定一個小目標！掌握內(nèi)連接查詢，能夠?qū)蓮埍磉M行內(nèi)連接，返回查詢結(jié)果7.5.2內(nèi)連接內(nèi)連接：根據(jù)匹配條件返回第1個表與第2個表所有匹配成功的記錄。結(jié)果：3條記錄7.5.2內(nèi)連接語法格式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外連接

先定一個小目標！掌握外連接查詢，能夠?qū)蓮埍磉M行左外連接或右外連接查詢，返回查詢結(jié)果7.5.3外連接結(jié)果：左表中的記錄和交集部分左外連接左外連接：也稱左連接，返回連接關(guān)鍵字（LEFTJOIN）左表的所有記錄和右表符合連接條件的記錄。左表右表7.5.3外連接語法格式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外連接語法格式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自然連接

先定一個小目標！掌握自然連接查詢，能夠?qū)蓮埍磉M行自然連接查詢，返回查詢結(jié)果7.5.4自然連接語法格式SELECT查詢字段FROM表1NATURALJOIN表2;自然連接（NATURALJOIN）可以自動尋找連接條件。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','頁面設(shè)計');創(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|李四|頁面設(shè)計|+------+----+------+----------+使用自然連接查詢。STEP037.5.5USING關(guān)鍵字

先定一個小目標！掌握USING關(guān)鍵字的使用，能夠使用USING關(guān)鍵字代替ON來設(shè)置連接使用的字段7.5.5USING關(guān)鍵字語法格式SELECT查詢字段FROM表1[CROSS|INNER|LEFT|RIGHT]JOIN表2USING(同名的連接字段列表);USING關(guān)鍵字的使用場景：使用連接查詢時如果數(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子查詢分類

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

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

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

先定一個小目標！掌握行子查詢，能夠通過行子查詢的方式查詢數(shù)據(jù)7.6.4行子查詢行子查詢：返回的結(jié)果是一條包含多個字段的記錄時，即一行多列。語法格式WHERE(指定字段名1,指定字段名2,…)=(SELECT字段列表FROM數(shù)據(jù)源);利用比較運算函數(shù)=表示行子查詢返回的一條記錄與指定的條件進行比較7.6.4行子查詢不同運算符的行比較含義：不同運算符的行比較描述(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.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|畫質(zhì)清晰|+----+----------+---------+-------+---------+7.6.5表子查詢

先定一個小目標！掌握表子查詢，能夠通過表子查詢的方式查詢數(shù)據(jù)7.6.5表子查詢表子查詢：子查詢的返回結(jié)果用于FROM數(shù)據(jù)源，它是一個符合二維表結(jié)構(gòu)的數(shù)據(jù)，可以是一行一列、一列多行、一行多列或多行多列。語法格式SELECT字段列表FROM(SELECT語句)[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)鍵字

先定一個小目標！掌握子查詢關(guān)鍵字，能夠利用EXISTS關(guān)鍵字判斷子查詢語句是否有返回結(jié)果7.6.6子查詢關(guān)鍵字EXISTS關(guān)鍵字：判斷子查詢的結(jié)果，返回兩個結(jié)果值：0表示不成立，1表示成立。語法格式WHEREEXISTS(子查詢語句);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子查詢無結(jié)果，則EXIST()的返回結(jié)果為0。UPDATE語句的更新條件不滿足，不會進行更新。7.7外鍵7.7.1什么是外鍵

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

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