--添加約束

--原因：防止無效數(shù)據(jù)插入
/*
NOT NULL    NN   非空 
UNIQUE       UN   唯一
PRIMARY KEY PK   主鍵  唯一區(qū)別其他數(shù)據(jù)的字段   唯一并且非空約束   一個表中只允許設(shè)一個主鍵
FOREIGN KEY FK   外鍵  另一個表的主鍵
CHECK       CK   檢查
DEFAULT     DF   默認值
*/
	DROP TABLE DEPARTMENT

	CREATE TABLE DEPARTMENT (
	  DEPTID    NUMBER(3),    
	  DEPTNAME  VARCHAR2(20)  DEFAULT 'HR',            --默認值
	  LEADER    VARCHAR2(10)  NOT NULL ,               --非空
	  CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY (DEPTID) --主鍵
	)

	INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
	VALUES(1,'JAVA','TOMCAT');

	INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
	VALUES(2,'MARKETING','HARRY');

	INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
	VALUES(4,'TEST','ROSE');

	INSERT INTO DEPARTMENT (DEPTID,LEADER)
	VALUES(5,'ANN');



	DROP TABLE EMPLOYEE 

	CREATE TABLE EMPLOYEE(
       	  EMPID     NUMBER(6),
       	  EMPNAME   VARCHAR2(10) UNIQUE ,
          DEPTID    NUMBER(3),
          AGE       NUMBER(3),
          SALARY    NUMBER(10,3),
          BIRTHDAY  DATE,
          CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (EMPID),
          CONSTRAINT EMPLOYEE_DEPTID_FK FOREIGN KEY (DEPTID)
                     REFERENCES DEPARTMENT(DEPTID),
	  CONSTRAINT EMPLOYEE_EMPNAME_UN UNIQUE(SALARY),
          CONSTRAINT EMPLOYEE_AGE_CK CHECK(AGE >18 AND AGE < 99)
	)

--添加FK對操作的影響
--INSERT   先往主表里插，在往輔表里插
--DELETE   先刪輔表，在刪主表
--UPDATE   先在主表創(chuàng)建，再更新輔表，再刪主表
--CREATE TABLE 主從順序（先建主表，在建從表）
--DROP TABLE （先刪從表，在刪主表）

--查詢約束

select * from user_constraints


--刪除約束
--ALTER TABLE 表名 DROP CONSTRAINT 約束名
ALTER TABLE DEPARTMENT  DROP CONSTRAINT DEPARTMENT_ID_PK  CASCADE; --及聯(lián)刪除外鍵約束


--追加約束
--追加約束
	DROP TABLE DEPARTMENT

	CREATE TABLE DEPARTMENT (
	  DEPTID    NUMBER(3),    
	  DEPTNAME  VARCHAR2(20)  DEFAULT 'HR',            --默認值
	  LEADER    VARCHAR2(10)  NOT NULL ,               --非空
	  CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY (DEPTID) --主鍵
	)
  	DROP TABLE DEPARTMENT

	CREATE TABLE DEPARTMENT (
	  DEPTID    NUMBER(3),    
	  DEPTNAME  VARCHAR2(20)  DEFAULT 'HR',            --默認值
	  LEADER    VARCHAR2(10)  NOT NULL ,               --非空
	  CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY (DEPTID) --主鍵
	)

	DROP TABLE EMPLOYEE 

	CREATE TABLE EMPLOYEE(
       	  EMPID     NUMBER(6),
       	  EMPNAME   VARCHAR2(10),
          DEPTID    NUMBER(3),
          AGE       NUMBER(3),
          SALARY    NUMBER(10,3),
          BIRTHDAY  DATE
	)


ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPID);

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_CK CHECK (AGE>10);

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT(DEPTID);

ALTER TABLE EMPLOYEE MODIFY EMPNAME NOT NULL;

--索引

--通過索引快速定位數(shù)據(jù)

--PRIMARY KEY，UNIQUE約束自動創(chuàng)建索引
--創(chuàng)建索引
CREATE INDEX EMOLOYEE_SALARY_INDEX
ON EMPLOYEE (SALARY)

--刪除索引
DROP  INDEX EMOLOYEE_SALARY_INDEX


--創(chuàng)建視圖
CREATE VIEW EMP_VIEW AS 
SELECT EMPNAME , SALARY FROM EMPLOYEE

SELECT * FROM  EMP_VIEW 

--修改視圖
CREATE OR REPLACE VIEW EMP_VIEW AS 
SELECT EMPNAME , SALARY , BIRTHDAY FROM EMPLOYEE

SELECT * FROM  EMP_VIEW 

DROP  VIEW EMP_VIEW 

 -- 創(chuàng)建表
--使用查詢結(jié)果創(chuàng)建表
DROP TABLE NAMESAL

CREATE TABLE NAMESAL AS 
SELECT EMPNAME,SALARY FROM EMPLOYEE

SELECT * FROM NAMESAL

--序列

--創(chuàng)建序列對象

CREATE SEQUENCE TESTSEQ_SEQ     --建議用 表名_SEQ 命名
MINVALUE 1                      --最小值
MAXVALUE 9999999999999          --最大值
START WITH 1234                 --開始值
INCREMENT BY 1                  --每次增長步長
CACHE 20                        --內(nèi)存儲存的數(shù)量 默認20

--測試

SELECT TESTSEQ_SEQ.CURRVAL FROM DUAL

SELECT TESTSEQ_SEQ.NEXTVAL FROM DUAL


--調(diào)用序列對象的下一個值
DROP TABLE TESTSEQ

CREATE TABLE TESTSEQ (SEQ NUMBER(20))

INSERT INTO TESTSEQ VALUES(TESTSEQ_SEQ.NEXTVAL)

SELECT * FROM TESTSEQ


--刪除序列

DROP SEQUENCE TESTSEQ_SEQ



--更改表結(jié)構(gòu)

DROP TABLE STUDENT

CREATE TABLE STUDENT(
     SNAME  VARCHAR2(10)
)

INSERT INTO STUDENT VALUES ('GOOD')

SELECT * FROM STUDENT 


--增加字段

ALTER TABLE STUDENT ADD WEIGHT NUMBER(3);

--修改字段 不能保證成功 新的數(shù)據(jù)類型或長度不能容納原有數(shù)據(jù)

ALTER TABLE STUDENT MODIFY WEIGHT NUMBER(4);
 
INSERT INTO STUDENT VALUES ('H',1234);
 
ALTER TABLE STUDENT MODIFY SNAME VARCHAR2(5);



--修改字段名

ALTER TABLE STUDENT RENAME COLUMN SNAME TO NAME;

--刪除字段

ALTER TABLE STUDENT DROP COLUMN WEIGHT;







設(shè)置外鍵，唯一，檢查的方法
CREATE TABLE EMPLOYEE(
       	  EMPID     NUMBER(6),
       	  EMPNAME   VARCHAR2(10) UNIQUE ,
          DEPTID    NUMBER(3),
          AGE       NUMBER(3),
          SALARY    NUMBER(10,3),
          BIRTHDAY  DATE,
          
          CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (EMPID),
          
          
          CONSTRAINT EMPLOYEE_DEPTID_FK FOREIGN KEY (DEPTID)
                     REFERENCES DEPARTMENT(DEPTID),
                     
                     
	        CONSTRAINT EMPLOYEE_EMPNAME_UN UNIQUE(SALARY),
    
    
          CONSTRAINT EMPLOYEE_AGE_CK CHECK(AGE >18 AND AGE < 99)
	)