一、进入Oracle数据库环境
sqlplus 用户名/密码
然后输入用户名和密码。
二、实验一
- 实验内容:SQL定义功能、数据插入
- 建立教学数据库的三个基本表:
S(Sno,Sname,Sgender,Sage,Sdept) 学生(学号,姓名,性别,年龄,系)
SC(Sno,Cno,Grade)选课(学号,课程号,成绩)
C(Cno,Cname,Cpno,Ccredit) 课程(课程号,课程名,先行课,学分)
按以下顺序分别建表
create table S(
Sno NUMBER(9) primary key,
Sname VARCHAR(20) not null,
Sgender VARCHAR(10) default '男' check(Sgender in ('男', '女')),
Sage NUMBER(2),
Sdept VARCHAR(10)
);
create table C(
Cno NUMBER(9) primary key,
Cname VARCHAR(10) not null,
Cpno NUMBER(9),
Ccredit NUMBER(2),
foreign key (Cpno) references C(Cno)
);
create table SC(
Sno NUMBER(9),
Cno NUMBER(9),
Grade NUMBER(3),
foreign key (Sno) references S(Sno),
foreign key (Cno) references C(Cno)
);
- DROP TABLE、ALTER TABLE、CREATE INDEX、DROP INDEX 及INSERT语句输入数据
- 删除表S和他所有的约束
drop table S CASCADE CONSTRAINTS;
- 将C表的Ccredit改为NUMBER(3)
alter table C modify Ccredit NUMBER(3);
- 为表SC的Sno和Cno建立索引
create index indexname on SC(Sno, Cno);
- 删除刚才建立的索引
drop index indexname;
- 为三个表插入数据
insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630301, 'King', '男', 20, 'B');
insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630302, 'Ming', '女', 21, 'C');
insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630303, 'Ting', '男', 20, 'A');
insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630304, 'Bing', '男', 22, 'C');
insert into C(Cno, Cname, Ccredit) VALUES(123456789, '概率论', 3);
insert into C(Cno, Cname, Cpno, Ccredit) VALUES(223456789, '计算机基础', 123456789, 3);
insert into C(Cno, Cname, Cpno, Ccredit) VALUES(323456789, '数理统计', 123456789, 3);
insert into C(Cno, Cname, Cpno, Ccredit) VALUES(423456789, '博弈论', 323456789, 3);
insert into SC(Sno, Cno, Grade) VALUES(161630301, 123456789, 89);
insert into SC(Sno, Cno, Grade) VALUES(161630301, 223456789, 90);
insert into SC(Sno, Cno, Grade) VALUES(161630301, 323456789, 80);
insert into SC(Sno, Cno, Grade) VALUES(161630301, 423456789, 77);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 123456789, 91);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 223456789, 88);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 323456789, 83);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 423456789, 93);
insert into SC(Sno, Cno, Grade) VALUES(161630303, 123456789, 90);
insert into SC(Sno, Cno, Grade) VALUES(161630303, 223456789, 89);
insert into SC(Sno, Cno, Grade) VALUES(161630304, 123456789, 87);
insert into SC(Sno, Cno, Grade) VALUES(161630304, 323456789, 86);
insert into SC(Sno, Cno, Grade) VALUES(161630304, 423456789, 85);
三、实验二
- 实验内容:数据查询
- 查询选修223456789号课程的学生学号与姓名
select S.Sno, Sname from S, SC where S.Sno=SC.Sno and SC.Cno=223456789;
- 查询选修课程名为概率论的学生学号与姓名
select S.Sno, Sname from S, SC, C where S.Sno=SC.Sno and C.Cno=SC.Cno and C.Cname='概率论';
- 查询不选223456789号课程的学生学号与姓名
select S.Sno, Sname from S where Sno not in (select Sno from SC where Cno in (select Cno from C where Cno=223456789));
- 查询学习全部课程学生姓名
select S.Sno, Sname from S where Sno in(select Sno from SC group by Sno having count(Cno)=4);
- 查询所有学生除了123456789选修号课程外所有成绩均及格的学生的学号和平均成绩,其结果按平均成绩的降序排列
create view SSC(Sno, Cno, Grade) as select S.Sno, Cno, Grade from S, SC where S.Sno=SC.Sno and Cno!=123456789;
select Sno, avg(Grade) as aver from SSC where (select min(Grade) from SSC SSSC where SSC.Sno=SSSC.Sno)>=60 group by Sno order by aver desc;
- 查询选修数理统计成绩第2名的学生姓名
select Sname from S where Sno in (select Sno from SC where Cno in (select Cno from C where Cname='数理统计') and Grade in (select max(Grade) from SC where Grade not in (select max(Grade) from SC where Cno in (select Cno from C where Cname='数理统计')) and Cno in (select Cno from C where Cname='数理统计')));
- 查询所有3个学分课程中有3门以上(含3门)课程获80分以上(含80分)的学生的姓名
create view select1(Sno, Cno) as select Sno, C.Cno from C, SC where C.Cno=SC.Cno and Ccredit=3 and Grade>=80;
select Sname from S where Sno in (select Sno from select1 group by Sno having count(Cno)>=3);
- 查询选课门数唯一的学生的学号
create view select2(Sno, Ccount) as select Sno, count(*) from SC group by Sno;
select Sno from SC group by Sno having count(*)<>all (select Ccount from select2 where select2.Sno!=SC.Sno);
四、实验三
- 实验内容:数据修改、删除
- 把223456789号课程的非空成绩提高10%
update SC set Grade=Grade*1.1 where Grade is not null and Cno=223456789;
- 在SC表中删除课程名为计算机基础的成绩的元组
delete from SC where Cno in (select Cno from C where Cname='计算机基础');
- 在S和SC表中删除学号为161630303的所有数据
下面的SYS_C0010950是我的约束名,你们的可能不太一样,直接运行delete语句会报错,错误中提示你约束名
alter table SC disable constraint SYS_C0010950 cascade;
delete from S where Sno=161630303;
delete from SC where Sno=161630303;
alter table SC enable constraint SYS_C0010950;
五、实验四
- 实验内容:视图的操作
- 建立男学生的视图,属性包括学号、姓名、选修课程名和成绩
create view male_stu(Sno, Sname, Cname, Grade) as select S.Sno, Sname, Cname, SC.Grade from S,C,SC where S.Sno=SC.Sno and C.Cno=SC.Cno and S.Sgender='男';
- 在男学生视图中查询平均成绩大于85分的学生学号与姓名
select Sno, Sname from male_stu group by Sno, Sname having avg(Grade)>85;
六、实验五
- 实验内容:库函数,授权控制
- 计算每个学生有成绩的课程门数、平均成绩
select Sno, count(*) as course_num, avg(Grade) as grade_avg from SC where Grade is not null group by Sno;
- 使用GRANT语句,把对基本表S、SC、C的使用权限授给其它用户
grant all on SC to public;
grant all on S to public;
grant all on C to public;
- 实验完成后,撤消建立的基本表和视图
drop table S CASCADE CONSTRAINTS;
drop table SC CASCADE CONSTRAINTS;
drop table C CASCADE CONSTRAINTS;
drop view select1 CASCADE CONSTRAINTS;
drop view select2 CASCADE CONSTRAINTS;
drop view male_stu CASCADE CONSTRAINTS;
drop view SSC CASCADE CONSTRAINTS;