注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

独立观察员·网易

分享万岁

 
 
 

日志

 
 

数据库原理 西安电子 杭州电子科技大学 实验报告  

2013-11-29 22:00:09|  分类: 作业 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

数据库原理 张红娟 西安电子科技大学出版社 杭州电子科技大学 实验报告

——独立观察员 2013.11.28

http://w19921004.blog.163.com/blog/static/21428821120131029100921/

指导老师:傅婷婷

2014.01.06更新

实验一内容

  1. SQL Server 的物理数据库文件有几种?后缀名分别是什么?

主数据文件(.mdf);次数据库文件(.ndf);日志文件(.ldf)。

 

(2)SQLServer 2000 中,有哪几种整型数据类型?它们占用的存储空间分别是多少?取值范围分别是什么

    Bigint——8字节(64位),-263 ~ 263 -1 ;

    Int——4字节,-231 ~ 231 -1 ;

    Smallint——2字节,-215 ~ 215 -1 ;

    Tinyint——1字节,0~255 。

 

实验二内容

 

--在"查询分析器"中创建worker表:

create table worker

    (Wno Char(4) PRIMARY key,

    Wname char(8) not null,

    Sex char(2) not null,

    Birthday Datetime);

    

--执行select语句:

select * from worker;

 

 

实验三内容

 

--创建4个表:

 

create table student

    (Sno Char(7) not null PRIMARY key,

    Sname varchar(20) not null,

    Ssex char(2) not null,

    Sage smallint ,

    Clno char(5) not null);

 

create table course(

    Cno char(1) not null PRIMARY key,

    Cname varchar(20) not null,

    Credit smallint );

 

create table class(

    Clno char(5) not null PRIMARY key ,

    Speciality varchar(20) not null,

    Inyear char(4) not null ,

    Number Integer,

    Monitor char(7) );

 

create table grade(

    Sno char(7) not null,

    Cno char(1) not null,

    Gmark numeric(4,1) );

 

--给学生表增加一属性Nation(民族),数据类型为Varchar(20);

alter table student

add Nation varchar(20);

 

--删除属性(列);

alter table student

drop column Nation;

 

--向成绩表中插入记录;注意单引号;

insert into grade (Sno,Cno,Gmark)

values('2001110','3',80);

 

--修改数据;

update grade

set Gmark = 70

where Sno = '2001110'

 

--删除记录;

delete from grade

where Sno = '2001110'

 

--在学生表的Clno属性上创建一个名为IX_CLASS的索引,默认为升序;

create index IX_CLASS

on student(Clno)

 

--删除索引;

drop index student.IX_CLASS

 

当插入的元组数据中,若有被"NOT NULL"限定的字段为空,则不被允许插入。这样可以防止插入无用数据项。

 

实验四

实习报告内容

1)验证习题312题中各项操作的SQL语句:

--找出所有被学生选修了的课程:

select distinct Cno

from Grade

where Sno is not null

 

--找出01311班女生的个人信息:

select *

from Student

where Clno='01311' and Ssex='女'

 

--01311和01312班的学生姓名、性别、出生年份:

select Sname '姓名', Ssex, 2013-Sage As [出生年份]

from Student

where Clno='01311' or Clno='01312'

 

--所有姓李的学生信息:

select *

from student

where Sname like '李%'

 

--李勇班级学生人数:

select Clno '李勇班级' , Number '学生人数'

from Class

where Clno in

    (select Clno

    from Student

    where Sname='李勇')

 

--操作系统的平均成绩、最高分、最低分:

select avg(Gmark) as '平均成绩', max(Gmark) as '最高分', min(Gmark) as '最低分'

from Grade

where Cno in ( select Cno from Course where Cname = '操作系统')

 

--找出选修了课程的学生人数:

select count(distinct Sno) '选修了课程的学生人数'

from Grade

where Cno is not null

 

--选修了操作系统的学生人数:

select count(distinct Sno) '选修了操作系统的学生人数'

from Grade

where Cno in (select Cno from Course where Cname='操作系统')

 

--找出2000级计算机软件班的成绩为空的学生姓名;

select Sname

from Student

where Sno in(

    select Sno

    from Student

    where Clno in(select Clno from Class where Inyear='2000' and Speciality='计算机软件')

) and Sno in(

    select Sno from Grade where Gmark is null)

 

2)实习内容(2)中的SQL语句是否正确?如果不正确,请写出正确的语句表达式。

--验证下列语句:

select eno, basepay, service

from salary

where basepay avg(basepay)

--错误:聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。

 

--修改:

select eno, basepay, service

from salary

where basepay < (select avg(basepay) from salary)

--对象名 'salary' 无效,说明表不存在,语法应该没错。

 

 

思考题

什么情况下需要使用关系的别名?别名的作用范围是什么?

    为名称较长的表分配别名,以使编码较长的查询更易进行,比如有子查询时。在做自连接查询中,必须为表指定两个别名,使之在逻辑上成为两张表。在查询中一旦分配了别名,就一定要在该查询中一直使用该别名。查询中一定不要将表的全名和其别名混合使用。 

 

 

实验五

实习报告内容

1)写出习题31314题中的各项操作的SQL语句。

 

--找出与李勇在同一班级的学生信息:

select *

from Student

where Clno=(select Clno from Student where Sname='李勇')

 

--找出所有与李勇有相同课程的学生信息;

select *

from Student

where Sno in (

    select Sno

    from Grade

    where Cno in(

        select Cno

        from Grade

        where Sno=(select Sno from Student where Sname='李勇')

    )

)

 

--找出年龄介于李勇和25岁之间的学生信息(已知李勇年龄小于25岁):

select *

from Student

where Sage between (select Sage from student where Sname='李勇') and 25

 

--找出选修了操作系统的学生学号和姓名:

select Sno '学号', Sname '姓名'

from Student

where Sno in (

    select Sno

    from Grade

    where Cno = (

        select Cno

        from Course

        where Cname = '操作系统'

    )

)

 

--没有选修1号课程的学生姓名:

select Sname '没有选修1号课程的学生姓名'

from Student

where Sno not in (

    select Sno

    from Grade

    where Cno = 1    

)

 

--找出选修了全部课程的学生姓名:

select Sname from Student where Sno in (

    select Sno from Grade group by Sno having count(Cno)=(

        select count(Cno) from Course

    )

)

 

--查询选修了3号课程的学生学号及其成绩,并按成绩降序排列:

select Sno '学号', Gmark '成绩'

from Grade

where Cno = '3'

order by Gmark desc

 

--查询全体学生信息,按班级号升序排列,同一班级按年龄降序排列:

select * from Student order by Clno, Sage desc

 

--求每个课程号的选课人数:

select Cno '课程号', count(Sno) as '选课人数'

from Grade

group by Cno

 

--选修了3门课以上的学生学号:

select Sno '选修了3门课以上的学生学号'

from Grade

group by Sno

having count(Cno) > 3

 

2 使用存在量词[NOT] EXISTS的嵌套查询时,何时外层查询的WHERE条件为真,何时为假。

    EXISTS代表存在量词,带有EXISTS谓词的子查询不返回任何实际数据,它只产生逻辑真值"true"或逻辑假值"false"。

    使用存在量词EXISTS后,若内层查询结果为非空,则外层的WHERE字句返回真值,否则返回假值。

    与EXISTS相对应的是NOT EXISTS谓词,使用NOT EXISTS谓词后,若内层查询结果为空,则外层的WHERE字句返回真值,否则返回假值。

    一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换,但带有谓词IN、比较运算符、ANY和ALL谓词的子查询都能用带有EXISTS谓词的子查询等价替换。

 

思考题

(1)UNIONUNION ALL将两个SELECT命令结合为一个时,结果有何不同。

在合并结果集时,默认将从最后的结果集中删除重复的行,除非使用ALL关键字。

2)当既能用连接词查询又能用嵌套查询时,应该选择哪种查询较好?为什么? 

    查询涉及多个关系时,用嵌套查询逐步求解,层次清楚,易于构造,具有结构化程序设计的优点。有些嵌套查询可以用连接运算替代,有些是不能替代的。对于可以用连接运算代替嵌套查询的,到底采用哪种方法用户可以根据自己的习惯确定。 

    讨论效率问题,需要从基表数据量大小、连接算法实现的选择以及索引的选择等多角度综合考虑,并不能简单得出谁好谁快的结论。

3)库函数能否直接使用在SELECT选取目标、HAVING子句、WHERE子句、GROUP BY列名中?

    可以直接使用:SELECT选取目标、HAVING子句 

    不能直接使用:GROUP BY列名、WHERE子句

 

 

实验六

实习报告内容

1)写出习题315题中各项操作的SQL语句:

--将01311班的全体学生成绩置零:

update Grade

set Gmark=0

where Sno in (

    select Sno

    from Student

    where Clno='01311'

)

 

--删除2001级计算机软件的全体学生的选课记录:

delete from Grade

where Sno in (

    select Sno from Student where Clno = (

        select Clno from Class where Inyear='2001' and Speciality='计算机软件'

    )

)

 

--李勇已退学,从数据库删除关于李勇的记录:

delete from Grade where Sno = (select Sno from Student where Sname='李勇')

update Class set Number=Number-where Clno = (select Clno from Student where Sname='李勇')

update Class set Monitor='' where Monitor = (select Sno from Student where Sname='李勇')

delete from Student where Sname='李勇'

 

--对每个班,求学生的平均年龄,并把结果存入数据库:

/*

select Clno , avg(Sage) as '该班学生平均年龄'

from Student

group by Clno

*/

alter table Class add Aage smallint null

 

update Class set Aage = case

when Clno='00311' then (select avg(Sage) from Student where Clno='00311')

when Clno='00312' then (select avg(Sage) from Student where Clno='00312')

when Clno='01311' then (select avg(Sage) from Student where Clno='01311')

end

 

update Class

set Aage = (

    select avg(Sage)

    from Student

    where Clno=Class.Clno

    )

 

2)实习内容(2)的SQL语句是否正确?如果不正确,请写出正确的语句表达式。

--验证是否正确:

update salary

set basepay=basepay+100

where eno in(

    select eno

    from employee

    where title='工程师'

)

--正确;

 

 

 

思考题

DROP命令和DELETE命令的本质区别是什么?

    DELETE语句用于从指定表中清除记录。这是一个有日志的操作过程,也就是说可以用ROLLBACK命令将它取消。 

    所有用CREATE命令创建的数据库对象,都可以用补充的DROP语句来卸下。DROP命令不能取消,作用是永久性的,在使用时必须要谨慎。还要注意到依赖性问题。

 

 

实验七

实习报告内容

1)写出习题316题中各项操作的SQL语句:

--建立01311班选修了1号课程的学生视图Stu_01311_1:

create view Stu_01311_1

as select *

from Student

where Clno='01311' and Sno in (select Sno from Grade where Cno='1')

--select * from Stu_01311_1

 

--建立01311班选修了1号课程并且成绩不及格的学生视图Stu_01311_2:

create view Stu_01311_2

as select *

from Student

where Clno='01311' and Sno in (select Sno from Grade where Cno='1' and Gmark<60)

--select * from Stu_01311_2

 

--建立视图Stu_year,由学生学号、姓名、出生年月组成:

create view Stu_year

as select Sno, Sname, 2013-Sage '出生年份'

from Student

--select * from Stu_year

 

--查询1990年以后出生的学生姓名:

create view Snamelt1900

as select Sname

from Student

where 2013-Sage>1990

--select * from Snamelt1900

 

--查询01311班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年月:

create view Stu_01311_5

as select Sno '学号', Sname '姓名', 2013-Sage '出生年份'

from Student

where Clno='01311' and Sno in (select Sno from Grade where Cno='1' and Gmark<60)

--select * from Stu_01311_5

 

2)建立一视图Class_grade,用来反映每个班的所有选修课的平均成绩,并对其进行更新操作。

CREATE VIEW Class_grade 

AS SELECT Clno,avg(gmark) as avg 

FROM Student,Grade 

WHERE student.sno=grade.sno 

GROUP BY  Clno

思考题

实习内容(2)创建的视图能否进行更新?为什么?

    不能,该视图定义含group by 和含聚集函数的派生列。

 

 

 

魏刘宏 11054126 数据库 第三次上机作业

——完整性约束和安全性

实验八

实习报告内容

(1)写出习题4第10题四个表结构的SQL定义语句:

--将Sno设为主码;

alter table Student

add constraint stu_pri primary key(Sno)

 

--Ssex男或女,默认为男;

alter table Student

add constraint stu_sex check(Ssex in ('男','女'))

alter table Student

add constraint stu_sex_def default '男' for Ssex

 

--年龄大于14,小于65;

alter table Student

add constraint stu_age check(Sage>14 and Sage<65)

 

--Clno为外部码,级联更新;

alter table Student

add constraint stu_for foreign key(Clno) references Class(Clno)

on update cascade

 

--Cno为主码;

alter table Course

add constraint cou_pri primary key(Cno)

 

--Credit取值1、2、3、4、5、6;

alter table Course

add constraint cou_cre check(Credit in ('1','2','3','4','5','6'))

 

--Clno为主码;

alter table Class

add constraint cla_pri primary key(Clno)

 

--班级人数大于1,小于300;

alter table Class

add constraint cla_num check(Number>and Number <300)

 

--班长学号为外部码;

alter table Class

add constraint cla_for foreign key(Monitor) references Student(Sno)

 

--学号和课程号为主属性;

alter table Grade

add constraint gra_pri primary key (Sno,Cno)

 

--学号为外部码,级联;

alter table Grade

add constraint gra_sno_for foreign key(Sno) references Student(Sno)

on update cascade

on delete cascade

 

--课程号为外部码,级联;

alter table Grade

add constraint gra_cno_for foreign key(Cno) references Course(Cno)

on update cascade

on delete cascade

 

--成绩大于0,小于100;

alter table Grade

add constraint gra_gmark check(Gmark>and Gmark<100)

 

(2)SQL Server 中提供了哪些方法实现实体完整性、参照完整性和用户自定义完整性?

实体完整性:

not null

primary key:唯一标识每一行,保证用户不输入重复的数据,且创建一个索引来提高性能,不允许空值;unique :防止非主关键字的重复,并保证创建一个索引来提高性能,允许空值。

 

参照完整性:

foreign key 的级联操作策略(级联更新、级联删除、置空):定义一个列或几个列的组合,他们的值匹配同一个表或另一个表中关键字。

 

用户定义完整性:

Check :指定在一个列中可接受的数据值;

default :指定在INSERT语句中没有明确提供一个值时,为该列提供的值。

 

实验九

实习报告内容

写出习题4第14题的SQL语句,并给出验证过程。

注:语句中没有注明登录身份的是以Windows身份验证(数据库管理员)身份执行的。

 

--用户张勇对Student表和Course表有select权限;---------------------

SP_ADDLOGIN '张勇', '123', 'GradeManager' --建立帐号;

go

SP_GRANTDBACCESS '张勇' --添加为设置的数据库的用户;

go

--授予权限;

grant select

on Student

to 张勇

grant select

on Course

to 张勇

--验证(以"张勇"登录时):

select * from Student --成功;

select * from Course --成功;

select * from Class --失败;

------------------------------------------------------------------

 

--把对表Student的insert和delete权限授予用户张三,并允许他再把此权限授予其他用户;----------------

SP_ADDLOGIN '张三', '123', 'GradeManager'

go

SP_GRANTDBACCESS '张三'

go

--授予权限;

grant insert,delete

on Student

to 张三

with grant option

 

--验证:

 

--(以"张三"登录)

    insert into Student

    values('2001105','独角兽','男','20','01311') --成功;

 

    delete from Student

    where Sname='独角兽'

    --拒绝了对对象 'Student' (数据库 'GradeManager',架构 'dbo')的 SELECT 权限。

 

    grant insert,delete --成功;

    on Student

    to 李四

 

--(以"李四"登录)

    insert into Student

    values('2001106','奥斯卡','男','22','01311') --成功;

 

------------------------------------------------------------------------------------------------

 

--把查询Course表和修改属性Credit的权限授给用户李四;--------------------------------------------

SP_ADDLOGIN 李四, '123'

go

use GradeManager

go

SP_GRANTDBACCESS 李四

go

--授予权限;

grant select,update(Credit)

on Course

to 李四

 

--验证:

update Course

set Credit=Credit+--成功;

update Course

set Cname='数据库2'

where Cno='1' --拒绝了对对象"Course"的列"Cname"的 UPDATE 权限。

 

--<附:

SP_DROPLOGIN 李四 --删除帐号;

SP_REVOKEDBACCESS 李四 --删除数据库用户;

--附>

-----------------------------------------------------------------------------------------------

 

--授予用户李勇敏对Student表的所有权限(读、插、删、改),并具有给其他用户授权的权限;----------

SP_ADDLOGIN 李勇敏, 123, GradeManager

go

SP_GRANTDBACCESS 李勇敏

go

grant all privileges

on Student

to 李勇敏

with grant option

 

--验证(以"李勇敏"登录):

select * from Student --成功;

 

grant insert

on Student

to 张勇 --成功;

 

-----------------------------------------------------------------------------------------------

 

--撤消(1)中对张勇所授予的所有权限;--------------------------------

revoke select --需在具有相关管理权限的账户下执行;

on Student

from 张勇

revoke select

on Course

from 张勇

--验证(在"张勇"账户下执行):

select * from Student --失败;

select * from Course --失败;

---------------------------------------------------------------------

 

--撤消(2)中对张三所授予的所有权限;------------------------------------------------------

revoke insert,delete

on Student

from 张三

cascade

 

--验证:

 

--以"张三"登录:

insert into Student

values('2001107','独角兽2','男','20','01311') --拒绝了对对象 'Student'的 INSERT 权限;

--以"李四"登录:

insert into Student

values('2001108','奥斯卡2','男','22','01311') --拒绝了对对象 'Student'的 INSERT 权限;

 

------------------------------------------------------------------------------------------

 

附几张截图:

 

 

 

 

 

2013.11.28

 

附(数据库表结构)

 

Class表:

 

Course表:

 

Student表:

 

Grade表:

 

2014.01.06

 

  评论这张
 
阅读(1075)| 评论(1)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018