-----创建students表。
create table students(
SNO number(6)primary key,
sname char(20) not null,
age number(3),
sex char(10)check(sex in('男','女')),
bplace char(20)
);
check(sex in('男','女')约束为只能选择男和女。
----往students表写入数据。
insert into students(sno,sname,age,sex,bplace) values(990027,'胡伟',22,'男','湖南');
insert into students(sno,sname,age,sex,bplace) values(990652,'张春明',24,'男','河北');
insert into students(sno,sname,age,sex,bplace) values(990668,'王翌',22,'女','四川');
insert into students(sno,sname,age,sex,bplace) values(990674,'丁晓春',24,'男','黒龙江');
insert into students(sno,sname,age,sex,bplace) values(990676,'贺正生',23,'男','湖南');
insert into students(sno,sname,age,sex,bplace) values(990684,'刘文革',24,'女','辽宁');
insert into students(sno,sname,age,sex,bplace) values(991091,'程会军',23,'男','山西');
insert into students(sno,sname,age,sex,bplace) values(991051,'程军',23,'工','山西');
commit; //输入完成后一定要COMMIT一下。
select * from students;
--------创建course表。
create table courses(
cno char(10) primary key,
cname char(20),
credit number(2)
);
insert into courses(cno,cname,credit) values('C1','数据库',3);
insert into courses(cno,cname,credit) values('C2','数据结构',3);
insert into courses(cno,cname,credit) values('C3','操作系统',4);
insert into courses(cno,cname,credit) values('C4','软件工程',3);
commit;
select * from courses;
-------创建teachers表
create table teachers(
TNO NUMBER(10) PRIMARY KEY,
TNAME CHAR(20),
AGE NUMBER(3),
PS CHAR(20)
);
INSERT INTOTEACHERS(TNO,TNAME,AGE,PS)VALUES(1420,'周振华',38,'副教授');
INSERT INTO TEACHERS(TNO,TNAME,AGE,PS) VALUES(1481,'刘建平',30,'讲师');
INSERT INTO TEACHERS(TNO,TNAME,AGE,PS) VALUES(1433,'王志伟',28,'讲师');
commit;
select * from teachers;
----创建enrolls表
Create TABLE ENROLLS(
SNO NUMERIC(6) NOT NULL,
CNO CHAR(10) NOT NULL,
GRADE INT,
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO),
FOREIGN KEY(CNO) REFERENCES COURSES(CNO),
CHECK ((GRADE IS NULL) or (GRADE BETWEEN 0 AND 100))
);
注意：当表与表之间用外键关联时，关联列的字段类型以及字段类型的数值一定要一致。
select * from enrolls;
insert into enrolls(sno,cno,grade) values(990027,'C1',90);
insert into enrolls(sno,cno,grade) values(990027,'C3',95);
insert into enrolls(sno,cno,grade) values(990027,'C4',88);
insert into enrolls(sno,cno,grade) values(990652,'C1',88);
insert into enrolls(sno,cno,grade) values(990652,'C4',83);
insert into enrolls(sno,cno,grade) values(990668,'C3',84);
insert into enrolls(sno,cno,grade) values(990674,'C2',77);
insert into enrolls(sno,cno,grade) values(990676,'C3',90);
insert into enrolls(sno,cno,grade) values(990684,'C3',85);
insert into enrolls(sno,cno,grade) values(990684,'C1',82);
insert into enrolls(sno,cno,grade) values(991091,'C2',93);
commit;
select * from enrolls;
create table teaching(
cno char(10) primary key,
classes char(10),
tno number(4),
snum number(3),
foreign key(cno) references courses(cno)
);
alter table teaching drop primary key;
insert into teaching(cno,classes,tno,snum) values('C1','E851',1420,30);
insert into teaching(cno,classes,tno,snum) values('C2','E851',1420,22);
insert into teaching(cno,classes,tno,snum) values('C3','E651',1481,30);
insert into teaching(cno,classes,tno,snum) values('C3','E852',1481,28);
insert into teaching(cno,classes,tno,snum) values('C4','E851',1433,24);
insert into teaching(cno,classes,tno,snum) values('C1','E852',1420,28);
commit;
create table girl as select sno,sname,age from students where sex='女';
select * from girl;
alter table teachers add (addr char(50));
select * from teachers;
alter table students drop column bplace;
注意：不能用用“as sysdba” 登陆，否则是删除不掉你建的表中的列。
----------待续中
