广州大学 数据库实验一实验报告

广州大学学生实验报告

实验课程名称 数据库原理实验
实验项目名称 SQL语言

一. 实验目的

通过本次实验，使学生能够熟练运用SQL语言进行数据查询和数据更新，以及对基本表、视图、索引的管理。

二. 实验要求

熟悉实验室实验环境，阅读本次实验预备知识，熟悉基本表、视图、索引的基本概念，了解基本表、视图、索引的基本管理语法，熟悉查询语句和更新语句的基本语法。实验中根据实验步骤要求书写相应的SQL代码并运行，记录和分析运行结果，使用代码验证SQL代码执行后是否满足步骤要求，并独立完成实验报告。

三. 实验环境

Oracle 11g，windows 10；

四. 实验内容和步骤

实验内容第一部分（无需截图）

(建议先把实验内容和步骤（实验内容和步骤可在QQ群共享文件夹中下载）拷贝到SQL Developer工作区，然后按步骤进行实验，后同)

1.创建学生选课关系数据库中的STUDENT表（特别提示：表结构见1.3节学生选课关系数据库，使用课本上的表结构是错误的，后同）；

create table Student
(
    Sno varchar2(17) primary key,
    Sname varchar2(10) unique,
    Ssex varchar2(3),
    Sage number(3),
    Sdept varchar2(20)
);

2.创建学生选课关系数据库中的COURSE表；

create table Course
(
    Cno varchar2(8) primary key,
    Cname varchar2(40) not null,
    Cpno varchar2(8),
    Ccredit number,
    foreign key (Cpno) references Course(Cno)
);

3.创建学生选课关系数据库中的SC表；

create table Sc
(
    Sno varchar2(17),
    Cno varchar2(8),
    Grade number,
    primary key (Sno,Cno),
    foreign key (Sno) references Student(Sno),
    foreign key (Cno) references Course(Cno)
);

4.运行下列语句，为基本表添加数据；

--以下为学生表的初始数据
insert into Student(sname,ssex,sno, sage, sdept) values('李勇','男','200215121',20,'CS');
insert into Student(sname,ssex,sno, sage, sdept) values('刘晨','女','200215122',19,'CS');
insert into Student(sname,ssex,sno, sage, sdept) values('王敏','女','200215123',18,'MA');
insert into Student(sname,ssex,sno, sage, sdept) values('张立','男','200215125',19,'IS');
--以下为课程表的初始数据
insert into course(cno,cname,cpno,ccredit) values('6','数据处理',null,2);
insert into course(cno,cname,cpno,ccredit) values('2','数学',null,2);
insert into course(cno,cname,cpno,ccredit) values('7','PASCAL语言','6',4);
insert into course(cno,cname,cpno,ccredit) values('5','数据结构','7',4);
insert into course(cno,cname,cpno,ccredit) values('1','数据库','5',4);
insert into course(cno,cname,cpno,ccredit) values('3','信息系统','1',4);
insert into course(cno,cname,cpno,ccredit) values('4','操作系统','6',3);
--以下为选修表的初始数据
insert into sc(sno,cno,grade) values('200215121','1',92);
insert into sc(sno,cno,grade) values('200215121','2',85);
insert into sc(sno,cno,grade) values('200215121','3',88);
insert into sc(sno,cno,grade) values('200215122','2',90);
insert into sc(sno,cno,grade) values('200215122','3',80);
commit;

5.修改Student表结构，为Student表格添加一个“入学时间”属性，属性名为Senrollment，各元组在属性Senrollment的值是多少；

alter table Student
add Senrollment varchar2(20);
update Student
set Senrollment = '2002';
6.修改Student表结构，把Ssex列的宽度设置为6个字节；
alter table Student modify Ssex varchar2(6);

7.修改Student表结构，删除Senrollment列（注：删除SYS模式下表的列将被拒绝）；

alter table Student
drop column Senrollment;

8.创建视图ds，该视图包含所有选修了“数据库”的学生信息（注：如果提示没有创建视图的权限，请使用管理员账户SYS或SYSTEM连接数据库并授予当前用户Create view权限）；

create view ds as
select student.*,sc.cno,course.cname,sc.grade
from Student, Course, Sc 
where Student.sno = Sc.sno
    and Sc.cno = Course.cno
    and course.cname = '数据库';

9.创建视图maleStudent，该视图包含男学生所有信息，通过视图maleStudent更新基本表数据时必须保证学生性别为男；

create view maleStudent
as
select *
from Student
where Ssex = '男'
with check option;

10.删除视图maleStudent；

drop view maleStudent;

11.为Course表的CName列建立唯一索引，索引名称为uniqueCname；是否能够为Course表的Cpno列建立唯一索引？为什么？

create unique index uniqueCname on Course(Cname);
create unique index uniqueCpno on Course(Cpno);
--不能为Cpno建立唯一索引，因为会重复

12.为Cource表的Cpno列建立普通索引，索引名称为indexCpno2；

create index indexCpno2 on Course(Cpno);

13.删除索引indexCpno2；

drop index indexCpno2;

14.删除基本表Student，如果发生错误，请分析原因；

drop table Student;
--Student的信息被Course表引用

15.删除基本表SC；

drop table Sc;

实验内容第二部分（无需截图）

本部分实验采用项目信息管理关系数据库，实验前请在QQ群共享文件夹中下载文件“项目信息管理信息DDL和初始插入数据.txt”，该文件内部包括“项目信息管理关系数据库的DDL代码”和“项目信息管理关系数据库初始化数据代码”，用于建立实验所需基本表并插入初始化数据，后续实验也都采用项目信息管理关系数据库。

1.查询系号为“d001”的所有教师的教工号、名称和工资；

select tno,tname,tsalary from teacher where dno = 'd001';

2.查询工资在3000到5000之间的教师姓名、年龄（提示：可使用当前年份减去教师的出生年份，教师的出生年份可以使用函数extract(year from tbirthday)获取）；

select tname,2023-extract(year from tbirthday)
from teacher
where tsalary between 3000 and 5000;

3.查询参加了项目的教工的编号，排除相同的元素；

select distinct tno
from tm;

4.查询名字中包含字“小”的教工姓名、出生日期；

select tname,tbirthday
from teacher
where tname like '%小%';

5.查询名字中第二个字为“小”的教工姓名、出生日期；

select tname,tbirthday
from teacher
where tname like '_小%';

6.查询所有不姓“李”、并且姓名为三个字的教工姓名、性别；

select tname,tsex
from teacher
where tname not like '李%' and tname like '___';

7.查询Department表有系主任的系号、系名称；

select dno,dname
from department
where tno is not null;

8.查询工资在4000以上或者性别为女的教师详细信息，按性别降序排列输出；

select *
from teacher
where tsalary > 4000 or tsex = '女'
order by tsex desc;

9.查询参与了项目的教工总人数；

select count(distinct tno) from tm;

10.查询“张三”负责的项目数量；

select count(*) from tm,teacher where tm.tno=teacher.tno and teacher.tname = '张三';

11.查询所有教师的平均工资、工资总和、最高工资、最低工资；

select avg(tsalary),sum(tsalary),max(tsalary),min(tsalary) from teacher;

12.创建视图departmentSalary，查询各个系的教师的平均工资、工资总和、最高工资、最低工资；

create view departmentSalary(dno,avg_salary,sum_salary,max_salary,min_salary)
as
select dno,avg(tsalary),sum(tsalary),max(tsalary),min(tsalary)
from teacher group by dno;

13.查询各个系的详细信息，包括各个系的教师的平均工资、工资总和、最高工资、最低工资(提示：可以使用department表与视图departmentSalary进行连接运算完成)；

Select department.*,departmentsalary.avg_salary,departmentsalary.sum_salary,
departmentsalary.max_salary, departmentsalary.min_salary
from department,departmentsalary
where department.dno = departmentsalary.dno;

14.查询教师平均工资大于4500的系号、系名称、平均工资（提示：要求不能使用视图departmentSalary，可把department与teacher连接后再进行分组，然后使用having子句对分组进行筛选）；

select dno,d_dname,avg_salary 
from (select dname as d_dname,avg(tsalary) as avg_salary 
    from department,teacher where department.dno=teacher.dno  
    group by dname having avg(tsalary)>4500),department 
where department.dname like d_dname;

15.查询教师参与项目的情况，列出教工号、姓名和项目名称，没有参与项目的教师也列出来（提示：用左外连接）；

select teacher.tno,tname,b.pname 
from teacher left outer join 
(select tm.tno,pname 
from tm,myproject 
where tm.pno=myproject.pno)b 
on(teacher.tno=b.tno);

16.查询与“李小龙”工资相同的教师详细信息（要求分别使用自身连接、子查询两种查询方法完成）；

select A.*
from teacher A, teacher B
where A.tsalary = B.tsalary and B.tname = '李小龙';

select *
from teacher
where tsalary = 
(select tsalary
from teacher
where tname = '李小龙');

17.查询参与了“云计算研究”并且工资在4000以上的教师详细信息；

select teacher.*
from tm,myproject,teacher
where tm.pno = myproject.pno and teacher.tno = tm.tno and myproject.pname = '云计算研究';

18.查询小于或等于“同一系中教师平均工资”的教工号、姓名、年龄（提示：请参阅书本的“相关子查询”示例）；

select tno,tname,2018-extract(year from tbirthday) age
from teacher A 
where tsalary<=(select avg(tsalary) 
from teacher B where B.dno=A.dno);

19.查询比“计算机科学系”教师工资都高、并且不是“网络工程系”的教师信息；

select *
from teacher,department
where teacher.dno = department.dno and department.dname<>'网络工程系'
    and teacher.tsalary > 
    (select max(tsalary) from teacher b_t, department b_d
    where b_t.dno = b_d.dno and b_d.dname = '计算机科学系');

20.查询没有参与项目“p0001”的教工号、姓名；

select teacher.tno,tname
from teacher,tm
where teacher.tno = tm.tno and tm.pno = 'p0001';

21.查询参与了所有项目的教师姓名；

select tname
from teacher,
(select tno t_tno, count(distinct pno) t_count
from tm
group by tno) t
where teacher.tno=t.t_tno and t.t_count = 
(select count(distinct pno)
from myproject);

22.查询工资大于3500或者在计算机科学系工作的教师详细信息（要求使用关键字UNION）；

select teacher.*
from teacher,department
where tsalary > 3500
union
select teacher.*
from teacher,department
where teacher.dno = department.dno and department.dname = '计算机科学系'; 

23.查询工资大于3500并且不在计算机科学系工作的教师详细信息（要求使用关键字MINUS）；

select teacher.*
from teacher,department
where tsalary > 3500
minus
select teacher.*
from teacher,department
where teacher.dno = department.dno and department.dname = '计算机科学系'; 

实验内容第三部分（无需截图）

1.列出Teacher表的所有约束，并说明每个约束的具体含义及其对表列取值的影响；

select table_name,constraint_name,constraint_type 
from user_constraints 
where table_name='TEACHER';

2.使用SQL语句在Teacher表中插入2条元组，元组内容任意设置，要求能取空值的列均设置为空（提示：如果插入失败，则查看是否满足基本表的约束条件）；

insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t009', '张三一', '男', null, To_date('7-7月-1977', 'DD-mon-yyyy'), 'd001');
insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t010', '张三二', '男', null, To_date('7-7月-1977', 'DD-mon-yyyy'), 'd001');

3.利用“create table teacher2 as select * from teacher”语句创建表teacher2，并列出Teacher2表的所有约束，比较Teacher2表与Teacher表的约束差异；

create table teacher2 as select * from teacher;
select table_name,constraint_name,constraint_type 
from user_constraints 
where table_name='TEACHER2';
--这里的没有pk约束和fk约束

4.使用带子查询的插入语句把teacher表中的所有男教师插入到teacher2表中；

insert
into teacher2
select * from teacher where tsex like '男';

5.为表Teacher添加check约束，使性别的取值只能为“男”或者“女”；

alter table teacher add constraint check_tsex check(tsex in ('男','女'));

6.删除teacher2表中“计算机科学系”的所有教师；

delete from teacher2
where teacher2.dno = 
(select dno from department
where dname = '计算机科学系');

7.删除teacher2表中的所有教师；

delete teacher2;

8.修改teacher2表，使列tno为主码，主码约束名字为PK_teacher2；

alter table teacher2 add constraint PK_teacher2 primary key(tno);

9.为teacher2表添加唯一约束，使tname的取值不能重复；

alter table teacher2 add constraint un_name unique(tname);

10.修改teacher2表，使列dno成为外码，引用department表的主码dno，当删除department表中的元组时，级联删除Teacher2表中的元组（提示：删除并重新创建外码约束，使用ON DELETE CASCADE选项）；

alter table teacher2 add constraint fk_department 
foreign key (dno)references department (dno) on delete cascade;

11.在department表中插入一个新系，系号为“xyz”，在Teacher2表中为该新系添加两个教师信息；

insert into department values('xyz',null,'人工智能系');
insert into teacher2(tno, tname, tsex, tsalary, tbirthday, dno) values('t011', '张一', '男', 8000, To_date('7-7月-1977', 'DD-mon-yyyy'), 'xyz');
insert into teacher2(tno, tname, tsex, tsalary, tbirthday, dno) values('t012', '张二', '男', 3500, To_date('8-7月-1987', 'DD-mon-yyyy'), 'xyz');

12.分别写出删除department表中系号为d001和xyz的记录的SQL语句并执行，比较并分析执行结果（提示：在Teacher表和Teacher2表中的外码定义是不同的）；

delete from department
where dno = 'd001';
delete from department
where dno = 'xyz';

13.在tm中插入一条元组，只设置tno、pno的值；

insert into tm(tno,pno) values('t008','p0004');

14.给teacher表中的所有教师的工资增加100；

update teacher set tsalary=tsalary+100;

15.给teacher表中的“计算机科学系”教师的工资增加100；

update teacher set tsalary=tsalary+100 
where tno in
(select teacher.tno from department,teacher 
where teacher.dno=department.dno and department.dname like '计算机科学系');

16.创建两个视图VT、VT2，两个视图均为包含所有teacher表的男教师的信息，但视图VT2的定义带有with check option选项，设置一条女教师信息记录，指出通过哪个视图可以成功插入记录，并说明with check option选项的作用；

create view VT as select * from teacher where tsex='男';
create view VT2 as select * from teacher where tsex='男' with check option;
insert into VT(tno,tname,tsex,tsalary,tbirthday,dno) values('t013','李一','女',3000,To_date('7-7月-1985', 'DD-mon-yyyy'), 'd001');
insert into VT2(tno,tname,tsex,tsalary,tbirthday,dno) values('t013','李一','女',3000,To_date('7-7月-1985', 'DD-mon-yyyy'), 'd001');
--VT视图可以插入，因为VT2有with check option约束条件，限制了插入的条件

五. 实验总结

（简要总结）
通过本次实验，我学会了运用SQL语言进行数据查询和数据更新，以及对基本表、视图、索引的管理。其中select查询语句的格式较多，因此查询的搭配也比较复杂，需熟悉select语句的基本原理才能更好地进行查询。除此之外，约束查询语句select table_name,constraint_name,constraint_type from user_constraints where table_name like ‘表名称’ 的表名称需要英文大写的字符串，否则查询不出来。