oracle.约束、视图、序列、索引、

一、约束
1、not NULL:非空约束，不允许为null值
建表时创建：
CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值 NOT NULL,
  ...
）;

修改表时创建非空约束：
ALTER TABLE  表名 MODIFY(列名 NOT NULL);

由not NULL 修改为null
ALTER TABLE  表名 MODIFY(列名 NULL);

CREATE TABLE A(
  ID NUMBER(4) DEFAULT 1 NOT NULL,
  NAME VARCHAR2(20) NOT NULL
);

INSERT INTO A(NAME) VALUES('aaa');
INSERT INTO A(ID) VALUES(5);

2、主键：又称为唯一索引键 不允许主键列的值为Null且不允许重复
PRIMARY KEY.
建表时创建：
CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值 primary key,
  ...
）;

CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值,
  ...
  列名 数据类型 DEFAULT 默认值,
  constraint 约束名 primary key(约束内容)
）;

修改表时创建：
ALTER TABLE  表名 ADD CONSTRAINT 约束名 PRIMARY KEY(约束内容);

删除主键约束：
ALTER TABLE  表名 DROP CONSTRAINT 约束名;

DROP TABLE A;
CREATE TABLE A(
  ID NUMBER(4) PRIMARY KEY,
  NAME VARCHAR2(20)
);

INSERT INTO A(NAME) VALUES('aaa');
INSERT INTO A(ID,NAME) VALUES(1,'aaa');

DESC A;
ALTER TABLE A MODIFY(ID NULL);    ---不能将主键约束的not null修改为null

ALTER TABLE A DROP CONSTRAINT SYS_C009789;

3、唯一约束 ： 不允许重复值
建表时创建：
CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值 unique,
  ...
）;

CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值,
  ...
  列名 数据类型 DEFAULT 默认值,
  constraint 约束名 unique(约束内容)
）;

修改表时创建：
ALTER TABLE  表名 ADD CONSTRAINT 约束名 unique(约束内容);

删除主键约束：
ALTER TABLE  表名 DROP CONSTRAINT 约束名;

DROP TABLE A;
CREATE TABLE A(
  ID NUMBER(3) PRIMARY KEY,
  NAME VARCHAR2(20) UNIQUE
);

INSERT INTO A(ID) VALUES(3);
SELECT * FROM A;

4、检查约束 ：check
建表时创建：
CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值 check(条件),
  ...
）;

CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值,
  ...
  列名 数据类型 DEFAULT 默认值,
  constraint 约束名 check(条件)
）;

修改表时创建：
ALTER TABLE  表名 ADD CONSTRAINT 约束名 check(条件);

删除检查键约束：
ALTER TABLE  表名 DROP CONSTRAINT 约束名;

DROP TABLE A;

CREATE TABLE A(
  ID NUMBER(3) CHECK(ID BETWEEN 1 AND 100)
);

INSERT INTO A VALUES(0);

5、外键：子表的外键的值必须在父表的主键中存在
父表：主键
子表：外键
FOREIGN KEY

建表时创建：
CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值 references 父表(主键) on delete cascade,
  ...
）;

CREATE TABLE 表名（
  列名 数据类型 DEFAULT 默认值,
  ...
  列名 数据类型 DEFAULT 默认值,
  constraint 约束名 foreign key(外键)  references 父表(主键) on delete cascade
）;

修改表时创建：
ALTER TABLE  表名 ADD CONSTRAINT 约束名 foreign key(外键)  references 父表(主键) on delete cascade;

删除检查键约束：
ALTER TABLE  表名 DROP CONSTRAINT 约束名;

ON DELETE CASCADE:级联删除   当删除父表的主键记录时子表的相关记录会一并删除

DROP TABLE A;
CREATE TABLE A(
  ID NUMBER(3) PRIMARY KEY,
  NAME VARCHAR2(20) UNIQUE
);

CREATE TABLE b(
  bid NUMBER(3) REFERENCES A(ID) ON DELETE CASCADE,
  NAME VARCHAR2(20)
);

INSERT INTO A VALUES(1,'aaa');

INSERT INTO b VALUES(1,'aaa');

SELECT * FROM A;
SELECT * FROM b;
DELETE FROM A;

ALTER TABLE b ADD CONSTRAINT scott_b_name_fk FOREIGN KEY(NAME) REFERENCES A(NAME);

drop table b;

CREATE TABLE b(
  bid NUMBER(3) REFERENCES A(ID),
  NAME VARCHAR2(20)
);

二、视图：
CREATE [or replace] VIEW 视图名 AS 子查询[ WITH READ ONLY | WITH CHECK OPTION];
不占用空间
减少查询的难度

1、创建视图表：
---创建查询emp20部门员工信息的视图表
CREATE or replace VIEW v_emp20 AS SELECT * FROM emp WHERE deptno=40;

SELECT * FROM v_emp20;
INSERT INTO v_emp20(empno,ename,mgr,sal,deptno) VALUES(1111,'lisi',7566,3000,20);
INSERT INTO v_emp20(empno,ename,mgr,sal,deptno) VALUES(1112,'zhangsan',7566,3000,10);

update v_emp20 set deptno=40;

delete from v_emp20;

SELECT * FROM emp;

2、with READ ONLY:只读视图
CREATE OR REPLACE VIEW v_emp30 AS SELECT * FROM emp WHERE deptno=30 WITH READ ONLY;
SELECT * FROM v_emp30;

DELETE FROM v_emp30;
INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,10);

3、with CHECK OPTION:条件检查
CREATE OR REPLACE VIEW v_emp30 AS SELECT * FROM emp WHERE deptno=30 WITH CHECK OPTION;
SELECT * FROM v_emp30;
INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,10);  ----错误

INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,30);

DELETE FROM v_emp30;

4、复杂视图：
（01）视图的子查询中包含distinct,不允许对视图表增删改操作，只能查询
CREATE OR REPLACE VIEW v_emp AS SELECT DISTINCT deptno,JOB FROM emp;

SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(20,'clerk');
UPDATE v_emp SET deptno=20;

（02）视图的子查询中包含rownum，不允许对视图表增删改操作，只能查询
CREATE OR REPLACE VIEW v_emp AS SELECT rownum r,empno,ename,sal FROM emp;

SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(20,8888,'aaa',2000);
UPDATE v_emp SET sal=20;

（03）视图的子查询中包含分组函数，不允许对视图表增删改操作，只能查询
CREATE OR REPLACE VIEW v_emp AS SELECT count(empno) c,sum(sal) s FROM emp;

SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(18,20000);
update v_emp set c=20;

(04) 视图的子查询中包含了group BY 子句,不允许对视图表进行删除和修改的操作，允许新增和查询
CREATE OR REPLACE VIEW v_emp AS SELECT empno,ename FROM emp group by empno,ename;
SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(8888,'wangwu');
update v_emp set empno=20;

（05）视图的子查询中包含了表达式, 不允许对视图表的表达式列进行增加和修改的操作，允许对非表达式的列进行增删改查
CREATE OR REPLACE VIEW v_emp AS SELECT empno,sal*12 salary FROM emp;

SELECT * FROM v_emp;
DELETE FROM v_emp;
ROLLBACK;
INSERT INTO v_emp VALUES(8888,30000);
INSERT INTO v_emp(empno) VALUES(9999);

update v_emp set empno=20 where rownum=1;

（06）视图的子查询中不包含源表中的非空约束列,不允许对表进行新增，允许修改，删除 和查询
DESC emp;
CREATE OR REPLACE VIEW v_emp AS SELECT ename,sal FROM emp;

INSERT INTO v_emp VALUES('aaa',5000);

UPDATE v_emp SET sal=3000;
DELETE FROM v_emp;
rollback;

5、删除视图
DROP VIEW 视图名;

DROP VIEW v_emp;

三、序列
1、创建序列
CREATE SEQUENCE 序列名
  [INCREMENT BY n]   ----步长
  [START WITH n]     ---序列的起始值    序列创建后第一次使用的第一个值
  [MINVALUE n]       ---序列的最小值    序列从第二次循环取值时的最小值
  [MAXVALUE n]       ---序列的最大值
  [CYCLE]            ---序列设置循环取值的标志
  [CACHE n]          ---序列缓存的个数
 
---所有属性都为默认值的序列
CREATE SEQUENCE myseq;

---设置属性为指定值的序列
CREATE SEQUENCE seq1
   INCREMENT BY 5
   START WITH 10
   MINVALUE 5
   MAXVALUE 50
   CYCLE
   CACHE 5;
  
两个属性：
  currval:取序列当前值
  nextval:序列的下一个值
语法：序列名.属性名
 
注意：当一个序列创建成功之后，必须先用nextval生成第一个序列值才可以使用该序列

SELECT myseq.nextval FROM dual;

SELECT myseq.currval FROM dual;

SELECT seq1.nextval,seq1.currval FROM dual;

DROP TABLE A CASCADE CONSTRAINTS;
CREATE TABLE A(
  ID NUMBER(5) PRIMARY KEY
);

insert into a values(myseq.nextval);

select * from a;

2、修改序列
alter SEQUENCE 序列名
  [INCREMENT BY n]   ----步长
  [MINVALUE n]       ---序列的最小值    序列从第二次循环取值时的最小值
  [MAXVALUE n]       ---序列的最大值
  [CYCLE]            ---序列设置循环取值的标志
  [CACHE n]          ---序列缓存的个数
 
3、删除序列
DROP SEQUENCE 序列名;

四、索引
1、创建索引
手动创建:
CREATE INDEX 索引名 ON 表名(列名[,列名...]);

CREATE TABLE employee1(
  pno NUMBER(7),
  pname VARCHAR2(20)
);

CREATE INDEX inx_scott_pno ON employee1(pno);
INSERT INTO employee1 SELECT empno,ename FROM emp;
COMMIT;

select * from employee1;

INSERT INTO employee1 SELECT * FROM employee1;

UPDATE employee1 SET pno=ROWNUM;

SELECT *
FROM employee1
where pno=99999;

SELECT *
FROM employee1
where UPPER(ENAME)='SCOTT';

函数索引:
CREATE INDEX inx_scott_pname ON employee1(upper(pname));

CREATE INDEX inx_scott_pno_pname ON employee1(pno,pname);

自动创建索引:当创建主键或唯一键时,也会自动创建对应列的索引

2\删除索引:
DROP INDEX 索引名;

DROP INDEX inx_scott_pname;

select * from user_indexes;

1.使用子查询的方式，创建test表。
create table test99 as select empno,ename,sal,deptno from emp;
2.快速复制test表中的数据，复制到100w条左右
INSERT INTO TEST SELECT * FROM TEST;
DESC test99;
alter table test99 modify(empno number(12));