数据库Oracle第3章.ppt

《数据库Oracle第3章.ppt》由会员分享，可在线阅读，更多相关《数据库Oracle第3章.ppt（35页珍藏版）》请在装配图网上搜索。

1、数据库应用技术第三章 深入SQL,样例数据库,共5个表，在Patrick ONeil, Elizabeth ONeil著数据库原理、编程与性能中示例数据库的基础上修改而成。 1、位置信息：locations,2、顾客信息：customers,3、代理商信息：agents,4、产品信息：products,5、订单信息：orders,3.2.5 TOP-N问题,TOP-N问题是一个在实践中经常遇到的典型问题。 假设：表ranks（主键字段id, 值字段score）。 问题：按照值字段的次序只查询出排名在某个范围的记录。 这类问题在实际应用中经常出现，如网站浏览数据时分页显示。此时，把所有的数据传送

2、到应用程序，然后只显示其中某个区间的记录，效率很。,具体区分有如下几种 M1，基本的TOP-N问题：按照score（增序）排序，列出排在最前面N位的记录。score重复（并列）时，准确地取出前N条记录。 M2：按照score（增序）排序，排在最前面N位的记录，和所有与第N条等值的记录。返回记录数目可能大于N。 M3：按照score（增序）排序，返回对应于N个不同score值的所有记录。 M4，广义的TOP-N问题：按照score（增序）排序，排名在N1到N2之间的记录。一般不考虑并列，只考虑记录数目。,通用的解决方法 Idea：位于前N位，就意味着比这个值小的记录数比N少。,例8： M2问题。

3、 SELECT * FROM ranks r1 WHERE 3 ( SELECT COUNT(*) FROM ranks r2 WHERE r2.score r1.score) ORDER BY score; 不出现重复时，能查出前3条记录 出现重复时，与第3条记录等值的结果都被查出来,例9： M3问题。 SELECT * FROM ranks r1 WHERE 3 ( SELECT COUNT(DISTINCT score) FROM ranks r2 WHERE r2.score r1.score) ORDER BY score;,例10： SELECT * FROM ranks r1 W

4、HERE 3 ( SELECT COUNT(*) FROM ranks r2 WHERE r2.score = r1.score) ORDER BY score; 出现并列的情况下，如果有多个记录位于第3个的位置，将都不能被检索出来。 极端情况下，如果有3个以上的记录都是最大值，都不能被查出来。,例11：例8 M2问题的另外一种写法。 SELECT * FROM ranks WHERE id IN ( SELECT r1.id FROM ranks r1, ranks r2 WHERE r1.score r2.score GROUP BY r1.id HAVING COUNT(*) 3) OR

5、DER BY score;,例12：M4问题。 SELECT * FROM ranks r1 WHERE 8 ( SELECT COUNT(*) FROM ranks r2 WHERE r2.score r1.score) AND 3 ( SELECT COUNT(*) FROM ranks r2 WHERE r2.score r1.score) ORDER BY score;,对于没有并列的M1问题，不考虑其他因素，只使用SQL语句是不能解决的。 原因：SQL实质上是集合的操作，两个记录如果完全相同是不可能区分开的。 解决：可以引入其他的强制排序准则，人为地定义一个序。,例13： SELEC

6、T * FROM ranks r1 WHERE 3 ( SELECT COUNT(*) FROM ranks r2 WHERE r2.score r1.score OR (r2.score = r1.score AND r2.id r1.id) ORDER BY score, id;,3.2.6 SELECT语句小结,如何理解SELECT 语句的执行过程？ SELECT FROM WHERE GROUP BY HAVING ORDER BY, 循环处理每个记录，判断是否满足WHERE子句条件；若有子查询，则进行内层循环，外层变量此时做常量处理, 若多表则构造笛卡尔积，得到所有行, 记录分组，将

7、每一组视为一个整体, 判断分组是否满足HAVING子句条件, 对所有选出的记录集筛选出SELECT子句所需字段, 最后处理,SQL实际执行时需要进行语法分析，产生执行计划，对执行方式进行优化。一般说来， 尽量使用连接而不是子查询，特别是子查询内部使用外查询字段值的； 尽量不把子查询作为表使用； 没有必要不要使用DISTINCT、GROUP BY和ORDER BY； 子查询中不可有ORDER BY子句。,SQL的能力 SQL对关系代数是先备的，但不是可计算性的。 原因：SQL非过程化。 解决：加入过程控制的PL/SQL。,3.3 DML语句,3.3.1 INSERT,INSERT：用来向表中插入

8、记录。 INSERT INTO () VALUES (); INSERT INTO customers (discnt, cname, city, cid) VALUES (12, Basics, Dallas, C02); 一次只能插入一行记录。 值列表要和字段列表对应，数量和类型。 当值列表与表定义顺序一致时，可以不提供字段列表。 INSERT INTO customers VALUES(C02, Basics, Dallas, 12);,INSERT INTO () ; 用子查询可一次向表中插入多行记录。 要求：子查询的结果列表要和字段列表对应。 对表上不在字段列表中的字段的取值，依据顺

9、序： 表格定义时字段指定缺省值，置为缺省值。 字段可以为空，置为NULL值。 否则，出错。 可以在值列表中使用关键字DEFAULT和NULL。,例1：已有新创建的表agents_copy，它的定义与表agents相同，要求将表agents中的内容复制到表agents_copy。 INSERT INTO agents_copy SELECT * FROM agents;,例2：经过一段时间之后，表agents中内容发生变化，现在要求将表agents中新增的内容加入表agents_copy中。（假设aid不发生变化） INSERT INTO agents_copy SELECT * FROM ag

10、ents WHERE aid NOT IN ( SELECT aid FROM agents_copy);,3.3.2 DELETE,DELETE：用来删除表中一行或多行记录。 DELETE FROM WHERE ; 将表中符合条件的记录删除。 如果不写WHERE条件，删除表中所有记录。 在条件中可以使用子查询。,例3：经过一段时间之后，表agents中内容发生变化，现在要求对于表agents中不再存在的内容，删除其在表agents_copy中对应的记录。 DELETE FROM agents_copy WHERE aid NOT IN ( SELECT aid FROM agents);,3

11、.3.3 UPDATE,UPDATE：用来更新表中一行或多行记录。 UPDATE SET = , = WHERE ; 将表中符合WHERE条件的记录的相应字段按照表达式重新赋值。 表达式中可以使用原值。 在条件和修改表达式中可以使用子查询。,例4：经过一段时间之后，表agents中内容发生变化，现在要求根据表agents中的值对表agents_copy中对应内容进行修改更新。 UPDATE agents_copy SET aname = ( SELECT aname FROM agents WHERE aid = agents_copy.aid), lid = ( SELECT lid FRO

12、M agents WHERE aid = agents_copy.aid), WHERE aid IN ( SELECT aid FROM agents);,MERGE：Oracle中增强的DML语句。 例如，有产品表products(pid, quantity)和进货表pnew(pid, comein)。 用pnew更新products时，已经有的pid可以UPDATE，但是没有的pid无法UPDATE，应该INSERT。,MERGE INTO products p USING pnew n ON p.pid = n.pid （连接表的条件） WHEN MATCHED SET quantit

13、y = p.quantity + ein WHEN NOT MATCHED INSERT (pid, quantity) VALUES(n.pid, ein); ORACLE中UPDATE语句不提供连接功能。,3.3.4 DML与事务处理,在通常情况下，所有的DML语句产生的效果都是临时的，需要使用COMMIT命令来使这种变化永久化。 不同会话之间的数据在没有提交之前不会相互影响。 语句级回滚：一个语句要么全部成功，要么全部失败，不会发生只对其中一些记录起作用的情况。,练习题,1、列出价格在0.5到1元间的产品。 2、列出代理商和产品在同一地点的二元组。 3、列出同时购买编号P01和P07产品的顾客姓名。 4、列出帮助居住在Duluth的顾客，订购过不在L01的产品的代理商。 5、列出7天内连续购买过产品的顾客ID。 6、列出Tom的下属的姓名。 7、列出和Mary有同一经理的其他人的姓名。 8、列出单笔销售额最大的记录。 9、列出总销售额最大的产品的名称。 10、列出没有订过货的顾客的ID。 11、列出没有通过A05订过货的顾客的ID。 12、列出总销售额第二的产品的ID。,