微软认证考试70-461 Work with Data 数据处理 --27%比重--(1)

附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第二节Work with Data 数据处理 。

第一部分：Query data by using SELECT statements. May include but not limited to: use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce. 用SELECT语句查询数据。可能包含但不仅限于：在一个查询中使用排名函数(ranking function)进行前（top）X行的多类数据筛选；使用SQL 2005或2008或更高版本的SQL关键字创建并执行高效的查询，比如 synonym, join (except, intersect)等关键字；使用动态SQL和系统元数据进行逻辑实现；编写高效的技术复杂的SQL查询，包括各种表联接的使用；根据已提供的表判断给出的SQL语句是否能正确执行；根据提供的带约束的表判断SQL语句是否可以加载表；使用并理解不同的数据访问技术；对比CASE, ISNULL, COALESCE;

• 在一个查询中使用排名函数(ranking function)进行前（top）X行的多类数据筛选use the ranking function to select top(X) rows for multiple categories in a single query.。

四个ranking function: RANK，DENSE_RANK，NTILE，ROW_NUMBER：

创建测试表：

create table test(
id int identity(1,1) primary key,
testid int,
name varchar(100)
)

插入测试数据：

insert into test(testid,name)select 1,'乐可乐可的部落格'
insert into test(testid,name)select 1,'乐可乐可的部落格'
insert into test(testid,name)select 1,'乐可乐可的部落格'
insert into test(testid,name)select 2,'乐可乐可的部落格'
insert into test(testid,name)select 3,'微软考试认证'
insert into test(testid,name)select 4,'微软考试认证'
insert into test(testid,name)select 4,'微软考试认证'

用一个SQL语句来查看各个函数的使用：

select id,testid,
ROW_NUMBER() over( order by testid) as rownum,
RANK() over(order by testid) as ranknum,
DENSE_RANK() over(order by testid) as denseranknum,
Ntile(4) over ( order by testid) as ntilenum
from test
order by testid

下面是运行结果：

id    testid    rownum    ranknum    denseranknum    ntilenum
1    1            1                 1                   1                             1
2    1            2                 1                   1                             1
3    1            3                 1                   1                             2
4    2            4                 4                   2                             2
5    3            5                 5                   3                             3
6    4            6                 6                   4                             3
7    4            7                 6                   4                             4

ROW_NUMBER() over( order by testid) 按testid升序排列为每一个testid生成与之对应的一个序列数字，这些数字是从1开始由小到大的不间断数字。每个序列数字是唯一的。

RANK() over(order by testid) 按testid升序排列为每一个testid生成与之对应的一个排名数字，这些数字是从1开始由小到大排序（可能间断）。相同的testid生成的排名数字也相同，但是下一排名数字不是由之前的排名数字加1计算出的，而是排名总数即行数。

DENSE_RANK() over(order by testid) 按testid升序排列为每一个testid生成与之对应的一个排名数字，这些数字是从1开始由小到大排序的不间断数字（可能重复）。相同的testid生成的排名数字也相同，但是下一排名数字是由之前的排名数字加1计算出，而不是排名总数或行数。

Ntile(4) over ( order by testid) 按testid升序排列并将所有testid平均分成4组（最后一组testid总数可能少于其它组），然后为每一个testid生成与之对应的一个所属组编号。组编号是从1开始由小到大的不间断数字。

partition by

下面看一个带partition by的SQL语句来查看各个函数的使用：

select id,testid,name,
ROW_NUMBER() over(partition by name order by testid) as rownum,
RANK() over(partition by name order by testid) as ranknum,
DENSE_RANK() over(partition by name order by testid) as denseranknum,
Ntile(2) over (partition by name order by testid) as ntilenum
from test
order by name

运行结果：

id    testid    name                         rownum    ranknum    denseranknum    ntilenum
1    1            乐可乐可的部落格    1                 1                   1                             1
2    1            乐可乐可的部落格    2                 1                   1                             1
3    1            乐可乐可的部落格    3                 1                   1                             2
4    2            乐可乐可的部落格    4                 4                   2                             2
5    3            微软考试认证            1                 1                   1                             1
6    4            微软考试认证            2                 2                   2                             1
7    4            微软考试认证            3                 2                   2                             2

ROW_NUMBER() over(partition by name order by testid) 按name先进行数据分区，然后对每一区进行正常的ROW_NUMBER()计算。
RANK() over(partition by name order by testid) 按name先进行数据分区，然后对每一区进行正常的RANK()计算。
DENSE_RANK() over(partition by name order by testid) 按name先进行数据分区，然后对每一区进行正常的DENSE_RANK()计算。
Ntile(2) over (partition by name order by testid) 按name先进行数据分区，然后对每一区进行正常的Ntile()计算。

为了便于区分数据，在这里进行了NTILE(2)而不是NTILE(4)。

参考： http://msdn.microsoft.com/en-us/library/ms189798(v=sql.110).aspx

• 使用SQL 2005或2008或更高版本的SQL关键字创建并执行高效的查询，比如 synonym, join (except, intersect)等关键字。write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect).

SYNONYM同义词

为表test创建一个synonym同义词：create synonym testsyn for test

下面两条语句执行的结果相同。

select * from testsyn
select * from test

可以为本地对象（如表,视图)，远程对象remote object，函数function创建同义词synonym。

详细：http://msdn.microsoft.com/en-us/library/ms177544.aspx

JOIN

以两表为例

inner join内联接，运行结果只显示关联行。参考： http://msdn.microsoft.com/zh-cn/library/ms190014%28v=sql.105%29.aspx

left join左联接，运行结果显示左表所有行和右表的关联行，左表剩余的未能与右表匹配的行以NULL填充右表数据结构。

right join右联接，运行结果显示右表所有行和左表的关联行，右表剩余的未能与左表匹配的行以NULL填充左表数据结构。

full join完全联接，运行结果显示所有关联行及所有未关联行，所有未关联行以NULL填充左右表数据结构。

参考：http://msdn.microsoft.com/zh-cn/library/ms187518%28v=sql.105%29.aspx

cross join交叉联接，没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。如果添加了WHERE 子句，则交叉联接的行为将与内部联接行为相似。

参考：http://msdn.microsoft.com/zh-cn/library/ms190690%28v=sql.105%29.aspx

self join自联接，表可以通过自联接与自身联接。要创建将某个表中的记录与同一表中的其他记录相联接的结果集时，使用自联接。若要在同一查询中两次列出某个表，必须至少为该表名称的一个实例提供表别名。

参考：http://msdn.microsoft.com/zh-cn/library/ms177490%28v=sql.105%29.aspx

LOOP算法，用于小数据量查询。循环从左表取一记录和右表所有记录偿试匹配。

MERGE算法，用于中型规模数据量查询。在排好序的两表中单向从上往下偿试匹配两表记录。

HASH算法，用于大型规模数据量查询。将数据里较小的表进行HASH运算将记录存储到HASH列表中，然后从另外的表抽取记录做HASH运算并在HASH列表中偿试匹配。

参考：

http://msdn.microsoft.com/zh-cn/library/ms173815.aspx

http://msdn.microsoft.com/zh-cn/library/ms189313%28v=sql.105%29.aspx

http://msdn.microsoft.com/zh-cn/library/ms190967%28v=sql.105%29.aspx

http://msdn.microsoft.com/zh-cn/library/ms191318%28v=sql.105%29.aspx

EXCEPT 和 INTERSECT

比较两个查询的结果，返回非重复值。

EXCEPT 从左查询中返回右查询没有找到的所有非重复值。

INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值。

以下是将使用 EXCEPT 或 INTERSECT 的两个查询的结果集组合起来的基本规则：

• 所有查询中的列数和列的顺序必须相同。

• 数据类型必须兼容。

假设test表中testid包含1,1,2,3,4,4；test2表中id包含1,2,3,4,5,6

select testid from test
intersect
select id from test2

结果只有1,2,3,4。

select id from test2
except
select testid from test

结果只有5，6.

参考： http://msdn.microsoft.com/zh-cn/library/ms188055(v=sql.105).aspx

• case versus isnull versus coalesce对比CASE, ISNULL, COALESCE

ISNULL和COALESCE大体上相同，相当于CASE 语句：

CASE

   WHEN (expression1 IS NOT NULL) THEN expression1

   WHEN (expression2 IS NOT NULL) THEN expression2

   ...

   ELSE expressionN

END

但ISNULL和COALESCE还是不同的。

SELECT ISNULL(NULL,NULL)返回NULL。

SELECT COALESCE(NULL,NULL)则报错：至少有一个参数不能为空。

ISNULL只能有两个参数，如ISNULL(1,NULL)，而COALESCE则可以有多个参数，如COALESCE（NULL,3,NULL,1)。

ISNULL最终返回值类型跟第一个参数类型相同。如果两个参数的类型不同并最终返回值等于第二个参数，则会把第二个参数进行隐式转换为第一个参数的类型再返回。

如，

DECLARE @Field1 char(4), @Field2 char(50)
SET @Field2 = '乐可乐可的部落格'
 j
SELECT ISNULL(@Field1, @Field2)
--返回 '乐可'
SELECT COALESCE(@Field1, @Field2)
--返回 '乐可乐可的部落格'

参考：

http://msdn.microsoft.com/zh-cn/library/ms190349.aspx

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE

第二部分直通车

第三部分直通车

第四部分直通车

第五部分直通车