第五章 数据查询.ppt

第五章 数据查询 项目知识要点与目标 单表查询 使用数据库和表的主要目的是存储数据以便在需要时进行检索 统计或组织输出 通过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字段列表 AS 别名 例5 2 查询Book表中图书类别为计算机的图书书名 作者和出版社 结果中各列的标题分别指定为name auther和publisher SELECT书名ASname 作者ASauther 出版社ASpublisherFROMBookWHERE图书类别 计算机 注意 当自定义的列标题中含有空格时 必须使用引号将标题括起来 SELECT图书编号AS booknumber 书名AS bookname FROMBookWHERE图书类别 计算机 替换查询结果中的数据 3 替换查询结果中的数据在对表进行查询时 有时对所查询的某些列希望得到的是一种概念而不是具体的数据 例如查询XS表的总学分 所希望知道的是学习的总体情况 这时 就可以用等级来替换总学分的具体数字 要替换查询结果中的数据 则要使用查询中的CASE表达式 格式为 CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2 ELSE表达式END 例5 3 查询Book表中图书编号 书名和数量 对其库存数量按以下规则进行替换 若数量为空值 替换为 尚未进货 若数量小于5 替换为 需进货 若数量在5 50之间 替换为 库存正常 若总学分大于50 替换为 库存积压 列标题更改为 库存 SELECT图书编号 书名 CASEWHEN数量ISNULLTHEN 尚未进货 WHEN数量 5and数量 50THEN 库存正常 ELSE 库存积压 ENDAS库存FROMBook 替换查询结果举例 计算列值 4 计算列值使用SELECT对列进行查询时 在结果中可以输出对列值计算后的值 即SELECT子句可使用表达式作为结果 格式为 SELECT表达式1 表达式2 例5 4 对Sell表已发货的记录计算订购金额 订购金额 订购册数 订购单价 并显示图书编号和订购金额 SELECT图书编号 订购册数 订购单价AS订购金额FROMSellWHERE是否发货 已发货 消除结果集中的重复行 5 消除结果集中的重复行对表只选择其某些列时 可能会出现重复行 例如 若对XSCJ数据库的XS表只选择专业名和总学分 则出现多行重复的情况 可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行 其格式是 SELECTDISTINCT字段列表其含义是对结果集中的重复行只选择一个 保证行的唯一性 例5 5 对Book表只选择图书类别和出版社 消除结果集中的重复行 SELECTDISTINCT图书类别 出版社FROMBook WHERE子句 WHERE子句必须紧跟FROM子句之后 在WHERE子句中 使用一个条件从FROM子句的中间结果中选取行 其基本格式为 WHERE列名运算符值 比较运算 WHERE子句会根据条件对FROM子句的中间结果中的行一行一行地进行判断 当条件为TRUE的时候 一行就被包含到WHERE子句的中间结果中 在SQL中 返回逻辑值 TRUE或FALSE 的运算符或关键字都可称为谓词 判定运算包括比较运算 模式匹配 范围比较 空值比较和子查询 1 比较运算比较运算符用于比较 除TEXT和BLOB类型外 两个表达式值 MySQL支持的比较运算符有 等于 大于 大于等于 相等或都等于空 不等于 不等于 当两个表达式值均不为空值 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关键字指出查询范围 格式为 表达式 NOT BETWEEN表达式1AND表达式2当不使用NOT时 若表达式expression的值在表达式expression1与expression2之间 包括这两个值 则返回TRUE 否则返回FALSE 使用NOT时 返回值刚好相反 注意 expression1的值不能大于expression2的值 例5 13 查询Book表中2010年出版的图书的情况 SELECT FROMBookWHERE出版时间BETWEEN 2010 1 1 AND 2010 12 31 使用IN关键字可以指定一个值表 值表中列出所有可能的值 当与值表中的任一个匹配时 即返回TRUE 否则返回FALSE 使用IN关键字指定值表的格式为 表达式IN 表达式1 n 例5 14 查询Book表中 高等教育出版社 北京大学出版社 和 人民邮电出版社 出版的图书的情况 SELECT FROMBookWHERE出版社IN 高等教育出版社 北京大学出版社 人民邮电出版社 说明 IN关键字最主要的作用是表达子查询 范围比较举例 空值比较 4 空值比较当需要判定一个表达式的值是否为空值时 使用ISNULL关键字 格式为 表达式IS NOT NULL当不使用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表名1 AS 别名1 表名2 AS 别名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 查询月收入在2000 3000元的员工姓名和支出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子查询用于进行一个给定值是否在子查询结果集中的判断 格式为 expression NOT IN 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 WITHROLLUP GROUPBY子句后通常包含列名或表达式 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