数据库实验报告(共17页).docx

精选优质文档-倾情为你奉上数据库实验报告班级：计科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 Turing.stuAdd (RegisterDate date);2 删除Student表的属性列RegisterDate。 Alter table stuDrop column RegisterDate cascade constraints;说明：为了保证后面例子能够顺利运行，请大家一定将属性列RegisterDate从Stu表中删除。3 将Sage(年龄)的数据类型改为SMALLINT型。 Alter table stuModify sage smallint;4 将Stu表的属性列RegisterDate名修改为RegDate，其它不变。 Alter table turing.stu Rename column RegisterDate to RegDate;5 增加Sname(姓名)必须取唯一值的约束。alter table turing.stuadd constraint cons_sname unique(sname);6 删除Sname(姓名)必须取唯一值的约束。 Alter table turing.stu Drop constraint cons_sname;7 表中添加PRIMARY KEY 约束 Alter table turing.stusc Add constraint PK_SC PRIMARY KEY (sno,cno); 8 StuSC表中添加FORENGN KEY 约束 alter table turing. stusc add constraint fk_sc foreign key (sno) references turing.stu(sno) foreign key (cno) references turing.cose(cno);9定义SC表中grade默认值为0； alter table turing. Stuscmodify (grade default 0) 10定义SC表中grade最小值为0，最大值为100； alter table turing. stuscadd constraint chk_grade1check (0<=grade) and (grade <=100); 删除基本表1删除Stu表(注：能直接删除student表吗？为什么？)。不能，因为stusc表中的sno依赖stu表中的sno2删除cose表(注：能直接删除course表吗？为什么？)。不能，因为stusc表中的cno依赖cose表中的cno3 删除StuSC表。实验二 SQL语言应用A、 单表查询无条件查询1 查询全体学生的详细记录。select *from student2查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。select sname,sno,sdeptfrom student;3 查询全体学生的姓名(Sname)、出生年份及学号(Sno)。 select sname,sno,(2018-sage) as birthdayfrom student;4 查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。select sname,(2018-sage) as birthday,lower(sno)from student;5 查询选修了课程的学生学号。select distinct snofrom sc;条件查询6 查询数学系全体学生的学号(Sno)和姓名 (Sname)。select sno,snamefrom studentwhere sdept='数理学院'7 查询所有年龄在1822岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。select sname,sagefrom student where sage between 18 and 22;8 查询年龄在1822岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。select sname,sagefrom student where sage not between 18 and 22;9 查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。select sname,sagefrom student where sage not between 18 and 22;10 查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。select sno,sname,ssexfrom studentwhere sdept in('数理学院','自动化系','计科系');11 查询既不是信息系、数学系、也不是计算机系的学生的姓名(Sname)和性别(Ssex)。select sno,sname,ssexfrom studentwhere sdept not in('数理学院','自动化系','计科系');12 查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。select sname,sno,ssexfrom studentwhere sname like '刘%'13 查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。select sname,sdeptfrom studentwhere sname like '刘_'14 查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。select sname,sagefrom studentwhere sname not like '刘%'15 查询课程名为“DB_设计”的课程号(Cno)和学分(Credit)。select cno,ccreditfrom coursewhere cname='DB_设计'16 查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。select cno,cpno,cname,ccreditfrom coursewhere cname like 'DB_%设_'17 假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。select sno,cnofrom scwhere grade is null;18 查询所有有成绩的学生学号(Sno)和课程号(Cno)。select sno,cnofrom scwhere grade is not null;查询结果排序19 查询选修了C03号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。select sno,gradefrom scwhere cno='c03' and grade is not nullORDER by grade desc;20 查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。select *from studentorder by sdept,sno desc;集函数的使用21 查询学生总人数。select count(sno)from student;22 查询选修了课程的学生人数。select count(distinct sno)from sc;23 计算选修C01号课程的学生平均成绩。select avg(grade)from scwhere cno='c01'24 查询选修C01号课程的学生最高分数。select MAX(grade)from scwhere cno='c01'查询结果分组25 求各个课程号(Cno)及相应的选课人数。select cno,count(sno)countfrom scgroup by cno;26 查询选修了3门或3门以上课程的学生学号(Sno)select snofrom studentwhere sno in(select sc.snofrom scgroup by sc.snohaving count(*)>3);B 链接查询不同表之间的连接查询27 查询每个学生及其选修课程的情况。select student.*,sc.*from student,scwhere student.sno=sc.sno;28 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。select student.sno,sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and o=o;自身连接29 查询每一门课的间接先修课(即先修课的先修课)。select A.cname,B.cnamefrom course A,course Bwhere A.cpno=B.cno;外连接30把例3.37中的等值连接改为左连接。C 嵌套查询带谓词IN的嵌套查询31 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。select sno,snamefrom studentwhere sno in(select snofrom scwhere cno='c02');32 查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。select sno,sname,sdeptfrom studentwhere sdept in(select sdeptfrom studentwhere sname='李伟');33 查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。select sno,snamefrom studentwhere sno in(select sno from scwhere cno in(select cnofrom coursewhere cname='数据结构');带谓词ANY或ALL的嵌套查询34 查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。select sname,sagefrom studentwhere sage<=all(select sagefrom studentwhere sdept='自动化')and sdept!='自动化'带谓词EXISTS的嵌套查询35 查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。select sname,sdeptfrom studentwhere exists(select snofrom scwhere student.sno=sc.sno and cno='c01');36 查询选修了所有课程的学生姓名(Sname)和所在系。select sname,sdeptfrom studentwhere exists(select *from scwhere student.sno=sc.sno);D 集合查询37 查询计算机科学系的学生或年龄不大于20岁的学生信息。select *from studentwhere sdept='计科系'unionselect *from studentwhere sage<=20;38 查询数学系的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。select *from studentwhere sdept='数学'unionselect *from studentwhere sage<=20;39 查询数学系的学生与年龄不大于20岁的学生的差集。select *from studentwhere sdept='数学'minusselect *from studentwhere sage<=20;E、综合应用1、查询每个同学应该选修的课程门数及学分数。select View.sno,View.count(cno),View.sum(credit)from(select distinct sc.sno,distinct o,course.creditform course,scwhere o=o and student.sno=sc.sno) Viewgroup by sno;2、查询平均成绩大于学号'0'学生平均成绩的所有学生的姓名。select snamefrom sc,studentwhere student.sno=sc.snogroup by snamehaving (avg(grade)>(select avg(grade)from scwhere sno='0');3、查询每一位同学的平均成绩及选修课程的门数。select sno,avg(grade),count(cno)from SCgroup 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表中插入一条新记录，学号为“”，选的课程号为“C01123”，成绩为89。插入数据能否成功？为什么？成功。insert into stucose(sno,cno,grade)values('','“C01123',89);7 向Stu表中插入一条新记录，“,刘德华,男，22，计科系”。insert into stu(sno,sname,ssex,sage,sdept)values('','刘德华''男',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 将学号为“4”的学生年龄改为22岁，即要修改满足条件的一个元组的属性值。update studentset age=22where sno='4'10 将系别是“计科系”学生都修改为“计算机科学与技术系”，并将这些学生的年龄都增加1岁。update studentset sdept='计算机科学与技术系'where sdept='计科系'11将数理学院所有学生的数据结构成绩都加5分。update scset grade=grade+5where sno in(select snofrom studentwhere sdept='数理学院');12* 将学生李大鹏同学的数据结构成绩修改为89分。update scset grade=89where sno in(select snofrom studentwhere sname='李大鹏'）andcno in(select cnofrom coursewhere cname='数据结构'）13* 将数据结构最低分同学的成绩修改为60分。update scset grade=60where sno in(select snofrom scwhere grade<=all( select grade from sc where cno in ( select cno from coursewhere cname='数据结构') )andcno in(select cnofrom coursewhere cname='数据结构')and cno in(select cnofrom coursewhere cname='数据结构');删除数据14 删除stu表中学号的学生信息。能够实现该删除操作吗？为什么？不能，因为在sc表中可以会依赖stu表中中的sno；写出删除该数据的操作步骤。deletefrom Studentwhere sno=''15 删除学号为“4”的学生选修的课号为“”的记录。deletefrom scwhere sno='4' and cno=''16 删除所有计科系学生的选课记录。deletefrom scwhere sno in(select snofrom studentwhere sdept='计科系');实验四 数据库安全控制视图操作1 建立数学系学生的视图C_Student，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。create view c_studentasselect sno，sname，sage，sdeptfrom studentwhere sdept='数学系'whit check option;2 建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图Student_CRcreate student_crasselect student.sno,sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and sc.sno=o;3 定义一个反映学生出生年份的视图student_birth(sno,sname,s_birth,ssex,sdept)。视图建立后，使用命令查询自己创建视图的定义。create view studnet_birthasselect sname,sno,2018-sage as 's_birth',ssex,sdeptfrom student;4 将数学系学生视图C_Student中学号为S05的学生姓名改为“黄海”。update c_studentset 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_studentwhere sno='s09'7 删除视图Student_CR。Drop view student_cr;角色应用1. 首先创建两个角色，一个具有查询权限，无密码，另一个角色具有插入数据的权限并带有口令。并对执行结果进行查看。 create role r2 identified by ;create role r1 ;grant selecton studentto r1;grant inserton studentto r2;2. 然后使用GRANT语句对这两个角色授权，使这两个角色拥有对turing.Student表的相应的操作权限。并对执行结果进行查看。grant insert,update,selecton studentto r2;grant select,update,inserton studentto 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;5、用户u1连接数据库。SQL>connect u1/u16、在用户U1下进行查询操作，验证这时能否使用U2所具有的权限。7、激活角色。SQL>set role role_name;实验五 存储过程建立与调用存储过程实验1、利用存储过程，向student表添加一条学生信息。Create or replace procedure insertdata (sno student.sno%type,sname student.sname%type,ssex student.ssex%type,sage student.sage%type,sdept student.sdept%type )IsBeginInsert into studentValues(sno,sname,ssex,sage,sdept);End insertdata;Exec insertdata('1','lsj','男',18,'english');2、利用存储过程，以姓名作为输入参数查询该生的平均成绩。Create or replace procedure getaverage(Name in student.sname%type)IsAverage number(3,1);BeginSelect avg(grade)Into averageFrom student,scWhere student.sno=sc.sno and sname=name;End getaverage;3、利用存储过程，以课程名作为输入参数，将计科系的该课程成绩低于60分的都提高到60分。Create or replace procedure updata(name in ame%type)IsBeginUpdate scSet grade=60Where sno in(select sno from student where sdept='sc') and grade<60 and cno in(select cno from course where cname=name);End updata;函数实验4、 创建函数，向student表中添加一条学生信息，如果添加成功则返回true,否则返回false。Create or replace function t1 (sno student.sno%type,sname student.sname%type,ssex student.ssex%type,sage student.sage%type,sdept student.sdept%type )return numberIsBeginInsert into studentValues(sno,sname,ssex,sage,sdept);if sql%rowcount<>0 then return 1;elsereturn 0;end if;End t1;5、创建函数1。以学号作为输入参数，查询该生的姓名(作为输出参数)和平均成绩，该生的平均成绩作为函数的输出。(注：为了下面对该函数的调用，将姓名定义为OUT类型的参数，平均成绩作为返回值)。Create or replace function tt1(sno in student.sno%type) return numberIsAverage number(3,1);sname student.sname%type;BeginSelect avg(grade)Into averageFrom scWhere sno=sc.sno;select snameinto snamefrom studentwhere sno=sno;return average;End tt1;6、创建函数。要求创建一个函数，输入一个学号通过该函数计算这个学生所选课程的平均成绩。Create or replace function t3(Name in student.sname%type) return numberIsAverage number(3,1);BeginSelect avg(grade)Into averageFrom student,scWhere student.sno=sc.sno and sname=name;return average;End t3;7、创建函数。以课程名作为输入参数，将计科系的该课程成绩低于60分的都提高到60分，更新的人数作为作为函数的输出。CREATE OR REPLACE FUNCTION turing24.Update_SC(name IN ame%type) RETURN NUMBERIS cnt NUMBER;BEGIN UPdate turing24.sc SET grade=60 WHERE grade<60 AND sno IN(select sno from turing24.student where sdept='计科系') AND cno=(select cno from turing24.course where cname=name); IF SQL%ROWCOUNT<>0 THEN cnt:=SQL%ROWCOUNT; RETURN cnt; ELSE RETURN 0; END IF;END Update_SC;专心-专注-专业