数据库上机第二次实验内容

实验3：T-SQL语言实验

实验目的：

要求学生熟练掌握使用T-SQL语言创建基本表和视图，同时利用SQL语言实现各种查询和更新。

实验内容：

利用“查询分析器”实现“学生-课程”数据库中的各个基本表的创建。库中表结构为：

学生（学号，姓名，性别，年龄，所在系）：

课程（课程号，课程名，先行课，学分）；

选课（学号，课程号，成绩）。

要求：

ｌ）建库、建表、建立表间联系。

２）选择合适的数据类型。

３）定义必要的索引、列级约束和表级约束。

具体实验任务如下。

１．基本操作实验

1)启动SQL Server2000下的企业管理器（Enterpriser Manager），逐个点开各

级小图标直到打开“数据库”，点击“数据库”右键，利用“新建数据库”建立“学生_课程”数据库如图3.1所示；

2）启动SQL Server2000下的“查询分析器”，选择当前正在使用的数据库“学生_课程”，在查询分析器界面输入以下代码如图3.2-3.4后，并点击工具栏里的“√”（命令分析）和“◢”（执行命令）后，从而创建“学生”表、“课程”表，“选课”表。（在企业管理器的菜单栏中点击“刷新”图标，即可在“学生_课程”数据库的表对象中看到这三个表。）

2．利用T-SQL语言向各个已经设计好的表中插入具体信息。在各个新建查询中输入如图3.3-3.5所示代码，点击工具栏里的“√”（命令分析）和“◢”（执行命令）后在企业管理器中打开各个表查看结果。

图3.1新建“学生_课程”数据库界面

图3.2 利用SQL语言创建“学生”表、“课程”表，“选课”表

图3.3向学生表中插入记录

图3.4向课程表中插入记录

图3.5向选课表中插入记录

3．利用T-SQL语言实现表的连接查询和嵌套查询

1）连接查询：

①求选修课程号为2且成绩在85分以上的学生学号、姓名及成绩：

T-SQL语句：select 学生.学号,姓名,成绩 from 学生,选课 where 学生.学号=

选课.学号 and 课程号=’C1’and 成绩>85

②查询每一门课的间接先行课（即先行课的先行课）:

T-SQL语句：select A.课程号,A.课程名,B.先行课 from 课程 A，课程 B where A.先行课=B.课程号

2）嵌套查询：

①求选修了数学的学生学号和姓名。

T-SQL语句：select 学号,姓名 from 学生 where 学号 in (select 学号 from 选课 where 课程号 in(select 课程号 from 课程 where 课程名=‘数学’))

②求其他系中比计算机系学生年龄都小的学生。

T-SQL语句：select * from 学生 where 年龄'计算机'

③求至少选修了学生为“200212122”的学生所选修的全部课程的学生学号的姓名。

T-SQL语句： Select 学号,姓名 from 学生 where not exists(select * from 选课选课1 where 选课1.学号=’200515122’and not exists(select * from 选课选课2 where 学生.学号=选课2.学号 and 选课2.课程号=选课1.课程号))

实验报告要求：

1）写出实验目的、实验内容、实验结果和主要实验步骤。

2）列出实验前准备的实验数据。写出相关的T-SQL语句。

3）写出实验中出现的问题、注意事项和解决方法。

4）写出实验体会。

实验4：数据库的视图，触发器的定义及使用实验

实验目的：

使学生掌握SQL Server中的视图创建向导和图表创建向导的使用方法，加深对视图和SQL Server图表作用的理解。学会通过T-SQL表达触发器和创建触发器的方法。

实验内容：

1、在SQL Server 企业管理器中，用视图创建向导创建这３个视图。

①建立计算机系学生的视图

②由学生、课程和选修课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩

③将学生的学号、总成绩、平均成绩定义成一个视图

基本操作如下：

在SQL Server 企业管理器中利用视图向导来创建视图，打开企业管理器，选中“学生_课程”数据库，点击“工具”下面的“向导”，创建视图；如图4.1所示，点击“创建视图向导”就可以一步步创建所要的视图，完成之后可在“学生_课程”数据库的“视图”对象中看到这三个表。）

。（例：在图4.2中给创建计算机系学生视图的相应的条件）。

图4.1所示

图4.2在创建视图的过程中给出条件界面。2．用企业管理器创建触发器的方法：建立缺省和捆绑缺省

1）为选课表建立一个插入触发器,保证学生_课程库选课表的参照完整性的触发器：

T-SQL语句：

实验报告要求：

5）写出实验目的、实验内容、实验结果和主要实验步骤。

6）写出创建这三个视图的相关的T-SQL语句。

7）写出实验中出现的问题、注意事项和解决方法。

8）写出实验体会。

实验5：数据安全性实验（选做内容）

实验目的：

通过实验使学生加深对数据安全性的理解，掌握SQL Server中有关用户、角色及操作权限的创建和管理方法

实验内容：

数据库的安全性实验。在SQL Server企业管理器中，设置SQL Server的安全认证模式，实现对SQLＳerver的用户和角色管理，设置和管理数据操作权限。实验要求：

１．基本操作实验

1)在SQL Server 企业管理器中为所属的SQL服务器设置WindowsNT安全认证模式。

2)在SQL Server 企业管理器中为自己建立一个服务器用户、数据库用户和数据库角色。并将自己创建的学生选课库、图书借阅库的所有操作权赋予它们，将自设计的数据库的操作权赋予新建的数据库用户。

(1)设置安全认证模式

在企业管理器中扩展开SQL 服务器组，用鼠标右键单击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，选择“安全性”，如图5.1。

图5.1 设置安全认证模式

(2)登录（服务器用户）的创建和管理

1）安全性文件夹

进入企业管理器，打开SQL服务器组和SQL服务器，选择“安全性”文件夹，可以看出“安全性”文件夹包含4个对象：登录、服务器角色、链接服务器和远程服务器，如图5.2所示。

图5.2 安全性文件夹图

2）用鼠标右键单击登录对象，出现的弹出菜单，在弹出菜单中选择”新登录“，就会出现一个”登录属性“对话框，如图5.3所示。

图5.3 登录属性

3）选择“常规”选项卡，输入登录用户的一般特征，包括输入用户名，选择该用户的安全认证模式，选择默认数据库和默认语言。设置时，如果使用SQL Server身份验证，可直接在名称栏中输入新登录名；如果选择Windows 身份验证，则要单击“名称”文本框右边的“…“按钮，调出windows预设的登录用户表，从中选择添加的登录名称。

4）选择“服务器角色“选项卡，选中”服务器角色“，使该登录用户成为相应的服务器角色成员，在下面的“描述”栏中，列出了当前被选中的服务器角色的权限，如图5.4所示。

图5.4 服务器角色权限

5）选择“数据库访问”选项卡，在页面上部的列表中确定用户能访问的数据库，在下部的列表中确定登录用户所属的数据库角色，如图5.5所示，通过单击前端的复选框实现。

图5.5 登录用户的数据库角色选择

6）操作完成后，单击“确定”按钮

(3)数据库用户的创建

1）在企业管理器中扩展SQL 服务器及数据库文件夹；用鼠标右键单击“用户”，出现用户弹出菜单，选择“新建数据库用户”项，出现“新建数据库用户”对话框，如图5.6。

图5.6 新建数据库用户

2）在对话框“登录名”列表框图选择一个SQL服务器登录用户名，并在它下面的“用户名”文本框中输入数据库用户名，在下面的“数据库角色成员”列表中选择参加的角色。

3）单击“确定”按钮。

(4)登录用户加入服务器角色

1）在企业管理器中，扩展指定的SQL服务器、安全性文件夹，单击“服务器角色”后，就会在右面的细节窗口中出现8个预定义的服务器级角色。选中一个服务器角色，例如Database creators，单击鼠标右键，出现弹出菜单，如图5.7所示。

图5.7 数据库用户访问许可

2）选择“属性”项后，出现“服务器角色属性”对话框，选择“常规”选项卡，单击“添加”按钮后出现“选择登录用户”对话框；在对话框中选择登录用户名后，单击“确定”回到“常规”选项卡，如图5.8所示。

图5.8 服务器角色常规选项卡

3）选择“权限”选项卡，可以看到该服务器角色可以执行的全部操作权限，如图5.9。

图5.9 服务器角色权限

4）单击“确定”。

实验报告要求：

1）写出实验目的、实验内容、实验结果和主要实验步骤。

2）列出新建的登录用户、数据库角色和用户属性及权限情况。

3）写出实验中出现的问题、注意事项和解决方法。

4）写出实验体会。