数据库实验报告

数据库实验报告

班级：计科161

姓名：蒋东旗

指导老师：杜献峰

实验一基本表的定义、删除与修改

定义基本表

1建立学生表Stu，每个属性名的意义为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。这里要求Sno和Sname不能为空值，且取值唯一。Sno为主码。

create table stu

(

sno char(20) primary key,

sname char(20) unique,

ssex char(2),

sage smallint,

sdept char(20)

);

2 建立课程表Cose，其属性名意义分别为Cno-课程号, Cname-课程名, Cpno-先修课程号, Credit-学分。Cno为主码。

create table cose

(

cno char(4) primary key,

cname char(40) not null,

cpno char(4),

ccredit smallint,

foreign key(cpno)references cose(cno)

);

3 建立成绩表StuSC。其中的属性名意义分别为Sno-学号，Cno-课程号和Grade-考试成绩。Sno和Cno为主码，Sno和Cno分别为外码。

create table stusc

(

sno char(9),

cno char(4),

grade smallint,

primary key(cno,sno),

foreign key(sno)references stu(sno),

foreign key(cno)references cose(cno));

修改基本表(Alter)

1向基本表Stu中增加“入学时间”属性列，其属性名为RegisterDate，数据类型为DATE型。

Alter table

Add (RegisterDate date);

2 删除Student表的属性列RegisterDate。

Alter table stu

Drop column RegisterDate cascade constraints;

说明：为了保证后面例子能够顺利运行，请大家一定将属性列RegisterDate从Stu表中删除。

3将Sage(年龄)的数据类型改为SMALLINT型。

Alter table stu

Modify sage smallint;

4 将Stu表的属性列RegisterDate名修改为RegDate，其它不变。

Alter table

Rename column RegisterDate to RegDate;

5增加Sname(姓名)必须取唯一值的约束。

alter table

add constraint cons_sname unique(sname);

6 删除Sname(姓名)必须取唯一值的约束。

Alter table

Drop constraint cons_sname;

7 表中添加PRIMARY KEY 约束

Alter table

Add constraint PK_SC

PRIMARY KEY (sno,cno);

8 StuSC表中添加FORENGN KEY 约束

alter table turing. stusc

add constraint fk_sc

foreign key (sno) references (sno)

foreign key (cno) references (cno);

9定义SC表中grade默认值为0；

alter table turing. Stusc

modify (grade default 0)

10定义SC表中grade最小值为0，最大值为100；

alter table turing. stusc

add constraint chk_grade1

check ((0<=grade) and (grade <=100));

删除基本表

1删除Stu表(注：能直接删除student表吗为什么)。

不能，因为stusc表中的sno依赖stu表中的sno 2删除cose表(注：能直接删除course表吗为什么)。

不能，因为stusc表中的cno依赖cose表中的cno

3 删除StuSC表。

实验二 SQL语言应用

A、单表查询

无条件查询

1查询全体学生的详细记录。

select *

from student

2查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。

select sname,sno,sdept

from student;

3查询全体学生的姓名(Sname)、出生年份及学号(Sno)。

select sname,sno,(2018-sage) as birthday

from student;

4查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。select sname,(2018-sage) as birthday,lower(sno)

from student;

5查询选修了课程的学生学号。

select distinct sno

from sc;

条件查询

6 查询数学系全体学生的学号(Sno)和姓名 (Sname)。

select sno,sname

from student

where sdept='数理学院';

7 查询所有年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。select sname,sage

from student

where sage between 18 and 22;

8 查询年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。select sname,sage

from student

where sage not between 18 and 22;

9 查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。

select sname,sage

from student

where sage not between 18 and 22;

10 查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。select sno,sname,ssex

from student

where sdept in('数理学院','自动化系','计科系');

11查询既不是信息系、数学系、也不是计算机系的学生的姓名(Sname)和性别(Ssex)。select sno,sname,ssex

from student

where sdept not in('数理学院','自动化系','计科系');

12 查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。

select sname,sno,ssex

from student

where sname like '刘%';

13查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。

select sname,sdept

from student

where sname like '刘____';

14查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。

select sname,sage

from student

where sname not like '刘%';

15 查询课程名为“DB_设计”的课程号(Cno)和学分(Credit)。

select cno,ccredit

from course

where cname='DB_设计';

16查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。

select cno,cpno,cname,ccredit

from course

where cname like 'DB\_%设_';

17假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。

select sno,cno

from sc

where grade is null;

18 查询所有有成绩的学生学号(Sno)和课程号(Cno)。

select sno,cno

from sc

where grade is not null;

查询结果排序

19查询选修了C03号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。

select sno,grade

from sc

where cno='c03' and grade is not null

ORDER by grade desc;

20查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。

select *

from student

order by sdept,sno desc;

集函数的使用

21查询学生总人数。

select count(sno)

from student;

22查询选修了课程的学生人数。

select count(distinct sno)

from sc;

23计算选修C01号课程的学生平均成绩。

select avg(grade)

from sc

where cno='c01';

24查询选修C01号课程的学生最高分数。

select MAX(grade)

from sc

where cno='c01';

查询结果分组

25求各个课程号(Cno)及相应的选课人数。

select cno,count(sno)count

group by cno;

26查询选修了3门或3门以上课程的学生学号(Sno)

select sno

from student

where sno in(

select

from sc

group by

having count(*)>3);

B 链接查询

不同表之间的连接查询

27查询每个学生及其选修课程的情况。

select student.*,sc.*

from student,sc

where =;

28 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。select ,sname,cname,grade

from student,sc,course

where = and =;

自身连接

29查`询每一门课的间接先修课(即先修课的先修课)。

select ,

from course A,course B

外连接

30把例中的等值连接改为左连接。

C 嵌套查询

带谓词IN的嵌套查询

31 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。

select sno,sname

from student

where sno in(

select sno

from sc

where cno='c02');

32查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。select sno,sname,sdept

from student

where sdept in(

select sdept

from student

where sname='李伟');

33查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。

select sno,sname

from student

where sno in(

select sno

where cno in(

select cno

from course

where cname='数据结构')

);

带谓词ANY或ALL的嵌套查询

34查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。

select sname,sage

from student

where sage<=all(select sage

from student

where sdept='自动化')and sdept!='自动化';

带谓词EXISTS的嵌套查询

35查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。

select sname,sdept

from student

where exists(select sno

from sc

where = and cno='c01');

36 查询选修了所有课程的学生姓名(Sname)和所在系。

select sname,sdept

from student

where exists(select *

where =;

D 集合查询

37查询计算机科学系的学生或年龄不大于20岁的学生信息。

select *

from student

where sdept='计科系'

union

select *

from student

where sage<=20;

38 查询数学系的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。

select *

from student

where sdept='数学'

union

select *

from student

where sage<=20;

39查询数学系的学生与年龄不大于20岁的学生的差集。

select *

from student

where sdept='数学'

minus

from student

where sage<=20;

E、综合应用

1、查询每个同学应该选修的课程门数及学分数。

select ,(cno),(credit)

from

(select distinct ,distinct ,

form course,sc

where = and = View

group by sno;

2、查询平均成绩大于学号'2140'学生平均成绩的所有学生的姓名。select sname

from sc,student

where =

group by sname

having (avg(grade)>

(select avg(grade)

from sc

where sno='2140'));

3、查询每一位同学的平均成绩及选修课程的门数。

select sno,avg(grade),count(cno)

from SC

group by Sno;

实验三索引及数据DML操作

实验准备

为了不破坏自己演示数据库中的数据，这里先创建自己的3个数据表stu表、cose表和stuCose表，并分别导入实验用数据，脚本如下：

Create Table stu as select * from student;

Create Table cose as select * from course;

Create Table StuCose as select * from SC;

建立索引

1在学生选课数据库中的Stu，Cose，StuCose三个表建立唯一索引。其中Stu表按Sname升序建唯一索引，Cose表按Cname升序建唯一索引，StuCose表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。

create unique index stusno on stu(sno);

create unique index cosecno on cose(cname);

create unique index stucosesc on stucose(sno asc,cno,desc);

删除索引

4删除基本表Stu上的索引。

drop index stucno;

5删除基本表StuCose上的索引。

drop index stucosesc;

插入数据

6向StuCose表中插入一条新记录，学号为“000”，选的课程号为“C01123”，成绩为89。

插入数据能否成功为什么

成功。

insert into stucose(sno,cno,grade)

values('000','“C01123',89);

7向Stu表中插入一条新记录，“000,刘德华,男，22，计科系”。

insert into stu(sno,sname,ssex,sage,sdept)

values('000','刘德华''男',22,'计科系');

8 创建数据库表History_Student(sno,sname,sdept)，其模式及属性与Stu完全一样。要求将关系Stu中的所有元组插入到关系History_Student中去。

create table history_student(sno,sname,sdept)

as select sno,sname,sdept from stu;

更新数据

9将学号为“2124”的学生年龄改为22岁，即要修改满足条件的一个元组的属性值。

update student

set age=22

where sno='2124';

10将系别是“计科系”学生都修改为“计算机科学与技术系”，并将这些学生的年龄都增加1岁。

update student

set sdept='计算机科学与技术系'

where sdept='计科系';

11将数理学院所有学生的数据结构成绩都加5分。

set grade=grade+5

where sno in(

select sno

from student

where sdept='数理学院');

12* 将学生李大鹏同学的数据结构成绩修改为89分。update sc

set grade=89

where sno in(

select sno

from student

where sname='李大鹏'）

and

cno in(

select cno

from course

where cname='数据结构'）

13* 将数据结构最低分同学的成绩修改为60分。update sc

set grade=60

where sno in(

select sno

from sc

where grade<=all

(

select grade

where cno in

(

select cno

from course

where cname='数据结构')

)

and

cno in

(

select cno

from course

where cname='数据结构'))

and

cno in

(

select cno

from course

where cname='数据结构');

删除数据

14 删除stu表中学号53的学生信息。

能够实现该删除操作吗为什么

不能，因为在sc表中可以会依赖stu表中53中的sno；写出删除该数据的操作步骤。

delete

from Student

where sno='53';

15删除学号为“2124”的学生选修的课号为“0811001”的记录。

delete

from sc

where sno='2124' and cno='0811001';

16删除所有计科系学生的选课记录。

delete

from sc

where sno in(

select sno

from student

where sdept='计科系');

实验四数据库安全控制

视图操作

1建立数学系学生的视图C_Student，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。

create view c_student

as

select sno，sname，sage，sdept

from student

where sdept='数学系'

whit check option;

2建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图Student_CR

create student_cr

as

select ,sname,cname,grade

from student,sc,course

where = and =;

3定义一个反映学生出生年份的视图student_birth(sno,sname,s_birth,ssex,sdept)。

视图建立后，使用命令查询自己创建视图的定义。

create view studnet_birth

as

select sname,sno,2018-sage as 's_birth',ssex,sdept

from student;

4将数学系学生视图C_Student中学号为S05的学生姓名改为“黄海”。

update c_student

set sname='黄海'

where sno='s05';

5向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“S09”，姓名为“王海”，年龄为20岁。

insert into c_student(sno,sname,sage,sdept)

values('s09','王海','20','数学系');

6删除数学系学生视图C_Student中学号为“S09”的记录。

delete

from c_student

where sno='s09';

7删除视图Student_CR。

Drop view student_cr;

角色应用

1. 首先创建两个角色，一个具有查询权限，无密码，另一个角色具有插入数据的权限并带有口令。并对执行结果进行查看。

create role r2 identified by 123456;

create role r1 ;

grant select

on student

to r1;

grant insert

on student

to r2;

2. 然后使用GRANT语句对这两个角色授权，使这两个角色拥有对表的相应的操作权限。并对执行结果进行查看。

grant insert,update,select

on student

to r2;

grant select,update,insert

on student

to r1;

3. 创建用户。创建两个用户u1和u2，并授予这两个用户有create session的权限。并对执行结果进行查看。

create user u1 identified by u1;

create user u2 identified by u2;

4. 给用户授权。将角色的权限授予用户。并对执行结果进行查看。

Grant r1 to u1;

Grant r2 to u2;