SQLserver基础篇

SQL Server 编程语法

局部变量

  1. 声明: declare @局部变量名 数据类型；如：declare @var1 int， @var2 datetime；

  2. 赋值

    1
    2
    3
    4
    5
    set @变量名 = 表达式；
         
    select @变量名 = 表达式；
         
     select @变量1 = 表达式或列名[  @变量2 = 表达式或列名 ] from 表名 where 条件表达式

显示信息

  1. print语句

    1
    2
    3
    print @变量名 -- 文本形式显示
    print 'hello, world'
    select @变量名 -- 表格形式显示

  2. raiserror语句

    1. 用于SQL Server返回错误信息的同时，返回用户指定的信息，设置了一个系统标记，记录产生的错误。

    2. raiserror (<错误号> | “错误信息”，[严重度])

批处理

  1. 批处理是成组执行的一条或多条SQL指令，被作为整体进行语法分析，优化，编译，和执行。
  2. go语句用于指定批处理语句的==结束处==，单独占用一行。

流程控制语句

begin…end语句

将多条SQL语句封装起来，形成一个语句块，使这些语句作为一个整体执行

begin 语句... end

if…else

  1. 判断语句
1
2
3
4
5
6
if 条件表达式
	语句
else if 条件表达式
	语句
else
	语句
  1. if语句用与关键字exists结合使用，用于检测是否存在满足条件的记录，只要检测到一条记录存在返回真

while循环语句

  1. 设置一个反复执行语句块，直到条件不满足为止
1
2
while 逻辑表达式
	语句
  1. 与break,continue语句结合使用

goto语句

  1. 使用goto语句可以使SQL语句的执行无条件地转移到指定的标号位置
1
2
3
goto lable
	...
lable
  1. 常用于while和if语句中，以跳出循环和分支处理

waitfor语句

  1. 可以在某一个时刻或某一个时间间隔之后执行SQL语句，语句块，存储过程等
1
2
waitfor delay '时间' SQL语句 -- 在某一个时间间隔之后执行SQL语句
waitfro time '时间'  SQL语句 -- 某一个时刻之后执行SQL语句
  1. 时间参数的数据类型为datetime，但不带日期，格式为”hh:mm:ss”

case语句

  1. case语句用于根据多个分支条件确定执行内容。case语句列出一个或多个分支条件，并对每个分支条件给出候选值。
1
2
3
4
5
6
7
select warehouse = case warehouse 
	when 'value1' then '北京'
	when 'value2' then '上海'
	when 'value3' then '广州'
	else '未知'
end
from tableName；

return语句

  1. 使查询或存储过程返回，使用return语句可以立即从当前程序结构中退出，并且return后面的语句不再执行
1
return 整形表达式
  1. 一般情况下，只有存储过程才会使用返回的整型结果，调用存储过程的 语句可以根据return语句返回的值判断下一步应该执行的操作。

视图

定义视图

  1. 语法
1
2
3
create view 视图名 [with encryption] as 
子查询
[with check option]

  1. with check option:表示用视图进行update，insert，delete，select操作时要保证更新，插入，删除，查询的元组要满足视图定义中的谓语条件（即子查询的条件表达式）

  2. with encryption：表示对该视图加密

  3. 子查询可以是任意的select语句，但是不能含有order by子句。

删除视图

  1. 语法
1
drop 视图名1，视图2...;

查询视图

  1. 视图在定义后，用户可以像查询基本表一样查询视图。

  2. 查看视图stuview1的创建信息

EXEC sp_help stuview1;
1
EXEC sp_help 视图名;
  1. 查看视图的定义脚本
1
exec sp_helptext 视图名;

  1. 查看加密视图的定义脚本

    1
    exec sp_helptext 视图名

更新视图

  1. 更新视图同更新基本表一样，更新视图最终是更新基表。
1
update 视图名 set xxx = xxx where xxx;

  1. SQLserver中规定以下视图无法更新

    1. 视图的字段来自聚合函数
    2. 视图的定义时含有group by 子句
    3. 视图在定义时含有distinct短语
    4. 在一个不允许更新的视图上定义的视图也不允许更新。

存储过程

  1. 存储过程是存储在数据库服务器中的一组编译成单个执行计划的SQL语句。
  2. 存储过程在创建时被编译和优化，在调用一次后，相关信息就保存在内存中，下次调用直接执行。
  3. 好处
    1. 运行效率高
    2. 降低了客户机和服务器组件的通信量
    3. 利于集中控制，和维护

创建存储过程

简单存储过程的创建和执行

  1. 创建
1
2
3
create procedure 存储过程名 as
SQL语句 #增删改查
go #遇到go表示代码结束
  1. 执行
1
exec 存储过程名

带输入参数的存储过程的创建和执行

  1. 创建
1
2
3
4
create procedure 存储过程名 @参数1 类型(varchar（10）) [= 'xxx']，@参数2 类型(int)[= 1] 
[ with recompile,encryption]#可从编译和加密
as 
SQL语句
  1. 执行
1
2
execute 存储过程名 参数1值， 参数2值；
execute 存储过程名 @参数1 = ‘xxx’,@参数2 = 'xxx';

带输出参数的存储过程

  1. 创建
1
2
create procedure 存储过程名 @参数1 类型 output
as sql语句；
  1. 执行
1
2
3
declare @变量名 类型
execute 存储过程名 @变量名 output;
print cast(@变量名 as varchar(20))；

修改和删除存储过程

  1. 修改
1
alter procedure 存储过程名 xxxx....
  1. 删除: drop procedure 存储过程名

触发器

基本概念

  1. 触发器是用户定义在关系表上的一类有事件驱动特殊过程，也是一种保存数据完整性的方法。触发器实际上是一类特殊的存储过程,其特殊性表现在一旦定义，无序用户调用，任何对表的操作都由服务器自动激活相应的触发器。
  2. 触发器的主要作用是实现主键和外键不能保证复杂的参照完整性和数据的一致性。

DML触发器的工作原理

  1. inserted表： 对insert操作来说，inserted表中存放的是要插入的数据；而对于update操作来说，inserted表中存放的是要更新的记录(即更新后的新值)；
  2. deleted表： 对delete操作来说，deleted表中存放的是被删除的记录；而对于update操作来书deleted表中存放的是更新前的记录(更新完毕后即被删除)
  3. DML触发器分为after触发器和instead of触发器

after触发器的工作原理

AFTER 触发器是一种类型的触发器，它在触发事件（如INSERT、UPDATE 或 DELETE）之后执行。具体来说，AFTER 触发器会在主要操作完成后立即执行，即在数据已经被插入、更新或删除之后触发。

工作原理如下：

  1. 当对表执行 INSERT、UPDATE 或 DELETE 操作时，这些操作会触发对应的 AFTER 触发器。
  2. 数据库引擎首先执行主要的数据操作，如将新记录插入到表中。
  3. 然后，数据库引擎会检查与该表相关的任何 AFTER 触发器，如果存在匹配的触发器，则执行触发器中定义的逻辑。
  4. 在触发器内部逻辑执行完毕后，主要的数据操作才算真正完成。

总结起来，AFTER 触发器在主要数据操作完成后处理其他相关逻辑，允许开发者对操作结果进行进一步处理、记录日志或实施其他业务规则。使用 AFTER 触发器可以确保触发器中的逻辑不会影响主要数据操作的性能，并且可以有效地控制数据的变化过程。

instead of触发器的工作原理

INSTEAD OF 触发器是另一种类型的触发器，与 AFTER 触发器不同，它在触发事件之前执行。具体来说，INSTEAD OF 触发器会在主要操作执行之前替代原始操作，允许开发者自定义对数据的处理逻辑。

工作原理如下：

  1. 当对表执行 INSERT、UPDATE 或 DELETE 操作时，这些操作会触发关联的 INSTEAD OF 触发器。
  2. 数据库引擎首先检查是否有相应的 INSTEAD OF 触发器，并在有触发器的情况下执行触发器中定义的逻辑。
  3. 在 INSTEAD OF 触发器内部逻辑中，开发者可以根据需求编写自定义的处理代码，例如拒绝操作、修改数据、调用其他存储过程等。
  4. 最终，数据库引擎将采取触发器内部逻辑所定义的行为，而不是直接执行原始的数据库操作。

总结起来，INSTEAD OF 触发器提供了一种在数据操作执行之前干预和修改操作的机制。通过使用这种触发器，开发者可以控制数据库操作的行为，实现更灵活的数据处理和业务规则约束。

创建触发器

1
2
3
4
5
6
7
CREATE TRIGGER trgExampleTrigger
ON TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
 -- Trigger logic goes here
END;
  • CREATE TRIGGER: 创建触发器的关键字，后面跟着触发器的名称。
  • trgExampleTrigger: 触发器的名称，在同一表上需要唯一。
  • ON TableName: 触发器所属的表名，指定触发器与哪个表相关联。
  • AFTER INSERT, UPDATE, DELETE: 触发器的类型和触发时机。在这个例子中，触发器被定义为在插入、更新或删除操���之后触发。可以根据需求选择其中一种或多种触发时机。
  • AS: 触发器逻辑的开始标记。
  • BEGIN: 触发器逻辑的起始点。
  • END;: 触发器逻辑的结束标记。

在创建触发器时，需要指定触发器的名称、所属表、触发时机以及触发器内部的逻辑。触发器的逻辑部分可以包含对数据的处理、约束实施、日志记录等操作。触发器的作用是监听表上的指定事件，并在事件发生时执行特定的逻辑。

insert,update,delete触发器

INSERT 触发器

INSERT 触发器会在向表中插入新记录时触发。可以用于实现自动填充字段、数据验证等功能。

1
2
3
4
5
6
7
CREATE TRIGGER trgInsertExample
ON TableName
AFTER INSERT
AS
BEGIN
 -- Trigger logic for INSERT goes here
END;
DELETE 触发器

DELETE 触发器会在从表中删除记录时触发。可以用于记录被删除的数据、级联删除相关数据等操作。

1
2
3
4
5
6
7
CREATE TRIGGER trgDeleteExample
ON TableName
AFTER DELETE
AS
BEGIN
 -- Trigger logic for DELETE goes here
END;
UPDATE 触发器

UPDATE 触发器会在更新表中的记录时触发。可以用于记录变更历史、检查数据一致性等功能。

1
2
3
4
5
6
7
CREATE TRIGGER trgUpdateExample
ON TableName
AFTER UPDATE
AS
BEGIN
 -- Trigger logic for UPDATE goes here
END;

在实际应用中，开发者可以根据需要编写逻辑来执行相应的操作。触发器能够为数据库操作提供灵活的扩展和控制机制，帮助确保数据的完整性和一致性。

INSTEAD OF 触发器

INSTEAD OF 触发器是一种特殊的触发器，它会在执行 INSERT、UPDATE 或 DELETE 操作之前触发。与 AFTER 触发器不同，INSTEAD OF 触发器可以用于在操作被执行之前干预和处理数据，而不是在操作完成后。

INSTEAD OF 触发器通常用于对视图或具有特殊约束条件的表进行操作，以实现自定义的数据处理逻辑。

以下是一个简单的 INSTEAD OF 触发器示例：

1
2
3
4
5
6
7
CREATE TRIGGER trgInsteadOfExample
ON ViewName
INSTEAD OF INSERT
AS
BEGIN
 -- Trigger logic for INSTEAD OF INSERT goes here
END;

在上述代码中：

  • CREATE TRIGGER: 创建触发器的关键字。
  • trgInsteadOfExample: 触发器的名称。
  • ON ViewName: 触发器所属的视图名称。
  • INSTEAD OF INSERT: 触发器类型为 INSTEAD OF INSERT，表示在插入操作执行之前触发触发器。
  • AS: 触发器逻辑的开始标记。
  • BEGIN: 触发器逻辑的起始点。
  • END;: 触发器逻辑的结束标记。

INSTEAD OF 触发器允许开发者在插入、更新或删除操作执行之前进行自定义处理，例如拒绝操作、修改数据、调用其他存储过程等。这些触发器提供了灵活的机制来实现复杂的业务逻辑和数据处理需求。

数据库的完整性

约束

  1. 约束通过限制列中的数据，行中的数据和表之间的数据来保证数据完整性

  2. 在表创建完成后再添加约束

1
2
-- 添加主键约束
alter table 表名 add constraint 约束名 primary key(列名)
1
2
-- 添加用于自定义的check约束
alter table 表名 add constraint 约束名 check (表达式)
1
2
-- 添加默认值约束
alter table 表名 add constraint 约束名 default(值) for 列名
1
2
-- 删除约束
alter table 表名 drop constraint 约束名
1
2
-- 查看约束名
select name ，type_desc from sys.objects where parent_object_id = object_id('表名')

默认值

  1. 默认是一种数据库对象，可以绑定到表的一列或多列上，也可以绑定到用于自定义的数据类型上，其作用类似于default约束。

  2. 默认约束是create table 或alter table语句中定义后，被嵌入到定义的表结构中，也就是说，在删除表的时候默认约束也将随之被删除。

1
2
3
-- 创建默认
create default 默认对象名 as 常量表达式. 
-- 常量表达式可以是常量，内置函数或数学表达式，表达式不能包含任何列名或其他数据库对象
1
2
3
-- 绑定默认值
sp_bindefault 默认对象名, 'objname';
-- objname为指定要绑定的表和列或用户定义的数据类型。
1
2
-- 查看默认
exec sp_helptext 默认对象名;
1
2
3
4
-- 解除默认
sp_unbindefault 'objname';
-- 删除默认, 删除之前先解除默认，否则报错
drop default 默认对象名;

规则

  1. 规则是数据库对存储在表中的列或用户自定义数据类型中的值的规定和限制，时单独存储的独立的数据库对象。

  2. 规则与check约束的不同之处

    1. check约束是在使用create table语句建表时指定的，而规则是独立于表的数据库对象，通过与指定表或数据类型来实现完整性约束
    2. 在一列上只能使用一个规则，但可以使用多个check约束
    3. 规则可以应用多个列还可以应用于用户自定义的数据类型，而check约束只能应用于他定义的列。

  3. 创建规则

1
2
create rules 规则名 as condition_expreession
# condition_expreession是规则的定义，他可以是用于where条件子句中任何表达式，以字符@开头
  1. 查看规则
1
sp_help 规则名
  1. 绑定和解除规则
1
2
3
4
# 绑定
sp_bindrule 规则名 绑定的对象名 [, 自定义类型]
# 解除
sp_unbindrlue 绑定的对象名 [, 自定义类型]
  1. 删除规则
1
2
drop rule {规则名} [, ...n]
# 在删除一个规则前必须先将其绑定的对象解除绑定。

索引

  1. 创建索引
1
2
3
4
create [unique, clustered, nonclustered] index 索引名 on 表名或视图名(列名)
# clustered： 聚集索引
# unique：唯一索引
# nonclustered： 非聚集索引
  1. 查看索引
1
exec sp_helpindex 表名
  1. 删除索引
1
2
drop index 表名.索引名
# 删除多个索引，索引之间用逗号隔开

数据库安全

用户管理和角色管理

SQL Server 的数据库安全涉及用户管理和角色管理，这些是确保数据库安全性的重要组成部分。以下是 SQL Server 中用户管理和角色管理的详细说明：

用户管理：

  1. 创建用户：创建用户以允许他们访问数据库。
1
2
3
4
5
-- 创建 Windows 身份验证用户
CREATE LOGIN [Domain\UserName] FROM WINDOWS;

-- 创建 SQL Server 身份验证用户
CREATE LOGIN UserName WITH PASSWORD = 'StrongPassword';
  1. 将用户分配给数据库：创建用户并将其分配给数据库。
1
2
3
4
5
-- 将 Windows 身份验证用户分配给数据库
CREATE USER UserName FOR LOGIN [Domain\UserName];

-- 将 SQL Server 身份验证用户分配给数据库
CREATE USER UserName FOR LOGIN UserName;
  1. 授权：授予用户对数据库对象的权限。
1
2
-- 授予用户权限
GRANT SELECT, INSERT, UPDATE, DELETE ON TableName TO UserName;
  1. 删除用户：在不需要用户访问数据库时，可以删除他们。
1
2
-- 删除用户
DROP USER UserName;

角色管理：

  1. 创建角色：创建角色以便将权限组织成逻辑单元。
1
2
-- 创建角色
CREATE ROLE RoleName;
  1. 将用户添加到角色：将用户添加到角色以便批量管理权限。
1
2
-- 将用户添加到角色
ALTER ROLE RoleName ADD MEMBER UserName;
  1. 授权：向角色授予对数据库对象的权限。
1
2
-- 授予权限给角色
GRANT SELECT, INSERT, UPDATE, DELETE ON TableName TO RoleName;
  1. 删除角色：在不再需要角色时，可以删除它们。
1
2
-- 删除角色
DROP ROLE RoleName;

固定角色

SQL Server 的数据库安全中，固定角色是预定义的角色，拥有特定的权限和功能，用于简化权限管理。以下是 SQL Server 中常见的固定角色及其说明：

  1. sysadmin：sysadmin 角色是 SQL Server 中最高权限的角色，拥有对所有数据库的完全控制权限。成员可以执行任何操作，包括配置服务器和数据库引擎、管理用户权限以及维护数据库。

  2. serveradmin：serveradmin 角色允许成员配置服务器级别的设置，但不能访问或修改用户数据库。

  3. securityadmin：securityadmin 角色允许成员管理登录、服务器级别的安全性设置和配置审核。

  4. processadmin：processadmin 角色允许成员执行活动监视和进程监视任务。

  5. setupadmin：setupadmin 角色允许成员管理服务器上的 SQL Server 安装。

  6. bulkadmin：bulkadmin 角色允许成员运行 BULK INSERT 语句。

  7. diskadmin：diskadmin 角色允许成员管理磁盘文件。

  8. dbcreator：dbcreator 角色允许成员创建、更改、删除数据库。

  9. public：public 角色是每个数据库中的默认角色，所有用户都是其成员。它是其他角色的默认父级，所有用户在数据库中都具有 public 角色的权限。

这些固定角色在 SQL Server 中起着重要的作用，它们简化了权限管理并提供了一种安全的默认配置。根据需要，您可以将用户添加到这些角色中，以授予他们相应的权限。

权限管理

SQL Server 的数据库安全性通过权限管理来确保数据的安全性和完整性。以下是 SQL Server 中的权限管理详细说明：

  1. 权限类型：SQL Server 中有多种权限类型，包括但不限于 SELECT、INSERT、UPDATE、DELETE 等。每种权限类型都控制用户对数据库对象的不同操作。

  2. 授权：授权是指授予用户或角色特定的权限，以允许他们执行特定的操作。

1
2
3
4
5
-- 授予用户对表的 SELECT 权限
GRANT SELECT ON TableName TO UserName;

-- 授予角色对表的 INSERT、UPDATE 权限
GRANT INSERT, UPDATE ON TableName TO RoleName;
  1. 回收权限：当不再需要某个用户或角色拥有特定权限时，可以撤销授权。
1
2
3
4
5
-- 回收用户对表的 SELECT 权限
REVOKE SELECT ON TableName FROM UserName;

-- 回收角色对表的 INSERT、UPDATE 权限
REVOKE INSERT, UPDATE ON TableName FROM RoleName;
  1. 权限检查：在执行敏感操作之前，可以使用权限检查来确保用户拥有执行该操作所需的权限。
1
2
3
4
5
6
7
8
9
10
-- 检查用户对表的 DELETE 权限
IF HAS_PERMS_BY_NAME('TableName', 'OBJECT', 'DELETE') = 1
BEGIN
    -- 执行删除操作
    DELETE FROM TableName WHERE Condition;
END
ELSE
BEGIN
    PRINT '您没有执行此操作的权限。';
END
  1. 视图权限：可以控制用户对视图的访问权限，以保护敏感数据。
1
2
3
4
5
-- 创建视图
CREATE VIEW ViewName AS SELECT * FROM TableName WHERE Condition;

-- 授予用户对视图的 SELECT 权限
GRANT SELECT ON ViewName TO UserName;
  1. 存储过程权限：存储过程也可以拥有自己的权限，控制用户对其执行的访问。
1
2
3
4
5
6
7
8
-- 创建存储过程
CREATE PROCEDURE ProcedureName AS
BEGIN
    -- 存储过程逻辑
END;

-- 授予用户对存储过程的 EXECUTE 权限
GRANT EXECUTE ON ProcedureName TO UserName;

通过以上权限管理措施，可以精确地控制用户对数据库对象的访问和操作，从而提高数据库的安全性和完整性。

架构

SQL Server 数据库安全的架构是一个综合性的系统，涵盖了多个方面，包括身份验证、授权、访问控制、加密和审计等。以下是 SQL Server 数据库安全架构的详细讲解：

1. 身份验证（Authentication）：

在 SQL Server 中，有两种常见的身份验证方式：

  • Windows 身份验证：通过 Windows 操作系统认证用户的身份。
  • SQL Server 身份验证：用户需要提供 SQL Server 指定的用户名和密码来验证身份。

2. 授权（Authorization）：

一旦用户成功登录，就需要授权来确定其对数据库对象的访问权限。授权通过角色、权限和权限层次结构来实现。

  • 角色：角色是权限的集合，可以将用户分配到角色中，从而简化权限管理。
  • 权限：权限确定了用户或角色可以执行的操作，如 SELECT、INSERT、UPDATE、DELETE 等。
  • 权限层次结构：SQL Server 中存在数据库级别、模式级别和对象级别的权限控制，可以细化到单个对象。

3. 访问控制（Access Control）：

访问控制确保只有经过授权的用户才能访问数据库对象。这包括：

  • 登录控制：限制谁可以登录 SQL Server。
  • 对象权限：控制用户对数据库对象的操作权限。
  • 行级别安全：通过行级别安全策略，限制用户对特定行数据的访问。

4. 加密（Encryption）：

加密用于保护数据库中的敏感数据，防止未经授权的访问。SQL Server 提供了多种加密技术，包括：

  • 列级加密：对特定列的数据进行加密，确保只有授权用户可以解密。
  • 透明数据加密（TDE）：对整个数据库进行加密，数据在磁盘上存储时是加密的，只有在内存中解密后才能访问。

5. 审计（Auditing）：

审计用于跟踪和记录数据库操作，以便监视对数据库的访问并检测潜在的安全问题。SQL Server 提供了内置的审计功能，可以记录登录事件、权限更改、数据修改等操作。

6. 数据库安全策略（Database Security Policy）：

数据库安全策略定义了数据库中敏感数据的保护规则和标准。这些策略可以包括密码策略、访问限制、数据分类等，帮助组织确保符合法规和标准要求。

SQL Server 数据库安全架构是一个综合的安全系统，需要综合考虑各个方面来确保数据库的安全性和完整性。通过合理配置和管理，可以有效地保护数据库免受未经授权的访问和恶意攻击。