第01章 表格及约束

--创建表
CREATE TABLE tbl_studentinfo (
    stuno CHAR(5) NOT NULL,
    stuname VARCHAR2(20) NOT NULL,
    stubirth DATE,
    stusex CHAR(1) DEFAULT '0',
    stuaddr VARCHAR2(200) ,
    stutel VARCHAR2(11)
);


CREATE TABLE tbl_classinfo(
    classno CHAR(3) NOT NULL,
    classname VARCHAR2(10) NOT NULL
);
CREATE TABLE tbl_scoreinfo(  
    stuno CHAR(5) NOT NULL,
    classno CHAR(3) NOT NULL,
    score NUMBER(3,1)
);


--删除表
DROP TABLE tbl_studentinfo;
DROP TABLE tbl_classinfo;
DROP TABLE tbl_scoreinfo;

--查询表数据
select * from tbl_studentinfo


ALTER TABLE tbl_studentinfo_new RENAME TO tbl_studentinfo_new1--修改表名
ALTER TABLE tbl_studentinfo RENAME COLUMN stutel TO phone --修改表列名
ALTER TABLE tbl_studentinfo RENAME COLUMN phone TO stutel--修改表列名


ALTER TABLE tbl_studentinfo ADD upstuno CHAR(5);--添加字段
ALTER TABLE tbl_studentinfo ADD upstuno CHAR(5) DEFAULT NULL--添加字段及属性
ALTER TABLE tbl_studentinfo DROP COLUMN upstuno;--删除字段
ALTER TABLE tbl_studentinfo MODIFY (stubirth Date not null)--修改字段属性
ALTER TABLE tbl_studentinfo MODIFY stubirth Date null--修改字段属性


--创建主键
--1
CREATE TABLE TBL_STUDENTINFO (
STUNO CHAR(5) PRIMARY KEY
)
--2
CREATE TABLE TBL_STUDENTINFO (
STUNO CHAR(5),
CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)
)
--3
CREATE TABLE TBL_STUDENTINFO (
STUNO CHAR(5)
)
ALTER TABLE TBL_STUDENTINFO ADD PRIMARY KEY (STUNO);


--外键约束(参照的表必须是主键)
ALTER TABLE tbl_scoreinfo ADD FOREIGN KEY (stuno) REFERENCES tbl_studentinfo(stuno);
--唯一约束
ALTER TABLE tbl_studentinfo ADD UNIQUE (stuname);
--检查约束
ALTER TABLE tbl_scoreinfo ADD CHECK (score >=0 AND score <= 150)


--删除约束
ALTER TABLE tbl_studentinfo DROP CONSTRAINT SYS_C004466;



--创建表及对应的约束
--1
CREATE TABLE TBL_STUDENTINFO (
STUNO CHAR(5) PRIMARY KEY,
STUNAME VARCHAR2(20) NOT NULL,
STUBIRTH DATE,
STUSEX CHAR(1),
STUADDR VARCHAR2(200),
STUTEL VARCHAR2(11) UNIQUE
)
CREATE TABLE TBL_SCOREINFO (
STUNO CHAR(5) REFERENCES TBL_STUDENTINFO(STUNO),
CLASSNO CHAR(3) NOT NULL,
SCORE NUMBER(3,1) CHECK (SCORE >=0 AND SCORE <= 100)
)
--2
CREATE TABLE TBL_STUDENTINFO (
STUNO CHAR(5) NOT NULL,
STUNAME VARCHAR2(20),
STUBIRTH DATE,
STUSEX CHAR(1),
STUADDR VARCHAR2(200),
STUTEL VARCHAR2(11),
CONSTRAINT PK_STUNO PRIMARY KEY (STUNO),
CONSTRAINT QK_STUTEL UNIQUE (STUTEL)
)
CREATE TABLE TBL_SCOREINFO (
STUNO CHAR(5),
CLASSNO CHAR(3) NOT NULL,
SCORE NUMBER(3,1),
CONSTRAINT FK_STUNO FOREIGN KEY (STUNO) REFERENCES TBL_STUDENTINFO (STUNO),
CONSTRAINT CK_SCORE CHECK (SCORE >=0 AND SCORE <= 100)
);
--3
CREATE TABLE TBL_STUDENTINFO (
STUNO CHAR(5),
STUNAME VARCHAR2(20),
STUBIRTH DATE,
STUSEX CHAR(1),
STUADDR VARCHAR2(200),
STUTEL VARCHAR2(11),
UPSTUNO CHAR(5)
);
ALTER TABLE TBL_STUDENTINFO ADD  PRIMARY KEY (STUNO);


CREATE TABLE TBL_SCOREINFO (
STUNO CHAR(5),
CLASSNO CHAR(3),
SCORE NUMBER(3,1)
);
ALTER TABLE TBL_SCOREINFO ADD PRIMARY KEY (STUNO,CLASSNO);
ALTER TABLE TBL_SCOREINFO ADD FOREIGN KEY (STUNO) REFERENCES TBL_STUDENTINFO(STUNO);
ALTER TABLE TBL_SCOREINFO ADD FOREIGN KEY (CLASSNO) REFERENCES TBL_CLASSINFO(CLASSNO);