MySQL数据库技术与项目应用教程电子教案 项目四 CRU操作系统数据

备课纸适用班级：_______P.课题项目四CRUD操作网上商城系统数据课时8+8课时教学目的会使用SELECT语句从表中检索数据会根据条件筛选指定的数据行会使用聚合函数分组统计数据会使用内连接、外连接和交叉连接及联合条件连接查询多表数据会使用比较运算符及IN、ANY、EXISTS等关键字查询多表数据重点使用SELECT语句从表中检索数据会根据条件筛选指定的数据行会使用聚合函数分组统计数据会使用内连接、外连接和交叉连接及联合条件连接查询多表数据会使用比较运算符及IN、ANY、EXISTS等关键字查询多表数据难点使用聚合函数分组统计数据使用内连接、外连接和交叉连接及联合条件连接查询多表数据使用比较运算符及IN、ANY、EXISTS等关键字查询多表数据教具多媒体+机房课型新授作业课后习题课后项目实战教学过程课时分配教学方法教学内容教学引入：数据库系统作为数据存储的仓库，不能仅仅只起到容器的作用，同时还需要担当起数据的管理，维护的角色。数据查询对工作、生活的影响学生讨论网上商城系统的用户有哪些？常用的操作有哪些？----学生讨论，导出查询在数据库操作中的作用。备课纸适用班级：_P.任务1查询单表数据任务场景单表数据查询是最基本的数据查询，其查询的数据源只涉及数据库中的一张表。带着问题开本任务讨论如下内容：展本章学习（1）怎样将用户需求的信息查询出来？激发学生兴（2）怎样筛选用户所需数据？趣（3）怎样对分组数据进行汇总？（4）怎样对汇总后的数据进行筛选？（5）怎样对结果集进行汇总？SELECT语句查询操作用于从数据表中筛选出符合需求的数据，查询得到的结果集也是关系模式，按照表的形式组织并显示。回顾关系代数据库查询操作类型：数数据的操连接---多表联接作类型选择---选择行投影---选择列解释每个子句的作用SELECT[ALL|DISTINCT]*|列名1[[AS]别名][,列名2,…,列名n]FROM表名[WHERE条件表达式][GROUPBY列名[ASC|DESC][HAVING条件表达式]][ORDERBY列名[ASC|DESC],...][LIMIT[OFFSET]记录数];SELECT子句：指定查询结果集返回的列，当使用“*”时，用于显示表中所有的列；关键字DISTINCT为可选参数，用于消除查询结果集中的重复记录。FROM子句：指定查询的数据源，可以是表或视图。WHERE子句：指定查询的筛选条件。GROUPBY子句：指定查询的分组列名；关键字HAVING为可选参数，用于指定分组后的结果集筛选条件。备课纸适用班级：_P.ORDERBY子句：指定查询结果集的排序列名。排序方式由参数ASC或DESC控制，其中ASC表示按升序排列，DESC则表示按降序排列，当不指定排序参数时，默认为升序。LIMIT子句：用于限制查询结果集的行数。参数OFFSET为偏移量，当OFFSET值为0时（默认），表示查询结果从第1条记录开始返回，若OFFSET为1，查询结果则从第2条记录开始，依此类推；记录数则表示结果集中包含的记录行数。课堂提问：SELECT语句中，哪些子句是必须的？选择列精讲选择列是指从表中选出指定的属性值组成的结果集，是关系代数中投影运算的具体实现。1.查询所有列在SELECT子句中，关键字“*”表示选择指定表中所有列。查询结果集中的排列顺序与源表中列的顺序相同。解释+演示【例4.1】查询onlinedb数据库中category（商品类别表）中所有的商品类别信息。USEonlinedb;SELECT*FROMcategory;学习提示除非需要使用表中所有列的数据，一般不建议使用“*”查询数据，以免由于获2.查询指定的列解释+演示SELECTgcode,gname,gprice,gsale_qtyFROMgoods;课堂练习：备课纸适用班级：_P.实例演示SELECTgname,gprice*gsale_qtyFROMgoods;实例演示SELECTuname,year(now())-year(ubirthday)FROMusers;学习提示在数据库设计过程中，为减少数据冗余，凡能通过已知列计算所得的数据一般不再提供列存储。4.为查询结果集中的列指定列标题当希望查询结果中显示的列使用自定义的列标题时，可以使用关键字AS更改结果集中的列标题（列的别名）。实例说明【例4.6】查询goods表，列出商品名称、价格和销售量，结果集中各列的标题指定为商品名、价格和销售量。SELECTgnameAS商品名,gpriceAS价格,gsale_qtyAS销售量FROMgoods;选择行以筛选出用户所需的数据，这种查询方式称为选择行，是关系代数中选择运算的具体实现。WHERE条件表达式1.使用比较运算符语法格式WHERE表达式1比较运算符表达式2备课纸适用班级：_P.解释+演示【例4.8】查询goods表,找出热销商品（gishot值为1）的商品编号和名称。SELECTgcode,gnameFROMgoodsWHEREgishot=1;解释+演示【例4.9】查询goods表,找出销售量在40及以上的商品名称、价格和销售量。SELECTgname,gprice,gsale_qtyFROMgoodsWHEREgsale_qty>=40;2.使用逻辑运算符语法格式WHERE[NOT]表达式1逻辑运算符表达式2举例说明：【例4.10】查询goods表,找出价格在100以下且销售额在3000元及以上的商品名称、价格、销售量和销售额，销售额的列标题为sales_figures。解释+演示SELECTgname,gprice,gsale_qty,gprice*gsale_qtyassales_figuresFROMgoodsWHEREgprice<100ANDgprice*gsale_qty>=3000;举例说明：【例4.11】查询goods表,找出销售量在40及以上或价格在3000元及以上的商品名称，价格和销量。解释+演示SELECTgname,gprice,gsale_qtyFROMgoodsWHEREgsale_qty>=50ORgprice>=3000;备课纸适用班级：_P.【例4.12】查询goods表,找出非热销商品（gishot值为0）的商品编号和名称。解释+演示SELECTgcode,gnameFROMgoodsWHERENOTgishot;学习提示当WHERE语句中有NOT运算符时，应将NOT放在表达式的前面。【例4.13】查询goods表,找出6月或7月上架且销量在40及以上的商品名称，销售量和上架时间。解释+演示SELECTgname,gsale_qty,gaddtimeFROMgoodsWHERE(MONTH(gaddtime)=6ORMONTH(gaddtime)=7)ANDgsale_qty>=40;学习提示AND的运算符优先级高于OR，当AND和OR运算符一起使用时，会先运算AND两侧的条件表达式，然后再运算OR两侧的条件表达式。当条件表达式中参与逻辑运算符都为AND时，且各表达式都精确比较时，MySQL8.0+提供了元组等值的比较方法。语法说明WHERE(字段值1,字段值2,[字段值3…])=(数值1,数值2,[数值3…])【例4.14】查询users表，找出所在城市为“长沙”，用户名为“段湘林”的登录名。SELECTuloginFROMusersWHERE(uname,ucity)=('段湘林','长沙');3.使用BETWEENAND运算符WHERE子句中，可使用BETWEENAND来限制查询数据的范围WHERE表达式[NOT]BETWEEN初始值AND终止值举例说明：【例4.15】查询goods表，找出价格在200到400元的商品名称和价格。备课纸适用班级：_P.解释+演示SELECTgname,gpriceFROMgoodsWHEREgpriceBETWEEN200AND400;学习提示使用BETWEEN…AND运算符，等价由AND运算符连接两个比较运算符组成的表达式，其中限制的初始值不能大于终止值。4.使用IN运算符解释+演示SELECTcid,gnameFROMgoodsWHEREcidIN(1,2,4);解释+演示SELECTgname,gsale_qty,gaddtimeFROMgoodsWHEREDATE_FORMAT(gaddtime,'%y-%m')IN('21-06','21-07');结果分析其中DATE_FORMAT()函数用于以不同的格式显示日期/时间数据。本例中%y表示用2位表示年份；%m表示月，取值为“00-12”。学习提示使用IN运算符比较，等价由OR运算符连接多个表达式，但使用IN构建搜索条件的语法更简洁。使用时不允许在值列表中出现NULL。5.使用LIKE运算符使用LIKE运算符实现字符串的模糊查询WHERE列名[NOT]LIKE'字符串常量'[ESCAPE'转义字符']备课纸适用班级：_P.举例说明：【例4.18】查询users表，找出用户名（uname）以“李”开头的用户名、性别和登录名。解释+演示SELECTuname,ugender,uloginFROMusersWHEREunamelike'李%';【例4.19】查询users表，找出uname第2个字为“湘”的用户姓名、性别和所在城市。解释+演示SELECTuname,ugender,ucityFROMusersWHEREunamelike'_湘%';学习提示MySQL中字符的比较不区分大小写。当LIKE后的字符串不含通配符时，则可以用“=”运算符替代，而“<>”则可以替代NOTLIKE运算。当比较的字符串中含通配符时，MySQL采用转义字符来实现。【例4.20】查询goods表，找出含有“伏羲式_七弦琴”的商品名称，列出商品名称和价格。解释+演示SELECTgname,gpriceFROMgoodsWHEREgnamelike'%伏羲式\_七弦琴%';【例4.21】修改【例4.20】的查询，模糊查询时指定转义字符为'|'。解释+演示SELECTgname,gpriceFROMgoodsWHEREgnamelike'%伏羲式|_七弦琴%'ESCAPE'|';6.使用REGEXP运算符MySQL支持正则表达式的匹配。正则表达式通常用来检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的字符串。REGEXP常用字符匹配模式：备课纸适用班级：_P.精讲【例4.22】查询goods表，找出商品名称含有“乐器”的商品，列出名称，价格和销售量。学生演示SELECTgname,gprice,gsale_qtyFROMgoodsWHEREgnameREGEXP'乐器';【例4.23】查询users表，找出登录名以“13、14、15、19”开头的用户，列出登录名和用户名。学生演示SELECTulogin,uname,ugenderFROMusersWHEREuloginREGEXP'^1[3-59]';7.使用ISNULL运算符ISNULL运算符实现表达式跟空值的比较语法格式WHERE列名IS[NOT]NULL【例4.24】查询users表，找出未填写邮箱的用户，列出登录名和用户名。实例演示SELECTulogin,uname,ugenderFROMusersWHEREuemailISNULLORuemail='';备课纸适用班级：_P.学习提示NULL表示不确定，不等同于数值0或空字符，除“<=>”运算符能与NULL比较外，不能使用其他比较运算符或者LIKE运算符对NULL进行判断。8.使用DISTINCT消除重复结果集当查询结果集的数据重复时，可使用DISTINCT关键字去除重复的结果集【例4.25】查询users表,列出用户来源的城市。实例演示SELECTDISTINCTucityFROMusers;使用LIMIT关键字限制返回记录数SELECT语句中的LIMIT子句可以限制返回记录的数量。并能指定查询结果从哪一条记录开始语法格式LIMIT[OFFSET,]记录数其中参数OFFSET表示偏移量，若偏移量为0，查询结果从第1条记录开始，偏移量为1时则从查询结果的第2条记录开始，依次类推。OFFSET为可选项，默认值为0。记录数则表示返回记录的行数。【例4.26】查询goods表，列出前3行商品的id、商品名称、价格。实例演示SELECTgid,gname,gprice,gsale_qtyFROMgoodsLIMIT3;【例4.27】查询goods表，列出第4至第6行商品的id、名称、价格和销售量。实例演示SELECTgid,gname,gprice,gsale_qtyFROMgoodsLIMIT3,3;结果分析根据查询需求，需设定OFFSET值为3，表示从第4行记录开始；设置记录数为3，表示结果集返回3行记录使用CASE表达式更改查询结果MySQL中，可以将CASE表达式嵌在SELECT语句中，实现对查询结果数据的变更备课纸适用班级：_P.1.CASE简单结构CASE简单结构将表达式与一组确切的数据值进行比较返回相应结果：语法格式CASE表达式WHEN数值1THEN结果值1WHEN数值2THEN结果值2……ELSE结果值nENDAS新列名【例4.28】查询goods表，列出所有商品的名称和销售情况（goods_status），其中销售情况值根据gishot列的值确定，当gishot为1时值为“热销”，值为0时值为“非热销”实例演示SELECTgname,gsale_qty,CASEgishotWHEN1THEN'热销'ELSE'非热销'ENDASgoods_statusFROMgoods2.CASE搜索结构语法格式CASEWHEN条件1THEN结果值1WHEN条件2THEN结果值2……ELSE结果值nENDAS新列名【例4.29】查询users表，列出前5名用户的用户名和积分，并根据积分的取值生成新的列“grade”以标识用户的等级，当积分在100分及以上，钻石会员，当积分在50及以上时为黄金会员，其余为普通会员。实例演示SELECTuname,ucredit,CASEWHENucredit>=100THEN'钻石会员'WHENucredit>=50THEN'黄金会员'ELSE'普通会员'ENDASgradeFROMusersLIMIT5;备课纸适用班级：_P.任务2统计分析单表数据任务描述实际应用中，除查询基本数据外，还需要对数据做排序、统计及分析等操作，以方便用户对数据进行基本的分析。数据排序强调指出设定格式的输出只会改变数据的表示方法ORDERBY子句可以对结果集进行升序(ASC)和降序(DESC)排列。ORDERBY{列名|表达式|正整数}[ASC|DESC][,…n]【例4.30】查询goods表，找出类别ID（cid）为1的商品名称、价格和销售量，并按价格升序排列。SELECTgname,gprice,gsale_qtyFROMgoodsWHEREcid=1ORDERBYgprice;课堂练习【例4.31】查询goods表，找出类别ID（cid）为1的商品名称、价格和销售量，并按价格升序排列，当价格相同时按销量降序排列。学生演示SELECTgname,gprice,gsale_qtyFROMgoodsWHEREcid=1ORDERBYgprice,gsale_qtyDESC;学习提示当指定的排序关键列有多个时，应分别指出各列的升序或降序选项。数据分组统计对表进行数据查询时，通常需要对查询结果集进行计算和统计。在SELECT语句中，使用聚合函数、GROUPBY子句能够实现对查询结果集进行分组和统计等操作1.使用聚合函数知识点介绍聚合函数能够实现对数据表中指定列的值进行统计计算，并返回单个数值。聚合函数主要应用在GROUPBY子句中，用来对查询结果进行分组、筛选或统计。备课纸适用班级：_P.精讲（1）SUM、AVG、MAX和MIN函数。语法格式SUM/AVG/MAX/MIN([ALL|DISTINCT]列名|常量|表达式)【例4.32】查询goods表，统计所有商品的总销售量。实例演示SELECTSUM(gsale_qty)FROMgoods;【例4.33】查询goods表，统计商品的最高价格和最低价格。SELECTMAX(gprice),MIN(gprice)FROMgoods;（2）COUNT函数COUNT({[[ALL|DISTINCT]列名|常量|表达式]|*})【例4.34】查询users表，统计用户总人数。SELECTCOUNT(*)FROMusers;【例4.35】查询orders表，统计购买过商品的用户人数。SELECTCOUNT(DISTINCTuid)FROMorders;学习提示COUNT(*)不能与DISTINCT一起使用。GROUPBY子句问题情景在销售管理系统，需要统计各业务员的月销售总量时，应怎样实现？----回顾计算机基础EXCEL中的分类与汇总备课纸适用班级：_P.使用GROUPBY子句则可以按指定的列对查询结果集进行分组，并使用聚合函数为结果集中的每个分组产生一个汇总值。语法格式GROUPBY[ALL]列名1,列名2[,...n][WITHROLLUP][HAVING条件表达式]学习提示实际应用中，GROUPBY子句使用时都会与聚合函数一起。【例4.36】查询users表，统计各城市的用户人数。实例讲解SELECTucity,COUNT(*)FROMusersGROUPBYucity;实例讲解【例4.37】查询users表，按性别统计年龄的最小值，列名为min_age。SELECTugender,min(year(CURRENT_DATE)-year(ubirthday))ASmin_ageFROMusersGROUPBYugender;使用GROUPBY进行数据分组时，还可以同时对多列进行交叉分组。实例讲解【例4.38】查询users表，按城市统计各性别的用户数，列名为city_nums，并按城市排序。SELECTucity,ugender,count(*)AScity_numsFROMusersGROUPBYucity,ugenderORDERBYucity;结果分析从结果可以看出，查询对用户表中的城市和性别两列进行了交叉统计，分别统计出了每个城市男性和女性的人数。（3）GROUPBY和GROUP_CONCAT一起使用GROUPBY和GROUP_CONCAT一起使用，能实现同一分组中某个列的数据值按指定的分隔符连接起来。语法解释GROUP_CONCAT([DISTINCT]表达式[ORDERBY列名][SEPARATOR分隔符])备课纸适用班级：_P.实例讲解【例4.39】查询users表，将同一城市的uid值用逗号“,”连接起来，列名为uids。SELECTucity,GROUP_CONCAT(uid)asuidsFROMusersGROUPBYucity;课堂练习【例4.40】查询users表，将同一城市的uid值用下划线“_”连接起来，列名为uid。学生演示SELECTuCity,GROUP_CONCAT(uidORDERBYuidSEPARATOR'_')asuidFROMusersGROUPBYucity;学习提示GROUP_CONCAT函数必须跟GROUPBY子句一起使用。（4）GROUPBY和WITHROLLUP一起使用GROUPBY和WITHROLLUP一起使用时，除统计每个分组值外，会增加额外的汇总行。【例4.41】修改例【4.36】，为查询结果添加汇总行。实例演示SELECTucity,COUNT(*)FROMusersGROUPBYucityWITHROLLUP;MySQL8.0+提供新的聚合函数GROUPING，可以标明这行数据是汇总数据行还是分组数据行。GROUPING(列名)【例4.42】修改例【4.38】，为查询结果添加汇总行，并标识该行是对哪一列数据的汇总。备课纸适用班级：_P.实例演示SELECTucity,ugender,count(*)AScity_nums, GROUPING(ucity)asgrp_city, GROUPING(ugender)asgrp_genderFROMusersGROUPBYucity,ugenderWITHROLLUP;学习提示GROUPING函数必须跟WITHROLLUP关键字一起使用（5）GROUPBY和HAVING一起使用知识点介绍HAVING关键字也是用来指定筛选条件，但它只能跟GROUPBY一起使用，用于对分组后的结果集进行筛选。【例4.43】查询users表，统计各城市的用户人数，显示人数在3人及以上的城市。实例演示SELECTucity,COUNT(*)FROMusersGROUPBYucityHAVINGCOUNT(*)>=3;【例4.44】查询goods表，统计每类商品的总销售额，列出总销售额在3000及以上的类别id和总销售额（列名为sale_total）。实例演示SELECTcid,SUM(gprice*gsale_qty)assale_totalFROMgoodsGROUPBYcidHAVINGsale_total>=3000;使用窗口函数分析数据窗口函数也称为OLAP函数（OnlineAnallyticalProcessing，联机分析处理），能对查询结果集进行实时分析处理，是MySQL8.0新增内容。备课纸适用班级：_P.窗口函数主要功能是对结果集进行数据分析处理，原则上只能写在SELECT子句中。语法格式<窗口函数>OVER(PARTITIONBY<分组列名>ORDERBY<排序列名>)[[AS]别名]

OVER关键字用来指定函数执行的窗口范围。

PARTITIONBY子句：窗口按指定列进行分组，窗口函数在不同的分组上分别执行；

ORDERBY子句：指定排序列，窗口函数将按照排序后的记录顺序进行编号；学习提示窗口函数与使用GROUPBY分组聚合不同，它不会对结果产生额外的分组行，统计分析中输出的记录数与输入的记录数相同。【例4.45】查询goods表，按商品id顺序分析商品销售累计量，列出商品id、名称和累计销量（列名为acc_num）。实例演示SELECTgid,gname,gsale_qty,SUM(gsale_qty)OVER(ORDERBYgid)asacc_numFROMgoods;【例4.46】查询goods表，按商品id顺序给每行记录加行号（列名为row_no）。实例演示SELECTROW_NUMBER()OVER(ORDERBYgid)asrow_no,cid,gname,gsale_qtyFROMgoods;课堂练习【例4.47】查询goods表，分析每件商品的销售量排名（列名为ranking），列出类别ID、商品名称和销售量排名。学生演示SELECTcid,gname,gsale_qty, RANK()OVER(ORDERBYgsale_qtyDESC)asranking, DENSE_RANK()OVER(ORDERBYgsale_qtyDESC)asdesc_rankFROMgoods;课堂练习【例4.48】查询goods表，分析每类商品中各商品的销售量排名（列名为ranking），列出类别ID、商品名称和销售量排名。备课纸适用班级：_P.学生演示SELECTcid,gname,gsale_qty,RANK()OVER(PARTITIONBYcidORDERBYgsale_qtyDESC)asrankingFROMgoods;任务3连接查询多表数据任务描述实际应用开发中，业务逻辑所关联的数据通常会涉及两张以上的数据表。连接是多表数据查询的一种有效手段，本任务阐述连接查询中的交叉连接、内连接和外连接，以及联合查询等方式，灵活构建多表查询，以满足实际应用需要。教学引入前面学习了很多重要的数据查询技巧，但都只是对某一张表的检出数据进行相关操作,根据关系数据库数据表设计原则,数据的提取需来自多个数据表。带着问题开本任务讨论如下知识内容：展本章学习（1）怎样进行多表查询？使用表的别名的必要性？激发学生兴（2）多表查询时应考虑哪些因素？趣（3）综合多表数据的方法？问题情景:在网上商城系统中，如果想获取某个用户所购商品的详细信息，这些数据就需要来自三张不同的数据表———采用表的联接来实现连接查询简介连接查询由SELECT语句的FROM子句中的JOIN关键字来实现。精讲SELECT[ALL|DISTINCT]*|列名1[,列名2,…,列名n]FROM表1[别名1][CROSS|INNER|LEFT|RIGHT]JOIN表2[别名2][ON表1.关系列=表2.关系列|USING(列名)];

JOIN：泛指各类连接操作的关键字，具体含义如表备课纸适用班级：_P.

ON连接条件表达式：指定连接的条件。交叉连接无该子句。略讲交叉连接交叉连接返回的结果集是被连接的两张表的笛卡儿积。【例4.49】查询会员能购买的所有可能的商品。列出用户名和商品名称。SELECTuname,gnameFROMusersCROSSJOINgoods;学习提示在一个规范化的数据库中使用交叉连接无太多应用价实际意义，但可以利用它为数据库生成测试数据，帮助理解连接查询的运算过程。内连接内联接是MySQL缺省的联接方式，仅当一个表中的一些行在另一个表中也有相应的行时，它忽略所有不符合ON子句指定的联接条件的行。精讲+演示【例4.50】查询goods表，列出所有商品id、名称、类别id和类别名称。SELECTgid,gname,category.cid,cnameFROMcategoryJOINgoods ONcategory.cid=goods.cid;使用表的别名学习提示由于命名规则的限制以及为了防止重名，有些数据库表名称会非常长并且复杂。为了增加脚本查询的可读性和可维护性，使用表的别名增加语句的可读性，有利于写复杂的联接操作，简化T-SQL脚本维护联接一个表和它自身时，必须使用别名备课纸适用班级：_P.【例4.51】查询goods表，列出所有“图书”类商品的id、名称、类别id和类别名称。SELECTgid,gname,g.cid,cnameFROMcategorycJOINgoodsg--category表的别名为c，goods表的别名指定为g ONc.cid=g.cidWHEREcname='图书';学习提示两张表在进行连接时，连接列字段的名称可以不同，但要求必须具有相同数据类型、长度和精度，且表达同一范畴的意义，通常连接字段一般是数据表的主键和外键。学习提示使用内连接后，仍可使用SELECT语句对单表数据查询的所有语法。当连接的表超过两张表时，需要分别为每个JOIN连接指定连接条件。精讲+演示【例4.52】查询用户名（uname）为“段湘林”的购物车信息，列出购物车中的商品id,商品名称、价格及购买数量。SELECTg.gid,gname,gprice,cnumFROMuserssJOINcartcONs.uid=c.uid JOINgoodsgONg.gid=c.gidWHEREuname='段湘林';第二种方案在多张表进行连接时，查询的连接可以先使用JOIN将所有表连接起来，再使用ON关键字写出多个连接条件SELECTg.gid,gname,gprice,cnumFROMuserssJOINcartcJOINgoodsg ONs.uid=c.uidANDg.gid=c.gidWHEREuname='段湘林';第三种方案在JOIN连接中，当连接条件由两张表相同名称且类型相同的字段相连时，可以使用USING(列名)来连接备课纸适用班级：_P.SELECTg.gid,gname,gprice,cnumFROMusersJOINcartUSING(uid) JOINgoodsgUSING(gid)WHEREuname='段湘林';自连接自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一个表。精讲+演示【例4.53】查询与用户“段湘林”在同一城市的用户，列出用户名、邮箱和城市名。SELECTu1.uname,u1.uemail,u1.ucityFROMusersu1JOINusersu2 ONu1.ucity=u2.ucityWHEREu2.uname='段湘林'ANDu1.uname!='段湘林';外连接连接返回的结果集除了包括符合连接条件的记录行外，还会返回FROM子句中至少一个表中的所有行，不满足条件的数据列显示为NULL。左外联接（LEFTJOIN）精讲+演示结果表中除了包括满足连接条件的行外，还包括左表的所有行【例4.54】查询每个用户的订单信息，列出用户id、用户名和订单金额。SELECTu.uid,uname,oamountFROMusersuLEFTJOINorderso ONu.uid=o.uid;右外联接（RIGHTJOIN）结果表中除了包括满足连接条件的行外，还包括右表的所有行精讲+演示【例4.55】查询每个用户的订单数，列出用户id、用户名和订单数（order_num）。SELECTu.uid,uname,count(o.uid)asorder_numFROMordersoRIGHTJOINusersu ONo.uid=u.uidGROUPBYu.uid;备课纸适用班级：_P.学习提示左外连接和右外连接的操作相同，区别在于表相对于JOIN关键字的位置不同。联合查询问题情景对于数据库中学生管理时，一般每个班都有相应的数据表，如果想汇总一个系，的所有学生情况，应该怎么办？讨论----采用表的联合使用UNION操作符可以从多个查询里产生单个结果集SELECT语句1UNION[ALL]SELECT语句2[UNION[ALL]<SELECT语句3>][...n]精讲+演示【例4.45】联合查询uID值为1和2的用户信息，列出uID，uName，uSex。SELECTuID,uName,uSexFROMusersWHEREuid=1UNIONSELECTuID,uName,uSexFROMusersWHEREuid=2;课堂练习【例4.46】联合查询tid值为1和2的商品信息，列出tid，gdName，gdPrice，并按gdPrice从高到低排序，显示前三行记录。SELECTtId,gdName,gdPriceFROMgoodsWHEREtId=1UNIONSELECTtId,gdName,gdPriceFROMgoodsWHEREtId=2ORDERBYgdPriceDESCLIMIT3;备课纸适用班级：_P.学习提示JOIN可以看作是将表进行水平组合，而UNION则是将表进行垂直组合UNION和JOIN的区别类比两个关JOIN是合并多个表并生存一个单独的结果集，该结果集将包含多键字个表中的字段UNION是把多个SELECT语句返回的结果集合并到一个结果集中使用操作符UNION，要求所引用的表必须具有相似的数据类型、相同的字段数，每个查询中的选择列表必须具有相同的顺序使用操作符JOIN，只要求联接的表共同拥有某些字段用UNION分解复杂的查询会提高查询速度，而联接表越多，查询速度越慢任务4嵌套查询多表数据任务描述子查询是多表数据查询的另一种有效方法，当数据查询的条件依赖于其他查询的结果时，使用子查询可以有效解决此类问题。课程引入子查询本质上是一个SELECT语句，通常它嵌套在其他的SELECT、INSERT、UPDATE、DELETE等语句中，作为联接的替代选择或者作为单独的表达式表示较复杂的查询条件。带着问题开本任务讨论如下内容：展本章学习（1）为什么要用子查询？激发学生兴（2）子查询的使用场合趣（3）子查询的分类及实现方法？问题情景：设想我们有一个网上书店管理系统。我们使用authors表存储作者的相关信息，使用books表存储书的相关信息。那么，如果我们需要查找北京地区作者的全部图书时，就可以采用子查询。备课纸适用班级：_P.子查询简介子查询是将一个复杂的查询分解成一系列的简单查询，通常在一个查询需要另外一个查询的结果时使用。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询设问：子查询或外部选择。许多包含子查询的SQL语句都可以改为用联接表示。与联接的异而其它一些问题只能由子查询提出。在Transact-SQL中，包括子查询的语句同？和不包括子查询但语义上等效的语句在性能方面通常没有区别。但是，在一些类比法必须检查存在性的情况中，使用联接会产生更好的性能。否则，为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。-------学生参与讨论使用子查询的注意事项子查询要用括号括起来只要需要返回一个值或一系列值，就可以使用子查询不能使用子查询检索数据类型为text或image的字段子查询可以再包含子查询，至多可嵌套32层子查询的类型把子查询用作表达式把子查询用作派生表使用子查询关联数据子查询用作表达式在SQL语句中，所有使用表达式的地方，都可以使用子查询代替当子查询被用作表达式时，子查询可以认为是被当作一个表达式处理并计算。查询优化器总是将表达式等同于联接一个只有一行记录的表在整个语句中，只计算一次备课纸适用班级：_P.1.使用比较运算符的子查询当子查询的结果返回为单个值时，通常可以用比较运算符为外层查询提供比较表达式比较运算符(子查询)演示+精讲【例4.59】查询用户名为“郭辉”的订单信息，列出订单编号、订单金额和下单时间。SELECTocode,oamount,ordertimeFROMordersWHEREuid=(SELECTuid FROMusers WHEREuname='郭辉');【例4.60】查询比"乐器"类商品销售总价高的商品类别，列出类别id和总销售额（sale_total）。实例演示SELECTcid,SUM(gprice*gsale_qty)assale_totalFROMgoodsGROUPBYcidHAVINGsale_total>(SELECTSUM(gprice*gsale_qty) FROMgoodsgJOINcategoryc onc.cid=g.gid WHEREcname='乐器')2.使用IN关键字的子查询当子查询的结果返回为单列集合时，可以使用IN关键字表达式[NOT]IN(子查询)演示+精讲【例4.61】查询未购买过商品的会员，列出用户id、用户名、性别和出生年月。SELECTuid,uname,ugender,ubirthdayFROMusersWHEREuidNOTIN(SELECTuidFROMorders);课堂练习【例4.62】查询消费金额在3000元以上的用户，用户id、用户名、性别和出生年月。SELECTuid,uname,ugender,ubirthdayFROMusersWHEREuidIN(SELECTuid FROMorders GROUPBYuid HAVINGSUM(oamount)>=3000);备课纸适用班级：_P.3.使用ANY或ALL关键字的子查询当子查询的结果返回为单列集合时，还可以使用ANY或ALL关键字表达式比较运算符{ANY|SOME|ALL}(子查询)ALL则表示外层查询的表达式要与子查询的结果集中的所有值匹配ANY表示外层查询的表达式与子查询结果集中的值有一个匹配演示+精讲【例4.63】查询比“电脑及配件”类某一商品价格高的商品信息，包括商品id、名称和价格。SELECTgid,gname,gpriceFROMgoodsWHEREgprice>ANY(SELECTgprice FROMgoods WHERE(cid=(SELECTcid FROMcategory WHEREcname='电脑及配件')));【例4.64】查询比“电脑及配件”类商品价格都高的商品信息，包括商品编号、名称和价格。SELECTgid,gname,gpriceFROMgoodsWHEREgprice>ALL(SELECTgprice FROMgoods WHERE(cid=(SELECTcid FROMcategory WHEREcname='电脑及配件')));学习提示ANY或ALL运算符必须与比较运算符一起使用。子查询作为派生表子查询的结果集亦可放置在FROM子句后作为查询的数据源表，这种表称为派生表用子查询产生派生表时，子查询可以认为是：是查询语句中的一个结果集，被用作一个表备课纸适用班级：_P.代替了FROM子句中的表将与查询的其他部分一起优化实例讲解【例4.65】查询年龄在20以下的用户名、性别和年龄（age）。SELECT*FROM(SELECTuname,ugender,year(now())-year(ubirthday)asage FROMusers)AStbWHEREage<20;学习提示FROM后的子查询得到的是一张虚表，需要用AS子句为虚表定义一个表名。此外，列的别名不能用作WHERE子句后的条件表达式，当需要使用别名作为过滤条件时，可以使用子查询作为派生表。课堂练习:采用子查询用作派生表问题情景若学生信息系统中有两个表：“学生信息表”和“系信息表”。在学生信息表中有列“学号”、列“姓名”、列“出生日期”、列“系号”，在系信息表中有列“系号”、列“系名”、列“系主任”。列“系号”是学生信息表引用系信息表的外键。相关子查询使用相关子查询时，内层子查询被反复执行。外层查询有多少记录，内层查询就被执行多少次,执行过程如下：子查询为外层查询的每一行记录执行一次，外层查询将子查询引用的列传递给子查询中引用列进行比较。若子查询中有行与其匹配，外层查询则取出该行放入结果集重复执行以上操作，直至所有外层查询的表的每一行都处理完。1．使用EXISTS关键字的子查询使用EXISTS的子查询不需要返回任何实际数据，而仅返回一个逻辑值[NOT]EXISTS(子查询)演示+精讲【例4.66】使用EXISTS关键字实现【例4.60】。查询未购买过商品的会员，列出用户id、用户名、性别和出生年月。备课纸适用班级：_P.SELECTuid,uname,ugender,ubirthdayFROMusersWHERENOTEXISTS(SELECT*FROMorders WHEREuid=users.uid);结果分析本例由于使用EXISTS关键字的子查询不需要返回实际数据，所以这种子查询的SELECT子句中的结果列表达式通常用“*”，给出列名没有意义。同时该子查询依赖于外层的某个列值，在本例中子查询依赖外层查询users表的uid。演示+精讲2．计算相关子查询相关子查询还可以嵌套在SELECT子句的目标列中，通过子查询计算出关联数据的目标列。【例4.67】修改【例4.60】。查询比"乐器"类商品销售总价高的商品类别，列出类别名称和总销售额（sale_total）。SELECT(SELECTcnameFROMcategoryWHEREcid=goods.cid)ascname,SUM(gprice*gsale_qty)assale_totalFROMgoodsGROUPBYcidHAVINGsale_total>(SELECTSUM(gprice*gsale_qty) FROMgoodsgJOINcategoryc onc.cid=g.gid WHEREcname='乐器');学习提示相关子查询是动态执行的子查询，是与外层查询行非常有效的连接查询类比连接查询和子查询的区别如下。（1）连接查询可以合并两个或多个表中数据，而子查询的SELECT语句的结果只能来自一个表。（2）几乎所有在连接查询中使用JOIN运算符的查询都可以写成子查询，对于数据库程序员来说，把SELECT语句以连接格式进行编写，更容易阅读和理解。（3）当需要即时计算聚合值并把该值用在外层查询中进行比较时，子查询比连接查询更容易实现。

备课纸适用班级：_P.推荐操作:使用子查询分解复杂的查询在相关子查询中使用表的别名尽量使用操作符EXISTS，而不是IN操作符子查询用于更新数据子查询不仅可以构造复杂的查询逻辑，当数据更新需要依赖某一个查询的结果集，使用子查询是一种有效的手段。1.查询结果集作为插入的数据源使用INSERT…SELECT语句可以把查询结果集添加到现有表中，比使用多个单行的INSERT语句效率要高得多。语法格式INSERT[INTO]表名[(列名1,列名2,…,列名n)]SELECT列名1[,列名2,…,列名n]FROM表名WHERE条件表达式演示+精讲【例4.68】创建商品历史表goods_history，并将销售量小于等于2且上架时间超过60天的商品下架处理，并将这些商品添加到goods_history表中。#创建商品历史表goods_history，其结构与商品表goods相同CREATETABLEgoods_historyLIKEgoods;#将满足条件的商品插入到goodsHistory表中INSERTINTOgoods_historySELECT*FROMgoodsWHEREgsale_qty<=2ANDDATEDIFF(NOW(),gaddtime)>=60;学习提示在使用INSERT…SELECT语句时，必须保证目标表中列的数据类型与源表中相应列的数据类型一致；必须确定目标表中列是否存在默认值，或所有被忽略的列是否允许为空，如果不允许为空，就必须为这些列提供值。2.子查询用于修改数据当数据的更新需要依赖于其他的表的数据时，可以使用子查询作为UPDATE的更新条件。

备课纸适用班级：_P.推荐操作:使用子查询分解复杂的查询在相关子查询中使用表的别名尽量使用操作符EXISTS，而不是IN操作符任务5修改系统数据任务描述数据库是存放数据的仓库，对数据表进行数据的添加、更新和删除是最基本的操作。实际开发中，众多业务都需要对系统数据进行更改，如在网上商城系统中，用户注册、将商品加到购物车，修改或删除购物车中的商品、提交订单等操作都会使系统数据发生更改教学引入：对数据库的操作除了查询外,还经常需要插入、删除和更改数据，而数据的更新会引发出一系列相应的操作，这就需要事务来进行绑定。例：多米勒骨牌带着问题开本任务讨论如下知识内容。展本章学习（1）怎样进行数据的插入、修改、删除操作？激发学生兴（2）做以上操作时，应注意些什么，性能如何？趣插入数据精讲可以通过指定一组值或由SELECT语句生成的结果作为插入值，由事务来插入数据。还可创建一张表，同时插入数据，不必为一行中所有字段插入数据。1.使用Navicat图形工具插入数据操作略2.使用INSERT语句插入单条数据备课纸适用班级：_P.语法讲解INSERTINTO表名[(字段列表)]VALUES(值列表);字段列表：指定需要插入的字段名，必须用圆括号将字段列表括起来，字段与字段间用逗号分隔；当向表中的每个字段都提供值时，字段列表可以省略。VALUES：指示要插入的数据值列表。值列表的顺序必须与字段列名中指定的列一一对应。若字段列表缺省时，则按表结构中列的顺序提供值。实例精讲【例3.46】向category表添加新记录，其中cname的值为“运动”，cid的值为7。mysql>INSERTINTOcategoryVALUES(7,'运动');若cid设置了自增长，则cid的值可以用null表示，此时系统会根据该列已有的值自动增长，语句改写如下。mysql>INSERTINTOcategoryVALUES(null,'运动');学习提示向表中插入记录时，表定义中标识为NOTNULL且无默认值或自增长的字段必须提供值，否则插入操作将失败。3.使用REPLACE语句插入单条数据使用REPLACE语句也可以插入记录，其语法同INSERT语句相似。REPLACEINTO表名[(字段列表)]VALUES(值列表);实例+演示【例3.49】使用REPLACE语句，操作【例3.48】。mysql>REPLACEINTOusers(uid,ulogin,uname,upwd)->VALUES(1,,'刘立','111');QueryOK,2rowsaffected(0.04sec)学习提示使用关键字REPLACE时，首先尝试将记录插入到数据表中，若检测到表中已经有主键值相同的记录，则执行替换记录操作。4.使用INSERT语句插入多条数据使用INSERT关键字插入数据时，一次可以插入多条记录INSERTINTO表名[(字段列表)]VALUES(值列表1)[(值列表2),…(值列表n)];备课纸适用班级：_P.实例+演示【例3.44】向user表中添加三条新记录。mysql>INSERTINTOusers(ulogin,uname,upwd)->VALUES(,'郑霞','asd'),->(,'刘红','555'),->(,'朱小兰','123');QueryOK,3rowsaffected(0.04sec)Records:3Duplicates:0Warnings:05.使用REPLACE语句插入多条数据实例+演示【例3.45】向user表中添加三条新记录，如果记录有重复的实行替换mysql>REPLACEINTOusers(uid,uname,ugender,upwd,ulogin)->VALUES(2,'关关','女','qaz',),->(4,'李兰','女','666',),->(5,'张顺','男','333',);QueryOK,5rowsaffected(0.04sec)Records:3Duplicates:2Warnings:0结果分析从结果集显示可以看出，uID为2和4的记录执行了替换操作，uID为5的记录则执行了插入操作。6.INSERT语句的其他语法格式使用INSERT语句插入数据还可以使用赋值语句的形式INSERTINTO表名SET字段名1=值1[,字段名2=值2,…]实例+演示【例3.52】向users表中添加记录，其中ulogin为，uname的值为“曲甜甜”，upwd值为“666”，usex的值为“女”。mysql>INSERTINTOusers->SETulogin=,->uname='曲甜甜',->upwd='666',->ugender='女';QueryOK,1rowaffected(0.04sec)备课纸适用班级：_P.修改数据UPDATE语句用于更新数据表中的数据。UPDATE表名SET字段名1=值1,字段名2=取值2,…,字段名n=取值n[WHERE条件表达式];实例+演示【例3.53】修改users表，将用户刘红的性别修改为女。mysql>UPDATEusers->SETugender='女'->WHEREuname='刘红';QueryOK,1rowaffected(0.04sec)Rowsmatched:1Changed:1Warnings:0结果分析从结果可以看出1行数据受到影响，其中“Rowsmatched:1”表示1行数据匹配成功，“Changed:1”表示1行数据被改变学习提示当不带条件表达式更新表时，表中所有的记录都会受到影响，操作前需慎重确认全部修改的必要性。删除数据删除数据是指删除表中不再需要的记录。1.使用DELETE语句删除数据语法讲解DELETEFROM表名[WHERE条件表达式];实例+演示【例3.55】删除users表中性别为“男”的用户。mysql>DELETEFROMusers->WHEREugender='男';QueryOK,2rowsaffected(0.04sec)实例+演示【例3.56】删除users表所有记录。mysql>DELETEFROMusers;QueryOK,4rowaffected(0.00sec)备课纸适用班级：_