基于SQL SERVER的查询和优化解决方案

基于SQL  SERVER的查询和优化一、    逻辑查询处理逻辑查询处理的各个阶段 (5) SELECT (5-2) DISTINCT (5-3) TOP(top_specification)(5-1)(select_list) (1) FROM(1-J)<left_table><join_type> join<right _table> on <on_predicate>(1-A) < left_table><apply_type> apply<right_table_expression> AS<alias>(1-P) < left_table>PIVOT(<piovt_specification>) AS <alias>(1-U) < left_table>UNPIVOT(<unpiovt_specification>) AS <alias>(2) WHERE <where_predicate>(3)GROUP BY<group_by_specification>(4)HAVING(having_predicate>(6)ORDER BY<order_by_list>;示例： IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;IF OBJECT_ID('dbo.Customers') IS NOT NULL DROP TABLE dbo.Customers;GOCREATE TABLE dbo.Customers ( customerid  CHAR(5)     NOT NULL PRIMARY KEY, city        VARCHAR(10) NOT NULL);CREATE TABLE dbo.Orders ( orderid    INT     NOT NULL PRIMARY KEY, customerid CHAR(5)     NULL REFERENCES Customers(customerid));GOINSERT INTO dbo.Customers(customerid, city) VALUES('FISSA', 'Madrid');INSERT INTO dbo.Customers(customerid, city) VALUES('FRNDO', 'Madrid');INSERT INTO dbo.Customers(customerid, city) VALUES('KRLOS', 'Madrid');INSERT INTO dbo.Customers(customerid, city) VALUES('MRPHS', 'Zion');INSERT INTO dbo.Orders(orderid, customerid) VALUES(1, 'FRNDO');INSERT INTO dbo.Orders(orderid, customerid) VALUES(2, 'FRNDO');INSERT INTO dbo.Orders(orderid, customerid) VALUES(3, 'KRLOS');INSERT INTO dbo.Orders(orderid, customerid) VALUES(4, 'KRLOS');INSERT INTO dbo.Orders(orderid, customerid) VALUES(5, 'KRLOS');INSERT INTO dbo.Orders(orderid, customerid) VALUES(6, 'MRPHS');INSERT INTO dbo.Orders(orderid, customerid) VALUES(7, NULL);--SELECT * FROM dbo.Customers;--SELECT * FROM dbo.Orders;SELECT    C.customerid, COUNT(O.orderid) AS numorders FROM    dbo.Customers AS CLEFT OUTER JOIN   dbo.Orders AS OON  C.customerid = O.customerid WHERE  C.city = 'Madrid'GROUP BY  C.customerid HAVING  COUNT(O.orderid) < 3ORDER BY  numorders;这句话是什么意思？ ---查询来自Madrid的并且订单少于3个的客户 ?    步骤1:FROM阶段 ?    步骤1-J1:执行笛卡尔积 ?    步骤1-J2:应用ON筛选器(三值逻辑)?    步骤1-J2:添加外部行 ?    步骤2:where 阶段 ?    步骤3:group by 阶段 ?    步骤4:having 阶段 ?    步骤5:select 阶段 ?    步骤5-1 计算表达式 ?    步骤5-2 应用distinct 子句 ?    步骤5-3 应用 top选项 ?    步骤6:排序用的order by 阶段 三值逻辑:?    在SQL中逻辑表达式的取值为TRUE,FALSE,UNKNOWN.?    SQL中的UNKNOWN逻辑值通常出现在涉及NULL值的逻辑表达式中。 ?    UNKNOWN的反面(NOT KNOWN)是什么？ ?    所有的查询筛选器(on,where,having)都把UNKOWN当做FALSE来处理。CHECK约束中的UNKNOWN值实际上被当做TRUE对待.?    在筛选器中比较两个null 值将得到unkown,而unkown按false来处理，就好像其中这两个null不相等一样.?    另一方面,在unique约束,集合运算,以及排序和分组操作中，认为两个null是相等的。 ?    如果表中有一列定义了unique约束，将无法向表中插入该列值为null的两行.?    GROUP BY和ORDER BY 子句会将所有NULL值分到一组.?    当比较两个集合中的行时，集合运算符认为不同的NULL 值是相等的.?    (1)表运算符 Apply1.A1:把右表表达式应用于左表的行.2.A2:添加外部行.Pivot1. P1  分组 2. P2  扩展 3. P3  聚合 Unpivot 1 U1 生成副本 2 U2 提取元素 3 U3 删除带有NULL的行 OVER子句(顺便讲讲排名函数)?    (2)集合运算符 UNION ALLUNIONEXCEPTINTERSERT