本文共 18333 字，大约阅读时间需要 61 分钟。

数据库总知识框架图：

（结构：创建存储数据的对象）	—————>	（目的：进行操作操作）
数据库(创建，修改，删除)		查询
——————————		游标
表格(创建，修改，删除)		视图与索引
		——————————
		存储过程
		用户自定义函数
		触发器
		——————————
		数据库并发控制（事务）
		数据库安全管理
		数据库备份与还原

————————————————————

1.1创建数据库

create database 数据库名

on ( name=主数据文件名, filename=‘主数据文件物理路径名’, size=默认数据库大小, maxsize=最大容量, filegrowth=增长量 ) log on ( name=日志文件名, filename=‘日志文件物理路径名’, size=默认数据库大小, maxsize=最大容量, filegrowth=增长量 )

eg: 用SQL 编程新建一个名为student的数据库，包括主文件、日志文件，并定义其5个参数（name, filename, size, maxsize, filegrowth）

create database student	--创建数据库onprimary	--主文件建立(		name=pstudentPQL,	--定义主文件逻辑名		filename='E:\pstudentPQL.mdf',	--定义主文件物理名		size=10MB,	--主文件的初始容量		maxsize=20Mb,	--主文件的最大容量		filegrowth=10%	--增量大小		)log on	--日志文件建立(		name=lstudentPQL,	--定义日志文件逻辑名		filename='E:\lstudentPQL.mdf',	--定义日志文件物理名		size=2MB,	--日志文件的初始容量		maxsize=unlimited,	--日志文件的最大容量		filegrowth=2MB	--增量大小		)go

1.1.1查看数据库信息

eg:用SQL 编程查看student数据库信息

sp_helpdb student	--查看数据库student属性go

1.2修改数据库

1.2.1修改数据库名字+还原数据库名字

eg：用SQL 其中一个命令将student数据库更改名字为stu

alter database student --指定要更改的数据库modify name = stu	--更改数据库名称go

eg:用SQL 另一个命令将stu数据库还原为student

sp_renamedb'stu','student'	--将名为stu的数据库更名为studentGo

1.2.2增加数据库文件

altetr datebase 数据库名

add 主数据文件名/日志文件名 ( name=主数据文件名, filename=‘主数据文件物理路径名’, size=默认数据库大小, maxsize=最大容量, filegrowth=增长量 )

1.2.3增加数据库空间

altetr datebase 数据库名

modify file ( name=主数据文件名, size=默认数据库大小, maxsize=最大容量 )

1.3删除数据库

altetr datebase 数据库名

remove file 主数据文件名 创建数据表 create table 表名称 ( 列名1 数据类型1 约束1,–（主键的约束条件比较多） 列名2 数据类型2 约束2, 列名3 数据类型3 约束3 )

eg:用SQL 编程删除student数据库(drop)

drop database student	--删除数据库go

约束条件主要有：

非空约束: not null 主键约束: primary key 外键约束: foreign key （外键表引用的id） references 主表名(主表主键) 检查约束: check(条件) 标示列,自动增长约束: identity(标识种子,增长量) 默认: default 默认值

————————————————————

2.1创建表

creat table 表名

（ 列名1 格式 列级完整约束 列名2 格式 列级完整约束 列名3 格式 列级完整约束 … … 表级完整约束 … … ） eg:

use student	--调用数据库gocreate table 学生信息_蒲强林	--创建表(	学号 char(7) primary key,	姓名 char(20) not null,	性别 char(2),	年龄 int,	所在系 char(15)	)gouse student --调用数据库go--插入数据insert 学生信息 values ('9056215','赵燕','女',20,'计算机系')insert 学生信息values ('9056219','李勇','男',21,'计算机系')insert 学生信息values ('9057123','张辉','男',19,'管理系')insert 学生信息values ('9057436','王鹏','男',17,'管理系')insert 学生信息values ('9056213','李静','女',21,'计算机系')go

列级完整约束：

（1）默认值约束 （2）空值/非空值约束 （3）主键约束 （4)外键约束 （5）唯一性约束 （6）检查约束 表级完整约束： （1）unique （2）primary key （3）foreign key （4）check

2.2修改表

alter表名称

–增加列：add 列名 数据类型 约束 eg:在“学生信息”表中添加 “电话”、“电子邮箱”字段，类型分别为定长字符（11长度）、可变字符型（40长度），允许为空

use student	--调用数据库goalter table 学生信息	--添加属性add 电话 char(11)alter table 学生信息	--添加属性add 电子邮箱 varchar(40)go

–修改列：alter column 列名 数据类型/约束

eg1:将“性别”字段的类型改为不允许为空

use studentgo--修改字段alter table 学生信息alter column 性别 char(2) not nullgo

eg2:将“学生信息”表中的 “电子邮箱”字段名改为“email地址”

use studentgoexec sp_rename '学生信息.电子邮箱','email地址','column'go

–删除列：drop column列名

eg:删除“学生信息”表中的 “email地址”字段

use studentgoalter table 学生信息drop column email地址go

删除数据表

drop table 表名

use studentgodrop table 学生信息go

2.3表中数据管理

1.插入表数据

insert into 表名（列名1，列名2，…） valus (‘列1值’，’列2值’，…)

2.修改表数据

update 表名 set 列1=’值1’， 列2=’值2’…… where 逻辑表达式

3删除表数据

delete from 表名/视图名 where 逻辑表达式

————————————————————

3.数据查询

3.1基本查询

select 目标列表表达式

from 表名 或 视图名 where 条件表达式

eg:在“课程”表中查询所有学生的“课程号”和“课程名称”。

use studentgoselect distinct 课程号,课程名称from 课程go

注意：目标列表表达式可以是：（属性列，字符串常量，算数表达式，函数）

distinct 目标属性 ：去掉重复行

3.2 带有where字句的查询

（1）比较大小

where A 比较符号 B （2）确定范围 范围内：where between …and … 范围外：where not between …and …

eg:在“课程号”表中查询“学分”在4-6之间的课程的“课程号”和“课程名称”。

use studentgoselect 课程号,课程名称from 课程where 学分>=4 and 学分<=6go

（3）确定集合

指定集合内：where 列名 in（‘A’,‘B’,‘C’） 指定集合外：where 列名 not in（‘A’,‘B’,‘C’） （4）字符匹配 匹配： like 匹配串 不匹配：not like 匹配串

注意：匹配符可以结合% -使用

%	表示任意的，表示0到无穷大的长度
-	表示单个字符
like	匹配的意思（后边有匹配符号只能用like）
is	匹配的意思（为空只能用is，不能用=）
=	等于
><	不等于的意思

eg:在“课程”表中查询所有“课程名称”中包含“数据库”字样的课程的全部信息

use studentgoselect *from 课程where 课程名称 like '%数据库%'

（5）涉及空值的查询

use studentgoselect 课程名称,学分from 课程where 先修课程 is nullgo

（6）多重条件的查询

and 并且 or 或

3.2 带有group by 字句的查询（分组功能）

（1）简单分组查询

select 目标列表表达式 from 表名 where 条件表达式 group by 列名

（2）带having 字句的分组查询

select 目标列表表达式 from 表名 group by 列名 having 条件表达式

eg:查询至少选修了2门课程以上的学生的学号。

use studentgoselect distinct 学号from 学生成绩group by 学号having count(*)>=2 go

3.3 带有order by 子句的查询（排序功能）

select 目标列表表达式

(into 新表名) from 表名 或 视图名 where 条件表达式 order by 列名 asc 或 desc

eg:在“课程”表中查询所有“课程名称”中包含“数据库”字样的课程的全部信息，将查询结果按“课程名称”降序排列。

use studentgoselect *from 课程where 课程名称 like '%数据库%'order by 课程名称 DESCgo

3.4 多表查询

连接查询：

（1）内连接 select 列名 from 表1 inner join 表2 on 连接条件表达式

（2）外连接（左外连接+右外连接）

左外连接： select 列名 from 表1 left join 表2 on 连接条件表达式

右外连接：

select 列名 from 表1 right join 表2 on 连接条件表达式 （3）交叉连接(笛卡尔积) select 列名 from 表1 cross join 表2 on 连接条件表达式

3.5 子查询（嵌套查询功能）

集合比较运算符：

all	全部都满足
any	满足任何一个
between	在某个范围内
exists	查询结果包含一些行（结果不为空）
in	操作数等于列表中的一个
not	取值反
some	有一些满足

子查询：将第一步查询嵌入到第二步查询的条件中（嵌套查询）

（1）无关子查询

（先执行子查询语句，得到的结果传递给父查询语句使用） use 数据库名 select 列名 from 表格 where 列名 比较运算符（select 列名 from 表格 where 列名）

eg:查询其它系中比“管理系”的所有学生年龄都小的学生的“姓名”、“年龄”和“性别”。

use studentgoselect 姓名,性别,年龄from 学生信息where 所在系 != '管理系' and 年龄

eg:查询“赵燕”同学选修的所有课程“课程名称”、“学分”、“分数”，对结果按照“分数”升序排列。

use studentgoselect 课程名称,学分,分数from 学生成绩,课程where 学生成绩.课程号 = 课程.课程号 and 学号 in(														select 学号														from 学生信息			where 姓名 = '赵燕')order by 分数go

（2）相关子查询

（子查询的执行依赖于父查询，多数情况下是因为子查询的where 子句引用了父查询的表） （3）表数据维护的子查询 插入子查询结果 带子查询的删除语句

3.5联合查询

（1）集合并运算(会自动去除重复)

… … union … …

（2）集合交运算

… … intersect … …

（3）集合差运算(前—后的意思)

… … except … …

3.6 输出结果选项

（1）输出前n行

use 数据库名 select top n 列名 from 表格 where 条件语句 （2）百分比输出 use 数据库名 select top n percent 列名 from 表格 where 条件语句 （3）查询结果输出到新建表中 use 数据库名 select 列名 into 新表格 from 原表格 where 条件语句

3.7输出结果插入到另一个表中

select 目标列表表达式 into 新表名

from 表名 where 条件表达式

eg:将“学生信息”表中“计算机系”的学生的全部信息插入到“计算机系学生信息”表中。

use studentgoselect *into 计算机系学生信息from 学生信息where  所在系 = '计算机系'go

eg:创建和“学生信息”表的表结构相同的“管理系学生信息”表，但该表中不包含任何记录。之后用带子查询的INSERT将“管理系”学生的全部信息插入到“管理系学生信息”表中。

use studentgoselect * into 管理系学生信息from 学生信息where 学号 is null gouse student	--调用数据库goinsert into 管理系学生信息select * from 学生信息where 所在系 = '管理系'go

———————————————————

4 游标

4.1 游标概念

游标是一种能从包括多个元素的集合中每次读取一个元素的机制

（可以把游标看作一个指针，把select查询结果集看作一张二维表格，用游标指向表格的任意一行，允许用户对该行数据进行处理） 游标分类：Transact-SQL游标，API服务器游标，客户游标 游标示意图：

表A		表B
游标指向位置——>第一行	————>	第一行
第二行		第二行
…		…
第n行		第n行

4.2 管理游标

（1）声明游标

declare 游标名称 scroll curson for secect … from 表名 where… （2）打开游标 open 游标名称 （3）读取游标（先将游标指向表的数据读出来，然后将数据存到指定的变量中） fetch 读取数据的位置 form 游标名称 into 变量名1,变量名2,变量名3,…

指定游标的位置：

游标位置	解释
first	读取游标中的第一行数据
last	读取游标中的最后一行数据
prior	读取游标当前位置的上一行数据
next	读取游标当前位置的下一行数据
absulute n	读取游标的第n行数据
pelative n	读取游标当前位置之前（n为正）或之后（n为负）的的n行数据

（4）关闭游标

close 游标名称 （5）释放游标 deallocate 游标名称 (释放游标) （6）游标系统变量与函数

@@cursor_rows返回值	说明
n	游标已被完全填充,返回n是游标的总行数
0	游标未打开
-1	游标为动态游标。
-m	游标被异步填充，返回的-m是键集中的当前行数

fetch读取一行数据的状态：

@@fetch_status返回值	说明
0	fetch语句读取一行数据成功
-1	fetch语句读取一行数据失败或者此元组不在结果集中
-2	被读取元组不存在

cursor-status确定是否游标或者结果集

@@cursor-status返回值	说明
1	结果集至少有一行
0	结果集为空
-1	游标被关闭
-2	游标不可用
-3	指定游标不存在

– current of 是表示当游标前指针所指的行

close 游标名 deallocate 游标名

删除当前数据行：

declare from 表名 where current of 游标名 – current of 是表示当游标前指针所指的行 close 游标名 deallocate 游标名

use studentgo --声明游标declare s_cur scroll cursorfor select * from sfor update of sno,snamego--打开游标open s_cur if @@ error=0print '共有'+convert(nchar(3),@@cursor_rows)+'行学生记录'--读取游标fetch next from s_cur--游标当前位置下一行declare @sno char(10)declare @sname char(10)declare @sex char(2)declare @birthdate char(5)declare @college char(10)fetch absolute 10 from s_curinto @sno,@sname,@birthdate,@sex,@collegeprint '第十行数据为：学号'+@sno+'姓名'+@sname+'生日'+@birthdate+'性别'+@sex+'学院'+@collegeupdate Sset sno='S111',sname='kaka'where current of s_curclose s_curdeallocate s_curgo

———————————————————

5视图与索引

5.1创建视图

creat view 视图名 （列名1，列名2…)

as select 查询子句 whith check = option

eg:建立数学学院（MA）和学生视图（V_MA），并进行修改和插入操作时仍需要保证该视图只有数学学院的学生

creat view v_ma asselect sno,sname,birthdatefrom swhere college='ma'whith check = option --此语句表示对该视图操作时，会验证条件

5.2修改视图

alter view 视图名 （列名1，列名2…)

as select 查询子句 whith check = option

eg:修改视图v_ma,并要求该视图只查询数学学院（ma）的男学生

creat view v_ma asselect sno,sname,birthdatefrom swhere college='ma' and sex='男'whith check = option --此语句表示对该视图操作时，会验证条件

5.3删除视图

drop view 视图名

5.4使用视图

5.5创建索引

作用：加快检索表中数据的方法，类似于书的目录查找

5.6管理索引

———————————————————

6 存储过程

6.1创建多种种存储过程

（1）不带参数的存储过程

eg: 使用create procedure 命令建立名为StudentCourseScore存储过程，该存储过程用于查询“学生选课名称、学分及分数”视图中的数据。写出程序代码。

--（1）use studentgoif exists (select name from sysobjects where name='pr_StudentCourseScore' and type = 'P')drop procedure pr_StudentCourseScoregocreate procedure pr_StudentCourseScoreasselect 课程.课程名称,课程.学分, 学生成绩.分数from 学生成绩,课程where 学生成绩.课程号 = 课程.课程号goexec pr_StudentCourseScorego

（2）带输入参数的存储过程

eg:使用带参数的存储过程StudentAge，根据制定的“年龄”，找出与给定“年龄”相等的学生的“学号”和“姓名”。写出程序代码。

use studentgoif exists (select name from sysobjects where name='pr_StudentAge' and type = 'P' )drop procedure pr_StudentAgegocreate procedure pr_StudentAge @age intasselect 学号,姓名from 学生信息where 年龄=@agegoexec pr_StudentAge 19go

（3）带输出参数的存储过程

eg:在上题中设置@count参数，作为输出参数，返回和给定“年龄”相同的学生的总人数。写出程序代码。

use studentgoif exists (select name from sysobjects where name='pr_StudentAge' and type = 'P' )drop procedure pr_StudentAgegocreate procedure pr_StudentAge @age int,@count int outputasselect @count = count(*)from 学生信息where 年龄=@agegodeclare @Scount int exec pr_StudentAge 20,@Scount output --执行时要给实参去接收形参print '年龄为19的学生总数为：'+cast(@Scount as char(4)) --cast函数将int型Scount转化为char型 ，并返回值go

（4）加密存储过程

（5）嵌套使用存储过程（最多32层）

6.2存储过程 属性查看，修改，删除

（1）查看存储过程信息

exec sp_helptext 存储过程名字 （2）修改存储过程 use 数据库名字 go alter procedure 存储过程名字 … 重写一遍存储过程 … （3）删除存储过程 drop proceduer 存储过程名字

6.3利用存储过程对数据 插入，修改，删除

（1）插入

eg:使用存储过程实现向“学生信息”表插入一条记录的操作。

use studentgoif exists (select name from sysobjects where name = 'pr_InsStuInfo' and type = 'P')drop procedure pr_InsStuInfogocreate procedure pr_InsStuInfo @学号 char(7),@姓名 char(20),@性别 char(2),@年龄 int,@所在系 char(15),@flag int outputasbegin		set @flag = 1		insert into 学生信息 values(@学号,@姓名,@性别,@年龄,@所在系)		if @@ERROR<>0		set @flag = 0endreturngodeclare @flag intset @flag = 0exec pr_InsStuInfo '008','张三','男',21,'计算机',@flag outputif @flag = 1print '学生信息添加成功！'elseprint '学生信息添加失败！'go

（2）修改

eg:在“学生信息”表中，修改和所给的“学号”相同的记录，用存储过程实现。

use studentgoif exists(select name from sysobjects 		where name = 'pr_UpdStuInfo' and type = 'P')drop procedure pr_UpdStuInfogocreate procedure pr_UpdStuInfo @学号 char(7),@姓名 char(20),@性别 char(2),@年龄 int,@所在系 char(15),@flag int outputasset @flag = 1if exists (select * from 学生信息 where 学号 = @学号)update 学生信息set 姓名=@姓名,性别=@性别,年龄=@年龄,所在系=@所在系where 学号 = @学号elseset @flag = 0returngodeclare @flag intset @flag = 0exec pr_UpdStuInfo '008','张三','男',21,'计算机',@flag outputif @flag = 1print '学生信息修改成功！'elseprint '学生信息修改失败！’go

（3）删除

eg:在“学生信息”表中，删除和所给的“学号”相同的记录，用存储过程实现。

use studentgoif exists (select name from sysobjects				where name ='pr_DelStuInfo' and type = 'P')drop procedure pr_DelStuInfogocreate procedure pr_DelStuInfo @学号 char(7),@flag int outputasbeginset @flag = 1if exists (select * from 学生信息 where 学号 = @学号)delete 学生信息 where 学号=@学号elseset @flag = 0endreturngodeclare @flag int,@学号 char(7)set @学号 = '008'set @flag = 0exec pr_DelStuInfo @学号,@flag outputif @flag = 1print '该学生信息删除成功！'elseprint '该学生不存在，无法删除！'go

——————————————————

7用户自定义函数

7.1 用户自定义函数和存储过程比较

比较项	存储过程	用户自定义函数
参数	允许有多个输入输出参数	允许有0到多个输入参数，不允许有多个输出参数（且参数要用用括号括起来）
返回值	可以没有返回值	有且只有一个返回值
调用	使用excute调用	在表达式或者赋值语句中调用

7.2 创建用户自定义函数

（1）创建标量型函数

eg:1）在student数据库中，创建标量函数，统计“课程”表中总共有多少条门课程，写出程序代码。(要求：在建立函数之前，为排除重名函数，先判断要创建的函数是否存在，如果存在则先删除。)

use studentgoif exists (select name from sysobjects where name = 'Ccount' and type = 'FN')drop function dbo.Ccountgocreate function dbo.Ccount()returns intasbegin		declare @num int		set @num = 0		select @num = count(*)		from 学生课程		return @numendgo-- 调用函数查询use studentgodeclare @num intset @num = 0set @num = dbo.Ccount()print '课程表中总共有：'+cast(@num as char(1)) + '门课程'go

（2）创建内联表值函数（没有函数主体，表是单个select语句的结果，直接return select语句的值）

eg:在student数据库中，创建内嵌表值函数，该函数给出制定学生所选修课程记录，即“学号”作为输入参数，写出程序代码。

if exists (select name from sysobjects where name = 'Cchoose' and type = 'FN')drop function dbo.Cchoosegocreate function dbo.Cchoose(@Cname as nvarchar(20))returns tableasreturn (select a.学号		from 学生成绩 a,学生课程 b		where  a.课程号 = b.课程号 and b.课程名称 = @Cname)go-- 调用函数查询选修C语言情况use studentgodeclare @Cname nvarchar(20)set @Cname = 'C语言'select *from dbo.Cchoose(@Cname)go

（3）创建多语句表值函数（返回一个表）

if exists (select name from sysobjects where name = 'Cchoose1' and type = 'FN')drop function dbo.Cchoose1gocreate function dbo.Cchoose1 (@Cname as nvarchar(20))returns @Cchoosetable table (学号 char(7))asbegin		insert @Cchoosetable		select a.学号		from 学生成绩 a,学生课程		where  a.课程号 = b.课程号 and b.课程名称 = @Cname 		returnendgo-- 调用函数查询学修C语言情况use studentgodeclare @Cname nvarchar(20)set @Cname = 'C语言'select *from dbo.Cchoose1(@Cname)go

7.3 管理用户自定义函数

（1）查看用户自定义函数

exec sp_helptext 用户定义函数名称 exec sp_help 用户定义函数名称 （2）修改用户定义函数 alter function 用户自定义函数名 … 重写一遍函数的实现 … （3）删除用户定义的函数 drop function 用户自定义的函数名

————————————————————

8触发器

8.1触发器的种类

触发器的种类：2（事前，事后）*3（添加，更新，删除）=6种

事前：for(类似于before)/instead of（表存涉及外键instead of）

事后：after

8.2创建触发器

（1）INSERT触发器

eg:使用create trigger命令建立名为“成绩tri_insert”的触发器，该触发器用于判断要插入的记录，“分数”一项必须是0-100之间的数字，否则不允许插入数据，写出程序代码。

use student if exists(select * from sysobjects where name='成绩tri_insert' and type='TR')	drop trigger 成绩tri_insertgocreate trigger 成绩tri_inserton 学生成绩for insertas 	if((select 分数 from inserted)between 0 and 100)		print '插入成功'	else 	begin		print'分数格式错误，插入失败'		rollback	endgoinsert into 学生成绩 values('123123','007',123)go

（2）UPDATE触发器

use studentgo create trigger s_i_uon sforinsert,updateasprint '对 s表进行了数据的插入和修改'go--触发过程use chaxungo update sset birthdate='1996-12-24'where sno ='S2'go

（3）DEELETE 触发器

eg:删除表S表学号为S7的元组前，必须先删除SC表中有外键联系的元组

use studentgo creat trigger s_delon sinstead of delete as delete from sc where sc.sno in							(select sno from deleted)return

eg:不允许删除修改数据库的触发器

--不允许删除修改数据库的触发器use studentgo create trigger ddl_tabletriggeron databasefor drop_table,alter_tableasprint '对不起，不能对数据表进行删除或修改操作，请联系DBA'rollbackgo--测试删除表use chaxungo drop table sc--不允许创建数据库的触发器use studentgo if exists(select * from sys.server_triggers	where name ='ddl-trig_dtabase')	drop trigger ddl_trig_dtabase	on all server ;	go	create trigger trig_dtabase	on all server 	for create_database	as 	print '对不起，不允许创建数据库。'	rollback	go	--测试创建数据库	use chaxun	go 	create database test

eg:在“学生信息”表中，建立名为“学生信息tri_delete”的触发器，当删除“学生信息”表中某条记录的同时，删除“学生成绩”表中与该记录“学号”相同的所有记录。

use student if exists(select * from sysobjects where name='学生信息tri_delete' and type='TR')	drop trigger 学生信息tri_deletegocreate trigger 学生信息tri_deleteon 学生信息 after delete as	delete from 学生成绩	where 学号 in(select 学号 from deleted)godelete 学生信息 where 学号='9056213'

eg:在“课程”表中，建立名为“课程tri_delete”的触发器，当删除“课程”表中某条记录的同时，删除“学生成绩”表中与该记录“课程号”相同的所有记录。

use student if exists(select * from sysobjects where name='课程tri_delete' and type='TR')	drop trigger 课程tri_deletegocreate trigger 课程tri_deleteon 课程 after delete as	delete from 学生成绩	where 课程号 in(select 课程号 from deleted)godelete 课程 where 课程号='0000007'

8.3管理触发器

（1）查看触发器信息

execsp_helptext 触发器名称 execsp_help 触发器名称

（2）修改触发器

alter trigger 触发器名 on 表名 … 重写触发器 …

（3）删除触发器

drop tigger 触发器名

（4）禁用与启用触发器

————————————————————

9数据库的并发控制（事务）

9.1管理事务

事务的4种运行模式：

显式事务	每个事务均以begin trasaction 语句显示开始，以commit或rollback语句显示结束
隐式事务	在前一个事务完成时新事务隐式启动，但每个事务仍以commit或rollback语句显示完成
自动提交事务	每条语句都是一个事务
批处理级事务

（1）启动和结束事务

（2）事务保存点 保存点设置语句格式：save transaction 保存点名 或保存点变量 保存点使用语句格式：rollback transaction 保存点名 或保存点变量 （3）自动提交事务 （4）事务的嵌套 eg: （1）Alice和Bob分别有银行账号A、B，分别用表a、表b表示，这2个表都只有1个字段amount，表示余额。现在需要从Alice向Bob转账制定金额，要求转账过程中不能出现错误，而且不管转账是否成功，都扣除Alice账号1元钱的手续费。 （2）amount字段上的约束条件是余额不能小于0

declare @xmoney money,@err1 int,@err2 intset @xmoney = 300  --指定转账金额为300begin transaction		update A		set amount = amount - 1 --扣除手续费		set @err1 = @err1 + @@ERROR --记录上述update可能出现的错误		save transaction transfer --设置保存点transfer		update A		set amount = amount - @xmoney --从账号A中扣除金额		set @err2 = @err2 + @@ERROR --记录上述update可能出错的情况		update B		set amount = amount + @xmoney --想账号B中转入金额		set @err2 = @err2 +@@error		if @err1 != 0 --如果扣除手续费出现错误		begin			rollback transaction			print '所有操作失败'		end		else begin			if @err2 != 0 --如果转账过程出现错误			begin				rollback transaction transfer --回滚到保存点transfer处				print '转账失败，但手续费已扣'			end		else begin			commit transaction --所有操作成功，提交事务			print '转账成功'		endendgo

9.2并发数据库访问管理

并发数据操作引起的问题

（1）丢失更新 （2）读脏（旧）数据 （3）不可重复读 （4）幻读 封锁机制 （1）锁的类型 （2）可以锁的资源 （3）锁的兼容性 （4）死锁

————————————————————

10数据库安全管理

10.1用户管理

（1）创建登录账户

（2）管理登录账户 查看登录账户：exec sp_helplogins 登录账户名 修改登录账户： 密码修改： exec sp_password 旧密码 新密码 登录账户 修改默认数据库：exec sp_defaultdb 登录账户 数据库名 修改默认语言： exec sp_defaultlanguage 登录账户 语言名 （3）删除登录账户：sp_droplogin 登录账户名

（1）创建数据库用户：exec sp_grantdbacedd 登录账户 用户名

（2）删除数据库的用户：exec sp_revokedbaccess 用户名

10.2角色管理

固定数据库角色管理:

（1）添加数据库角色 （2）删除固定数据库成员 （3）查看数据库固定成员信息 用户定义数据库角色： （1）创建和删除用户定义数据库角色 （2）添加和删除用户数据库角色成员

10.3权限管理

（1）语句权限：

creatdatabase	创建数据库
create table	在数据库中创建表
create view	在数据库中创建视图
create default	在数据库中创建默认对象
create procedure	在数据库中创建存储过程
create function	在数据库中创建函数
backup database	备份数据库
backup log	备份日志

（2）对象权限：

对象	操作
表	select ，insert，update，delete，referances
视图	select ，insert，update，delete
存储过程	excute
列	select ,update

(3)授权用户角色权限

语句	含义
grant	授权
deny	拒绝
revoke	撤销

————————————————————

11数据库的备份与还原

备份类型	说明
完整备份	某一次完全备份
差异备份	一次完全备份+最后一次差异备份
日志备份	一次完全备份+后边依次备份

11.1完整备份

use 数据库名

go backup database 数据库名 to disk=物理文件路径名 with init name =备份设备名 description =设备描述 init 或 noint go

eg:创建备份设备back_student上备份数据库student,并覆盖以前的数据。

--预处理工作use mastergoexec  sp_addumpdevice 'disk','my_disk','D:\sqlBackup\firstbackup'go --完整备份数据库use mastergobackup database studentto disk='D:\sqlBackup\tmpxsbook.bak'--物理文件名（备份的文件名）with init   					--表示覆盖当前设备上的每一项内容name='D:\sqlBackup\BACK_STUDENT'--备份到的设备名字description ='This is then full full backup STUDENT'

11.2差异备份

use 数据库名

go backup database 数据库名 to disk=物理文件路径名 with diffetental name =备份设备名 description =设备描述 init 或 noint go eg:创建数据库student的差异备份，并将此次备份追加到以前所有备份的后边 。

--差异备份数据库use mastergobackup database studentto disk='D:\sqlBackup\firstbackup'with diffetental  --注意此处为diffetental  noint		--不覆盖go

11.3日志备份

use 数据库名

go backup log 数据库名 to disk=物理文件路径名 with diffetental name =备份设备名 description =设备描述 init 或 noint norecovery

eg：对数据库student做日志备份。要求追加到现有的备份集firstbackup 本地磁盘设备上。

--日志备份数据库use mastergobackup log studentto disk='D:\sqlBackup\firstbackup'with nointgo

11.4备份恢复

--修改记录（插入测试）use chaxungoinsert into sc (sno,cno,grade)values('s62','c34',66)go--恢复数据USE MASTERgoRESTORE DATABASE CHAXUNFROM  DISK='D:\sqlBackup\firstbackup'go

最后：如果感觉可以的话的话🙂，可以赏个👍

转载地址：http://swowi.baihongyu.com/