数据库Oracle第3章1.ppt

例4：找出在同一城市居住的顾客对。 写SQL 例4：找出在同一城市居住的顾客对。 SELECT c1.cid, c2.cid FROM customers c1, customers c2 WHERE c1.city = c2.city; 为了不重复列出， 另外自身配对不列出，可以 WHERE c1.city = c2.city AND c1.cid c2.cid; 例5：找出比Smith工资高的代理商。 SELECT a2.aname FROM agents a1, agents a2 WHERE a1.aname = ‘Smith’ AND a2.salary a1.salary; 例6：写出购买过商品的顾客姓名和产品名称。（多表连接） SELECT c.cname, p.pname FROM customers c, products p, orders o WHERE o.cid = c.cid AND o.pid = p.pid; 一般来说，N个表连接需要N-1个连接条件。 例7：写出购买过单价超过1元的产品的顾客名单。（多表连接） SELECT c.cname FROM orders o, customers c, products p WHERE o.cid = c.cid AND o.pid = p.pid AND p.price 1; 例8：找出与经理在同一城市的代理商。 写SQL 例8：找出与经理在同一城市的代理商。 SELECT a2.aname FROM agents a1, agents a2, locations l1, locations l2 WHERE a1.manager = a2.aid AND a1.lid = l1.lid AND a2.lid = l2.lid AND l1.city = l2.city; 3.2.2 聚集函数与分组统计 聚集函数：对于多条记录统计出一个结果的函数。 包括：COUNT，MAX，MIN，AVG，SUM 聚集函数和NULL的关系 COUNT(*)：计数符合条件的所有记录，不考虑是否为空。 COUNT(字段)：此字段为空的行不计数在内。 MAX、MIN：NULL不参与比较。 AVG、SUM：NULL不参与运算。 无GROUP BY子句，带聚组函数的查询一定只返回一行数据，但是可能是空值。如果表中无数据，或者无满足条件数据，或者所有符合条件数据都是空，COUNT返回0，其他函数则返回空值。 聚集函数里面可以使用DISTINCT，只对不重复的字段进行统计。也可以使用ALL，为缺省值。 SUM、AVG 只用于数值型。 MAN、MIN可用于数值、字符、日期。 SELECT COUNT (*)…返回记录数 SELECT COUNT (字段)…字段为空的记录不参与计数 SELECT COUNT (DISTINCT字段)…重复字段不参与计数 SELECT MAX(aname), AVG(salary) FROM agents 分组子句GROUP BY 按照GROUP BY条件对记录进行分组，然后进行统计。每一个分组得出一个结果。可以使用多个条件。 使用GROUP BY子句对查询字段（不包括条件字段）使用要求 在SELECT 子句中直接出现的字段（包括非聚组表达式内）必须是在GROUP BY中列出的字段，否则，必须放在聚组函数之内。 如使用了聚组函数，未使用GROUP BY 子句，则所有字段均不能直接出现在SELECT 子句中。 分组筛选子句HAVING 对分组后的每个组按照条件进行筛选； 只能使用聚集函数和分组字段（通常无意义）； 只能在GROUP BY后面出现。 例1：当前有多少订单。 SELECT COUNT(*) FROM orders; 例2：当前locations内有多少个邮编（可以重复）？ SELECT COUNT(postcode) FROM locations; 例3：在locations中有多少个不重复的城市？ SELECT COUNT(DISTINCT city) FROM locations; 例4：列出每一个顾客ID，总购买额。（GROUP BY） SELECT cid, SUM(dollars) FROM orders （不考虑顾客表中的姓名） GROUP BY cid; 例5：列出每一个顾客ID，姓名及总购买额。 SELECT o.cid, c.cname, SUM(o.dollars) FROM orders o, customers c WHERE o.cid = c.