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 产品 , 供应商 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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’

例子：允许 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’,  ‘backup<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />1’,

       ‘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 备份设备名