SQL1，CREATE SCHEMA

 来自《数据库系统概论》王珊，萨师煊 高等教育出版社

CREATE SCHEMA
CREATE SCHEMA <schemaName> AUTHORIZATION <username>
CREATE SCHEMA <schemaName> AUTHORIZATION <username> [ <table definition clause> | <view definition clause> | <grant clause>]
创建模式的同时创建其中的表，索引和授权定义
Demo:
CREATE SCHEMA TEST AUTHORIZATION ZHANG
 CREATE TABLE TAB1(COL1 SAMLLINT,
                                 COL2 INT
                               );
DROP SCHEMA
DROP SCHEMA <schemaname> <CASCADE | RESTRICT>

DROP SCHEMA TEST CASCADE

CREATE TABLE
CREATE TABLE <TABLENAME> (<COLNAME> <DATATYPE> [COLUMN LEVEL CONSTRAINT]
                                              [, <COLNAME> <DATATYPE> [COLUMN LEVEL CONSTRAINT]]
                                              [, <TABLE LEVEL CONSTRAINT>]
                                             );
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则可以定义在列级也可以定义在表级。

CONNECT TO AD USER ADAMS;
CREATE TABLE ADAMS.STUDENT ( SNO CHARACTER (9)  NOT NULL , SNAME VARCHAR (20)  NOT NULL , SSEX CHARACTER (4)  NOT NULL , SAGE SMALLINT  NOT NULL , SDEPT VARCHAR (20)  NOT NULL  , CONSTRAINT student_pkey PRIMARY KEY ( SNO) , CONSTRAINT student_unique_name UNIQUE ( SNAME)  ) ;
CONNECT RESET;
姓名取唯一值

CONNECT TO AD;
CREATE TABLE ADAMS.COURSE (
CNO CHARACTER (4)  NOT NULL ,
CNAME VARCHAR (40)  NOT NULL ,
CPNO CHARACTER (4) ,
CCREDIT SMALLINT  NOT NULL  ,
CONSTRAINT COURSE_PKEY PRIMARY KEY ( CNO) ,
CONSTRAINT COURSE_FKEY1 FOREIGN KEY (CNO) REFERENCES ADAMS.COURSE (CNO)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED  ENABLE QUERY OPTIMIZATION  ) ;
CONNECT RESET;
对自身的一个属性进行参照

CONNECT TO AD;
CREATE TABLE ADAMS.SC ( SNO CHARACTER (9)  NOT NULL , CNO CHARACTER (4)  NOT NULL , GRADE SMALLINT  NOT NULL  , CONSTRAINT SC_PKEY PRIMARY KEY ( SNO, CNO) , CONSTRAINT SC_FKEY1 FOREIGN KEY (SNO) REFERENCES ADAMS.STUDENT (SNO)  ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED  ENABLE QUERY OPTIMIZATION , CONSTRAINT SC_PKEY2 FOREIGN KEY (CNO) REFERENCES ADAMS.COURSE (CNO)  ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED  ENABLE QUERY OPTIMIZATION  ) ;
CONNECT RESET;
两个属性构成主码必须定义在表级，属性可以同时是主键和外键

INSERT INTO Student VALUES ('200215121', '李勇','男', 20, 'CS'), ('200215122', '刘晨', '女', 19, 'CS'), ('200215123', '王敏', '女', 18, 'MA'), ('200215125', '张立', '男', 19, 'IS');
INSERT INTO Course VALUES ('1', '数据库', '5', 4),
('2', '数学', NULL, 2),
('3', '信息系统', '1', 4),
('4', '操作系统', '6', 3),
('5', '数据结构', '7', 4),
('6', '数据处理', NULL, 2),
('7', 'PASCAL语言', '6', 4);

INSERT INTO SC VALUES ('200215121', '1', 92),
('200215121', '2', 85),
('200215121', '3', 88),
('200215122', '2', 90),
('200215122', '3', 80);

CREATE DATABASE AD AUTOMATIC STORAGE YES ON 'H:/' USING CODESET UTF-8 TERRITORY CN ;
UPDATE DB CFG FOR AD USING AUTO_MAINT ON;
UPDATE DB CFG FOR AD USING AUTO_TBL_MAINT ON;
UPDATE DB CFG FOR AD USING AUTO_RUNSTATS ON;
UPDATE ALERT CFG FOR DATABASE ON AD USING db.db_backup_req SET THRESHOLDSCHECKED YES;
UPDATE ALERT CFG FOR DATABASE ON AD USING db.tb_reorg_req SET THRESHOLDSCHECKED YES;
UPDATE ALERT CFG FOR DATABASE ON AD USING db.tb_runstats_req SET THRESHOLDSCHECKED YES;