MySQL多表操作表关系与数据查询

主讲教师：魏巍巍北京信息职业技术学院数据库技术与应用表关系与数据查询创建采集任务01表关系01目录CONTENTS数据查询02表关系01

先定一个小目标！了解一对一表关系的概念了解实现一对一表关系的方式表关系一对一：表中的一条记录与另外一张表中有且仅有一条记录有关系。实现一对一表关系：一张表拆分成两个表，将频繁使用的字段和生僻字段进行垂直分割，使用相同的主键对应。拆分前数据表示例学号姓名性别年龄身高体重籍贯政治面貌1张三男20165140河北团员2李四女21168100山东团员3小明男22170130陕西团员姓名、性别和年龄为频繁使用字段表关系一对一设计数据表示例学号姓名性别年龄1张三男202李四女213小明男22学号身高体重籍贯政治面貌1165140河北团员2168100山东团员3170130陕西团员学生表：学生信息表：表关系

先定一个小目标！了解一对多表关系的概念了解实现一对多表关系的方式表关系一对多：表中的一条记录与另外一张表中多条记录对应，也叫多对一。一对多设计数据表示例班级编号班级名班主任1软件班张老师2设计班王老师学号姓名性别班级号1张三男12李四女13小明男2班级表：学生表：表关系

先定一个小目标！了解多对多表关系的概念了解实现多对多表关系的方式表关系多对多：表中的多条记录与另外一张表中多条记录对应，需要借助第三张表，将多对多关系变成多个多对一的关系。多对多设计数据表示例课程编号课程名1计算机2数据库课程表：表关系多对多设计数据表示例学号姓名性别班级号1张三男12李四女13小明男2编号学号课程编号111221312432学生表：学生选课表：表关系数据查询02

先定一个小目标！掌握查询选项的语法格式掌握查询选项的基本使用数据查询去除重复记录：使用SELECT语句的选项进行筛选。SELECTselect选项字段列表FROM数据表;语法格式select选项默认值为ALL，表示保存所有查询到的记录select选项值为DISTINCT，表示去除重复记录，只保留一条数据查询查询全部记录代码示例去除重复项代码示例数据查询

先定一个小目标！掌握比较运算符的基本使用掌握逻辑运算符的基本使用数据查询比较运算符比较运算符：在条件表达式中对结果进行限定。比较运算符的结果值：1（TRUE，表示为真）、0（FALSE，表示为假）或NULL。数据查询比较运算符运算符描述=用于相等比较<=>可以进行NULL值比较的相等运算符>

表示大于比较<

表示小于比较>=表示大于等于比较<=表示小于等于比较<>、!=表示不等于比较BRTWEEN…AND…比较数据是否在闭区间范围内，若在返回1，不在返回0（1/2）数据查询比较运算符运算符描述NOTBRTWEEN…AND…比较数据是否不在闭区间范围内，若不在返回1，若在返回0IS比较数据是否是TRUE、FALSE或UNKNOWN，若是返回1，若不是返回0ISNOT比较数据是否不是TRUE、FALSE或UNKNOWN，若不是返回1，若是返回0ISNULL比较数据是否是NULL，若是返回1，若不是返回0ISNOTNULL比较数据是否不是NULL，若不是返回1，若是返回0LIKE'匹配模式'获取匹配到的数据NOTLIKE'匹配模式'获取匹配不到的数据（2/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|+----+------------+---------+数据查询逻辑运算符逻辑运算符：在条件表达式中用于逻辑判断、可与比较运算符结合使用。逻辑运算符的结果值：1（TRUE，表示为真）、0（FALSE，表示为假）或NULL。运算符描述AND或&&逻辑与，若操作数全部为真，则结果为1，否则为0OR或||逻辑或，若操作数中只要有一个为真，则结果为1，否则为0NOT或!逻辑非，若操作数为0，则结果为1，否则为0XOR逻辑异或，若操作数一个为真，一个为假，则结果为1；若操作数全部为真或全部为假，则结果为0数据查询案例：逻辑运算符的使用代码示例#查询my_goods表中关键词为电子产品的5星商品，商品信息包括id、name和pricemysql>SELECT`id`,`name`,`price`FROM`my_goods`->WHERE`keyword`='电子产品'&&`score`=5;+----+---------+---------+|id|name|price|+----+---------+---------+|5|智能手机|1999.00|+----+---------+---------+数据查询

先定一个小目标！掌握分组的基本使用掌握回溯统计的基本使用掌握排序的基本使用掌握HAVING子句的基本使用数据查询分组分组：在WHERE条件后面添加GROUPBY。SELECT字段列表FROM数据表名[WHERE条件表达式]GROUPBY字段名;语法格式注意：对数据分组后，SELECT获取的字段列表只能是GROUPBY分组的字段或使用了聚合函数的非分组字段。数据查询分组常用聚合函数：函数名描述COUNT()返回参数字段的数量，不统计为NULL的记录SUM()返回参数字段之和AVG()返回参数字段的平均值MAX()返回参数字段的最大值MIN()返回参数字段的最小值GROUP_CONCAT()返回复合条件的参数字段值的连接字符串数据查询分组使用聚合函数：使用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|+-------------+------------+数据查询回溯统计回溯统计：系统自动对分组字段向上进行一次新的统计并产生一个新的统计数据，该数据对应的分组字段值为NULL。SELECT字段列表FROM数据表名[WHERE条件表达式]GROUPBY字段名1[,字段名2…]WITHROLLUP;语法格式数据查询案例：单字段回溯统计代码示例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|对分组的回溯统计+-------------+----------+数据查询案例：多分组回溯统计代码示例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|+-------+-------------------+-----------+数据查询排序分组排序：为指定字段进行升序或降序排序的功能，默认为升序。SELECT字段列表FROM数据表名[WHERE条件表达式]GROUPBY字段名[ASC|DESC];语法格式数据查询案例：分组排序代码示例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铅笔,钢笔,碳素笔|+-------------+------------------------+---------------------------+数据查询HAVING子句筛选分组结果：对查询的数据执行分组操作时，可以利用HAVING根据条件进行数据筛选。SELECT字段列表FROM数据表名[WHERE条件表达式]GROUPBY字段名HAVING条件表达式;语法格式数据查询HAVING子句HAVING和WHERE区别：WHERE操作是从数据表中获取数据，将数据从磁盘存储到内存中，HAVING是对已存放到内存中的数据进行操作。HAVING位于GROUPBY子句后，WHERE位于GROUPBY子句之前。HAVING关键字后可以使用聚合函数，WHERE不可以。数据查询案例：分组筛选代码示例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|+-------+-------------------+----------------------+数据查询

先定一个小目标！掌握单字段排序的基本语法和使用掌握多字段排序的基本语法和使用掌握对中文排序的使用数据查询STEP02SELECT字段列表FROM数据表名[WHERE条件表达式]ORDERBY字段名1[,字段名2…][ASC|DESC];语法格式两种排序方式：单字段排序和多字段排序。数据查询案例：单字段排序代码示例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|+----+-----------+---------+数据查询案例：多字段排序代码示例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|+-------------+----+----------+---------+数据查询STEP02中文排序使用“CONVERT(字段名USINGgbk)”函数强制让指定的字段按中文拼音顺序排序。案例：代码示例mysql>mysql>SELECT`id`,`name`FROM`my_goods`ORDERBYCONVERT(`name`USINGgbk)ASC;+----+------------+|id|name|+----+------------+|1|2B铅笔||8|办公计算机||10|薄毛衣||4|液晶显示器|+----+------------+数据查询

先定一个小