文件 No.1 db_init.txt

最新推荐文章于 2024-10-15 14:59:42 发布

DLNU-linglian

最新推荐文章于 2024-10-15 14:59:42 发布

阅读量507

点赞数

分类专栏：文件文章标签： sql

本文链接：https://blog.csdn.net/linglian0522/article/details/70665289

版权

文件专栏收录该内容

3 篇文章 4 订阅

订阅专栏

/**建立三张表，并插入数据 */
set feedback off; 
show user;
drop table sc;
drop table s;
drop table c;


create table s(
  sno char(4) not null primary key,
  sname varchar2(10),
  sex char(3),
  dept varchar2(4),
  age int 
);
insert into s values('801','金贤重','男','CS',23);
insert into s values('802','李敏镐','男','MA',25);
insert into s values('803','那英','女','CS',41);
insert into s values('804','金秀贤','女','MA',24);
insert into s values('805','刘德华','男',null,52);
insert into s values('806','文章','男','CS',27);
insert into s values('807','刘欢','男','MA',51);
insert into s values('808','马伊琍','女','CS',36);
insert into s values('809','姚笛','女','MA',23);
insert into s values('810','周杰伦','男',null,39);


create table c(
  cno char(3) primary key,
  cname varchar2(20),
  credit int
);
insert into c values('01','数据库概论',3);
insert into c values('02','OS',4);
insert into c values('03','OOP',2);
insert into c values('04','Java',3);
insert into c values('05','Python',3);
insert into c values('06','D语言讲座',2);





create table sc(
  sno char(4),
  cno char(3),
  grade numeric(3),
  constraint sc_pk primary key(sno,cno),
  constraint sc_fk1 foreign key(sno) references s(sno),
  constraint sc_fk2 foreign key(cno) references c(cno),
  constraint sc_grade check (grade between 0 and 100 or grade is null)
);

insert into sc values('801','01',81);
insert into sc values('802','01',59);
insert into sc values('803','01',0);
insert into sc values('804','01',60);
insert into sc values('805','01',30);
insert into sc values('806','01',90);
insert into sc values('807','01',60);
insert into sc values('808','01',null);  



insert into sc values('801','02',null);
insert into sc values('802','02',30);
insert into sc values('803','02',45); 
insert into sc values('804','02',80);
insert into sc values('805','02',70);
insert into sc values('806','02',60); 




insert into sc values('801','03',90);
insert into sc values('802','03',60);
insert into sc values('803','03',40);
insert into sc values('804','03',null);
insert into sc values('805','03',50);
insert into sc values('806','03',70);
insert into sc values('807','03',70);
insert into sc values('808','03',80);


insert into sc values('801','04',40);
insert into sc values('802','04',0);
insert into sc values('803','04',null); 
insert into sc values('804','04',80);
insert into sc values('805','04',90);   
insert into sc values('806','04',60);
insert into sc values('807','04',70);
insert into sc values('808','04',50); 

insert into sc values('809','05',0);  

commit;

select * from s;
select * from c;
select * from sc;