第五章 数据查询ppt课件

.,第五章,数据查询,项目知识要点与目标,单表查询,使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过SQL语句的查询可以从表或视图中迅速方便地检索数据。一、SELECT语句语法格式：SELECTALL|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字段列表AS别名【例5.2】查询Book表中图书类别为计算机的图书书名、作者和出版社，结果中各列的标题分别指定为name、auther和publisher。SELECT书名ASname,作者ASauther,出版社ASpublisherFROMBookWHERE图书类别=计算机;注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。SELECT图书编号ASbooknumber,书名ASbookname,FROMBookWHERE图书类别=计算机;,替换查询结果中的数据,3.替换查询结果中的数据在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2ELSE表达式END,【例5.3】查询Book表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为“尚未进货”；若数量小于5，替换为“需进货”；若数量在5-50之间，替换为“库存正常”；若总学分大于50，替换为“库存积压”。列标题更改为“库存”。SELECT图书编号,书名,CASEWHEN数量ISNULLTHEN尚未进货WHEN数量=5and数量=（大于等于）、（相等或都等于空）、（不等于）、!=（不等于）。当两个表达式值均不为空值（NULL）时，除了“”运算符，其他比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN。,【例5.6】查询Bookstore数据库Book表中书名为“网页程序设计”的记录。SELECT书名,单价FROMBookWHERE书名=网页程序设计;【例5.7】查询Book表中单价大于30的图书情况。SELECT*FROMBookWHERE单价30;,比较运算举例,逻辑运算,通过逻辑运算符（AND、OR、XOR和NOT）组成更为复杂的查询条件。逻辑运算操作的结果是“1”或“0”，分别表示“true”或“false”。,逻辑运算举例,非：selectnot0,not1,notnull;或：select(1or0),(0or0),(1ornull),(1or1),(nullornull);与：select(1and1),(0and1),(3and1),(1andnull);异或：select(1xor1),(0 xor0),(1xor0),(0 xor1),(nullxor1);,【例5.8】查询Sell表中已收货且已结清的订单情况。SELECT订单号,是否收货,是否结清FROMSellWHERE是否收货=已收货AND是否结清=已结清;【例5.9】查询Book表中清华大学出版社和北京大学出版社出版的价格大于25元的图书。SELECT书名,出版社,单价FROMBookWHERE(出版社=清华大学出版社OR出版社=北京大学出版社)AND单价=25;或：SELECT书名,出版社,单价FROMBookWHERE(出版社=清华大学出版社AND单价=25)OR(出版社=北京大学出版社AND单价=25);【思考题】以下语句能否得到正确结果？为什么？SELECT书名,出版社,单价FROMBookWHERE出版社=清华大学出版社OR出版社=北京大学出版社AND单价=25;,WHERE子句举例,LIKE运算符,2.模式匹配（LIKE运算符）LIKE运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。使用LIKE进行模式匹配时，常使用特殊符号_和%，可进行模糊查询。“%”代表0个或多个字符，“_”代表单个字符。由于MySQL默认不区分大小写，要区分大小写时需要更换字符集的校对规则。【例5.10】查询Members表中姓“张”的会员的身份证号、姓名及注册时间。SELECT用户号,会员姓名,注册时间FROMMembersWHERE会员姓名LIKE张%;【例5.11】查询Book表中图书编号倒数第二位为T的图书的图书编号和书名。SELECT图书编号,书名FROMBookWHERE图书编号LIKE%T_;例【5.12】查询Book表中书名中包含下画线的图书。SELECT图书编号,书名FROMBookWHERE书名LIKE%#_%ESCAPE#;,范围比较,3.范围比较用于范围比较的关键字有两个：BETWEEN和IN。当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：表达式NOTBETWEEN表达式1AND表达式2当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。注意：expression1的值不能大于expression2的值。【例5.13】查询Book表中2010年出版的图书的情况。SELECT*FROMBookWHERE出版时间BETWEEN2010-1-1AND2010-12-31;,使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：表达式IN(表达式1,n)【例5.14】查询Book表中“高等教育出版社”、“北京大学出版社”和“人民邮电出版社”出版的图书的情况。SELECT*FROMBookWHERE出版社IN(高等教育出版社,北京大学出版社,人民邮电出版社);说明：IN关键字最主要的作用是表达子查询。,范围比较举例,空值比较,4.空值比较当需要判定一个表达式的值是否为空值时，使用ISNULL关键字，格式为：表达式ISNOTNULL当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例5.15】查询Sell表中尚未发货的订单记录。SELECT*FROMSellWHERE是否发货ISNULL;本例即查找总学分为空的学生，结果为空。MySQL有一个特殊的等于运算符“”，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。【例5.16】查询Sell表中还未收货的订单情况。SELECT*FROMSellWHERE是否收货NULL;,多表查询,前面介绍了使用SELECT子句选择列，本小节讨论SELECT查询的对象（即数据源）的构成形式。SELECT的查询对象由FROM子句指定，其格式为：FROM表名1AS别名1,表名2AS别名2/*查询表*/|JOIN子句/*连接表*/引用一个表：可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：SELECT*FROMdb2.tb;当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。,FROM子句,【例5.17】从Members表中检索出所有客户的信息，并使用表别名Users。SELECT*FROMMembersASUsers;引用多个表：如果要在不同表中查询数据，则必须在FROM子句中指定多个表。指定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的连接。例如，在XSCJ数据库中需要查找选修了离散数学课程的学生的姓名和成绩，就需要将XS、KC和XS_KC三个表进行连接，才能查找到结果。【例5.18】查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。SELECTBook.书名,Sell.订购册数,Sell.订购时间FROMBook,SellWHEREBook.图书编号=Sell.图书编号;,全连接,当数据查询涉及到多张表格时，要将多张表格的数据连接起来组成一张表格，连接的方式有多种。全连接全连接产生的新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。全连接可能得到的行数为每个表中行数之积。如表A有3行，表B有2行，表A和B全连接后得到6行（3x2=6)的表,全连接举例,表A和B全连接后得到如下6行（3x2=6)的表：,表A,表B,等值连接,FROM子句各个表用逗号分隔，这样就指定了全连接。全连接潜在地产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这样的情形下，通常要使用WHERE子句设定条件来将结果集减少为易于管理的大小，这样的连接即为等值连接。若表A和B进行等值连接（T1=T3），则如下表所示，只有两行。,【例5.19】查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。SELECTBook.书名,Sell.订购册数,Sell.订购时间FROMBook,SellWHEREBook.图书编号=Sell.图书编号;,JOIN连接,2.JOIN连接第二种方式是使用JOIN关键字的连接，语法格式如下：表名1INNERJOIN表名2ON条件|USING(列名)（1）内连接：指定了INNER关键字的连接是内连接。【例5.20】要实现例4.23中的结果，可以使用以下语句：SELECTBook.书名,Sell.订购册数,Sell.订购时间FROMBookinnerjoinSellONBook.图书编号=Sell.图书编号;该语句根据ON关键字后面的连接条件，合并两个表，返回满足条件的行。,内连接是系统默认的，可以省略INNER关键字。使用内连接后，FROM子句中ON条件主要用来连接表，其他并不属于连接表的条件可以使用WHERE子句来指定。【例5.26】用JOIN关键字表达下列查询：查找购买了“网页程序设计”图书信息。SELECT书名,订购册数FROMBookJOINSellONBook.图书编号=Sell.图书编号WHERE书名=网页程序设计;,JOIN连接举例,【例5.21】用JOIN关键字表达下列查询：查找订购数量大于5本图书和会员姓名和订购册数。SELECT会员姓名,订购册数FROMSellJOINMembersONSell.用户号=Members.用户号WHERE订购册数5;,多表连接举例,内连接还可以用于多个表的连接。【例5.22】用JOIN关键字表达下列查询：查找购买了“网页程序设计”且订购数量大于5本的图书和会员姓名和订购册数。SELECTBook.图书编号,会员姓名,书名,订购册数FROMSellJOINBookONBook.图书编号=Sell.图书编号JOINMembersONSell.身份证号=Members.身份证号WHERE书名=网页程序设计AND订购册数5;,多表连接举例,作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。【例5.23】查找BookStore数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数。SELECTa.订单号,a.图书编号,a.订购册数FROMSellASaJOINSellASbONa.图书编号=b.图书编号wherea.订单号!=b.订单号;,自表连接,如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么ON条件也可以换成USING子句。USING（column_list）子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中column_list为两表中相同的列名。【例5.24】查找Members表中所有订购过图书的会员姓名。SELECTDistinct会员姓名FROMMembersJOINSellUSING(用户号);查询的结果为Sell表中所有出现的身份证号对应的会员姓名。例5.24的语句与下列语句等价：SELECTDistinct会员姓名FROMMembersJOINSellONMembers.用户号=Sell.用户号;,USING子句,多表查询,对YGGL数据库完成多表查询：1、查询每个雇员的基本情况和薪水情况2、查询“王林”的基本情况和所工作的部门名称3、查询月收入在20003000元的员工姓名和支出4、查询研发部在1970年以前出生的员工姓名和薪水情况,外连接,指定了OUTER关键字的连接为外连接。外连接包括：左外连接（LEFTOUTERJOIN）：结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。右外连接（RIGHTOUTERJOIN）：结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。,左外连接举例,FromAleftjoinBonT1=T3：,表A,表B,外连接举例,【例5.25】查找所有图书的图书编号、数量及订购了图书的会员身份证号，若从未订购过，也要包括其情况。SELECTBook.图书编号,Book.数量,身份证号FROMBookLEFTOUTERJOINSellONBook.图书编号=Sell.图书编号;【例5.26】查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。SELECT订单号,图书编号,订购册数,Members.会员姓名FROMSellRIGHTJOINMembersONMembers.用户号=Sell.用户号;,子查询,在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。SQL标准允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。（1）IN子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expressionNOTIN(subquery)其中，subquery是子查询。当表达式expression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。,子查询举例,【例5.27】查找在Bookstore数据库中张三的订单信息。SELECT*FROMSellWHERE用户号IN(SELECT用户号FROMMembersWHERE会员姓名=张三);说明：在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。本例中，先执行子查询：SELECT用户号FROMMembersWHERE会员姓名=张三得到一个只含有身份证号列的表。再执行外查询，若Sell表中某行的身份证号列值等于子查询结果表中的任一个值，则该行就被选择。,子查询嵌套举例,IN子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。【例5.28】查找购买了除“网页程序设计”以外图书的会员信息。要查找会员信息，先要知道会员的身份证号，而要知道购买了除“网页程序设计”以外图书的会员，可以按图书编号在Sell表中查到，但是“网页程序设计”的图书编号要通过查找Book才可以获得。SELECT*FROMMembersWHERE用户号IN(SELECT用户号FROMSellWHERE图书编号NOTIN(SELECT图书编号FROMBookWHERE书名=网页程序设计);,比较子查询,这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算。其格式如下：表达式|=|!=|ALL|SOME|ANY(子查询)【例5.29】查找Book表中所有比“网页设计”类图书价格都高的图书基本信息。SELECT图书编号,图书类别,单价FROMBookWHERE单价ALL(SELECT单价FROMBookWHERE图书类别=网页设计);,比较子查询,【例5.30】查找Sell表中订购册数不低于编号为TP7/301-135的任何一个订单的订购册数的订单信息。SELECT图书编号,订购册数FROMSellWHERE订购册数SOME(SELECT订购册数FROMSellWHERE图书编号=TP7/301-135);,外连接与子查询,一、对YGGL数据库完成多表查询：1、使用左外连接显示员工表中员工基本信息和所在部门的部门名称二、使用子查询完成：2、查找工资收入大于2000元的员工的基本信息3、查找在财务部工作的员工的基本信息4、查找住在“中山”的员工的工作部门名称,聚合函数,SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。,COUNT函数,（1）COUNT函数聚合函数中最经常使用的是COUNT()函数，用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。语法格式为：COUNT(ALL|DISTINCT表达式|*)其中，表达式1是一个表达式，其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含NULL值。,【例5.31】求会员总人数。SELECTCOUNT(*)AS会员数FROMMembers;【例5.32】统计已结清的订单数。SELECTCOUNT(是否结清)AS已结清的订单数FROMSell;注意：这里COUNT(是否结清)只统计是否结清列中不为NULL的行。【例5.33】统计订购册数在5以上的订单数。SELECTCOUNT(订购册数)AS订购册数在5以上的订单数FROMSellWHERE订购册数5;,COUNT函数举例,MAX和MIN函数,（2）MAX和MINMAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：MAX/MIN(ALL|DISTINCT表达式)其中，expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。【例5.35】求订购了图书编号为TP7/301-135的订单的最高订购册数和最低订购册数。SELECTMAX(订购册数),MIN(订购册数)FROMSellWHERE图书编号=TP7/301-135;注意：当给定列上只有空值或检索出的中间结果为空时，MAX和MIN函数的值也为空。,SUM函数和AVG函数,（3）SUM函数和AVG函数SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM/AVG(ALL|DISTINCT表达式)其中，expression是常量、列、函数或表达式，其数据类型只能是数值型。【例5.36】求订购了图书编号为TP7/301-135图书的订购总册数。SELECTSUM(订购册数)AS订购总册数FROMSellWHERE图书编号=TP7/301-135;【例5.37】求订购图书编号为TP7/301-135图书的订单平均册数。SELECTAVG(订购册数)AS每笔订单平均册数FROMSellWHERE图书编号=TP7/301-135;,任务三分类汇总与排序,GROUPBY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XS表中的所有行分组，结果是每个专业的学生成为一组。GROUPBY子句的语法格式如下：GROUPBY列名|表达式ASC|DESC,.WITHROLLUPGROUPBY子句后通常包含列名或表达式。MySQL对GROUPBY子句进行了扩展，可以在列的后面指定ASC（升序）或DESC（降序）。GROUPBY可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。,【例5.38】输出Book表中图书类别名。SELECT图书类别FROMBookGROUPBY图书类别;【例5.39】按图书类别统计Book表中各类图书的库存数。SELECT图书类别,COUNT(*)AS库存数FROMBookGROUPBY图书类别;【例5.40】按图书编号分类统计其订单数和订单的平均订购册数。SELECT图书编号,AVG(订购册数)AS订购册数,COUNT(订单号)AS订单数FROMSellGROUPBY图书编号;,带ROLLUP的GROUPBY子句,使用带ROLLUP操作符的GROUPBY子句，可指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。【例5.41】按图书类别、出版社分类统计Book表中各类图书的库存数。SELECT图书类别,出版社,Sum(数量)AS库存数FROMBookGROUPBY图书类别,出版社;请将执行结果与以下语句比较：SELECT图书类别,出版社,Sum(数量)AS库存数FROMBookGROUPBY图书类别,出版社WITHROLLUP;,HAVING子句,使用HAVING子句的目的与WHERE子句类似，不同的是WHERE子句是用来在FROM子句之后选择行，而HAVING子句用来在GROUPBY子句后选择行。语法格式：HAVING条件其中，条件的定义和WHERE子句中的条件类似，不过HAVING子句中的条件可以包含聚合函数，而WHERE子句中则不可以。SQL标准要求HAVING必须引用GROUPBY子句中的列或用于聚合函数中的列。不过，