SQL——数据库实验一

最新推荐文章于 2024-08-25 09:19:41 发布

纵马且长歌

最新推荐文章于 2024-08-25 09:19:41 发布

阅读量3.4k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/no2015214099/article/details/78619235

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这篇博客介绍了SQL数据库的一些基本操作，包括创建student、course和sc三张表，对表进行修改，添加唯一约束和检查约束，创建及删除索引，以及插入、更新和删除数据的操作。内容涵盖数据表结构设计、数据完整性、索引管理和数据操作。

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

create table student 
(
 sno number primary key,
 sname varchar2(8) not null unique,
 sage int,
 ssex varchar2(8),
 sdept varchar2(10)
);

create table course
(
cno number primary key,
cname varchar2(10),
cpno number,
ccredit int
);

create table sc
(
sno number,
cno number,
primary key(sno,cno),
grade smallint,
foreign key(sno) REFERENCES student(sno),
foreign key(cno) REFERENCES course(cno)
);

alter table student add bloodtype varchar2(2);

alter table student modify sdept varchar2(40);

alter table student add constraint yueshu1 check(sage>15 and sage<30);

alter table student drop constraint yueshu1;

alter table student drop column bloodtype;

create unique index stusnam4099 on student (sname);

create index i_sc4099 on sc(sno,cno desc);

drop index stusnam4099;

drop index i_sc4099;

insert into student(sno,sname,sage,ssex,sdept)values(200215121,'liyong',20,'m','cs');
insert into student(sno,sname,sage,ssex,sdept)values(200215122,'liuchen',19,'f','cs');
insert into student(sno,sname,sage,ssex,sdept)values(200215123,'wangmin',18,'f','ma');
insert into student(sno,sname,sage,ssex,sdept)values(200215125,'zhangli',19,'m','is');

insert into course(cno,cname,cpno,ccredit)values(1,'shujuku',5,4);
insert into course(cno,cname,cpno,ccredit)values(2,'shuxue',null,2);
insert into course(cno,cname,cpno,ccredit)values(3,'信息系统',1,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(6,'shujuchuli',null,2);
insert into course(cno,cname,cpno,ccredit)values(7,'pascal',6,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='wangmin';

update student set sage=sage+1 ;

update sc set grade = 0 where sno in
(select sno from student where sdept='cs');

delete from student where sdept = (select sdept from student where sname='liuchen'); 

delete from sc where sno in (select sno from student where sdept = 'cs');