数据库完整性约束

数据库完整性约束第一部分：创建如下两张表student和course：学生表中：有如下字段与约束要求： sno/*列级完整性约束，设置sno属性为主键*/ sname /*列级完整性约束，要求sname属性的值唯一*/ ssex /*DEFAULT默认值约束，CHECK约束限制ssex属性的取值范围*/ sage /*检查约束的实现，范围可以自行定义*/ sdeptCREATE TABLE STUDENT(SNO INT CONSTRAINT STUDENT_SNO_PK PRIMARY KEY,SNAME VARCHAR(10) UNIQUE,SSEX VARCHAR(2) DEFAULT 男 CONSTRAINT STUDENT_SSEX_CHK CHECK(SSEX IN(男, 女),SAGE INT CONSTRAINT STUDENT_SAGE_CHK CHECK(SAGE = 0 AND SAGE = 0 AND GRADE = 90000 AND SNO = 0 AND SAGE =30),SSEX NVARCHAR2(4) CONSTRAINT SSEX_CHECK CHECK(SSEX IN (男, 女),CONSTRAINT SNO_PK PRIMARY KEY(SNO);二、 删除约束三、删除其中某条约束ALTER TABLE STUDENT_E10 DROP CONSTRAINT XXX;四、 管理其他约束SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = STUDENT_E10;准备工作：创建没有任何约束的表dept1. 部门编号 数值型2. 部门名称 字符型CREATE TABLE DEPT (DNO INT,DNAME VARCHAR(20);1、 增加删除空值非空值约束，并观察结果ALTER TABLE DEPT MODIFY 列名 NULL/NOT NULL;SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = DEPT;2、 增加删除主键约束，并观察约束在哪里ALTER TABLE DEPT ADD/DROP CONSTRAINT 约束名 PRIMARY KEY(列名);SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = DEPT;3、 增加删除唯一约束，并观察约束在哪里ALTER TABLE DEPT ADD/DROP CONSTRAINT 约束名 UNIQUE(列名);SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = DEPT;4、 增加删除默认值约束，并观察约束在哪里ALTER TABLE DEPT MODIFY/DROP 列名 DEFAULT(默认值);SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = DEPT;5、 增加删除检查约束，并观察约束在哪里要求部门名称末尾一定要有部。如业务部ALTER TABLE DEPT ADD/DROP CONSTRAINT 约束名 CHECK(DNAME LIKE %部);SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = DEPT;6、 增加删除外键约束，并观察约束在哪里ALTER TABLE DEPT ADD/DROP CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 表名(列名);第二部分：创建表book，用于记录书本信息，要求字段如下-编号：用于记录书本的编号书名：记录书名，不超过200个字符出版社编号：编号，从出版社表引用作者编号：编号，从作者表引用CREATE TABLE BOOK (BID INT,BNAME VARCHAR(200) CONSTRAINT BNAME_CHECK CHECK(LENGTH(BNAME) = 200),PID INT,AID INT,CONSTRAINT PID_FK FOREIGN KEY(PID) REFERENCES PUBLISHER(PID),CONSTRAINT AID_FK FOREIGN KEY(AID) REFERENCES AUTHOR(AID)2、创建表publisher，用于记录出版社信息。-编号：出版社的编号。如：1、2、3、4出版社名字：记录出版社的名字，不操作200个字符出版社地址：记录出版社地址，不超过200个字符。CREATE TABLE PUBLISHER (PID INT CONSTRAINT PID_PK PRIMARY KEY,PNAME VARCHAR(200) CONSTRAINT PNAME_CHECK CHECK(LENGTH(PNAME) = 200),PADDR VARCHAR(200) CONSTRAINT PADDR_CHECK CHECK(LENGTH(PADDR) = 200)3、创建表author，用于记录书籍的作者信息-编号：用于记录作者的编号姓名：用于记录作者的名字联系地址：用于记录该作者的联系地址CREATE TABLE AUTHOR (AID INT CONSTRAINT AID_PK PRIMARY KEY,ANAME VARCHAR(10),AADDR VARCHAR(20);4、要求为书表（book）添加主键id。ALTER TABLE BOOK ADD CONSTRAINT BID_PK PRIMARY KEY(BID);5、要求为书表添加外键约束a、出版社编号必需引用出版社表（publisher）中的编号b、作者编号必需引用作者表（author）中的编号ALTER TABLE BOOK ADD CONSTRAINT PID_FK FOREIGN KEY(PID) REFERENCES PUBLISHER(PID);ALTER TABLE BOOK ADD CONSTRAINT AID_FK FOREIGN KEY(AID) REFERENCES AUTHOR(AID);6、要求为表book添加字段:出版日期。用于记录日期格式。ALTER TABLE BOOK ADD PDATE DATE;7、创建书城用户表tuser，用于记录注册的了用户信息-要求如下：编号：用于记录用户的编号用户名：用户登录系统的用户名,不超过16位姓名：要求用于记录用户的真实姓名密码：记录密码，不超过32位注册日期：用于记录用户的注册时间。CREATE TABLE TUSER (TUID INT,UNAME VARCHAR(16) CONSTRAINT UNAME_CHECK CHECK(LENGTH(UNAME) = 16),NAME VARCHAR(10),PASSWD VARCHAR(32) CONSTRAINT PASSWD_CHECK CHECK(LENGTH(PASSWD) = 32),REGISTER_DATE DATE)8、为用户表创建关键字编号ALTER TABLE TUSER ADD CONSTRAINT TUID_PK PRIMARY KEY(TUID);9、为用户创建约束ALTER TABLE TUSER ADD CONSTRAINT UNAME_CHECK CHECK(LENGTH(UNAME) 0);第三部分：7.其他：（查询）编写一个查询，显示姓名中包含有字符T的雇员所在的部门内的所有雇员的编号和姓名，并将该查询保存为p6q1.sqlSELECT EMPNO, ENAME FROM SCOTT.EMPWHERE DEPTNO IN (SELECT DEPTNO FROM SCOTT.EMPWHERE ENAME LIKE %T%);2、修改p6q1.sql，显示工资高于平均工资水平并且与姓名中包含有T的雇员在同一部门工作的雇员的编号、姓名、及工资，将修改后的结果另存为p6q2.sql，并返回查询结果SELECT EMPNO, ENAME, SAL FROM SCOTT.EMPWHERESAL (SELECT AVG(SAL) FROM SCOTT.EMP)ANDDEPTNO IN (SELECT DEPTNO FROM SCOTT.EMP WHERE ENAME LIKE %T%);3、创建一查询，显示能获得比所有从事CLERK工作的雇员的个人工资更高的其他雇员的姓名、工作及工资，输出按工资降序排列，并将该查询保存为文件p6q3.sqlSELECT ENAME, JOB, SAL FROM SCOTT.EMPWHERE SAL (SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB = CLERK)ORDER BY SAL DESC;或者SELECT ENAME, JOB, SAL FROM SCOTT.EMPWHERE SAL ALL(SELECT SAL FROM SCOTT.EMP WHERE JOB = CLERK)ORDER BY SAL DESC;4、编写一查询，显示与Blake在同一部门工作的雇员的姓名和受雇日期，但是Blake不包含在结果内SELECT ENAME, HIREDATE FROM SCOTT.EMPWHERE DEPTNO = (SELECT DEPTNO FROM SCOTT.EMP WHERE ENAME = BLAKE)AND ENAME != BLAKE;5、创建一查询，显示工资大于平均工资水平的雇员的编号及姓名，输出结果按工资降序排列。SELECT EMPNO, ENAME FROM SCOTT.EMPWHERE SAL (SELECT AVG(SAL) FROM SCOTT.EMP)ORDER BY SAL DESC;6、显示位置在Dallas的部门内的雇员的姓名、编号及工作SELECT ENAME, EMPNO, JOB FROM SCOTT.EMPWHERE DEPTNO = (SELECT DEPTNO FROM SCOTT.DEPT WHERE LOC = DALLAS);或者SELECT E.ENAME, E.EMPNO, E.JOBFROM SCOTT.EMP E, SCOTT.DEPT DWHERE E.DEPTNO = D.DEPTNO AND D.LOC = DALLAS;7、显示被King直接管理的雇员的姓名及其工资SELECT ENAME, SAL FROM SCOTT.EMPWHERE MGR = (SELECT EMPNO FROM SCOTT.EMP WHERE ENAME = KING);8、显示在Sales部门工作的雇员的部门编号、姓名和工作SELECT DEPTNO, ENAME, JOB FROM SCOTT.EMPWHERE DEPTNO = (SELECT DEPTNO FROM SCOTT.DEPT WHERE DNAME = SALES);9、显示与工作在Dallas的雇员的工资及奖金同时匹配的雇员的姓名、部门名称及工资SELECT ENAME, DNAME, SAL FROM SCOTT.EMP JOIN SCOTT.DEPT USING(DEPTNO)WHERE (SAL, NVL(COMM, 0) IN (SELECT SAL, NVL(COMM, 0) FROM SCOTT.EMPWHER