第六章 SELECT 语句和视图 SQL 语句的核心 数据源中数据表的各种连接 各种关系代数运算和集合运算的实现： 投影、选择、连接、广义笛卡尔积和并、 交、差、除。 函数在 SELECT 中运用 视图及其应用.

Presentation on theme: "第六章 SELECT 语句和视图 SQL 语句的核心 数据源中数据表的各种连接 各种关系代数运算和集合运算的实现： 投影、选择、连接、广义笛卡尔积和并、 交、差、除。 函数在 SELECT 中运用 视图及其应用."— Presentation transcript:

1 第六章 SELECT 语句和视图 SQL 语句的核心 数据源中数据表的各种连接 各种关系代数运算和集合运算的实现： 投影、选择、连接、广义笛卡尔积和并、 交、差、除。 函数在 SELECT 中运用 视图及其应用

2 6.1SELECT 语句一般格式 SELECT [INTO ] [FROM ] [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] [COMPUTE ][BY ]

3 6.2 数据源中数据表的各种连接 交叉连接：即两个表的笛卡尔积运算 内连接：为缺省连接，返回所有符合连接条件的行 左（右）外连接：结果集为满足连接条件的行 + 所有左 （右）表不满足连接条件的行，这些行对应右表的列 值均为空 全连接：返回行中包括左右两边表不满足连接条件的 行，即返回行一定包含左右两边表的所有行。 自连接：将表中的列与同一表中的其它列连接起来称 为自连接。

4 例：交叉连接和内连接 交叉连接：获得所有学生和课程的组合 SELECT a.name,b.subid,b.grade FROM students a cross JOIN grade b 该语句等价于： SELECT a.name,b.subid,b.grade FROM students a,grade b 内连接：学生所选课程的成绩表 SELECT a.id,a.name,b.subid,b.grade FROM students a JOIN grade b ON a.id=b.id

5 例：外连接 左外连接：学生所选课程成绩表，要包括没选任何课 的学生名单，他们对应的课程号和成绩显示 Null 。 SELECT a.id,a.name,b.subid,b.grade FROM students a LEFT JOIN grade b ON a.id=b.id 思考和练习： 1 ）没选任何课的学生课程号显示 “ 未选 ” ，成绩显示空， 如何修改 ? 2 ） ON a.id=b.id 后加 and a.id= ‘ 000002 ’ ，结果发生什么 变化 ? （与增加 WHERE a.id= ‘ 000002 ’ 比较） （显示 ‘ 000002 ’ 学生的选课情况以及其他学生名单）

6 例：全连接 假设有客户表和销售表，有下列数据： VIP 客户表销售表 编号姓名日期 客户编号 商品名称 数量 00001 张三 03-01 99999A1 00002 李四 03-01 00001B100 其中客户 “99999” 表示普通客户，如要查询所有客户 的消费记录，列为 “ 客户编号，名称，商品名称，数量 ” 则必须使用全连接： SELECT a.id,a.name,b.goodsnm,b.quantity FROM customers a FULL JOIN sales b ON a.id=b.id

7 例：自连接 Students 中增加邻坐同学编号 id_side ， 要求查询学生编号，学生姓名，邻坐同 学编号，邻坐同学姓名，其语句为： SELECT a.id,a.name,a. neighbour_id,b.name FROM students a,students b WHERE a.id_side=b.id

8 6.3 实现关系和集合运算 一）关系运算 投影：选列 -SELECT 选择：选行 -WHERE 广义笛卡尔积：两个或多个关系元组的全部组 合 -CROSS JOIN 连接：对两个或多个关系进行广义笛卡尔积的 结果上进行扩展的（增加了外连接）选择操作， 连接条件即为选择条件 -JOIN/LEFT JOIN/RIGHT JOIN 或 WHERE 。

9 二）集合运算 运算及使用 SELECT 的实现方法： 并 -UNION （所有 DBMS ） 交 - 子查询或 INTERSECT （ Oracle ） 差 - 子查询或 MINUS （ Oracle ） 除 - 子查询 1 ）并 -UNION ： 例：把学生编号、姓名和教师编号、姓名合并查询 SELECT sno,sname FROM Students UNION SELECT tno,tname FROM Teachers

10 改进上述查询： 为了区分学生还是老师，并要求把学生和老师分 列在一起，常用下列语句： SELECT sno,sname ， ’ 学生 ’ FROM students UNION SELECT tno,tname, ’ 老师 ’ FROM teachers Order by 3 不必在教师表和学生表中增加区分学生还是老师的列。 Order by 3 表示按查询结果的第三列排序。 若要求学生排在前面，则最后一行改为 : order by 3 desc

11 2 ） 交 - 子查询或 INTERSECT （ Oracle ） 例：查询被学号 09001 和 09002 的学生同时都选的课程编 号 使用 INTERSECT （ Oracle ）： SELECT cno FROM electivecourse WHERE sno= ‘ 09001 ’ INTERSECT SELECT cno FROM electivecourse WHERE sno= ‘ 09002 ’ 使用子查询： SELECT cno FROM electivecourse WHERE sno= ‘ 09001 ’ AND cno IN (SELECT cno FROM electivecourse WHERE sno= ’ 09002 ’ )

12 3 ）差 - 子查询或 MINUS （ Oracle ） 例：学号为 95001 学生选择但没有被学号为 95002 的学生选择的课程编号 使用 MINUS （ Oracle ）： SELECT cno FROM electivecourse WHERE sno= ‘ 09001 ’ MINUS SELECT cno FROM electivecourse WHERE sno= ‘ 09002 ’ 使用子查询： SELECT cno FROM electivecourse WHERE sno= ‘ 09001 ’ AND cno NOT IN (SELECT cno FROM electivecourse WHERE sno= ’ 09002 ’ )

13 4 ）除运算和含义和实现 含义：关系 R 和 S 的属性分别分成两组 X 和 Y 、 Y 和 Z ， R ÷ S 是 R 在 X 上的投影中其对应 Y(x 的像集 ) 包含关 系 S 在 Y 上的投影的所有元组。 理解：一类对象 X （学生）可以拥有 1 － N （选修 1 － N 门课程）个特性 Y ，关系 R 记录了每一个对象 X （学生）所拥有的特性 Y （选课表 ElectiveCourse ）， 关系 S 包含了所有 1 － N 个特性（课程表 Course ）， 则 R ÷ S 为具有所有 1-N 个特性 Y 的对象 X （选了所 有课程的学生）

14 实现： R(X,Y) ÷ S(Y,Z) 其相反的条件是至少存在一个特性没有被选择，即存 在一个特性，查询结果中的对象不具有该特性。 所以要解决的基础查询是：查询没有被选择的特性 : SELECT * FROM S WHERE Y NOT IN (SELECT Y FROM R WHERE Y=S.Y) R(X,Y) ÷ S(Y,Z) 对应的 SELECT 语句： SELECT X FROM R WHERE NOT exists (SELECT * FROM S WHERE Y NOT IN (SELECT Y FROM R WHERE Y=S.Y))

15 例： 查询选修了全部课程的学生号码和姓名。 查询某学生没有选择的课程 SELECT * FROM courses WHERE cno NOT IN (SELECT cno FROM electivecourse WHERE sno=students.sno) 查询语句为： SELECT sno,sname FROM students WHERE NOT EXISTS ( SELECT * FROM courses WHERE cno NOT IN (SELECT cno FROM sc WHERE sno=student.sno) )

16 例：查询至少选修了学生 95002 选修的全 部课程的学生号码。 。 学生 95001 选修的所有课程： SELECT cno FROM electivecourse WHERE sno=‘95001’ 上列课程中没有被某学生选修的课程 SELECT * FROM courses WHERE cno IN (SELECT cno FROM electivecourse WHERE sno=‘95001’) AND cno NOT IN (SELECT cno FROM electivecourse WHERE sno=students.sno) 最终的查询语句： select sno,sname from students where NOT EXIST ( SELECT * FROM courses WHERE cno IN (SELECT cno FROM electivecourse WHERE sno=‘95001’) AND cno NOT IN (SELECT cno FROM electivecourse WHERE sno=students.sno))

17 6.4 函数在 SELECT 中运用 一）查询结果中某些统计列对应不同的统 计条件的查询。 例 1 ：销售表的月累计和年累计销售额的统计。 如销售表为 Sales(saledate,goodsno,quantity ， price) 要求查询：月份，品号，月累计销售数，年累 计销售数。年累计销售数为从年初到某月底的 销售数。

18 1 ）仅输出月累计销售数 语句一：适用 sales 仅存放同一年的销售数据，否则其 中的 SUM(quantity) 将是各年某月的累计销售数。 SELECT MONTH(saledate),goodsno,SUM(quantity) FROM sales GROUP BY MONTH(saledate),goodsno 语句二：适用 sales 存放各年的销售数据 SELECT YEAR(saledate),MONTH(saledate),goodsno,SUM(quantity) FROM sales GROUP BY YEAR(saledate),MONTH(saledate),goodsno 语句三：把年月合并成一列 SELECT STR(YEAR(saledate),4)+'-'+STR(MONTH(saledate),2), goodsno,SUM(quantity) FROM sales GROUP BY STR(YEAR(saledate),4)+'-'+STR(MONTH(saledate),2), goodsno

19 分析：分组统计必须满足下面两条： 必须能构造一个表达式，使每一组的元组具有相同值。 其蕴涵了条件是每一个元组只能属于一个组，即每一组的上 述表达式的值要求不同。 本例显然不满足上述条件，比如 2000 年 3 月的销售数 据，同时要被分组统计到 3 月及 3 月以后各月份的年累 计销售数据中。 解决方法：由于 2000 年 3 月的数据要被同时分组到 2000 年 3 月到 12 月的各组中，但元组又不能被重复分 配到不同组中，解决的方法是把这些数据重复扩展 10 次，扩展的方法是使用交叉连接。 2 ）仅输出年累计销售数

20 解决步骤： 建立一个表 happenmon(hyear,hmon) ，存放所有发生 过销售和年 - 月，可使用下列语句产生数据： INSERT INTO happenmon SELECT DISTINCT YEAR(saledate),MONTH(saledate) FROM sales 扩展 Sales 表： n 月的数据被重复扩展 12-n+1 次 SELECT a.hyear,a.hmon,b.saledate,b.goodsno,b.quantity FROM happenmon a,sales b WHERE YEAR(b.saledate)=a.hyear AND MONTH(b.saledate)<=a.hmon 最终查询语句： SELECT a.hyear,a.hmon,b.goodsno,SUM(b.quantity) FROM happenmon a,sales b WHERE YEAR(b.saledate)=a.hyear AND MONTH(b.saledate)<=a.hmon GROUP BY a.hyear,a.hmon,b.goodsno

21 3 ）同时输出月累计销售数和年累计销售数 方案 1 ：联合两个查询并定义为一个视图，然后对视图分 组统计 定义视图： （红色为原查询上增加内容） CREATE VIEW vsale (year,mon,goodsno,monqty,yearqty) AS SELECT YEAR(saledate),MONTH(saledate),goodsno,SUM(quantity),0 FROM sales group by YEAR(saledate),MONTH(saledate),goodsno UNION SELECT a.hyear,a.hmon,b.goodsno,0,sum(b.quantity) FROM happenmon a,sales b WHERE YEAR(b.saledate)=a.hyear AND MONTH(b.saledate)<=a.hmon GROUP BY a.hyear,a.hmon,b.goodsno 输出结果： SELECT year,mon,goodsno,SUM(monqty),SUM(yearqty) FROM vsale GROUP BY year,mon,goodsno

22 方案 2 ：把两个查询定义为两个视图然后进 行视图的连接 分别把 1 ）的语句 2 和 2 ）的查询做成视图 年销售数： vmonsale(year,mon,goodsno, qty) 月销售数： vyearsale(year, mon,goodsno, qty) 按年、月和品号进行连接两个视图 SELECT a.year,a.mon,a.goodsno,b.qty,a.qty FROM vyearsale LEFT JOIN vmonsale ON a.year=b.year AND a.mon=b.mon AND a.goodsno=b.goodsno

23 方案 3 ：不使用视图，使用自定义函数 对年销售数的查询语句增加红色（月销 售数）部分： SELECT a.hyear,a.hmon,b.goodsno, SUM(dbo.fthismon(a.hyear,a.hmon,b.saledate)*b.quantity), SUM(b.quantity) FROM happenmon a,sales b WHERE YEAR(b.saledate)=a.hyear AND MONTH(b.saledate)<=a.hmon GROUP BY a.hyear,a.hmon,b.goodsno ORDER BY 3,1,2 fthismon(int year,int mon datetime dt) ：自定 义函数，当 dt 的年份＝ year 并且 dt 的月份 =mon 时，返回 1 ，否则返回 0

24 定义 fthismon ： CREATE FUNCTION fthismon(@year int,@mon int,@dt datetime) RETURNS INT BEGIN IF (YEAR(@dt)=@year AND MONTH(@dt)=@mon) RETURN 1 RETURN 0 END

25 函数作用的分析： 红色部分使用函数 fthismon 把非本月数据乘 0 ， 本月数据乘 1 ，实际实现了对本月数据的汇总。 一般一个查询语句所有统计列，其统计的条件 必须是一致的，使用此方法，可实现一个查询 中的不同统计列在不同条件下的汇总。 上例实际也是使用函数实现了对一个列数据不 同统计要求的分列统计。 使用函数的优点是减少了对表的扫描次数。

26 例：列的拆分查询 例：男同学平均成绩＋女同学平均成绩 例：各班学生各分数段人数统计：班级， 优、良、中、不及格 例：销售表包含了退货数据，退货数据 可用下列两种方法表示： 使用销售 / 退货标志 数量的正和负表示 如何实现销售数据和退货数据的分列查询？

27 分部门查询男女同学平均成绩 查询结果例包括：部门，男生平均成绩和女生平均成绩 方法一：用两个视图（ view ） 1) 建立男女同学平均成绩的视图 create view v_maleavggrade (sdept,maleavggrade) as select a.sdept,avg(b.grade) from student a,sc b where a.sno=b.sno and ssex=1 group by a.sdept create view v_femaleavggrade(sdept,femaleavggrade) as select a.sdept,avg(b.grade) from student a,sc b where a.sno=b.sno and ssex=0 group by a.sdept 2) 对视图查询 select a.sdept,maleavggrade,femaleavggrade from v_maleavggrade a, v_femaleavggrade b where a.sdept=b.sdept 3) 改进 ( 上述查询没有出现没有男生或女生选的课程 ) select isnull(a.sdept,b.sdept),maleavggrade,femaleavggrade from v_maleavggrade a full join v_femaleavggrade b on a.sdept=b.sdept

28 方法二：用联合（ UNION ）及一个视图（ view ） 1) 建立视图 create view v_avggrade (sdept,maleavggrade,femaleavggrade) as select a.sdept,avg(b.grade),0 from student a,sc b where a.sno=b.sno and ssex=1 group by a.sdept union select a.sdept,0,avg(b.grade) from student a,sc b where a.sno=b.sno and ssex=0 group by a.sdept 2) 对视图查询 select sdept,sum(maleavggrade),sum(femaleavggrade) from v_avggrade group by sdept 缺陷：显示平均成绩为 0 ，可能为没有人选该课程，解决方法见 书

29 方法三：不使用视图 查询各部门男女同学的成绩之和 select a.sdept,sum(a.ssex*b.grade),sum((1-a.ssex)*b.grade) from student a,sc b where a.sno=b.sno group by a.sdept 平均成绩为总成绩除 ssex 的和，但如果和为 0 会出错 select a.sdept,case when sum(a.ssex*1)<>0 then sum(a.ssex*b.grade)/sum(a.ssex*1) else null end, case when sum(1-a.ssex)<>0 then sum((1- a.ssex)*b.grade)/sum(1-a.ssex) else null end from student a,sc b where a.sno=b.sno group by a.sdept Ssex 为 bit 类型不能用 sum 求和，必须乘 1 后自动转换为 数字

30 方法四：用一个视图 建立包含部门，性别和平均成绩的视图 对该视图进行分列统计 优点是直接得到平均成绩

31 二）获得某列最大（小）值所在行的其他 列值 问题描述： 实例：商场中商品的零售价格经常会出现变动，为了 检验数据的一致性，我们需要根据销售日期还原当时 的价格，以和实际的销售价格进行比较 基础表： 商品变价表： pricechange(chgdate,goodsno,newprice) 商品销售表： sales(saledate,goodsno,quantity) 查询目标：销售日期 saledate ，品号 goodsno ，价格和 数量 quantity 。 关键点：查询中的价格是 pricechange 中早于 saledate 的最后的变价价格

32 Select 语句： 1 ）获得各商品的最新价格 使用子查询： SELECT goodsno,newprice FROM pricechange a WHERE chgdate>=ALL(select chgdate from pricechange WHERE goodsno=a.goodsno) 或可以把 WHERE 后改成： chgdate=(SELECT MAX(chgdate) … ) 使用函数：取 “ 日期＋价格 ” 的最大值，然后取出右十位 （价格字符串），再转换成数值。 select goodsno,convert(int,right(max(convert(char(10),chgdate,20) +str(newprice)),10)) from pricechange group by goodsno – 参数 20 表表示转换格式为年 - 月 - 日 使用函数的优点是对表进行一次扫描完成查询

33 2 ） 获得每个商品早于 saledate 的最后的 变价价格： 使用子查询： SELECT a.saledate,a.goodsno,b.newprice,a.quantity FROM sales a,pricechange b WHERE a.goodsno=b.goodsno AND b.chgdate<=a.saledate AND b.chgdate>=ALL(SELECT chgdate FROM pricechange WHERE goodsno=a.goodsno AND chgdate<=a.saledate) 使用函数： SELECT a.saledate,a.goodsno,convert(int,right(max(convert(char(10), chgdate,20)+str(b.newprice)),10)),a.quantity FROM sales a,pricechange b WHERE a.goodsno=b.goodsno AND b.chgdate<=a.saledate group by a.saledate,a.goodsno,a.quantity Convert 的第三个参数为 style ，表示转化日期为字符串的格式为 yyyy- mm-dd hh:mm:ss[.fff]

34 三）实现交叉表（ Cross Table ） 一般的制作报表的软件均支持 Cross Table ，如 Crystal Report ， PB 等。 什么是交叉表： 由学生表 students 、课程表 subjects 和成绩表 grade 数 据输出的下列表称为交叉表： （其特点是列数不定） 学号姓名数学语文英语计算机 A001 张三 88879378 A002 李四 78858883

35 方法一：使用 select 语句 原表：构造表： 01 zhou 英语 8901zhou89 00 01zhou 数学 9301zhou0930 01zhou 语文 7801zhou0078 02wang 英语 9102wang9100 02wang 数学 8702wang0870 02wang 语文 8202wang0087 对构造得到的表，按前两列分组求后三列的和 sum ，就得到所要的表

36 实现上述思想的 select 语句： select a.id,a.name,sum(case subid when 'sub001' then grade else 0 end) as 英语, sum(case subid when 'sub002' then grade else 0 end) as 计算机, sum(case subid when 'sub003' then grade else 0 end) as 数学, sum(case subid when 'sub004' then grade else 0 end) as 语文 from students a,grade b where a.id=b.id group by a.id,a.name order by a.id

37 case 语句只适用 SQL Server 数据库， 改进的方法： 思路：用其他方法实现 case 语句 构造一个函数 is ，函数参数为 subjects 中的 某列和行号 i ，第 i 行返回 1 ，其他行返回 0 则查询语句为 ： select a.id,a.name,sum(c.grade*is(b.subid,1)), sum(c.grade* is(b.subid,2)), sum(c.grade* is(b.subid,3)), sum(c.grade* is(b.subid,4)) from students a,subjects b,grade c where a.id=c.id and b.subid=c.subid group by a.id,a.name order by a.id

38 对 SQL Server 该函数可以使用标准函数复合得到： （假设 subid 取值依次为 sub001,sub002 … ） 1-abs(sign(cast(substring(subid,4,3) as int)-i)) 交叉表的 select 语句 select a.id,a.name, sum(grade*(1-abs(sign(cast(substring(b.subid,4,3) as int)-1)))), sum(grade*(1-abs(sign(cast(substring(b.subid,4,3) as int)-2)))), sum(grade*(1-abs(sign(cast(substring(b.subid,4,3) as int)-3)))), sum(grade*(1-abs(sign(cast(substring(b.subid,4,3) as int)-4)))) from students a,subjects b,grade c where a.id=c.id and b.subid=c.subid group by a.id,a.name order by a.id

39 方法二： 方法一存在的问题是当课程增加时，必 须修改 select 语句。 解决的方法：使用程序动态生成 select 语 句，生成的途径有两种： 客户端方案：使用高级语言生成 select 语句 服务器端方案：在 SQL Server 中可使用存储 过程 sp_executesql 执行动态生成的 SQL 语 句（字符串），但如何返回 select 语句的执 行结果到客户端是个问题。

40 * 思考和练习： 表值函数返回的表、视图等都必须是固 定列，不适用返回交叉表动态列的情况。 可选的方法是通过存储过程动态建立一 个与交叉表同结构的临时表，然后使用 以动态 select 语句作为子句的 insert 语句， 把交叉表数据插入临时表，界面程序通 过访问临时表（ select ）获得交叉表数据。 尝试实现此方法。