SQL Server 常用开发技术

Ø  简介

本文记录笔者在开发中遇到的MSSQL常见的开发技术分享给大家，包括以下内容：

1.   T-SQL 运行时生成语句

2.   使用RAISERROR函数抛出异常，及异常捕获

3.   查询数据表的容量/大小

4.   替换非法字符

5.   存储多国语言

6.  数据库存储特殊字符

7.   在查询结果中添加自增列的两种方法

8.   创建表语法中ON [PRIMARY]是什么意思

9.   使用触发器注意事项

10.  设置主表主键字段修改或删除时，子表外键字段一同修改或删除

11.  存储过程中使用游标，将当前数据库的所有用户存储过程加密

12.  使用游标遍历，将同一数据库服务中的A数据库数据表复制到B数据库中

13.  选择性查询字段

14.  同义词

 

1.  T-SQL 运行时生成语句

1)  使用EXECUTE执行动态命令

EXECUTE命令：执行Transact-SQL 批中的命令字符串、字符串或执行下列模块之一：系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。

语法：{ EXEC | EXECUTE }( { @string_variable | [N]'tsql_string } [+...n] )[ AS { LOGIN | USER } = 'name' ] [;]

参数说明：

EXEC：是EXECUTE的简写，两者皆可使用。

@string_variable：局部变量的名称，可以是任意char、varchar、nchar或nvarchar数据类型，其中包括(max)数据类型。可以将T-SQL代码封装在局部变量中被执行。

[N]'tsql_string'：常量字符串，可以使任意nvarchar或varchar数据类型。如果包含N，则字符串将解释成nvarchar数据类型。如果不是动态生成的字符串命令，直接将其写成常量字符串也可以直接被执行。

[ AS { LOGIN | USER } = 'name' ]：LOGIN指定执行的上下文(Context)为登录名，所以其执行范围为服务器级；USER指定执行的上下文为用户，所以其执行范围为数据库级。

注意事项：EXECUTE在使用中可能导致SQL注入式攻击，即超越用户本身权限的SQL语句可能会被执行，这样在生成动态命令字符串时，可以对字符串的内容进行检查。通过指定执行上下文，即使用[ AS { LOGIN | USER } = 'name' ]的语法形式，可以限定EXECUTE语句的执行环境，确保安全。

EXEC命令有两种用法，一种是执行一个存储过程，另一种是执行一个动态的批处理。以下所讲的都是第二种用法。

示例：

A.  拼接SQL语句存入变量执行

DECLARE @TableName varchar(50),@Sql varchar(MAX),@Id int;

SET @TableName = 'Student';

SET @Id = 3;

SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +' WHERE Id = '+CAST(@Id AS varchar(10));

EXEC(@sql);

结果集：

B.  EXEC括号中只允许包含一个字符串常量，但是可以串联多个变量

a)  多个字符串常量

EXEC('SELECT * FROM '+QUOTENAME('Student')+' WHERE Id = '+CAST(3 AS varchar(10)));    --语法报错

b)  多个字符串变量

DECLARE @sql1 varchar(MAX), @sql2 varchar(MAX);

SET @sql1 = 'SELECT * FROM '+QUOTENAME('Student');

SET @sql2 = ' WHERE Id = '+CAST(3 AS varchar(10));

EXEC(@sql1+@sql2);  --正常执行

 

2)  使用sp_executesql执行动态命令

sp_executesql是一个系统存储过程，其功能和EXECUTE大致相同，不同的是其支持参数替换功能。在使用sp_executesql执行时，可以使用参数。参数的使用有两种类型，一种是输入参数，一种是输出参数。而EXECUTE不支持参数替换功能。

功能：执行可以多次重复使用或动态生成的T-SQL语句或批处理，可以包含嵌入的参数。在批处理、临时变量作用域和数据库上下文上，SP_EXECUTESQL与EXECUTE相同。

语法、示例，参考 SQL Server 系统存储过程

 

3)  EXECUTE与sp_executesql比较

A.  使用sp_executesql效率比EXECUTE要高，同一类型的语句，只需编译一次即可，而EXECUTE执行几次就需要编译几次。

B.   参数替换带来的高效率：其最大的特想在于sp_executesql的参数替换，而EXECUTE语句不支持该功能。如果对于只是参数不同的操作，使用sp_executesq，SQLServer只需要编译一次；而如果用EXECUTE，SQLServer需要编译两次。

C.   执行计划重用带来的高效率：使用sp_executesql可以重用SQL Server的执行计划。多次执行T-SQL语句且只更改了提供给T-SQL语句的参数值时，可以使用sp_executesql而不要使用存储过程。因为T-SQL语句本身保持不变，仅参数值发生更改，所以SQLServer查询优化器可能会重用第一次执行时生成的执行计划。

 

2.  使用RAISERROR函数抛出异常，及异常捕获

生成错误消息并启动会话的错误处理。RAISERROR 可以引用 sys.messages 目录视图中存储的用户定义消息，也可以动态建立消息。该消息作为服务器错误消息返回到调用应用程序，或返回到TRY…CATCH 构造的关联CATCH 块。

语法：RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

示例：

A.  抛出自定义异常并捕获异常

BEGIN TRY

    RAISERROR('你的语法出错了', 16, 1);

END TRY

BEGIN CATCH

    SELECT

         ERROR_NUMBER() AS 错误编号,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState,

        ERROR_PROCEDURE() AS ErrorProcedure,

        ERROR_LINE() AS 错误行号,

        ERROR_MESSAGE() AS 错误信息;

END CATCH;

 

 

3.  查询数据表的容量/大小

1)  查询数据库中所有表的容量

CREATE TABLE ##Temp(

    Name VARCHAR(50),

    Rows INT,

    Reserved VARCHAR(50),

    Data VARCHAR(50),

    Index_Size VARCHAR(50),

    Unused VARCHAR(50)

);

INSERT INTO ##Temp(Name,Rows,Reserved,Data,Index_Size,Unused)

exec sp_msforeachTable @Command1="sp_spaceused'?'";

SELECT * FROM ##Temp ORDER BY Data DESC;

2)  查询指定数据表的容量

EXEC sp_spaceused Student;

 

4.  替换非法字符

REPLACE('我是被替换的字符串',char(30),'');

 

5.  存储多国语言

存储多国语言时，字段需要使用nvarchar 类型，并且在写入数据时，指定为"N"，例如：

CREATE TABLE T1(Id int, Name nvarchar(256));

INSERT INTO T1 VALUES(1, N'ﺪ اﻟﻤﺠﻴﺪ ﻋﺰﻳﺰ اﻟﺰﻧﺪاﻧﻲ');

INSERT INTO T1 VALUES(2, N'中国삼성abc');

SELECT * FROM T1;

DROP TABLE T1;

 

6.  数据库存储特殊字符，例如音标：'sə:vis

1)  设置字段的类型为nchar 或者nvarchar 类型

2)  存储SQL 值前加'N'字符，例如：N'''ɔ:rientid,''əʊ-'

 

7.  在查询结果中添加自增列的两种方法

1)  解决办法一：

在SQL Server数据库中表信息会用到Identity关键字来设置自增列。但是当有数据被删除的话，自增列就不连续了。如果想查询出这个表的信息，并添加一列连续自增的ID，可用如下查询语句：

SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) AS Id, * FROM TableName;

2)  解决办法二：

使用关键字IDENTITY创建临时表

SELECT IDENTITY(int,1,1) AS Id, * INTO MyTable FROM TableName

 

8.  创建表语法中ON [PRIMARY]是什么意思

CREATE TABLE [dbo].[Users](

     [ID] [int] IDENTITY (1, 1) NOT NULL,

     [RegName] [char] (20) NOT NULL,

     [Pwd] [char] (10) NOT NULL,

) ON [PRIMARY];
指定存储表的文件组。如果指定filegroup，则表将存储在指定的文件组中，数据库中必须存在该文件组。如果指定DEFAULT，或者根本未指定ON 参数，则表存储在默认文件组中。 
ON [PRIMARY]就是把表存储在主文件组，在sqlserver里数据文件分为主文件和附文件，你可以去安装目录里看下，会发现有MDF，ndf，LDF三种文件，分别是指主文件，附文件，日志文件。

ON {filegroup | DEFAULT} 也可以在 PRIMARY KEY 约束或 UNIQUE 约束中指定。这些约束会创建索引。如果指定 filegroup，则索引将存储在指定的文件组中。如果指定 DEFAULT，则索引将存储在默认文件组中。如果约束中没有指定文件组，则索引将与表存储在同一文件组中。如果 PRIMARY KEY 约束或 UNIQUE 约束创建聚集索引，则表的数据页将与索引存储在同一文件组中。

 

9.  使用触发器注意事项

在对记录同一个UPDATE时，满足条件的会进入deleted和inserted逻辑表。有几条数据这两张表就会有几条数据。

如果在同一张表存在两个或多个触发器，会按照顺序执行。而且，在同一个A触发器中，触发再次触发了A触发器，会等待这次触发完之后，在回到第一次触发的状态.

 

10.  设置主表主键字段修改或删除时，子表外键字段一同修改或删除

示例：

A.  Student主表Id字段修改时，Score子表StudentId字段一同修改

DROP TABLE Score, Student, Grade;

--创建Grade年级表

IF(OBJECT_ID('Grade', 'U') IS NOT NULL)

    DROP TABLE Grade;

GO

CREATE TABLE Grade

(

    Id int PRIMARY KEY NOT NULL,

    Name varchar(20) NOT NULL

);

GO

 

--创建Student学生信息表

IF(OBJECT_ID('Student', 'U') IS NOT NULL)

    DROP TABLE Student;

GO

CREATE TABLE Student

(

    Id int PRIMARY KEY NOT NULL,

    GradeId int CONSTRAINT GradeId REFERENCES Grade(Id),

    Name varchar(20) NOT NULL,

    Age int NOT NULL CHECK (Age > 10),

    Sex bit NOT NULL,

    [Address] varchar(50) DEFAULT '地址不详'

);

GO

 

--创建Score成绩表

IF(OBJECT_ID('Score', 'U') IS NOT NULL)

    DROP TABLE Score;

GO

CREATE TABLE Score

(

    Id int PRIMARY KEY NOT NULL,

    --ON UPDATE CASCADE ON DELETE CASCADE 语句，可在Student主表Id字段修改时，StudentId一同被修改

    StudentId int CONSTRAINT StudentId REFERENCES Student(Id) ON UPDATE CASCADE ON DELETE CASCADE,

    SubjectName varchar(20) NOT NULL,

    Score float NOT NULL

);

GO

 

--插入数据

INSERT INTO Grade VALUES(1, '一年级'),(2, '二年级'),(3, '三年级');

INSERT INTO Student VALUES(1, 1, '张小强', 16, 1, DEFAULT),(2, 2, '王美丽', 17, 0, '北京'),(3, 2, '向北京', 18, 1, '上海');

INSERT INTO Score VALUES(1, 1, '语文', 78.6),(2, 2, '数学', 82),(3, 3, '英语', 76);

--查询数据

SELECT * FROM Grade

SELECT * FROM Student

SELECT * FROM Score

 

UPDATE Student SET Id=4 WHERE ID=3;     --修改主表主键字段，子表关联记录一同被修改

DELETE Student WHERE Id=4;  --删除主表记录，子表关联记录一同被删除

 

11. 存储过程中使用游标，将当前数据库的所有用户存储过程加密

DECLARE @sp_name nvarchar(400), @sp_content nvarchar(2000);

DECLARE @asbegin int;

DECLARE @now datetime;

SELECT @now = GETDATE();

DECLARE sp_cursor CURSOR

FOR SELECT OBJECT_NAME(id) FROM sysobjects WHERE xtype = 'P' AND type = 'P' AND crdate < @now

    AND OBJECTPROPERTY(id, 'IsMSShipped')=0;

 

OPEN sp_cursor

FETCH NEXT FROM sp_cursor INTO @sp_name;

WHILE @@FETCH_STATUS = 0

BEGIN

    SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID(@sp_name) ;

    SELECT @asbegin = PATINDEX ( '%AS' + char(13) + '%', @sp_content) ;

    SELECT @sp_content = SUBSTRING(@sp_content, 1, @asbegin - 1)+' WITH ENCRYPTION AS'

        +SUBSTRING(@sp_content, @asbegin+2, LEN(@sp_content));

    SELECT @sp_name = 'DROP PROCEDURE [' + @sp_name + ']';

    EXEC sp_executesql @sp_name ;

    EXEC sp_executesql @sp_content;

    FETCH NEXT FROM sp_cursor INTO @sp_name;

END

 

CLOSE sp_cursor;

DEALLOCATE sp_cursor;

 

12. 使用游标遍历，将同一数据库服务中的A数据库数据表复制到B数据库中

示例：

A.  将“RM_DB”数据库中的数据表复制到“MyTestDB”数据库（包含数据）

USE RM_DB;

DECLARE @TableName varchar(100), @sql nvarchar(max);

DECLARE @TableCount int=0;

DECLARE cur_TablesNames CURSOR LOCAL SCROLL SCROLL_LOCKS

    FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME;

OPEN cur_TablesNames;

FETCH FIRST FROM cur_TablesNames INTO @TableName;

WHILE(@@FETCH_STATUS = 0)

BEGIN

    SET @TableCount=@TableCount+1;

    SET @sql = 'SELECT * INTO [MyTestDB].[dbo].['+@TableName+'] FROM [RM_DB].[dbo].['+@TableName+']';

    EXEC(@sql);

    PRINT '执行SQL：'+@sql+'，数据表：'+@TableName+'，计数：'+CAST(@TableCount AS varchar(2));

    FETCH NEXT FROM cur_TablesNames INTO @TableName;

END

CLOSE cur_TablesNames;

DEALLOCATE cur_TablesNames;

 

13. 选择性查询字段

CREATE TABLE #Temp1(Id int, Col1 int, Col2 int, Col3 int, Name nvarchar(25))

INSERT #Temp1 VALUES

(1, 1, 2, 3, 'Test1'),

(2, 4, 5, 6, 'Test2'),

(3, 7, 8, 9, 'Test3');

DECLARE @Columns nvarchar(max) = '';

SELECT @Columns += (CASE WHEN LEN(@Columns) > 0 THEN ',' ELSE '' END) + '[' + name + ']' FROM tempdb..SYSCOLUMNS WHERE id = OBJECT_ID('tempdb..#Temp1') AND name LIKE 'Col%';

DECLARE @Sql nvarchar(max) = 'SELECT ' + @Columns + ' FROM #Temp1';

PRINT @Sql;

EXEC(@Sql);

DROP TABLE #Temp1;

 

14. 同义词

SQL Server 同义词一般用于查询其他数据库。