SQL Server 知识点_sql server的考点-CSDN博客

本文链接：https://blog.csdn.net/mxcheng0126/article/details/107767899

一. NULL 值

 1. NULL 值代表遗漏的未知数据，用作未知的或不适用的值的占位符。
 2. 如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
 3. 无法比较 NULL 和 0；它们是不等价的，0表示一个确定的值
 4. NULL 不等于空字符串" "。
 5. NULL 不能参与 <> 和 != 和 = 运算
 6. 任何数字与null参与数学运算结果都是null
 7. 任何值与null比较时都会返回false
 8. 永远不会有什么数据等于NULL
 9. 必须使用 IS NULL 和 IS NOT NULL 操作符。

二.事务

定义：

事务是作为单个逻辑单元执行的一系列操作，它是一个不可分割的工作逻辑单元。
它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。
如果某一事务成功，则在该事务中进行的所有数据更改均会提交,成为数据库中永久组成部分，如果事务中遇到错误就必须取消或回滚，则所有数据更改均被撤销。

特别适用于多用户同时操作数据库系统，例如：航空系统订票银行保险公司证券等等；

举个例子，我们经常用到的 ATM 存取款机，比如转账的时候，是先减去转出账户的金额，然后再在指定转入账户的金额加上转出的金额。如果刚好这个时候转出的操作已经执行完成，但是由于系统的故障，导致转入的操作失败了。那么怎么办？这就需要用到事务了，只要事务里面有一条命令未成功执行，那么数据就会回滚到事务开始之前的状态。

2.特性

(1) 原子性（Atomicity）：事务是一个完整的操作，事务中所有操作命令必须作为一个整体提交或回滚。如果事务中任何操作命令失败，则整个事务将因失败而回滚。

(2) 一致性（Consistency）：当事务完成时，数据都处于一致状态。

(3) 隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，它不以任何方式依赖或影响其他事务。

(4) 持久性（Durability）：事务提交之后，数据是永久性的，不可再回滚。

例子：

xact_abort on/off  on：开启，事务一旦出问题，全部回滚  off：关闭，不检查事务是否发生错误。

begin tran
            declare    @errorcount int
                 set    @errorcount = 0;
begin try
          update #aa set Value = '5'
end try
begin catch
          set @errorcount = @errorcount+1
end catch

if(@errorcount > 0)
begin
         rollback tran
         print @errorcount
end
else
begin
         commit tran
         print @errorcount
end

--或者使用@@error
if(@@error > 0)
begin
         rollback tran
end

索引

定义：
索引是通过提供对数据库表、行的快速访问而创建的，以加快从数据库表或视图的数据检索和查询处理操作的速度，而无需扫描表的所有数据，以便检索请求的数据。
数据库中的索引与书籍中的目录类似，索引是SQL Server编排数据的内部方法，它为SQL Server提供一种方法来编排查询数据的路由。
索引页是数据中存储索引的数据页。索引页存放检索数据行的关键字页及该数据行的地址指针。通过使用索引，可以大大提高数据库的检索速度、改善数据库性能

优点：
① 建立索引的列可以保证行的唯一性，生成唯一的Row Id
② 建立索引可以有效缩短数据的检索时间
③ 建立索引可以加快表与表之间的连接
④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

索引的缺点：
① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
② 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
③ 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

什么样的表跟列要建立索引：
① 总的来说就是数据量大的，经常进行查询操作的表要建立索引
② 表中字段建立索引应该遵循几个原则：

越小的数据类型通常更好：越小的数据类型通常在磁盘、内存中都需要更少的空间，处理起来更快。
简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂，处理起来也更耗时。
尽量避免NULL：应该指定列为NOT NULL。含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义，所以索引的建立应当更多的选取唯一性更高的字段。
③ 表与表连接用于多表联合查询的约束条件的字段应当建立索引
④ 用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引。
⑤ 添加多列索引的时候，对应的多条件查询可以触发该索引的同时，索引最左侧的列的单条件查询也可以触发。
⑥ 如果有些表注定只会进行查询所有，也就没必要添加索引，因为查询全部只能进行全量搜索即扫描全表。

索引的原理：
索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全量搜索，也就是进行全局扫描，有多少条数据就要进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。而建立索引之后，会将建立索引的KEY值放在一个n叉树上（BTree）。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表，每次以索引进行条件查询时，会去树上根据key值直接进行搜索，次数约为log总条数，底数为页面存储数，例如一个100万数据的表，页面存储数为100，那么有索引的查询次数为3次log1000000100，但是全量搜索为100万次搜索，这种方式类似于二分法，但是这个是n分法。

聚集索引和非聚集索引的区别

根本区别
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

聚集索引
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引
非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

create database IndexDemo 
go 
use IndexDemo 
go 

create table ABC 
( 
A int not null, 
B char(10), 
C varchar(10) 
) 
go 
insert into ABC select 1,'B','C' 
union select 5,'B','C' 
union select 7,'B','C' 
union select 9,'B','C' 
go select * from abc

在这里插入图片描述

优化

1.避免在where子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描
2.避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据
3.尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段
设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
4.消除对大型表行数据的顺序存取
尽管在所有的检查列上都有索引，但某些形式的WHERE子句强迫优化器使用顺序存取。如：

SELECT * FROM orders 
WHERE (customer_num=104 AND order_num>1001) 
OR order_num=1008
解决办法可以使用并集来避免顺序存取：
SELECT ＊ FROM orders 
WHERE customer_num=104 AND order_num>1001
 UNION
  SELECT ＊ FROM orders WHERE order_num=1008

5.能够用 between 的就不要用 in