数据库应用技术教程第三章深入SQL.ppt

《数据库应用技术教程第三章深入SQL.ppt》由会员分享，可在线阅读，更多相关《数据库应用技术教程第三章深入SQL.ppt（81页珍藏版）》请在三一办公上搜索。

1、数据库应用技术第三章 深入SQL,1/81,3.1 SQL概述,SQL的历史1970,Structured English Query Language(SEQUEL)，IBM1979,OracleSQL的标准化过程从SQL-86(ANSI)到SQL-2003（ANSI,ISO）SQL的设计初衷是给高级用户提供一种通用的、易于学习和理解的数据库操作方式。演变为给程序员使用的数据库标准接口。,2/81,SQL的特点,面向集合的操作方式，是关系代数的实现；高度非过程化。只需要提出“What”，不需要指出“How”；上下文无关，运行结果取决于数据；语言风格统一，类自然语言，简单易学；既是自含式语言，

2、又是嵌入式语言,3/81,SQL的分类,查询（Data Query Language,DQL）SELECT操纵（Data Manipulate Language,DML）INSERT,DELETE,UPDATE定义（Data Definition Language,DDL）CREATE,DROP,ALTER。控制（Data Control Language,DCL）GRANT,REVOKE,COMMIT,ROLLBACK。,4/81,SQL的基本语法,SQL语句不区分大小写，可以写在多行，各个单词之间使用分隔符（空格，回车，制表符）分隔。SQL语句中的关键字属于保留字，不能用于其他地方。数字常

3、量的写法和通常程序语言一致，字符串使用单引号包含，字符串中的单引号使用两个连续的单引号转义表示，不区分字符和字符串。,5/81,SQL书写规范,为便于书写和阅读，通常采用如下的书写规范：1、关键字大写，其他标识符小写字段名大写？2、每个子句单起一行3、使用制表符缩进,6/81,标准与可移植性,在SQL92标准中定义了四种级别：Entry,Transitional,Intermediate,and Full国际标准与各个厂商之间的不兼容性。事实上的不可移植性字符串连接|与+；专有的查询关键字数据类型与函数特殊语义；可移植性神话,7/81,3.2 简单SELECT语句,8/81,3.2.1 基本语

4、句,语法SELECT DISTINCT|ALL*|查询项列表 FROM 表名;说明查询项：字段|表达式 AS 别名 无条件：查询出表的所有记录。次序不确定、不稳定，依赖于数据库的实现。*：按特定顺序列举所有字段。,9/81,3.2.1 基本语句,当明确要查询哪些字段的时候，将字段名称列在SELECT的后面，这样语句含义清楚，性能也有所提高。AS 别名：别名起到简化作用，通常还作为表达式的标题。特别是很多使用SQL的开发工具直接使用SELECT语句中每个查询表达式的名称作为标识符，这种情况下更需要给出别名。表达式与别名之间的AS是一个冗余的关键字，可以省略，直接以空白符分隔。DISTINCT|A

5、LL：在整个查询内容前加上DISTINCT，返回结果中不重复的内容。不重复针对所有要查询的表达式集合而言。ALL是缺省值，列出包括重复的所有记录。另外，DISTINCT是一个比较费时间的操作，使用时需慎重。,10/81,3.2.1 基本语句,查询的内容可以是字段，也可以是由字段、常数通过运算符、函数构成的复杂表达式。这种计算要对表上的所有行都施加运算。SELECT sid,UPPER(sname),salary+100FROM sales;,11/81,3.2.1 基本语句,在某些情况下，也可能使用SQL计算纯粹常量表达式的值，如检索服务器当前时间。SELECT SYSDATE,1+2FROM

6、 sales;依赖于表中记录的数目，将得到若干行（也可能是0行）同样的结果。而这一般不是我们想要得到的结果。,12/81,3.2.1 基本语句,如果明确地只想得到一行结果在MS-SQL和MySQL中，可以使用不带FROM子句的SELECT语句；SELECT 1+2在Oracle中，不允许没有FROM子句。系统提供了一个名叫dual的表，保证其中只有一条记录，不依赖于表的计算可以使用这个表。SELECT SYSDATE,1+2 FROM dual;,13/81,3.2.2 筛选,可以使用WHERE子句来筛选出满足某些条件的记录。WHERE 逻辑表达式基本比较运算符=，=，它们都具有通常的含义。比

7、较运算符的两端都可以是字段或者表达式。其他相当于比较运算符的结构BETWEEN ANDINLIKENULL的运算逻辑运算符NOT AND OR,14/81,其他where条件,1、BETWEEN AND 如果要限定某个表达式的值在某个区间内，可以使用这个关键字。例：查询出薪水在1000和2000之间的记录的sid和salary值。SELECT sid,salaryFROM salesWHERE salary BETWEEN 1000 AND 2000;这是一个闭区间，等于两端值的记录也会被查出来。另外，一般要求区间前面的值小于后面的值，否则将查不出结果。2、ININ运算符用来检验某个表达式的值

8、是否包含在一个指定的值集合内。例：查询出名字叫Tom、Jack或者Mary的销售员的sid和salary值。SELECT sid,salaryFROM salesWHERE sname IN(Tom,Jack,Mary);,15/81,3、LIKE如果要对字符串进行通配查找时，需要使用LIKE运算符。它允许在表达式中使用专门的通配符，表示符合某种规则的结果。%：表示可以匹配0个或任意多个字符；_：表示通配一个字符。例1：查询出所有名字以S开头的销售员记录，例如Smith、Sa和S等都符合这个条件。SELECT sid,salaryFROM salesWHERE sname LIKE S%;例2

9、：查询出来所有名字中含有第一个是a，第三个是b，中间可以是任意字符的子串的顾客信息。SELECT*FROM customersWHERE cname LIKE%a_b%;,16/81,如果要查询的通配表达式中含有%或者_做为通常的含义出现，那么需要使用转义描述。例3：查询名字以%开头，以_结尾的销售员id和工资。SELECT sid,salaryFROM salesWHERE sname LIKE%_ ESCAPE;在LIKE的后面加上ESCAPE关键字说明通配表达式中使用的转义字符（上例为），则转义字符后面的直接字符不按照通配符对待。4、IS NULL见后,17/81,可以使用逻辑运算符AN

10、D，OR，NOT将多个关系表达式连接在一起，构成复杂的逻辑表达式。它们具有通常的逻辑含义。在不使用括号的时候，AND的优先级高于OR运算。另外，对于一些特别的表达式的否定，可以使用另外的语法。如对IS NULL的否定可以写成IS NOT NULL。,逻辑运算,18/81,空值逻辑,空值NULL用来表示 未知的(Unknown)，不适用的(Inapplicable)不能直接和空值进行比较，需要使用IS NULL和IS NOT NULL。例4：查询地址非空的顾客信息。SELECT*FROM customersWHERE address IS NOT NULL;反例：SELECT*FROM sale

11、sWHERE salary 1000 OR salary=1000;将不能查询出所有记录，因为salary为空的不满足任何条件。等价：SELECT*FROM salesWHERE salary IS NOT NULL;,19/81,NVL函数NVL(X,Y)=X,IF X IS NOT NULL Y,IF X IS NULL例5：查询薪水大于1000的销售员信息，薪水为空按照2000计算SELECT*FROM salesWHERE NVL(salary,2000)1000;SELECT*FROM salesWHERE salary 1000 or salary IS NULL;例6：欲发放奖金

12、，为薪水的10%，如果薪水为空，按照1000计算SELECT NVL(salary,1000)*0.1 FROM sales,20/81,3.2.3 聚集函数与分组统计,聚集函数：对于多条记录统计出一个结果的函数。包括：COUNT，MAX，MIN，AVG，SUM聚集函数和NULL的关系COUNT(*)：计数符合条件的所有记录，不考虑是否为空。COUNT(字段)：此字段为空的行不计数在内。MAX、MIN：NULL不参与比较。AVG、SUM：NULL不参与运算。聚集函数里面可以使用DISTINCT，只对不重复的字段进行统计。缺省为ALL,21/81,聚集函数-cont,数据类型SUM、AVG 只用

13、于数值型。MAN、MIN可用于数值、字符、日期。Count的几种用法SELECT COUNT(*)返回记录数SELECT COUNT(字段)字段为空的记录不参与计数SELECT COUNT(DISTINCT字段)重复字段不参与计数,22/81,分组统计GROUP BY,分组子句GROUP BY按照GROUP BY条件对记录进行分组，然后进行统计。每一个分组得出一个结果。可以使用多个条件，多个条件的次序关系？分组筛选子句HAVING对分组后的每个组按照条件进行筛选；只能在GROUP BY后面出现。一般使用聚集函数对分组后数据的统计值进行筛选直接使用分组字段通常无意义，其他字段非法,23/81,例

14、1：当前有多少订单。SELECT COUNT(*)FROM orders;例2：当前locations内有多少个邮编，不重复数有多少？SELECT COUNT(postcode),COUNT(DISTINCT postcode)FROM locations;例3：在locations中有多少个不重复的城市？SELECT COUNT(DISTINCT city)FROM locations;例4：在一周之前，有多少个顾客购买过产品？SELECT COUNT(DISTINCT cid)FROM ordersWHERE buy_date sysdate-7,24/81,例5：列出最后一次出入库的时间

15、例6：列出共有多少种产品已经被销售过例7：列出每一个顾客ID，总购买额。例8：根据出入库记录，计算库存数,SELECT MAX(date_happen)FROM stock_in_out,SELECT COUNT(DISTINCT PID)FROM ORDERS,SELECT cid,SUM(dollars)FROM ordersGROUP BY cid;（不考虑顾客表中的姓名）,SELECT SUM(qty_in_out)FROM stock_in_out,25/81,例9：列出每一个顾客ID，姓名及总购买额。例10：按产品分类，总销售额最高值是多少？,SELECT o.cid,ame,SU

16、M(o.dollars)FROM orders o,customers cWHERE o.cid=c.cidGROUP BY o.cid,ame;,cname虽然冗余，但必须出现在GROUP BY后面，除非使用其他方法。,SELECT MAX(SUM(dollars)FROM ordersGROUP BY pid;,26/81,例11：对于销售数量大于1000的订单，按照产品计算平均销售额。例12：对于平均销售金额大于1000的产品，列出id和总销售额。例13：求至少购买过两次单笔销售额超过1000的顾客及总购买额。,SELECT pid,SUM(dollars)FROM ordersGROU

17、P BY pidHAVING AVG(dollars)1000;,SELECT cid,SUM(dollars)FROM ordersWHERE dollars 1000GROUP BY cidHAVING COUNT(*)1;,SELECT pid,AVG(dollars)FROM ordersWHERE qty 1000GROUP BY pid;,27/81,小结,返回数据无GROUP BY子句，带聚集函数的查询一定只返回一行数据，但是可能是空值。如果表中无数据，或者无满足条件数据，或者所有符合条件数据都是空，COUNT返回0，其他函数则返回空值。带有聚集函数的SQL对查询表达式的要求未使

18、用GROUP BY，但出现了一个聚集函数，其他表达式也必须是聚集函数使用GROUP BY，查询表达式必须是使用聚组函数的表达式分组字段直接参与的表达式其他字段直接出现，为语法错误HAVING子句的要求同查询表达式例9的三种写法,28/81,3.2.4 排序,ORDER BY 排序准则列表排序准则：字段|表达式|别名|序号 ASC|DESCASC：升序，缺省值。DESC：降序。字段名：不一定在SELECT中列出。表达式：有些DBMS不支持。序号：从1开始排。在多个SELECT进行集合运算时，特别适合使用序号方式。按照关系数据库的原理来说，ORDER BY只能是SELECT语句的最后一个子句。在排

19、序结果中，NULL通常被排在一起，放在结果集的前面或后面（依赖于实现或者特别的选项）。,29/81,3.3 复杂的SELECT语句,30/81,3.3.1 连接查询,将多个表的数据进行笛卡尔积，在此基础上进行查询，称为连接查询(Join)。连接条件：在笛卡尔积的基础上，通常要根据多个表之间相关联的某些字段之间的判断条件进行筛选，这种多表之间的筛选条件称为连接条件。根据连接条件，可以分为：等值连接(Equal join)、不等值连接(Non-equal join)、自连接(Self join)和外连接(Outer join)。,31/81,例1：找出所有顾客购买的商品名称和数量。,SELECT

20、products.pname,orders.qtyFROM products,ordersWHERE products.pid=orders.pid;,或简写为：SELECT p.pid,p.pname,o.qtyFROM products p,orders oWHERE p.pid=o.pid;,例2：找出住在Dallas或Duluth的顾客购买过产品的产品号。,SELECT o.pid FROM orders o,customers cWHERE o.cid=c.cid AND(c.city=Dollas OR c.city=Duluth);,32/81,自连接,例3：列出每个销售员及其经

21、理的姓名。（自连接）SELECT s1.sname as 下属,s2.sname as 经理FROM sales s1,sales s2WHERE s1.manager=s2.sid;自连接对于同一个表的两种不同角色的使用并不真正复制数据，必须使用表的别名。,33/81,例4：找出在同一城市居住的顾客对。SELECT c1.cid,c2.cidFROM customers c1,customers c2WHERE c1.city=c2.city;自身配对不列出WHERE c1.city=c2.city AND c1.cid c2.cid;不重复列出(a,b)和(b,a)WHERE c1.cit

22、y=c2.city AND c1.cid c2.cid;,34/81,例5：找出比Smith工资高的销售员。SELECT a2.snameFROM sales a1,sales a2WHERE a1.sname=Smith AND a2.salary a1.salary;当有多个smith时，该问题的答案例6：写出购买过商品的顾客姓名和产品名称。SELECT ame,p.pnameFROM customers c,products p,orders oWHERE o.cid=c.cid AND o.pid=p.pid;一般来说，N个表连接需要N-1个连接条件。,35/81,例7：写出购买过单价

23、超过1元的产品的顾客名单。（多表连接）SELECT ame FROM orders o,customers c,products pWHERE o.cid=c.cid AND o.pid=p.pid AND p.price 1;例8：找出与经理在同一城市的销售员。SELECT s2.snameFROM sales s1,sales s2,locations l1,locations l2WHERE s1.manager=s2.sid AND s1.lid=l1.lid AND s2.lid=l2.lid AND l1.city=l2.city;,36/81,3.3.2 子查询,子查询：出现在另

24、外一个SQL语句中的查询。里面出现的SQL语句也被称为子查询(subquery)或内查询(inner query)外面的查询被称为外查询(outer query)子查询出现的位置一般出现在where子句中按照不同的扩展语法，也可以出现在select、having和from子句中,37/81,使用子查询的方法,在where子句使用子查询，有以下几种方法字段表达式 比较运算符(subquery)字段表达式 IN(subquery)字段表达式 比较运算符 量词(subquery)NOT EXISTS(subquery),38/81,1、直接使用子查询,如果子查询返回一个数据，则可以作为直接数参与普通

25、比较运算。例1：查询与编号为A01的销售员工资相同的其他人。SELECT*FROM sales WHERE salary=(SELECT salaryFROM salesWHERE sid=a01);,39/81,例2：查询比Smith工资高的销售员信息。SELECT*FROM sales WHERE salary(SELECT salary FROM sales WHERE sname=Smith);（需要保证只有一个叫Smith的人）,40/81,例3：查询比平均工资高的人员信息。SELECT*FROM salesWHERE salary(SELECT AVG(salary)FROM sa

26、les);例4：销售量第二高的单笔销售额。SELECT MAX(dollars)FROM ordersWHERE dollars(SELECT MAX(dollars)FROM orders);,41/81,直接使用子查询的要求,要求子查询必须返回一行（或零行）数据如果子查询不返回数据，作为NULL值处理如果返回多行数据，产生运行时错误（非语法错误）保证上述要求的方法主键（或唯一键）参与的查询使用无GROUP BY的聚组函数其他逻辑保证出现在SELECT中的子查询，必须符合上面要求,42/81,2、量词,如果子查询返回多行结果，需要使用量词和关系运算符。表达式 比较运算符 量词(子查询)量词包

27、括ALL，SOME（ANY）。ALL意味着子查询所返回的所有记录的值均满足条件；而SOME表示存在一些记录的值满足条件；ANY的意义与SOME相同。ALL相当于谓词逻辑中的全称量词，而SOME相当于存在量词。,43/81,例5：找出比工作在L01的所有人工资高的人员。SELECT*FROM salesWHERE salary ALL(SELECT salary FROM sales WHERE lid=L01);,44/81,例6：求通过居住在L01的销售员购买货物的顾客ID。SELECT cidFROM ordersWHERE sid=SOME(SELECT sid FROM salesWH

28、ERE lid=L01);,45/81,例7：薪水最高的sid及姓名。SELECT sid,sname FROM sales WHERE salary=(SELECT MAX(salary)FROM sales);或者WHERE SALARY=ALL(SELECT salary FROM sales);,46/81,3、使用IN,除了使用量词外，也可以使用IN进行比较。IN等价于=SOME，NOT IN 等价于 ALL。例8：没有订过货的顾客ID和姓名。SELECT cid,cnameFROM customersWHERE cid NOT IN(SELECT cid FROM orders);

29、,47/81,例9：找出工作地点和薪金与Smith相同的其他人员。SELECT*FROM salesWHERE lid IN(SELECT lid FROM sales WHERE sname=Smith)AND salary IN(SELECT salary FROM sales WHERE sname=Smith);当有多个Smith时，上面语句出错,48/81,例9：找出工作地点和薪金与Smith相同的其他人员在Oracle中可以写作：SELECT*FROM salesWHERE(lid,salary)IN(SELECT lid,salary FROM sales WHERE sname

30、=Smith);还可以写为：WHERE lid|salary IN(SELECT lid|salary FROM sales WHERE sname=Smith);另外一种写法：SELECT*FROM sales aWHERE EXIST(SELECT*FROM sales WHERE sname=Smith AND lid=a.lid AND salary=a.salary);,49/81,例10：找出住在Duluth的顾客通过位于L01的代理订货的订单号。写SQL,50/81,例10：找出住在Duluth的顾客通过位于L01的代理订货的订单号。SELECT ordnoFROM orders

31、WHERE cid IN(SELECT cid FROM customers WHERE city=Duluth)AND sid IN(SELECT sid FROM sales WHERE lid=L01);在Oracle中可以写作SELECT ordnoFROM ordersWHERE(cid,sid)IN(SELECT cid,sid FROM customers c,sales a WHERE c.city=Duluth AND a.lid=L01);,51/81,量词与其他形式之间的转换,很多情况下，比较运算符跟一个量词可以转换为其他的写法。如：,52/81,4、EXISTS,使用E

32、XISTS和NOT EXISTS判断子查询是否返回行（不考虑具体数据）。通常将外部的数据引入到子查询内部作为条件。例11：没有通过A05订货的顾客姓名。SELECT ameFROM customers cWHERE NOT EXISTS(SELECT*FROM orders oWHERE sid=A05 AND o.cid=c.cid);该语句同时也会返回从未订过货的顾客，若想滤掉可以：SELECT DISTINCT ame FROM orders o1,customers cWHERE o.cid=c.cid ANDNOT EXISTS(SELECT*FROM orders o2 WHERE

33、 sid=A05 AND o2.cid=c.cid);,53/81,使用NOT EXIST进行For-All查询例12：在所有销售员处都订过货的顾客ID。思考！写SQL。Select distinct cid From customer cWhere not exists(select*from sale a where not exist(Select*from orders o where o.sid=c.sid and o.cid=c.cid);,54/81,使用NOT EXIST进行For-All查询例12：在所有销售员处都订过货的顾客ID。对于顾客cid，不存在他没订过货的销售员。S

34、ELECT DISTINCT cid FROM customers cWHERE NOT EXISTS(SELECT*FROM sales a WHERE NOT EXISTS(SELECT*FROM orders o WHERE o.sid=a.sidAND o.cid=c.cid);,55/81,子查询的使用建议,一般不需要DISTINCT一般不需要排序,56/81,3.3.3 集合运算,SELECT查询出来的结果是一个集合，两个查询语句的结果可以进行集合运算。这时两个查询的来源可以没有任何关系，只要求查询表达式的数目和对应数据类型相一致。,57/81,集合运算有如下几种并集运算：取两个查

35、询结果的并集。使用UNION关键字。例1：SELECT city FROM locationsUNIONSELECT city FROM customers;这个操作将合并完全相同的数据形成一个结果记录。如果要保留重复的记录，需要使用 UNION ALL代替UNION。交集运算：取两个查询结果的交集，使用INTERSECT关键字。差集运算：取两个查询结果的差集。使用MINUS（Oracle）或者EXCEPT（MSSql）关键字。可以对集合运算的总体结果进行排序，在语句的最后面写ORDER BY。这种情况下使用序号作标识是比较方便的。,58/81,3.3.4 其他高级SELECT语言,一、树状查

36、询二、灵活的子查询,59/81,3.3.4 SELECT语句小结,如何理解SELECT 语句的执行过程？SELECT FROM WHERE 简单比较，字段IN()，连接条件。组合条件 GROUP BY HAVING ORDER BYDB先看FROM子句，若多表则构造笛卡尔积，得到所有行；循环处理每个记录，判断时满足WHERE子句条件；若有子查询，则进行内层循环，外层变量此时做常量处理；若有GROUP BY子句，记录分组，对每一组视为一个整体，判断是否满足HAVING子句条件；对所有选出的记录集筛选出SELECT子句所需字段；最后一步处理ORDER BY子句。,60/81,SQL实际执行时需要进

37、行语法分析，产生执行计划，对执行方式进行优化。一般说来，尽量使用连接而不是子查询，特别是子查询内部使用外查询字段值的；尽量不把子查询作为表使用；没有必要不要使用DISTINCT，GROUP BY，ORDER BY；子查询中不可有ORDER BY子句。SQL的能力SQL对关系代数是先备的，但不是可计算性的，原因是SQL非过程化。PL/SQL加入过程控制。SQL能力缺陷聚组函数有限；树状查询（闭包查询），ORACLE中有一个子句文本检索 如：搜索，关键字5个出现2个（10）即可,61/81,3.4 TOP-N问题,TOP-N问题是一个在实践中经常遇到的典型问题。假设有一个表ranks，其中包含一个

38、主键字段id，一个值字段score。问题是，按照值字段的次序只查询出排名在某个范围的记录。具体区分有如下几种：M1：按照score（增序）排序，排在最前面N位的记录（基本的TOP-N问题）。如果值字段出现重复（并列）时，也要准确地取出N条记录。M2：按照score（增序）排序，排在最前面N位的记录，并且包含所有与第N条等值的记录（返回记录数目可能大于N）。M3：按照score（增序）排序，返回score不同值的出现位于前N个的所有记录。M4：按照score（增序）排序，排名在N1到N2之间的记录。（广义的TOP-N问题），一般不考虑并列，只考虑记录数目。如网站数据分页浏览,62/81,专用方案

39、-MySql,MySql对此类问题的支持最为充分。SELECT语句的选项LIMIT(m,)n如果只有一个参数N，将只返回查询的前N个结果；如果有两个参数m和n，将返回结果集中从第m条记录（从零开始计数）开始的n个记录。配合ORDER BY子句就可以完成前面提出的问题。例子例1：M1问题的MySql解。SELECT*FROM ranks ORDER BY score LIMIT 3;例2：M4问题的MySql解。SELECT*FROM ranks ORDER BY score LIMIT 2,3;在MySql中没有直接专用方法解决M2和M3问题。,63/81,专用方案-MSSQL,在MSSql中

40、，可以在SELECT中使用TOP N选项，其含义是返回查询的前N行。例3：M1问题的MSSql解。SELECT TOP 3 FROM ranks ORDER BY score;例4：M2问题的MSSql解。SELECT TOP 3 WITH TIES*FROM ranks ORDER BY score;通过子查询来解决M4问题例5：M4问题的MSSql解。SELECT TOP 3*FROM ranks WHERE id NOT IN(SELECT TOP 2 id FROM ranks ORDER BY score)ORDER BY score;在MSSql中没有直接专用方法解决M3问题。,6

41、4/81,专用方案-Oracle,Oracle对此类问题的支持比较弱。Oracle中提供了一个伪列（pseudo-column）ROWNUM，其使用语法如同一个字段一样，但并不是表中字段，因此称之为伪列。ROWNUM表示当前返回的记录是整个结果集合的第几个（从1开始计数）。因此可以写出这样的例子：例6：M1问题在Oracle中一个错误的解。SELECT*FROM ranksWHERE ROWNUM=3ORDER BY score;很遗憾，上面的例子并不能得到期望的结果。因为ROWNUM这个伪列是作为一个WHERE条件出现的，所以要先于ORDER BY计算。所以上例的结果是选出开始的3条记录（任

42、意次序），然后进行排序。在Oracle中如何解决上面的M1问题？,65/81,专用方案-Oracle,为在Oracle中解决上面的M1问题，需要使用一种特殊的语法，在FROM中使用子查询：例7：M1问题的Oracle解。SELECT*FROM(SELECT*FROM ranks ORDER BY score)WHERE ROWNUM=3;在Oracle中，很难用ROWNUM来解决剩下的问题。,66/81,通用的解决方法,处于前N位，就意味着比这个值小的记录数比N少。从这个想法出发，先看一个语句。例8：M2问题。SELECT*FROM ranks r1WHERE 3(SELECT COUNT(*

43、)FROM ranks r2 WHERE r2.score r1.score)ORDER BY score;仔细研究这个语句，在不出现重复的情况下，确实能查出前3个记录，但如果出现重复时，与第3个记录等值的结果都能被查出来，因此这个语句解决的是M2问题。,67/81,例9：M3问题。SELECT*FROM ranks r1WHERE 3(SELECT COUNT(DISTINCT score)FROM ranks r2 WHERE r2.score r1.score)ORDER BY score;,68/81,例10：SELECT*FROM ranks r1WHERE 3=(SELECT CO

44、UNT(*)FROM ranks r2 WHERE r2.score=r1.score)ORDER BY score;什么结果？,69/81,例10：SELECT*FROM ranks r1WHERE 3=(SELECT COUNT(*)FROM ranks r2 WHERE r2.score=r1.score)ORDER BY score;在出现并列的情况下，如果有多个记录位于第3个的位置，将都不能被检索出来。在极端情况下，如果有3个以上的记录都是最大值，都不能被查出来。,70/81,例11：例8的另外一种写法。SELECT*FROM ranksWHERE id IN(SELECT r1.i

45、d FROM ranks r1,ranks r2 WHERE r1.score r2.score GROUP BY r1.id HAVING COUNT(*)3)ORDER BY score;,71/81,例12：M4问题。SELECT*FROM ranks r1WHERE 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;当然其中的子查询必须重复写两次。同样，这个方法不能避免并列问题。,

46、72/81,对于没有并列的M1问题，不考虑其他因素，只使用SQL语句是不能解决的。因为SQL实质上是集合的操作，两个记录如果完全相同是不可能区分开的。在实际中，我们可以引入其他的强制排序准则，人为地定义一个序。例13：SELECT*FROM ranks r1WHERE 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;,73/81,3.5 DML语句,74/81,3.5.1 INSERT,INSERT:插入记录INSERT I

47、NTO 表名(字段列表)VALUES(值列表);这种形式的INSERT语句一次只能插入一行。值列表要和字段列表对应，数量和类型。当值列表与表定义顺序一致时，可以不给字段列表。INSERT INTO 表名(字段列表)子查询;可以利用子查询一次插入多行，子查询的结果列表要和字段列表对应。对于不在字段列表中的字段，按以下步骤决定其值：表格定义时字段指定缺省值，置为缺省值。字段可以为空，置为NULL值。否则会出错。另外，可以在值列表中使用DEFAULT和NULL关键字。,75/81,INSERT INTO customers(discnt,cname,city,cid)VALUES(12,Basics

48、,Dallas,C02);例1：已创建一个新表sales_copy，定义与sales相同，现要将sales中内容复制到里面。INSERT INTO sales_copySELECT*FROM sales;例2：经过一段时间之后，sales中内容有所变化，现要将新填内容放入sales_copy中。（假设sid不发生变化）INSERT INTO sales_copySELECT*FROM salesWHERE cid NOT IN(SELECT cid FROM sales_copy);,76/81,3.5.2 DELETE,DELETE：用来删除表中一行或多行记录。DELETE FROM 表名

49、WHERE 条件;将表中符合条件的记录删除，如果不写WHERE条件，将删除所有记录。在条件中可以使用子查询。例3：对于sales表中已经不存在的内容，将sales_copy表中对应记录删除。DELETE FROM sales_copy WHERE sid NOT IN(SELECT sid FROM sales);,77/81,3.5.3 UPDATE,UPDATE：用来更新表中一行或多行记录。UPDATE 表名SET字段=表达式,字段=表达式WHERE 条件;将表中符合条件的记录的相应字段按照表达式重新赋值。表达式中可以使用原值。在条件和修改表达式中可以使用子查询。例4：按照sales中的值

50、修改sales_copy表中对应内容。UPDATE sales_copySET sname=(SELECT sname FROM sales WHERE sid=sales_copy.sid),lid=(SELECT lid FROM sales WHERE sid=sales_copy.sid),WHERE sid IN(SELECT sid FROM sales);,78/81,其他增强的DML语句,MERGE：Oracle中增强的DML语句。例如，有产品表products(pid,quantity)和进货表pnew(pid,comein)。用pnew更新products时，已经有的pid