.Net培训个人总结笔记29

学习交流，欢迎转载。转载请注明文章来源：http://www.cnblogs.com/lgjspace/archive/2011/10/13/2218272.html

 

细节：
在 SQL 语句中，“--”为单行注释，相当于 C# 代码中的“//”，而“/*被注释的内容*/”则为多行注释。

 

细节：！！！
SQL 语句中 Where、Order By、Group By、Having 关键字的使用顺序：
1. Order By 语句和 Group By 语句都必须位于 Where 语句之后；
2. Having 语句必须位于 Group By 语句之后；
   （注意：Where 语句中不能有聚合函数，如果有聚合函数，可以用 Having。）

 

重点：
1. Join：连接，即两个表通过某一个（或多个）列进行关联时，要把两个表中的列放到一个结果集中。
2. 连接（Join）有分为：内连接（Inner Join），左外连接（Left Outer Join），右外连接（Right Outer Join）。

 

教训：
1. 在 SQL 语句中，假设有三个字符串值 FirstName、MiddleName 和 LastName，如果一个字符串（暂假设是 FirstName）值为 null ，则“FirstName + MiddleName + LastName”的值也是为 null，因为在 SQL 语句中，null 不是表示“没有”，而是表示“不知道”，“不知道”的值无论加上什么值也只能是“不知道”。
2. 如果需要判断一个值是否为空，极不推荐用“Value <> null”或“Value = null”，而推荐用“Value is not null”或“Value is null”，也可以用函数 ISNULL(变量值, 该变量值为空时要返回的值) 来实现。如：ISNULL(C.FirstName,'')，如果字符串变量 C.FirstName 不为空，执行该语句之后会返回 C.FirstName 的实际值，否则返回 “''”，即空字符串。

 

细节：
1. 为列名或表名添加别名时用的关键字 as 是可以省略不写的，没写 as 和写上 as 效果上没有什么区别。同样地，Inner Join 中的 Inner 也一样，也可以省略不写，效果没有区别。
2. 同时“Join”两个以上的表的方法。如下面代码所示：（留意关键字 From 之后的几个 Join 语句）
Select E.Title as 职位名称,
isnull(C.FirstName,'') + isnull(C.MiddleName,'') + isnull(C.LastName,'') as 姓名,
E.HireDate as 雇用日期,C.EmailAddress as Email地址
From HumanResources.Employee as E
 Inner Join Person.Contact as C
  On E.ContactID = C.ContactID
 Inner Join Person.CountryRegion as R
  On R.ModifiedDate = E.ModifiedDate

 

经验：
1. 数据表的连接（Join）时机：理论上是在数据库设计时就要连接好的，因为这一般都是基于业务的；
2. 数据表的连接（Join）条件：当两个（或多个）表的业务意义相等（如上面代码中的表 Person.Contact 和表 HumanResources.Employee 之间的关系）时才需要连接，否则连接就没有意义。

 

细节：
使用 Inner Join 时需要注意：
假如有两个表，如下：
A:
  ProductID ProductNums
1     2  22
2     4  41
3     1  38
4     3  12
B:
  ProductID    Name
1     2         abc
2     4         ccc
3     1        Null
4     3         bac
如果执行以下 SQL 语句：
Select A.ProductID, A.ProductNames, B.Name
From A Join B On A.ProductID = B.ProductID
执行该语句后，由于表 B 中 ID 为 1 的 Name 值为 Null，用 Join 连接后会使该 ID 的整个项都被抛弃掉，效果如下：
After A Join B:
  ProductID ProductNums Name
1     2  22  abc
2     4  41  ccc
    //被抛弃掉的空行
4     3  12  bac
由于被连接的表 B 中的 ID 为 1 的项的 Name 值为 Null，Join 后导致整个项都被抛弃掉，即表中没有了 ID 为 1 的项了。

细节：！！！
Left Outer Join ：“Left Join”左边的表的值（只要是有值的，就算值是为 Null 也不例外）都会被全部保留，右边的表如果没有与之（左边的表的值）对应的值则用 Null 来填充。
Right Outer Join：正好和“Left Join”的方向刚好相反，“Right Join”右边的表的值（只要是有值的，就算值是为 Null 也不例外）都会被全部保留，左边的表如果没有与之（右边的表的值）对应的值则用 Null 来填充。

 

关于 Inner Join、Left Outer Join 和 Right Outer Join 的总结：
inner join       ：仅包括两个表的匹配连接行；
Left outer join  ：除匹配连接行外还包含左边表的所有行；（左边的表的行只要用来连接的列是有值的都被保留，就算值为 null，右边的表如果没有和左边对应行匹配的行则用 Null 来填充）
Right outer join ：除匹配连接行外还包含右边表的所有行；（右边的表的行只要用来连接的列是有值的都被保留，就算值为 null，左边的表如果没有和右边对应行匹配的行则用 Null 来填充）
如下面的案例所示：
数据库 T1 中有 T_Name 和 T_Age 两个表：

T_Name:
IDName  Name
 1   Tom
  //表 T_Name 中没有 ID 为 2 的项
 NULL   Jim //含有 id 为 NULL 的项
 4   Lucy
 5   Mike

T_Age:
IDAge  Age
 1   20
 2   30
 3   40
  //表 T_Age 中没有 ID 为 4 的项
 NULL   60 //含有 id 为 NULL 的项

执行下面的 SQL 语句：
Select *
From T_Name as N
    Left Join T_Age as A
        On N.IDName = A.IDAge

执行该 SQL 语句后的结果为：
  IDName    Name    IDAge    Age
1    1      Tom       1       20
     //由于是 Left Join，而且 T_Name 表中没有 IDName 为 2 的项，因此就算 T_Age 表中有 IDAge 为 2 的行，该行也被整行抛弃掉。
2  NULL     Jim      NULL    NULL //由于是 Left Join，而且 T_Name 表中 Name 值为 Jim 的行的 IDName 为 NULL ，所以在表 T_Age 中没有与之对应的行（即 IDAge = IDName 的行，注意 NULL 是不等于 NULL 的），因此 Join 过之后的表中 IDAge 和 Age 列的值都为 NULL。
3    4      Lucy     NULL    NULL //“Left Join”后，左边的表保留 ID 为 4 的项，右边的表由于没有该项，因此用 Null 来填充该对应项的所有值。
4    5      Mike     NULL    NULL

执行下面的 SQL 语句：
Select *
From T_Name as N
    Right Join T_Age as A
        On N.IDName = A.IDAge

执行该 SQL 语句后的结果为：
  IDName    Name    IDAge    Age
1    1      Tom       1       20
2  NULL     NULL      2       30 //“Right Join”后，右边的表保留 ID 为 2 的项，左边的表由于没有该项，因此用 Null 来填充该对应项的所有值。
3  NULL     NULL      3       40
     //由于是 Right Join，而且 T_Age 表中没有 IDAge 为 4 的项，因此就算 T_Name 表中有 IDName 为 4 的行，该行也被整行抛弃掉。
4  NULL     NULL     NULL     60 //由于是 Right Join，而且 T_Age 表中 Age 值为 60 的行的 IDAge 为 NULL ，所以在表 T_Name 中没有与之对应的行（即 IDAge = IDName 的行，注意 NULL 是不等于 NULL 的），因此 Join 过之后的表中 IDName 和 Name 列的值都为 NULL。

 

细节：
Left Join、Right Join 细节：
通过改变 Left Join（Right Join）关键字前、后的表的放置顺序、改变 On 关键字后面的连接键的前后放置顺序来观察各种写法对执行后得出的表的结构的影响区别：
有下面两个自定义的临时表 #T1、#T2：
WITH t1
AS
(
 Select 1 as ID1, 'a' as value1
  UNION ALL
 Select NULL, 'b' -- id 为 2 的项的 id 为 null
 -- UNION ALL
 --Select 3, 'c'  --没有 id 为 3 的项
  UNION ALL
 Select 4, 'd'
  UNION ALL
 Select 5, 'e'
  UNION ALL
 Select 6, 'f'
)
Select * into #T1 From t1; --这里要加上分号，否则会报错。
WITH t1 --由于前面的临时创建的表 t1（不是临时表，只是一个临时的表）在上面的 Select 语句执行之后就会被删除掉，所以在这里继续创建一个也叫 t1 的表不算是重名，所以是合法的。
AS
(
 Select 1 as ID2, 'a' as value2
  UNION ALL
 Select 2, 'b'
  UNION ALL
 Select 3, 'c'
  UNION ALL
 Select NULL, 'd' -- id 为 4 的项的 id 为 null
 -- UNION ALL
 --Select 5, 'e'  --没有 id 为 5 的项
  UNION ALL
 Select 6, 'f'
)
Select * into #T2 From t1
执行以下的 SQL 语句：
--1
Select * From #T1
 left join #T2
  On #T1.ID1 = #T2.ID2; --原始的表结构。
--2
Select * From #T1
 left join #T2
  On #T2.ID2 = #T1.ID1; -- On 语句的连接键的顺序和上面的 Select 语句中的 On 语句连接键顺序调转了，但不影响执行结果的表的结构。
--3  
Select * From #T2
 left join #T1
  On #T2.ID2 = #T1.ID1; -- 这里把 Left Join 语句前后的表的顺序调转了，但 On 语句的连接键顺序没变，执行结果的表的结构改变了。
--4
Select * From #T1
 right join #T2
  On #T1.ID1 = #T2.ID2; --原始的表结构。
--5
Select * From #T1
 right join #T2
  On #T2.ID2 = #T1.ID1; -- On 语句的连接键的顺序和上面的 Select 语句中的 On 语句连接键顺序调转了，但不影响执行结果的表的结构。
--6
Select * From #T2
 right join #T1
  On #T2.ID2 = #T1.ID1; -- 这里把 Right Join 语句前后的表的顺序调转了，但 On 语句的连接键顺序没变，执行结果的表的结构改变了。
--删除两个临时表：
Drop Table #T1
Drop Table #T2
执行完上面的 SQL 语句后的结果：
--1
ID1 value1 ID2 value2
1 a 1 a
NULL b NULL NULL
4 d NULL NULL
5 e NULL NULL
6 f 6 f
--2
ID1 value1 ID2 value2
1 a 1 a
NULL b NULL NULL
4 d NULL NULL
5 e NULL NULL
6 f 6 f
--3
ID2 value2 ID1 value1
1 a 1 a
2 b NULL NULL
3 c NULL NULL
NULL d NULL NULL
6 f 6 f
--4
ID1 value1 ID2 value2
1 a 1 a
NULL NULL 2 b
NULL NULL 3 c
NULL NULL NULL d
6 f 6 f
--5
ID1 value1 ID2 value2
1 a 1 a
NULL NULL 2 b
NULL NULL 3 c
NULL NULL NULL d
6 f 6 f
--6
ID2 value2 ID1 value1
1 a 1 a
NULL NULL NULL b
NULL NULL 4 d
NULL NULL 5 e
6 f 6 f

 

总结：
对比以上这些结果可以得出：
1. On 关键字后的连接键的前后顺序对查询结果的表的结构没有任何影响；
2. Right Join 和 Left Join 所依据的表的“左右”位置只取决于“Join”关键字之前和之后的表的放置顺序，在 Join 前面的即为“左表”，后面的即为“右表”；
3. 查询结果的表的“左、右”显示结构也是只取决于“Join”关键字之前和之后的表的放置顺序，在 Join 前面的即为“左表”，后面的即为“右表”，无论是被 Left Join 还是被 Right Join，左表总是放在结果表的左边，右表总是放在结果表的右边，Left Join 和 Right Join 只是决定了结果表保留值时是应该以左边的值为标准还是以右表的值为标准而已。

 

细节：
COUNT() 和 SUM() 的区别：
COUNT() 是计算符合条件的所有数据的总条数；
SUM() 是计算符合条件的所有数据的值的总和，但要求作为参数的列的值的类型必须是数字类型的。

 

细节：
Group by 语句的细节：
假设创建有下面这个表 Test：
id     name
 1 a
 2 a
 2 a
 2 b
 3 b
如果执行下面这条 SQL 语句：
Select id, name From Test
Group by id, name
则结果如下表所示：
id     name
 1 a
  //这里由于被 Group by，和下面一行重复的数据被 Group by “合并”在一起了。
 2 a
 2 b
 3 b
从这个过程可以看出，Group by 的分组依据是 Group by 关键字后面的全部字段的值是否完全相同，如果全部字段值完全相同，才把完全相同的这两行数据当成是一个组，即把这两行数据“合并”成一个组，而如果两行数据只要有其中一个（标记在 Group by 关键字后面的）字段的值不相同，都会被当成不是同一个组的数据。

 

细节：
SQL 语句的执行和书写顺序：
1. SQL 的执行顺序：from... join... on... where... group by... having... select... order by... top...
2. SQL 的书写顺序：select... top... from... join... on... where... group by... having... order by...
3. Order By 其实很耗性能。

 

 SQL 语句的执行顺序的描述：
1.FROM：      对 FROM 子句中的前两个表（ Product 和 SalesOrderDetail ）执行交叉联接，生成虚拟表VT1。
2.ON：        对 VT1 应用 ON 筛选器。只有那些使 p.ProductID = d.ProductID 为真的行才被插入 VT2。
3.WHERE：     对 VT2 应用 WHERE 筛选器。只有使 h.OrderDate='2003-1-1' 为 true 的行才被插入 VT3。
4.GROUP BY：  按 GROUP BY 子句中的列列表对 VT3 中的行分组，生成 VT4。
5.HAVING：    对 VT4 应用 HAVING 筛选器。只有使 sum(d.OrderQty)>80 为 true 的组才会被插入 VT5。
6.SELECT：    处理 SELECT 列表，产生 VT6。
7.ORDER BY：  将 VT6 中的行按 ORDER BY 子句中的列列表排序，生成游标（VC7)。
8.TOP：       从 VC7 的开始处选择指定数量或比例的行，生成表 VT8，并返回调用者。

 

细节：
Union 比 Union All 要更耗性能，一般情况下都建议用 Union All，因为 Union All 只是直接把两个表简单联合，除此之外没有任何额外工作；而 Union 则是除了简单的联合之外，还会对结果集进行排序和去掉重复的项，有重复的项时只会保留一项。

 

细节：
Except 一般在“查找历史记录”时比较常用，但总的来说用处不是很大，而且可以用子查询来代替。

 

区别：
Select...Into...From... 和 Insert Into...Select...From...
1. Select...Into...From... 是把“Select”到的数据“Into”到另一个将要被创建的新表中；
2. Insert Into...Select...From... 是把“Select...From...”到的数据“Insert Into...”到另一个已经存在的表中
3. Select Into 是创建一个新的表并填充数据；而 Insert Into 是对现有的表进行插入操作。
4. 使用格式分别是：“Select 要添加到新表的在旧表中的列名1, 列名2... Into 要被创建的新表表名 From 现有的旧表表名”；“Insert Into 存放数据的目标表的表名（必须是已经存在的表） Select 要添加到新表的在旧表中的列名1, 列名2... From 现有的源数据表表名”

 

用法：
1. 删除一个现有的非表变量的表（如临时表和永久表等）可以用“Drop Table 表名”的方式来把整个表从数据库中删除（而不是仅仅把表里的数据删除）。
2. “#+表名”为临时表，例如“#Persons”等；“##+表名”为全局临时表，例如“##Product”等；

 

区别：
1. 临时表与永久表在使用时没有区别；
2. 临时表使用“Create Table #表名”语句来创建，而表变量使用“Declare @表名 Table(列的类型声明)”语句来创建。
3. 临时表存储在名为 tmppdb 的数据库中，在连接结束或者使用 drop 语句时才会消失；而表变量存储在内存中，在执行完毕后（或遇到 GO 语句时）自动删除。（注：Go 语句只）
4. 由于表变量是存储在内存中，因此速度会比临时表要快，但会更耗内存资源。
5. 一般使用时的临时存储用临时表就可以。

 

区别：
1. 作用域：表变量只能用于一次执行过程；临时表可以在连接结束前一直有效。
2. 定义和使用：临时表可以不用定义结构，直接 select into 即可；而表变量必须先定义结构，通常与 insert into 结合使用。
3. 性能上，因为表变量使用内存，所以速度稍快。
4. 在实际应用中，根据系统状态决定使用临时表还是表变量：系统内存较充裕时，使用表变量提供效率；数据量大内存不足够充裕时，使用临时表。
5. 总之，临时表更像永久表，表变量更像变量。

 

经验：
由于存储过程自身的“执行速度快”的特点，建议在大批量的比较简单的重复性数据处理时使用存储过程，在业务逻辑特别复杂的时候不推荐使用存储过程。

 

作用：
SET ANSI_NULLS ON/OFF 的作用：在遇到 NULL 值时，SQL 是不是按照 ISO 标准来使用 = 和 <> 。
不用太纠结这个开关，一般不推荐用“= NULL”和“<> NULL”（微软预计以后不会支持这种方式），而推荐用“IS NULL”和“IS NOT NULL”。

 

细节：
在 SQL Server 中，如果表名、列名、别名等名称和 SQL 语句关键字一样时，需要用中括号 [] 来把该名称括起来以作区别。
这一点在 SQL Server 的存储过程中有一个类似的开关：“QUOTED_IDENTIFIER ON/OFF”，开关为 ON 时可以用双引号 "" 代替中括号 []，开关为 OFF 时只能用中括号 []。

 

细节：
在 SQL Server 的存储过程中，SET NOCOUNT ON/OFF 用来设定是否返回受影响的行数，如果为 ON，则返回受影响的行数，为 OFF 则不返回受影响的行数。这设置会对查询性能产生影响，一般都设为 OFF。

 

语法：
关键字 With 的用法：
With 表名
AS
(
    Select * From ..........
)
这种方式相当于临时创建了一个表（不同于临时表，相当于临时的表），“With”出来的表（假设为tmp）只在紧跟 AS() 后面的第一行内有效，一旦出了该行就不能用了。
这种语法创建的表比临时表更临时一些。

 

重点经验！！！：
如下面的案例所示：
/*实例：“找人（先用普通 SQL 方式实现）”*/
/*
查找Mary Baker同部门的last name相同的人
输入参数：Mary Baker
输出：部门名称，first name，last name
目标表：
HumanResources.Employee
HumanResources. EmployeeDepartmentHistory
HumanResources.Department
Person.Contact
*/
--前半部分
With Emp
AS
(
 Select D.Name as 部门名称, C.FirstName, C.MiddleName , C.LastName
 From Person.Contact as C
  Join HumanResources.Employee as E
   On C.ContactID = E.ContactID
  Join HumanResources.EmployeeDepartmentHistory as H
   On E.EmployeeID = H.EmployeeID
  Join HumanResources.Department as D
   On H.DepartmentID = D.DepartmentID
)
--后半部分
Select *
From Emp E1
 Join Emp E2
 On E1.部门名称 = E2.部门名称
Order by E1.部门名称

 

技巧点！！！
在上面的代码中的后半部分，通过利用自身别名为 “部门名称” 的列来进行 “自连接”，然后对其中一边的表（E1）进行按 “部门名称” 来分组，即可从另一边的表（E2）看出与表 E1 的某个 “名称” 的 “雇员” 同部门的名单。

 

细节：
创建存储过程时建议填上下面的信息栏

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

“<Author,,Name>”位置填写作者名称；
“<Create Date,,>”位置填写编写该存储过程的日期；
“<Description,,>”位置填写对该存储过程的相关描述；

如下面的示例所示：
-- =============================================
-- Author: 邱爽
-- Create date: 2011-01-24
-- Description: 测试
-- =============================================

 

细节：
1. 一般创建了一个新的存储过程后存储过程的文件夹列表下是没有这个新创建的存储过程的，直到存储过程被执行过一次之后，存储过程的文件夹中才会有这个存储过程的记录（要刷新一下）。
2. 同一个存储过程一旦被创建，就不能被再次重复创建，因此需要把存储过程设定为“先删除后创建”的模式，具体操作实现流程为：选中该存储过程的文件→右键打开菜单→选择“编写存储过程脚本为”→“DROP 和 CREATE 到(R)”→“新查询编辑器窗口”，这样就会得到一个新的被修改过的存储过程，这个新的被修改过的存储过程（暂名为 存储过程 2 ）功能上和前面新创建而且没被修改过的存储过程（暂名为 存储过程 1 ）是一样的，只是存储过程 2 是在存储过程 1 的原文基础上加上一个小功能，就是“在存储过程被创建之前，如果有重名的旧存储过程，则先删掉重名的存储过程”。

 

细节：
存储过程的名称一般以“usp_”或“sp_”开头，但具体用什么开头要看个人或者公司的代码习惯。

 

细节：
存储过程也有“输出参数”，和 C# 中的输出参数的原理一样，用法也十分类似，也要加上关键字“out”，如下面的例子所示：
1. 存储过程中的代码：
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_GetName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  梁国锦
-- Create date: 2011-07-14
-- Description: use output parameters
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetName]
 @inputName varchar(100),
 @OutputName varchar(100) out  --输出参数，声明和使用时都要加上关键字 out 来标识。
AS
BEGIN
 set @OutputName = @InputName + ' this is a test'
END
GO

2. SQL 语句调用存储过程的代码：
declare @o varchar(100)
set @o = ''
exec usp_GetName 'liangguojin',@o out --输出参数，声明和使用时都要加上关键字 out 来标识。
select @o

 

细节：
当用“Select *”来“Join”多个表时回报类似于“列名重复”的错误，这是因为把两个表“Join”到一起之后产生了重名的列。
若要解决该问题，最好养成“尽量不要用“*”，尽可能地显式指定所要查询的列”的习惯。
（补充：“Select *”中的“*”需要“全表扫描”每个列，数据多的话会很耗性能。）

 

细节：
With 的语法是 VS2005 后才有的语法，可以看作 With 后面是一个自定义行列格式和数据内容的表名（表名后面还可以定义表的结构），该表是存放在内存中的，一旦出了后面的 AS() 的括号就不能用了。

 

细节经验：
Group by 语句中的列不能使用 Select 语句中为列自定义的别名，因为 SQL 语言的执行顺序是先执行 Group by，再执行 Select 的，如果在 Group by 中使用了 Select 中的别名，就相当于还没有为相应的列定义过任何别名就使用了一个别名，这样编译器是不认的，编译通不过。

 

细节：
1. SQL Server 中，所有的关键字一般都要用大写。
2. GO 就是命令程序执行 GO 关键字上面的 SQL 语句。
3. 在存储过程中， SET 关键字是为变量赋值。
4. 在存储过程中的“BEGIN”和“END”就相当于 C# 中的一对大括号。
5. 在存储过程中也有 IF、ELSE、RETURN 等关键字，用法如下：
IF YEAR(@BeginDate) < 2003
BEGIN
    RETURN;
END
ELSE
BEGIN
...
END

 

细节：
在 SQL Server 中：
1. 用关键字 exec 执行存储过程。
2. 可以用“DECLARE @变量名”来创建一个变量，也可以写成“DECLARE @变量名1  变量类型, @变量名2  变量类型, @变量名3  变量类型,...”这样来定义多个变量，如“DECLARE @a int, @Name  nvarchar(20), @Gender bit”等。
3. 用关键字 SET 来为变量赋值，如“SET @a = 3”、“SET @Birthday = '1995-02-14'”等。
4. IF、RETURN 语句的使用和 C# 中的一样。
5. BEGIN END 语句等同于 C# 中的大括号。