T-Sql（八）字段索引和数据加密

浏览数：18 / 时间：2015年06月12日

原文:T-Sql（八）字段索引和数据加密

　　t-sql的基本用法讲到第八章也差不多了，最后就讲下字段索引和数据加密，这两个内容对编程人员可能用的地方不是太多，还是那句老话“防患于未然”。

　　下面我就简单的说下字段索引和数据加密的内容，只是简单概述，对初学者起到抛砖引玉的作用，大牛们请飘过！！！

一，字段索引

　　索引是什么，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，例如 employee 表的姓名（name）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。

　　下面是百度百科对索引概述的优缺点；

　　优点：

　　1.大大加快数据的检索速度;

　　2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

　　3.加速表和表之间的连接;

　　4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

　　缺点：

　　1.索引需要占物理空间。

　　2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

　　这里我将下t-sql中索引的用法，示例代码：

1 create index IX_ID_NAME--创建索引
2 on worker(w_id,w_name)--在ID NAME 两个字段上创建非聚集索引
3 
4 drop index worker.IX_ID_NAME--删除索引
5 select * from sys.indexes where name = ‘IX_ID_NAME‘--查看索引

　　创建唯一非聚集索引语法：

1 create unique nonclustered index index_Production_Product  --创建唯一的非聚集索引
2 on Production.Product(ProductID,Name)
3 where Color is not null;

　我们在创建表的时候也可以指定主键为唯一非聚集索引。

　　示例代码：

1 create table Index_Table_Student 
2 (
3     id int identity primary key nonclustered,    --指定该主键为非聚集索引
4     name nvarchar(50)
5 )

　　启动和禁用索引示例代码：

1 alter index all      --禁用所有的索引
2 on Production.Product
3 disable
4 
5 alter index all      --启用所有的索引
6 on Production.Product
7 rebuild

二，数据加密（certificate，symmetric）

　　编程的时候我们有时候会接触到数据加密，但那个数据加密是表现在程序上的，数据库的数据加密我们平时会用的很少，除非一些敏感，机密的数据要使用数据加密，还有一种情况是防止数据库数据泄露。

　　下面主要讲t-sql中的两种数据加密方式：证书加密（certificate），密钥加密（symmetric）。

　　大家只要熟悉t-sql的语法，知道怎么创建证书和密钥，怎么加密数据，怎么解密数据就行了。

1，证书加密

　　首先我们创建一个证书：

1 create certificate WageCert             --创建证书
2 with subject=‘To Encrypt Wage‘,       　--证书主题
3 expiry_date=‘2014/1/1‘;                 --证书过期时间

　　expiry_date表示的是证书的过期时间，超过这个时间，创建的证书也就失效了，这点注意下。

　　创建好证书，下一步就是使用证书加密数据了，我们先创建一张员工表，然后插入数据对工资字段进行机密。

　　示例代码：

1 create table employee
2 (
3     EmpID int primary key,
4     Wage varbinary(500) not null        --工资字段，加密后为二进制数据
5 )
6 insert into dbo.employee
7 values(1,encryptbyCert(Cert_id(‘WageCert‘),‘5000‘))   --使用证书加密

　　使用证书解密数据示例代码：

1 select EmpID,CONVERT(varchar(30),DECRYPTBYcert(Cert_id(‘WageCert‘),Wage)) as Wage
2 from dbo.employee                                     --使用证书解密数据

2，密钥加密

　　密钥加密我们结合刚才的证书加密进行讲解。

　　首先创建一个证书和使用证书的密钥，示例代码：

1 create certificate AdminPwdCert               --创建证书
2 with subject=‘To Encrypt Admin Password‘,     --证书主题
3 expiry_date=‘2014/1/1‘;                       --证书过期时间
4 
5 create symmetric key PwdKey                   --创建对称密钥
6 with algorithm=aes_256                          --使用aes_256加密算法
7 encryption by certificate AdminPwdCert        --使用证书加密

　　使用密钥加密和使用证书加密不同的是，使用密钥加密的时候要打开密钥，然后加密数据，最后再关闭密钥。

　　示例代码：

 1 open symmetric key PwdKey                     --打开对称密钥
 2 decryption by certificate AdminPwdCert
 3 
 4 insert into dbo.Wrokers(WrokerID,Name,Sex,Monery)    --插入加密数据
 5 values(103,‘张三‘,‘男‘,encryptbykey(key_guid(‘PwdKey‘),‘1234‘))
 6 
 7 select WrokerID,Name,Sex,convert(varchar(100),DECRYPTBYKEY(Monery)) as Monery    --解密数据
 8 from dbo.Wrokers
 9 
10 close symmetric key PwdKey                    --关闭对称密钥