08高级查询.ppt

1、8.1 使用聚合函数 一常用的聚合函数,8 高级查询,二聚合函数在SQL 中的使用 例：select avg(unitprice) from products CREATE DATABASE northwind ON (FILENAME = c:northwnd.mdf) FOR ATTACH 8.2 分组 一GROUP BY子句： GROUP BY ALL group_by_expression ,.n WITH CUBE | ROLLUP ,例： SELECT CategoryID , avg(UnitPrice) FROM Products group by CategoryID SEL

2、ECT CategoryID , SupplierID ,avg(UnitPrice) FROM Products group by CategoryID , SupplierID SELECT CategoryID ,avg(UnitPrice) FROM Products where CategoryId in (2,4) group by all CategoryID SELECT CategoryID ,sum(UnitsInStock) FROM Products group by CategoryID with cube SELECT CategoryID , SupplierID

3、 ,sum(UnitsInStock) FROM Products group by CategoryID , SupplierID with cube SELECT CategoryID , SupplierID ,sum(UnitsInStock) FROM Products group by CategoryID , SupplierID with Rollup,注： 指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中。 text、ntext 和 image 类型的列不能用于group_by_expression。 COMPUTE BY

4、子句使得以用同一 SELECT 语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。 例： SELECT productid , ProductName , CategoryID ,UnitPrice FROM Products compute sum(UnitPrice) SELECT productid , ProductName , CategoryID ,UnitPrice FROM Products order by CategoryID compute sum(UnitPrice) by CategoryID,二HAVING子句 指定分组子句的条件。

5、 HAVING 例： SELECT CategoryID , avg(UnitPrice) as avgprice FROM Products group by CategoryID having avg(UnitPrice) 30,8.3 多表查询 一笛卡儿乘积（交叉连接） 笛卡儿乘积的定义为“把所有表单的所有行完全合并”。 例： select * from student,school select * from student CROSS JOIN school,CREATE TABLE school ( id char(2) , schoolname char(10) CONSTRAI

6、NT school_PK PRIMARY KEY (id) ) go INSERT school VALUES(01 , 北京大学) INSERT school VALUES(02 , 清华大学) INSERT school VALUES(03 , 南京大学) INSERT school VALUES(04 , 东南大学) CREATE TABLE student ( code char(4) , name char(10) , schoolid char(2) CONSTRAINT student_PK PRIMARY KEY (code) ) go INSERT student VALUE

7、S(A001 , 张三, 02) INSERT student VALUES(A002 , 李四, 03) INSERT student VALUES(A003, 赵五, 02) INSERT student VALUES(A004 , 钱六, 02) INSERT student VALUES(A005 , 周七, 05),二多表查询 FROM子句：指定要查询的表或者视图。 FROM ,.n :=table_name AS table_alias | derived_table AS table_alias ( column_alias ,.n ) | INNER | LEFT | RIGH

8、T | FULL OUTER JOIN ON ,例1：别名。 SELECT s.ProductID, ProductName FROM products as S 例2：多表查询。（ANSI的SQL-86） SELECT ProductID, ProductName ,CategoryName FROM Products , Categories where Products.CategoryID = Categories.CategoryID 例3：连接。（ANSI的SQL-92） select * from student LEFT JOIN school on student.schoo

9、lid = school.id select * from student RIGHT JOIN school on student.schoolid = school.id select * from student FULL JOIN school on student.schoolid = school.id select * from student,school where student.schoolid = school.id,例4：自连接（指表与自身的连接） CREATE TABLE worker ( id char(4) , name varchar(8) , leadid

10、char(4) CONSTRAINT work_PK PRIMARY KEY (id) ) GO INSERT worker VALUES(A001 , 张经理, null) INSERT worker VALUES(A002 , 李组长, A001) INSERT worker VALUES(A003 , 王组长, A001) INSERT worker VALUES(B001 , 李组长一, A002) INSERT worker VALUES(B002 , 李组长二, A002) INSERT worker VALUES(B003 , 李组长三, A002) INSERT worker

11、VALUES(C001 , 王组长一, A003) INSERT worker VALUES(C002 , 王组长二, A003) INSERT worker VALUES(C003 , 王组长三, A003) INSERT worker VALUES(C004 , 王组长四, A003) SELECT A.id , A.name , B.name leadname FROM worker AS A left outer JOIN worker AS B ON A.leadid=B.id -包括张经理 SELECT A.id , A.name , B.name leadname FROM wo

12、rker AS A INNER JOIN worker AS B ON A.leadid=B.id -不包括张经理,三联合查询 将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中的所有查询的全部行。 UNION ALL 注： 1 ) UNION中的所有选择列表必须具有相同列数、相似数据类型和相同的顺序出现。 2 ) 列名来自第一个SELECT语句。 3 ) 若UNION中包含ORDER BY子句，则将对整个结果集排序。ORDER BY子句写在最后，必须是第一个SELECT列表中的列 4) 在合并结果时，将从结果集中删除重复行。若使用ALL，结果集中包含所有的行,例： CREATE

13、TABLE worker ( code char(4) , name char(10) , schoolid char(2) CONSTRAINT worker_PK PRIMARY KEY (code) ) GO INSERT worker VALUES(A003 , 赵五, 02) INSERT worker VALUES(A004 , 钱六, 02) INSERT worker VALUES(A005 , 周七, 05) SELECT name from student union select name from worker SELECT name from student unio

14、n all select name from worker,补充：用UNION ALL批量添加数据。 CREATE TABLE Dept( id int PRIMARY KEY, name nvarchar(20) INSERT INTO Dept SELECT 1, 财务部 UNION ALL SELECT 2, 行政部 UNION ALL SELECT 3, 业务部,四EXCEPT 和INTERSECT EXCEPT 从左查询中返回右查询没有找到的所有非重复值。 INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值。 例： SELECT name fr

15、om student EXCEPT select name from worker SELECT name from student INTERSECT select name from worker,8.4 嵌套子查询 一FROM数据源使用的子查询（衍生表查询） SELECT * FROM ( SELECT productid , productname FROM products ) as table1 SELECT * FROM ( SELECT productid , productname FROM products ) as table1(id , name) SELECT Cat

16、egoryID , a.数量 from ( SELECT CategoryID ,count(*) 数量 FROM Products group by CategoryID ) as A where a.数量8 等同： SELECT CategoryID ,count(*) 数量 FROM Products group by CategoryID having count(*) 8 SELECT a.CategoryID , Categories.CategoryName ,a.数量 from ( SELECT CategoryID ,count(*) 数量 FROM Products gro

17、up by CategoryID ) as A inner join Categories on a.CategoryID = Categories.CategoryID where a.数量8,二SELECT列表使用的子查询 SELECT ProductID 产品代号,ProductName 产品名称, (SELECT avg(UnitPrice) FROM Products ) - UnitPrice as 价格差异 FROM Products SELECT ProductID 产品代号,ProductName 产品名称, (SELECT avg(UnitPrice) FROM Produ

18、cts AS I WHERE I.CategoryID=P.CategoryID ) - UnitPrice as 价格差异 FROM Products P,三WHERE条件使用的子查询 SELECT ProductID ,ProductName , UnitPrice FROM Products where UnitPrice (SELECT avg(UnitPrice) FROM Products ) NOT IN： 例： SELECT * FROM Products WHERE SupplierID IN ( SELECT SupplierID FROM Suppliers WHERE

19、City = London ) SELECT * FROM Products P, Suppliers S WHERE P.SupplierID=S.SupplierID AND S.City = London,ANY ANY（1，2，3） 大于最小值就可满足条件 =ANY（1、2、3） IN（1，2，3） ANY（1、2、3） 1 OR 2 OR 3 SELECT ProductID ,ProductName ,CategoryID FROM Products WHERE CategoryID ANY (SELECT 5 UNION ALL SELECT 4 UNION ALL SELECT

20、 3 ) SELECT ProductID ,ProductName ,CategoryID FROM Products WHERE CategoryID = any (SELECT CategoryID FROM Categories WHERE CategoryName LIKE C%) ORDER BY CategoryID,ALL ALL（1，2，3） 大于最大值才可满足条件 = ALL（1、2、3） =1 AND =2 AND =3 ALL（1、2、3） 1 AND 2 AND 3 SELECT ProductID ,ProductName ,CategoryID FROM Products WHERE CategoryID ALL (SELECT 5 UNION ALL SELECT 4 UNION ALL SELECT 3 ) SELECT ProductID ,ProductName ,CategoryID FROM Products WHERE CategoryID ALL (SELECT CategoryID FROM Categories WHERE CategoryN