4.20.txt

java学习的点滴

--添加约束

--原因：防止无效数据插入
/*
NOT NULL    NN   非空 
UNIQUE       UN   唯一
PRIMARY KEY PK   主键  唯一区别其他数据的字段   唯一并且非空约束   一个表中只允许设一个主键
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   先在主表创建，再更新辅表，再删主表
--CREATE TABLE 主从顺序（先建主表，在建从表）
--DROP TABLE （先删从表，在删主表）

--查询约束

select * from user_constraints


--删除约束
--ALTER TABLE 表名 DROP CONSTRAINT 约束名
ALTER TABLE DEPARTMENT  DROP CONSTRAINT DEPARTMENT_ID_PK  CASCADE; --及联删除外键约束


--追加约束
--追加约束
	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;

--索引

--通过索引快速定位数据

--PRIMARY KEY，UNIQUE约束自动创建索引
--创建索引
CREATE INDEX EMOLOYEE_SALARY_INDEX
ON EMPLOYEE (SALARY)

--删除索引
DROP  INDEX EMOLOYEE_SALARY_INDEX


--创建视图
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 

 -- 创建表
--使用查询结果创建表
DROP TABLE NAMESAL

CREATE TABLE NAMESAL AS 
SELECT EMPNAME,SALARY FROM EMPLOYEE

SELECT * FROM NAMESAL

--序列

--创建序列对象

CREATE SEQUENCE TESTSEQ_SEQ     --建议用 表名_SEQ 命名
MINVALUE 1                      --最小值
MAXVALUE 9999999999999          --最大值
START WITH 1234                 --开始值
INCREMENT BY 1                  --每次增长步长
CACHE 20                        --内存储存的数量 默认20

--测试

SELECT TESTSEQ_SEQ.CURRVAL FROM DUAL

SELECT TESTSEQ_SEQ.NEXTVAL FROM DUAL


--调用序列对象的下一个值
DROP TABLE TESTSEQ

CREATE TABLE TESTSEQ (SEQ NUMBER(20))

INSERT INTO TESTSEQ VALUES(TESTSEQ_SEQ.NEXTVAL)

SELECT * FROM TESTSEQ


--删除序列

DROP SEQUENCE TESTSEQ_SEQ



--更改表结构

DROP TABLE STUDENT

CREATE TABLE STUDENT(
     SNAME  VARCHAR2(10)
)

INSERT INTO STUDENT VALUES ('GOOD')

SELECT * FROM STUDENT 


--增加字段

ALTER TABLE STUDENT ADD WEIGHT NUMBER(3);

--修改字段 不能保证成功 新的数据类型或长度不能容纳原有数据

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;







设置外键，唯一，检查的方法
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)
	)