北京联合大学
《数据库》实验报告
题 目：姓 名：学 号: 专 业：编 制 时 间： 北京联合大学编制
实验一
一、实验目的
学习MS SQL Server 2005的安装、启动方法，注册SQL Server服务器的方法。
熟悉SQL Server的工作环境
掌握使用向导和命令建立数据库的方法
掌握数据库常用选项的设置方法
掌握建立数据表的方法。
二、实验内容
采用SQL语句创建数据库
CREATE DATABASE OrderMag2
ON
PRIMARY(NAME=OrderMag2_Data,
FILENAME = 'C:\Program
Server\MSSQL\data\OrderMag2_Data.ndf',
SIZE=10MB,
MAXSIZE=200MB,
FILEGROWTH=10%)
LOG ON
(NAME=OrderMag2_Log,
FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL\data\OrderMag2_Log.LD', SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=10%)
GO
三 回答问题
创建Store、Customer 、Order1三个表
use OrderMag2
create table Store(
Files\Microsoft SQL
)
) Pno char(6) primary key not null, Pname varchar(20) not null, Ptype char(2), Pnum int create table Customer( Cno char(6) primary key not null, Cname varchar(20) not null, Ctel varchar(12) not null, Caddr varchar(50), Czip varchar(6)
create table Order1(
)
alter table Order1 add constraint fk_customer_id foreign key(Cno) references Customer(Cno);
alter table Order1 add constraint fk_Store_id foreign key(Pno) references Store(Pno);
alter table Order1 add constraint check_Onum check(Onum>=0 and Onum <=100) alter table Order1 add constraint check_Osum check(Osum>0)
操作过程中遇到的问题是“order”是数据库中的一个关键字，不能把它作为表的名字，所以在创建order表的时候在其后面加上数字或其他字母来以示区别。
四 实验小结
通过本次实验学会了MS SQL Server 2005的安装、启动方法，注册
SQL Server服务器的方法；和如何使用向导和命令建立数据库和数据库常用选项的设置方法及建立数据表的方法。
Ono char(6) primary key not null, Cno char(6), Pno char(6), Onum int, Osum int, Odate datetime
实验二、数据维护及简单查询
一、实验目的： 学习使用查询分析器
掌握使用Insert、Update、Delete命令维护数据表的数据的方法 熟练掌握简单SQL命令的使用 二、实验内容： 1. INSERT命令输入数据
使用Insert命令分别向OrderMag数据库中的三个表中输入如下5条数据。
1) Store（Pno, Pname, Ptype， Pnum）
2) Order(Ono, Cno, Pno, Onum, Osum, Odate)
订单（订单号，顾客号，定购零件号，定购数量，订单金额，签订日期）
3) Customer(Cno, Cname, Ctel, Caddr, Czip) 顾客（顾客号，顾客名称，电话，地址，邮编）
先修改Store表的列Ptype的长度,再进行插入操作,如下 ALTER TABLE Store
ALTER COLUMN Ptype char(10)
INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES ('P1','齿轮','传动',200)
INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES ('P2','蜗杆','传动',30)
INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES ('P3','螺栓','标准',120)
INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES ('P4','垫圈','标准',500)
INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES ('p5','螺母','标准',1020)
INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES ('C1','北京联合大学','010-64900310','北京','100101')
INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES ('C2','上海贝尔','021-23232323','上海','')
INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES ('C3','LG','0755-2325123','深圳','412100')
INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES ('C4','华为（北京）', null,'北京','')
INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES ('C5','香港航空','852-56545236','香港','142553')
INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES ('O1','C2','P2',100,1000,'2006-4-8')
INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES ('O2','C3','P3',10,100,'2008-5-2')
INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES ('O3','C1','P2',20,200,'2007-4-5')
INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES ('O4','C1','P4',2,20,'2009-2-4')
INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES ('O5','C3','P5',1,10,'2009-3-1')
2．把store表中所有零件的数量增长一倍
UPDATE Store SET Pnum=Pnum*2
3．把order表中签订日期在20xx年之前的数据删除。
delete from order1 where Odate<'2005'
4．向store表中添加一列：零件颜色（Pcolour）；
ALTER TABLE Store ADD Pcolour varchar(20)
5．查询三个表中的内容
SELECT * FROM Store
SELECT * FROM Customer
SELECT * FROM Order1
6．查询所有订单的金额，并按照金额的降序排列
SELECT Osum FROM Order1 ORDER BY Osum DESC
7．查询签订日期在20xx年的所有订单的信息。
SELECT * FROM Order1 WHERE Odate<'2009'
8．查询顾客姓名中含有“北京”的顾客姓名和电话。
SELECT * FROM Customer WHERE Cname LIKE '%北京%'
9．查询库存数量少于100的零件号和名称。
SELECT Pno ,Pname FROM Store WHERE Pnum<100
10．查询邮编为空的顾客的情况。
SELECT * FROM Customer WHERE Ctel is null
三、 回答问题
1) 使用Insert命令需要注意哪些问题
答：1）插入一条记录时最好一次性插进去，避免主键列报错。或者把主键列设置为自动增长，这样就不用考虑主键那报错了2）尽量不用关键字做列的名子，即使要用也要在其外面加[]。
2）Alter与Update语句有什么区别
答：Alter用于修改表结构，而Update用于修改表中内容
四、 实验小结
通过本实验学会了如何运用Insert、Update、Delete命令对表中内容进行修改。实验中遇到的问题是区别邮编为空(null)的情况和空字符串(’’)，。
实验三、SQL命令使用（二） 一、实验目的：
（1）学习、掌握分组与汇总的函数的使用
（2）学习、掌握在SQL语句中使用函数的方法
（3）学习、掌握连接查询的方法
（4）学习、掌握子查询的方法
二、实验内容：
,Order1 WHERE 1.查询订单金额大于100的顾客的名称和电话； SELECT Cname,Ctel FROM Customer
Order1.Cno=Customer.Cno AND Order1.Osum>100
2.查询所有签订订单的顾客的名称和邮编；
select Cname,Czip from Customer where Cno in(select distinct Cno from Order1)
3.统计每类零件的数量分别为多少；
select Pno,sum(Onum) from Order1 group by Pno
4.统计每个顾客签订订单的次数；
select Cno,count(*) from Order1 group by Cno
5.查询所有顾客签订订单的情况（包括没有签订订单的顾客）；
select * from Customer
on(Customer.Cno=Order1.Cno) left outer join Order1
6.查询没有卖过一次的零件号（没有订单）；
select Pno from Store where Pno not in(select distinct Pno from Order1)
7.查询每个顾客签订订单的金额总数；
select Cno,sum(Osum) from Order1 group by Cno
8.查询所有订单金额的平均值；
select avg(Osum) from Order1
9.查询至少签订过两次订单的顾客信息。
select Cno,count(*) times from Order1 group by Cno having count(*)>=2
三、完成实验报告并回答问题
1) 外连接与内联接的区别在哪里
答：外连接是指把舍弃的元组也保存在结果关系中，而在其他属性上填
空值。
内连接是指只有满足连接条件的元组才能作为结果输出。
2）使用子查询需要注意哪些事项
答：a. 一个子查询必须放在圆括号中。
b.子查询的select语句中不能使用order by子句，order by 子句只能对最终查询结果排序。
c.带有In谓词的子查询中，当In中为单一值时可用“=”代替，否则不可。
d.带有比较运算符的子查询，将子查询放在比较条件的右边以增加
可读性。
f.子查询返回单值时可以用比较运算符，但返回多值时要用Any或
All谓词修饰，而使用Any或All时必须同时使用比较运算符。例>Any,!=All
g.带有Exists谓词的子查询不返回任何数据，只产生逻辑真值‘True’或逻辑假值‘False’。使用存在量词Exists后，若内层查询结果非空，则外层的where子句返回真值，否则返回假值；与Exists相对应的是Not Exists，使用Not Exists时，若内层查询结果为空，则外层的where子句返回真值，否则返回假值。由Exists引出的子查询，其目标列表达式通常用*，因为带Exists的子查询只返回真值或假值，给出列名无实际意义。一些带Exists或Not Exists的子查询不能被其他形式的子查询等价替换，但所有带In谓词，比较运算符，Any和All谓词的子查询都能用带Exists谓词的子查询等价替换。
四、 实验小结
通过本实验学会了如何使用分组与汇总函数，对连接查询和子查询有了深入的了解。
实验四．视图和索引 一、实验目的：
（1）掌握建立、维护索引的方法
（2）掌握建立、使用视图的方法
二、实验内容：
1．使用Create View Wizard创建视图
通过企业管理器的Create View Wizard创建视图
1）在企业管理器中，单击菜单“工具”下面的“向导”
2）展开“数据库”后，双击下面的“创建视图向导”
3）单击“下一步”后，选择数据库OrderMag，选择表order
4）选择字段Ono, Cno, Pno, Onum。
5）输入条件语句WHERE Onum >1000，
6）输入视图名称v_order
8）在企业管理器中的“数据库” ?OrderMag?视图下查看视图v_order。
9）在查询分析器中输入并执行语句SELECT * FROM v_order
10）结果如何？显示的字段是否为前面自己定义的字段？
答，显示出满足条件的结果，显示的字段为当前自己定义的字段。
11）删除视图v_order。
Drop View v_order
2. 在查询分析器中创建视图
1) 建立一个视图，包括订单号、零件名称、顾客名称、订单金额等信息 create view is_osc(Ono,Pname,Cname,Osum)
Order1.Ono,Store.Pname,Customer.Cname,Order1.Osum
Order1,Customer,Store
where Order1.Pno=Store.Pno and Order1.Cno=Customer.Cno
2）建立一个视图，查询订单金额大于10000元的大客户信息。 create view is_c
as
SELECT Cname, Osum
FROM is_osc
WHERE (Osum > 10000)
3）建立一个视图，查询每个顾客签订订单的总金额
create view is_sum
as select Cno,sum(Osum)Ssum from Order1 group by Cno
3. 察看系统提供的示例数据库pubs上有哪些索引，是什么类型的，列
as select from
4．为OrderMag数据库设计索引，并建立到表上。
Create unique index cnoind on Customer(Cno);
Create unique index onoind on Orders(Ono);
Create unique index pnoind on Store(pno);
三、完成实验报告并回答问题
1) 视图的建立有哪些需注意的问题
答：A在CREATE VIEW语句中，不能包括ORDER BY，COMPUTE或者COMPUTE BY 子句也不能出现INTO关键字
B创建视图所参考基表的列数最多为1024列
C创建视图不能参考临时表
D尽量避免使用外连接创建视图
E在一个批处理语句中，CREATE VIEW 语句不能和其他TRANSACT-SQL语句混合使用
2）索引的作用是什么？有什么分类？每类有什么特点？
答：1）创建索引可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
2）根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两
种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引，另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。
3）聚集索引
（1）首先指出一个误区，主键并不一定是聚集索引，只是在SQL
SERVER中，未明确指出的情况下，默认将主键定义为聚集，而ORACLE中则默认是非聚集，因为SQL SERVER中的ROWID未开放使用。
（2）聚集索引适合用于需要进行范围查找的列，因为聚集索引的叶子
节点存放的是有序的数据行，查询引擎可根据WHERE中给出的范围，直接定位到两端的叶子节点，将这部分节点页的数据根据链表顺序取出即可；
（3）聚集索引尽量建立在值不会发生变更的列上，否则会带来非聚集
索引的维护；
（4）尽量在建立非聚集索引之前建立聚集索引，否则会导致表上所有非聚集索引的重建；
（5）聚集索引应该避免建立在数值单调的列上，否则可能会造成IO的竞争，以及B树的不平衡，从而导致数据库系统频繁的维护B树的平衡性。聚集索引的列值最好能够在表中均匀分布。
2、非聚焦索引
（1）非聚集索引适合用于需要进行等值查找的列，因为非聚集索引的叶子节点存放的是有序的索引列与书签的映射行，查询引擎可根据WHERE中给出的值，得到书签，继而定位到数据行；
（2）覆盖索引（Covering Index），是非聚集索引的一种特殊且高效的应用，就是将需要返回的数据列设计成组合索引，在SELECT时只查询索引中存在的数据列，这样就能形成索引覆盖，因为索引行中已经包含了想到的数据，不需要再进行书签查找；
在SQL SERVER 2005及以上版本中，提供了INCLUDED关键字，可以在非聚集索引中包含更多列，也是覆盖索引的一个有效引申；
（3）非聚集索引建立在值具有单调性的列上，比如：自增列（单调递增），可以减少索引的外部碎片及索引结构的维护；
四、 实验小结
通过本实验，学会了如何建立，删除视图，并对索引有了一定的了解和掌握。
实验五．用户自定义函数（2学时）
实验目的：
学习、掌握用户自定义函数的建立和使用
实验内容：
1、 创建自定义函数
创建一个用户自定义函数，并测试、查看函数返回值。
1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_TaxRate
(@ProdID INT)
RETURNS numeric(5,4)
AS
BEGIN
RETURN
(SELECT
CASE CategoryID
WHEN 1 THEN 1.10
WHEN 2 THEN 1
WHEN 3 THEN 1.10
WHEN 4 THEN 1.05
WHEN 5 THEN 1
WHEN 6 THEN 1.05
WHEN 7 THEN 1
WHEN 8 THEN 1.05
END
FROM Products
WHERE ProductID = @ProdID)
END
GO
2）此函数中输入变量是什么？返回值类型？如何定义的返回值？ 答：此函数输入变量是@ProdID，返回值类型是numeric(5,4) 返回值是这样定义的
SELECT
CASE CategoryID
WHEN 1 THEN 1.10
WHEN 2 THEN 1
WHEN 3 THEN 1.10
WHEN 4 THEN 1.05
WHEN 5 THEN 1
WHEN 6 THEN 1.05
WHEN 7 THEN 1
WHEN 8 THEN 1.05
END
FROM Products
WHERE ProductID = @ProdID
3）输入并执行语句测试函数
SELECT ProductName, UnitPrice,Northwind.dbo.fn_TaxRate(ProductID) AS TaxRate,UnitPrice * Northwind.dbo.fn_TaxRate(ProductID) AS PriceWithTax
FROM Products
查看结果
注意： 函数可以在Select子句后面调用。
2、返回值为多值的自定义函数
创建函数返回多列多值。
1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_LargeFreight
(@FreightAmt money)
RETURNS TABLE
AS
RETURN
( SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightAmt
)
函数中输入变量是什么？返回值类型？如何定义的返回值？
答：函数中输入变量是@FreightAmt，返回值类型是money
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightAmt
2）输入并执行语句测试函数
SELECT * FROM fn_LargeFreight(600)
查看结果
3、返回值为多值的自定义函数
本实验创建的函数也是返回多列多值，注意与上面实验的差别。
1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY, Name nvarchar(40) NOT NULL, Title nvarchar(30), MgrEmployeeID int, processed tinyint default 0)
AS
BEGIN
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID
RETURN
END
GO
此函数中输入变量是什么？返回值类型是什么？如何定义的返回值？ 答：此函数中输入变量是@InEmployeeID，返回值类型是TABLE， 这样定义的返回值
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
3）输入并执行语句测试函数
SELECT EmployeeID,
dbo.fn_FindReports(5)
查看结果。
[Name], Title, MgrEmployeeID FROM FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID
4．设计一个函数，在OrderMag数据库中，输入零件类别，返回该类别零件的平均存量、最高存量和该类零件的总数量。
CREATE FUNCTION fund_Pno
(@Pptype char(10))
RETURNS TABLE
AS
RETURN
( SELECT avg(Pnum) Avgp,max(Pnum) Maxp,sum(Pnum) Sump
FROM Store
WHERE Ptype= @Pptype
)
select * from fund_Pno('传动')
5．设计一个函数，在OrderMag数据库中，输入订单号，返回该订单所涉及的零件名称和类别。
USE OrderMag
GO
CREATE FUNCTION fpname
(@fOno varchar(60))
RETURNS TABLE
AS
RETURN
( select O.Ono,S.Pname,S.Ptype from Store AS S join Orders As O On O.Pno=S.Pno where O.Ono= @fOno
)
select * from fpname('O1')
6．设计一个函数，在OrderMag数据库中，根据零件库存量的大小，大于500的认为是充足，在100-500之间的是均衡，小于100的为面临缺货。
USE OrderMag
GO
CREATE FUNCTION fpnum4()
RETURNS Table
AS
RETURN
(SELECT
'Pnum Range'=
CASE
WHEN Pnum>500 THEN '充足'
WHEN Pnum BETWEEN 100 and 500 THEN '均衡'
WHEN Pnum<100 THEN '面临缺货'
END
FROM Store
)
select * from fpnum4()
7．设计一个函数，根据输入的数值，计算从1加到该数的和（如输入5，则计算1+2+3+4+5=15,输出为15）。
create function sumn(@num int)
returns int
as
begin
declare @i int;
declare @s int;
set @i=1;
set @s=0;
while (@i<=@num)
begin
set @s=@s+@i;
set @i=@i+1;
end
return @s
end
print '从1加到该数的和是：'+cast(dbo.sumn(5) as varchar)
8．完成实验报告
（1）用户自定义函数在定义与使用上有何需要注意的问题？与存储过程有何不同？
答：在SQL SERVER中调用自定义函数时，必须在自定义函数前加上创建此函数的用户
存储过程:
存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编
程功能。
存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
可以出于任何使用 SQL 语句的目的来使用存储过程，它具有以下优点：
1、可以在单个存储过程中执行一系列 SQL 语句。
2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。用户定义函数：Microsoft SQL Server 2000 允许创建用户定义函数。与任何函数一样，用户定义函数是可返回值的例程。根据所返回值的类型，每个用户定义函数可分成以下三个类别：
1、返回可更新数据表的函数
如果用户定义函数包含单个 SELECT 语句且该语句可更新，则该函数返回的表格格式结果也可以更新。
2、返回不可更新数据表的函数
如果用户定义函数包含不止一个 SELECT 语句，或包含一个不可更新的 SELECT 语句，则该函数返回的表格格式结果也不可更新。
3、返回标量值的函数
用户定义函数可以返回标量值。
四、 实验小结
通过本实验掌握了自定义函数的建立及用法
实验六．安全性管理（2学时）
一、实验目的：
(1) 了解SQL Server 的认证模式，安全管理的主要内容和方法
(2) 掌握用户管理、访问权限管理的基本方法
二、实验内容：
SQL Server、数据库、对象等不同级别的安全性设置与管理。
1．设置身份验证模式
1）在SSMS中，选择服务器实例
2）单击右键，选择“属性”，在属性对话框中选择“安全性”选项卡
3）将身份验证模式设定为“仅Windows ”
4）单击“确定”，SQL SERVER重新启动服务后更改验证模式
5）重新启动服务，登陆SSMS时选择SQL SERVER验证，然后输入sa用户登录
6）能否登录？为什么？
不能，因为此时的访问权限仅限于Windows
7）重复1-3步骤，将身份验证模式设定为混合模式。
注意：实验完成后应该将其重新设定为混合认证模式；只有重新启动服务，验证模式才生效。
2．添加SQL SERVER登录帐号
添加SQL SERVER登录帐号TestSQL。
1）在SSMS中，展开“安全性”，选择“登录名”，选择“新建登录名”
2）选择SQL Server身份验证，
3）输入用户名TestSQL及密码TestSQL，单击“确定”
4）打开数据库引擎查询页，输入登录帐号TestSQL和密码TestSQL，单击“确定”，此时数据库引擎查询页是以TestSQL身份登录。
5）输入并执行查询语句SELECT * FROM master.dbo.sysDatabases
6）在查询页中输入“Use OrderMag”T-SQL语句，切换当前数据库。
7）切换是否成功? 为什么?
答：不成功，因为系统没有对TestSQL开放访问数据库的权限。
3、添加数据库用户帐号
授权SQL SERVER登录帐号TestSQL访问数据库OrderMag。
1）在SSMS中，展开数据库OrderMag中的“安全性”，选择右键单击“用户”，选择“新建用户”
2）在“登录名”中选择TestSQL，然后单击“确认”
3）打开数据库引擎查询页，输入登录帐号TestSQL和密码TestSQL，单击“确认”。
4）此时查询就是以TestSQL身份登录
5）在查询页中输入“OrderMag”T-SQL语句，切换当前数据库。
6）切换是否成功？为什么？
答，不成功，因为虽然对用户开放了数据库，但用户对数据库中的表仍没有访问权限。
注意：User Name与Login的名称可以相同，也可以不同，但是建议采用相同的名称以方便维护管理。
4、给角色分配语句权限
基于自定义角色分配语句许可权限。在完成以上练习的基础上完成此练习。
1）使用TestSQL帐号登录到查询分析器中
2）将当前数据库切换到OrderMag，执行语句SELECT * FROM Customer
3）执行结果为什么?
拒绝了对对象 'Customer'（数据库 'OrderMag'，所有者 'dbo'）的 SELECT 权限，没有授予TestSQL用户对OrderMag数据库中表的SELECT 权限。
4）在SSMS中，双击角色DBRole，单击“权限”按钮
5）在表Customer所对应的行中，选择“Select”
6）再使用TestSQL帐号登录到查询分析器中
7）将当前数据库切换到OrderMag，执行语句SELECT * FROM Customer
8）执行结果?为什么?
答：获得Customer表中的信息，因为用户TestSQL对表Customer有查询的权限
5、给登录帐号分配固定服务器角色
给帐号分配固定服务器角色，让帐号有管理SQL Server权限。
1） 创建一个新的SecurityAdmin登录帐号
2）双击SecurityAdmin帐号，选择“服务器角色”选项卡
3）选择Security Administrators固定服务器角色，“确定”
4）SecurityAdmin拥有什么权限？
登录，查看master数据库
6、给用户帐号分配固定数据库角色
给用户帐号分配固定数据库角色，让帐号有备份数据库的权限。
1）创建新的登录帐号BackupAdmin
2）在数据库OrderMag上创建用户帐号BackupAdmin
3）BackupAdmin帐号，
4）选择db_backupoperator固定数据库角色，单击“确定”。
5）BackupAdmin拥有什么权限？
备份数据库权限
7．完成实验报告
（1）描述系统的安全机制。
答：在计算机系统中，安全措施是一级一级层层设置的。例如用户在进入计算机系统时，系统首先根据输入的用户标识进行身份鉴定，只有合法的的用户才准许进入计算机系统，对已进入系统的用户，DBMS还要进行存取控制，只允许用户执行合法操作。操作系统一级也会有自己的保护措施，数据最后还可以以密码形式存储到数据库中。
（2）用户帐号的概念，用户帐号与登录帐号的区别。
答：当用户通过身份验证，以某个登录帐号连接到SQL Server以后，还必须取得相应数据库的“访问许可”，才能使用该数据库。这种用户访问数据库权限的设置是通过用户帐号来实现的。
登录帐号是属于服务器的层面。而登录者要使用服务器中的数据库数据时，必须要有用户帐号。就如同在公司门口先刷卡进入大门（登录服务器），然后再拿钥匙打开自己的办公室门（进入数据库）一样。
（3）固定服务器角色的权限、固定数据库角色的权限的概念。
答：固定服务器角色的权限针对数据库服务器，
固定数据库角色的权限针对具体某一数据库（用户）
固定数据库角色是在SQL Server每个数据库中都存在的系统预定义用户组。它们提供了对数据库常用操作的权限。系统管理员可以将用户加入这些角色中，固定数据库角色的成员也可将其他用户添加到本角色中。但固定数据库角色本身不能被添加、修改或删除。
四、 实验小结
通过本实验知道了SQL Server 的认证模式，安全管理的主要内容和方法及用户管理、访问权限管理的基本方法
实验七．备份与恢复（2学时）
一、实验目的：
(1) 了解MS SQL Server 提供的备份、恢复功能
(2) 熟练掌握数据库、日志备份的方法
(3) 熟练掌握数据库恢复的方法
二、实验内容：
在备份设备、文件名上进行完全、差异数据库备份；数据库恢复。
1、创建备份设备
建立备份设备 nw1,nw2
sp_addumpdevice 'disk','Nw1' ,'d:\nw1.bak'
sp_addumpdevice 'disk','Nw2' ,'d:\nw2.bak'
2、备份数据库
使用两种方法进行完全数据库备份。
方法一：在SSMS中进行全库备份
1）在SSMS中右击Northwind数据库，选择“任务” →“备份数据库”。
2）在备份窗口中的“备份”中选择“数据库---完全”选项。
3）在目标中，先单击“添加”，选择“备份设备”中的[Nw1]备份设备。
4）单击“确定”开始备份。
方法二：使用T-SQL语句进行全库备份
1）在查询页中输入并执行语句
backup database northwind to nw2 with init
2）在资源管理器中查看D:\Nw2.bak是否创建？
已创建
3、查看备份设备
查看备份设备中的备份内容，了解备份信息。
1）在SSMS中展开数据库
2）单击[备份]，添加备份设备Nw1
3）右击备份设备[Nw1]
4）单击[查看内容]来查看备份内容。
5）同样的方法查看Nw2备份设备。实验是否成功？
是
4、执行差异备份
在SSMS中进行数据库的 差异备份
1）选择[Northwind]数据库，选择“任务”下面的“备份”
2）在备份窗口中的备份中选择“数据库---差异”选项
3）在备份目的中，先单击“添加”，选择“磁盘上的目标中“备份设备
[Nw1]”
4）在选项页中选择“追加到媒体集”选项，单击“确定”开始进行差异备份。
5）查看备份设备中的内容
5、恢复完全数据库备份
利用上述实验建立的备份，使用两种方法从备份设备中恢复完全数据库备份。
方法一：在SSMS中进行实验
1）在SSMS中右击Northwind数据库创建表table1
2）在SSMS中右击Northwind数据库，选择“任务”下面的“还原”
3）在还原窗口中的下方列表中显示两个备份信息，第一个为完全备份，第二个为差异备份。
4）只选择第一个完全备份，单击“确定”。
5）表table1是否存在？如果存在，右击Northwind数据库选择“刷新”，这时表Ttable1是否存在？
存在，不存在
方法二：使用T-SQL语句进行实验
1）在SSMS中右击[Northwind]数据库创建表table2
2）在查询分析器中输入并执行语句
RESTORE DATABASE
FILE=1,RECOVERY,REPLACE Northwind FROM Nw1 WITH
3）在SSMS中查看表table2是否存在？如果存在，右击Northwind数据库选择“刷新”，这时表Ttable2是否存在？
table2存在，“刷新”后Ttable2不存在
注意：在SSMS中区别完全备份和差异备份图标；使用T-SQL语句时的FILE选项 对应的是完全备份。要经常使用[刷新]选项才能看到最新结果。
6、恢复为其他数据库
1）在SSMS中右击数据库，选择“任务”下面的“还原”
2）在[还原为数据库]中输入新的数据库名称NewNorthwind
3）选择“从设备”单选按钮，单击“选择设备”按钮，单击“添加”按钮
4）选择Nw1设备，单击“确定”。
5）在还原数据库窗体中单击“确定”
6）数据库是否能恢复？否
7）如果有错误对话框弹出，单击“确定”取消对话框。
8）单击“选项”选项卡，然后再单击“确定”
9）数据库是否能恢复？能
7、完成实验报告
（1）“重写现有媒体集”和“追加到媒体集”选项的区别。
答：首先在文件备份上2者没有区别。
"追加到媒体"，将备份追加到备份设备上任何现有的备份中。
"重写现有媒体"，将重写备份设备中任何现有的备份。
（2）完全备份的概念、备份设备的使用。
完全备份： 备份全部选中的文件夹，并不依赖文件的存档属性来确定备份那些文件。（在备份过程中，任何现有的标记都被清除，每个文件都被标记为已备份，换言之，清除存档属性）。
建立备份设备 nw1,nw2
USE master;
GO
EXEC sp_addumpdevice 'disk','Nw1' ,'d:\nw1.bak';
EXEC sp_addumpdevice 'disk','Nw2' ,'d:\nw2.bak';
删除备份设备 nw1,nw2
USE master;
GO
EXEC sp_dropdevice 'disk','Nw1' ,'d:\nw1.bak';
EXEC sp_dropdevice 'disk','Nw2' ,'d:\nw2.bak';
（3）差异备份的概念和差异备份的使用策略。
差异备份基于之前最近一次的数据备份，此次备份称为差异备份的“基准”。差异备份只记录自其基准备份以来更改过的数据。在还原差异备份之前，必须先还原其基准备份。
创建和还原差异备份时，Microsoft SQL Server 2005 将数据库作为一组文件来处理。这会影响差异备份的内容以及差异备份与数据库和文件备份结合使用的方式。使用数据库引擎可以轻松处理常用的方案并且不产生意外行为。
进行差异备份时通常不需要考虑特殊事项。但需要注意一下以下这些情况：
数据库是只读的。
将完全备份和文件备份混在一起，创建了多基准差异备份。
在更改文件组的 IsReadOnly 属性后进行部分差异备份。
对某个只读数据库进行差异备份。
（5）还原数据库的方法。
Microsoft SQL Server 2005 提供了高性能的备份和还原功能。SQL Server 备份和还原组件提供了重要的保护手段，以保护存储在 SQL Server 数据库中的关键数据。实施计划妥善的备份和还原策略可保护数据库，避免由于各种故障造成的损坏而丢失数据。通过还原一组备份并恢复数据库来测试您的策略，为有效地应对灾难做好准备。
“备份”是数据的副本，用于在系统发生故障后还原和恢复数据。备份使您能够在发生故障后还原数据。通过适当的备份，可以从多种故障中恢复，包括：
媒体故障。
用户错误（例如，误删除了某个表）。
硬件故障（例如，磁盘驱动器损坏或服务器报废）。
自然灾难。
此外，数据库备份对于例行的工作（例如，将数据库从一台服务器复制到另一台服务器、设置数据库镜像、政府机构文件归档和灾难恢复）也很有用。
下图说明了由于灾难或其他原因丢失数据后，从数据库的完整备份还原数据的简单情形。
此示例说明了备份和还原的最简单形式，即只将数据库恢复到其最近一次的备份。备份点和故障点之间的所有更新将全部丢失。但是通过添加日志备份，通常可将数据库还原到故障点，而不会丢失数据（如果使用的 SQL Server 版本支持时点恢复）。
四、 实验小结
通过本实验学会了SQL Server的备份与恢复