MySQL数据库技术周德伟电子课件单元5数据查询

单元五数据查询单元五数据查询项目知识要点与目标项目知识要点知识能力目标学时5.1任务1单表查询5.1.1SELECT语句定义5.1.2选择指定的列5.1.3聚合函数5.1.4WHERE子句5.2任务2多表查询5.2.1FROM子句5.2.2多表连接5.3任务3排序与分类汇总5.3.1GROUPBY子句5.3.2HAVING子句5.3.3ORDERBY子句5.4综合实例PetStore数据查询【情境】：Petstore数据查询【职业岗位】：操作员、程序员【典型工作任务】：查询记录、多表查询、子查询【技能要求】：1、使用MySQL命令完成多功能数据查询；2、使用图形工具完成多功能数据查询【知识要求】：1、简单查询、分类汇总2、联接和嵌套查询3、数据操作的SQL语句8项目实训五“员工管理系统”数据库查询操作8项目知识要点与目标项目知识要点知识能力目标学时5.1任务任务一单表查询

使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过SQL语句的查询可以从表或视图中迅速方便地检索数据。一、SELECT语句

语法格式：SELECT[ALL|DISTINCT]输出列表达式,...[FROM表名1[,表名2]…] /*FROM子句*/[WHERE条件]/*WHERE子句*/[GROUPBY{列名

|表达式

|列编号}[ASC|DESC],... /*GROUPBY子句*/[HAVING条件] /*HAVING子句*/[ORDERBY{列名

|表达式

|列编号}[ASC|DESC],...]/*ORDERBY子句*/[LIMIT{[偏移量,]行数|行数OFFSET偏移量}] /*LIMIT子句*/

顺序严格地排序。例如，一个HAVING子句必须位于GROUPBY子句之后，并位于ORDERBY子句之前。

任务一单表查询使用数据库和表的主要目的是存储数选择列选择指定的列使用SELECT语句选择一个表中的某些列的语法格式如下：SELECT

字段列表FROM

表名称各列名之间要以逗号分隔。【例5.1】 查询Bookstore数据库的Members表中各会员的会员姓名、联系电话和注册时间。

USEBookstore;SELECT会员姓名,联系电话,注册时间FROMMembers;说明：当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列，如要显示Members表中所有列，不必将所有字段名一一列出，使用SELECT*FROMMembers;选择列选择指定的列列别名2.定义列别名当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列别名。语法格式为：SELECT

column_name[AS]

column_alias【例5.2】查询Book表中图书类别为计算机的图书书名、作者和出版社，结果中各列的标题分别指定为name、auther和publisher。SELECT书名ASname,作者ASauther,出版社ASpublisherFROMBookWHERE图书类别='计算机';注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。SELECT学号AS'Studentnumber',姓名AS'Studentname',总学分ASmarkFROMXSWHERE专业名='计算机';列别名2.定义列别名替换查询结果中的数据3.替换查询结果中的数据在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2

……ELSE表达式END替换查询结果中的数据3.替换查询结果中的数据【例5.3】查询Book表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为“尚未进货”；若数量小于5，替换为“需进货”；若数量在5-50之间，替换为“库存正常”；若总学分大于50，替换为“库存积压”。列标题更改为“库存”。SELECT图书编号,书名,CASE WHEN数量ISNULLTHEN'尚未进货' WHEN数量<5THEN'需进货' WHEN数量>=5and数量<=50THEN'库存正常' ELSE'库存积压' ENDAS库存

FROMBook;替换查询结果举例【例5.3】查询Book表中图书编号、书名和数量，对其库存计算列值4.计算列值使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECTexpression[,expression]【例5.4】对Sell表已发货的记录计算订购金额（订购金额=订购册数*订购单价），并显示图书编号和订购金额。SELECT图书编号,订购册数*订购单价AS订购金额

FROMSellWHERE是否发货='已发货';计算列值4.计算列值消除结果集中的重复行5.消除结果集中的重复行对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行，其格式是：SELECTDISTINCT

column_name[,column_name…]其含义是对结果集中的重复行只选择一个，保证行的唯一性。【例5.5】对Book表只选择图书类别和出版社，消除结果集中的重复行。SELECTDISTINCT图书类别,出版社

FROMBook;消除结果集中的重复行5.消除结果集中的重复行课堂练习对YGGL数据库完成以下查询：1、查询每个雇员的的地址和电话，显示的列标题要求显示“地址”、“电话”。2、查询employees表部门号和性别，要求消除重复行3、计算每个雇员的实际收入4、查询employees表中员工的姓名和性别，要求sex值为1时显示为“男”，为0时显示为“女”。5、查询Salary表中员工的员工编号和收入水平，要求2000元以下显示为“低收入”，2000-3000显示为“中等收入”，3000元以上时显示为“高收入”。课堂练习对YGGL数据库完成以下查询：聚合函数SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。函数名说明COUNT求组中项数，返回int类型整数MAX求最大值MIN求最小值SUM返回表达式中所有值的和AVG求组中值的平均值聚合函数SELECT子句的表达式中还可以包含所谓的聚合函数。COUNT函数（1）COUNT函数聚合函数中最经常使用的是COUNT()函数，用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。

语法格式为：COUNT({[ALL|DISTINCT]

expression

}|*)其中，expression是一个表达式，其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含NULL值。COUNT函数（1）COUNT函数【例5.6】求会员总人数。SELECTCOUNT(*)AS'会员数'FROMMembers;【例5.7】统计已结清的订单数。SELECTCOUNT(是否结清)AS'已结清的订单数'FROMSell;注意：这里COUNT(是否结清)只统计是否结清列中不为NULL的行。【例5.8】统计订购册数在5以上的订单数。SELECTCOUNT(订购册数)AS'订购册数在5以上的订单数‘

FROMSellWHERE订购册数>5;COUNT函数举例【例5.6】求会员总人数。COUNT函数举例MAX和MIN函数（2）MAX和MINMAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：MAX/MIN([ALL|DISTINCT]

expression)其中，expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。【例5.9】求订购了图书编号为ISBN8-5006-6625-X的订单的最高订购册数和最低订购册数。SELECTMAX(订购册数),MIN(订购册数)FROMSellWHERE图书编号='ISBN8-5006-6625-X';注意：当给定列上只有空值或检索出的中间结果为空时，MAX和MIN函数的值也为空。MAX和MIN函数（2）MAX和MINSUM函数和AVG函数（3）SUM函数和AVG函数SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM/AVG([ALL|DISTINCT]

expression)其中，expression是常量、列、函数或表达式，其数据类型只能是数值型。【例5.10】求订购了图书编号为ISBN8-5006-6625-X图书的订购总册数。SELECTSUM(订购册数)AS'订购总册数'FROMSellWHERE图书编号='ISBN8-5006-6625-X';【例5.11】求订购图书编号为ISBN8-5006-6625-X图书的订单平均册数。SELECTAVG(订购册数)AS'每笔订单平均册数'FROMSellWHERE图书编号='ISBN8-5006-6625-X';SUM函数和AVG函数（3）SUM函数和AVG函数课堂练习对YGGL数据库完成以下查询：1、计算员工总数2、计算salary表中员工月收入的平均数3、计算所有员工的总支出4、显示最高收入和最低收入的员工的员工号课堂练习对YGGL数据库完成以下查询：WHERE子句WHERE子句必须紧跟FROM子句之后，在WHERE子句中，使用一个条件从FROM子句的中间结果中选取行。其基本格式为：WHERE

列运算符值操作符描述=等于<>不等于>大于<小于>=大于等于<=小于等于BETWEEN在某个范围内LIKE搜索某种模式WHERE子句WHERE子句必须紧跟FROM子句之后，在WH比较运算WHERE子句会根据条件对FROM子句的中间结果中的行一行一行地进行判断，当条件为TRUE的时候，一行就被包含到WHERE子句的中间结果中。在SQL中，返回逻辑值（TRUE或FALSE）的运算符或关键字都可称为谓词。判定运算包括比较运算、模式匹配、范围比较、空值比较和子查询。1.比较运算比较运算符用于比较（除TEXT和BLOB类型外)两个表达式值，MySQL支持的比较运算符有：=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）。当两个表达式值均不为空值（NULL）时，除了“<=>”运算符，其他比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN。比较运算WHERE子句会根据条件对FROM子句的中间结果中的【例5.12】查询Bookstore数据库Book表中书名为“网页程序设计”的记录。SELECT书名,单价FROMBookWHERE书名='网页程序设计';【例5.13】查询Book表中单价大于30的图书情况。SELECT*FROMBookWHERE单价>30;MySQL有一个特殊的等于运算符“<=>”，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。没有UNKNOWN的情况。【例5.14】查询Sell表中还未收货的订单情况。SELECT订单号,是否收货FROMSellWHERE是否收货<=>NULL;比较运算举例【例5.12】查询Bookstore数据库Book表中书名逻辑运算通过逻辑运算符（AND、OR、XOR和NOT）组成更为复杂的查询条件。逻辑运算操作的结果是“1”或“0”，分别表示“true”或“false”。符号1符号2说明示例说明not!非运算!x如果x是"true"，那么示例的结果是"false"；如果x是"false"，那么示例的结果是"true"。or||或运算x||y如果x或y任一是"true"，那么示例的结果是"true"，否则示例的结果是"false"。and&&与运算x&&y如果x和y都是"True"，那么示例结果是"true"，否则示例的结果是"false"。xor^异或运算x^y如果x和y不相同，那么示例结果是"true"，否则示例的结果是"false"。逻辑运算通过逻辑运算符（AND、OR、XOR和NOT）组成更逻辑运算举例非：selectnot0,not1,notnull;或：select(1or0),(0or0),(1ornull),(1or1),(nullornull);与：select(1and1),(0and1),(3and1),(1andnull);异或：select(1xor1),(0xor0),(1xor0),(0xor1),(nullxor1);

逻辑运算举例非：selectnot0,not1,n【例5.15】查询Sell表中已收货且已结清的订单情况。SELECT订单号,是否收货,是否结清FROMSellWHERE是否收货='已收货'AND是否结清='已结清';

【例5.16】查询Book表中清华大学出版社和北京大学出版社出版的价格大于25元的图书。SELECT书名,出版社,单价FROMBookWHERE(出版社='清华大学出版社'OR出版社='北京大学出版社')AND单价>=25;或：

SELECT书名,出版社,单价FROMBookWHERE(出版社='清华大学出版社'AND单价>=25)OR(出版社='北京大学出版社'AND单价>=25);【思考题】以下语句能否得到正确结果？为什么？

SELECT书名,出版社,单价FROMBookWHERE出版社='清华大学出版社'OR出版社='北京大学出版社'AND单价>=25;WHERE子句举例【例5.15】查询Sell表中已收货且已结清的订单情况。W课堂练习对YGGL数据库完成一下查询：1、显示月收入高于2000元的员工号码2、查询1970年以后出生的员工的姓名和地址3、显示女雇员的地址和电话，列标题要求用中文“地址”、“电话”表示课堂练习对YGGL数据库完成一下查询：LIKE运算符2.模式匹配（LIKE运算符）LIKE运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。使用LIKE进行模式匹配时，常使用特殊符号_和%，可进行模糊查询。“%”代表0个或多个字符，“_”代表单个字符。由于MySQL默认不区分大小写，要区分大小写时需要更换字符集的校对规则。【例5.17】查询Members表中姓“张”的会员的身份证号、姓名及注册时间。SELECT身份证号,会员姓名,注册时间FROMMembersWHERE会员姓名LIKE'张%';【例5.18】查询Book表中图书编号倒数第二位为T的图书的图书编号和书名。SELECT图书编号,书名FROMBookWHERE图书编号LIKE'%T_';例[5.19】查询Book表中书名中包含下画线的图书。

SELECT图书编号,书名FROMBookWHERE书名LIKE'%#_%'ESCAPE'#';LIKE运算符2.模式匹配（LIKE运算符）范围比较3.范围比较用于范围比较的关键字有两个：BETWEEN和IN。当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：expression[NOT]BETWEEN

expression1

AND

expression2当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。注意：expression1的值不能大于expression2的值。【例5.20】查询Book表中2010年出版的图书的情况。SELECT*FROMBookWHERE出版时间BETWEEN'2010-1-1'AND'2010-12-31';范围比较3.范围比较使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expression

IN(expression[,…n])【例5.21】查询Book表中“高等教育出版社”、“北京大学出版社”和“人民邮电出版社”出版的图书的情况。SELECT*FROMBook

WHERE出版社IN('高等教育出版社','北京大学出版社','人民邮电出版社');说明：IN关键字最主要的作用是表达子查询。范围比较举例使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与空值比较4.空值比较当需要判定一个表达式的值是否为空值时，使用ISNULL关键字，格式为：expression

IS[NOT]NULL当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例5.22】查询Sell表中尚未发货的订单记录。SELECT*FROMSellWHERE是否发货ISNULL;本例即查找总学分为空的学生，结果为空。空值比较4.空值比较课堂练习对YGGL数据库完成一下查询：1、显示月收入高于2000元的员工号码2、查询1970年以后出生的员工的姓名和地址3、

查询支出在50-150之间的员工信息4、查询财务部、研发部、市场部的员工信息5、显示工作年限三年以上（含3年）、学历在本科以上（含本科）的男性员工的信息6、查找员工号码中倒数第二个数字为0的姓名、地址和学历7查找地址中包含’中山路’的员工的信息

课堂练习对YGGL数据库完成一下查询：任务二多表查询前面介绍了使用SELECT子句选择列，本小节讨论SELECT查询的对象（即数据源）的构成形式。SELECT的查询对象由FROM子句指定，其格式为：FROM表名1[[AS]别名1][,表名2[[AS]别名2]]…/*查询表*/|JOIN子句

/*连接表*/●引用一个表：可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：SELECT*FROMdb2.tb;当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。任务二多表查询前面介绍了使用SELECT子句选择列，本小FROM子句【例5.23】从Members表中检索出所有客户的信息，并使用表别名Users。SELECT*FROMMembersASUsers;●引用多个表：如果要在不同表中查询数据，则必须在FROM子句中指定多个表。指定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的连接。例如，在XSCJ数据库中需要查找选修了离散数学课程的学生的姓名和成绩，就需要将XS、KC和XS_KC三个表进行连接，才能查找到结果。【例5.24】查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。SELECTBook.书名,Sell.订购册数,Sell.订购时间

FROMBook,SellWHEREBook.图书编号=Sell.图书编号;FROM子句【例5.23】从Members表中检索出所有客全连接当数据查询涉及到多张表格时，要将多张表格的数据连接起来组成一张表格，连接的方式有多种。全连接全连接产生的新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。全连接可能得到的行数为每个表中行数之积。如表A有3行，表B有2行，表A和B全连接后得到6行（3x2=6)的表全连接当数据查询涉及到多张表格时，要将多张表格的数据连接起来全连接举例T1T2T3T4T51A13M6F20N2BT1T2T3T4T51A13M6F13M2B13M1A20N6F20N2B20M表A和B全连接后得到如下6行（3x2=6)的表：表A表B全连接举例T1T2T3T4T51A13M6F20N2BT1T等值连接T1T2T3T4T51A13M2B20NFROM子句各个表用逗号分隔，这样就指定了全连接。全连接潜在地产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这样的情形下，通常要使用WHERE子句设定条件来将结果集减少为易于管理的大小，这样的连接即为等值连接。若表A和B进行等值连接（T1=T3），则如下表所示，只有两行。【例5.24】查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。SELECTBook.书名,Sell.订购册数,Sell.订购时间

FROMBook,SellWHEREBook.图书编号=Sell.图书编号;等值连接T1T2T3T4T51A13M2B20NFROM子句JOIN连接2.JOIN连接第二种方式是使用JOIN关键字的连接，语法格式如下：table_referenceINNERJOINtable_reference

ONconditional_expr|USING(column_list)（1）内连接：指定了INNER关键字的连接是内连接。【例5.25】要实现例4.23中的结果，可以使用以下语句：SELECTBook.书名,Sell.订购册数,Sell.订购时间

FROMBookinnerjoinSellONBook.图书编号=Sell.图书编号;该语句根据ON关键字后面的连接条件，合并两个表，返回满足条件的行。JOIN连接2.JOIN连接内连接是系统默认的，可以省略INNER关键字。使用内连接后，FROM子句中ON条件主要用来连接表，其他并不属于连接表的条件可以使用WHERE子句来指定。【例

5.26】用JOIN关键字表达下列查询：查找购买了“网页程序设计”且订购数量大于5本的图书信息。

SELECT书名,订购册数FROMBookJOINSell

ONBook.图书编号=Sell.图书编号

WHERE书名='网页程序设计'AND订购册数>5;JOIN连接举例内连接是系统默认的，可以省略INNER关键字。JOIN连接举内连接还可以用于多个表的连接。【例5.27】用JOIN关键字表达下列查询：查找购买了“网页程序设计”且订购数量大于5本的图书和会员姓名和订购册数。SELECTBook.图书编号,会员姓名,书名,订购册数

FROMSellJOINBookONBook.图书编号=Sell.图书编号

JOINMembersONSell.身份证号=Members.身份证号

WHERE书名='网页程序设计'AND订购册数>5;多表连接举例内连接还可以用于多个表的连接。多表连接举例作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。【例5.28】查找BookStore数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数。SELECTa.订单号,a.图书编号,a.订购册数

FROMSellASaJOINSellASb

ONa.图书编号=b.图书编号wherea.订单号!=b.订单号;自表连接作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么ON条件也可以换成USING子句。USING（column_list）子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中column_list为两表中相同的列名。

【例5.29】查找Members表中所有订购过图书的会员姓名。SELECTDistinct会员姓名FROMMembersJOINSellUSING(身份证号);

查询的结果为Sell表中所有出现的身份证号对应的会员姓名。例5.29的语句与下列语句等价：SELECTDistinct会员姓名

FROMMembersJOINSellONMembers.身份证号=Sell.身份证号;USING子句如果要连接的表中有列名相同，并且连接的条件就是外连接指定了OUTER关键字的连接为外连接。外连接包括：●左外连接（LEFTOUTERJOIN）：结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。●右外连接（RIGHTOUTERJOIN）：结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。外连接指定了OUTER关键字的连接为外连接。外连接举例【例5.30】查找所有图书的图书编号、数量及订购了图书的会员身份证号，若从未订购过，也要包括其情况。SELECTBook.图书编号,Book.数量,身份证号

FROMBookLEFTOUTERJOINSellONBook.图书编号=Sell.图书编号;说明：

【例5.31】查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。

SELECT订单号,图书编号,订购册数,Members.会员姓名FROMSellRIGHTJOINMembersONMembers.身份证号=Sell.身份证号;外连接举例【例5.30】查找所有图书的图书编号、数量及订购子查询在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。SQL标准允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。（1）IN子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression[NOT]IN(subquery)其中，subquery是子查询。当表达式expression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。子查询在查询条件中，可以使用另一个查询的结果作为条子查询举例【例5.32】查找在Bookstore数据库中张三的订单信息。

SELECT* FROMSell WHERE身份证号IN

(SELECT身份证号FROMMembersWHERE会员姓名='张三');说明：在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。本例中，先执行子查询：

SELECT身份证号FROMMembersWHERE会员姓名='张三'得到一个只含有身份证号列的表。再执行外查询，若Sell表中某行的身份证号列值等于子查询结果表中的任一个值，则该行就被选择。子查询举例【例5.32】查找在Bookstore数据库中张子查询嵌套举例IN子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。【例5.33】查找购买了除“网页程序设计”以外图书的会员信息。要查找会员信息，先要知道会员的身份证号，而要知道购买了除“网页程序设计”以外图书的会员，可以按图书编号在Sell表中查到，但是“网页程序设计”的图书编号要通过查找Book才可以获得。SELECT*FROMMembers WHERE身份证号IN(SELECT身份证号FROMSellWHERE图书编号NOTIN

(SELECT图书编号FROMBookWHERE书名='网页程序设计'));子查询嵌套举例IN子查询只能返回一列数据。对于较复杂的查询，多表查询对YGGL数据库完成多表查询：1、查询每个雇员的基本情况和薪水情况2、查询“王林”的基本情况和所工作的部门名称3、查询月收入在2000~3000元的员工姓名和支出4、查询研发部在1970年以前出生的员工姓名和薪水情况5、使用子查询查找工资收入大于2000元的员工的基本信息6、查找在财务部工作的员工的基本信息7、查找住在“中山路”的员工的工作部门名称多表查询对YGGL数据库完成多表查询：任务三分类汇总与排序

GROUPBY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XS表中的所有行分组，结果是每个专业的学生成为一组。GROUPBY子句的语法格式如下：GROUPBY{列名

|表达式

|列编号}[ASC|DESC],...

[WITHROLLUP]GROUPBY子句后通常包含列名或表达式。MySQL对GROUPBY子句进行了扩展，可以在列的后面指定ASC（升序）或DESC（降序）。GROUPBY可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。任务三分类汇总与排序GROUPBY子句主要用于根据字段【例5.38】输出Book表中图书类别名。SELECT图书类别FROMBookGROUPBY图书类别;【例5.39】按图书类别统计Book表中各类图书的库存数。SELECT图书类别,COUNT(*)AS'库存数'FROMBookGROUPBY图书类别;【例5.40】按图书编号分类统计其订单数和订单的平均订购册数。SELECT图书编号,AVG(订购册数)AS'订购册数',COUNT(订单号)AS'订单数'FROMSellGROUPBY图书编号;【例5.38】输出Book表中图书类别名。带ROLLUP的GROUPBY子句

使用带ROLLUP操作符的GROUPBY子句，可指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。【例5.41】按图书类别、出版社分类统计Book表中各类图书的库存数。

SELECT图书类别,出版社,Sum(数量)AS'库存数'FROMBookGROUPBY图书类别,出版社;请将执行结果与以下语句比较：

SELECT图书类别,出版社,Sum(数量)AS'库存数'FROMBookGROUPBY图书类别,出版社WITHROLLUP;带ROLLUP的GROUPBY子句使用带ROLLHAVING子句使用HAVING子句的目的与WHERE子句类似，不同的是WHERE子句是用来在FROM子句之后选择行，而HAVING子句用来在GROUPBY子句后选择行。例如，查找XSCJ数据库中平均成绩在85分以上的学生，就是在XS_KC表上按学号分组后筛选出符合平均成绩大于等于85的学生。语法格式：

HAVING条件其中，where_definition是选择条件，条件的定义和WHERE子句中的条件类似，不过HAVING子句中的条件可以包含聚合函数，而WHERE子句中则不可以。SQL标准要求HAVING必须引用GROUPBY子句中的列或用于聚合函数中的列。不过，MySQL支持对此工作性质的扩展，并允许HAVING引用SELECT清单中的列和外部子查询中的列。HAVING子句使用HAVING子句的目的与WHERE子句类HAVING子句举例【例5.42】查找Sell表中每个会员平均订购册数在10本以上的会员的身份证号和平均订购册数。SELECT身份证号,AVG(订购册数)AS'平均订购册数'FROMSellGROUPBY身份证号

HAVINGAVG(订购册数)>10;【例5.43】查找Sell表中会员订单数在2笔以上且每笔订购册数都在5本以上的会员。SELECT身份证号

FROMSellWHERE订购册数>5GROUPBY身份证号

HAVINGCOUNT(*)>2;HAVING子句举例【例5.42】查找Sell表中每个会员ORDERBY子句在一条SELECT语句中，如果不使用ORDERBY子句，结果中行的顺序是不可预料的。使用ORDERBY子句后可以保证结果中的行按一定顺序排列。语法格式：

ORDERBY{列名

|表达式

|列编号}[ASC|DESC],...说明：ORDERBY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。例如，使用ORDERBY3表示对SELECT的列清单上的第3列进行排序。关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。ORDERBY子句在一条SELECT语句中，如果不使用ORORDERBY子句举例【例5.44】将Book表中记录按出版时间先后排序。SELECT*FROMBookORDERBY出版时间;【例5.45】将Sell表中记录按订购册数从高到低排列。SELECT*FROMSellORDERBY订购册数DESC;ORDERBY子句举例LIMIT子句LIMIT子句是SELECT语句的最后一个子句，主要用于限制被SELECT语句返回的行数。语法格式：LIMIT{[offset,]row_count|row_countOFFSEToffset}语法格式中的offset和row_count都必须是非负的整数常数，offset指定返回的第一行的偏移量，row_count是返回的行数。例如，“LIMIT5”表示返回SELECT语句的结果集中最前面5行，而“LIMIT3，5”则表示从第4行开始返回5行。值得注意的是初始行的偏移量为0而不是1。LIMIT子句LIMIT子句是SELECT语句的最后一个子句LIMIT子句举例【例5.46】查找Members表中注册时间最靠前的5位会员的信息。SELECT* FROMMembers ORDERBY注册时间

LIMIT5;【例5.47】查找Book表中从第4条记录开始的5条记录。SELECT* FROMBook ORDERBY学号

LIMIT3,5;为了与PostgreSQL兼容，MySQL也支持LIMITrow_countOFFSEToffset语法。所以将上面例子中的LIMIT子句换成“LIMIT5OFFSET3”，结果一样。LIMIT子句举例【例5.46】查找Members表中注册课堂练习对YGGL数据库完成查询：1、按部门列出该部门工作的员工人数2、按员工学历分组统计各种学历人数3、分别统计男性员工和女性员工人数4、查找雇员数超过2人的部门名称和员工数量5、将员工信息按出生日期从大到小排序6、将员工薪水按收入多少从小到大排序7、按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序课堂练习对YGGL数据库完成查询：综合练习对YGGL数据库完成查询：1、对salary表，计算每个雇员的实际收入（实际收入=收入-支出。2、查找员工号码中倒数第二个数字为0的姓名、地址和学历。3、按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序。4、查询研发部在1970年以前出生的员工姓名和薪水情况。综合练习对YGGL数据库完成查询：单元五数据查询单元五数据查询项目知识要点与目标项目知识要点知识能力目标学时5.1任务1单表查询5.1.1SELECT语句定义5.1.2选择指定的列5.1.3聚合函数5.1.4WHERE子句5.2任务2多表查询5.2.1FROM子句5.2.2多表连接5.3任务3排序与分类汇总5.3.1GROUPBY子句5.3.2HAVING子句5.3.3ORDERBY子句5.4综合实例PetStore数据查询【情境】：Petstore数据查询【职业岗位】：操作员、程序员【典型工作任务】：查询记录、多表查询、子查询【技能要求】：1、使用MySQL命令完成多功能数据查询；2、使用图形工具完成多功能数据查询【知识要求】：1、简单查询、分类汇总2、联接和嵌套查询3、数据操作的SQL语句8项目实训五“员工管理系统”数据库查询操作8项目知识要点与目标项目知识要点知识能力目标学时5.1任务任务一单表查询

使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过SQL语句的查询可以从表或视图中迅速方便地检索数据。一、SELECT语句

语法格式：SELECT[ALL|DISTINCT]输出列表达式,...[FROM表名1[,表名2]…] /*FROM子句*/[WHERE条件]/*WHERE子句*/[GROUPBY{列名

|表达式

|列编号}[ASC|DESC],... /*GROUPBY子句*/[HAVING条件] /*HAVING子句*/[ORDERBY{列名

|表达式

|列编号}[ASC|DESC],...]/*ORDERBY子句*/[LIMIT{[偏移量,]行数|行数OFFSET偏移量}] /*LIMIT子句*/

顺序严格地排序。例如，一个HAVING子句必须位于GROUPBY子句之后，并位于ORDERBY子句之前。

任务一单表查询使用数据库和表的主要目的是存储数选择列选择指定的列使用SELECT语句选择一个表中的某些列的语法格式如下：SELECT

字段列表FROM

表名称各列名之间要以逗号分隔。【例5.1】 查询Bookstore数据库的Members表中各会员的会员姓名、联系电话和注册时间。

USEBookstore;SELECT会员姓名,联系电话,注册时间FROMMembers;说明：当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列，如要显示Members表中所有列，不必将所有字段名一一列出，使用SELECT*FROMMembers;选择列选择指定的列列别名2.定义列别名当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列别名。语法格式为：SELECT

column_name[AS]

column_alias【例5.2】查询Book表中图书类别为计算机的图书书名、作者和出版社，结果中各列的标题分别指定为name、auther和publisher。SELECT书名ASname,作者ASauther,出版社ASpublisherFROMBookWHERE图书类别='计算机';注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。SELECT学号AS'Studentnumber',姓名AS'Studentname',总学分ASmarkFROMXSWHERE专业名='计算机';列别名2.定义列别名替换查询结果中的数据3.替换查询结果中的数据在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2

……ELSE表达式END替换查询结果中的数据3.替换查询结果中的数据【例5.3】查询Book表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为“尚未进货”；若数量小于5，替换为“需进货”；若数量在5-50之间，替换为“库存正常”；若总学分大于50，替换为“库存积压”。列标题更改为“库存”。SELECT图书编号,书名,CASE WHEN数量ISNULLTHEN'尚未进货' WHEN数量<5THEN'需进货' WHEN数量>=5and数量<=50THEN'库存正常' ELSE'库存积压' ENDAS库存

FROMBook;替换查询结果举例【例5.3】查询Book表中图书编号、书名和数量，对其库存计算列值4.计算列值使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECTexpression[,expression]【例5.4】对Sell表已发货的记录计算订购金额（订购金额=订购册数*订购单价），并显示图书编号和订购金额。SELECT图书编号,订购册数*订购单价AS订购金额

FROMSellWHERE是否发货='已发货';计算列值4.计算列值消除结果集中的重复行5.消除结果集中的重复行对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行，其格式是：SELECTDISTINCT

column_name[,column_name…]其含义是对结果集中的重复行只选择一个，保证行的唯一性。【例5.5】对Book表只选择图书类别和出版社，消除结果集中的重复行。SELECTDISTINCT图书类别,出版社

FROMBook;消除结果集中的重复行5.消除结果集中的重复行课堂练习对YGGL数据库完成以下查询：1、查询每个雇员的的地址和电话，显示的列标题要求显示“地址”、“电话”。2、查询employees表部门号和性别，要求消除重复行3、计算每个雇员的实际收入4、查询employees表中员工的姓名和性别，要求sex值为1时显示为“男”，为0时显示为“女”。5、查询Salary表中员工的员工编号和收入水平，要求2000元以下显示为“低收入”，2000-3000显示为“中等收入”，3000元以上时显示为“高收入”。课堂练习对YGGL数据库完成以下查询：聚合函数SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。函数名说明COUNT求组中项数，返回int类型整数MAX求最大值MIN求最小值SUM返回表达式中所有值的和AVG求组中值的平均值聚合函数SELECT子句的表达式中还可以包含所谓的聚合函数。COUNT函数（1）COUNT函数聚合函数中最经常使用的是COUNT()函数，用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。

语法格式为：COUNT({[ALL|DISTINCT]

expression

}|*)其中，expression是一个表达式，其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含NULL值。COUNT函数（1）COUNT函数【例5.6】求会员总人数。SELECTCOUNT(*)AS'会员数'FROMMembers;【例5.7】统计已结清的订单数。SELECTCOUNT(是否结清)AS'已结清的订单数'FROMSell;注意：这里COUNT(是否结清)只统计是否结清列中不为NULL的行。【例5.8】统计订购册数在5以上的订单数。SELECTCOUNT(订购册数)AS'订购册数在5以上的订单数‘

FROMSellWHERE订购册数>5;COUNT函数举例【例5.6】求会员总人数。COUNT函数举例MAX和MIN函数（2）MAX和MINMAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：MAX/MIN([ALL|DISTINCT]

expression)其中，expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。【例5.9】求订购了图书编号为ISBN8-5006-6625-X的订单的最高订购册数和最低订购册数。SELECTMAX(订购册数),MIN(订购册数)FROMSellWHERE图书编号='ISBN8-5006-6625-X';注意：当给定列上只有空值或检索出的中间结果为空时，MAX和MIN函数的值也为空。MAX和MIN函数（2）MAX和MINSUM函数和AVG函数（3）SUM函数和AVG函数SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM/AVG([ALL|DISTINCT]

expression)其中，expression是常量、列、函数或表达式，其数据类型只能是数值型。【例5.10】求订购了图书编号为ISBN8-5006-6625-X图书的订购总册数。SELECTSUM(订购册数)AS'订购总册数'FROMSellWHERE图书编号='ISBN8-5006-6625-X';【例5.11】求订购图书编号为ISBN8-5006-6625-X图书的订单平均册数。SELECTAVG(订购册数)AS'每笔订单平均册数'FROMSellWHERE图书编号='ISBN8-5006-6625-X';SUM函数和AVG函数（3）SUM函数和AVG函数课堂练习对YGGL数据库完成以下查询：1、计算员工总数2、计算salary表中员工月收入的平均数3、计算所有员工的总支出4、显示最高收入和最低收入的员工的员工号课堂练习对YGGL数据库完成以下查询：WHERE子句WHERE子句必须紧跟FROM子句之后，在WHERE子句中，使用一个条件从FROM子句的中间结果中选取行。其基本格式为：WHERE

列运算符值操作符描述=等于<>不等于>大于<小于>=大于等于<=小于等于BETWEEN在某个范围内LIKE搜索某种模式WHERE子句WHERE子句必须紧跟FROM子句之后，在WH比较运算WHERE子句会根据条件对FROM子句的中间结果中的行一行一行地进行判断，当条件为TRUE的时候，一行就被包含到WHERE子句的中间结果中。在SQL中，返回逻辑值（TRUE或FALSE）的运算符或关键字都可称为谓词。判定运算包括比较运算、模式匹配、范围比较、空值比较和子查询。1.比较运算比较运算符用于比较（除TEXT和BLOB类型外)两个表达式值，MySQL支持的比较运算符有：=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）。当两个表达式值均不为空值（NULL）时，除了“<=>”运算符，其他比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN。比较运算WHERE子句会根据条件对FROM子句的中间结果中的【例5.12】查询Bookstore数据库Book表中书名为“网页程序设计”的记录。SELECT书名,单价FROMBookWHERE书名='网页程序设计';【例5.13】查询Book表中单价大于30的图书情况。SELECT*FROMBookWHERE单价>30;MySQL有一个特殊的等于运算符“<=>”，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。没有UNKNOWN的情况。【例5.14】查询Sell表中还未收货的订单情况。SELECT订单号,是否收货FROMSellWHERE是否收货<=>NULL;比较运算举例【例5.12】查询Bookstore数据库Book表中书名逻辑运算通过逻辑运算符（AND、OR、XOR和NOT）组成更为复杂的查询条件。逻辑运算操作的结果是“1”或“0”，分别表示“true”或“false”。符号1符号2说明示例说明not!非运算!x如果x是"true"，那么示例的结果是"false"；如果x是"false"，那么示例的结果是"true"。or||或运算x||y如果x或y任一是"true"，那么示例的结果是"true"，否则示例的结果是"false"。and&&与运算x&&y如果x和y都是"True"，那么示例结果是"true"，否则示例的结果是"false"。xor^异或运算x^y如果x和y不相同，那么示例结果是"true"，否则示例的结果是"false"。逻辑运算通过逻辑运算符（AND、OR、XOR和NOT）组成更逻辑运算举例非：selectnot0,no