数据库Oracle第3章2.ppt

数据库应用技术 第三章 深入SQL,样例数据库,共5个表，在Patrick ONeil, Elizabeth ONeil著数据库原理、编程与性能中示例数据库的基础上修改而成。 1、位置信息：locations,2、顾客信息：customers,3、代理商信息：agents,4、产品信息：products,5、订单信息：orders,3.2.3 子查询,子查询：出现在另外一个SQL语句中的查询。 里面出现的SQL语句也被称为子查询(subquery)或内查询(inner query) 外面的查询被称为外查询(outer query) 子查询出现的位置 一般出现在where子句中 按照不同的扩展语法，也可以出现在select、having和from子句中,在where子句使用子查询，标准的语法有 字段表达式 比较运算符 (子查询) 字段表达式 比较运算符 量词 (子查询) 字段表达式 IN (子查询) NOT EXISTS (子查询),子查询返回1个数据时，可直接参与比较运算。 注意：子查询应出现在比较运算符的右端。,例1：查询与编号为A01的代理商工资相同的其他人。 SELECT * FROM agents WHERE salary = ( SELECT salary FROM agents WHERE aid = A01 );,例2：查询比Smith工资高的代理商信息。 SELECT * FROM agents WHERE salary ( SELECT salary FROM agents WHERE aname = Smith );,需要保证只有一个叫Smith的人！,例3：查询比平均工资高的人员信息。 SELECT * FROM agents WHERE salary ( SELECT AVG(salary) FROM agents );,例4：列出单笔销售额第二高的销售额。 SELECT MAX(dollars) FROM orders WHERE dollars ( SELECT MAX(dollars) FROM orders );,上面的例子都要求子查询必须返回0条或者1条结果! 如果子查询返回0条数据，作为NULL值处理； 否则，产生运行时错误（非语法错误）。 用户需要在逻辑上和数据上保证返回，一般使用主键检索或唯一性聚组函数。,在where子句使用子查询，标准的语法有 字段表达式 比较运算符 (子查询) 字段表达式 比较运算符 量词 (子查询) 字段表达式 IN (子查询) NOT EXISTS (子查询),子查询返回多行结果时，需要使用量词和关系运算符。 量词包括ALL，SOME（ANY） ALL相当于谓词逻辑中的全称量词，而SOME相当于存在量词。 ALL意味着子查询所返回的所有记录的值均满足条件； SOME表示存在一些记录的值满足条件； ANY的意义与SOME相同，但由于ANY在英语中有歧义（有任意的意思），所以一般不使用。,例5：找出代理商信息，他的工资高于所有工作在L01的代理商。 SELECT * FROM agents WHERE salary ALL ( SELECT salary FROM agents WHERE lid = L01 );,WHERE salary ( SELECT MAX(salary) FROM agents WHERE lid = L01 );,工作在L01，且具有最高工资的那个人是否被返回？,例6：列出曾经通过工作在L01的代理商购买过货物的顾客的ID。 SELECT cid FROM orders WHERE aid = SOME ( SELECT aid FROM agents WHERE lid = L01 );,例7：列出薪水最高的代理商的ID和姓名。 （请在纸上练习写SQL） SELECT aid, aname FROM agents WHERE salary = ( SELECT MAX(salary) FROM agents );,WHERE salary = ALL ( SELECT salary FROM agents);,不需要DISTINCT,在where子句使用子查询，标准的语法有 字段表达式 比较运算符 (子查询) 字段表达式 比较运算符 量词 (子查询) 字段表达式 IN (子查询) NOT EXISTS (子查询),WHERE aname IN(Smith, Mary, Kate); 常量集合 变量集合 IN 等价于 = SOME NOT IN 等价于 ALL,例6：列出曾经通过工作在L01的代理商购买过货物的顾客的ID。 SELECT cid FROM orders WHERE aid = SOME ( SELECT aid FROM agents WHERE lid = L01 );,WHERE aid IN ( SELECT id FROM agents WHERE lid = L01 );,不需要DISTINCT,例8：列出从未订过货的顾客的ID和姓名。 SELECT cid, cname FROM customers WHERE cid NOT IN ( SELECT cid FROM orders );,WHERE cid ALL ( SELECT cid FROM orders );,？ WHERE cid IN (customers中cid orders中cid),例9：找出工作地点和薪水都与Smith相同的其他代理商信息。（请在纸上练习写SQL） SELECT * FROM agents a1, agents a2 WHERE a1.lid = a2.lid AND a1.salary = a2.salary AND a2.aname = Smith;,用IN(子查询)的方式写 SELECT * FROM agents WHERE lid IN ( SELECT lid FROM agents WHERE aname = Smith) AND salary IN ( SELECT salary FROM agents WHERE aname = Smith);,多个叫Smith的人？且工资和工作地点不一样怎么办？,Smith: L01, 2000 Smith: L02, 3000 ? Mary: L01, 3000,在Oracle中WHERE可以写作 SELECT * FROM agents WHERE (lid, salary) IN ( SELECT lid, salary FROM agents WHERE aname = Smith);,例10：找出住在Duluth的顾客通过工作在L01的代理商订货的订单号。（请在纸上练习写SQL） SELECT ordno FROM orders o, customers c, agents a WHERE o.aid = a.aid AND o.cid = c.cid AND c.city = Duluth AND a.lid = L01);,用IN(子查询)的方式写 SELECT ordno FROM orders WHERE cid IN ( SELECT cid FROM customers WHERE city = Duluth) AND aid IN ( SELECT aid FROM agents WHERE lid = L01);,在Oracle中WHERE可以写作 SELECT ordno FROM orders WHERE (cid, aid) IN ( SELECT cid, aid FROM customers c, agents a WHERE c.city = Duluth AND a.lid = L01);,表上没有合适的连接条件，所以与题意不符。,比较运算符跟一个量词可以转换为其他的写法。,在where子句使用子查询，标准的语法有 字段表达式 比较运算符 (子查询) 字段表达式 比较运算符 量词 (子查询) 字段表达式 IN (子查询) NOT EXISTS (子查询),使用EXISTS和NOT EXISTS可以判断子查询是否返回记录（不考虑具体数据）。 使用时，通常将外部的数据引入到子查询，作为子查询内部条件的参数。,例9：找出工作地点和薪水都与Smith相同的其他代理商信息。 SELECT * FROM agents a WHERE EXIST ( SELECT * FROM agents WHERE aname = Smith AND lid = a.lid AND salary = a.salary);,例11：列出从未通过工作在L05的代理商订过货的顾客的姓名。（请在纸上练习写SQL） SELECT ame FROM customers c WHERE NOT EXISTS ( SELECT * FROM orders o, agents a WHERE o.aid = a.aid AND a.lid = L05 AND o.cid = c.cid); 这条语句会返回在其他代理商处也从未订过货的顾客！,只关心是否有返回结果，不关心返回什么,若想滤掉可以 SELECT ame FROM orders o1, customers c WHERE o.cid = c.cid AND NOT EXISTS ( SELECT * FROM orders o2, agents a WHERE o2.aid = a.aid AND a.lid = L05 AND o2.cid = c.cid);,或者 SELECT ame FROM customers c WHERE EXISTS ( SELECT * FROM orders o1 WHERE o1.cid = c.cid) AND 0 = ( SELECT COUNT(*) FROM orders o2, agents a WHERE o2.aid = a.aid AND a.lid = L05 AND o2.cid = c.cid);,使用NOT EXISTS进行For-All查询 例12：列出在所有代理商处都订过货的顾客的ID。 （请在纸上练习写SQL） 对于顾客cid，不存在他没订过货的代理商！ SELECT DISTINCT cid FROM customers c WHERE NOT EXISTS ( SELECT * FROM agents a WHERE NOT EXISTS ( SELECT * FROM orders o WHERE o.aid = a.aid AND o.cid = c.cid );,子查询的结果作为集合的使用方式,在where子句使用子查询，标准的语法有 字段表达式 比较运算符 (子查询) 字段表达式 比较运算符 量词 (子查询) 字段表达式 IN (子查询) NOT EXISTS (子查询),子查询 都在右侧,3.2.4 集合运算,SELECT查询出来的结果是一个集合，两个结果集合可以进行集合运算。 查询的来源可以没有任何关系，只要求查询表达式的数目和对应数据类型一致。,集合运算有如下几种 并集运算：UNION 交集运算：INTERSECT 差集运算：MINUS（Oracle） EXCEPT（MSSql）,例如： SELECT city FROM locations UNION SELECT city FROM customers; UNION：合并完全相同的数据形成一个结果记录。 UNION ALL：保留重复的记录。,需要对集合运算结果进行排序时，可以在语句的最后面写ORDER BY。这时，一般用序号作为排序标识。 例如： SELECT aname, aid FROM agents UNION ALL SELECT cname, cid FROM customers ORDER BY 1;,3.2.5 TOP-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之间的记录。一般不考虑并列，只考虑记录数目。,专用方案 MySql中SELECT语句的选项LIMIT 一个参数N：返回查询的前N个结果。 两个参数N1和N2：返回结果集中从第N1条记录（从零开始计数）开始的N2个记录。,例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问题。,MSSql中SELECT语句的选项TOP n WITH TIES TOP n：返回查询的前N个结果。 TOP n WITH TIES：返回查询的前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;,例5：M4问题的MSSql解。 SELECT TOP 3 * FROM ranks WHERE id NOT IN ( SELECT TOP 2 id FROM ranks ORDER BY