SQL Server

-------------------------------一、sql搜索like通配符的用法
我们写的最多的查询语句可能是这样的

select * from computes where 字段 name '%sql技术%'

那么，上面sql语句中的%是起什么作用呢？

%,sql中查询通配符，它匹配包含零个或多个字符的任意字符串
比如上面sql的意思就是查询出name中包含'sql技术'的所有记录。
因为%匹配零个或者多个字符。我们可以只使用一个%来查询只是开始或结尾处包含'sql技术'的所有记录，分别如下：
1,查询所有name字段以sql技术开头的记录。

select * from computes where 字段 name 'sql技术%'

2,查询所有name字段以sql技术结尾的记录。

select * from computes where 字段 name '%sql技术'

除了%外，可能我们有忽略掉sql中另一个通配符了，它就是下划线'_'。

_,匹配sql中任何单个字符
比如下面的sql

select * from hr_staff where name like '_bcd'

那么它将返回所有name长度为4，且以'bcd'结尾的所有记录,比如abcd,ebcd都会满足查询条件，但aebcd就不满足了，因为_是匹配单个字符。

有了匹配单个与多个字符的方法了，那么有没有匹配指定范围内字符的写法呢？这个当然也是有的。就是使用范围符中括号'[]'了。

[],指定[]内标识范围或集合中的任何单个字符
比如，看下面的sql的写法：
1,返回name字段以a或者b或者c开头的所有记录

select * from hr_staff where name like '[a,b,c]%'

2,返回name字段以a或者b或者c结尾的所有记录

select * from hr_staff where name like '%[a,b,c]'

[]中集合还有一种写法，就是以-来表示从某字符开始，到某字符结束。比如下面的sql语句

select * from hr_staff where name like '[a-c]%'

它表示查询hr_staff表中name字段所有从a开始，到c结束开头的记录，它的作用与select * from hr_staff where name like '[a,b,c]%'是一样的。还有[0-9]表示匹配从0到9中的任意数字。

有了在范围内的写法，对应就会有不在范围内的写法：

[^],表示不属于[]指定范围或集合的任何单个字符
比如:[^0-9]表示不匹配任何数字。
示例：我们要查询表table1的col1列全为数字的记录，sql语句可以这样写：

select * from table1 where col1 not like '%[^0-9]%'

 

---------------------------------------------二、在sql中获取所有表的列信息
可能有朋友要说了，这还不简单，在企业管理器或microsoft sql server management studio中展开对应表下面的列信息不就可以看到列的详细信息了么？当然这是可以的，但这里我们主要讲的时如何利用sql语句来查询指定表的列信息。

利用sql语句来查询列信息，就是要用到系统视图sys.columns，这个视图记录了数据库中所有表，视图，表值函数等的所有列信息。我们可以利用语句select * from sys.columns来查看这个视图返回的信息。
sys.columns返回的列比较多，大部分情况下很多列的信息我们可能用不到，下面我们只解释一下比较常用的列的信息。

1,object_id--这个列是比较重要的，它是返回当前列所属表的ID。利用它，我们就可以查询指定表的所有列信息，比如下面的sql语句是查询表table1的所有列信息。

select * from sys.columns where object_id=object_id('table1')

2,name--该列的列名。
3,column_id--该列在数据库中的ID，注意，数据库中任何对象的ID都是唯一的。
4,system_type_id--该列的类型的ID，和下面max_length,precision,scale三列一起可以来举个示例。
5,max_length--该列的最大长度
6,precisionp--如果这列是数值列，那么这是该列的精度，否则就是0
7,scale--如果这列是数值列，那么这就是列的小数位数，否则就是0

system_type_id，max_length,precision,scale四列结合系统视图sys.types一起我们来举个示例。
如果我们要查询表table1的所有列，及列的类型，列的精度，列的小数位数，sql语句如下：

select a.name,b.name,a.max_length,a.precision,a.scale
from sys.columns a left join sys.types b on a.user_type_id=b.user_type_id
where a.object_id=object_id('table1')

8,is_nullable--该列是否可以为null
9,is_identity--该列是否是标识列
10,is_computed--该列是否是计算列。利用该列，我们也就可以查询某个表的所有计算列了，比如我们要查询表table1中的所有计算列，sql语句如下：

select * from sys.columns where object_id=object_id('table1') and is_computed=1

sys.columns视图中常用的列就是这10列了，其它列的信息大家可以到SQLServer 2005联机丛书中查询。                           

-------------------------------------三、sql server的加密和解密

在sql server中，我们如何为数据进行加密与解密，避免使用者窃取机密数据？

对于一些敏感数据，如密码、卡号，一般不能使用正常数值来存储。否则会有安全隐患。以往的加密解密都有前端应用程序来辅助完成。而数据库一般只能加密不能解密。

从2005开始提供了数据库层面的数据加密与解密。其实现方式主要有以下：

1、利用CONVERT改变编码方式：
利用该函数把文字或数据转换成VARBINARY。但该方式不具备保护数据的能力，仅避免浏览数据的过程中能直接看到敏感数据的作用。
示例：

CREATE TABLE t_test
(
  userID INT IDENTITY(1, 1) ,
  userName VARCHAR(10) ,
  userSalary FLOAT ,
  cyberalary NVARCHAR(MAX)
);

INSERT INTO t_test
(userName,userSalary )
select 'taici', 1234
union all
select 'hailong', 3214
union all
select 'meiyuan', 1111

--ALTER TABLE test
--ADD userNewSalary VARBINARY(512)
--使用转换函数把数据转换成varbinary，改变编码方式。
SELECT *,CONVERT(VARBINARY(512), userSalary) FROM  t_test

--把数据转换成int，可以恢复原有编码方式
SELECT *,CONVERT(INT, userSalary) FROM t_test

2、利用对称密钥：
搭配EncryptByKey进行数据加密。使用DecryptByKey函数进行解密。这种方式比较适合大数据量。因为对称密钥的过程好用资源较少。
示例：

--创建对称密钥
CREATE SYMMETRIC KEY SymKey123
WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD='P@ssw0rd'
GO
--注意事项:在启用时,需要先OPEN SYMMETRIC KEY 搭配密钥密码，否则所产生的数据都会是null值。而且需要搭配Key_GUID函数来使用
--打开对称密钥
OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';
--进行数据加密
SELECT * ,ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
FROM Person.Address

--把加密后数据更新到原来另外的列上
UPDATE Person.Address
SET AddressLine2=ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
--解密:解密过程同样需要OPEN SYMMETRIC KEY ,且需要利用DECRYPTBYKEY 和CONVERT函数
OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';

SELECT AddressID,CONVERT(VARCHAR(MAX ) ,CONVERT (VARCHAR(MAX ),DECRYPTBYKEY(AddressLine2)))
FROM Person.Address

3、利用非对称密钥：
搭配EncryptByAsymKey进行数据加密。使用DecryptByAsymKey函数进行解密。用于更高安全级别的加解密数据。因为耗用资源叫多。
示例：

--非对称密钥使用两种不同的密钥，所以加密是是不需要输入密码验证，但解密时就需要
CREATE ASYMMETRIC KEY AsymKey123 WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD='P@ssw0rd';
GO

--添加新列存储加密后的数据
ALTER TABLE Person.Address ADD  AddressLine3 nvarchar(MAX)
GO

--进行加密
SELECT *,ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))
FROM Person.Address
GO

--把数据更新到一个新列
UPDATE Person.Address
SET AddressLine3=ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))

SELECT *--addressline3
FROM Person.Address

--解密：此过程一定要使用密码来解密，此处的类型要与加密时相同，比如加密时用varchar,而这里用nvarchar的话是解密不了的。
SELECT TOP 10 AddressID,CONVERT(VARCHAR(MAX),CONVERT (VARCHAR(MAX ),DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey123'),AddressLine3,N'P@ssw0rd'))) AS Decryptedata
FROM Person.Address

4、利用凭证的方式：
搭配EncryptByCert进行加密和DecryptByCert函数进行解密。比较类似非对称密钥。
示例：

CREATE CERTIFICATE certKey123--证书名
ENCRYPTION BY PASSWORD='P@ssw0rd'--密码
WITH SUBJECT='Address Certificate',--证书描述
START_DATE='2012/06/18',--证书生效日期
EXPIRY_DATE='2013/06/18' ;--证书到期日
GO
--利用证书加密
SELECT *,ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1)) cyberAddress
FROM Person.Address   
   
--添加新列存放加密数据
ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX )

--把加密后数据放到新列
UPDATE Person.Address
SET AddressLine4=ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1))

--解密
SELECT AddressID,CONVERT(VARCHAR(MAX ),CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('certKey123'),AddressLine4,N'P@ssw0rd'))) DecryAddressu) FROM Person.Address

5、利用密码短语方式：
搭配EncryptBypassPhrase进行加密，使用DecryptByPassPhrase函数来解密。可以使用有意义的短语或其他数据行，当成加密、解密的关键字，比较适合一般的数据加解密。
示例：

--短语加密：该过程较为简单，只需要使用EncryptByPassPhrase函数，使用短语加密时，参考的数据航不可以变动，否则解密失败。
SELECT *,AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)
FROM Person.Address

--添加新列存放数据，注意，ENCRYPTBYPASSPHRASE函数返回的是VARBINARY类型
ALTER TABLE Person.Address ADD AddressLine5 VARBINARY(256)

--将数据更新，过程中使用P@ssw0rd和AddressID数据行当成密码短语
UPDATE Person.Address
SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)

SELECT * FROM Person.Address

 

 

-----------------------------------------四、在sql server数据库中取指定范围内的随机日期

我们必须先了解数字与日期之间的转换规则。
可能还有很多的朋友还不清楚甚至还不知道数字与日期之间能够相互转换。我们先来看一个示例：

select convert(float,convert(datetime,'2008-1-1'))

该sql会报错吗？日期格式的值能转换为数字吗？答案是当然可以的，以上sql返回结果39446。那么，为什么是这个结果呢？我们再来看一个sql.

select dateadd(d,39446,'1900-01-01')

返回结果2008-01-01 00:00:00.000，再看一个示例：

select convert(datetime,39446)

返回结果2008-01-01 00:00:00.000

呵呵，从上面的示例结果中相信大家已经看明白了，在sql server中日期与数字之间的转换，就是返回从1900-01-01开始到转化的日期之间的天数。反过来，从数字转换成日期，就是返回1900-01-01加上转化数字的天数后的日期。

明白了这个规则，我们就开始今天的示例了:在sql server数据库中取指定范围内的随机日期
示例如下：

Create FUNCTION [dbo].[udf_GetRandomDatetime]
(
@MinValue datetime = null,
@MaxValue datetime = null
)
RETURNS datetime
AS
/*
函数名称:udf_GetRandomDatetime
功能简述：取随机日期
相关对象：无
参数：@MinValueDecimal 最小值
@MaxValueDecimal 最大值
*/
BEGIN 
declare @RandomValue float
declare @ReturnValueDecimal Datetime
declare @MinValueDecimal decimal
declare @MaxValueDecimal decimal

--取最小日期对应的数值
if @MinValue is not null
 begin
 select @MinValueDecimal = convert(float,@MinValue)
 end
--取最大日期对应的数值
if @MaxValue is not null
 begin
 select @MaxValueDecimal = convert(float,@MaxValue)
 end

while(1=1)
 begin
 --从随机数视图中获取一个随机值(函数中不能直接使用rand())
 select @RandomValue = RandValue
 from V_Rand
 --根据最大最小值获取随机整数
 if @MinValueDecimal is not null and @MaxValueDecimal is not null
  begin
  select @ReturnValueDecimal = @RandomValue * @MaxValueDecimal
  if  @ReturnValueDecimal  >= @MinValueDecimal and @ReturnValueDecimal <= @MaxValueDecimal
   begin
   break
   end
  end
 else if @MinValueDecimal is not null and @MaxValueDecimal is null
  begin
  select @ReturnValueDecimal = @RandomValue * @MinValueDecimal
  if  @ReturnValueDecimal  >= @MinValueDecimal
   begin
   break
   end
  end
 else if @MinValueDecimal is null and @MaxValueDecimal is not null
  begin
  select @ReturnValueDecimal = @RandomValue * @MaxValueDecimal
  if  @ReturnValueDecimal <= @MaxValueDecimal
   begin
   break
   end
  end
 else if @MinValueDecimal is null and @MaxValueDecimal is null
  begin
  select @ReturnValueDecimal = @RandomValue
  break

 注意：该示例中需要用到Rand()函数，但又因为在自定义函数中不能使用Rand函数，所以我们创建了V_Rand视图来解决该问题

执行sql,自定义函数创建成功，我们再来调用函数看一下结果吧：

select dbo.udf_GetRandomDatetime('2008-1-1','2010-12-1')

 -------------------------------------------五、往sql server 的表中插入列与在表尾追加列的区别

 

以前一直以为在SQL SERVER中，在表的列中间插入新列与在表的最后面添加一列，这两者应该是一样的。今天特意为这两者做了对比，分析如下：

我们先创建一个简单的表，用来做分析：

CREATE TABLE [dbo].[Table_1](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

然后在Microsoft SQL Server Management Studio中在该表的ID列与Name列中插入varchar(50)列UserID,然后点击生成更改脚本，生成的脚本如下：

/* 为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Table_1
 (
 ID int NOT NULL IDENTITY (1, 1),
 UserID varchar(50) NULL,
 Name varchar(50) NULL
 )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table_1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table_1)
  EXEC('INSERT INTO dbo.Tmp_Table_1 (ID, Name)
  SELECT ID, Name FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
GO
DROP TABLE dbo.Table_1
GO
EXECUTE sp_rename N'dbo.Tmp_Table_1', N'Table_1', 'OBJECT'
GO
COMMIT

可以看出来，SQL SERVER是先将插入后的列和原有的列一起创建了一个新的表Tmp_Table_1，然后设置IDENTITY_INSERT为on，即开启自增量显式插入,再将Table_1的数据复制到Tmp_Table_1中，然后关闭自增量显式插入,再然后删除dbo.Table_1，最后修改dbo.Tmp_Table_1表名为dbo._Table_1

  end  
 end

return convert(datetime,@ReturnValueDecimal)

END