第7章 数据库中表的高级查询操作.ppt

第7章数据库中表的高级查询操作 计算机系胡骏 学习目标 T SQL语言中 SELECT语句是功能非常强大的语句 本章着重介绍复杂查询的实现需要掌握的内容 GROUPBY ORDERBY以及聚合函数的使用 UNION运算符的使用方法 连接查询 7 1限定条件的查询 SELECT语句中的WHERE子句是用来控制结果集的记录构成 可以再WHERE子句中指定一系列的查询条件 满足这些条件的记录集构成结果集 比较运算符 范围说明 BETWEEN和NOTBETWEEN 可选值列表 IN NOTIN 模糊查询 LIKE和NOTLIKE 逻辑组合 AND OR NOT 7 1限定条件的查询 1 比较条件查询2 范围查询3 列表查询5 逻辑运算符略 7 1限定条件的查询 4 模糊查询通配符 7 1限定条件的查询 例 注意 查询包含字符串30 任何记录LIKE 30 对吗 如果遇到以上情况 可以使用ESCAPE子句用以指定转义符LIKE 30 ESCAPE 例题 ESCAPE子句练习 说明 转义符的作用是指定其后第一个字符为本意字符不为通配符 而转义符本身在查询的时候会忽略 转义符可为除通配符以外其他所有字符 练习 查询附注中含有 的记录 7 2使用GROUPBY子句的查询 GROUPBY子句的主要作用是可以将数据记录的条件分成多个组 而且只有使用了GROUPBY子句 SELECT子句中所使用的聚合函数才起作用 GROUPBY子句例子 练习1求有成绩的每个学生的平均成绩2求每门课程的平均成绩3求各个课程号及相应的选课人数 解答 1selectsno avg grade AS平均分fromscgroupbysno HAVING子句 1指定组或聚合的搜索条件 HAVING通常与GROUPBY子句一起使用 2如果不使用GROUPBY子句 HAVING的行为与WHERE子句一样 3HAVING子句在整个查询语句的最后select from where groupby having HAVING子句例题 1求平均成绩大于86分的学生学号及平均成绩2求平均成绩大于86分的课程的课程号及课程平均成绩 解答 1selectsno avg grade as学生平均成绩fromscgroupbysnohavingavg grade 86 HAVING子句练习 查询18岁以上 不含18岁 的男女生的人数与性别 7 3使用聚合函数查询 聚合函数包括SUM AVG COUNT COUNT MAX MIN 他们的作用是在查询结果集中生成汇总值 其中SUM AVG MAX MIN略 7 3使用聚合函数查询 COUNT以及COUNT 各自的作用 COUNT的功能是返回表达式值的个数 其表达式通常是字段名称 COUNT 的功能是返回符合条件的记录的个数 COUNT COUNT 例题 1Selectcount SNO as个数FromscWheregrade 882Selectcount as个数FormscWheregrade 88 COUNT COUNT 例题 1selectcount distinctsno fromscwheregrade 802selectcount fromscwheregrade 80 总结 两个函数有以下区别 COUNT函数忽略对象中的空值 而COUNT 返回所有满足条件记录的个数 COUNT函数可选用DISTINCT去掉重复记录 而COUNT 则不能 7 4ORDERBY子句查询 ORDERBY子句的作用是根据查询结果中的一个字段或多个字段对查询结果进行排序 如果是升序则为ASC如果是降序则为DESC Compute子句 COMPUTE子句通常和ORDERBY子句连用生成分类汇总语法 COMPUTE AVG COUNT MAX MIN SUM expression n BYexpression n Compute子句 练习 查询SC表中所有信息 根据SNO学号排序 并汇总每个同学的平均分 7 5UNION组合多个运算结果 UNION运算符可用来将两个或多个SELECT语句的查询结果组合成一个结果集 组合的基本原则 所有查询中的列数和列的顺序必须相同 数据类型必须兼容 补充 连接查询 当查询同时涉及两个或两个以上的表时 称为连接查询 补充 连接查询 数据表之间的联系是通过表的字段值来体现的 这种字段称为连接字段 连接操作的目的就是通过加在连接字段的条件将多个表连接起来 以便从多个表中查询数据 连接操作的执行过程 嵌套循环法首先在表1中找到第一个元组 然后从头开始扫描表2 逐一查找满足连接条件的元组 找到后就将表1中的第一个元组与该元组拼接起来 形成结果表中一个元组 表2全部查找完后 再找表1中第二个元组 然后再从头开始扫描表2 逐一查找满足连接条件的元组 找到后就将表1中的第二个元组与该元组拼接起来 形成结果表中一个元组 重复上述操作 直到表1中的全部元组都处理完毕 连接方法 方法1 表之间满足一定的条件的行进行连接 此时FROM子句中指明进行连接的表名 WHERE子句指明连接的列名及其连接条件 Select学号 姓名 语文 数学 英语fromgao202 成绩2班wheregao202 学号 成绩2班 学号 连接方法 方法2 利用关键字JOIN进行连接 CROSSJOIN 交叉连接 INNERJOIN 内连接 LEFT OUTER JOIN RIGHT OUTER JOIN 外连接 FULL OUTER JOIN 连接查询类型 广义笛卡尔积 等值连接 含自然连接 与非等值连接 自身连接 外连接查询 复合条件连接查询 交叉连接 又称为非限制连接 不带连接谓词的连接 它将两个表不加任何约束地组合在一起 也就是将第一个表的每条记录分别与第二个表的每条记录组成新记录 交叉连接 交叉连接有两种语法格式 1 SELECT列名列表FROM表名1 表名22 SELECT列名列表FROM表名1CROSSJOIN表名2 交叉连接示意图 Select from商品 库存或者Select from商品crossjoin库存 等值与非等值连接查询 其中 比较运算符主要有 当比较运算符为 时 称为等值连接 其他情况为非等值连接 等值连接查询 等值连接是指将两个表根据各自表中共有字段值相等连接起来的连接查询 select from库存 商品where库存 商品名称 商品 商品名称 自然连接查询 等值连接的一种特殊情况 把目标列中重复的属性列去掉 select库存 商品名称 库存 商品名称 库存 商品单价 库存 商品数量 商品 销售数量 商品 销售单价from库存 商品where库存 商品名称 商品 商品名称 非等值连接 CREATETABLE等级 lowint uppint rankchar 10 INSERTINTO等级values 90 100 非常优秀 INSERTINTO等级values 80 89 优秀 INSERTINTO等级values 70 79 中 INSERTINTO等级values 60 69 及格 INSERTINTO等级values 0 59 不及格 非等值连接查询 SELECT学号 姓名 高等数学 rankFROM等级 学生成绩表WHERE高等数学BETWEENlowANDupp 注意 等值连接和自然连接实际上就是内连接 innerjoin 所有等值连接和自然连接查询的语句都可以改写为innerjoin语句 例 select from库存 销售where库存 商品名称 销售 商品名称改 select from库存innerJoin销售on库存 商品名称 销售 商品名称 自身连接 当一个表与自已进行连接操作时 称为表的自身连接 自身连接就是一张表的两个副本之间的内连接 使用它可以将同一个表的不同行连接起来 自身连接 使用自身连接时 必须为表指定两个不同的别名 使之在逻辑上成为两个表以示区别 由于所有属性名都是同名属性 因此必须使用表别名前缀 自身连接 查询电子商务成绩表中选修高等数学课程的成绩高于计算机基础课程成绩的所有学生学号 姓名 并按成绩从高到低排列 自身连接 SELECTx 学号 x 姓名 x 高等数学 y 计算机应用基础FROM电子商务成绩表asx 电子商务成绩表asyWHEREx 高等数学 y 计算机应用基础andx 学号 y 学号ORDERBYx 高等数学DESC 测试结果 外连接 OUTERJOIN 外连接与普通连接的共同点与不同点共同点 都能将两个或多个表进行连接输出不同点 内连接操作只输出满足连接条件的元组 外连接操作除输出满足连接条件的元组外 还可以使一个表 左外连接 右外连接 或两个表 全外连接 中的不满足连接条件的元组也出现在结果中 外连接 左外连接LEFT OUTER JOIN 将左表的所有记录分别与右表的每一条记录进行连接组合 结果集中除显示符合条件的数据行以外 还显示左边表中不符合条件的数据行 此时右边数据行会以NULL来显示 左外连接测试 SELECTT1 T2 FROMT1LEFTJOINT2ONT1 sno T2 sno结果 T1 snosnameT2 snodegree001王辉00180002程伟刚NULLNULL003薛东清00390004孙强00478 右外连接RIGHT OUTER JOIN 将右表的所有记录分别与左表的每一条记录进行连接组合 结果集中除显示符合条件的数据行以外 还要显示右边表中不符合条件的数据行 此时左边数据行会以NULL来显示 右外连接测试 SELECTT1 T2 FROMT1RIGHTJOINT2ONT1 sno T2 sno结果 T1 snosnameT2 snodegree001王辉00180003薛东清00390004孙强00478NULLNULL00587 全外连接 FULL OUTER JOIN 显示符合条件的数据行以及左边表和右边表中不符合条件的数据行 此时缺乏数据的数据行会以NULL来显示 全外连接测试 SELECTT1 T2 FROMT1FULLJOINT2ONT1 sno T2 sno结果 T1 snosnameT2 snodegree001王辉