数据库实验报告1-SQL语言

（1）实验目的

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

（2）实验要求

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

实验内容1：

–1.创建学生选课关系数据库中的STUDENT表

create table student
(
sno char(9) primary key,
sname char(20) unique,
ssex char(3),
sage smallint,
sdept char(20)
);

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

create table course
(
cno char(4) primary key,
cname char(20),
cpno char(4),
ccredit smallint
);

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

create table sc
(
sno char(9),
cno char(4),
grade smallint
);

–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的值是多少；

–值为null

alter table student add Senrollment date;

–6.修改Student表结构，把Ssex列的宽度设置为6个字节；

alter table student modify  ssex char(6);

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

alter table student drop column Senrollment;

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

create view ds(sname,ssex,sno, sage, sdept) as
select student.sname,student.ssex,student.sno, student.sage, student.sdept from student,sc where student.sno=sc.sno and sc.cno='1';

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

create view malesutdent(sname,ssex,sno, sage, sdept) as
select sname,ssex,sno,sage, sdept 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);

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

create index indexcpno2 on course(cpno);

–13.删除索引indexCpno2；

drop index indexcpno2;

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

–没有失败

drop table student;

–15.删除基本表SC；

drop table sc;

实验内容2：

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

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

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

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

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

select distinct tno from tm where pno is not null;

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

select tname,2022-extract(year from tbirthday) from teacher where tname like '%小%';

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

select tname,2022-extract(year from tbirthday) from teacher where tname like '_小%';

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

select tname,2022-extract(year from tbirthday) from teacher where tname not like '李%' and tname like '___';

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

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

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

select tno, tname, tsex, tsalary, tbirthday, dno from teacher where tsex='女'  or tsalary>4000;

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

select pno,count(tno) as 总人数 from tm group by pno;

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

select count(pno) from myproject where tno in (select tno from teacher where tname='张三');

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

select avg(tsalary) as 平均工资,sum(tsalary) as 工资总和,max(tsalary) as 最高工资,min(tsalary) as 最低工资 from teacher;

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

create view departmentSalary  as select dno,avg(tsalary) as 平均工资,sum(tsalary) as 工资总和,max(tsalary) as 最高工资,min(tsalary) as 最低工资 from teacher group by dno;

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

select * from department,departmentSalary where department.dno=departmentSalary.dno;

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

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

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

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

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

1.子查询：select tno, tname, tsex, tsalary, tbirthday, dno from teacher where tsalary in(select tsalary from teacher where tname='李小龙');

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

select  teacher.tno, tname, tsex, tsalary, tbirthday,dno,tm.pno from teacher,tm where teacher.tno=tm.tno and tm.pno='p0002' and teacher.tsalary>4000;

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

select tno,tname,2022-extract(year from tbirthday) from teacher x where tsalary<=(select avg(tsalary) from teacher y where y.dno=x.dno );

–内层是查询同一个系中的教师平均工资，至于是哪个系的平均工资要看参数x.dno的值

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

select * from teacher where tsalary>all(select tsalary from teacher where dno='d001') and dno!='d003';

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

select tno,tname from teacher where tno  not in(select tno from tm where pno='p0001');

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

select tname from teacher where not exists(select * from myproject where not exists(select * from tm where tno=teacher.tno and pno=myproject.pno ));

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

select * from teacher where tsalary>3500 union select * from teacher where dno='d001';

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

select * from teacher where tsalary>3500 minus select * from teacher where dno!='d001';

实验内容3：

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

外键约束：外键是用来控制数据库中数据的完整性的，对一个表的数操作时，和它关联的一个或多个表的数据同时发生改变。

constraint FK_DEPARTME_MANAGE_TEACHER foreign key (TNO) references Teacher (TNO);
constraint FK_MYPROJEC_PROJECTMA_TEACHER foreign key (TNO) references Teacher (TNO);
constraint FK_TM_TM_TEACHER foreign key (TNO) references Teacher (TNO);
constraint FK_TEACHER_BELONGTO_DEPARTME foreign key (DNO) references Department (DNO);

主键约束：主键是能确定一条记录的唯一标识，带有主键约束的值不能为null也不能重复

constraint PK_TEACHER primary key (TNO);

Not Null约束：不接受NULL值
TNO                  VARCHAR2(20)         not null,
DNO                  VARCHAR2(10)         not null,
TName                NVARCHAR2(20)        not null,
TSex                 VARCHAR2(3)          not null,
TBirthday            DATE                 not null

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

select * from teacher;
insert into teacher(tno, tname, tsex, tbirthday, dno) values('t009', '陈小明', '男',To_date('12-10月-1987', 'DD-mon-yyyy'), 'd001');
insert into teacher(tno, tname, tsex, tbirthday, dno) values('t010', '陈小红', '女',To_date('11-10月-1987', 'DD-mon-yyyy'), 'd002');

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

create table teacher2 as select * from teacher;

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

insert into teacher2(tno, tname, tsex, tsalary, tbirthday, dno)  select tno, tname, tsex, tsalary, tbirthday, dno from teahcer where tsex='男';

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

alter table teacher add check(tsex='男' or tsex='女');

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

delete from teacher2 where dno='d001';

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

delete from teacher2;

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

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

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

alter table teacher2 add unique(tname);

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

alter table teacher2 add constraint FK_TEACHER2_DEARTMENT foreign key(dno) referenceS department(dno) on delete cascade;

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

insert into department(dno,dname) values('xyz','网络安全系');

insert into teacher2(tno, tname, tsex, tsalary, tbirthday, dno) values('t001', '张十', '男', 6000, To_date('7-1月-1990', 'DD-mon-yyyy'), 'xyz');

insert into teacher2(tno, tname, tsex, tsalary, tbirthday, dno) values('t002', '李九', '女', 5000, To_date('1-7月-1990', '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的值；

select * from tm;
insert into tm(tno,pno) values('t005','p0001');

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

update teacher set tsalary=tsalary+100;

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

update teacher set tsalary=tsalary+100 where dno='d001';

–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('t011', '陈小雪', '女', 4000, To_date('1-1月-1988', 'DD-mon-yyyy'), 'd002'); 

–插入成功

insert into VT2(tno, tname, tsex, tsalary, tbirthday, dno) values('t012', '陈小白', '女', 4000, To_date('1-1月-1990', 'DD-mon-yyyy'), 'd002'); 

–插入失败，WITH CHECK OPTION 表示对视图进行UPDATE,INSERT和DELETE操作时要保证更新，插入或删除的行满足视图定义中的谓词条件，女教师不满足性别为男的这个条件，所以插入失败

---

如果大家想要**实验内容2跟实验内容3的初始化代码**，可以私信我