《数据库技术》第三次实验参考代码
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1. insert into xs values ('000001','黎平','通信工程','1','1980-03-10','50','NULL')
2. select 学号,姓名,性别,专业名 into xs1 from xs
3. insert into xs1 select 学号,姓名,性别,专业名 from xs
add constraint xm 3.定义检查约束: 成绩:
unique (姓名)
alter table xs_kc add constraint cj check (成绩>=0 and 成绩<=100)
性别:
alter table xs add constraint xb check (性别='0' or 性别='1')
备注: ALTER TABLE xs ADD CONSTRAINT dtrq DEFAULT convert(varchar(50),getdate()) FOR 备注
5．定义规则： Create rule rule_学号 As @学号 like’[0-3][0-3][0-9][0-9][0-9][0-9]’
（2）定义唯一约束：xs 表的姓名字段。
（3）定义检查约束，如下表所示。
字段名
约束要求
性别
男或女
成绩
大于等于 0 小于等于 100
专业名
计算机,通信工程,测控
（4）定义默认值，如下表所示。
字段名
默认值
专业名
计算机
备注
当天的日期
（4）定义规则，要求学号输入格式为：学号共 6 位，前两位数字范围在 0－3 之间，后 4
Leabharlann Baidu
位数字范围在 0－9 之间。并绑定到 XS 表的学号字段上。 参考答案：
1.定义 xs_kc 表的主键约束(学号,课程号)
alter table xs_kc add constraint xh_kch primary key (学号,课程号)
2.定义惟一约束: xs 表的姓名字段
alter table xs
实验 4 学生成绩数据的插入、修改、删除
实验步骤 以 xscj 数据库为例，完成以下操作：
（1） 使 用 INSTERT 语 句 添 加 记 录 （ ’000001’ ， ’ 黎 平 ’ ， ’ 通 信 工 程 ‘ ， ’ 男’，’1980-03-10’,’50’）到 XS 表中。
（2） 根据 XS 表创建一个学生表(XS1)，该表包括学号，姓名,性别,专业名。（采用 SELECT…INTO 语句）
1. 创建视图
create view v_xs1
as select * from xs
where 专业名=‘计算机’and 总学分>50
2. 创建视图
create view v_xs
as select xs.学号,姓名,专业名,课程名,成绩
from xs,kc,xs_kc
where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号 and xs.总学分>50
1. select * from xs select 姓名 from xs
2. select top 10 * from xs
select top 10 percent *from xs 3. select 学号,姓名,专业名 from xs where 总学分<50 4. select 学号,课程号,成绩*0.7 as '考试所占分数' from xs_kc
Go Exec sp_bindrule ‘rule_学号’, ‘XS.学号’
实验 6 视图和存储过程的使用
实验内容 以 XSCJ 数据库为例，使用查询分析器和企业管理器完成以下操作： (1) 创建一个叫 v_xs1 的视图, 查询总学分大于 50 的计算机专业学生的信息。
(2) 创建一个叫 v_xs 的视图,查询总学分大于 50 的所有学生的学号,姓名,专业和选修的课 程名及成绩 (3)创建一个叫 proc_info 的存储过程，查询计算机专业的所有学生的信息。 (4)创建一个带参数叫 proc_student 的存储过程，查询某个学生的信息。 (5)删除 v_xs 视图 (6)删除 proc_student 存储过程 参考答案：
4. update kc set 课程号='0203' where 课程号='206' 5. delete from xs where 学号='000001'
6. delete xs1
truncate table xs1
实验 5 学生成绩数据完整性设计
实验内容
以 xscj 数据库为例，使用查询分析器和企业管理器完成以下操作： （1）定义 xscj 数据库中各表的主键和外键约束。
)
CREATE TABLE xs_kc (
学号 CHAR(6) , 课程号 CHAR(3), 成绩 tinyint NULL, CONSTRAINT pk_学号_课程号 PRIMARY KEY (学号,课程号) )
上面的SQL代码调试正确之后，请将xscj数据库整个删除，再进
行下面的操作。
带完整数据的 XSCJ 数据库的还原：先下载我课件中的 homework 备份。
NAME = xscj_Data, FILENAME='D:\SQL2000\DataBase\xscj_Data.MDF', SIZE = 10, MAXSIZE = 300, FILEGROWTH = 5 ) LOG ON ( NAME = xscj_Log, FILENAME='D:\SQL2000\DataBase\xscj_Log.LDF', SIZE = 10, MAXSIZE = 200, FILEGROWTH = 2 )
学号 select 姓名,性别,专业名,课程号,成绩 from xs a join xs_kc b on a.学号=b.学号 where 总学分>50 10. select 姓名,课程名,成绩 from xs a,xs_kc b ,kc c where 专业名='计算机' and 总学分 >50 and a.学号=b.学号 and b.课程号=c.课程号 select 姓名,课程名,成绩 from xs a join xs_kc b on a.学号=b.学号 join kc c on b.课程号=c. 课程号 where 专业名='计算机' and 总学分>50 11．Select 课程号，平均分=avg(成绩) from XS_KC Group by 课程号
点击后找到你下载下 来的 homework 的备份 （我课件中 XSCJ 数据 库的备份）所在的位置
然后一直“确定”下去，就可以还原 XSCJ 数据库了，下面的操作都在这个数 据库上进行。 如果报错，把第二幅图中的选项改为计算机上正确的文件路径即可。
实验 3 学生成绩数据的简单和复杂查询
实验步骤 以 xscj 数据库为例，使用查询分析器完成以下的查询语句：
或者：
SELECT KC.课程名, XS.学号, XS.姓名, XS.专业名, XS_KC.成绩
FROM XS_KC INNER JOIN
XS ON XS_KC.学号 = XS.学号 INNER JOIN
KC ON XS_KC.课程号 = KC.课程号
WHERE ( XS.总学分 > 50)
3. 创建存储过程
select 学号,课程号, 考试所占分数=成绩*0.7 from xs_kc 5. select 学生姓名=姓名,出生时间,所学专业=专业名 from xs 6. select * from xs_kc where 成绩>=80 and 成绩<=89
select * from xs_kc where 成绩 between 80 and 89 7. select * from kc where 学时>60 order by 学时 desc
数据库技术实验
实验 2 创建库和表
1．在'D:\SQL2000\DataBase\路径下创建数据库 xscj，要求：主数据文件逻辑名为 xscj_Data， 物理名为 xscj_Data.MDF，初始大小为 10MB，最大允许为 300MB，文件以 5MB 递增。事 务日志文件逻辑名为 xscj_Log，物理名为 xscj_Log.LDF，初始大小为 10MB，最大允许为 200MB，文件以 2MB 递增。 CREATE DATABASE xscj ON (
create procedure proc_info
as select * from xs
where 专业名＝’计算机’
go
exec proc_info
4.带参数的：
create procedure proc_student @name char(8)
as select * from XS where 姓名=@name
2． 创建表参见教材附录 A 的要求： CREATE TABLE xs (
学号 CHAR(6) PRIMARY KEY, 姓名 CHAR(8) NOT NULL, 专业名 CHAR(10) NULL, 性别 bit default 1 , 出身时间 smalldatetime NOT NULL, 总学分 tinyint NULL, 备注 text NULL ) CREATE TABLE kc ( 课程号 CHAR(3) PRIMARY KEY, 课程名 CHAR(16) NOT NULL, 开课学期 tinyint default 1 NOT NULL, 学时 tinyint NOT NULL, 学分 tinyint NULL
TABLE 命令实现） （7） 通过企业管理器完成在 XS 表中添加学生记录（’000001’，’黎平’，’通信工程‘，’
男’，’1980-03-10’,’50’,）。 （8） 用企业管理器在 XS_KC 表中删除学号号为 000001 的数据行。 （9） 用企业管理器学号是 001101 的学生的姓名为赵涵。 参考答案：
（3） 基于 XS 表和 XS1 表完成这个练习，将 XS 中的学号，姓名,性别,专业名插入到新 XS1 中。（采用 INSERT…SELECT 语句）
（4） 使用查询分析器将 KC 表中课程号为 206 的课程号改为 0204。 （5） 使用查询分析器删除 XS 表中学号为 000001 的学生。 （6） 使用查询分析器删除 XS1 表中的所有数据。（分别用 DELETE 和 TRUNCATE
专业:
alter table xs
add constraint zy 4.定义默认值 专业名
check (专业名
in ('计算机','通信工程','测控'))
(1)ALTER TABLE xs ADD CONSTRAINT zym_mrz DEFAULT '计算机' FOR 专业名
(2)create default zym_mrz as '计算机' --建立默认值对象 exec sp_bindefault 'zym_mrz' ,'xs.专业名' --绑定
select * from kc where 学时>60 order by 学时 asc 8. select * from xs where 姓名 like '王%' 9. select 姓名,性别,专业名,课程号,成绩 from xs a,xs_kc b where 总学分>50 and a.学号=b.
（1） 查询 xs 表中的所有学生的信息以及仅查询学生姓名。 （2） 查询出 XS 表中的前 10 个或前面 10％的学生信息。 （3） 查询总学分<50 的所有学生的学号，姓名,专业名。 （4） 将 XS_KC 表中的学生学号,课程号及成绩输出,其中成绩按 70％的分数显示，并
且将成绩列名改为‘考试所占分数’。 （5） 查询 XS 表中的学生相关信息，并以学生姓名,所学专业,出生时间作为列名。 （6） 查询 XS_KC 表，显示学生成绩在 80 到 89 之间的学生选课信息。 （7） 从 KC 表中找出学时大于 60 的课程信息，并按照升序和降序显示。 （8） 找出姓王的所有学生。 （9） 查询总学分大于 50 的所有学生的姓名,性别,专业和选修课程号和成绩 （10） 查询计算机专业的学分大于 50 的学生的姓名及所选课程名和成绩。 （11） 查询各门课程的平均分数。 参考答案：
2. select 学号,姓名,性别,专业名 into xs1 from xs
3. insert into xs1 select 学号,姓名,性别,专业名 from xs
add constraint xm 3.定义检查约束: 成绩:
unique (姓名)
alter table xs_kc add constraint cj check (成绩>=0 and 成绩<=100)
性别:
alter table xs add constraint xb check (性别='0' or 性别='1')
备注: ALTER TABLE xs ADD CONSTRAINT dtrq DEFAULT convert(varchar(50),getdate()) FOR 备注
5．定义规则： Create rule rule_学号 As @学号 like’[0-3][0-3][0-9][0-9][0-9][0-9]’
（2）定义唯一约束：xs 表的姓名字段。
（3）定义检查约束，如下表所示。
字段名
约束要求
性别
男或女
成绩
大于等于 0 小于等于 100
专业名
计算机,通信工程,测控
（4）定义默认值，如下表所示。
字段名
默认值
专业名
计算机
备注
当天的日期
（4）定义规则，要求学号输入格式为：学号共 6 位，前两位数字范围在 0－3 之间，后 4
Leabharlann Baidu
位数字范围在 0－9 之间。并绑定到 XS 表的学号字段上。 参考答案：
1.定义 xs_kc 表的主键约束(学号,课程号)
alter table xs_kc add constraint xh_kch primary key (学号,课程号)
2.定义惟一约束: xs 表的姓名字段
alter table xs
实验 4 学生成绩数据的插入、修改、删除
实验步骤 以 xscj 数据库为例，完成以下操作：
（1） 使 用 INSTERT 语 句 添 加 记 录 （ ’000001’ ， ’ 黎 平 ’ ， ’ 通 信 工 程 ‘ ， ’ 男’，’1980-03-10’,’50’）到 XS 表中。
（2） 根据 XS 表创建一个学生表(XS1)，该表包括学号，姓名,性别,专业名。（采用 SELECT…INTO 语句）
1. 创建视图
create view v_xs1
as select * from xs
where 专业名=‘计算机’and 总学分>50
2. 创建视图
create view v_xs
as select xs.学号,姓名,专业名,课程名,成绩
from xs,kc,xs_kc
where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号 and xs.总学分>50
1. select * from xs select 姓名 from xs
2. select top 10 * from xs
select top 10 percent *from xs 3. select 学号,姓名,专业名 from xs where 总学分<50 4. select 学号,课程号,成绩*0.7 as '考试所占分数' from xs_kc
Go Exec sp_bindrule ‘rule_学号’, ‘XS.学号’
实验 6 视图和存储过程的使用
实验内容 以 XSCJ 数据库为例，使用查询分析器和企业管理器完成以下操作： (1) 创建一个叫 v_xs1 的视图, 查询总学分大于 50 的计算机专业学生的信息。
(2) 创建一个叫 v_xs 的视图,查询总学分大于 50 的所有学生的学号,姓名,专业和选修的课 程名及成绩 (3)创建一个叫 proc_info 的存储过程，查询计算机专业的所有学生的信息。 (4)创建一个带参数叫 proc_student 的存储过程，查询某个学生的信息。 (5)删除 v_xs 视图 (6)删除 proc_student 存储过程 参考答案：
4. update kc set 课程号='0203' where 课程号='206' 5. delete from xs where 学号='000001'
6. delete xs1
truncate table xs1
实验 5 学生成绩数据完整性设计
实验内容
以 xscj 数据库为例，使用查询分析器和企业管理器完成以下操作： （1）定义 xscj 数据库中各表的主键和外键约束。
)
CREATE TABLE xs_kc (
学号 CHAR(6) , 课程号 CHAR(3), 成绩 tinyint NULL, CONSTRAINT pk_学号_课程号 PRIMARY KEY (学号,课程号) )
上面的SQL代码调试正确之后，请将xscj数据库整个删除，再进
行下面的操作。
带完整数据的 XSCJ 数据库的还原：先下载我课件中的 homework 备份。
NAME = xscj_Data, FILENAME='D:\SQL2000\DataBase\xscj_Data.MDF', SIZE = 10, MAXSIZE = 300, FILEGROWTH = 5 ) LOG ON ( NAME = xscj_Log, FILENAME='D:\SQL2000\DataBase\xscj_Log.LDF', SIZE = 10, MAXSIZE = 200, FILEGROWTH = 2 )
学号 select 姓名,性别,专业名,课程号,成绩 from xs a join xs_kc b on a.学号=b.学号 where 总学分>50 10. select 姓名,课程名,成绩 from xs a,xs_kc b ,kc c where 专业名='计算机' and 总学分 >50 and a.学号=b.学号 and b.课程号=c.课程号 select 姓名,课程名,成绩 from xs a join xs_kc b on a.学号=b.学号 join kc c on b.课程号=c. 课程号 where 专业名='计算机' and 总学分>50 11．Select 课程号，平均分=avg(成绩) from XS_KC Group by 课程号
点击后找到你下载下 来的 homework 的备份 （我课件中 XSCJ 数据 库的备份）所在的位置
然后一直“确定”下去，就可以还原 XSCJ 数据库了，下面的操作都在这个数 据库上进行。 如果报错，把第二幅图中的选项改为计算机上正确的文件路径即可。
实验 3 学生成绩数据的简单和复杂查询
实验步骤 以 xscj 数据库为例，使用查询分析器完成以下的查询语句：
或者：
SELECT KC.课程名, XS.学号, XS.姓名, XS.专业名, XS_KC.成绩
FROM XS_KC INNER JOIN
XS ON XS_KC.学号 = XS.学号 INNER JOIN
KC ON XS_KC.课程号 = KC.课程号
WHERE ( XS.总学分 > 50)
3. 创建存储过程
select 学号,课程号, 考试所占分数=成绩*0.7 from xs_kc 5. select 学生姓名=姓名,出生时间,所学专业=专业名 from xs 6. select * from xs_kc where 成绩>=80 and 成绩<=89
select * from xs_kc where 成绩 between 80 and 89 7. select * from kc where 学时>60 order by 学时 desc
数据库技术实验
实验 2 创建库和表
1．在'D:\SQL2000\DataBase\路径下创建数据库 xscj，要求：主数据文件逻辑名为 xscj_Data， 物理名为 xscj_Data.MDF，初始大小为 10MB，最大允许为 300MB，文件以 5MB 递增。事 务日志文件逻辑名为 xscj_Log，物理名为 xscj_Log.LDF，初始大小为 10MB，最大允许为 200MB，文件以 2MB 递增。 CREATE DATABASE xscj ON (
create procedure proc_info
as select * from xs
where 专业名＝’计算机’
go
exec proc_info
4.带参数的：
create procedure proc_student @name char(8)
as select * from XS where 姓名=@name
2． 创建表参见教材附录 A 的要求： CREATE TABLE xs (
学号 CHAR(6) PRIMARY KEY, 姓名 CHAR(8) NOT NULL, 专业名 CHAR(10) NULL, 性别 bit default 1 , 出身时间 smalldatetime NOT NULL, 总学分 tinyint NULL, 备注 text NULL ) CREATE TABLE kc ( 课程号 CHAR(3) PRIMARY KEY, 课程名 CHAR(16) NOT NULL, 开课学期 tinyint default 1 NOT NULL, 学时 tinyint NOT NULL, 学分 tinyint NULL
TABLE 命令实现） （7） 通过企业管理器完成在 XS 表中添加学生记录（’000001’，’黎平’，’通信工程‘，’
男’，’1980-03-10’,’50’,）。 （8） 用企业管理器在 XS_KC 表中删除学号号为 000001 的数据行。 （9） 用企业管理器学号是 001101 的学生的姓名为赵涵。 参考答案：
（3） 基于 XS 表和 XS1 表完成这个练习，将 XS 中的学号，姓名,性别,专业名插入到新 XS1 中。（采用 INSERT…SELECT 语句）
（4） 使用查询分析器将 KC 表中课程号为 206 的课程号改为 0204。 （5） 使用查询分析器删除 XS 表中学号为 000001 的学生。 （6） 使用查询分析器删除 XS1 表中的所有数据。（分别用 DELETE 和 TRUNCATE
专业:
alter table xs
add constraint zy 4.定义默认值 专业名
check (专业名
in ('计算机','通信工程','测控'))
(1)ALTER TABLE xs ADD CONSTRAINT zym_mrz DEFAULT '计算机' FOR 专业名
(2)create default zym_mrz as '计算机' --建立默认值对象 exec sp_bindefault 'zym_mrz' ,'xs.专业名' --绑定
select * from kc where 学时>60 order by 学时 asc 8. select * from xs where 姓名 like '王%' 9. select 姓名,性别,专业名,课程号,成绩 from xs a,xs_kc b where 总学分>50 and a.学号=b.
（1） 查询 xs 表中的所有学生的信息以及仅查询学生姓名。 （2） 查询出 XS 表中的前 10 个或前面 10％的学生信息。 （3） 查询总学分<50 的所有学生的学号，姓名,专业名。 （4） 将 XS_KC 表中的学生学号,课程号及成绩输出,其中成绩按 70％的分数显示，并
且将成绩列名改为‘考试所占分数’。 （5） 查询 XS 表中的学生相关信息，并以学生姓名,所学专业,出生时间作为列名。 （6） 查询 XS_KC 表，显示学生成绩在 80 到 89 之间的学生选课信息。 （7） 从 KC 表中找出学时大于 60 的课程信息，并按照升序和降序显示。 （8） 找出姓王的所有学生。 （9） 查询总学分大于 50 的所有学生的姓名,性别,专业和选修课程号和成绩 （10） 查询计算机专业的学分大于 50 的学生的姓名及所选课程名和成绩。 （11） 查询各门课程的平均分数。 参考答案：