SQL-Server使用点滴(一-数据对象篇)

前言

　　SQL的语法比较简单，学起来相比界面ＵＩ控制要简单得多，但是ＳＱＬ在企业级应用中又是如此的重要，以至于很多开发人员都把重点放在ＳＱＬ上。

　　SQL并没有面向对象的概念，最复杂的设计也不过是表值函数，以及基于ＯＲＭ思维的表类型映射处理了，还包括一些通用的程序数据结构处理。当然，ＳＱＬ真正的精华在我看来，是如何优化其执行效率。各类数据库范式的应用以及索引的构建让数据效率足够强大，同时又结合了数据库结构设计与现实工程中的联系。一般程序语言总会在程序可读性和代码效率性中间遇到矛盾，而ＳＱＬ直观的代码表达和复杂的关系结构运算，让她变成效率处理中的王者。我们几乎不用考虑太多的代码可读性问题，只需要关注规范及效率即可。

　　说这么，反回说来还是落在现在的这片文档上，本文着力收录一些基础通用的ＳＱＬ语法点，并且包含一些巧妙的处理内容。举例力求少和精简，使之即可以作为一个文档手册，又可以作为SQL知识扩展使用。

一，基础的增删改查：

　　SQL中大概可以分为三种操作的对象类型：数据库，数据表，记录。这三种对象都有其对应的操作语句。

　　１，【数据库】操作

      1>, 创建数据库

            if exists(Select * from sys.databases where name='数据库名')                  --先判断要建立的数据库是否存在

            Drop Database 数据库名                                                                        --删除数据库

            Go

            Create Database 数据库名

      2>，删除数据库：参照1>

      3>，备份数据库

             --先创建备份数据的设备(Device)

              use master          --当前库转到主库

              Exec sp_addumpdevice 'Disk', 'Testbak', '路径\文件名.dat'             --这里Disk为设备类型，还可以写Pipe(管道)，tape(磁带机)，不过都使用较少。

                                                                                                             --Testbak是为当前设备起的别名

              --开始备份

              Backup Database 要备份的数据库名 to Testbak

       4>数据库修改操作使用的比较少，一般即便要修改也是通过企业管理器来进行，具体的语句这里不写了。

 

       2, 【数据表】及各类约束的操作

       1>创建新表

          a> 标准建表： Create Table TbName(Col1 Type1[not null] [ Primary key] Default(Value1), Col2 Type2[null],...)

          b>利用查询建表: Select * into 目地表名 from 原表名

              这种建表的方式，不能把原表中的约束复制到新表中，必须通过其他语句逐一复制。如果利用这样的语句建立一个空表结构，可以增加 Top 0 或者 Where 1=0即可。

              /*还有一种查询建表的方式: Create Table TbName as Select Col1,Col2 From TbOldName [Definition only]

              这里的Definition only 也是只建立表结构的意思，第二种查询建表只对Oracle数据库有效。*/

       2>删除表

           Drop Table TbName

       3>修改表

           a>增加列

               Alter Table TbName Add ColName ColType [Default Value]

           b>删除列

               Alter Table TbName Drop Column ColName

           c>添加/删除　主键 (这里也可以通过建立和删除约束的方法实现同样的效果)

               Alter Table TbName Add Primary Key (ColName)   --注意，这样设置的列，必须是不为空的列,为空列设置主键会报错。

               /*Alter Table TbName Drop Primary Key(ColName)  这种语法只适合Oracle,SqlServer只有通过删除主键约束的方式来删除主键)

            d>更改列的字段类型

              Alter Table TbName Alter ColName ColType           --注意，不是所有类型列都可以转换的，其兼容级别可以通过 sp_dbcmptlevel　来查询

                                                                                       --另外，对已经有约束的列，也是不允许更改的，必须先删除其约束

        4>修改列的高级附属属性

            a>索引的建立与删除:

                Create [Unique] Index IdxName On TbName(ColName1, ..)     --这里的Unique 效果也可以通过约束来实现，效果细节有差别。

                Drop Index IdxName on TbName

　　　　　 注意，索引是不可以更改的，如果要修改原有的索引，必须通过删除，然后重建来实现。

            b>约束的使用：

　　　　　　约束的对象是表，虽然其大多是对字段起作用。约束分四种：主键PK(PRIMARY KEY)，默认值DF(DEFAULT)，唯一UQ(UNIQUE)，

                 规则检查CK(Check), 外键约束FK(Foreign Key).

                 (1) 建立

                 约束都是以Alter Table 开始的。

                 Alter Table TbName Add/Alter ColName CONSTRAINT 约束名 约束类型    --这里允许省略　CONSTRAINT 约束名 直接跟约束类型，系统会自动生成约束名

                 也可以直接建约束

                 Alter Table TbName Add CONSTRAINT  约束名　约束类型   --整体约束

                或者在建表的时候，直接增加约束:

                 Create Table TbName(

                    Col1 ColType1 约束类型..,      --这种方式，约束名会由系统自动生成.

                    Col2...,

                    ...

                    CONSTRAINT  约束名 约束类型..    --这种方式，适合对多字段建立整体约束例如Check

                    )

                 (2) 删除                  
                     Alter Table TbName  Drop CONSTRAINT  约束名

                 (3)约束使用示例:

                 ★主键:Alter Table TbName ADD CONSTRAINT PK_主键约束名后部 Primary key [NOCLUSTERED]([ColName])

 

                 ★默认值：Alter Table TbName ADD CONSTRAINT DF_默认约束名后部 DEFAULT (DefaultValue) FOR [ColName]

                 当然也可以这么写: Alter Table TbName Add ColName ColType Default(DefaultValue)  [not null]  --系统自动生成约束名,也可以在Create Table中这么写.

 

                 ★唯一约束: Alter Table TbName   ADD CONSTRAINT UQ_唯一约束命后部 UNIQUE (ColName)   --同样，也可以在增加字段时，由系统自动命名

                      自动命名写法: Create Table TbName(..., ColName ColType Unique, ...)      

                 这里需要强调下主键约束，唯一约束，唯一性索引的区别：

                 i>主键约束会自动创建唯一性索引，并且是非空的，在表中无其他聚集索引的情况下，会自动标志为聚集索引(Clustered).

　　　　　ii>唯一性索引，必须主动命名，无法象约束一样由系统自动生成。其值虽然唯一，但是允许多个空值的存在。其主要的意义是加速表遍历。

                 iii>唯一性约束，可以在定义字段时自动由系统生成，并且不允许有多个空值(可以有一个Null)，其存在的意义是保证字段的完整性，而不是访问速度。

                 iv>这里要对第三条进行一个修正，在SQL2008以后的数据库中，建立唯一约束时，会自动建立出一个唯一索引，并标识出Unique Key（与唯一索引不同），

                     而且，本身约束也会检查是否有多个null值，并因重复而阻止。带Unique Key标志的索引，会直接影响并出现在视图：information_schema.key_column_usage。 

 

                 ★规则检查约束: Alter Table TbName Add [Constraint CK_检查约束后部]　Check(检查性约束的布尔表达式) 

　　　　　　Check约束一般是精确限制一个或几个字段的取值范围，以及实现一些字串的掩码。其定义比其他的约束要随意得都，虽然可以在新增

　　　　　　列的定义后面附加Check约束，但是它并不依附在某一列上，而是一个全局性的约束。并且，其约束名称也可以随意省略。

                 注意：i>有Null参与的比较运算，其结果被Check表达式判定为Unknow值，而不是False值，这个不受其限制的；

                         ii>在Delete 语句中，增加约束是不起作用的　例如:Delete TbName Check(表达式)

                         iii>在查询的列上增加Check约束，可以提升查询性能。

　　　　　　★外键约束:Alter Table TbName1 Add [Contraint FK_外键后部] Foreign Key(ColName) References (TbName2)

                                [On Update Cascade On Delete Cascade]

                 这里的ColName会自动去对应引用表TbName2中的主键，类型必须一致；并且，当一个表被引用的话，其他表中的外键引用约束不删除，

　　　　　　　　则被引用表也不允许删除，被引用表的主键约束也不能被改变。

                 注意：这里的On Update Cascade　和 On Delete Cascade就是外键的精华了，它可以防止在从表中出现垃圾数据（丢失外键的数据).

                          On Update ...是当主表(TbName2)中的主键主动改变的时候，所有的从表(TbName1)中的外键也随着一起改变；

　　　　　　　　　On Delete ...是当主表(TbName2)中的主键所在记录被删除时，会自动删除其从表(TbName1)中所有对应的明细。

       ３, 【记录】的增删改查操作．

        　记录的操作不会影响到表的结构，但是其操作的复杂度却可以比数据库操作和表结构操作复杂得多，在表嵌套和表关联后可以直接增加其算法复杂度的级数。

　　　　１>新增记录:

　　　　Insert [Into] TbName (字段名列表) Values　(值列表)　　--这里　字段名列表　如果省略的话，代表所有字段；

           Insert [Into] TbName Select 查询                                -- Values(值列表)其实是一个关系集合，也可以由查询实现，注意这里查询并不需要括号

           ２>修改记录:

           Update TbName Set

               ColName1=Value1, ColName2=Value2,..

           From TbName                   --这里允许跟几个表的关联模式，但是更新时，只针对Update后接的表，这里的关联表，只相当于多了一些循环。

           Where 条件

           3>记录删除

           Delete TbName1,TbName2,...               --这里的表名可以省略，默认是From中出现的所有表，如果单独删除某一个表的记录，这里就打某一个表名。

           From TbName1,TbName2,...关联关系

           Where 条件                                         --这里省略的话，就是删除对应表中的所有记录。

           4>记录查询

           记录查询表面上看似不重要，因为其出错不会改变实际记录而造成影响，但是事实上查询是记录操作中最复杂的部分，因为它可以嵌在前三种操作当中，成为子操作。

             a>普通查询:

                 Select [别名=]ColList1              --这里可以为每一列重新命名，也可以用[As 别名]的方式，效果跟[别名＝]是一样的。

                 From TbName [As 别名]            --其实这里的As也可以省略

                 Where Condition                      --省略Where Condition的话，是默认查所有数据

                 Order By ColList2 [Desc]          --这里Desc省略的时候，默认值是Asc　即自小到大升序排列;　ColList2也可以用序号代替，如1,2,3会自动对应查询结果列.

 

             b>统计(聚合)函数查询:

                 ★Avg:平均数。

                      如果非要将null值的数值参与计算的话，可以用isnull(表达式,0)来转换；整型字段的平均值计算，不会自动转换为实型，而会自动以去尾法(Floor)取整。

                 ★Count:计数。

                      可以统计一个或多个字段的数量，同样需要统计null的话，需要用isnull进行转换；Count支持内置的Distinct使用技巧，可以只统计非重复列。 

                 ★Min/Max: 最小/大值。

                      往往用排序加Top 1的方式也可以求到最值。

              　★Sum:求和

                      自动忽略null值，所以不用额外写isnull；只能对整型和实型类数值操作，不能对字串或流操作。

                 ★CheckSum,Binary_CheckSum,CheckSum_Agg : 行检查函数

                      CheckSum:生成一列或多列的校验值，常用于建立哈希索引。

                          eg. Alter Table TbName Add NewCol As CheckSum(ColList)

                               Create Index IX_NewCol On TbName(NewCol)

                      Binary_CheckSum:常用于整行统计出校验值，用户检测行改变。但是它会忽略二进制和游标字段(text,ntext,image,cursor).

                      CheckSum_Agg:可以将一个小组校验值转换成一个整型数输出，从而校验整个小组数据的改变。可以用Distinct连用，组内数据的顺序不会改变校验值。

                          eg. Select GroupName, CheckSum_Agg(Binary_CheckSum(*))

                               From TbName Group by GroupNmae.

                 ★Var/Varp: 样本/总体 方差.  计算每个项，离其平均值差的平方均值。

                                 

                                 上面的公式,【S方】就是方差；　而【S】是标准偏差。

                 ★Stdev/Stdevp:样本/总体 标准偏差.

                    方差及标准偏差的ＳＱＬ分布计算，以计算表TbName中的iQty字段为例:               

                    SELECT @AVG=AVG(iQty*1.0)
                                 FROM TbName

                    SELECT  SUM(SQUARE (iQty-@AVG))/COUNT(*) AS [VARP],              --总体方差
                        SQRT(SUM(SQUARE(iQty-@AVG))/COUNT(*)) AS [STDEVP],          --总体标准方差
                        SUM(SQUARE(iQty-@AVG))/(COUNT(*)-1) AS [VAR],                    --样本方差             样本在总统计数上，少１
                        SQRT(SUM(SQUARE(iQty-@AVG))/(COUNT(*)-1)) AS [STDEV]       --样本标准方差
                    FROM TbName                             

                 注意：所有聚合函数都会忽略表达式为null值的项。

                

             c> 集合运算查询:

                 如果将一个查询结果看做一个集合，那么SQL就提供了三种集合运算符：Union(并), Except, InterSect(交).

                 Except是计算在前一个集合中存在，但不在后一个集合中存在的记录。 Union结果=Except结果+InterSect结果。

               　注意:　Union的计算结果都会自动合并相同行，如果在连接符号后跟All 则自动去掉重复行；　

　　　　　　　　　　必须要保证两个查询的数据类型结构一致，如果出现结果字段名不一致的情况，则集合运算结果按第一个表的结果字段名取新字段名。

    

             d> 表连接:

                ★内连接: 也称为普通连接或者自然连接，标准写法: [Inner] Join TbName On Condition。 这里Inner 通常允许省略，并且如果直接将表排列在一起，

　　　　　　　　　　 并且用逗号隔开，ＯＮ的连接条件写到后面Where 部分里，也是允许的，在ＳＱＬ－９２标准以后，这样写的执行效率不会受任何影响。

                ★左连接: 通常左连接可以代替右连接，不是在特别必要的情况下，能用左连接，就不写右连接，以免造成人的使用差异。左连接的标准写法为:

                             Left Join TbName On Condition 这里什么都不可以省略。当TbName中找不到符合Condition的数据时，会对应左表记录生成对应右表字段类型

　　　　　　　　　　但值为Null。这种连接适合取基础表数据，不会因为基础资料的缺失影响到整体表的查询结果条数。

                ★外连接: Full [Outer] Join On Condition 这种连接跟inner内连的不同之处，是会在类Inner的条件下，附加一组左表全为Null值匹配全右表的结果记录，

　　　　　　　　　　还附加一组右表全为Null匹配左表全记录的结果记录。这相当于把左连接，右连接相对内连接的增量，一次全加在内连接上。

                ★全连接: Cross Join 注意，这种连接是不需要On条件的，相当于写两个表名列表，然后Where 1=1的效果。它的结果集记录数恰好是左表记录数*右表记录数。

             e>查询嵌套:

                 这里只讲两层查询嵌套的模式，多层嵌套可以由两层嵌套扩展

　　　　　　用一条语句表示三种嵌套形式:

　　　　　　Select ColName1,

                           AliasName=(Select Top 1 ColName From TbName2 Where KeyName=A.KeyName)          --第一种嵌套

                 From TbName1 A inner Join (Select * from TbName3) As B On A.KeyName=B.KeyName            --第二种嵌套

                 Where exists(Select 1 From TbName4 Where KeyName=A.KeyName)                                       --第三种嵌套

                 ★第一种嵌套:利用了查询语句返回单值的效果，用一个子查询直接赋值，这么做的好处跟建空字段，然后用表关联Update效果很象，

　　　　　　　　　　　　不会因为空值或者多值影响到查询结果的记录数。但是效率不是很高，虽然因为SQL缓存的关系不会形成多次查询，但是

　　　　　　　　　　　　对于大型数据表，最好还是用建空字段＋Update语句的形式来更实现。

                 ★第二种嵌套:最常用的嵌套，一般不会象举例那样查单表，而是用多表关联并带很多条件或者还有分组的查询结果作为一个新表B,来实现嵌套。

                                  这样做也有一些缺点，就是嵌套的查询结果在外层是没有索引的，这个也不利于超大型数据的处理。

                 ★第三种嵌套:这种子查询是利用了查询返回是否为空的特性，使用很少，也有人很喜欢用 ColName1 In (子查询)的模式进行判断。但是，

　　　　　　　　　　　　有一种需求是非利用这样的查询完成不可，不然就需要写多段复杂语句，那就是分组取每组的Top n 结果记录。

                  eg. Select * from TbName A

                        Where (Select Count(1) from TbName B Where A.KeyCol=B.KeyCol And B.OrderCol>A.OrderCol)<nTopQty

                        Order by KeyCol,OrderCol  --这里KeyCol是用来分组的字段，OrderCol是每组用来排序的字段，nTopQty是每组显示的前多少条。

                       上面这条语句实现的效率非常高，并且使用了自身查询的效果。当然，还有另一种也是一条语句解决问题的，效率也不错：

                       Select * from

                       (Select *, Row_Number() over (Partition by SectionID order by OrderCol) as RowID from TbName) A

                       Where A.RowID<=nTopQty

                       Order by KeyCol,OrderCol

                      这里调用了系统函数Row_Number()功能，并且使用的是第二种嵌套模式。