数据库实验一：数据定义和数据更新

最新推荐文章于 2023-05-27 19:34:58 发布

勤奋的懒羊羊o

最新推荐文章于 2023-05-27 19:34:58 发布

阅读量490

点赞数 4

分类专栏： Oracle sql实验

本文链接：https://blog.csdn.net/qq_47299421/article/details/116458343

版权

Oracle sql实验专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这篇博客详细介绍了数据库操作，包括创建学生、课程和选课表，以及添加、修改和删除字段、约束和索引。还展示了如何插入学生和课程数据，并更新和删除记录。最后，进行了表的删除操作，完整地演示了一次数据库管理流程。

摘要由CSDN通过智能技术生成

create table Student(
Sno VARCHAR(15) PRIMARY KEY,
Sname VARCHAR(8),
Sage INT,
Ssex CHAR(2),
Sdept CHAR(20));

create table course(
cno varchar(10) primary key,
cname varchar(10),
cpno varchar(10),
ccredit int,
foreign key(cpno)references course(Cno));

create table sc(
Sno VARCHAR(15),
Cno varchar(10),
Grade int,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references course(Cno));

alter table student ADD BloodType char(2);
alter table student MODIFY (Sdept varchar2(40));
alter table student add CONSTRAINT ck_sage check(sage>15 and sage<30);
alter table student DROP CONSTRAINT ck_sage;
alter table student DROP (BloodType);

create unique index stusnam0352 on student(Sname);
create unique index i_sc0352 on sc(sno ASC,cno DESC);

drop index stusnam0352;
drop index i_sc0352;

Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215121’,‘李勇’,‘男’,20,‘CS’);
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215122’,‘刘晨’,‘女’,19,‘CS’);
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215123’,‘王敏’,‘女’,18,‘MA’);
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215125’,‘张立’,‘男’,22,‘IS’);

insert into Course(Cno,Cname,Cpno,Ccredit) values(‘2’,‘数字’,null,‘2’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘6’,‘数据处理’,null,‘2’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘7’,‘PASCAL语言’,‘6’,‘4’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘4’,‘操作系统’,‘6’,‘3’);
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 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’,‘4’,‘90’);
insert into SC(Sno,Cno,Grade) values(‘200215122’,‘3’,‘80’);

update student set sage=20 where SNAME=‘王敏’;
update student set sage=sage+1;
update sc set Grade=0 where Sno in(select sno from student where sdept=‘CS’);

delete from SC where Sno in(select Sno from Student where Sdept=‘CS’);
delete from student where sdept in(select sdept from student where Sname=‘刘晨’);

drop table sc;
drop table student;
drop table course;

commit work;