MySQL数据库技术 电子ppt课件单元5 数据查询.ppt

单元五,数据查询,项目知识要点与目标,任务一 单表查询,使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过SQL语句的查询可以从表或视图中迅速方便地检索数据。一、SELECT语句 语法格式： SELECT ALL | DISTINCT 输出列表达式, . FROM 表名1 , 表名2 /*FROM子句*/ WHERE 条件 /*WHERE子句*/ GROUP BY 列名 | 表达式 | 列编号 ASC | DESC, . /* GROUP BY 子句*/ HAVING 条件 /* HAVING 子句*/ ORDER BY 列名 | 表达式 | 列编号 ASC | DESC , . /*ORDER BY子句*/ LIMIT 偏移量, 行数|行数OFFSET偏移量 /*LIMIT子句*/ 顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。,选择列,选择指定的列使用SELECT语句选择一个表中的某些列的语法格式如下： SELECT 字段列表 FROM 表名称各列名之间要以逗号分隔。【例5.1】查询Bookstore数据库的Members表中各会员的会员姓名、联系电话和注册时间。 USE Bookstore; SELECT 会员姓名,联系电话,注册时间 FROM Members;说明：当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列，如要显示Members表中所有列，不必将所有字段名一一列出，使用 SELECT * FROM Members;,列别名,2. 定义列别名 当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列别名。语法格式为： SELECT column_name AS column_alias【例5.2】 查询Book表中图书类别为计算机的图书书名、作者和出版社，结果中各列的标题分别指定为name、auther和publisher。 SELECT 书名 AS name, 作者 AS auther, 出版社 AS publisher FROM Book WHERE 图书类别= 计算机;注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。 SELECT 学号 AS Student number, 姓名 AS Student name, 总学分 AS mark FROM XS WHERE 专业名= 计算机;,替换查询结果中的数据,3. 替换查询结果中的数据在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 ELSE 表达式END,【例5.3】 查询Book表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为“尚未进货”；若数量小于5，替换为“需进货”；若数量在5-50之间，替换为“库存正常”；若总学分大于50，替换为“库存积压”。列标题更改为“库存”。 SELECT 图书编号, 书名, CASE WHEN 数量 IS NULL THEN 尚未进货 WHEN 数量 =5 and 数量=50 THEN 库存正常 ELSE 库存积压 END AS 库存 FROM Book;,替换查询结果举例,计算列值,4. 计算列值使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECT expression , expression 【例5.4】 对Sell表已发货的记录计算订购金额（订购金额=订购册数*订购单价），并显示图书编号和订购金额。 SELECT 图书编号, 订购册数*订购单价 AS 订购金额 FROM Sell WHERE 是否发货= 已发货;,消除结果集中的重复行,5. 消除结果集中的重复行 对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行，其格式是：SELECT DISTINCT column_name , column_name其含义是对结果集中的重复行只选择一个，保证行的唯一性。【例5.5】 对Book表只选择图书类别和出版社，消除结果集中的重复行。 SELECT DISTINCT 图书类别, 出版社 FROM Book;,课堂练习,对YGGL数据库完成以下查询： 1、查询每个雇员的的地址和电话，显示的列标题要求显示“地址”、“电话”。2、查询employees表部门号和性别，要求消除重复行3、计算每个雇员的实际收入4、查询employees表中员工的姓名和性别，要求sex值为1时显示为“男”，为0时显示为“女”。5、查询Salary表中员工的员工编号和收入水平，要求2000元以下显示为“低收入”，2000-3000显示为“中等收入”，3000元以上时显示为“高收入”。,聚合函数,SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。,COUNT函数,（1）COUNT函数聚合函数中最经常使用的是COUNT()函数，用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。 语法格式为：COUNT ( ALL | DISTINCT expression | * )其中，expression是一个表达式，其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含 NULL值。,【例5.6】 求会员总人数。 SELECT COUNT(*) AS 会员数 FROM Members; 【例5.7】 统计已结清的订单数。 SELECT COUNT(是否结清) AS 已结清的订单数 FROM Sell;注意：这里COUNT(是否结清)只统计是否结清列中不为NULL的行。【例5.8】 统计订购册数在5以上的订单数。 SELECT COUNT(订购册数) AS 订购册数在5以上的订单数 FROM Sell WHERE 订购册数5;,COUNT函数举例,MAX和MIN函数,（2）MAX和MINMAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：MAX / MIN ( ALL | DISTINCT expression )其中，expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期 类型。【例5.9】 求订购了图书编号为ISBN 8-5006-6625-X的订单的最高订购册数和最低订购册数。 SELECT MAX(订购册数), MIN(订购册数) FROM Sell WHERE 图书编号 = ISBN 8-5006-6625-X;注意：当给定列上只有空值或检索出的中间结果为空时，MAX和MIN函数的值也为空。,SUM函数和AVG函数,（3）SUM函数和AVG函数SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM / AVG ( ALL | DISTINCT expression )其中，expression是常量、列、函数或表达式，其数据类型只能是数值型。【例5.10】 求订购了图书编号为ISBN 8-5006-6625-X图书的订购总册数。 SELECT SUM(订购册数) AS 订购总册数 FROM Sell WHERE 图书编号 = ISBN 8-5006-6625-X; 【例5.11】 求订购图书编号为ISBN 8-5006-6625-X图书的订单平均册数。 SELECT AVG(订购册数) AS 每笔订单平均册数 FROM Sell WHERE 图书编号 = ISBN 8-5006-6625-X;,课堂练习,对YGGL数据库完成以下查询：1、计算员工总数2、计算salary表中员工月收入的平均数3、计算所有员工的总支出4、显示最高收入和最低收入的员工的员工号,WHERE子句,WHERE子句必须紧跟FROM子句之后，在WHERE子句中，使用一个条件从FROM子句的中间结果中选取行。其基本格式为： WHERE 列 运算符 值,比较运算,WHERE子句会根据条件对FROM子句的中间结果中的行一行一行地进行判断，当条件为TRUE的时候，一行就被包含到WHERE子句的中间结果中。在SQL中，返回逻辑值（TRUE或FALSE）的运算符或关键字都可称为谓词。判定运算包括比较运算、模式匹配、范围比较、空值比较和子查询。1. 比较运算比较运算符用于比较（除TEXT和BLOB类型外) 两个表达式值，MySQL支持的比较运算符有：=（等于）、（大于）、=（大于等于）、（相等或都等于空）、（不等于）、!=（不等于）。当两个表达式值均不为空值（NULL）时，除了“”运算符，其他比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN。,【例5.12】 查询Bookstore数据库Book表中书名为“网页程序设计”的记录。 SELECT 书名,单价 FROM Book WHERE 书名=网页程序设计; 【例5.13】 查询Book表中单价大于30的图书情况。 SELECT * FROM Book WHERE 单价30; MySQL有一个特殊的等于运算符“”，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。没有UNKNOWN的情况。【例5.14】 查询Sell表中还未收货的订单情况。 SELECT 订单号,是否收货 FROM Sell WHERE 是否收货NULL;,比较运算举例,逻辑运算,通过逻辑运算符（AND、OR、XOR和NOT）组成更为复杂的查询条件。逻辑运算操作的结果是“1”或“0”，分别表示“true”或“false”。,逻辑运算举例,非：select not 0, not 1, not null;或： select (1 or 0), (0 or 0), (1 or null), (1 or 1), (null or null);与： select (1 and 1), (0 and 1), (3 and 1), (1 and null);异或： select (1 xor 1), (0 xor 0), (1 xor 0), (0 xor 1), (null xor 1);,【例5.15】 查询Sell表中已收货且已结清的订单情况。 SELECT 订单号,是否收货,是否结清 FROM Sell WHERE 是否收货=已收货 AND 是否结清=已结清; 【例5.16】 查询Book表中清华大学出版社和北京大学出版社出版的价格大于25元的图书。SELECT 书名,出版社,单价 FROM Book WHERE (出版社=清华大学出版社 OR 出版社=北京大学出版社 ) AND 单价=25;或： SELECT书名,出版社,单价 FROM Book WHERE (出版社=清华大学出版社 AND 单价=25) OR (出版社=北京大学出版社 AND 单价=25); 【思考题】以下语句能否得到正确结果？为什么？ SELECT 书名,出版社,单价 FROM Book WHERE 出版社=清华大学出版社 OR 出版社=北京大学出版社 AND 单价=25;,WHERE子句举例,课堂练习,对YGGL数据库完成一下查询：1、显示月收入高于2000元的员工号码2、查询1970年以后出生的员工的姓名和地址3、显示女雇员的地址和电话，列标题要求用中文“地址”、“电话”表示,LIKE运算符,2. 模式匹配（LIKE运算符）LIKE运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。使用LIKE进行模式匹配时，常使用特殊符号_和%，可进行模糊查询。“%”代表0个或多个字符，“_”代表单个字符。由于MySQL默认不区分大小写，要区分大小写时需要更换字符集的校对规则。【例5.17】 查询Members表中姓“张”的会员的身份证号、姓名及注册时间。 SELECT 身份证号,会员姓名, 注册时间 FROM Members WHERE 会员姓名 LIKE 张%; 【例5.18】 查询Book表中图书编号倒数第二位为T的图书的图书编号和书名。 SELECT 图书编号, 书名 FROM Book WHERE 图书编号 LIKE %T_; 例5.19】 查询Book表中书名中包含下画线的图书。 SELECT 图书编号,书名 FROM Book WHERE 书名 LIKE %#_% ESCAPE #;,范围比较,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 * FROM Book WHERE 出版时间 BETWEEN 2010-1-1 AND 2010-12-31;,使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expression IN ( expression ,n)【例5.21】 查询Book表中“高等教育出版社”、“北京大学出版社”和“人民邮电出版社”出版的图书的情况。 SELECT * FROM Book WHERE 出版社 IN ( 高等教育出版社, 北京大学出版社, 人民邮电出版社); 说明：IN关键字最主要的作用是表达子查询。,范围比较举例,空值比较,4. 空值比较当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为：expression IS NOT NULL当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例5.22】 查询Sell表中尚未发货的订单记录。 SELECT * FROM Sell WHERE 是否发货 IS NULL;本例即查找总学分为空的学生，结果为空。,课堂练习,对YGGL数据库完成一下查询：1、显示月收入高于2000元的员工号码2、查询1970年以后出生的员工的姓名和地址3、 查询支出在50-150之间的员工信息4、查询财务部、研发部、市场部的员工信息5、显示工作年限三年以上（含3年）、学历在本科以上（含本科）的男性员工的信息6、查找员工号码中倒数第二个数字为0的姓名、地址和学历7 查找地址中包含中山路 的员工的信息,任务二 多表查询,前面介绍了使用SELECT子句选择列，本小节讨论SELECT查询的对象（即数据源）的构成形式。SELECT的查询对象由FROM子句指定，其格式为：FROM 表名1 AS 别名1 , 表名2 AS 别名2 /*查询表*/ | JOIN子句 /*连接表*/ 引用一个表：可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：SELECT * FROM db2.tb;当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。,FROM子句,【例5.23】 从Members表中检索出所有客户的信息，并使用表别名Users。 SELECT * FROM Members AS Users; 引用多个表：如果要在不同表中查询数据，则必须在FROM子句中指定多个表。指定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的连接。例如，在XSCJ数据库中需要查找选修了离散数学课程的学生的姓名和成绩，就需要将XS、KC和XS_KC三个表进行连接，才能查找到结果。【例5.24】 查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。 SELECT Book.书名, Sell.订购册数, Sell.订购时间 FROM Book, Sell WHERE Book.图书编号=Sell.图书编号;,全连接,当数据查询涉及到多张表格时，要将多张表格的数据连接起来组成一张表格，连接的方式有多种。全连接全连接产生的新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。全连接可能得到的行数为每个表中行数之积。如表A有3行，表B有2行，表A和B全连接后得到6行（3x2=6)的表,全连接举例,表A和B全连接后得到如下6行（3x2=6)的表：,表A,表B,等值连接,FROM子句各个表用逗号分隔，这样就指定了全连接。全连接潜在地产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这样的情形下，通常要使用WHERE子句设定条件来将结果集减少为易于管理的大小，这样的连接即为等值连接。若表A和B进行等值连接（T1=T3 ），则如下表所示，只有两行。,【例5.24】 查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。 SELECT Book.书名, Sell.订购册数, Sell.订购时间 FROM Book, Sell WHERE Book.图书编号=Sell.图书编号;,JOIN连接,2. JOIN连接 第二种方式是使用JOIN关键字的连接，语法格式如下： table_reference INNER JOIN table_reference ON conditional_expr| USING (column_list)（1）内连接：指定了INNER关键字的连接是内连接。【例5.25】 要实现例4.23中的结果，可以使用以下语句：SELECT Book.书名, Sell.订购册数, Sell.订购时间 FROM Book inner join Sell ON Book.图书编号=Sell.图书编号; 该语句根据ON关键字后面的连接条件，合并两个表，返回满足条件的行。,内连接是系统默认的，可以省略INNER关键字。使用内连接后，FROM子句中ON条件主要用来连接表，其他并不属于连接表的条件可以使用WHERE子句来指定。【例 5.26】 用JOIN关键字表达下列查询：查找购买了“网页程序设计”且订购数量大于5本的图书信息。 SELECT 书名,订购册数 FROM Book JOIN Sell ON Book.图书编号 = Sell. 图书编号 WHERE 书名 = 网页程序设计 AND 订购册数5;,JOIN连接举例,内连接还可以用于多个表的连接。【例5.27】 用JOIN关键字表达下列查询：查找购买了“网页程序设计”且订购数量大于5本的图书和会员姓名和订购册数。SELECT Book.图书编号, 会员姓名, 书名, 订购册数 FROM Sell JOIN Book ON Book. 图书编号= Sell.图书编号 JOIN Members ON Sell.身份证号 = Members.身份证号 WHERE 书名 = 网页程序设计 AND 订购册数5 ;,多表连接举例,作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。【例5.28】 查找BookStore数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数。 SELECT a.订单号,a.图书编号,a.订购册数 FROM Sell AS a JOIN Sell AS b ON a. 图书编号=b. 图书编号 where a. 订单号!=b. 订单号;,自表连接,如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么ON条件也可以换成USING子句。USING（column_list）子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中column_list为两表中相同的列名。 【例5.29】 查找Members表中所有订购过图书的会员姓名。 SELECT Distinct 会员姓名 FROM Members JOIN Sell USING (身份证号); 查询的结果为Sell表中所有出现的身份证号对应的会员姓名。 例5.29的语句与下列语句等价： SELECT Distinct 会员姓名 FROM Members JOIN Sell ON Members.身份证号=Sell.身份证号 ;,USING子句,外连接,指定了OUTER关键字的连接为外连接。外连接包括： 左外连接（LEFT OUTER JOIN）： 结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。 右外连接（RIGHT OUTER JOIN）： 结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。,外连接举例,【例5.30】 查找所有图书的图书编号、数量及订购了图书的会员身份证号，若从未订购过，也要包括其情况。 SELECT Book.图书编号,Book.数量,身份证号 FROM Book LEFT OUTER JOIN Sell ON Book.图书编号= Sell.图书编号;说明： 【例5.31】 查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。 SELECT 订单号,图书编号,订购册数, Members.会员姓名 FROM Sell RIGHT JOIN Members ON Members.身份证号= Sell.身份证号;,子查询,在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。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 *FROM Sell WHERE 身份证号 IN( SELECT 身份证号 FROM Members WHERE 会员姓名 = 张三 );说明：在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。 本例中，先执行子查询： SELECT身份证号 FROM Members WHERE会员姓名= 张三 得到一个只含有身份证号列的表。再执行外查询，若Sell表中某行的身份证号列值等于子查询结果表中的任一个值，则该行就被选择。,子查询嵌套举例,IN子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。【例5.33】 查找购买了除“网页程序设计”以外图书的会员信息。要查找会员信息，先要知道会员的身份证号，而要知道购买了除“网页程序设计”以外图书的会员，可以按图书编号在Sell表中查到，但是“网页程序设计”的图书编号要通过查找Book才可以获得。SELECT * FROM Members WHERE 身份证号 IN (SELECT 身份证号 FROM Sell WHERE 图书编号 NOT IN ( SELECT 图书编号 FROM Book WHERE 书名=网页程序设计);,多表查询,对YGGL数据库完成多表查询：1、查询每个雇员的基本情况和薪水情况2、查询“王林”的基本情况和所工作的部门名称3、查询月收入在20003000元的员工姓名和支出4、查询研发部在1970年以前出生的员工姓名和薪水情况5、使用子查询查找工资收入大于2000元的员工的基本信息6、查找在财务部工作的员工的基本信息7、查找住在“中山路”的员工的工作部门名称,任务三 分类汇总与排序,GROUP BY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XS表中的所有行分组，结果是每个专业的学生成为一组。GROUP BY子句的语法格式如下：GROUP BY 列名 |表达式 | 列编号 ASC | DESC, . WITH ROLLUPGROUP BY子句后通常包含列名或表达式。MySQL对GROUP BY子句进行了扩展，可以在列的后面指定ASC（升序）或DESC（降序）。GROUP BY可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。,【例5.38】 输出Book表中图书类别名。 SELECT 图书类别 FROM Book GROUP BY 图书类别; 【例5.39】 按图书类别统计Book表中各类图书的库存数。 SELECT 图书类别,COUNT(*) AS 库存数 FROM Book GROUP BY 图书类别; 【例5.40】 按图书编号分类统计其订单数和订单的平均订购册数。 SELECT 图书编号, AVG(订购册数) AS 订购册数 , COUNT(订单号) AS 订单数 FROM Sell GROUP BY 图书编号;,带ROLLUP的GROUP BY子句,使用带ROLLUP操作符的GROUP BY子句，可指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。【例5.41】按图书类别、出版社分类统计Book表中各类图书的库存数。 SELECT 图书类别, 出版社, Sum(数量) AS 库存数 FROM Book GROUP BY 图书类别, 出版社; 请将执行结果与以下语句比较： SELECT 图书类别, 出版社, Sum(数量) AS 库存数 FROM Book GROUP BY 图书类别, 出版社 WITH ROLLUP;,HAVING子句,使用HAVING子句的目的与WHERE子句类似，不同的是WHERE子句是用来在FROM子句之后选择行，而HAVING子句用来在GROUP BY子句后选择行。例如，查找XSCJ数据库中平均成绩在85分以上的学生，就是在XS_KC表上按学号分组后筛选出符合平均成绩大于等于85的学生。语法格式： HAVING 条件其中，where_definition是选择条件，条件的定义和WHERE子句中的条件类似，不过HAVING子句中的条件可以包含聚合函数，而WHERE子句中则不可以。SQL标准要求HAVING必须引用GROUP BY子句中的列或用于聚合函数中的列。不过，MySQL支持对此工作性质的扩展，并允许HAVING引用SELECT清单中的列和外部子查询中的列。,HAVING子句举例,【例5.42】 查找Sell表中每个会员平均订购册数在10本以上的会员的身份证号和平均订购册数。SELECT 身份证号, AVG(订购册数) AS 平均订购册数 FROM Sell GROUP BY 身份证号 HAVING AVG(订购册数) 10; 【例5.43】 查找Sell表中会员订单数在2笔以上且每笔订购册数都在5本以上的会员。SELECT 身份证号 FROM Sell WHERE 订购册数 5 GROUP BY 身份证号 HAVING COUNT(*) 2;,ORDER BY子句,在一条SELECT语句中，如果不使用ORDER BY子句，结果中行的顺序是不可预料的。使用ORDER BY子句后可以保证结果中的行按一定顺序排列。语法格式： ORDER BY 列名 | 表达式 | 列编号 ASC | DESC , .说明：ORDER BY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。例如，使用ORDER BY 3表示对SELECT的列清单上的第3列进行排序。关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。,ORDER BY子句举例,【例5.44】 将Book表中记录按出版时间先后排序。 SELECT * FROM Book ORDER BY 出版时间; 【例5.45】 将Sell表中记录按订购册数从高到低排列。 SELECT * FROM Sell ORDER BY 订购册数 DESC;,LIMIT子句,LIMIT子句是SELECT语句的最后一个子句，主要用于限制被SELECT语句返回的行数。语法格式： LIMIT offset, row_count | row_count OFFSET offset语法格式中的offset和row_count都必须是非负的整数常数，offset指定返回的第一行的偏移量，row_count是返回的行数。例如，“LIMIT 5”表示返回SELECT语句的结果集中最前面5行，而“LIMIT 3，5”则表示从第4行开始返回5行。值得注意的是初始行的偏移量为0而不是1。,LIMIT子句举例,【例5.46】 查找Members表中注册时间最靠前的5位会员的信息。 SELECT *FROM Members ORDER BY 注册时间 LIMIT 5; 【例5.47】 查找Book表中从第4条记录开始的5条记录。 SELECT *FROM Book ORDER BY 学号 LIMIT 3, 5; 为了与PostgreSQL兼容，MySQL也支持LIMIT row_count OFFSET offset语法。所以将上面例子中的LIMIT子句换成“LIMIT 5 OFFSET 3”，结果一样。,课堂练习,对YGGL数据库完成查询：1、按部门列出该部门工作的员工人数2、按员工学历分组统计各种学历人数3、分别统计男性员工和女性员工人数4、查找雇员数超过2人的部门名称和员工数量5、将员工信息按出生日期从大到小排序6、将员工薪水按收入多少从小到大排序7、按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序,综合练习,对YGGL数据库完成查询：1、对salary表，计算每个雇员的实际收入（实际收入=收入-支出。2、查找员工号码中倒数第二个数字为0的姓名、地址和学历。 3、按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序。 4、查询研发部在1970年以前出生的员工姓名和薪水情况。,