数据库Oracle第3章.ppt

《数据库Oracle第3章.ppt》由会员分享，可在线阅读，更多相关《数据库Oracle第3章.ppt（35页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、数据库应用技术第三章 深入SQL样例数据库v共5个表，在Patrick ONeil,Elizabeth ONeil著数据库原理、编程与性能中示例数据库的基础上修改而成。1、位置信息：locationslidcityaddresspostcodeL01New YorkL02DallasL03KyotoL04DuluthL06Changchun130021L07Newark字段名字段名数据类型数据类型约束约束描述描述lidCHAR(4)NOT NULL位置编码cityVARCHAR2(10)NOT NULL城市addressVARCHAR2(30)NULL地址postcodeCHAR(6)NULL

2、邮政编码PRIMARY KEY(lid)2、顾客信息：customers字段名字段名数据类型数据类型约束约束描述描述cidCHAR(4)NOT NULL顾客编码cnameCHAR(8)NOT NULL顾客姓名cityVARCHAR2(10)NULL城市discntNUMBER(4,2)NOT NULL折扣addressVARCHAR2(40)NULL地址PRIMARY KEY(cid)cidcnamecitydiscntaddressC01TipTopDuluth10C02BasicsDallas12C03AlliedDallas8Street 55C04ACMEDuluth8Road 417

3、C06ACMEKyoto0C07IBM63、代理商信息：agents字段名字段名数据类型数据类型约束约束描述描述aidCHAR(4)NOT NULL代理商编码anameCHAR(8)NOT NULL代理商姓名lidCHAR(4)NULL城市编码percentNUMBER(3,1)NOT NULL提成率salaryNUMBER(7,2)NOT NULL工资managerCHAR(4)NULL经理PRIMARY KEY(aid)CONSTRAINT agents_fk_1 FOREIGN KEY(lid)REFERENCES locations(lid)CONSTRAINT agents_fk_2

4、 FOREIGN KEY(manager)REFERENCES agents(aid)aidanamelidpercentsalarymanagerA01SmithL01610000A02JonesL0767000A01A03BrownL0375000A02A04GrayL0167200A01A05OtasiL0454800A04A06JackL0655500A044、产品信息：products字段名字段名数据类型数据类型约束约束描述描述pidCHAR(4)NOT NULL产品编码pnameCHAR(8)NOT NULL产品名称lidCHAR(4)NOT NULL城市编码quantityNUM

5、BER(8,0)NOT NULL数量priceNUMBER(6,2)NOT NULL价格PRIMARY KEY(pid)CONSTRAINT products_fk_1 FOREIGN KEY(lid)REFERENCES locations(lid)pidpnamecityquantitypriceP01combL021114000.5P02brushL072030000.5P03razorL041506001P04PenL041253001P05pencilL022214001P06folderL021231002P07caseL0710050015、订单信息：orders字段名字段名数据

6、类型数据类型约束约束描述描述ordnoCHAR(4)NOT NULL订单编码buy_dateDATENOT NULL购买日期cidCHAR(4)NOT NULL顾客编码aidCHAR(4)NOT NULL代理商编码pidCHAR(4)NOT NULL产品编码qtyNUMBER(4,0)NOT NULL购买数量dollarsNUMBER(8,2)NOT NULL购买金额PRIMARY KEY(ordno)CONSTRAINT orders_fk_1 FOREIGN KEY(cid)REFERENCES customers(cid)CONSTRAINT orders_fk_2 FOREIGN KE

7、Y(aid)REFERENCES agents(aid)CONSTRAINT orders_fk_3 FOREIGN KEY(pid)REFERENCES products(pid)ordnobuy_datecidaidpidqtydollars10122001/01/01C001A01P01100045010112002/04/08C001A01P01100045010192001/04/01C001A02P0240018010172001/04/02C001A06P0360054010182003/03/01C001A03P0460054010232003/04/11C001A04P055

8、0045010222001/03/01C001A05P0640072010252001/02/01C001A05P0780072010132002/01/01C002A03P03100088010262001/05/01C002A05P0380070410152002/01/01C003A03P051200110410142002/01/01C003A03P051200110410212001/08/01C004A06P01100046010162001/09/01C006A01P01100050010202001/10/01C006A03P0760060010242001/03/06C006

9、A06P018004003.2.5 TOP-N问题vTOP-N问题是一个在实践中经常遇到的典型问题。v假设：表ranks（主键字段id,值字段score）。v问题：按照值字段的次序只查询出排名在某个范围的记录。v这类问题在实际应用中经常出现，如网站浏览数据时分页显示。此时，把所有的数据传送到应用程序，然后只显示其中某个区间的记录，效率很。v具体区分有如下几种M1，基本的TOP-N问题：按照score（增序）排序，列出排在最前面N位的记录。score重复（并列）时，准确地取出前N条记录。M2：按照score（增序）排序，排在最前面N位的记录，和所有与第N条等值的记录。返回记录数目可能大于N。M3

10、：按照score（增序）排序，返回对应于N个不同score值的所有记录。M4，广义的TOP-N问题：按照score（增序）排序，排名在N1到N2之间的记录。一般不考虑并列，只考虑记录数目。v通用的解决方法Idea：位于前N位，就意味着比这个值小的记录数比N少。例8：M2问题。SELECT*FROM ranks r1WHERE 3 (SELECT COUNT(*)FROM ranks r2 WHERE r2.score (SELECT COUNT(DISTINCT score)FROM ranks r2 WHERE r2.score (SELECT COUNT(*)FROM ranks r2 W

11、HERE r2.score r2.score GROUP BY r1.id HAVING COUNT(*)(SELECT COUNT(*)FROM ranks r2 WHERE r2.score r1.score)AND 3 (SELECT COUNT(*)FROM ranks r2 WHERE r2.score (SELECT COUNT(*)FROM ranks r2 WHERE r2.score r1.score OR(r2.score=r1.score AND r2.id r1.id)ORDER BY score,id;idscoreCOUNT110221322423534635746

12、84795810593.2.6 SELECT语句小结v如何理解SELECT 语句的执行过程？SELECTFROM WHEREGROUP BYHAVINGORDER BY 循环处理每个记录，判断是否满足WHERE子句条件；若有子查询，则进行内层循环，外层变量此时做常量处理 若多表则构造笛卡尔积，得到所有行 记录分组，将每一组视为一个整体 判断分组是否满足HAVING子句条件 对所有选出的记录集筛选出SELECT子句所需字段 最后处理vSQL实际执行时需要进行语法分析语法分析，产生执行计划执行计划，对执行方式进行优化。一般说来，尽量使用连接而不是子查询，特别是子查询内部使用外查询字段值的；尽量不把

13、子查询作为表使用；没有必要不要使用DISTINCT、GROUP BY和ORDER BY；子查询中不可有ORDER BY子句。vSQL的能力SQL对关系代数是先备的，但不是可计算性的。原因：SQL非过程化。解决：加入过程控制的PL/SQL。3.3 DML语句3.3.1 INSERTvINSERT：用来向表中插入记录。INSERT INTO ()VALUES();vINSERT INTO customers(discnt,cname,city,cid)VALUES(12,Basics,Dallas,C02);v一次只能插入一行记录。v值列表要和字段列表对应，数量和类型。v当值列表与表定义顺序一致时

14、，可以不提供字段列表。vINSERT INTO customersVALUES(C02,Basics,Dallas,12);INSERT INTO ();v用子查询可一次向表中插入多行记录。v要求：子查询的结果列表要和字段列表对应。对表上不在字段列表中的字段的取值，依据顺序：v表格定义时字段指定缺省值，置为缺省值。v字段可以为空，置为NULL值。v否则，出错。可以在值列表中使用关键字DEFAULT和NULL。例1：已有新创建的表agents_copy，它的定义与表agents相同，要求将表agents中的内容复制到表agents_copy。INSERT INTO agents_copySELE

15、CT*FROM agents;例2：经过一段时间之后，表agents中内容发生变化，现在要求将表agents中新增的内容加入表agents_copy中。（假设aid不发生变化）INSERT INTO agents_copySELECT*FROM agentsWHERE aid NOT IN(SELECT aid FROM agents_copy);3.3.2 DELETEvDELETE：用来删除表中一行或多行记录。DELETE FROM WHERE;v将表中符合条件的记录删除。v如果不写WHERE条件，删除表中所有记录。v在条件中可以使用子查询。例3：经过一段时间之后，表agents中内容发生

16、变化，现在要求对于表agents中不再存在的内容，删除其在表agents_copy中对应的记录。DELETE FROM agents_copy WHERE aid NOT IN(SELECT aid FROM agents);3.3.3 UPDATEvUPDATE：用来更新表中一行或多行记录。UPDATE SET=,=WHERE;v将表中符合WHERE条件的记录的相应字段按照表达式重新赋值。v表达式中可以使用原值。v在条件和修改表达式中可以使用子查询。例4：经过一段时间之后，表agents中内容发生变化，现在要求根据表agents中的值对表agents_copy中对应内容进行修改更新。UPDA

17、TE agents_copySET aname=(SELECT aname FROM agents WHERE aid=agents_copy.aid),lid=(SELECT lid FROM agents WHERE aid=agents_copy.aid),WHERE aid IN(SELECT aid FROM agents);vMERGE：Oracle中增强的DML语句。例如，有产品表products(pid,quantity)和进货表pnew(pid,comein)。用pnew更新products时，已经有的pid可以UPDATE，但是没有的pid无法UPDATE，应该INSERT

18、。MERGE INTO products pUSING pnew nON p.pid=n.pid（连接表的条件）WHEN MATCHEDSET quantity=p.quantity+einWHEN NOT MATCHEDINSERT(pid,quantity)VALUES(n.pid,ein);ORACLE中UPDATE语句不提供连接功能。3.3.4 DML与事务处理v在通常情况下，所有的DML语句产生的效果都是临时的，需要使用COMMIT命令来使这种变化永久化。v不同会话之间的数据在没有提交之前不会相互影响。v语句级回滚：一个语句要么全部成功，要么全部失败，不会发生只对其中一些记录起作用的情况。练习题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。