数据库原理与技术实验指导书（管理）

数据库原理与技术实验指导书

计算机与通信工程学院

实验一 SQLSERVER 2000的安装

一、学时：２学时 二、实验目的

了解安装SQL SERVER 2000的软、硬件环境以及安装过程；

三、实验准备

1．SQL SERVER 2000安装盘； 2、 计算机

四、实验方法及步骤

1、 了解SQL SERVER 2000的版本介绍 SQL SERVER 2000不同版本的软件环境要求 SQL Server 版本或组件 操作系统要求 Microsoft Windows NT Server 4、0、Microsoft Windows NT Server 4.0 企业版、Windows 2000 Server、Windows 2000 Advanced Server 和 Windows 2000 Data Center Server。 注意：SQL Server 2000的某些功能必须要求在Microsoft Windows 2000 Server（任何版本）上运行。 Microsoft Windows NT Server 4.0、Windows 2000 Server、Microsoft Windows NT Server 企业版、Windows 2000 Advanced Server 和 Windows 2000 Data Center Server。 Microsoft Windows Me、Windows 98、Windows NT Workstation 4.0、Windows 2000 Professional、Microsoft Windows NT Server 4.0、Windows 2000 Server 和所有更高级的 Windows 操作系统。 Microsoft Windows NT Workstation 4.0、Windows 2000 Professional和所有其它Windows NT和Windows 2000 操作系统。 Microsoft Windows Me、Windows 98、Windows NT Workstation 4.0、Windows 2000 Professional、Microsoft Windows NT Server 4.0、Windows 2000 Server 和所有更高级的 Windows 操作系统。 Microsoft Windows NT 4.0、Windows 2000（所有版本）、Windows Me 和 Windows 98。 企业版 标准版 个人版 开发版 桌面引擎 仅客户端工具 仅连接 Microsoft Windows NT 4.0、Windows 2000（所有版本）、Windows Me、Windows 98 和 Windows 95。 2. SQL SERVER 2000的安装

(1)将企业版安装光盘插入光驱后，出现以下提示框。

注意：如果您的计算机的操作系统是windows 95以上，则选择“安装SQL Server 2000 组件”，如果操作系统是windows 95,则需要选择“安装SQL Server 2000的先决条件”。

由于SQL Server 2000 的某些功能要求在Microsoft Windows 2000 Server以上的版本才能运行。因此安装Windows Server 2000(建议为Advanced版本)，可以学习和使用到SQL Server 2000的更多功能，以及享受更好的性能。

本安装将在Windows 2000 Advanced Server操作系统作为示例，详细介绍安装SQL Server 2000企业版的过程。大家清首先安装Windows 2000 Advanced Server。

(2)选择 \安装 SQL Server 2000 组件\，出现下一个页面。

(3)选择 \安装数据库服务器\，出现如下界面：

(4)选择\下一步\，出现如下界面：

(5)选择’本地计算机’（默认）选项，选择\下一步\，出现如下界面：

在 \安装选择\窗口，选择 \创建新的SQL Server实例...\。对于初次安装的用户，应选用这一安装模式，不需要使用 \高级选项\进行安装。 \高级选项\中的内容均可在安装完成后进行调整。选择\下一步\，出现如下界面：

(13)然后就是约10分钟左右的安装时间，单击‘完成’系统安装完毕。

附：企业管理器和查询分析器的介绍

企业管理器和查询分析器是SQLSERVER 2000 实验的主要工具，下面分别对这两类工具进行介绍： 1、企业管理器：

（1）按图所示找到SQLSERVER 2000 中的企业管理器；

（2）用鼠标单击企业管理器菜单项，进入企业管理器管理界面：

根据图形界面，可进行数据库、表等的管理。

2、查询分析器：

所有的SQL语句操作都在查询分析器中运行，对查询分析器的使用要熟练掌握。 （1）按图所示找到SQLSERVER 2000 中的查询分析器；

（2）用鼠标单击查询分析器菜单项，进入查询分析器界面：

（3）若以Windows身份验证进入，可直接点击‘确定’；若以SQL server 身份验证，可输入登陆用户名及登陆密码；‘sa’是超级用户；身份验证通过后，可进入查询分析器界面如下：

在查询分析器界面中，左边是数据库构成对象；右边分上下两部分：上面部分是查询分析器的SQL语句编辑部分，实验中的SQL语句在此部分进行编辑；下边部分是SQL语句的执行结果区域，SQL语句的执行结果在此部分显示。查询分析器的使用注意以下问题：

(a)SQL语句所作的操作都是基于当前数据库的，进入界面的默认数据库是master数据库，如不在 master数据上操作，请在界面上部切换到你要操作的数据库。

(b)SQL语句执行时，用鼠标点击界面工具条中的绿色箭头；语句执行完毕后结果显示在界面右部的下面的区域。

(c)SQL语句的执行是从第1句开始执行，为了仅执行部分SQL语句，可采用两重方法：

第一是用鼠标选种执行的部分，然后点击绿色箭头进行执行；

第二是把不执行的SQL语句用注释符号屏蔽掉。注释符号有两种。‘--’符号是行注释，仅注释1 行；‘/* */ ’是多行内容注释，把不执行的语句放在‘/*’与‘*/’之间。

(d)SQL语句可保存在磁盘上。

实验一 SQL Server2005 管理工具及其使用

实验目的：

(1) 掌握管理工具SMSS的使用

(2) 掌握登录用户与数据库用户的创建 (3) 掌握用户权限的授予与回收 (4) 掌握数据库的备份与恢复

实验内容：

一、 管理工具SMSS的使用 1. 启动SMSS

Microsoft SQL Server Management Studio 是SQL Server 2005 中最重要的管理工具，是一个可视化集成管理环境，用于访问、配置、控制、管理和开发 SQL Server 2005的所有组件。

选择”开始菜单”/”程序”/”Microsoft SQL Server 2005”/”SQL Sever Management Studio”

得到如下界面：

在”服务器名称(s)”栏中输入服务器名称：可以是服务器名，也可以服务器IP

地址.

在”身份验证（A）栏中”选择身份验证的方式，有两种选择方式：Windows集成认证和 SQL server 身份验证。在登录名和密码栏中输入用户名和相应的密码。 系统默认的管理员为sa,密码为空。

SMSS可以管理SQL Server 2000和SQL Server 2005两种实例。

2. 创建和运行查询

在SMSS管理界面中，单击工具栏上“新建查询”按钮，即可编辑SQL查询

语句,如下图

在下图中右边窗口中键入SQL语句,按”F5”即可运行SQL查询语句。

3. 数据库的分离与附加

(1)数据库分离

数据库中的数据文件(扩展名为.mdf和.ndf)及日志文件(扩展名为.ldf)可以从数据库中分离出来(Detach),单独保存，可供在不同机器实例之间进行数据传递。操作步骤是：

展开“数据库”，选中要分离的数据库（比如：xskcsjk）,然后单击右键，选择：任务/分离(D)即可。如下图

（2）数据库的附加(A)： 选中“数据库”，单击右键选择：附加（A）

在弹出的对话框中，选择数据文件所在的文件夹与文件：如下图

在上图中单击“添加”按钮，选择要附加的数据库文件(.mdf及.ldf)

二、 用户创建与权限管理

1. 登录名和用户名管理

(1)要连接SQL SERVER服务器需一个登录名: sp_addlogin 登录名,密码 sp_droplogin

创建一个登录名: userxxx, 其中xxx表示学号的后三位,密码为123456 sp_addlogin userXXX, 123456 (2)创建数据库用户名： 将登录名关联到数据库：

use <数据库名>

sp_adduser <登录名> sp_dropuser <用户名>

如：将登录名userXXX关联到数据库xskcsjk

Use xskcsjk Go

Sp_adduser userXXX 2. 权限管理

(1)将表student的查询权限授予用户：userXXX Grant Select On student to userXXX

然后以用户userXXX进行登录，看能否访问表student (2)将用户userXXX对表student的查询权限回收 Revoke Select On student from userXXX

三、 数据库的备份与恢复

1. 数据库备份：

试着利用SMSS和SQL语句进行数据库的完全备份、差异备份、日志备份

Alter Database xskcsjk set Recovery simple (设置恢复模式为简单模式simple，默认恢复模式为完全模式full)

Backup database xskcsjk to disk=’d:\\xskcsjk_full.bak’ with init (简单模式下完全备份)

Backup database xskcsjk to disk=’d:\\xskcsjk_diff.bak’ with init,differential (简单模式下差异备份)

Backup database log xskcsjk to disk=’d:\\xskcsjk_log.bak’ with init (要在完全模式下或大容量模式下Bulk_logged) 2. 数据库恢复：

试着利用SMSS和SQL语句进行数据库的完全恢复、差异恢得、及日志恢复 Restore Database xskcsjk from disk=’d:\\xskcsjk_full.bak’ (完全恢复) 差异恢复时，先要进行一次完全恢复，再进行差异恢复：

Restore Database xskcsjk from disk=’d:\\xskcsjk_full.bak’ with NoRecovery Restore database xskcsjk from disk=’d:\\xskcsjk_diff.bak’

完全恢复与日志恢复的混合策略：先进行一次完全模式下的恢复，再进行日志恢复 如：

Restore Database xskcsjk from disk=’d:\\xskcsjk_full2.bak’ with NoRecovery Restore Log xskcsjk from disk=’d:\\xskcsjk_log.bak’

注意：此处完全备份文件xskcsjk_full2.bak是在完全模式下的完全备份

实验二 创建数据库和创建表

一、学时：2学时 二、实验目的

熟悉和掌握数据库的创建和连接方法； 熟悉和掌握数据库表的建立、修改和删除；

加深对表的实体完整性、参照完整性和用户自定义完整性的理解； 三、实验准备

1、熟悉SQL SERVER 工作环境；

2、复习有关表的建立、修改和删除的SQL语言命令 四、实验内容：用SQL语言完成下列操作。

1、 创建一个教学数据库，数据库的名称为MIS；

2、 创建MIS数据库中的5个关系模式，要求包含完整性约束的定义。 系（系编号，系名称）

教工（教工号，姓名，性别，职称，工资，系编号）

学生（学号，姓名，性别，出生年月，专业，家庭地址，系编号） 课程（课程编号，课程名称，学时，系编号）

成绩（学号，课程编号，分数） 五、 SQL语句的参考脚本：

1、 在SQL Server中使用批处理和脚本两个术语。批处理是一个以“go”

结束的SQL语句集，这些语句是作为一批执行并一起提交的。 “go”是SQL Server2000批处理的结束标志，不是SQL语句。

2、 create database 语句将创建一个数据库，在使用这个数据库时，

还需要打开这个数据库，打开语句是use。

3、下面这段SQL脚本程序，可以在查询分析器中打开这个文件，也可以

直接将下面这段SQL脚本复制（粘贴）到查询分析器的编辑窗口。为了简化中文输入的烦琐操作过程，在实验脚本子目录下，还有一个全英文的脚本文件。

/* 注释：实验一中文脚本.sql 创建数据库 */ create database MIS go

/* 打开数据库 */ use MIS

/* 创建5个表 */ create table 系

(系编号 smallint not null , 系名称 char(12), primary key (系编号)) go

create table 课程(

课程编号 char(8) not null, 课程名称 char(16), 学时 smallint, 系编号 smallint,

primary key (课程编号),

foreign key (系编号) references 系(系编号)) go

create table 教工( 教工号 smallint, 姓名 char(8), 性别 char(2), 职称 char(10), 工资 numeric(8,2),

系编号 smallint, primary key (教工号),

foreign key (系编号)references 系(系编号)) go

create table 学生( 学号 char(6), 姓名 char(8), 性别 char(2),

出生年月 datetime, 系编号 smallint,

primary key (学号),

foreign key (系编号) references 系(系编号)) go

create table 成绩(

学号 char(6) not null,

课程编号 char(8) not null, 分数 numeric(4,1),

primary key (学号 ,课程编号),

foreign key (学号) references 学生(学号),

foreign key (课程编号) references 课程(课程编号)) go

六、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

2、在查询分析器的命令编辑窗口输入SQL语句，点击执行键，将

执行SQL语句。参见执行SQL语句的演示。

3、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，

再点击执行键，执行选择的语句。参见执行SQL语句的演示。 4、注意：当多个SQL语句执行时，可能出现的逻辑错误，即就单个语句来说，语句没有错误，但多条语句顺序执行时可能出错。例如，上述5个表创建的顺序必须是“先父后子”，而删除的顺序则是“先子后父”。 DROP TABLE 成绩 DROP TABLE 学生 DROP TABLE 教工 DROP TABLE 课程 DROP TABLE 系

5、保存调试通过的SQL程序。

实验三 数据插入、更新、与删除

一、学时：２学时 二、实验目的

熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用； 加深理解表的定义和数据更新的作用 三、实验准备

1、建立数据库student、表S、C、SC以及索引

2、复习对表中数据的插入、修改和删除的SQL语言命令； 四、实验内容：根据教材§3.4的内容，用SQL语言完成下列操作。

1、已创建的MIS数据库的5个表中，插入如下样本数据。

系 课程

系编号 101 102 103 104 系名称 数学 计算机 外语 经济 课程号 课程名 学时 C101 C102 C103 C104 数学 英语 计算机 经济学 68 85 102 51 系编号 101 103 102 104

教工号 姓 名 2101 2203 2405 2104 2302 2205 葛小平 李长江 姜立伟 张丽丽 康立华 王伟平 教工 职称 教授 副教授 副教授 讲师 教授 讲师 工 资 3420.00 3190.00 3140.00 2243.00 3740.00 2130.00 系编号 101 102 104 101 103 102 性别 女 男 男 女 女 男

成绩

学 号 991022 991022 992124 992124 992124 994021 课程编号 分数 C101 C102 C101 C102 C103 C104 88 67 77 95 45 87

学生

学 号 姓 名 性别 出生年月 系编号 族别 991022 08/05/1980 101 田平平 女 汉 992124 郭黎明 男 03/04/1981 102 汉 994021 何明慧 女 04/12/1982 104 回 991223 姜明明 男 12/05/1980 101 苗 993012 何漓江 男 10/05/1979 103 汉 992104 康纪平 女 03/04/1981 102 汉

994125 康嘉家 男 07/05/1980 104 汉 2、 用SQL语言完成下列操作

(1)在系表中插入一行数据{105,’管理’}

(2)向教工表中插入一行数据{2001,'葛小平’,’女’授’,3420.00,102}

(3)向教工表中插入一个教工号、姓名和工资

数据是{2109,’田新民’,2650}

(4)将教工表的姓名和工资拷贝到一个酬金表中。 (5)将教工表中职称为“教授”的工资增加15%。 (6)将教工表中职称不是教授的人，工资增加10%。 (7)从教工表中删除教工号为2001的教工。 (8) 删除酬金表的所有数据。 (9) 删除酬金表。

(10)在系表中添加一个电话号码属性，属性类型为CHAR(8)。 ALTER TABLE 系 ADD 电话号码 CHAR(8)

（11）修改系表中电话号码属性的宽度为CHAR(13)。

ALTER TABLE 系 ALTER COLUMN 电话号码 CHAR(13) (12)删除系表中电话号码属性。

ALTER TABLE 系 DROP COLUMN 电话号码

(13)在学生表中删除何漓江同学的信息，包括他选课的信息。 提示：先删除子表中的选课信息，再删除主表中的信息。 思考：为什么必须这样做？

五、 SQL

语句的参考脚本：

1、 在5个表中插入数据

,’教 insert into 系 values(101,'数学') insert into 系 values(102,'计算机') insert into 系 values(103,'外语') insert into 系 values(104,'经济') go

insert into 课程 values('c101','数学',68,101)

insert into 课程 values('c102','英语',85,103) insert into 课程 values('c103','计算机',102,102) insert into 课程 values('c104','经济学',51,104) go

insert into 教工 values('2101','葛小平','女','教授',3420.00,101) insert into 教工 values('2203','李长江','男','副教授',3190.00,102) insert into 教工 values('2405','姜立伟','男','副教授',3140.00,104) insert into 教工 values('2104','张丽丽','女','讲师',2243.00,101) insert into 教工 values('2302','康立华','女','教授',3740.00,103) insert into 教工 values('2205','王伟平','男','讲师',2130.00,102)

go

insert into 学生 values('991022','田平平','女','08/05/1980',101) insert into 学生 values('992124 ','郭黎明','男','03/04/1981',102) insert into 学生 values('994021 ','何明慧','女','04/12/1982',104) insert into 学生 values('991223 ','姜明明','男','12/05/1980',101) insert into 学生 values('993012 ','何漓江','男','10/05/1979',103) insert into 学生 values('992104 ','康纪平','女','03/04/1981',102) insert into 学生 values('994125 ','康嘉家','男','07/05/1980',104) insert into 学生 values('991134 ','包立琪','女','03/14/1981',101) insert into 学生 values('994115 ','王海洋','男','04/13/1982',104) insert into 学生 values('991354 ','王立平','女','12/05/1981',101) go

insert into 成绩 values('991022','c101',88)

insert into 成绩 values('991022','c102',67) insert into 成绩 values('992124','c101',77) insert into 成绩 values('992124','c102',95)

insert into 成绩 values('992124','c103',45) insert into 成绩 values('994021','c104',87) insert into 成绩 values('994021','c102',78) insert into 成绩 values('994021','c103',67) insert into 成绩 values('991223','c101',66) insert into 成绩 values('991223','c102',89) insert into 成绩 values('993012','c102',93) insert into 成绩 values('993012','c103',84)

2、 完成实验操作的SQL语言

--（1）在系表中插入一行数据{105,’管理’}。

insert into 系 values (105,'管理') --（2）向教工表中插入一行数据{2001,’葛小平’,’女’,’教授’,3420.00,102}。

insert into 教工 values (2001,'葛小平','女','教授',3420.00,102) --（3）向教工表中插入一个教工号、姓名和工资，数据是{2109,’田新民’,2650}。

insert into 教工(教工号,姓名,工资) values (2109,'田新民',2650)

--（4）将教工表的姓名和工资拷贝到一个酬金表中。 --创建一个酬金表。

create table 酬金 (教工姓名 char(8), 工资 numeric(8,2)) --将教工表中姓名和工资两列数据拷贝到酬金表中。

insert into 酬金 (教工姓名,工资) select 姓名,工资 from 教工

--（5） 将教工表中职称为“教授”的工资增加15%。

update 教工 set 工资=工资*1.15 where 职称='教授' --（6） 将教工表中职称不是教授的人，工资增加10%。

update 教工 set 工资=工资*1.1 where 职称 NOT LIKE '教授' --（7） 从教工表中删除教工号为2101的教工。

delete from 教工 where 教工号 = '2101' --（8） 删除酬金表的所有数据。

delete from 酬金 --（9） 删除酬金表。

drop table 酬金

--（10）在系表中添加一个电话号码属性，属性类型为char(8)。 alter table 系 add 电话号码 char(8)

--（11）修改系表中电话号码属性的宽度为char(13)。 alter table 系 alter column 电话号码 char(13) --（12）删除系表中电话号码属性。

alter table 系 drop column 电话号码

--（13） 在学生表中删除学号为993012 ‘的学生信息，包括他选课的信息。

delete from 成绩 where 学号 ='993012 ' delete from 学生 where 学号 = '993012 ' 六、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

2、在查询分析器的命令编辑窗口输入SQL语句，点击执行键 ?，将执行SQL语句。参见执行SQL语句的演示。

3、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，再点击

执行键，执行选择的语句。参见执行SQL语句的演示。

4、 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在

子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。

5、保存调试通过的SQL程序。

实验四 数据查询

一、学时：4学时 二、实验目的

熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练地使用SQL 语句的各种形式； 三、实验准备

1、熟悉SQL SERVER 工作环境；

2、复习对表中数据查询的SQL语言命令； 四、实验内容

1、简单查询操作

（1）列出所有教授的姓名和工资：

SELECT 姓名,工资 FROM 教工

WHERE 职称= '教授' （2）列出教授的所有信息。

SELECT * FROM 教工

WHERE 职称='教授'

（3）列出教工表中的系编号并消除重复的元组。

SELECT DISTINCT 系编号 FROM 教工

（4）已知学分=学时/17，计算每一门课程的学分数。

SELECT 课程名称,学时/17 AS 学分 FROM 课程

（5）显示教授的工资和提高10%的工资额。

SELECT 姓名,工资,工资*1.1 AS '工资*1.1' FROM 教工 WHERE 职称='教授'

（6）显示田平平同学出生100天的日期。

SELECT 姓名, 出生年月+100 AS '出生年月+100' FROM 学生

WHERE 姓名='田平平'

（7）列出教工表中工资在3000元以上的名单。

SELECT 姓名 FROM 教工

WHERE 工资>= 3000

（8）列出学生表中1980年1月1日之后出生的学生名单。

SELECT 姓名 FROM 学生

WHERE 出生年月>= '1980-1-1'

（9）列出学生表中在1980年1月1日之后出生的男同学名单。

SELECT 姓名 FROM 学生

WHERE 出生年月>= '1980-1-1' AND 性别='男' （10）列出教工表中教授或副教授中工资低于3000元的名单。

SELECT 姓名 FROM 教工 WHERE (职称='教授' OR 职称='副教授')AND 工资<3000 （11）显示男学生的姓名和所在的系名称。

SELECT 姓名,系名称 FROM 学生,系

WHERE 学生.系编号=系.系编号 AND 性别='男' 或

SELECT 姓名,系名称 FROM 学生 R,系 S

WHERE R.系编号=S.系编号 AND 性别='男' （12）列出教工中比姜立伟工资低的姓名和工资。

SELECT R.姓名,R.工资 FROM 教工 R, 教工 S

WHERE R.工资

SELECT 姓名 FROM 教工

WHERE 职称 NOT LIKE '教授'

（14）检索工资在1000元到2000元范围内的职工信息。

SELECT * FROM 职工

WHERE 工资 BETWEEN 1000 AND 2000 等价于

SELECT * FROM 职工

WHERE 工资>=1000 AND 工资<=2000

（15）按学号的升序，显示系编号等于101的学生信息：

SELECT *

FROM 学生

WHERE 系编号=101 ORDER BY 学号

（16）求教工表中教授工资的平均值。

SELECT AVG (工资) AS 平均工资 FROM 教工 WHERE 职称='教授'

（17）求所有教工的工资总和。

SELECT SUM (工资) AS 工资总和 FROM 教工

（18）找出教工中最高工资和最低工资。

SELECT MAX(工资)，MIN(工资) FROM 教工

（19）显示学生中最早和最晚的出生日期。

SELECT MIN (出生日期), MAX (出生日期) FROM 学生

（20）查询学生的总人数。

SELECT COUNT(*)AS 人数 FROM 学生 2、复杂查询

（21）如果要统计成绩表中所有选课的学生人数，即不管一名学生选了几门课程，都只计算一次

SELECT COUNT (DISTINCT (学号)) AS 人数 FROM 成绩

（22）统计学生表中男生和女生的人数。

SELECT 性别, COUNT (*) FROM 学生 GROUP BY 性别

（23）统计成绩表中，每一门课程的平均成绩。

SELECT 课程编号,AVG (分数) FROM 成绩

GROUP BY 课程编号

（24）查询教工表中每一种职称的最高工资和最低工资。

SELECT 职称, MAX (工资), MIN (工资) FROM 教工 GROUP BY 职称

（25）统计成绩表中选修人数超过2以上的课程编号和人数。

SELECT 课程编号,COUNT (*) FROM 成绩

GROUP BY 课程编号 HAVING COUNT(*)>2 （26）列出成绩表中分数在60以上、选课数大于2且平均分超过70的学

号、选课数目和平均分。

SELECT 学号,COUNT(课程编号 ), AVG (分数) FROM 成绩

WHERE 分数>=60 GROUP BY 学号

HAVING COUNT(课程编号)>2 AND AVG(分数) >70 (27)统计“CS”系学生的人数；

(28)统计各系学生的人数，结果按升序排列；

(29)按系统计各系学生的平均年龄，结果按降序排列； (30)查询每门课程的课程名；

(31)查询无先修课的课程的课程名和学时数；

(32)统计无先修课的课程的学时总数；

(33)统计每位学生选修课程的门数、学分及其平均成绩； (34)统计选修每门课程的学生人数及各门课程的平均成绩；

(35)找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的

升序排 列；

(36)查询选修了“1”或“2”号课程的学生学号和姓名； (37)查询选修了“1”和“2”号课程的学生学号和姓名；

(38)查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学

号、姓名和成绩； (39)查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；

(40)查询没有选修课程的学生的基本信息； (41)查询选修了3门以上课程的学生学号；

(42)查询选修课程成绩至少有一门在80分以上的学生学号； (43)查询选修课程成绩均在80分以上的学生学号； (44)查询选修课程平均成绩在80分以上的学生学号； 五、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

在查询分析器的命令编辑窗口输入SQL语句，点击执行键 ?，将执行SQL语句。参见执行SQL语句的演示。

2、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，再点

击执行键，执行选择的语句。参见执行SQL语句的演示。

3、 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然

后在子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。 4、保存调试通过的SQL程序。

实验5 复杂查询操作

一、学时：4学时 二、实验目的

掌握连接和嵌套查询操作； 三、实验准备

1、熟悉SQL SERVER 工作环境；

2、复习对表中数据查询的SQL语言命令； 四、实验内容

1、 打开MIS数据库，用SQL语言完成下列问题。

（1）根据学生、课程和成绩表，输出“计算机”课程的成绩单，包括姓

名和分数。

（2）查询所开课程的选修情况，包括没有任何学生选修的课程。 （3）从学生、课程和成绩表中，产生数据库课程的成绩单。 2、假设有贷款关系如图1所示。

贷款

帐号 G10020 T20078 K65743 H89765 H76890 图1 贷款关系

姓名 关平之 李连玉 姜海洋 田平平 方一华 金额 20033.00 30092.00 30200.60 19080.40 （4）创建贷款关系。

create table 贷款 ( 帐号 char(20), 姓名 char(8),

金额 char(10))

insert into 贷款 values('G10020','关平之', '20033.0' ) insert into 贷款(帐号,姓名) values('T20078','李连玉' ) insert into 贷款 values('T65743','姜海洋', '30092.00 ')

insert into 贷款 values('H89765','田平平', '30200.60') insert into 贷款 values('H76890','方一华', '19080.40') （5）找出贷款表中金额为空值的帐号和姓名。 （6）找出贷款表中贷款金额非空的帐号和姓名。 3、 假设有导师与研究生2个表，如图2所示。

研究生 导师

教工号 T001 T002 T005 T008 姓名 江海 代宁 潘涛 田立 性别 男 女 男 女 学号 99001 99002 99003 99004 99006 图2 导师与研究生表

姓名 李南 刘星 王海 张力 郭天

导师号 T001 T002 T002 T001

（7） 查询研究生与其导师的情况。 SELECT *

FROM 研究生A INNER JOIN教师 B ON A.导师号= B.教工号 （也可以写成等价的SQL语句： SELECT *

FROM 研究生A,导师 B WHERE A.导师号= B.教工号）

（8）计算导师与研究生关系的笛卡儿乘积。 SELECT *

FROM 导师 CROSS JOIN 研究生

（9）查询教师指导研究生的情况，包括不指导研究生的教师。 SELECT *

FROM 导师 A LEFT OUTER JOIN 研究生 B ON (A.教工号= B.导师) （10）查询研究生和教师的情况，包括没有导师的研究生和没有指导研究

生的教师。

SELECT *

FROM 研究生 A FULL OUTER JOIN 导师 B ON (A.导师号=B.教工号)

（11）根据学生、课程和成绩表，输出“计算机”课程的成绩单，包括姓

名和分数。

SELECT 姓名，分数

FROM 学生 A JOIN 成绩 B ON（A.学号=B.学号） JOIN课程 C ON（B.课程编号=C.课程编号） WHERE C.课程名称=’计算机’ 等价的SQL语句：

SELECT 姓名,分数

FROM 学生 A,成绩 B,课程 C

WHERE A.学号=B.学号 AND B.课程编号= C.课程号 AND C.课程名='

计算机'

（12）查询所开课程的选修情况，包括没有任何学生选修的课程。

SELECT 学号, 课程名称, 分数

FROM 成绩 A RIGHT OUTER JOIN 课程 B ON (A.课程编号=B.课程编号)

（13）从学生、课程和成绩表中，产生数据库课程的成绩单。

SELECT 姓名,课程名,分数 FROM 学生 S,课程 C,成绩 G

WHERE S.学号=G.学号 AND G.课程编号=C.课程号 AND C.课程名='

数据库'

或

SELECT 姓名，课程名称，分数 FROM 学生 S JOIN 成绩 G ON (S.学号=G.学号) JOIN课程 C ON (G.课程编号=C.课程编号) WHERE C.课程名称=’数据库’

（14）找出贷款表中金额为空值的帐号和姓名。 （15）找出贷款表中贷款金额非空的帐号和姓名。 （16） 查询研究生与其导师的情况。

SELECT *

FROM 研究生A INNER JOIN教师 B ON A.导师号= B.教工号 也可以写成等价的SQL语句： SELECT *

FROM 研究生A,导师 B WHERE A.导师号= B.教工号

（17）查询教师指导研究生的情况，包括不指导研究生的教师。 SELECT *

FROM 导师 A LEFT OUTER JOIN 研究生 B ON (A.教工号= B.导师) （18）查询研究生和教师的情况，包括没有导师的研究生和没有指导研究

生的教师。

SELECT *

FROM 研究生 A FULL OUTER JOIN 导师 B ON (A.导师号=B.教工号)

（19）查询所开课程的选修情况，包括没有任何学生选修的课程。

SELECT 学号, 课程名, 分数

FROM 成绩 A RIGHT OUTER JOIN 课程 B ON (A.课程编号=B.课程

编号)

（20） 查询研究生与其导师的情况。

SELECT *

FROM 研究生A INNER JOIN教师 B ON A.导师号= B.教工号 也可以写成等价的SQL语句： SELECT *

FROM 研究生A,导师 B WHERE A.导师号= B.教工号

（21）查询教师指导研究生的情况，包括不指导研究生的教师。

SELECT *

FROM 导师 A LEFT OUTER JOIN 研究生 B ON (A.教工号= B.导师)

五、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

2、在查询分析器的命令编辑窗口输入SQL语句，点击执行键 ?，将执行SQL语句。参见执行SQL语句的演示。 3、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，再点击

执行键，执行选择的语句。参见执行SQL语句的演示。

4、 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后

在子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。 5、保存调试通过的SQL程序。

实验六 视图和索引的定义和使用

一、学时：2学时 二、实验目的

熟悉掌握对数据表中视图的定义操作和SQL命令的使用； 熟悉掌握对数据表中视图的查询操作和SQL命令的使用；

熟悉掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系；

灵活熟练的进行视图的操作，认识视图的作用； 掌握建立与删除索引； 三、实验准备

1、熟悉SQL SERVER 工作环境； 2、复习有关视图操作的SQL语言命令； 3、复习有关索引操作的SQL语言命令； 四、实验内容

1、 建立视图

（1）建立数学系学生的视图，并要求进行修改和插入操作时仍需保证

该视图只有数学系的学生，视图的属性名为学号，姓名，出生年月，系编号。

CREATE VIEW C_Student AS

SELECT 学号, 姓名, 出生年月, 系编号 FROM 学生

WHERE 系编号=’数学’

WITH CHECK OPTION

（2） 建立学生的学号、姓名、选修课程名及成绩的视图。

本视图由三个基本表的连接操作导出，其SQL语句如下： CREATE VIEW Student_CR AS

SELECT 学生.学号, 姓名, 课程名, 分数 FROM 学生,分数 , 课程 WHERE 学生.学号=成绩.学号 AND成绩.课程编号=课程.课程编号 （3） 定义一个反映学生出生日期的视图。

CREATE VIEW Student_birth(学号, 姓名, 出生年月) AS SELECT 学号, 姓名, 出生年月 FROM 学生 2、 删除视图

（4） 删除视图Student_CR。

DROP VIEW Student_CR;

3、查询视图

（5）在数学系的学生视图C_Student中找出女学生姓名和出生年月。

SELECT 姓名, 出生年月 FROM C_Student WHERE 性别=‘女’;

说明：本例转换后的查询语句为：

SELECT 姓名, 出生年月

FROM 学生

WHERE 系编号=’数学’ AND性别=‘女’

（6）在Student_CR视图中查询成绩在85分以上的学生学号、姓名和

课程名称。

SELECT 学号, 姓名, 课程名 FROM Student_CR WHERE 成绩>85; 4、更新视图 （7） 将数学系学生视图C_Student中学号为S05的学生姓名改为“黄海”。

UPDATE C_Student

SET 姓名='黄海' WHERE 学号='S05';

说明：DBMS自动转换为对基本表的更新语句如下： UPDATE 学生 SET 姓名='黄海'

WHERE 学号='S05' AND 系编号='数学';

（8） 向数学系学生视图C_Student中插入一个新的学生记录，其中

学号为“S09”，姓名为“王海”，出生年月为“07/05/1980”。 INSERT

INTO C_Student

VALUES ('S09', '王海', '07/05/1980, ’数学’);

（9） 删除数学系学生视图C_Student中学号为“S09”的记录。 DELETE

FROM C_Student WHERE 学号=’S09’ 5 、建立索引

（10）为学生选课数据库中的学生、成绩、课程三个表建立索引。其

中学生表按学号升序建唯一索引，课程表按课程号升序建唯一索引，成绩表按学号升序和课程编号降序建唯一索引。其语句为：

CREATE UNIQUE INDEX Stu_Sno ON学生（学号）; CREATE UNIQUE INDEX Cou_Cno ON 课程(课程编号); CREATE UNIQUE INDEX Rep_Scno ON (学号ASC, 成绩DESC); （11）在基本表学生表姓名和学号列上建立一个聚簇索引，而且学生

中的物理记录将按照姓名值和学号值的升序存放。其语句为： CREATE CLUSTERED INDEX Stu_Sname_Sno ON 学生Students(姓名,

学号);

6 、删除索引

（12） 删除成绩表上的Rep_SCno索引。 DROP INDEX成绩.Rep_Scno;

7、 现场综合练习

（1）在课程表上创建一个唯一、非聚簇复合索引（按学号升序、课程

号降序）。

（2）创建视图“教师信息”，用于保存何珊所教班级的所有任课教师

的姓名、学时数、课程号。再将C802的学时数改为80。 （3）创建视图“选修门数”，用于保存选修课门数在1门以上的学生

学号、门数。再显示选修课门数为2门的学生学号。

（4）将学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

并查询结果;

（5）将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果; （6）定义一个反映学生出生年份的视图V_YEAR并查询结果;

（7）将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并

查询结果;

（8）将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G并查询

结果;

（9）查询平均成绩为90分以上的学生学号、姓名和成绩；

（10）查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩； （11）按系统计各系平均成绩在80分以上的人数，结果按降序排列； （12）通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改

为“S1_MMM”,”S4_MMM” 并查询结果;

（13）通过视图V_IS，新增加一个学生记录 (‘S12’,’YAN

XI’,19,’IS’)，并查询结果；

（14）通过视图V_IS，新增加一个学生记录 ('S13','YAN

XI',19,'MA')，并查询结果；

（15）通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查

询结果；

（16）要通过视图V_S_C_G，将学号为“S12”的姓名改为“S12_MMM”，

是否可以实现？

（17）要通过视图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，

是否可以实现？ 五、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

2、在查询分析器的命令编辑窗口输入SQL语句，点击执行键 ?，将执行

SQL语句。参见执行SQL语句的演示。

3、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，再点击

执行键，执行选择的语句。参见执行SQL语句的演示。 4. 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在

子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。

5、保存调试通过的SQL程序。

实验七 安全性管理

一、学时：2学时 二、实验目的

掌握账户的建立。 掌握权限的分配与回收。 掌握数据的备份与恢复。 三、实验准备

1．熟悉SQL SERVER 2000的登录、认证方式； 2．熟悉对数据操作权限的设置

3. 熟悉数据备份设备的建立、数据备份和恢复的方法。 四、 实验内容

1、使用企业管理器完成

（1）、用企业管理器建立登录账户：user1，user2，user3。

（2）、将user1，user2，user3映射为“学生－课程”数据库的用户。 （3）、授予user1，user2，user3具有对S、C、SC三张表的查询权。 （4）、授予user1具有对S、C表的插入、删除权。

（5）、在“学生－课程”数据库中建立用户角色ROLE1，把S表的插入、

删除、查询权授予它，并将user1，user2添加到此角色中。 （6）、回收user1对S表的查询权。 （7）、备份数据库。 （8）、还原数据库。 2、操作步骤：

（1）、打开企业管理器，点击“安全性”，然后右击“登录”图标，选

择“新建登录”。建立三个登录帐户。

（2）、在学生－课程数据库中选择用户，右键点击“用户”，在弹出的

快捷菜单中选择“新建数据库用户”。把三个登录账户映射为学生－

课程数据库的三个用户。

（3）、在学生－课程数据库中选中一个用户，右击它，在弹出的快捷菜

单中选中“属性”，在对话框中点击“权限”，给这个用户分配权限。

另外两个用户也依次进行。在学生－课程数据库中选中“角色”，右击它，在弹出的快捷菜单中选中“新建角色”，建立角色ROLE1，同时将三个用户添加到此角色中。

（4）、为了给角色分配权限，可以在右击已经建立好了的角色，选择快

捷菜单中的“属性”。点击“权限”，在对话框中给ROLE1分配权限。 （5）、使用企业管理器回收user1对S表的查询权。

（6）、备份数据库。右击要备份的数据库，从“所有任务”里面选择“备

份数据库”，实现对数据库的备份。

（7）、还原数据库。打开企业管理器，选中“数据库”图标，右击它，

从快捷菜单中选择“所有任务”中的“还原数据库”，根据备份数据实现数据库的还原。

（8）、导入导出数据。将一个表中的数据导出到一个EXCEL文件中。 可

在企业理器中右击要导出的表，在弹出的快捷菜单中选择“所有任务”→“导出数据”，按向导可将表中的数据导出到目的位置。 3、使用查询分析器命令完成

（1）、建立一个U1的登录用户、数据库用户

EXEC sp_addlogin ‘U1’,’123’,’mis’ Use mis

Exec sp_grantdbaccess ‘u1’

（2）、 使用相同的方法创建u2,u3,u4,u5 （3）、把查询学生表权限授给用户U1

GRANT SELECT ON 学生 TO U1;

（4）、把对学生表和课程表的全部权限授予用户U2和U3

GRANT ALL ON 学生, 课程 TO U2, U3; （5）、把对表成绩的查询权限授予所有用户 GRANT SELECT ON 成绩

TO PUBLIC;

（6）、把查询学生表和修改学生学号的权限授给用户U4 GRANT UPDATE(学号), SELECT

ON 学生 TO U4;

（7）、把用户U4修改学生学号的权限收回 REVOKE UPDATE(学号) ON 学生 FROM U4;

（8）、收回所有用户对表成绩的查询权限

REVOKE SELECT

ON 成绩

FROM PUBLIC;

（9）、把用户U5对成绩表的INSERT权限收回 REVOKE INSERT ON 成绩

FROM U5 CASCADE ;

（11）创建角色 r_test

EXEC sp_addrole 'r_test'

（12）授予 r_test 对 MIS 表的所有权限

GRANT ALL ON MIS TO r_test

（13）授予角色 r_test 对 学生 表的 SELECT 权限

GRANT SELECT ON 学生 TO r_test

（14）添加登录 l_test,设置密码为pwd,默认数据库为MIS

EXEC sp_addlogin 'l_test','pwd','MIS'

（15）为登录 l_test 在数据库 MIS 中添加安全账户 u_test

EXEC sp_grantdbaccess 'l_test','u_test' （16）添加 u_test 为角色 r_test 的成员

EXEC sp_addrolemember 'r_test','u_test'

（17）拒绝安全账户 u_test 对 学生 表的 SELECT 权限

DENY SELECT ON 学生 TO u_test

/*--完成上述步骤后,用 l_test 登录,可以对jobs表进行所有操作,但无法对学生表查询,虽然角色 r_test 有学生表的select权限,但已经在安全账户中明确拒绝了对学生的select权限,所以l_test无学生表的select权限--*/

（18）从数据库 MIS 中删除安全账户

EXEC sp_revokedbaccess 'u_test' （19）删除登录 l_test

EXEC sp_droplogin 'l_test' （20）删除角色 r_test

EXEC sp_droprole 'r_test' 五、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

2、在查询分析器的命令编辑窗口输入SQL语句，点击执行键 ?，将执行

SQL语句。参见执行SQL语句的演示。

3、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，再点击

执行键，执行选择的语句。参见执行SQL语句的演示。

4、 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在

子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。

5、保存调试通过的SQL程序。

实验八 数据完整性和触发器

一、学时：2学时 二、实验目的

熟悉和掌握使用SQL查询分析器用PRIMARY KEY、CHECK、FOREIGN KEY??REFERENCES、NOT NULL、UNIQUE等关键字验证SQL SERVER 2000的实体完整性、参照完整性及用户定义完整性。 熟悉触发器的定义和使用 三、实验准备

1、熟悉SQL SERVER 工作环境；

2、复习有关建表操作的SQL语言命令； 3、复习触发器的定义和使用命令； 四、实验内容

1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性 在查询分析器窗体下键入如下命令：

CREATE TABLE Student1(Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(8), Ssex CHAR(1),

Sage INT,

Sdept CHAR(20), CONSTRAINT PK_Student PRIMARY KEY(sno))

运行后插入、更改数据并观察结果；

2、利用SQL查询分析器用FOREIGN KEY??REFERENCES子句保证参照完整性

在查询分析器窗体下键入如下命令：

CREATE TABLE SC(sno CHAR(5) NOT NULL UNIQUE, cno CHAR(5) NOT NULL UNIQUE, grade INT,

CONSTRAINT FK-SC FOREIGN KEY(sno,cno)

REFERENCES ( Student(sno),Course(cno)) ON DELETE CASCADE); 运行后插入、更改数据并观察结果；

3、利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性

CREATE TABLE Student(sno CHAR(5) ,

sname CHAR(8) CONSTRAINT U1 UNIQUE, ssex CHAR(1) ,

sage INT CONSTRAINT U2 CHECK FOR sage<=28, sdept CHAR(20)，

CONSTRAINT PK-Student PRIMARY KEY(sno)) INSERT INTO student(sno,ssex,sdept,sage) VALUES （‘95020’，’男’，‘1S’，38）；

运行后插入、更改数据并观察结果；

4、 触发器。

触发器可以看成是一类特殊的存储过程，在满足某个特定条件时自

动触发执行，是提高数据库服务器性能的有力工具。触发器分为三类，更新触发器、插入触发器和删除触发器。能够定义触发器的用户有：表的所有者； 系统管理员；拥有创建触发器的权限，且拥有对操作对象的相应的操作权限的用户。

（1） 创建学生表上的触发器，实现删除学生表中元组时自动删除成绩表中引用该元组的相关数据。

1）首先查看学生表中的“管理关系”的设置，确保关系FK_学生_

成绩对“INSERT”和“update”不发生作用

2）测试删除学生表中的 “991022”

DELETE FROM 学生 WHERE 学号=“991022” 3） CREATE TRIGGER del_stu ON学生 FOR DELETE

AS DELETE学生 FROM 学生 a,deleted b WHERE a.学号=b.学号

4） 在企业管理器中查看触发器，展开MIS数据库，单击“表”，

选择“学生” →“所有任务” →“管理触发器”命令，弹出“触发器属性”对话框，在名称下拉框选择del_stu，显示该触发器的定义，进而可以修改触发器的定义和删除触发器。

5） 验证触发器del_stu的有效性，执行

DELETE FROM 学生 WHERE 学号=“991022” 五、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

2、在查询分析器的命令编辑窗口输入SQL语句，点击执行键 ?，将执行

SQL语句。参见执行SQL语句的演示。

3、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，再点击

执行键，执行选择的语句。参见执行SQL语句的演示。

4、 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在

子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。

5、保存调试通过的SQL程序。

实验九 存储过程的使用

一、学时：2学时 二、实验目的

熟练掌握使用SQL SERVER 2000创建和执行存储过程的方法。 熟练掌握存储过程的删除操作。 三、实验准备

1．熟悉SQL SERVER 2000设计环境； 2．熟悉存过过程的创建方法、步骤 四、实验内容

1、利用企业管理器或查询分析器创建proc_s存储过程。在查询分析器中

建立存储过程的命令如下： create procedure proc_s as

select * from s

2、使用EXECUTE语句执行存储过程并观察结果。命令如下：

exec proc_s 3、利用企业管理器或查询分析器创建proc_goods存储过程。在查询分析

器中建立存储过程的命令如下： create procedure proc_goods @toyname varchar(20)

as

select * from s where sname=@toyname

4、使用EXECUTE语句执行存储过程并观察结果。命令如下： exec proc_goods

5、在查询分析器中使用drop procedure删除上面建立的存储过程。

五、 实验过程

1、进入查询分析器，参见进入查询分析器演示。

2、在查询分析器的命令编辑窗口输入SQL语句，点击执行键 ?，将执行

SQL语句。参见执行SQL语句的演示。

3、在调试程序过程中，可以用鼠标选择某些要执行的SQL语句，再点击

本文来源：https://www.bwwdw.com/article/1v2t.html