07_高级查询.ppt

1、第一章,SQL Server 2000 数 据 库,上节回顾,掌握select 语句语法结构 掌握where条件限定语句的用法 掌握数据过滤和消除重复记录 掌握order by排序 理解top和topwith ties的用法,第七章：高级查询,本章目标,理解联接查询的类型和定义 掌握内/外/自/交叉连接* 掌握聚合函数的用法 理解Group By含义 *,定 义：根据各个数据库表之间的逻辑关系从二个或多个数 据表中检索数据。 注 意： 所要连接的表必须拥有相同的字段值，且数据类型 似。 尽量在表的主键上的基础上，指定连接条件。,连接概述,联接分类,联接查询分为以下几类： 内连接：基本内连接、多

2、表连接、自连接 外连接：左外连接、右外连接、全外连接、交叉连接,本章目标,理解联接查询的类型和定义 掌握内/外/自/交叉连接* 掌握聚合函数的用法 理解Group By含义 *,内连接概述,用inner join on语句进行连接，join指定需要连接的数据表，on指定连接条件(inner可省略)。 语法： select 字段1，字段2， from 表1 inner join 表2 on 关联条件 go,内连接inner join on,【例】查找学生数据库中每个学生的情况和选修的课程情况 use xscj select * from xs inner join xs_kc on xs.学号=

3、xs_kc.学号 go,内连接 inner joinon,【例】查找选修了206课程且成绩在80分以上的学生姓名和成绩 use xscj select 姓名，成绩 from xs join xs_kc on xs.学号=xs_kc.学号 where 课程号=206 and 成绩=80 go,多表连接,功能：多表连接主要用于两个以上的表之间的查询。 语法：selectfrom 表1 join 表2 on 条件1 join 表3 on 条件2 join 表4 on 条件3 注意： 组合键（主键）中的每一个字段都必须有一个相应的ON子句。,【分析案例】从yry数据库中查找小孩名、父亲名、母亲名及他们

4、的电话 use yry select ,fathername,fathers.tel,mothername,mothers.tel from childs join fathers on fathername= join mothers on mothername= go,多表连接分析,多表内联接案例,【例】从数据库xscj中查询学生的姓名、课程名和成绩 use xscj select 姓名，课程名，成绩 from xs join xs_kc on xs.学号=xs_kc.学号 join kc on kc.课程号=xs_kc

5、.课程号 go,内连接inner join on,【例】查找选修了计算机基础课程且成绩在80分以上的学生的学号、姓名、课程名及成绩 use xscj select 学号，姓名，课程名，成绩 from xs join xs_kc on xs.学号=xs_kc.学号 join kc on kc.课程号=xs_kc.课程号 where 课程名=计算机 and 成绩=80 go,多表查询方式(一) joinon,【例】在northwind中查询订购了23号产品且数量大于20 的该产品的订单号和客户号 方法一： select os.orderid,customerid from orders os jo

6、in order details od on os.orderid=od.orderid where productid=23 and quantity20 go,方法二： select os.orderid,customerid from orders os , order details od where os.orderid=od.orderid and productid=23 and quantity20 go,多表查询方式(二) where,自联接(特殊的内联接),功能： 自连接查询一个表中各行之间的关系。主要用于比较一个表中各个记录之间的值，规范化的数据库中很少使用自连接。 【例

7、】查找不同课程但成绩相同的学生的学号、课程号和成 绩 use xscj select a.学号，a.课程号，b.课程号,b.成绩 from xs_kc a join xs_kc b on a.成绩=b.成绩 and a.学号=b.学号 and a.课程号!=b.课程号 go,外联接,外连接类型：,语法： select 字段名1，字段名2字段n from 表1 left outer join 表2 -表1 right outer join 表2 -表1 full outer join 表2 go,外联接,左/右/全外联接分析,use yry select ,fathers.

8、name,tel from fathers join childs on childs.fathername= go,use yry Select , ,tel from fathers left join childs on childs.fathername= go,【例】查找所有学生情况及他们选修的课程号，若学生未选修任何课程，也要包括其情况。 use xscj select xs.*，课程号 from xs left outer join xs_kc -right outer join xs_

9、kc -full outer join xs_kc on xs.学号=xs_kc.学号 go,左/右/全外联接案例,左/右/全外联接案例,【例】查找被选修了的课程的选修情况和所有开设的课程名 use xscj select xs_kc.*，课程名 from xs_kc right join kc on kc.课程号=xs_kc.课程号 go,cross join交叉连接(乘集),功能： 交叉连接将从被连接的表中返回所有可能的记录组合（如：一个表8条记录，另一个表9条记录，结果将返回72条 记录）。一般用于数据库创建测试数据，或为清单及企业模版创建所有可能的组合数据。 语法： select 字段

10、1，字段2， from 表1 cross join 表2 go,【例】返回xs表和kc表所有可能记录的组合 use xscj select 学号，姓名，课程号，课程名 from xs cross join kc go 结果集的条数为两张表记录条数的乘积,cross join交叉连接(乘集),理解联接查询的类型和定义 掌握内/外/自/交叉连接* 掌握聚合函数的用法 理解Group By含义 *,本章目标,聚合函数概述,功能：返回数字表达式中所 有值的和，用来求和的 表达 式通常是字段名称或 包含字段名称的表 达式。 【例】求选修101课程的学生的总成绩。 use xscj select sum(

11、成绩) as 课程101总成绩 from xs_kc where 课程号=101 go,聚合函数 sum(列名）,聚合函数 sum(列名）,使用SUM函数时，应当注意以下几点： 运算时，sum将忽略求和对象中的空值。 使用sum函数时，可以同时使用distinct关键 字，以便在求和前去掉重复值。 sum函数只能对数字类型的字段使用，这些数 字类型包括int、smallint、tinyint,decimal、 numeric、float、real、money和smallmoney。,功能：返回数字表达式中所有值 的平均值，用来求平均 值的表达式通常是字段名称或包含字段名称的表 达式。 【例】求

12、选修101课程的学生的平均成绩。 use xscj select avg(成绩) as 课程101平均成绩 from xs_kc where 课程号=101 go,聚合函数avg(列名),聚合函数avg(列名),使用AVG聚合函数时，应当注意以下几点： 运算时，AVG函数将忽略运算对象中的空值。 使用AVG函数时，可以同时使用DISTINCT关 键字，以便在运算前去掉重复值。 AVG函数只能对数字类型的字段使用，这些数 字类型包括int、smallint、tinyint、decimal、 numeric、float、real、money和smallmoney。,聚合函数 count(*),功能

13、：统计表中有多少行记录（包括某列的行值为空） 【例】求学生的总人数 use xscj select count(*) as 学生总人数 from xs go,功能：统计表中有多少行记录（不包括某列的行值为空） 【例】求选修了课程的学生总人数 use xscj select count(distinct 学号) as选修课程总人数 from xs_kc go,聚合函数 count(列名),count(列名)与count(*)的区别： count(列名)函数将忽略对象中的空值，而count(*)函数将所有符合条件的记录都计算在内 在使用count(列名)函数时，可以同时使用可选关键字 distin

14、ct来去掉重复值，而使用count(*)函数时则不可以 不能使用count(列名)函数来计算定义为text和image数 据类型的字段的个数，但是可以使用count(*)函数,聚合函数 count区别,功能：返回表达式中所有值的最高值，用来选取最高值 的表达式通常是字段名称或包含字段名称的表达 【例】求选修101课程的学生的最高分 use xscj select max(成绩) as 课程101的最高分 from xs_kc where 课程号=101 go,聚合函数 max(列名 ),【例】按照字母顺序，返回xs表中排在最后面的学生姓名 use xscj select max(姓名) fro

15、m xs go,聚合函数 max(列名 ),使用max汇总函数时，应当注意以下两点： max函数将忽略选取对象中的空值。 不能使用max函数从bit、text和image数据类型 的字段中选取最大值。,聚合函数 max(列名 ),功能：返回表达式中所有值的最小值，用来选取最小值 的表达式通常是段名称或包含字段名称的表达式。 【例】求选修101课程的学生的最低分 use xscj select max(成绩) as 课程101的最低分 from xs_kc where 课程号=101 go,聚合函数 min(列名),【例】按照字母顺序，返回xs表中排在最前面的学 生姓名 use xscj sel

16、ect min(姓名) from xs go,聚合函数 min(列名),使用min汇总函数时，应当注意以下两点： min函数将忽略选取对象中的空值。 不能使用min函数从bit、text和image数据类型的字段中选取最小值。,聚合函数 min(列名),本章目标,理解联接查询的类型和定义 掌握内/外/自/交叉连接* 掌握聚合函数的用法 理解Group By含义 *,聚合函数只能求出单个字段的汇总值， group By可以创建多个字段的汇总值。但group和聚集函数必须同时使用。对相应的字段或表达式使用group By子句能够把表中的记录进行分组，并对组中的数据进行汇总。 语法： group b

17、y 字段名,group by使用基础,使用group by时，应注意以下几项： 在group By子句中出现的字段，必须出现在 Select语句的列表中(子集关系)。 group by与order by连用，order by 子句中必 包含group by子句后所有字段 group by关键字后可以使用多个字段名作为 分组字段，这样，系统将根据这些字段的先后 顺对果集进行更加详细地分组。,group by使用基础,group by分析,【例】将学生数据库中个专业名输出 use xscj select 专业名 from xs group by 专业名 go,group by案例,【例】求学生数据

18、库中个专业的学生数 use xscj select 专业名，count(*) as 学生数 from xs group by 专业名 go,group by案例,【例】求被选修的各门课程的平均成绩和选修该门课程的人 数。 use xscj select 课程号，avg（成绩）as 平均成绩,count(学 号) as 选修人数 from xs_kc group by 课程号 go,group by案例,使用group by 时，应注意： 不能对数据类型为ntext、text、image或bit的字段 使用 group by子句。,group by,group by与having连用， having子句将对group by子句选择出来的结果进行再次筛选，最后输出符合having子句中条件的记录。如果having子句中包含多个条件，那么这些条件将通过逻辑运算符(and、or或not)组合在一起。 having子句的语法与where子句的语法类似，唯一不同的是having子句中可以包含聚合函数，而且having子句可以引用选择列表中(select后面出现的字段)出现的任意项。,group byhavi