SQL Server 实验
第二章：管理数据库和表实例
1. 创建数据库（两种方法：图形界面和Transact-SQL语句）请用语句创建名为mydatabase的数据库，并要求设置其保存路径为f盘，数据文件初始大小为100mb，最大为5000mb，增长为100mb，日志文件初始大小为50mb，最大为2000mb，增长为50mb
Create database mydatabase
On
(name=’mydatabase_dat’,
Filename=’f:\sql\mydatabase_dat.mdf’,
Size=100,
Maxsize=5000,
Filegrowth=100)
Log on
(name=’mydatabase_log’,
Filename=’f:\sql\mydatabase_log.ldf’,
Size=50,
Maxsize=2000,
Filegrowth=50)
2. 自行完成数据库的管理，包括属性配置、收缩数据库、分离数据库、附加数据库
3. 在mydatabase中创建表（两种方法：图形界面和Transact-SQL语句），请用语句创建名为学生成绩表的一个表,表中包含学号、姓名、性别、语文、数学,（数据类型自行定义），并需要设置主键
Create table 学生成绩表
(姓名 varchar(12),
性别 varchar(4),
语文 int,
数学 int,
学号 varchar(12))
4. 向表中插入记录（图形界面和transact语句两种方法都进行尝试），这里不再演示
5. 更改表中的记录（图形界面和transact语句两种方法都进行尝试）
6. 删除表中的记录（图形界面和transact语句两种方法都进行尝试）
7. 在SSMS管理控制台中导入数据库NorthWind，完成如下要求：
1) 查询供应商表： A 返回所有行，并将结果放入新表中 B 返回供应商ID，公司名称，地址 C 返回地区为华南、西南、华东的所有记录 D 返回所有传真为空的所有记录 E
2) 查询产品表：A 查询全部，但只返回前30%记录 B 返回产品ID，产品名称，供应商ID，单价，库存量 C 查询单价大于20，并按降序排序 D 查询库存量大于5且小于100 E 查询所有油类产品（有油字符的） F 查询单价小于15或者大于40
3) 复杂查询：
Ø 返回产品名称，公司名称，单价，并按升序排列（利用供应商表和产品表）：
select 产品.产品名称,产品.单价,供应商.公司名称from 产品,供应商
select 产品.产品名称,产品.单价,供应商.公司名称from 产品,供应商
where 产品.供应商ID=供应商.供应商ID order by 单价
Ø 返回公司名称，货主名称，姓氏，名字（利用订单，雇员，客户表，请使用别名）
select 客户.公司名称,订单.货主名称,雇员.姓氏,雇员.名字
from 客户,订单,雇员
where 订单.客户ID=客户.客户ID and 订单.雇员id=雇员.雇员id
查询尊称为“女士”的雇员姓氏，名字，货主城市，以及雇员ID和公司名称
select 雇员.姓氏,雇员.名字,订单.货主城市,订单.雇员id,客户.公司名称from 雇员,订单,客户
where 雇员.雇员id=订单.雇员id
and 客户.客户id=订单.客户id and 订单.雇员id in(select 雇员id from 雇员where 尊称='女士')
order by 雇员id desc
第三章：安全管理
1. windows身份验证：
Sp_grantlogin ‘login’
Sp_grantlogin ‘login’
例子：允许test域用户xz及sqlusers组连接SQL服务：
允许本地组Users连接SQL服务
sp_grantlogin ‘test\xz’
sp_grantlogin ‘test\sqlusers’
sp_grantlogin ‘BUILTIN\users’
go
Sp_denylogin ‘login’
例子：
– 禁止test域用户xz及sqlusers组连接SQL服务：
– 禁止本地组Users连接SQL服务
sp_denylogin ‘test\xz’
sp_denylogin ‘test\sqlusers’
sp_denylogin ‘BUILTIN\users’
go
删除登录标识：
Sp_revokelogin ‘login’
例子：
从sysxlogins系统表中删除xz及sqlusers
sp_revokelogin ‘test/xz’
sp_revokelogin ‘test/sqlusers’
go
创建SQL登录标识：
Exec Sp_addlogin ‘login’
‘password’
‘database’
‘language’
‘sid’
‘encryption_option’
举例：Exec Sp_addlogin test1
Exec Sp_addlogin test2, password, northwind
修改登录标识：
Exec Sp_password ‘oldpsw’,’newpsw’,’login’
Exec Sp_defaultdb ‘login’,’database’
Exec Sp_defaultlanguage ‘login’,’language
删除SQL登录标识：
exec sp_droplogin ‘login’
2. 数据库角色：
创建和删除自定义角色：
Sp_addrole ‘role’, ‘owner’
– Role为创建的角色
– Owner为角色的拥有者,默认为dbo
Sp_droprole ‘role’
Use northwind
Go
Sp_addrole ‘sales’, ‘dbo’
Go
Sp_addrole ‘manager’
Go
Sp_droprole ‘manager’
Go
为固定服务器角色添加删除成员：
Sp_addrole ‘role’, ‘owner’
– Role为创建的角色
– Owner为角色的拥有者,默认为dbo
Sp_droprole ‘role’
为数据库角色添加删除成员：
Sp_addrolemember ‘role’, ‘account’
Sp_droprolemember ‘role’, ‘account’
– Role为固定或自定义数据库角色
– Account为SQL登录标识或NT帐号
管理数据库用户：
Sp_grantdbaccess ‘login’, ‘name_in_db’
Sp_revokedbaccess ‘name_in_db’
– Login为登录标识
– Name_in_db为在数据库里的用户帐号，默认为同名
3. 权限授予、拒绝、回收
将northwind的订单表的select许可授予所有用户：
Use northwind
Go
Grant select
on 订单
to public
go
将sales表的数据修改许可授予自定义角色salesmanger：
Grant insert,update,delete
on sales
to salesmanger
Go
允许用户xz查看publishers表的所有内容，但只允许修改pub_id列：
Grant select,update{pub_id}
on publishers
to xz
Go
第四章 备份与还原
1、Transanct-SQL语句创建备份设备：
Sp_addumpdevice ‘devicetype’, ‘logical_name’,‘physical_name’
Devicetype：备份设备类型
disk:硬盘文件
tape:磁带设备
pipe:命名管道
Logical_name：备份设备的逻辑名称
Physical_name：物理名称
例1：
Use master
Go
Sp_addumpdevice ‘disk’, ‘backup1’ ,
‘d:\mssql7\backup\backup1.dat’
Go
例2：
Use master
Go
Sp_addumpdevice ‘disk’, ‘backup2’ ,
‘\\xz\backup\backup2.dat’
Go
2、查看备份设备
Sp_helpdevice [‘name’]
v 指定设备的name，可以查看该设备
v 不指定，查看服务器上所有设备
v 返回的cntrltype参数：
0：数据库设备
2：硬盘备份设备
3、4：软盘备份设备
5：磁带备份设备
3、删除备份设备
v Sp_dropdevice ‘device’, [‘delfile’]
v Device：设备的逻辑名称
v Delfile：可选项，指定是否删除对应的物理文件
v 例：
use master
go
sp_dropdevice ‘backup’, ‘delfile’
go
4、备份数据库
v Backup database 数据库名 to 设备名
例如：
Use master
Go
Backup database pubs to backup1
Go
5、备份数据库文件和文件组
v Backup database 数据库名
file=‘文件名’|filegroup=‘文件组名’
to 设备名
6、备份事务日志
v Backup log 数据库名
with no_log
to 设备名
7、数据库恢复
restore database 数据库名 from 设备名
8、恢复事务日志
Restore log 数据库名from 备份设备名
