MSSQL2008 临时总结文档2014

2.  索引可以建立在基表上（基表索引），也可以视图上称为视图索引。

基表索引是建立在表数据中的，故视图索引是建立在查询后的结果集上的。

（假如建立了视图索引）当从视图中取数据的时候，优化查询分析器会根据实际情况判断如果使用视图索引性能较小，那么就使用视图索引。

默认，如果视图定义的列在基表中已经定义了索引，那么即使不显示定义视图索引，也会有视图索引可以使用。

聚索引似书的页码，非聚簇索引类似目录。

4.完整性约束：级联操作

1）级联删除，级联更新。   指的是被引用键和外键的关系。

级联操作：当用户视图更新或者删除现有外键指向的键时，sql server执行的操作

2）定义:create table 或者 alter table 中的 references 中建立

3）on delete  [on action|cascade|set null|set default]

     on update…

如果没有指定 ON DELETE 或 ON UPDATE，则默认为 NO ACTION

on action  试图删除或者更新某行数据，但是改行的某个键在其他表的现有行中被引用。 则提示错误，并回滚。

on cascade   被引用键所在的行被删除或者更新，则外键所在的行数据也被删除或者更新。（注意多级级联操作。。）

set null         ….，外键所在的行设置为空，前提此列可以设置为空

set default     ….,外键所在的行设置默认值为此列类型的默认值。

4）如果 timestamp 列是外键或被引用键的一部分，则不能指定 CASCADE。

5）多个级联引用操作。

表A中id列 被表b中现有行的id引用（并且定义时候使用 on delete cascade），此时b的主键又被表C中的XX列引用（并且表c中定义外键定义的是 on delete cascade），则删除A某行，那么B

中关联的B中数据也删除，同样和b关联的数据也都被删除。

5. 外键，被引用键的说法。  A中id列，被B中cd列引用，则cd叫外键，A中的id叫被引用键

6.大多数使用联接的查询可以用子查询（嵌套在其他查询中的查询）重写，并且大多数子查询可以重写为联接

7.MSSQL数据先写到事务日志，不是实时写到数据库。数据库会在检查点查看事务日志，这才更新到数据库中。日志中修改记录都是顺序的，所以可以回滚

8.元数据就是数据的描述信息

9.触发器可与视图关联，约束不行。触发器用在业务涉及到多个变。

10.自定义函数和存储过程区别。

1）两者相似

2）自定义函数只能完成其范围内的职责，无副作用。例：不能在自定义函数内更改表，更改数据库参数，发送电子邮件等！

3）函数参数是值传递

4）不能返回text,ntext等类型，可以返回表数据类型

11. null 设置在列上

定义null要注意。注意其表达的意思。 注意：null和0，什么时候用null。

例： 表中某列代表销售增长率。  2012年为第一年销售，所以增长率为0.那么2013年的增长率应为null，而不是0，因为如果为0，就说明没增长，那么意思去年也有收入了。所以此列应该允许为null。

12.查询优化分析器

1）查询优化分析器会综合查询条件和索引和数据库当前性能，寻找最佳查询方式。会在返回结果和影响别的用户情况下权衡。

2）显示估计查询计划，实际查询计划   

注意：大多数情况估计和实际计划相同。

13. 联接

内连接  inner join       例：select * from A inner join b  on  x=y    or  select * from A ,B where A.x=B.x

外连接：  left (outer) join , right (outer)join on  xx.xx=yy.yy,  full (outer) join

14. any,some,all,in ,exists

1)in 和 exists

select * from administrator  where adminid in (select * from administrator where adMinid>0)
select * from administrator  where exists  (select adminid from administrator where adMinid>0)  --注意 exists 后面的子查询只能是一列

2)any 和some

some要求查询条件必须大于或者小于或者不等于子查询的所有给定的列值

例： select * from tableA where NumberOfDays < SOME ( select NumberOfDays  from  xx where  NumberOfDays beteween  xx  and  xx)

any 只要满足一个条件就可以执行

例：例： select * from tableA where NumberOfDays =any ( select NumberOfDays  from  xx where  NumberOfDays beteween  xx  and  xx)

注意：scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }      { SOME | ANY } ( subquery )

15.

嵌套查询.      内查询先查出，然后当作条件送到外面的查询   例：select * from A  where  id in (select id from b where  id>xx)
相关子查询   信息双向进行的(比如同一个表关联)

select * from A  as aa where  id in (select id from a as bb where aa.id=bb.id )

外先查询id，然后再转到内查询（后面括号里的查询），然后再转到外查询

16.  交集，差集，合并

1）union  合并结果集，不包含重复的。  合并的结果集要求对应的列名都一致

  union all   包含重复的

2) except   差集（最后结果集中有重复的去重复的）。   在左表中取出那些不在右表中的数据。

    intersect 交集(最后结果集中有重复的去重复的)

3)exists  也可以实现交集

not exists 可以实现差集

注意:几乎 exists和not exists 性能大于等于  except ,intersect

17  

>,<,  <> ，>=,<=是ISO标准

!= ，!>,!< 非iso标准

is null ,is not null

like ‘%f

18.  SQL SERVER 对XML的支持

1）列类型可以为xml类型

2）通过xquery 对xml类型列中数据特定的节点或者属性的值进行查询

3) xml模式集合 xml schema collection，检查数据是否符合自我定义的规范。

xml模式定义，以及使用

4)

xquery 的扩展xml数据操纵语言 xmldml
新加了3条指令 insert,delete,replace value of，区分大小写

5).nodes可以获取xml块

注意sql模式(关系数据库模式)和xml模式区别
xquery 方法是只读操作，不能更改

19. sql server 数据存储单元

1)区段是表和给定文件中索引分配存储空间的基本存储单元

8个64kb连续的页组成

注：本区多余的一条记录将放到另外一个区中，此条记录占用的是整个区（不是此记录本身存储大小）。

基于区段而不是实际使用的空间来分配空间的概念。但这浪费空间的总量通常并不多，只占整个数据库空间的小百分比。

2)页是真正达到真正的数据行的最后一个级别。

区段中的数目是固定的，但页中存储的行数是不固定的，取决于行的大小，行不允许夸页。

通常行的大小可以达到8kb
行的大小限制为8060个字符，行中最大列数限制为1024列
当某行某列存放的数据若为text,image,varchar max,varbinary max,则此列的值存放在别处，而本行本列的值存的是实际存放的地址信息。

注：页拆分，聚簇索引不会拆分页

20.全文目录与存储单元无关，而是全文索引的逻辑分组

文件流

21. SQL中排序

1) 二进制排序  

速度快，但基本不用，因为违背人类思维。原理每个字符都是一串数字。

2）字典排序

类似字典中排序，但是有改变。

可设 “区分大小写”,”区分重音”,以及其他字符集选项。

注：1. 每种语言都有自己默认的字典排序

        2.排序规则，影响数据是否相等，也影响在索引中存储的方式。

22. 聚合函数_   group_by_expression

1）意思：按照 groupby_expression的表达式进行分组

select  <selectlist> from t/view  group by   expressionn

注意：expression 表达式必须完全来自

2) group by  xx  with  rollup/cute

    group by  xx roolup/cute(a,b,c)

注意：group by 有ISO和非iso的语法，同时只能用一种语法样式。（对新代码一律使用ISO语法，支持非iso语法为了以前代码能向后兼容）

(msdn帮助文档中列出了哪些版本支持哪些指令，如sql2005不支持with rollup等模式，2008某版本支持)

例：（下面的例子rollup()，但是本机器sql2008 模式太低，无法运行）

生成简单的 GROUP BY 聚合行以及小计行或超聚合行，还生成一个总计行。

SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c)

--

消息 10707，级别 15，状态 1，第 3 行
当前兼容模式下不允许使用 CUBE() 和 ROLLUP() 分组构造。只有 100 或更高模式下才允许使用这些构造。

注意：group by 中的讲解的例子 数据都来自administrator表，adminid为主键，所有数据如下图

例1：使用  with rollup

例2：使用 with cube

SELECT adminid,[username],[userpwd]  FROM administrator
GROUP BY adminid,username,userpwd WITH CUBE   (19条数据)

3）表达式和selectlist的规定

*group by表达式可以包含 from中的表，派生表或者视图的列

*select 列表中任何非聚合表达式中的每个表的列或者视图的列都必须包含在 group by后面的表达式中。 也就是 若select出的列，没经过聚合函数处理，则必须都放在group by后面的表达式里。

（同样不能用*号）

*不能在 group_by_expression 中使用类型为 text、ntext 和 image 的列。

*group by 中的列名不能为 select中的别名列

例:不能使用*  （错误的例子）

use dblifeshop
go
select * from administrator group by adminid

消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 ‘administrator.userName‘ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

例子：（允许的做法，即基于选择出来的列，再去考虑group by后面的表达式。）

select ColumnA, ColumnB FROM TABLEA  GROUP BY ColumnA, ColumnB

select ColumnA+ColumnB from  tablea  group by ColumnA,ColumnB

                                                           group by ColumnA+ColumnB

select ColumnA+ColumnB+ constant from t  group by ColumnA,ColumnB        //constant 为常量，恒定不变的，非列

例：不允许  （多注意这个）

select ColumnA, ColumnB FROM TABLEA  GROUP BY ColumnA+ColumnB

select ColumnA+ constant+ColumnB from t  group by ColumnA+ColumnB      

,例：

--此句错的
select adminid,username from administrator group by adminid
--对
select adminid,len(username) from administrator group by adminid,username
--adminid int类型，和username nvarchar进行相加，则必须将int转换成nvarchar。且group 表达式必须有adminid,username缺一不可的。
select adminid+username from administrator group by adminid,username  

22.聚合函数

聚合函数对一组值执行计算，并返回单个值。除了 COUNT 以外，聚合函数都会忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用

T-sql提供12个聚合函数

大小 max,min; 平均求和 avg,sum;数目 count

MSSQL2008 临时总结文档2014,古老的榕树,5-wow.com