oracle数据库的一些简单操作

苏5669

一、建表
create table D(dno number(6,0) not null primary key,
dname varchar2(20)) ;


create table s(sno char(10) not null primary key,
sname varchar2(20),ssex char(2),
sage number(3,0),
home varchar2(20),dno number(6,0)) ;


create table c(cno char(10) not null primary key,cname varchar2(20),credit number) ;



create table sc(sno char(10) not null,cno char(10) not null, grade number(4,1),primary key(sno,cno)) ;



create cluster t_tc(tno char(10))
index;
create table t(tno char(10) not null primary key,
tname varchar2(20),tsex char(2),tage number(3,0),
rank char(10),sal number(8,2),dno number(6,0))
cluster t_tc(tno);
create table tc(tno char(10) not null,cno char(10) not null)
cluster t_tc(tno);
create index index_t_tc on cluster t_tc;


alter table c
add(ctime number(3,0)) ;


alter table sc
add foreign key(cno) references c(cno);
alter table sc
add foreign key(sno) references s(sno);


create sequence mysquence
increment by 1
start with 1
nomaxvalue ;


create public synonym trg for sys.trigger$ ;



///////////////////////

输入数据
insert into d values ('1','机械系') ;
insert into d values ('2','计算机系') ;
insert into d values ('3','管理系') ;

insert into s values ('0203123','李杰','女',19,'北京',2) ;
insert into s values ('0203125','王浩','男',21,'天津',2) ;
insert into s values ('0203201','刘立','男',18,'河北',2) ;
insert into s values ('0103116','孙明','女',21,'天津',1) ;
insert into s values ('0103118','黄宏','女',21,'上海',1) ;


insert into c values ('9001','C语言',4,null) ;
insert into c values ('9002','java程序设计',3,null) ;
insert into c values ('9003','计算机原理',4,null) ;
insert into c values ('8004','机械原理',4,null) ;
insert into c values ('1001','英语',3,null) ;
insert into c values ('1002','高等数学',6,null) ;




insert into sc values ('0203123','9001',85) ;
insert into sc values ('0203125','9001',75) ;
insert into sc values ('0103116','9001',90) ;
insert into sc values ('0103118','9001',75) ;
insert into sc values ('0103116','8004',85) ;
insert into sc values ('0103118','8004',80) ;
insert into sc values ('0203123','1002',75) ;



insert into t values ('101','王刚','男',30,'讲师',1500,1) ;

insert into t values ('201','李华','女',45,'副教授',2000,2) ;

insert into t values ('202','陈晓','女',26,'讲师',1500,2) ;

insert into t values ('301','肖天','男',40,'教授',3000,3) ;


insert into tc values ('101','9003') ;
insert into tc values ('201','9001') ;
insert into tc values ('201','9002') ;
insert into tc values ('202','1001') ;
insert into tc values ('301','1002') ;

create table s_02 (sno char(10),sname varchar2(20),ssex char(2));
insert into s_02  select sno,sname,ssex from s
where sno like '02%' ;


update t set rank=(select rank from t where tno=301), sal=(select sal from t where tno=301) where tno=101 ;


delete from s_02 where ssex=(select ssex from s_02 where sname='王浩') ;

create view myview1 as select s.sno,sname,dname,grade from s,d,sc
where sc.cno=(select cno from c where cname='C语言')
and sc.sno=s.sno
and s.dno=d.dno ;

新建视图
create view shaoview as
select sno,avg(grade) as 平均成绩,count(*) as 课程总数
from sc
group by sno