SQL执行的过程笔记

 

 

USE MyDB

GO

IF OBJECT_ID('dbo.orders') IS NOT NULL

DROP TABLE dbo.orders

go

IF object_id('dbo.customers') IS NOT NULL

DROP TABLE dbo.customers

go

CREATE TABLE dbo.customers

(

  customerid CHAR(5) NOT NULL PRIMARY KEY,

  city VARCHAR(10) NOT null

);

INSERT INTO dbo.customers VALUES('FISSA','Madrid');

INSERT INTO dbo.customers VALUES('FRNDO','Madrid');

INSERT INTO dbo.customers VALUES('KRLOS','Madrid');

INSERT INTO dbo.customers VALUES('MRPHS','Zion');

CREATE TABLE dbo.orders

(

  orderid INT NOT NULL PRIMARY KEY,

  customerid CHAR(5) NULL REFERENCES customers(customerid)

);

 

INSERT INTO dbo.orders VALUES(1,'FRNDO');

INSERT INTO dbo.orders VALUES(2,'FRNDO');

INSERT INTO dbo.orders VALUES(3,'KRLOS');

INSERT INTO dbo.orders VALUES(4,'KRLOS');

INSERT INTO dbo.orders VALUES(5,'KRLOS');

INSERT INTO dbo.orders VALUES(6,'MRPHS');

INSERT INTO dbo.orders VALUES(7,NULL);

 

 

 

/*查询城市为Madrid，订单数小于的客户和订单数*/

SELECT a.customerid,COUNT(b.orderid) AS orderNumber FROM customers a

LEFT JOIN orders b

ON a.customerid=b.customerid

WHERE a.city='Madrid'

GROUP BY a.customerid

HAVING COUNT(b.orderid)<3

ORDER BY orderNumber

 

--SQL执行过程：

--1.对from子句的前两个表执行笛卡尔乘积(交叉联接或未限定的联接),生成虚拟表VT1

SELECT * FROM customers AS c cross JOIN orders AS o --生成4*7=28行数据

 

--2.应用on筛选器(联接条件),on筛选器是可以用于查询的三个筛选器on、WHERE、HAVING中的第一个

--只有联接条件为TRUE的那些行才会包含在由步骤返回的虚拟表VT2中

--注：三值逻辑：true false UNKNOWN,查询筛选器把unknown当作false处理,而check约束中的

--unknown值被当作true对待:例salary列的值必须大于，向该表插入salary为NULL的行时可以被

--接受，因为null>0等于unknown,因此unknown在check约束中被视为和true一样

--而unique约束、排序操作和分组操作认为两个NULL值是相等的

SELECT * FROM customers AS c inner JOIN orders AS o

ON (c.customerid=o.customerid)

--3.添加外部行outer row

--与外部联接有关，指定一种外部联接(left right full)可以把一个或两个输入表标记为保留表

--保留表中的行被称为外部行,外部行中非保留表的属性（attribute,列值）被赋值为null

--生成虚拟表VT3(如果有多个表，则重复1至3)

SELECT * FROM customers AS c LEFT JOIN  orders o ON(c.customerid=o.customerid)

--4.应用where筛选器

--对VT3应用where筛选器生成虚拟表VT4

SELECT * FROM CUSTOMERS C LEFT JOIN  ORDERS O

ON C.CUSTOMERID=O.CUSTOMERID

WHERE C.city='Madrid'

 

--5.分组group by 生成VT5,VT5由两部分组成,实际组构成的成组部分(group section)和由上一步

返回的基行构成的原始部分(RAW section)

SELECT c.customerid FROM CUSTOMERS C LEFT JOIN  ORDERS O

ON C.CUSTOMERID=O.CUSTOMERID

WHERE C.city='Madrid'

GROUP BY c.customerid

--6.应用CUBE或ROLLUP选项

如果指定了CUBE或ROLLUP，将创建超组并把它添加到上一步返回的虚拟表中，生成虚拟表VT6

--7.应用HAVING筛选器

HAVING是第一个也是唯一一个应用到已分组数据的筛选器。生成虚拟表VT7

 

SELECT c.customerid FROM dbo.customers c

LEFT JOIN orders o ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

HAVING COUNT(o.orderid)<3

--8.处理SELECT 列表，生成虚拟表VT8, 处理SELECT 列表这一步用于构建终要被返回给调用方的表。SELECT列表中的表达式可以是由上一步返回的虚拟表的基列，也可以是对这些基列的操作。应该为不是基列的表达式应用别名，使其在结果表中拥有一个列名称。

注：在SELECT 列表中创建的别名不能在前面的步骤中使用。实际上，表达式别名甚至不能用于SELECT列表中的其他表达式。该限制是由于SQL的一个独有的特性“即同时操作（all-at-once operation）”,例：UPDATE dbo.商品库存 SET 单价=单价+(SELECT MAX(单价) FROM dbo.商品库存)

SELECT c.customerid,COUNT(o.orderid)AS numbers FROM dbo.customers c

LEFT JOIN orders o ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

HAVING COUNT(o.orderid)<3

 

--9.应用DISTINCT子句

如果在查询中指定了DISTINCT子句，将从上一眯返回的虚拟表中移除重复行，并生成虚拟表VT9

如果使用了GROUP BY ，再使用DICTINCT是多余的，它不会移除任何行。

SELECT DISTINCT(在此使用DISTINCT无意义) c.customerid,COUNT(o.orderid)AS numbers FROM dbo.customers c

LEFT JOIN orders o ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

HAVING COUNT(o.orderid)<3

-10.应用ORDER BY子句,此步不返回有效的表，而是返回一个游标。SQL是基于集合理论的。集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序无关紧要。对表进行排序的查询可以返回一个对象，包含按特定物理顺序组织的行。ANSI把这种对象称为游标。

按ORDER BY 子句中的列列表排序VT9返回的行。返回游标VC10。你可以在ORDER BY子句中指定SELECT列表中结果列的序号，但不推荐这种方法，因为你可能改变SELECT列表而忘了修改ORDER BY 列表。而且，当查询字符串很长时，要找出ORDER BY列表中的序号所对应的SELEC列表中的列是非常困难的。

SELECT DISTINCT(在此使用DISTINCT无意义) c.customerid,COUNT(o.orderid)AS numbers FROM dbo.customers c

LEFT JOIN orders o ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

HAVING COUNT(o.orderid)<3

ORDER BY numbers

注：使用了ORDER BY 子句的查询不能用作表表达式，表表达式包括：视图、内联表值函数、子查旬、派生表和共用表表达式（CTE）。它的结果必须返回给期望得到物理记录集的客户端应用程序。

如，下面的派生表查询无效，并产生一个错误：

SELECT * FROM (select orderid,customerid FROM orders ORDER BY orderid) AS d

消息1033，级别15，状态1，第2 行

除非另外还指定了TOP 或FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

同样，下面的视图也无效：

Create view vsortedorders

As

SELECT orderid,customerid from orders  order by orderid

Go

可以使用此种方式  SELECT TOP (100)  percent  orderid,customerid  FROM orders  ORDER BY customerid desc

另：排序是需要成本的，SQL SERVER需要执行有序索引扫描（ordered index scan）或使用排序运算符

SELECT orderid,customerid FROM orders ORDER BY 2,1

 

--11.应用TOP选项(T-SQL特有的，不属于关系范畴)

TOP选项允许你指定要返回的行数或百分比（取整）。生成表VT11

这一步根据行的物理顺序确定哪些行属于被优先请求的。如果在查询中指定了ORDER BY子句及唯一的ORDER BY列表，结果将是确定的。

SELECT TOP 10 c.customerid,COUNT(o.orderid) AS numbers FROM dbo.customers c

LEFT JOIN orders o ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

HAVING COUNT(o.orderid)<3

ORDER BY numbers

常用分页查询

SELECT TOP 5* FROM orders WHERE orderid NOT  IN(SELECT TOP 50 orderid FROM orders order by orderid ) order by orderid