MSSQLServer2008复习

-- MSSQLServer复习

--================================上部分===========================--

-- 系统数据库
-- master
-- tempdb
-- msdb
-- model


-- 数据库文件
-- 数据文件(主数据文件有且只有一个,次数据文件有且只有一个)
-- 日志文件


-- 类型约束
-- 主键约束:要求主键列数据唯一,不允许为空
-- 唯一约束:要求该列唯一,允许为空
-- 检查约束:某列取值范围限制,格式限制等,如有关年龄的约束
-- 默认约束:某列的默认值,如我们的男性学员较多,性别默认为"男"
-- 外键约束:用于两表之间建立关系,需要指定引用主表的那列,哪个表是外键表，就修改哪个表


-- SQL分类
-- DDL(data define language):数据定义语言:create[table database index view procedure] drop[...] alter[....]
-- DML(data managent language):数据操作语言:insert update delete select
-- DCL(data control language):数据操作语言 (分配权限之类的) Grant 


-- 聚合函数(5个)
-- count()
-- sum()
-- avg()
-- max()
-- min()


-- 排序
-- order by [asc/desc] 默认asc


-- 分组
-- group by(一般与聚合函数配合)




-- 问题:drop table delete table truncate table的区别


-- truncate 删除表数据(不可带条件),不记录事务日志,保留表结构
-- delete   删除表数据(可带条件),记录事务日志,同样保留表结构
-- drop     这个命令可以删除的东东比较多,可以删除整个表,结构和数据,也可以删除数据库,删除字段,删除约束等.




use master
go


select * from sysdatabases -- 查询当前有多少个数据库


select * from sysobjects


-- 创建数据库
use master
go
if exists(select * from sysdatabases where name='school') -- 如果存在有school数据库则把它删掉
drop database school


create database fuxidb
on primary
(
name='school',
filename='f:\s3_database\school.mdf',
size=10mb,
filegrowth=10%
)


-- 创建表
use fuxidb
go


create table class -- 班级表
(
clsId int identity(1,1) primary key,  -- identity(种子,自增量)
clsName varchar(20) not null
)
go
alter table class
add constraint PK_clsId primary key(clsId) -- 主键约束


drop table student -- 学生表
create table student(
stuId int not null,
clsId int not null,
stuName varchar(20) not null,
stuAge int not null,
stuAddress varchar(20) not null,
context varchar(200)
)
go
-- 为student表添加约束
alter table student
add constraint PK_stuId primary key(stuId) -- 主键约束
alter table student
add constraint FK_clsId foreign key(clsId) references class(clsId) -- 外键约束
alter table student
add constraint CK_stuAge check(stuAge between 18 and 35)
alter table student
add constraint DF_stuAddress default('地址不知道') for stuAddress
alter table student
add constraint UQ_stuName UNIQUE(stuName)

drop table exam
create table exam -- 考试表
(
examId int identity primary key,
stuId int constraint FK_stuId foreign key references student(stuId),
exam  int not null check(exam >= 0 and exam <= 100) -- 检查约束
)
use fuxidb
go


select * from class
insert into class(clsName) values('一班');
insert into class(clsName) values('二班');
insert into class(clsName) values('三班');


insert into student(stuId, clsId, stuName, stuAge, stuAddress, context) values(1,1,'zhangsan',25,default,'good');
insert into student(stuId, clsId, stuName, stuAge, stuAddress, context) values(2,2,'zhangsan2',25,default,'good');
insert into student(stuId, clsId, stuName, stuAge, stuAddress, context) values(3,3,'zhangsan3',25,default,'good');


-------------新增数据-----------------


--插入单行数据
--insert into student values(.....);


--插入多行数据
--方式一:创建新表,把旧表数据放入新表
select * into newtable from class
select * from newtable
--方式二：以查询的某个结果作为源放入表,保证列个数,数据类型,具体长度
insert into class(clsName)
select '四班' union
select '五班'


select * from class


-----------------查询数据-----------------
select COUNT(context) from student
select COUNT(context), COUNT(*) from student
select AVG(stuAge) from student
select clsId, AVG(stuAge) from student group by clsId
select clsId, AVG(stuAge) from student group by clsId order by AVG(stuAge) desc
select clsId, AVG(stuAge) from student group by clsId having AVG(stuAge) >= 24 order by AVG(stuAge) desc


select clsId, AVG(stuAge) from student
where
stuAge >= 20
group by
clsId
having 
AVG(stuAge) >= 22
order by
avg(stuAge) desc
/*
  执行顺序
  1. where stuAge >= 20  去掉年龄小于20岁的人家，20岁以下学生不在统计范围内
  2. grou by clsId avg(stuAge) 把剩下的学生分组计算年龄
  3.having avg(stuAge) >= 22 去掉平均年龄小于22岁的班
  4. order by avg(stuAge) desc 把剩下符合条件的班按隆序排列
*/
-- where 与having 都是条件的筛选,请问他们的区别
-- where 是对表里原始的数据进行的筛选,一般最先执行,having是对表中数据分组使用聚合函数计算后的(数据
--加工)数据进行筛选.having在where之后,having与group by 配合使用


--关键字
-- top
-- distinct
-- in
-- between
select top 5 * from student -- 查出前5条记录
select top 50 percent * from student -- 查出前50%的记录
select * from student
select distinct context from student -- 去掉重复值


-- 其它
select * into student2 from student -- 把student表中的数据插入到student2中,相当于复制表和表中的数据
select * into student2 from student where 1 = 2 -- 复制表结构，没有数据
insert into student3 select * from student -- 表示数据的迁移


-- 模糊查询
select * from student where stuName like '%9_'
-- _代表一个关键字
-- %代表多个关键字


----------------子查询----------------------
-- 一个查询的结果作为另一个查询的条件
select * from student where clsId = (select clsId from class where clsName = '一班')
select * from student where stuName = 'zhangsan1' or stuName = 'zhangsan2' or stuName = 'zhangsan3'
-- 上面的一条查询语句等于下面这条查询语句
select * from student where stuName in ('zhangsan', 'zhangsan2', 'zhangsan3')
-- 要点:如果用=,>,<等，要保证子查询的结果只返回一行一列
select * from student where clsId =(select clsId from class) -- 这行语句执行失败
-- in子查询
-- 当返回一列多行时,可以使用in子查询


-- 子查询应用,分页查询
-- 分页思想:前提必须确保按照某个规则排序,即每次查找时每行数据的索引不变。
-- 第一步:子查询,找出已经显示的数据(之前的)
-- 第二步:父查询,除去已经显示的数据,在剩下的里面使用top显示某几


/*
一页显示3条 pageSize
当前是第2页 nowPage
*/
-- 找出已经显示的数据或者说之前的数据


select top 3 * from student where stuId not in(select top 3 stuId from student)
order by stuName




-----------------联接查询--------------------
-- 当在一个结果中要显示多个表列时使用表联接查询
--内联接(99.9%)
--select tb1.col1, tb2.col1 from table1 as tb1 inner join table2 as tb2 on (tb1.主键=tb2.外键)
select stu.stuName, cls.clsName from student as stu
inner join class cls
on (cls.clsId = stu.clsId)


--外联接(0.001%)


--insert into exam(stuId, exam) values(1, 99);
--insert into exam(stuId, exam) values(2, 99);
--insert into exam(stuId, exam) values(3, 99);
--select * from exam
--select * from exam inner join student stu on (stu.stuId = exam.stuId)


select cls.clsName, stu.stuName, exam.exam from -- 三表联接查询
exam
inner join student stu
on(stu.stuId = exam.stuId)
inner join class cls
on(cls.clsId = stu.clsId)


select * from student
where stuId not in(select stuId from exam) -- 找出没有参加考试的人


select * from student stu -- 前面出现的为左表
left join exam -- 后面出现的为右表
on (stu.stuId = exam.stuId)
-- 1. 首先列出内连接数据
-- 2.看左表是否还有没匹配的,有的话就原样列出,右表部分用null补齐.
-- 3.总结:左联接要显示左表的所有数据


select * from student 
where stuId in (select stuId from exam) -- 找出参加过考试的人


select * from student stu inner join exam on(stu.stuId = exam.stuId) -- 实现和上一个句子一样的功能


-- 完全内连接 = 左外连接 + 右外连接
select * from student,exam






--================================下部分===========================--


---------------------------定义变量及常用函数的使用--------------------


-- 常用函数
-- convert(type, data) dateAdd(partName, number, data) datePart(partName, date) dateName(partName, date) 
-- ascii('char') char(number) right(data, count) left(data, count) substring(data, location, index) charIndex(srcData, desData)
-- ltrim('string') rtrim('string') len('string') upper('string') lower('string')


create database sqlReview
go


use sqlReview
go


-- 定义变量
declare @i int, @name varchar(20)
set @i = 5
set @name = 'weichao'
--select @i as 定义的变量
print @i
print @name + convert(varchar(2),@i) -- 打印变量的值


drop table student
create table student
(
stuId int identity(1,1) primary key,
stuname varchar(20)
)


insert into student values('zhangsan');
insert into student values('lisi');


declare @count int, @count2 int
set @count = 0
select @count = COUNT(stuId), @count2 = COUNT(stuname) from student
print '总共有学生人数:' + convert(varchar(4), @count) + '人'


select * from student
declare @name varchar(20)
select @name = stuname from student
print @name


-- 修改表中的字段
alter table student
add stuSeat int


update student set stuSeat = 1 where stuId = 1
update student set stuSeat = 2 where stuId = 2
insert into student values('wangwu',3);


-- 求lisi左右两边的同桌


declare @left varchar(10)
declare @right varchar(10)
declare @seatNo int


select @seatNo = stuSeat from student where stuname = 'lisi'
select @left = stuname from student where stuSeat = @seatNo - 1 --lisi左边的同学
select @right = stuname from student where stuSeat = @seatNo + 1 --lisi右边的同学


print @left + ' - lisi - ' + @right


-- 全局变量
print @@version
print @@rowcount


-- 流程控制语句
if( 5 > 6)
print 'true'
else
begin
print 'false'
print 'false'
print 'false'
end


-- 判断student表
declare @count int
set @count = 0
select @count = COUNT(*) from student


if(@count > 2)
begin
print '本班级人数较多'
select * from student
end
else
print '本班级人数较少'

-- 逻辑循环语句
declare @i int
set @i = 0
while @i <= 100
begin
set @i = @i + 1
print @i
end


-- 循环插入数据
declare @name varchar(20), @seatNo int
declare @i int
set @i = 1


while(@i < 1000)
begin
set @name = 'weichao' + CONVERT(varchar(20), @i)
set @seatNo = CONVERT(int, RAND() * 100)
insert into student values(@name, @seatNo)
set @i = @i + 1
end

select * from student








-- dateadd函数


declare @date datetime
set @date = '1990-10-05 11:30:00'
print @date
print dateadd(yyyy, 2, @date)


print dateName(year, getdate()) -- 输出当前的年分
print datepart(year, getdate()) -- 同上




--字符串函数
print ascii('c')
print char(65)


print left('weichao', 3)
print right(left('weichao',3), 2)
print substring('weichao', 2, 2)
print charIndex('ab', 'abcd') -- 返回一个字符在另一个字符串中的位置,非下标,如果在第一个则返回1，如果不存在则返回0
print ltrim('    weichao');
print rtrim('weichao   ');
print len('weichao')
print upper('weichao')
print lower('WEICHAO')


--等待延迟
waitfor delay '00:00:02'
print 'hello'


-- case when then else end
declare @i int
set @i = 5
print case @i
when 1 then 'A'
when 2 then 'B'
when 3 then 'C'
else 'D'
end


select *, case stuname when 'weichao' then '男' when '0' then '女' else '空' end as '性别' from student


--行转列,列转行(转制)
select stuName,
sum(case subject when 'Chinese' then marks else 0 end) as 'Chinese',
sum(case subject when 'Math' then marks else 0 end) as 'Math',
sum(case subject when 'English' then marks else 0 end) as 'English'
from score group by stuname




----------------------视图、索引、事务-------------------------------------


-- 视图:虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上,它并不存放数据，
-- 页是存放在视图所引用的原始表，同一张原始表，根据不同用户的不同需要，可以创建不同的视图，视图可以是表，也可以是
-- 视图，只要修改了表的数据，视图中也可以马上反映出来，因为它是数据表的一个引用


-- 注意：视图是属于数据库的，它不属于表，因为复杂的SQL创建的视图可能是多表联查的结果


-- 视图的作用:
--1.筛选表中的行(起到赋予权限的作用，限制某些列在视图中显示)
--2.防止未经许可的用户访问第三数据
--3.降低数据库的复杂程度(比一般的SQL查询速度快一点，可以节省流量)


-- 创建测试表及插入测试数据


create table score
(
scoreId int identity(1,1) primary key,
stuName varchar(20),
subject varchar(20),
marks int
)


insert into score values('weichao', 'Chinese', 90);
insert into score values('weichao2', 'Chinese', 90);
insert into score values('weichao3', 'Chinese', 90);


insert into score values('zhangsan', 'Chinese', 90);
insert into score values('zhangsan2', 'Chinese', 60);
insert into score values('zhangsan3', 'Chinese', 30);


insert into score values('weichao', 'Math', 80);
insert into score values('weichao2', 'Math', 70);
insert into score values('weichao3', 'Math', 60);


insert into score values('weichao', 'English', 80);
insert into score values('weichao2', 'English', 70);
insert into score values('weichao3', 'English', 60);


-- 创建视图
create view vw_score
as
select stuName,
SUM(case subject when 'Chinese' then marks else 0 end) Chinese,
SUM(case subject when 'Math' then marks else 0 end) Math,
SUM(case subject when 'English' then marks else 0 end) English
from score group by stuName 
go


-- 查看视图
select * from vw_score


-- 修改视图(把create改为alter即可)


alter view vw_score
as
select stuName,
sum(case subject when 'Chinese' then marks else 0 end) Chinese,
SUM(case subject when 'Math' then marks else 0 end) Math,
SUM(case subject when 'English' then marks else 0 end) English
from score group by stuName
go


-- 删除视图
drop view vw_score


-- 其它问题：视图可以用来进行增删改查，但是一般只用于查询，不用于其它操作，因为视图一般是复杂联表查询，如果进行其它的操作，很可能会出错




-- 索引(index)
-- 优点:优化查询 缺点：如果数据很多，插入数据时会变得比较慢，因为要找到合适的位置
-- 存放的位置：索引页
-- 分类：主键索引(速度快)、唯一索引(速度快)、聚集索引(效率高,数据的物理存放按哪一方式存放哪一个方式就是聚集索引)、非聚集索引
-- 注意：索引是属于表的,它一般建立在数据量很大的数据表上,它最好建立在经常被查询的列上,如果一个列中有大量相同的数据则不必建立索引
-- 不好建立太多的索引，否则速度变慢，效率隆低;经常更新、变动的列不要建立索引
-- 其它问题:聚集索引(clustered):一张表只能有一个聚集索引,非聚集索引(nonclustered):一张表可以有多个非聚集索引


-- 创建索引
create nonclustered index ix_student_stuName on student(stuname desc)


-- 删除索引
drop  index ix_student_stuName




-- 事务(transaction)


-- 分类:显式事务、隐式事务、自动提交事务(默认,每一条语句就是一条事务)[显式事务：能清楚看到事务的流程；隐式事务：看不到事务的流程]
-- 作用：保证数据的完整性和一致性
-- 使用情况:有两个或者两个以上的操作同时进行的时候，就需要用到事务,作为单个逻辑工作单元执行一系列操作
-- 注意：事务是一个不可分割的工作逻辑单元，要么都执行成功，要么都失败


-- 特性:ACID 原子性、一致性、隔离性、永久性
-- 原子性:不能再分割，扣钱，加钱一起执行
-- 一致性：结果的一致性，张三的钱被扣，李四要加上钱
-- 隔离性：只针对某一部分，控制并发，比如张三给李四钱，张三扣钱，李四加钱，与张四张五无关


-- 注意：一旦事务提交或回滚，则事务结束(跟一般的方法中的return类似)


-- 创建测试表及插入测试数据


create table account
(
accountId int primary key,
accountName varchar(20),
balance int
)


alter table account
add constraint ck_account_balance check(balance > 1)


insert into account values(1, 'zhangsan', 500)
insert into account values(2, 'lisi', 10)


select * from account


-- 转帐
update account set balance = balance + 1000
where accountId = 1


update account set balance = balance + 1000
where accountId = 2


-- 创建事务
begin transaction -- 开始事务
rollback transaction -- 回滚事务
commit transaction -- 提交事务 


select * from sysobjects where name = 'account'


-- 一个事务例子


declare @count int
set @count = 0


begin transaction


update account set balance = balance - 1000
where accountId = 1
set @count = @count + @@error


update account set balance = balance + 1000
where accountId = 2
set @count = @count + @@error


if(@count = 0)
begin
print ' 转帐成功'
commit transaction --(transaction可以被省略)
end
else
begin
print '转帐失败'
rollback transaction
end

-- 其它问题:savepoint(保存点：如果事务很长，前几步成功，最后一步失败，想回滚到前面成功的某个地方,就可以设个保存点)
-- 事务之间可以嵌套


----------------------------------------存储过程------------------------------------------
-- 存储过程(stored procedurer):是在大型数据库系统中，一组为了完成特定功能的SQL语句集,经编译后保存在数据库中，用户通过指定存储过程的名字并给出参数(如果需要)来执行它
-- 使用原因: 复杂的业务，频繁地使用业务，就会用到存储过程
-- 优点：执行速度快、模块化程序设计、提高系统安全性、减少网络流量
-- 分类:系统存储过程、用户自定义存储过程


-- 系统存储过程:系统定义，存放在master数据库中, 一般以sp_开头或者xp_开头
-- sp_:System procedure 系统存储过程
-- xp_:extend Procedure 扩展存储过程
-- 执行系统存储过程的三种方式
-- execute sp_tables
-- exec sp_columns student
-- sp_help


-- 常见的系统存储过程:
sp_databases -- 列出服务器上的所有数据库
sp_helpdb -- 报告有关指定数据库或所有数据库的信息
sp_rename -- 重命名数据为中的对象，比如表，列
--sp_rename 'score','score1'(重命名表名)
--sp_rename 'score1','score'
--sp_rename 'score.stuName','stuname2'(重命名列名)
--exec sp_rename 'score.stuname2','stuName'
sp_renamedb -- 更改数据库的名称
--sp_renamedb 'sqlReview','sqlReview2'(重命名数据库)
--sp_renamedb 'sqlReview2','sqlReview'
sp_tables -- 返回当前环境下可查询的对象的列表
sp_columns -- 返回某个表中列的信息
--sp_columns score
sp_help -- 查看某个表的信息
--sp_help score
sp_helpconstraint -- 查看某个表的约束
--sp_helpconstraint score
sp_helpindex -- 查看某个表的索引
--sp_helpindex score
sp_configure -- 管理员配置


xp_cmdshell 'cmdcommand' -- 执行dos命令
xp_cmdshell 'notepad'




-- 创建自定义的存储过程
create procedure proc_print
as
print 'Hello procedure'
go


-- 执行存储过程
execute proc_print
exec proc_print
proc_print


-- 删除存储过程
drop procedure proc_print


-- 创建测试表和插入测试数据
if exists(select * from sysobjects where name='score')
begin
drop table score
end


create table score
(
scoreId int identity(1,1) primary key,
stuName varchar(20),
writtenExam int,
labExam int
)


-- 插入测试数据
insert into score values('zhangsan', 98,80);
insert into score values('lisi',70,50);
insert into score values('wangwu',75,70);


select * from score


-- 创建不带参数的存储过程


create proc proc_score
as
declare @writtenAvg int
declare @labAvg int
select @writtenAvg = AVG(writtenExam) from score
select @labAvg = AVG(labExam) from score
print '笔试平均分:' + str(@writtenAvg, 2)
print '机试平均分:' + str(@labAvg, 2)
if(@writtenAvg > 70 and @labAvg > 70)
print '本班成绩优秀'
else
print '本班成绩一般'
print '--------------------'
print '------考试通过的学生信息如下----------'
select * from score where writtenExam >= 60 and labExam >= 60
go




-- 执行存储过程
execute proc_score


-- 删除存储过程
drop proc proc_score


-- 注意: 存储过程没有返回值,但是它可以使用输出参数(可以有多个)把值传出去


-- 创建带参数的存储过程
drop proc proc_score


create proc proc_score
@writtenPass int,
@labPass int
as
declare @writtenAvg int
declare @labAvg int
select @writtenAvg = AVG(writtenExam) from score
select @labAvg = AVG(labExam) from score
print '笔试平均分:' + str(@writtenAvg, 2)
print '机试平均分:' + str(@labAvg, 2)
if(@writtenAvg > 70 and @labAvg > 70)
print '本班成绩优秀'
else
print '本班成绩一般'
print '-------------------------'
print '--------考试通过的学生信息如下------------'
select * from score where writtenExam >= @writtenPass and labExam >= @labPass
go


-- 第一种执行带参数存储过程方法
exec proc_score 60,60


-- 第二种
declare @w int
set @w = 65
declare @l int
set @l = 65


exec proc_score @w,@l


--第三种
exec proc_score @writtenPass=70,@labPass=70-- 在知道参数名称的前提下,这样写可以不按定义参数的顺序来传值




-- 创建带参数，带默认值的存储过程
drop proc proc_score


create proc proc_score
@writtenPass int = 50,
@labPass int = 60
as
declare @writtenAvg int
declare @labAvg int
select @writtenAvg = AVG(writtenExam) from score
select @labAvg = AVG(labExam) from score
print '笔试平均分:' + str(@writtenAvg, 2)
print '机试平均分:' + str(@labAvg, 2)
if(@writtenAvg > 70 and @labAvg > 70)
print '本班成绩优秀'
else
print '本班成绩一般'
print '-------------------------------'
print '------考试通过的学生信息如下---------'
select * from score where writtenExam >= @writtenPass and labExam >= @labPass
go


-- 执行存储过程
exec proc_score -- 不给出参数，则使用默认参数
exec proc_score 65 -- 给出第一个参数，第二个参数使用默认值
exec proc_score @labPass = 80 -- 指定某个参数的值，其它使用默认值




-- 输出参数的存储过程
create proc proc_getDate
@now datetime output
as
set @now = GETDATE()
go


-- 执行带输出参数的存储过程
declare @date datetime
exec proc_getDate @date output -- 执行的时候如果是输出参数，在调用的时候也需要指明output关键字
print @date




-- 不带默认值
create proc proc_add
@num1 int,
@num2 int,
@sum int output
as
set @num1 = 5
set @sum = @num1 + @num2
go


-- 执行
declare @he int
exec proc_add 1,2,@he output
print convert(char(10), @he)


-- 带默认值和输出参数的存储过程
drop proc proc_add


create proc proc_add
@sum int output, -- 输出参数最好写在输入参数的前面（因为输出参数必须在执行的时候进行接收，输入变量可以使用默认值）
@num1 int = 5,
@num2 int = 0
as 
set @sum = @num1 + @num2
go


-- 执行
declare @he int
exec proc_add @he output
print @he






------------------------------------------触发器-------------------------------
-- 触发器:它是一种特殊的存储过程，类似于侦听机制的事件
-- 作用:执行一些与某些操作有关的额外的操作
-- 注意:触发器与表相关
-- 类型:delete insert update
-- 修改: 分为两步,先删除再增加
-- 临时表:inserted表(插入、更新后)、deleted表(更新前的记录)


-- 创建触发器


create trigger trig_score_insert
on score for insert -- 侦听insert操作
as
print '-----数据添加-----'
go


select * from score


-- 执行insert操作
insert into score values('weichao', 100, 100);


-- 删除触发器
drop trigger trig_score_insert


create trigger trig_score_insert
on score for insert
as
declare @name varchar(20)
select @name = stuName from inserted
print '数据添加' + @name
select * from inserted
go


insert into score values('yuki', 99, 99);


-- 代替check约束的触发器,可以让它回滚
drop trigger trig_score_insert


create trigger trig_score_insert
on score for insert
as
declare @name varchar(20)
select @name = stuName from inserted
if(@name = 'admin')
print 'sorry'
rollback
go


insert into score values('admin', 80, 80);


-- 响应删除事件的触发器
create trigger trig_score_backup
on score for delete
as
if exists(select * from sysobjects where name='score_bak')
insert into score_bak select * from deleted -- 备份表已存在
else
select * into score_bak from deleted -- 备份表未存在
go


select * from score
select * from score_bak


delete from score where scoreId = 1


-- 控制修改字段的频度
drop trigger trig_score_update


create trigger trig_score_update
on score for update
as
declare @oldScore int
declare @newScore int
select @oldScore = labExam from deleted
select @newScore = labExam from inserted
if(abs(@oldScore - @newScore) > 5)
begin
print '分数修改的幅度太大'
rollback
end
go




select * from score
update score set labExam = labExam + 6 where scoreId = 2


----------------------------数据库安全----------------------------




-- 建立登陆用户
exec sp_addlogin 'user1', '123'
exec sp_addlogin 'user2', '123'


-- 为登录用户设置testdb数据库用户
exec sp_grantdbaccess 'user1', 'fuxidb'


-- 添加用户角色
exec sp_addrole testrole


-- 为角色添加数据库用户
exec sp_addrolemember testrole, user1
exec sp_addrolemember testrole, user2


-- 设置查询权限
grant select to testrole


-- 取消testuser2对表clss的访问权限
revoke select, insert, update, delete on class from user2


-- 赋予用户user1对数据库表的拥有权
grant all priviliges on table exam to user1
grant update(exam), select on table exam to user1


-- 回收用户user1对数据库表权限
revoke update(exam), select on table exam from user1


-- 查看用户权限
exec sp_helprotect 'class'




--------------------------------数据库的备份和恢复---------------------------
-- 备份:backup,恢复:restore
-- 备份文件后缀: .bak
-- 注意：在数据库运行的时候也可以运行
-- 备份类型： 完整备份、差异备份


-- 备份数据库
backup database fuxidb to disk = 'd:\fuxidb.bak'


-- 差异备份
backup database fuxidb to disk = 'd:\fuxidb.bak' with differential


-- 恢复数据库(差异还原)
use master
go
restore database fuxidb from disk = 'd:\fuxidb.bak'


-- 完整还原
restore database fuxidb from disk = 'd:\fuxidb.bak' with norecovery