数据库上机实验报告

数据库实践 课程设计报告

part1

一、数据库的创建和数据的输入 （1）学生基本表的创建 学生基本信息表

create table s，(snum char(50), sname char(50), birthd char(50), sex char(50), mnum char(50), primary key (snum)); 学生选课表

create table sc，(snum char(50), cnum char(50), teacher char(50), score char(50), primary key (snum,cnum)); （2）视图与索引的创建 3：学生选课视图

create view sc_sview(学号,姓名,课程号,课程名,教师,成绩,学分,性质,开课学期) as select s.snum,sname,c.cnum,cname,sc.teacher,score,ccredit,ckind,cdate s.snum=sc.snum

from

and

s,c,tea,sc,mtrain

c.cnum=sc.cnum

where and

mtrain.cnum=c.cnum and tea.teacher=sc.teacher 4：商学院学生选课视图

create view 商学院(年龄) as select

year(getdate())-year(birthd) from s where s.snum like'%16%' 5：索引的创建

（1）create index 专业信息_index on minf(mnum,major,mintro)

create index 专业培养计划_index on mtrain(mnum,cnum,ccredit,ckind,cdate)

（2）create index 学生选课_index on s on sc(s.snum asc,sc.cnum desc)where s.snum=sc.snum

（3）create index 学生基本信息_index on s(snum,sname,birthd,sex,mnum) 二、数据查询

1：select distinct cname,teacher from c,tea,s,mtrain where c.cnum=tea.cnum and ckind like'必修' and s.snum like '%160505%';

2：select distinct sname from s,sc where sex='男' and teacher='刘强' and s.snum=sc.snum ;

3：select distinct sname,s.snum from s,c,sc where sex='女' and s.snum not in (select sc.snum from sc where sc.teacher='刘强' and sc.snum=s.snum);

4：select c.cnum,cname,score from c,sc,s,mtrain where s.sname='王丽'and c.cnum=sc.cnum and s.snum=sc.snum

and ckind='第四学期';

5：select distinct sname,s.snum from s,sc,mtrain where s.snum in

(select s.snum from s,sc where s.snum like'%160107%' and ckind='选修'and s.snum=sc.snum group by s.snum having count(sc.snum)>=3);

6：select distinct c.cnum,cname from c,s,sc,mtrain where s.snum like'%160107%'and c.cnum=sc.cnum and sc.snum=s.snum and ckind like '选修';

7：select count(teacher)from sc where teacher in (select distinct teacher from sc group by teacher); 8：select year(getdate())-year(birthd) from s where birthd in (select distinct birthd from s group by birthd )and s.snum like'%160107%';

9：select count( sc.cnum),sum(score),avg(ccredit) from sc,mtrain

where sc.cnum=mtrain.cnum and score is not null group by sc.snum,ckind order by avg(score) desc

10：select count( sc.cnum),sum(ccredit), avg(score) from sc,mtrain

where sc.snum like '160505%' and sc.cnum=mtrain.cnum and score is not null group by ckind

11：select count( sc.cnum) from sc,mtrain

where sc.snum like '16%' and sc.cnum=mtrain.cnum group by mnum,ckind order by count( sc.cnum) 12：select sc.snum,sc.cnum,c.cname,sc.teacher from sc,c,mtrain

where sc.snum like '%160507%'and sc.cnum=c.cnum and score is null and sc.cnum=mtrain.cnum

and ckind like'必修'and cdate not in('第一学期','第二学期','第三学期','第四学期')

13：select sc.snum,sname from s,sc,mtrain

where s.sname like'%160507%'and s.snum=sc.snum and sc.snum in(select sc.snum from sc,mtrain where sc.cnum=mtrain.cnum and ckind like'必修' group by sc.snum having count(ccredit)>15); 14：select c.cnum,cname from c,mtrain

where ckind like'必修'and cdate like'第五学期' and mnum='1605' and mtrain.cnum=c.cnum; 15

：

select

s.snum,sname,major,mintro

from

s,minf,sc,c,mtrain where s.snum like'%060105%'and s.snum=sc.snum and c.cnum=mtrain.cnum

and ckind='选修' and (select sum(score)from sc)<25; 16：select avg(score) from sc,mtrain where

sc.cnum=mtrain.cnum

and teacher='高强' group by cdate,mnum;

17：select sname,s.snum from s,sc,mtrain where s.snum like'%160107%'and cdate='第四学期'and s.snum=sc.snum and score<60;

create index 不及格名单_index on s(snum desc) 18：select distinct sc.snum from sc,mtrain where sc.snum not in

( select sc.snum where teacher='李中华' and ckind='选修');

19：select cname,avg(score) from sc,c where sc.snum like'%16%'and sc.cnum=c.cnum group by cname order by avg(score)desc ;

20：select s.snum,avg(score) from sc,c,s where sc.cnum=c.cnum and s.snum=sc.snum group by s.snum order by avg(score)desc; 四、数据更新：：

1：delete sc where score is null;

2：delete from sc where snum in (select s.snum from s,sc,c,mtrain where sex='刘强' and sex='女' and s.snum=sc.snum and c.cnum=sc.cnum and ckind='选修'); 3：update sc set score='60'where sc.cnum in(select

c.cnum from sc,c,s where s.snum like'%160107%'and cname='高等数学'and score<60 and c.cnum=sc.cnum); 4：update sc set score=score*1.05 where sc.snum in (select sc.snum from sc,s,c where s.snum like '%160107%'and cname='数据库技术'and sex='女'and score<(select avg(score)from sc));

5：insert into mtrain(mnum,cnum,ckind,cdate) values('1605','网络','必修','第五学期') 五、存在的问题与解决方法

问题1：在例如'160107%'前面用的“is”。 解决方法：应该把“is”改成“like”。

问题2：有时候显示对象名“sc”无效，之类的语句。 解决方法：文件夹那里默认是master的，应该选择自己所在的文件夹，因为创建的表格都在自己的文件夹中。 在例如'160107%'前面用的“is”。 解决方法：应该把“is”改成“like”。

问题3：有时候显示名字附近有错误。

解决方法：有些列名前面要加上表名，来区分其他列名。

问题4：有的结果重复了好多行。

解决方法：在语句前面加上“distinct”。

问题5：有时候显示对象名不存在。

解决方法：往往是表名打错字，例如把专业培养计划表“mtrain”打成“matiain”。找到错误的表名，更改过来。 有时候显示名字附近有错误。

解决方法：有些列名前面要加上表名，来区分其他列名。

Part2

宾馆信息管理系统的设计 一、 数据表的创建 （1）客房基本信息表kf

create table kf，(kfbh numeric(50), kflx varchar(50), kfez varchar(8), kfdj numeric(50), kfzt varchar(50), bz varchar(50), primary key (kfbh)); 字段

字段含

数据

数据是否为空

是否为主

备注（是否

代号 义 kfbh

客房编号 客房类型

类型 长度 （√）为空 属性（√） 为外码） 数值

50

√

kflx 文本 文本

50 √ 50 √

kfwz 客房位

置

kfdj bz

（2）顾客信息表gk，create table gk，(gkid numeric(50), gkxm varchar(50), bz varchar(50), primary key (gkid)); 字段

字段数据数据是否为空

是否为主

备注（是否为

客房单价 备注

数值 文本

50 √ 50 √

代号 含义 类型 长度 （√）为空 属性（√） 外码） gkid

顾客编号 顾客姓名

数值

50

√

gkxm bz

文本 50 √ 50 √

备注 文本

（3）订房信息表df，create table df ，(gkid numeric(50), gkxm varchar(50), rzrq datetime(8), kfbh numeric(50), bz varchar(50), primary key (gkid,rzrq)); 字段

字段数据数据是否为空

是否为主

备注（是否为

代号 含义 类型 长度 （√）为空 属性（√） 外码） gkid

顾客编号

数值

50

√

√

gkxm

顾客姓名 入住日期 客房编号

文本 50 √

rzrq 日期 8 √

kfbh bz

数值 50 √ 50 √

备注 文本

（4）结算信息表js，create table jf，(gkid numeric(50), jsrq datetime(8), zkr numeric(50), jsjr numeric(50)，primary key (gkid,jsrq)); 字段

字段数据数据是否为空

是否为主

备注（是否

代号 含义 类型 长度 （√）为空 属性（√） 为外码） gkid

顾客编号 结算日期 客房号 折扣率

数值

50

√

√

jsrq 日期 8 √

kfh 数值 50 √

zkr 数值 50 √ 50 √

jsjr 结算数值

金额

二、 数据查询

（1） 查询客房单价小于150的客房信息

select * from kf where kfdj<150;

（2） 查询未被预定及入住的空客房号

select kf.kfbh from kf where kfzt is null; （3） 查询王先生的住房信息

select kf.kfbh,rzrq,gkxm, from kf,df,gk where

gkxm like'%王%'and kf.kfbh=df.kfbh and gk.gkbh=df.gkbh;

（4） 查询2008年7月15日入住的总人数 select

count(gk.gkid)from

gk,df

where

gk.gkid=df.gkid

and rzrq='2008/7/15'

SQL sever 2000 安全管理的认识

SQL Server的安全机制一般主要包括三个方面:

服务器级别的安全机制:这个级别的安全性主要通过登录帐户进行控制,要想访问一个数据库服务器,必须拥有一个登录帐户。登录帐户可以是Windows账户或组，也可以是SQL Server的登录账户。登录账户可以属于相应的服务器角色。至于角色，可以理解为权限的组合。

数据库级别的安全机制:这个级别的安全性主要通过用户帐户进行控制,要想访问一个数据库，必须拥有该数据库的一个用户账户身份。用户账户是通过登录账户进行映射的，可以属于固定的数据库角色或自定义数据库角色。 数据对象级别的安全机制:这个级别的安全性通过设置数据对象的访问权限进行控制。如果是使用图形界面管理工具,可以在表上点右键,选择属性|权限,然后在相应的权限项目上打勾就可以了。

如果是使用sql语句，可以如下：

grant select|insert|update|delete on tablename to username

deny select|insert|update|delete on tablename to username

revoke select|insert|update|delete on tablename from username

grant是赋予权限，deny是拒绝权限，revoke是撤消权限，而select|insert|update|delete是可以设置的各个权限项目。

其他方面的安全机制还包括应用程序的安全问题和网络传输的安全问题。

Part3：

9-6 use ckgldb go

create function wznamef (@wzno varchar(6)) returns varchar(16) begin

declare @wzname varchar(16) select @wzname=wzmc from wzbmb where wzbm=@wzno return @wzname end 9-7 use ckgldb go

create function wzckf (@days datetime) returns table return( select*

from wzlkb where rq<@days） 9-8 use ckgldb go

create procedure unitll as

select rq,wzbm,qls,sfs,llr,flr from wzlkb where dwbm='0101' go 9-14 use ckgldb go

alter procedure everyrkr1 with encryption @name varchar(8)=null as

if@name is null

select a.wzbm,wzmc,rq,rkh,gms,b.price,rkr from wzbmb a inner join wzrkb b on a.wzbm=b.wzbm

else

select a.wzbm,wzmc,rq,rkh,gms,b.price,rkr from wzbmb a inner join wzrkb b on a.wzbm=b.wzbm where wzmc=@name go 9-15： use ckgldb go

if exists(select name from sysobjects where name='wzbmb_d'and type='TR') drop trigger wzbmb_d go

create trigger wzbmb_d on wzbmb for delete as

print'数据被删除！' go 9-18： use go

if exists(select name from sysobjects where name='T_dw_d' and type='TR') drop trigger T_dw_d go

create trigger T_dw_d on dwbmb for delete as

declare @dwno varchar(16),@dwtemp varchar(16) select @dwno=dwbm from deleted select @dwtemp=dwbm from wzlkb

where (rq between getdate()-10 and getdate()) and(dwbm=@dwno) if @dwno=@dwtemp

print'该记录不能被删除！' else

print'该记录已被删除！' go

总结：

数据库的上机实验结束了，通过对数据库的学习也初步掌握了其各方面的知识，数据库的的功能是强大的，面对目前的信息化社会，在整理、查询、分析数据方面是一款强有力的工具。

学习的目的在于将知识能合理顺利的运用，将书本知识化为己用，将理论知识用在显示当中的问题，是一个不知道到知道，了解完善应用的过程，尤其是计算机方面的知识更是如此，必要的上机练习是必不可少的。本次数据库上机主要用到的是SQL结构化查询语言，它是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。通过这几次上机实验，我们学会了数据查询、修改、视图建立和查询、权限的修改、触发器的建立等。上机过程中也遇到不少问题，通过老师的帮助和同学互相探讨，解决了很多问题。通过本次实验，我了解到数据库今后的发展方向以及其广泛应用，在今后应加强学习。