第二章 建表和约束

/删除表/
drop table infos;
/创建表/
CREATE TABLE INFOS (
STUID VARCHAR2(7) NOT NULL , –学号 学号=‘S’+班号+2位序号
STUNAME VARCHAR2(10) NOT NULL, –姓名
GENDER VARCHAR2(2) NOT NULL,–性别
AGE NUMBER(2) NOT NULL, –年龄
SEAT NUMBER(2) NOT NULL, –座号
ENROLL DATE, –入学时间
STUADDRESS VARCHAR2(50) DEFAULT ‘地址不详’, –住址
CLASSNO VARCHAR2(4) NOT NULL –班号 班号=学期序号+班级序号
);
/添加备注/
COMMENT ON table INFOS IS ‘个人信息’;
comment on column INFOS.STUID is ‘id’;
comment on column INFOS.STUNAME is ‘姓名’;
comment on column INFOS.GENDER is ‘性别’;
comment on column INFOS.AGE is ‘年龄’;
comment on column INFOS.SEAT is ‘座号’;
comment on column INFOS.ENROLL is ‘入学时间’;
comment on column INFOS.STUADDRESS is ‘地址’;
comment on column INFOS.CLASSNO is ‘班号’;

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。

ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ;–设置主键约束

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER –性别
CHECK(GENDER=’男’ OR GENDER=’女’) ;–设置验证约束

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT
CHECK(SEAT>0 AND SEAT<=50); –设置验证约束

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE
CHECK(AGE>=0 AND AGE<=100) ; –设置验证约束

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO
CHECK((CLASSNO >=’1001’ AND CLASSNO<=’1999’) OR (CLASSNO>=’2001’ AND CLASSNO<=’2999’));

ALTER TABLE INFOS ADD CONSTRAINT UN_STUNAME UNIQUE(STUNAME); –设置唯一约束
ALTER TABLE INFOS ADD CONSTRAINT UN_CLASNO_SEAT UNIQUE(CLASSNO,SEAT); –设置唯一约束

ALTER TABLE infos ADD CONSTRAINT FK_CLASSNO_ID
FOREIGN KEY(CLASSNO) REFERENCES class(ID); -设置外键约束

–删除表的约束
–alter table 约束基于的表名 drop constraint 约束名
alter table infos drop constraint CK_INFOS_AGE;

–修改字段是否为空
ALTER TABLE infos MODIFY(ENROLL null);

/把emp中ename的字段长度修改为10 /
alter table emp modify(ename varchar2(20));
select * from emp2;
alter table emp rename column ename to empname;
alter table emp rename to emp2;

/根据结果集创建表/
CREATE TABLE INFOS2 AS SELECT * FROM INFOS1;

CREATE TABLE INFOS3 AS SELECT * FROM INFOS1 where 1=2;