数据库Oracle第3章.pptVIP

数据库应用技术第三章深入SQL 样例数据库 共5个表 在PatrickO Neil ElizabethO Neil著 数据库原理 编程与性能 中示例数据库的基础上修改而成 1 位置信息 locations 2 顾客信息 customers 3 代理商信息 agents 4 产品信息 products 5 订单信息 orders 3 2 5TOP N问题 TOP N问题是一个在实践中经常遇到的典型问题 假设 表ranks 主键字段id 值字段score 问题 按照值字段的次序只查询出排名在某个范围的记录 这类问题在实际应用中经常出现 如网站浏览数据时分页显示 此时 把所有的数据传送到应用程序 然后只显示其中某个区间的记录 效率很 具体区分有如下几种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问题 SELECT FROMranksr1WHERE3 SELECTCOUNT FROMranksr2WHEREr2 score r1 score ORDERBYscore 不出现重复时 能查出前3条记录出现重复时 与第3条记录等值的结果都被查出来 例9 M3问题 SELECT FROMranksr1WHERE3 SELECTCOUNT DISTINCTscore FROMranksr2WHEREr2 score r1 score ORDERBYscore 例10 SELECT FROMranksr1WHERE3 SELECTCOUNT FROMranksr2WHEREr2 score r1 score ORDERBYscore 出现并列的情况下 如果有多个记录位于第3个的位置 将都不能被检索出来 极端情况下 如果有3个以上的记录都是最大值 都不能被查出来 例11 例8M2问题的另外一种写法 SELECT FROMranksWHEREidIN SELECTr1 idFROMranksr1 ranksr2WHEREr1 score r2 scoreGROUPBYr1 idHAVINGCOUNT 3 ORDERBYscore 例12 M4问题 SELECT FROMranksr1WHERE8 SELECTCOUNT FROMranksr2WHEREr2 score r1 score AND3 SELECTCOUNT FROMranksr2WHEREr2 score r1 score ORDERBYscore 对于没有并列的M1问题 不考虑其他因素 只使用SQL语句是不能解决的 原因 SQL实质上是集合的操作 两个记录如果完全相同是不可能区分开的 解决 可以引入其他的强制排序准则 人为地定义一个序 例13 SELECT FROMranksr1WHERE3 SELECTCOUNT FROMranksr2WHEREr2 score r1 scoreOR r2 score r1 scoreANDr2 id r1 id ORDERBYscore id 3 2 6SELECT语句小结 如何理解SELECT语句的执行过程 SELECTFROM WHERE GROUPBY HAVING ORDERBY 循环处理每个记录 判断是否满足WHERE子句条件 若有子查询 则进行内层循环 外层变量此时做常量处理 若多表则构造笛卡尔积 得到所有行 记录分组 将每一组视为一个整体 判断分组是否满足HAVING子句条件 对所有选出的记录集筛选出SELECT子句所需字段 最后处理 SQL实际执行时需要进行语法分析 产生执行计划 对执行方式进行优化 一般说来 尽量使用连接而不是子查询 特别是子查询内部使用外查询字段值的 尽量不把子查询作为表使用 没有必要不要使用DISTINCT GROUPBY和ORDERBY 子查询中不可有ORDERBY子句 SQL的能力SQL对关系代数是先备的 但不是可计算性的 原因 SQL非过程化 解决 加入过程控制的PL SQL 3 3DML语句 3 3 1INSERT INSERT 用来向表中插入记录 INSERTINTO VALUES INSERTINTOcustomers discnt cname city cid VALUES 12 Basics Dallas C02 一次只能插入一行记录 值列表要和字段列表对应 数量和类型 当值列表与表定义顺序一致时 可以不提供字段列表 INSERTINTOcustomersVALUES C02 Basics Dallas 12 INSERTINTO 用子查询可一次向表中插入多行记录 要求 子查询的结果列表要和字段列表对应 对表上不在字段列表中的字段的取值 依据顺序 表格定义时字段指定缺省值 置为缺省值 字段可以为空 置为NULL值 否则 出错 可以在值列表中使用关键字DEFAULT和NULL 例1 已有新创建的表agents copy 它的定义与表agents相同 要求将表agents中的内容复制到表agents copy INSERTINTOagents copySELECT FROMagents 例2 经过一段时间之后 表agents中内容发生变化 现在要求将表agents中新增的内容加入表agents copy中 假设aid不发生变化 INSERTINTOagents copySELECT FROMagentsWHEREaidNOTIN SELECTaidFROMagents copy 3 3 2DELETE DELETE 用来删除表中一行或多行记录 DELETE FROM WHERE 将表中符合条件的记录删除 如果不写WHERE条件 删除表中所有记录 在条件中可以使用子查询 例3 经过一段时间之后 表agents中内容发生变化 现在要求对于表agents中不再存在的内容 删除其在表agents copy中对应的记录 DELETEFROMagents copyWHEREaidNOTIN SELECTaidFROMagents 3 3 3UPDATE UPDATE 用来更新表中一行或多行记录 UPDATESET WHERE 将表中符合WHERE条件的记录的相应字段按照表达式重新赋值 表达式中可以使用原值 在条件和修改表达式中可以使用子查询 例4 经过一段时间之后 表agents中内容发生变化 现在要求根据表agents中的值对表agents copy中对应内容进行修改更新 UPDATEagents copySETaname SELECTanameFROMagentsWHEREaid agents copy aid lid SELECTlidFROMagentsWHEREaid agents copy aid WHEREaidIN SELECTaidFROMagents MERGE Oracle中增强的DML语句 例如 有产品表products pid quantity 和进货表pnew pid comein 用pnew更新products时 已经有的pid可以UPDATE 但是没有的pid无法UPDATE 应该INSERT MERGEINTOproductspUSINGpnewnONp pid n pid 连接表的条件 WHENMATCHEDSETquantity p quantity einWHENNOTMATCHEDINSERT pid quantity VALUES n pid ein ORACLE中UPDATE语句不提供连接功能 3 3 4DML与事务处理 在通常情况下 所有的DML语句产生的效果都是临时的 需要使用COMMIT命令来使这种变化永久化 不同会话之间的数据在没有提交之前不会相互影响 语句级回滚 一个语句要么全部成功 要么全部失败 不会发生只对其中一些记录起作用的情况 练习题 1 列出价格在0 5到1元间的产品 2 列出代理商和产品在同一地点的二元组 3 列出同时购买编号P01和P07产品的