《PHP+MySQL动态网站开发》课件 第7章 MySQL多表操作

第7章MySQL多表操作《PHP+MySQL动态网站开发》学习目标/Target

熟悉数据表的联系，能够说出一对一、一对多和多对多联系的区别。

熟悉数据库设计范式，能够运用范式合理设计数据库。

掌握去除查询结果中的重复数据的方法，能够利用DISTINCT实现去重查询。

掌握运算符的用法，能够在SQL语句中使用运算符查询数据。学习目标/Target

掌握分组、排序和限量的使用，能够对查询结果实现分组、排序和限量操作。

掌握联合查询的使用，能够根据不同场景灵活使用联合查询。

掌握聚合函数的用法，能够用聚合函数统计数据。

掌握连接查询操作，能够根据不同场景使用交叉连接查询、内连接查询和外连

接查询。学习目标/Target

掌握子查询的使用，能够根据不同的需求使用标量子查询、列子查询、行子查询、

表子查询和EXISTS子查询。

熟悉外键约束的概念，能够说明外键约束的作用。

掌握数据表中外键约束的使用，能够正确添加、删除外键约束，并完成关联表中

数据的添加、更新和删除操作。

熟悉子查询的概念，能够区分每种子查询的作用。章节概述/Summary在前面的章节中，已经讲解了MySQL的基础知识和查询语法，然而在实际开发中，业务逻辑较为复杂，通常都需要对多张表进行关联操作，才能满足需求。本章将讲解数据表的联系、数据库设计范式、数据进阶操作、联合查询、连接查询，以及子查询和外键约束的使用。目录/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用户编号订单编号用户名付款状态11张三已支付12张三未支付23李四已支付订单表用户表7.2.2第二范式用户编号和订单编号组成了复合主键，付款状态完全依赖该复合主键，而用户名只依赖用户编号采用上述方式设计的用户表存在以下问题：插入异常：如果一个用户没有下过订单，则该用户无法插入。删除异常：如果删除一个用户所有的订单，则该用户会被删除。更新异常：由于用户名冗余，修改一个用户时需要修改多条数据。如果稍有不慎，漏改某些数据，会出现更新异常。7.2.2第二范式为了满足第二范式，将复合主键移动到订单表中。7.2.2第二范式满足第二范式的情况：用户编号用户名1张三2李四订单编号用户编号订单商品购买件数下单时间付款状态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第三范式

先定一个小目标！熟悉逆规范化，能够合理运用数据库设计范式设计数据表。7.2.4逆规范化逆规范化是一种反范式的设计，其目的主要是提高查询效率。范式虽然减少了数据冗余，但是增加了表的数量，这会使查询变得复杂，尤其是在连接多张表查询数据时，会使查询性能降低。7.2.4逆规范化逆规范化设计商品表和订单表：商品编号商品名称商品价格商品销量1铅笔252钢笔8913圆珠笔101订单编号商品编号购买件数下单时间1132023-01-2008:30:152122023-01-2109:00:153212023-01-2209:30:154312023-01-2309:59:15商品表订单表7.2.4逆规范化数据进阶操作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。回溯统计的语法格式：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在实际使用时的区别：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外连接查询使用左外连接查询和右外连接查询的区别：左外连接查询：返回左表中的所有数据和右表中符合连接条件的数据。右外连接查询：返回右表中的所有数据和左表中符合连接条件的数据。外连接查询左外连接（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作为执行所需的条件或查询的数据源。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和专业表majors数据之间的关联：7.7.1外键约束概述

先定一个小目标！掌握添加外键约束的语法，能够正确添加外键约束。7.7.2添加外键约束外键约束可以在创建数据表时添加，也可以在修改数据表时添加，添加外键约束的语法格式：#创建数据表时添加外键约束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添加外键约束创建student数据表，在创建时添加外键约束：CREATETABLEstudent(

idINTPRIMARYKEYAUTO_INCREMENT,

nameVARCHAR(32)NOTNULL,

midINTNOTNULL,

CONSTRAINTm_id

FOREIGNKEY(mid)REFERENCESmajors(id)

ONDELETERESTRICTONUPDATECASCADE);7.7.2添加外键约束创建maj