数据库系统实验在Linux上用MySQL做

前言：

老师要我们下载SQL server 2008R2做的实验，因为某些特殊原因我在Linux上用MySQL做了。

我的知识有限，并不能完成所有实验。

之前老师给了一个叫“pubs”的库让我们做查询语句的练习，后来才自己建库，前面实验的表已经建好并插入数据了，否则下面的部分内容就不能做，因为太简单繁琐了就没有记录。

不知道是操作系统的原因还是语句的原因，虽然感觉SQL server和MySQL两个都差不多但其实还是有细微的差别的。

关于数据库安全性管理那块我在查找了资料以后还是不能做到实验要求的，所以用了SQL server做，嗯图形界面确实比较方便操作。

里面的操作我都尽量截图了，有什么不对的请指正。

下面的实验分别是：

实验五 视图的定义与操作
1. 对应 HRM 数据库，参照前面实验中完成的查询，按如下要求自行设计视图：

（1）基于单个表按投影操作定义视图。 举例：定义一个视图用以查看所有员工的编号、姓名和出生日期。

（2）基于单个表按选择操作定义视图。 举例：定义一个满足 sex=‟true‟的员工的所有信息的视图。

（3）基于单个表按选择和投影操作定义视图。 举例：定义一个视图用以查看部门号码为„2‟的所有员工的姓名、电话和邮件地址。

（4）基于多个表根据连接操作定义视图。 举例：定义一个视图用以查看所有员工的姓名、部门名及工资。

（5）基于多个表根据嵌套查询定义视图。 举例：定义一个比所有财务部的雇员工资都高的雇员的信息的视图

（6）定义含有虚字段（即基本表中原本不存在的字段）的视图。 举例：定义一个视图用以查看所有雇员的编号、姓名、年龄。 2. 分别在定义的视图上进行查询、插入、更新和删除操作，分情况（查询、更新）讨 论哪些操作可以成功完成，哪些不能成功完成，并分析原因。

实验六 数据库完整性的管理

1． 建立课程的实体完整性, 和课程号 Cno 的参照完整性；

2． 对 HRM 数据库，练习建立三个表的主外键约束、唯一约束、取空值约束、用户自定 义的约束（参考 HRM 数据库表定义图中说明）；

3． 建立 salary 表的 Income 字段限定在 0-9999 之间。

实验八 存储过程实验 .

对应于“S+学号”数据库

（1） 创建一个无参存储过程 StuScoreInfo，查询以下信息：班级，学号，姓名，性别， 课程名称，考试成绩。

（2） 创建一个带参数的存储过程 stu_info，该存储过程根据传入的学生编号在 Student 表中查询此学生的信息。

（3） 创建一个带参数的存储过程 StuScoreInfo2，该存储过程根据传入的学生编号和课 程名称查询以下信息:班级，学号，姓名，性别，课程名称，考试成绩。

（4） 编写存储过程，统计离散数学的成绩分布情况，即按照各分数段统计人数。

（5） 编写带参数的存储过程，根据传入的课程名称统计该课程的平均成绩。

 

 

实验5

1. 对应 HRM 数据库，参照前面实验中完成的查询，按如下要求自行设计视图：

（1）基于单个表按投影操作定义视图。

举例：定义一个视图用以查看所有员工的编号、姓名和出生日期。

（2）基于单个表按选择操作定义视图。

举例：定义一个满足 sex=‟true‟的员工的所有信息的视图。

（3）基于单个表按选择和投影操作定义视图。

举例：定义一个视图用以查看部门号码为”2‟的所有员工的姓名、电话和邮件地址。

（4）基于多个表根据连接操作定义视图。

举例：定义一个视图用以查看所有员工的姓名、部门名及工资。

（5）基于多个表根据嵌套查询定义视图。

举例：定义一个比所有财务部的雇员工资都高的雇员的信息的视图

（6）定义含有虚字段（即基本表中原本不存在的字段）的视图。

举例：定义一个视图用以查看所有雇员的编号、姓名、年龄。

2. 分别在定义的视图上进行查询、插入、更新和删除操作，分情况（查询、更新）讨

论哪些操作可以成功完成，哪些不能成功完成，并分析原因。

如上视图为例，做查询、插入、更新和删除的操作

查询视图v4中的Name

在视图v4中插入数据

显示已操作成功

在后来查看视图v4中所有数据的时候没有显示之前插入的数据

但是在Employee中可以查看到之前插入的数据

 

更新

在Employee表中插入数据

在视图v4可以看到已经将数据Employee的录入视图v4

删除

在视图v4中删除Name=”王冰”的操作

在后来查看视图v4时显示已删除

 

实验6

一、可视化界面的操作方法：

1. 实体完整性

（1）将 Student 表的“Sno”字段设为主键：在表设计界面中，单击左边的行选定块，选

定“Sno”字段，单击工具按钮 设置主键。如图 9- 1 所示：

已定义过主键，命令如上图所示，error表示不可定义多个主键

（2）将“SC”表的“Sno”和“Cno”设置为主键：在表设计界面中，单击并按住 Ctrl 键拖动

左边的行选定块，选定 Sno 和 Cno 字段，单击工具按钮 设置主键。

2. 域完整性

（1）将“Ssex”字段设置为只能取“男”，“女”两值：在表设计界面，点击鼠标右键—CHECK

约束,—添加约束，添加 CK_Student_sex 名称，然后在约束表达式框中输入“ Ssex in ('男','女

') ”。如图 9- 2 所示。

38

图 9- 2 CHECK 设置

3. 参照完整性

（1）“Student”表和“SC”表中的“Sno”字段设为参照: 打开“SC”表的设计界面, 点击工具

栏按钮 , 在弹出的属性(properties)对话框中点击“新建”按钮，在“主键表(Primary key

table)”下拉框中选择“Student”表，在其下的字段选择框中选择“Sno”，在“外键表(Foreign key

table)”下拉框中选择“SC”表，在其下的字段选择框中选择“Sno”，单击关闭即可，如图 9- 3

所示。

图 9- 3 参照完整性

命令方式操作方法：

实体完整性

1．将“Student”表的“Sno”字段设为主键：

当“Student”表已存在则执行：

Alter table Student add constraint pk_Sno primary key (Sno)

39

当“Student”表不存在则执行:

Create table Student(Sno CHAR(5) primary key ,

Sname CHAR(10) NOT NULL,

Ssex CHAR(2),

Sage int,

Sdept CHAR(4))

注:可用命令“drop table Student”删除“Student”表

2. 添加一身份证号字段,设置其惟一性.(注: 操作前应删除表中的所有记录)

Alter table Student add id char(18) unique (id)

3. 将“SC”表的“Sno”和“Cno”设置为主键：

当“SC”表已存在则执行：

alter table SC add constraint PK_SnoCno primary key (Sno,Cno)

当“SC”表不存在则执行：

Create table SC(Sno CHAR(5),

Cno CHAR(2),

Grade INT NULL,

constraint PK_SnoCno primary key (Sno,Cno))

域完整性

4. 将“Ssex”字段设置为只能取“男”，“女”两值：

当“Student”表已存在则执行：

alter table Student add constraint CK_Sex check (Ssex in ('男' ,'女'))

当“Student”表不存在则执行：

Create table Student(Sno CHAR(5) primary key ,

Sname CHAR(10),

Ssex CHAR(2) check (Ssex in ('男' ,'女')) ,

Sage int, Sdept CHAR(4))

5. 设置学号字段只能输入数字:

 alter table Student add constraint CK_Sno_Format check (Sno like

'[0-9][0-9][0-9][0-9][0-9]')

6. 设置身份证号的输入格式:

alter table Student add constraint CK_ID_Format check ((id like

'[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_') OR (id

like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'))

7. 设置 18 位身份证号的第 7 位到第 10 位为合法的年份(1900-2050)

40

alter table Student add constraint CK_ID_Format2 check (len(id)=18 and

( (convert(smallint,substring(id,7,4) )>=1900) and(convert(smallint,substring(id,7,4) )<=2050)) )

红色表示没做出来

8. 设置男生的年龄必须大于 22, 女生的年龄必须大于 20.

Alter table Student add constraint CK_age check (Ssex='男' and Sage>=22 or Ssex='女' and

Sage>=20 )

参照完整性

9. 将“Student”表和“SC”表中的“Sno”字段设为参照:

当“SC”表已存在则执行：

alter table SC add constraint FP_Sno foreign key (Sno) references Student(Sno )

当“SC”表不存在则执行：

Create table SC(Sno CHAR(5) constraint FP_Sno

foreign key references Student(Sno),

Cno CHAR(2),Grade INT NULL,

constraint PK_SnoCno primary key (Sno,Cno) )

完整性验证

1．实体完整性：在“Student”表数据浏览可视化界面中输入学号相同的两条记录将会出

现错误如图 5 所示：

图 5

或者在命令窗口输入下面两条命令也会出现错误提示：

Insert into Student values('95003','张三','男',24,'CS','42222919901012903X')

insertinto Student values('95001','李四','女',21,'CS','422229199510129031')

41

图 6

下面的语句用来验证“SC”表中的实体完整性：alter

insert into SC values('95002', '10',65)

insert into SC values('95002', '10',90)

2. 域完整性:

使用下面的语句验证“Ssex”字段的域完整性：

insert into Student values('95009','张匀','大',20,'CS', „42222919901012904X‟)

3．参照完整性：

使用下面的语句“验证”SC 表中的“Sno”字段的域完整性(假设 Student 表中没有学号为“95998”的学生记录)：

insert into SC values('98998', '10',98)

1. 建立课程的实体完整性, 和课程号 Cno 的参照完整性；
2. 

2． 对 HRM 数据库，练习建立三个表的主外键约束、唯一约束、取空值约束、用户自定义的约束（参考 HRM 数据库表定义图中说明）；

主键约束:alter table tbname add primary key(age);

外键约束:Alter table tbname add foreign key(classno) references t_class(cno);

唯一约束: create table tbname(id varchar(20) unique;

取空值约束:null

用户自定义的约束: Alter table Student add constraint CK_age check (Ssex='男' and Sage>=22 or Ssex='女' and Sage>=20 );

3． 建立 salary 表的 Income 字段限定在 0-9999 之间。

 

 

实验七

1． 创建角色 ProgramerRole，拥有创建表，存储过程，视图权限，拥有对 Salary 表的

查询、修改、插入权限

图1 创建角色，赋予限权

实验八

对应于“S+学号”数据库

（1） 创建一个无参存储过程 StuScoreInfo，查询以下信息：班级，学号，姓名，性别，

课程名称，考试成绩。

图1 创建无参存储

图2 创建结果

（2） 创建一个带参数的存储过程 stu_info，该存储过程根据传入的学生编号在 Student

表中查询此学生的信息。

图3 创建带参存储

图4 创建结果

（3） 创建一个带参数的存储过程 StuScoreInfo2，该存储过程根据传入的学生编号和课

程名称查询以下信息:班级，学号，姓名，性别，课程名称，考试成绩。

图5 创建带多参存储

图6 创建结果

（4） 编写存储过程，统计离散数学的成绩分布情况，即按照各分数段统计人数。

图7 创建带参存储的成绩分布情况

（5） 编写带参数的存储过程，根据传入的课程名称统计该课程的平均成绩。

图8 创建带参存储的统计平均成绩

图9 创建结果