ORACLE_SQL

--建立学生表
create table Student (
       Sno char(9) primary key,
       Sname char(20)unique,
       Sex  char(4),
       Sage int,
       Sdept char(20)
);

drop table Student;
commit;
select *from student

--向student表中增加“入学时间”字段 为日期型的
alter table student add S_entrance date;
--删除“入学时间”字段   报错 缺乏关键字？？  必须要加上column关键字
alter table student drop column S_entrance;
--将学生表中的char型数据修改为 varchar2型  因为 char是不可变长的  varchar可变长 节省空间？？类型不兼容
alter table student modify (Sno varchar2(9));

update  student set s_entrance='1-1月-2015' where sname='李勇';

--建立一个课程的表
create table Course(
       Cno char(4)primary key,
       Cname char(40) not null,
       Cpno char(4),
       Ccredit int,
       foreign key (Cpno) references Course(Cno)
);

--建立学生选课表

create table SC (
       Sno char(9),
       Cno char(4),
       Grade int,
       primary key (Sno,Cno),
       foreign key (Sno) references Student(Sno),
       foreign key (Cno) references Course(Cno)
);

drop table sc;


select * from student;

insert into Student values('201215121','李勇','男',20,'CS');
insert into Student values('201215122','刘晨','女',19,'CS');
insert into Student values('201215123','王敏','女',18,'MA');
insert into Student values('201215124','张立','男',19,'CS');



select * from Course order by Cno;
--插入课程表中数据
--出现了错误 因为外键的约束条件？解决方案 ：1先插入没有外键联系的  再插入其他的
--2.修改约束
insert into Course (Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4);
insert into Course (Cno,Cname,Ccredit)values('2','数学',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','数据结构','5',4);
insert into Course (Cno,Cname,Ccredit)values('6','数据处理',4);
insert into Course (cno,Cname,Cpno,Ccredit)values('7','PASCAL','6',4);

--更新表中的数据
update Course set cname='数据结构C版'where cno=5;

insert into SC values('201215121','1',92);
insert into SC values('201215121','2',85);
insert into SC values('201215121','3',88);
insert into SC values('201215122','2',90);
insert into SC values('201215122','3',80);

select * from SC;