MySQL多表操作表關(guān)系與數(shù)據(jù)查詢

主講教師：魏巍巍北京信息職業(yè)技術(shù)學(xué)院數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用表關(guān)系與數(shù)據(jù)查詢創(chuàng)建采集任務(wù)01表關(guān)系01目錄CONTENTS數(shù)據(jù)查詢02表關(guān)系01

先定一個(gè)小目標(biāo)！了解一對(duì)一表關(guān)系的概念了解實(shí)現(xiàn)一對(duì)一表關(guān)系的方式表關(guān)系一對(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)員姓名、性別和年齡為頻繁使用字段表關(guān)系一對(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é)生信息表：表關(guān)系

先定一個(gè)小目標(biāo)！了解一對(duì)多表關(guān)系的概念了解實(shí)現(xiàn)一對(duì)多表關(guān)系的方式表關(guān)系一對(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é)生表：表關(guān)系

先定一個(gè)小目標(biāo)！了解多對(duì)多表關(guān)系的概念了解實(shí)現(xiàn)多對(duì)多表關(guān)系的方式表關(guān)系多對(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ù)課程表：表關(guān)系多對(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é)生選課表：表關(guān)系數(shù)據(jù)查詢02

先定一個(gè)小目標(biāo)！掌握查詢選項(xiàng)的語(yǔ)法格式掌握查詢選項(xiàng)的基本使用數(shù)據(jù)查詢?nèi)コ貜?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ù)記錄，只保留一條數(shù)據(jù)查詢查詢?nèi)坑涗洿a示例去除重復(fù)項(xiàng)代碼示例數(shù)據(jù)查詢

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

表示大于比較<

表示小于比較>=表示大于等于比較<=表示小于等于比較<>、!=表示不等于比較BRTWEEN…AND…比較數(shù)據(jù)是否在閉區(qū)間范圍內(nèi)，若在返回1，不在返回0（1/2）數(shù)據(jù)查詢比較運(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）數(shù)據(jù)查詢案例：比較運(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|+----+------------+---------+數(shù)據(jù)查詢邏輯運(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é)果為0數(shù)據(jù)查詢案例：邏輯運(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|+----+---------+---------+數(shù)據(jù)查詢

先定一個(gè)小目標(biāo)！掌握分組的基本使用掌握回溯統(tǒng)計(jì)的基本使用掌握排序的基本使用掌握HAVING子句的基本使用數(shù)據(jù)查詢分組分組：在WHERE條件后面添加GROUPBY。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]GROUPBY字段名;語(yǔ)法格式注意：對(duì)數(shù)據(jù)分組后，SELECT獲取的字段列表只能是GROUPBY分組的字段或使用了聚合函數(shù)的非分組字段。數(shù)據(jù)查詢分組常用聚合函數(shù)：函數(shù)名描述COUNT()返回參數(shù)字段的數(shù)量，不統(tǒng)計(jì)為NULL的記錄SUM()返回參數(shù)字段之和AVG()返回參數(shù)字段的平均值MAX()返回參數(shù)字段的最大值MIN()返回參數(shù)字段的最小值GROUP_CONCAT()返回復(fù)合條件的參數(shù)字段值的連接字符串?dāng)?shù)據(jù)查詢分組使用聚合函數(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|+-------------+------------+數(shù)據(jù)查詢回溯統(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ǔ)法格式數(shù)據(jù)查詢案例：?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ì)+-------------+----------+數(shù)據(jù)查詢案例：多分組回溯統(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|+-------+-------------------+-----------+數(shù)據(jù)查詢排序分組排序：為指定字段進(jìn)行升序或降序排序的功能，默認(rèn)為升序。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]GROUPBY字段名[ASC|DESC];語(yǔ)法格式數(shù)據(jù)查詢案例：分組排序代碼示例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鉛筆,鋼筆,碳素筆|+-------------+------------------------+---------------------------+數(shù)據(jù)查詢HAVING子句篩選分組結(jié)果：對(duì)查詢的數(shù)據(jù)執(zhí)行分組操作時(shí)，可以利用HAVING根據(jù)條件進(jìn)行數(shù)據(jù)篩選。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]GROUPBY字段名HAVING條件表達(dá)式;語(yǔ)法格式數(shù)據(jù)查詢HAVING子句HAVING和WHERE區(qū)別：WHERE操作是從數(shù)據(jù)表中獲取數(shù)據(jù)，將數(shù)據(jù)從磁盤存儲(chǔ)到內(nèi)存中，HAVING是對(duì)已存放到內(nèi)存中的數(shù)據(jù)進(jìn)行操作。HAVING位于GROUPBY子句后，WHERE位于GROUPBY子句之前。HAVING關(guān)鍵字后可以使用聚合函數(shù)，WHERE不可以。數(shù)據(jù)查詢案例：分組篩選代碼示例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|+-------+-------------------+----------------------+數(shù)據(jù)查詢

先定一個(gè)小目標(biāo)！掌握單字段排序的基本語(yǔ)法和使用掌握多字段排序的基本語(yǔ)法和使用掌握對(duì)中文排序的使用數(shù)據(jù)查詢STEP02SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達(dá)式]ORDERBY字段名1[,字段名2…][ASC|DESC];語(yǔ)法格式兩種排序方式：?jiǎn)巫侄闻判蚝投嘧侄闻判?。?shù)據(jù)查詢案例：?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|+----+-----------+---------+數(shù)據(jù)查詢案例：多字段排序代碼示例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|+-------------+----+----------+---------+數(shù)據(jù)查詢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|液晶顯示器|+----+------------+數(shù)據(jù)查詢

先定一個(gè)小