复习要点和答案

复习要点

1、关系数据库的基本概念

2、数据库的逻辑结构和物理结构、数据库的分类（各个系统数据库的作用）、

用sql语句创建、管理和删除数据库。

SQL Server数据库是存储数据的容器，是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。用户观点将数据库称为逻辑数据库，组成数据库的逻辑成分称为数据库对象。SQL Server 2005的数据库对象主要包括表、视图、索引、存储过程、触发器和约束等。

从物理结构看，SQL Server2005将数据库映射为一组操作系统文件，一般一个数据库被组织成数据文件和日志文件两种类型。

●系统数据库

系统数据库是在SQL Server2005的每个实例中都存在的标准数据库，用于存储有关SQL Server的信息，SQL Server使用系统数据库来管理系统。

?master数据库

master数据库是SQL Server2005中的总控数据库，是最重要的系统数

据库。系统是根据master数据库中的信息来管理系统和其他数据库。如

果master数据库信息被破坏，整个SQL Server系统将受到影响，用户

数据库将不能被使用。

?model数据库

model（模板）数据库是为用户建立新数据库提供模板和原型，它包含

了将复制到每个新建数据库中的系统表

?msdb数据库

msdb数据库支持SQL Server代理。当代理程序调度作业、记录操作时，系统要用到或实时产生很多相关信息，这些信息一般存储在

msdb数据库中。

?tempdb数据库

tempdb数据库是一个临时数据库，保存所有的临时表、临时数据以及临时创建的存储过程。

?resource数据库

resource数据库是一个只读和隐藏的数据库，包含SQL Server2005所有的系统对象。（系统对象在物理上保存在resource 中，

逻辑上显示在每个数据库的sys构架中）

3、用sql语句创建、删除、管理表结构；插入、修改、删除表数据；各种约束

的创建和作用（主键约束、唯一性约束、检查约束、默认约束、外键约束），

数据完整性的分类和实现。

实体完整性约束、参照完整性约束、函数依赖约束、统计约束四类。

实体完整性实体完整性是指一个关系中所有主属性（即主码的属性）不能取空值。所谓“空值”就是“不知道”或“无意义”的值。如主属性取空值，就说明某个不可标识的实体，这与现实世界的应用环境相矛盾，因此这个实体一定不是完整的实体。

参照完整性约束参照完整性约束是指参照关系中外码的取值或者是空值（外码的每个属性均为空值）或者是取被参照关系中某个元组的主码值。下面举例说明。现有两个关系模式：学生（学号，姓名，性别，专业号，年龄）和专业（专业号，专业名）。在实现参照完整性时要注意以下几个问题：①外码是否可以接受空值。因为外码能否为空是依赖于应用环境的，如有两个关系模式分别为选修（学号，课程好，成绩）学生（学号，姓名，性别，年龄，所在系），选修关系中的外码“学号”不能为空，如果为空，说明某个不知学号的学生选修了某门课程，这与学校的应用环境不符。②删除被参照关系的原组时的考虑。有时需要删除被参照关系的某个原组，而参照关系中又有若干原组的外码值与被删除的被参照关系中的主码值相对应，这时要进行级联删除，即将参照关系中的所有外码值与被参照关系中要删除元组主码值相对应的元组一起删除，如果参照关系同时又是另一个关系的被参照关系则这种级联删除应该级联删除应该级联下去。③修改被参照关系中主码时的考虑。

遇到这种情况时，做法同删除被参照关系的元组类似，要做级联修改，既修改被参照关系中主码值的同时，用相同的方法修改参照关系中相应的外码值。

函数依赖约束大部分函数依赖约束都是隐含在关系模式结构中，特别是规范化程度较高的关系模式（如3NF或BCNS）都由模式来保持函数依赖。在实际应用中，为了不使信息过于分离，一般不能过分地追求规范化。这样在关系的字段间就可以存在一些函数要显式地表示出来。

系统约束即某个字段值与一个关系多个元组的统计值之间的约束关系。如，本部门经理的工资不得高于本部门职工的平均工资的5倍。其中职工的平均工资值是一个统计计算值。在许多场合，统计数据往往可以公开，而个别数据却是保密的，但是个别数据值可以从统计数据推断出来，所以要采取一定的防范措施防止数据泄密。

4、各类数据查询语句（基本查询、分组查询、连接查询、嵌套查询）

5、索引的类型和作用，用sql语句创建索引。

索引的类型和作用，用sql语句创建索引。

聚集索引将表中的记录在物理数据页中的位置按索引字段值重新排序，再将重排后的结果写回到磁盘上。

非聚集索引

唯一索引确保索引键不包含重复的值。

复合索引在对数据表创建索引时，有时创建基于单个字段的索引不能满足查询要求，这时需要对表创建多个字段的索引，这样的索引

称为复合索引。

索引视图如果视图基表的数据量很大，并且不常被更新，而在查询中频繁地引用这类视图，可通过对视图创建唯一聚集索引来提高性

能。

全文索引一种特殊类型的基于标记的功能性索引，由Microsoft SQL Server全文引擎（MSFTESQL）服务创建和维护。用于帮助

在字符串数据中搜索复杂的词。

XML索引当一个查询是基于XML数据类型列时，为了提高查询速度

6、视图的概念，用sql语句创建、修改和删除视图

视图是个虚表，是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。

7、数据库对象的命名规则、局部变量的创建和赋值方法、用户自定义函数的类

型，各类自定义函数的创建和调用、各种流程控制语句的使用方法

8、游标的使用方法（填空）、事务的概念和作用（简答）、使用方法。（选择）

a)事务（transaction）是SQL Server 中的单个逻辑工作单元，也是一个

操作序列，它包含了一组数据库操作命令。一个事务内的所有语句被作

为一个整体执行。在事务执行过程中，如果遇到错误，则可以回滚事务，取消该事务所做的全部改变，从而保证数据库的一致性和完整性。因此，

事务是一个不可分割的工作逻辑单元，一个事务中的语句要么全部正确

执行，要么全部不起作用。

b)事务作为一个逻辑工作单元必须具有四个属性：原子性（Atomicity）、

一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

这四个属性简称ACID属性。

9、各类存储过程的创建和使用方法（不带参数、带输入参数、带输出参数、

带返回值）

10、触发器与存储过程的区别和联系简答），DML触发器的创建和使用

11、Sqlserver2005身份验证模式，登录账户、用户、角色的创建和使用方法，权限的授予、拒绝和回收

12、备份的作用、类型，恢复模式对备份的影响，利用sql语句备份和恢复数据库。

题型：

一、选择（20题，20分）全部内容

二、判断（10题，10分）全部内容

三、简答（2题，10分）见要点

四、综合应用题（本题共11小题，共60分）

示例：

现有关系数据库如下：

数据库名：员工管理,包括如下两张表

（1）员工表(员工号 char(6)，姓名char(10)，性别 char(2)，民族 char(10)学历 char(4),出生日期 datetime, 工作时间 datetime,身份证号

char(18)，部门号 char(3))

（2）部门表(部门号 char(3)，部门名称 char(20),备注 varchar(100)) 写出或补充完整实现如下功能的sql语句代码：

1、创建“员工管理”数据库，包含一个数据文件和一个日志文件，保存在目录D:\data\ 下。数据文件的逻辑名为employee_data，物理文件名为employee__data.mdf，初始大小为10MB，最大可增加至50MB，增幅为10％；日志文件的逻辑名为employee__log，物理文件名为employee__log.ldf，初始大小为5MB，最大值为100MB，以2MB增幅增加。请将创建数据库代码填写完整。（每空1分，共4分）

2.假设数据库和部门表已经创建，写出创建员工表的代码。(8分)

要求在建表时设置主键(员工号)和外键(部门号)，并添加如下约束：姓名字段不允许为空（not null）、民族字段设置默认值（defualt）为’汉’、身份证号设为唯一（unique）、性别字段只能取’男’或‘女’（check）3.将下列部门信息添加到部门表，添加完成后将部门号为‘001’的部门名称修改为“信息中心”，然后删除部门号为‘002’的部门信息(4分)

部门号部门名称

001 财务部

002 组织部

4.查询员工表中年龄大于50岁的员工信息，并将结果按部门排序。（4分）

5.分组统计每个部门的员工人数，并输出员工人数大于10人的部门号和人数。(4分)

6.查询与“张三”在同一个部门的员工姓名，学历和民族。(4分)

7.创建视图employeeview,包括字段员工号, 姓名, 性别 , 民族, 身份证号,

部门名称等字段（4分）。

8．自定义函数fun1,利用该函数可以查看某部门是否编制已满，如果部门人数小于20人，则显示“编制未满”,否则显示“编制已满”，并调用该函数查询‘001’部门编制情况。（每空2分，共8分）

9.定义游标dep_cursor查询001部门的员工姓名和总人数.请将下面程序补充完整（每空2分，共8分）。

10.创建带参数的存储过程Pro_dep,输入一个部门号，可以查看该部门的员工姓名, 性别和部门名称，并执行此过程，查询'001’部门情况。 (8分)

11.为员工管理数据库创建一个sqlserver登陆账户，登陆名为T1，密码是123456;然后创建一个数据库用户T1，其登录名为T1，并将查询、修改部门表的权限赋予该用户。（4分）

答案：

1、CREATE DATABASE员工管理

ON

(NAME= employee_data,

FILENAME='d:\data\employee_data.mdf',

SIZE=10mb,

MAXSIZE=50mb,

FILEGROWTH=10%

)

log on

(NAME=class_log,

FILENAME='d:\data\class_log.ldf',

SIZE=5mb,

MAXSIZE=100mb,

FILEGROWTH=2MB)

2、（8分，建基本表和主键4分，添加约束4分）

Create table员工表

(员工号char(6)primary key,

姓名char(10)not null,

性别char(2)check(性别='男'or性别='女'),

民族char(10)default'汉',

出生日期datetime,

工作时间datetime,

身份证号char(18)unique,

部门号char(3)references部门表(部门号)

)

注：所有约束可以设置在列之后，也可以单独设定。当主码中字段多余一个时必须单独设定。如上题可在所有字段定义后，用primary key(员工号)，

foreign key部门号 references 部门表(部门号)单独设定主码和外码

3、（每题1分，共4分）

insert into部门表values('001','财务部')

insert into部门表values('002','组织部')

update部门表set部门名称='信息中心'where部门号='001'

delete from部门表where部门号='002'

4、（共4分）

select*

from员工表

where(year(getdate())-year(出生日期))>50

order by部门号

5、（共4分）

select部门号,count(员工号)

from员工表

group by部门号

having count(员工号)>10

6、（4分）

Select姓名,学历,民族

from员工表

where部门号=(select部门号from员工表where姓名='张三')

7、（4分）

create view employeeview as

select员工号,姓名,性别,民族,身份证号,部门名称

from员工表,部门表

where员工表.部门号=部门表.部门号

8、（每空2分，共8分）

create function fun1(@depnum as char(3))--创建函数

returns char(10)

begin

declare @depcount int,@dep char(10)

--找出该部门的人数，并赋值给变量

select @depcount=count(员工号)from员工表where部门号=@depnum if @depcount<20

select @dep ='编制未满'

else

select @dep ='编制已满'

return @dep

end

select dbo.fun1('001')as部门编制情况

9、（每空2分，共8分）

declare dep_cursor cursor--声明游标

For select姓名

From员工表

Where部门号='001'

open dep_cursor --打开游标

--定义使用的变量

declare @name varchar(10),@list varchar(300),@cnt int

set @list='001部门有：'

set @cnt=0

--从游标中取出一条数据存入变量@name

fetch next from dep_cursor into @name

while(@@fetch_status=0)--循环取出数据直到最后一条begin

set @list=@list+@name+','--将该员工姓名加入列表中

set @cnt=@cnt+1 --将部门人数加

fetch next from dep_cursor into @name --取出下一条数据

end

--将该部门员工姓名和总人数放入到变量@list

set @list=@list+'共'+cast(@cnt as varchar)+'人'

close dep_cursor --关闭游标

print @list --打印该部门员工姓名和总人数。

10 （共8分）

create procedure Pro_dep @depnum char(3)

as

select姓名,性别,部门名称

from employeeview

where部门号=@depnum

go （6分）

execute Pro_dep '001'（2分）

11（共4分）

Create login T1 with password='123456'（1分）

Use员工管理

Create user T1 from login T1 （1分）

grant select,update on部门表to T1 （2分）