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

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

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

先定一個小目標！了解一對多表關(guān)系的概念了解實現(xiàn)一對多表關(guān)系的方式表關(guān)系一對多：表中的一條記錄與另外一張表中多條記錄對應(yīng)，也叫多對一。一對多設(shè)計數(shù)據(jù)表示例班級編號班級名班主任1軟件班張老師2設(shè)計班王老師學號姓名性別班級號1張三男12李四女13小明男2班級表：學生表：表關(guān)系

先定一個小目標！了解多對多表關(guān)系的概念了解實現(xiàn)多對多表關(guān)系的方式表關(guān)系多對多：表中的多條記錄與另外一張表中多條記錄對應(yīng)，需要借助第三張表，將多對多關(guān)系變成多個多對一的關(guān)系。多對多設(shè)計數(shù)據(jù)表示例課程編號課程名1計算機2數(shù)據(jù)庫課程表：表關(guān)系多對多設(shè)計數(shù)據(jù)表示例學號姓名性別班級號1張三男12李四女13小明男2編號學號課程編號111221312432學生表：學生選課表：表關(guān)系數(shù)據(jù)查詢02

先定一個小目標！掌握查詢選項的語法格式掌握查詢選項的基本使用數(shù)據(jù)查詢?nèi)コ貜陀涗洠菏褂肧ELECT語句的選項進行篩選。SELECTselect選項字段列表FROM數(shù)據(jù)表;語法格式select選項默認值為ALL，表示保存所有查詢到的記錄select選項值為DISTINCT，表示去除重復記錄，只保留一條數(shù)據(jù)查詢查詢?nèi)坑涗洿a示例去除重復項代碼示例數(shù)據(jù)查詢

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

表示大于比較<

表示小于比較>=表示大于等于比較<=表示小于等于比較<>、!=表示不等于比較BRTWEEN…AND…比較數(shù)據(jù)是否在閉區(qū)間范圍內(nèi)，若在返回1，不在返回0（1/2）數(shù)據(jù)查詢比較運算符運算符描述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ù)查詢案例：比較運算符的使用代碼示例#查詢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|+----+------------+---------+數(shù)據(jù)查詢邏輯運算符邏輯運算符：在條件表達式中用于邏輯判斷、可與比較運算符結(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é)果為0數(shù)據(jù)查詢案例：邏輯運算符的使用代碼示例#查詢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|+----+---------+---------+數(shù)據(jù)查詢

先定一個小目標！掌握分組的基本使用掌握回溯統(tǒng)計的基本使用掌握排序的基本使用掌握HAVING子句的基本使用數(shù)據(jù)查詢分組分組：在WHERE條件后面添加GROUPBY。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]GROUPBY字段名;語法格式注意：對數(shù)據(jù)分組后，SELECT獲取的字段列表只能是GROUPBY分組的字段或使用了聚合函數(shù)的非分組字段。數(shù)據(jù)查詢分組常用聚合函數(shù)：函數(shù)名描述COUNT()返回參數(shù)字段的數(shù)量，不統(tǒng)計為NULL的記錄SUM()返回參數(shù)字段之和AVG()返回參數(shù)字段的平均值MAX()返回參數(shù)字段的最大值MIN()返回參數(shù)字段的最小值GROUP_CONCAT()返回復合條件的參數(shù)字段值的連接字符串數(shù)據(jù)查詢分組使用聚合函數(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|+-------------+------------+數(shù)據(jù)查詢回溯統(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;語法格式數(shù)據(jù)查詢案例：單字段回溯統(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)計+-------------+----------+數(shù)據(jù)查詢案例：多分組回溯統(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|+-------+-------------------+-----------+數(shù)據(jù)查詢排序分組排序：為指定字段進行升序或降序排序的功能，默認為升序。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]GROUPBY字段名[ASC|DESC];語法格式數(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|收腰風衣||12|4|液晶顯示器||10|8|辦公計算機||9|7|頭戴耳機||8|6|桌面音箱||6|5|智能手機||3|1,2,3|2B鉛筆,鋼筆,碳素筆|+-------------+------------------------+---------------------------+數(shù)據(jù)查詢HAVING子句篩選分組結(jié)果：對查詢的數(shù)據(jù)執(zhí)行分組操作時，可以利用HAVING根據(jù)條件進行數(shù)據(jù)篩選。SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]GROUPBY字段名HAVING條件表達式;語法格式數(shù)據(jù)查詢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不可以。數(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ù)查詢

先定一個小目標！掌握單字段排序的基本語法和使用掌握多字段排序的基本語法和使用掌握對中文排序的使用數(shù)據(jù)查詢STEP02SELECT字段列表FROM數(shù)據(jù)表名[WHERE條件表達式]ORDERBY字段名1[,字段名2…][ASC|DESC];語法格式兩種排序方式：單字段排序和多字段排序。數(shù)據(jù)查詢案例：單字段排序代碼示例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|+----+-----------+---------+數(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|智能手機|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|+-------------+----+----------+---------+數(shù)據(jù)查詢STEP02中文排序使用“CONVERT(字段名USINGgbk)”函數(shù)強制讓指定的字段按中文拼音順序排序。案例：代碼示例mysql>mysql>SELECT`id`,`name`FROM`my_goods`ORDERBYCONVERT(`name`USINGgbk)ASC;+----+------------+|id|name|+----+------------+|1|2B鉛筆||8|辦公計算機||10|薄毛衣||4|液晶顯示器|+----+------------+數(shù)據(jù)查詢

先定一個小