四、SQL基础知识--约束和视图

--创建约束的方式
--一、在字段创建时将约束添加到字段之后
CREATE TABLE ZYJ_YUESHU(
       ZYJ_ID VARCHAR(20) NOT NULL PRIMARY KEY,   --创建非空、主键约束
       ZYJ_NAME VARCHAR(20) DEFAULT 'jie' NOT NULL,  --创建默认约束
       ZYJ_NO VARCHAR(20) UNIQUE,    --创建唯一约束
       ZYJ_SEX VARCHAR(20) CHECK(ZYJ_SEX IN('男','女')),
       ZYJ_CHENGJI INT CHECK(ZYJ_CHENGJI>10)
);
COMMENT ON TABLE ZYJ_YUESHU IS 'soulsjie ，学习约束创建的数据表';
COMMENT ON COLUMN ZYJ_YUESHU.ZYJ_ID IS '序号字段 非空 主键';
COMMENT ON COLUMN ZYJ_YUESHU.ZYJ_NAME IS '姓名字段 非空 默认为jie';
COMMENT ON COLUMN ZYJ_YUESHU.ZYJ_NO IS '编号字段 唯一约束';
COMMENT ON COLUMN ZYJ_YUESHU.ZYJ_SEX IS '性别字段 只能是男、女';
COMMENT ON COLUMN ZYJ_YUESHU.ZYJ_CHENGJI IS '成绩字段 大于10';

--二、在所有的字段创建之后单独添加约束
CREATE TABLE ZYJ_YUESHU(
       ZYJ_ID VARCHAR(20),   
       ZYJ_NAME VARCHAR(20),
       ZYJ_NO VARCHAR(20),    
       ZYJ_SEX VARCHAR(20),
       ZYJ_CHENGJI INT,
       --在字段创建之后，单独添加约束的格式【constraint 约束名 约束类型（约束的对象字段）】
       CONSTRAINT pk_zhujian PRIMARY KEY(ZYJ_ID), --创建主键约束
       CONSTRAINT not_null UNIQUE(ZYJ_NO),        --创建唯一约束
       CONSTRAINT ch_jiancha CHECK(ZYJ_SEX IN ('男','女'))  
);

--添加约束
       ALTER TABLE ZYJ_YUESHU ADD CONSTRAINT un_unique UNIQUE(ZYJ_CHENGJI);
--删除约束
       ALTER TABLE ZYJ_YUESHU DROP CONSTRAINT ch_jiancha;
--修改约束
       ALTER TABLE ZYJ_YUESHU DROP CONSTRAINT ch_jiancha;--先删除约束
       ALTER TABLE ZYJ_YUESHU ADD CONSTRAINT ch_jiancha CHECK(ZYJ_SEX IN ('1','0'));--重新创建

--视图
--创建一个查看学生平均成绩的视图
CREATE VIEW STU_AVG AS SELECT I.STU_NAME as names ,AVG(S.KECHENG_CHENGJI) as avgsc FROM STU_INFO I ,KECHENG_INFO K,STU_SCORE S WHERE I.STU_ID=S.STU_ID AND S.KECHENG_BIANHAO=K.KECHENG_BIANHAO GROUP BY I.STU_NAME;
--视图的使用
SELECT * FROM STU_AVG WHERE AVGSC>=60 ORDER BY AVGSC DESC;

--约束之创建外键约束的语法格式

--在下例中，将创建一个名为 Skills 的表，其中包含潜在技能列表，然后创建一个与 Skills 表具有外键关系、名为 EmployeeSkills 的表。请注意，EmployeeSkills.SkillID 与 Skills 表的主键列 (Id) 存在外键关系。

CREATE TABLE Skills (
   Id INTEGER PRIMARY KEY,
   SkillName CHAR(40),
   Description CHAR(100) 
);
CREATE TABLE EmployeeSkills (
   EmployeeID INTEGER NOT NULL,
   SkillID INTEGER NOT NULL,
   SkillLevel INTEGER NOT NULL,
   PRIMARY KEY( EmployeeID ),
   FOREIGN KEY (SkillID) REFERENCES Skills ( Id )
);