数据库原理-实验一--sql语句

📍实验目的

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

📍实验内容第一部分

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(40),
     cpno char(4),
     ccredit smallint,
     foreign key(cpno) references course(cno) 
);

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

create table sc
(
    sno char(9),
    cno char(4),
    grade smallint,
    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 varchar(9);
初值为null

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

alter table Student modify ssex varchar(6);

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

alter table student drop column senrollment;

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

create view ds as 
select * from student 
where student.sno in
(select sno from sc where sc.cno in (select cno from course where cname = '数据库'))

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

create view maleStudent as select * from Student where ssex='男';

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 （在sys下删）

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

drop table student; 不行，因为整装删除的student的外键或主键被使用
drop table student CASCADE CONSTRAINTS;

15.删除基本表SC；

drop table sc;

📍实验内容第二部分

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

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

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

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

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

select tno from teacher where teacher.tno in (select tno from myproject);

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 length(tname)=3;

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 myproject /*distinct过滤重复项*/

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

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

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

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

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

create view departmentSalary (dno,avg_sly,sum_sly,max_sly,min_sly)
as select dno,avg(tsalary),sum(tsalary),max(tsalary),min(tsalary) 
from teacher group by dno order by dno;
(记得先让sys给权限）

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

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

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

select department.dno,dname,avg(tsalary) as 平均工资 
from department,teacher 
where department.dno=teacher.dno group by department.dno,dname
having avg(tsalary)>4500 /*筛选*/

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

select t.tno,t.tname,p.pname 
from teacher t
left join myproject p
on t.tno=p.tno
order by t.tno

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

/*1.自身连接*/
select t1.*,t2.*
from teacher t1
join teacher t2
on t1.tname='李小龙' and t1.tsalary=t2.tsalary;

/*子查询*/
select * from teacher where tsalary in(select tsalary from teacher where tname='李小龙');

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

select teacher.* from teacher,myproject where pname='云计算研究' and tsalary > 4000;

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

select t.tno,t.tname,2021-extract(year from t.tbirthday) as age,t.tsalary as tyear 
from teacher t,
(select dno,AVG(tsalary) as avf_salary from teacher group by dno) c 
where c.dno = t.dno and t.tsalary <= avf_salary

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

select b.* 
from teacher b,
(select avg(a.tsalary) as acg_salary from (select * from teacher t where t.dno in (select d.dno from department d where d.dname = '计算机科学系')) a) c
where b.tsalary > c.acg_salary and b.dno not in (select p.dno from department p where p.dname = '网络工程系')

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

select t.tno,t.tname 
from teacher t
where t.tno not in (select p.tno from myproject p where p.pno='p0001');

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

select t.tname 
from teacher t 
where t.tno in (select tno from (select tno,count(pno) as num from Tm group by tno) where num = 4)

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

select * from teacher where tsalary>3500
union
select * from teacher where dno in (select dno from department where dname='计算机科学系')

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

select * from teacher where tsalary>3500
minus
select * from teacher where dno in (select dno from department where dname='计算机科学系')

📍实验内容第三部分

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

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

TEACHER	SYS_C007040	C
TEACHER	SYS_C007041	C
TEACHER	SYS_C007042	C
TEACHER	SYS_C007043	C
TEACHER	SYS_C007044	C
TEACHER	PK_TEACHER	P 
TEACHER	FK_TEACHER_BELONGTO_DEPARTME 	R

C: 检查约束，设置字段的数据特性
P：主键约束，确定表中的标识列标识，确定一个对象的唯一表现
R：外键约束，确定表与表之间的联系方式

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

insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t009', '周杰伦', '男', null, To_date('18-1月-2002', 'DD-mon-yyyy'), 'd001');
insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t010', '林俊杰', '男', null, To_date('10-1月-2003', '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 like 'TEACHER2';

TEACHER2	SYS_C007052	C
TEACHER2	SYS_C007053	C
TEACHER2	SYS_C007054	C
TEACHER2	SYS_C007055	C
TEACHER2	SYS_C007056	C
相比teacher缺少主键约束和外键约束

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

insert into teacher2 (select * from teacher where tsex = '男')

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

alter TABLE Teacher add CHECK (tsex = '男' or tsex = '女');

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

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

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 constraint un_teacher2_tname unique(tname);

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

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

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

insert into department VALUES('xyz',null,'网安');
insert into teacher2 VALUES('t011','xyz', '五月天', '男', null, To_date('8-1月-2010', 'DD-mon-yyyy'));
insert into teacher2 VALUES('t012', 'xyz','周星驰', '男', null, To_date('7-1月-1990', 'DD-mon-yyyy'));

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

delete from department where dno = 'd001' ;  /*报错，违反完整性约束*/
delete from department where dname = 'xyz';

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

insert into tm(tno, pno) VALUES('t010','p0001');

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

update teacher set tsalary = tsalary+100;

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

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

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

create view v1 as select *from teacher where tsex='男';
create view v2 as select *from teacher where tsex='男' with check option;
insert into v1(tno,tname,tsex,tsalary,tbirthday,dno) values('t020','张杰','男',3000,To_date('20-11月-1995', 'DD-mon-yyyy'), 'd001'); /*成功*/
insert into v2(tno,tname,tsex,tsalary,tbirthday,dno) values('t015','张曼玉','女',3000,To_date('20-12月-1995', 'DD-mon-yyyy'), 'd001'); /*失败*/

若没有with check option ，可以任意修改视图；
若有with check option，
1.对于update,有with check option，要保证update后，数据要被视图查询出来；
2.对于delete,有无with check option都一样；
4.对于insert,有with check option，要保证insert后，数据要被视图查询出来；
5.对于没有where 子句的视图，使用with check option是多余的。

📍实验总结

1.基本操作

通过本次实验，先了解了schema、基本表、索引、视图的概念，然后实践了对表的操作（建表、修改表（增删改查记录）、删表）。
建表：create，注意设置关系完整性（主键、外键等等）
改表：
1.增加记录：insert
2.删除记录：delete，若当级联删除元组时，可以使用on delete cascade修改表结构
3.修改记录：alter
4.查询记录：select，体会各种查询方法的用法（简单查询、集合查询、连接查询、嵌套查询等等）
删表：drop

2.基本表、视图、索引的区别：

1.基本表是数据库中存储数据的实体，包含多条记录和多个字段。基本表的数据是实时存储的，可以被查询、插入、修改和删除。
2.视图是一种虚拟的表，不存储数据，仅仅是根据查询语句生成的虚拟表。视图的作用是简化数据查询和操作，可以隐藏数据表中的某些列或行。视图可以与基本表一样被查询，也可以被更新，但是视图本身不包含任何数据。
3.索引是一种数据结构，用于提高数据查询的效率。索引可以看做是一张表格，其中每一行对应着一个数据记录，每一列对应着一个数据字段。索引可以加速数据查询的速度，因为它提供了快速查找数据的方式，避免了全表扫描的低效率查询。索引可以建立在基本表或视图上，但是不存储数据，只存储基本表或视图的字段值和指向相应记录的指针。
总之，基本表是存储数据的实体，包含多条记录和多个字段；视图是一种虚拟的表，用于简化数据查询和操作；索引是提高数据查询效率的数据结构，可以加速数据查询的速度。

3.约束：

主键(primary key)：
用于唯一标识一条记录的字段或字段组合。不能重复、不能为空、且一个表中只能有一个主键。

唯一键(unique)：
用于确保数据表中某个字段或字段组合的值唯一的约束。可以为空，且可以设置多个。
（思考：已经存在了主键来约束数据不能重复为什么还要有唯一键存在呢?）
其实主键和唯一键并不冲突，在一个表中可以有一个主键，也可以同时具有唯一键。例如我们创建一个学生表，我们人身上是有很多具有唯一性的属性的如学号、身份证号、以及各种社交账号。同时也有一些不具有唯一性的属性如姓名、身高、年龄等等。若是只有主键存在的话，那么我们身上的其他具有唯一性的属性怎么来保证。若我们用主键约束了身份证号唯一，但学号就可能出现相同（不同学校的学生），这是不允许的，因此可以使用唯一键对学号进行约束。（当然使用身份证号和学号属性组合作为主键也可以）

外键：
外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。例如A表中的一个字段，是B表的主键，那它就可以是A表的外键。

非空约束：
用于保证某个字段不为空，非空约束可以确保表中某个字段的值不会为NULL。

检查约束：
用于检查某个字段的取值是否符合指定的条件，检查约束可以确保表中某个字段的值满足一定的要求，如取值范围、数据类型等。