Oracle表的操作

eff33

--创建表
CREATE [GLOBAL TEMPORARY] TABLE table_name(
coloum_name TYPE [CONSTRAINT constraint_def DEFAULT default_exp]
[,coloum_name TYPE [CONSTRAINT constraint_def DEFAULT default_exp] ...]
)
[ON COMMIT {DELETE | PRESERVE} ROWS]
TABLESPACE tab_space;
/*
GLOBAL TEMPORARY 说明该表的行是临时表，这种表就称为临时表。行的有效性由on commit子句指定
ON COMMIT 控制临时表中行的有效期。DELETE 说明这些行在事务的末尾要被删除。PRESERVE 说明
这些行要在会话的末尾要被删除。
TABLESPACE 为该表指定表空间。若没有指定，该表就存储在用户的默认表空间。
*/
CREATE TABLE Order_Status2(
ID INTEGER CONSTRAINT Order_Status2_pk PRIMARY KEY,
status VARCHAR2(10),
last_modified DATE DEFAULT SYSDATE
);

CREATE GLOBAL TEMPORARY TABLE Order_Status_temp(
ID INTEGER,
status VARCHAR2(10),
last_modified DATE DEFAULT SYSDATE
)
ON COMMIT PRESERVE ROWS;

INSERT INTO Order_Status_temp
VALUES(1,'New',SYSDATE);

SELECT * FROM Order_Status_temp;

--获得有关表的信息
--对表执行 describe 命令。
--查询user_tables
SELECT * FROM user_tables;
SELECT * FROM all_tables;

SELECT table_name, tablespace_name, temporary
  FROM user_tables
WHERE table_name IN ('ORDER_STATUS2', 'ORDER_STATUS_TEMP');

--获取表的列的信息
SELECT * FROM user_tab_columns;
SELECT * FROM all_tab_columns;

SELECT column_name, data_type, data_length, data_precision, data_scale
  FROM user_tab_columns
WHERE table_name = 'PRODUCTS';

--修改表
--alter table
/*
添加、修改或删除列
添加或删除约束
启用或禁用约束
*/
--添加列
CREATE TABLE Order_Status2 AS SELECT * FROM Order_Status;

ALTER TABLE Order_Status2 ADD modified_by INTEGER;

ALTER TABLE Order_Status2 ADD initially_created DATE DEFAULT SYSDATE NOT NULL;

--修改列
/*
修改列的长度
修改数字列的精度
修改列的数据类型
修改列的默认值
*/
--修改列的长度
--只有在表中没有行或这列为空值时才可以减小列的长度
ALTER TABLE Order_Status2 MODIFY status VARCHAR2(15);

--修改数字列的精度
--只有在表中没有行或这列为空值时才可以减小列的精度
ALTER TABLE Order_Status2 ADD ID NUMBER(4);
ALTER TABLE Order_Status2 MODIFY id NUMBER(5);

--修改列的数据类型
--如果表中没有或这列为空值时，可以修改为任何类型，否则只能修改为兼容的数据类型
ALTER TABLE Order_Status2 MODIFY status char(15);

--修改列的默认值
ALTER TABLE Order_Status2 MODIFY last_modified DEFAULT SYSDATE - 1;

--删除列
ALTER TABLE Order_Status2 DROP COLUMN initially_created;

--添加约束

----------------------------------------------------------------------------------------------
约束                约束类型                 意义
----------------------------------------------------------------------------------------------
CHECK                 C                指定一列或一组列的值必须满足特定的约束
----------------------------------------------------------------------------------------------
NOT NULL              C                指定一列不允许存储空值。这实际上是一种强制的check约束
----------------------------------------------------------------------------------------------
PRIMARY KEY           P                指定表的主键。主键由一列或多列组成。它唯一标识了表的一行
----------------------------------------------------------------------------------------------
FOREIGN KEY           F                指定表的外键。外键引用另外一个表中的一列，在自引用情况下
                                       则引用本表中的一列
----------------------------------------------------------------------------------------------
UNIQUE                U                指定一列或一组列只能存储唯一的值
----------------------------------------------------------------------------------------------
CHECK OPTION          V                指定对视图执行的DML操作必须满足子查询的条件。
----------------------------------------------------------------------------------------------
READ ONLY             O                指定视图是只读的
----------------------------------------------------------------------------------------------

--(1)添加check约束
ALTER TABLE Order_Status2 ADD CONSTRAINT order_status2_status_ck
CHECK (status IN ('PLACED','PENDING','SHIPPED'));

INSERT INTO order_status2
  (status, last_modified, modified_by)
VALUES
  ('', SYSDATE, 2);

ALTER TABLE Order_Status2 ADD CONSTRAINT order_status2_id_ck
CHECK (ID > 0);

--(2)添加not null约束
ALTER TABLE Order_Status2 MODIFY status CONSTRAINT order_status2_status_nn NOT NULL;

ALTER TABLE Order_Status2 MODIFY modified_by CONSTRAINT order_status2_modified_by_nn NOT NULL;

ALTER TABLE Order_Status2 MODIFY last_modified NOT NULL;

SELECT * FROM Order_Status2;

INSERT INTO Order_Status2
VALUES(1,'PLACED',SYSDATE,NULL);

--(3)添加primary key约束
CREATE TABLE table_name(
col_name TYPE PRIMARY KEY,
...
);

CREATE TABLE table_name(
col_name TYPE CONSTRAINT cons_name PRIMARY KEY,
...
);

ALTER TABLE table_name ADD CONSTRAINT col_name_pk PRIMARY KEY(col_name);

CREATE TABLE t_pk(pid INT);
ALTER TABLE t_pk ADD CONSTRAINT pid_pk PRIMARY KEY(pid);

--(4)添加foregin key约束
ALTER TABLE Order_Status2 DROP COLUMN modified_by;

SELECT * FROM Order_Status2;
SELECT * FROM employees;

ALTER TABLE employees ADD CONSTRAINT employees_id_fk PRIMARY KEY(employee_id);

--没有存在列，加入外键
ALTER TABLE Order_Status2 ADD CONSTRAINT  Order_Status2_modified_by_fk modified_by
REFERENCES employees(employee_id);

--存在列，加入外键
ALTER TABLE Order_Status2 ADD CONSTRAINT  Order_Status2_modified_by_fk FOREIGN KEY(modified_by)
REFERENCES employees(employee_id);

SELECT * FROM Order_Status2;

--外键对插入的影响
INSERT INTO Order_Status2
VALUES(3,'PLACED',SYSDATE,4);
INSERT INTO Order_Status2
VALUES(2,'PLACED',SYSDATE,4);
INSERT INTO Order_Status2
VALUES(4,'PLACED',SYSDATE,3);

--外键对修改的影响
SELECT * FROM Order_Status2;
SELECT * FROM employees;

UPDATE Order_Status2
SET modified_by = 4
WHERE order_status_id = 2;

--外键对删除的影响
DELETE employees
WHERE employee_id = 4;


--使用一个带有foreign key约束的on delete cascade子句，可以指定在父表中删除一行记录时，子表中匹配的所有行
--也都将被删除。
ALTER TABLE Order_Status2 DROP COLUMN modified_by;

ALTER TABLE Order_Status2 ADD CONSTRAINT  Order_Status2_modified_by_fk modified_by
REFERENCES employees(employee_id) ON DELETE CASCADE;

--当从employees表中删除一行记录时，Order_Status2表所匹配的行也将都被删除。
DELETE employees
WHERE employee_id = 4;

--使用一个带有foreign key约束的on delete set null子句，可以指定在父表中删除一行记录时，子表中匹配行的外键
--将被设置为空值
ALTER TABLE Order_Status2 DROP COLUMN modified_by;

ALTER TABLE Order_Status2 ADD CONSTRAINT  Order_Status2_modified_by_fk modified_by
REFERENCES employees(employee_id) ON DELETE SET NULL;

--当从employees表中删除一行记录时，Order_Status2表所匹配的行所在的外键列都被置为null。
DELETE employees
WHERE employee_id = 4;

--(5)添加unique约束
ALTER TABLE Order_Status2 ADD CONSTRAINT  Order_Status2_status_uq UNIQUE(status);

--删除约束
ALTER TABLE Order_Status2 DROP CONSTRAINT Order_Status2_status_uq;

--禁用约束
ALTER TABLE Order_Status2 ADD CONSTRAINT  Order_Status2_status_uq UNIQUE(status) DISABLE;

ALTER TABLE Order_Status2 DISABLE  CONSTRAINT order_status2_modified_by_nn;

--启用约束
--表中的所有行必须满足约束条件
ALTER TABLE Order_Status2 ENABLE  CONSTRAINT Order_Status2_status_uq;
--对新数据启用约束
ALTER TABLE Order_Status2 ENABLE NOVALIDATE CONSTRAINT Order_Status2_status_uq;

--延迟约束
--deferred constraint是在事务被提交时强制执行的约束。
--在添加约束时，可以使用deferrable子句指定约束是延迟约束。
--在添加延迟约束时，可以将其标识为initally immediate 或initally deferred。
--initally immediate每次向表中添加数据、修改数据或删除数据时都要检查这个约束
--initally deferred只有在事务被提交时才会检查这个约束。
ALTER TABLE  Order_Status2
DROP CONSTRAINT Order_Status2_status_uq;

ALTER TABLE  Order_Status2 ADD CONSTRAINT Order_Status2_status_uq UNIQUE(status)
DEFERRABLE INITIALLY DEFERRED;

INSERT INTO Order_Status2
VALUES(3,'Order placed',SYSDATE);

--若向Order_Status2表中添加行，那么只有在执行commit时，才会对Order_Status2_status_uq约束进行检查

--获取约束的信息
SELECT * FROM User_Constraints;
SELECT * FROM all_Constraints;

SELECT constraint_name, constraint_type, status, deferrable, deferred
  FROM user_constraints
WHERE table_name = 'ORDER_STATUS2';

--获取有关列约束的信息
SELECT * FROM User_Cons_Columns;
SELECT * FROM all_Cons_Columns;

SELECT constraint_name, column_name
  FROM user_cons_columns
WHERE table_name = 'ORDER_STATUS2';

SELECT ucc.column_name, ucc.constraint_name, uc.constraint_type, uc.status
  FROM user_constraints uc, user_cons_columns ucc
WHERE uc.table_name = ucc.table_name
   AND uc.constraint_name = ucc.constraint_name
   AND ucc.table_name = 'ORDER_STATUS2';

--重命名表
RENAME Order_Status2 TO order_state;

RENAME order_state TO Order_Status2;

--向表中添加注释
--注释有助于记住表或列的用途
COMMENT ON TABLE Order_Status2 IS 'Order_Status2 stores the state of an order';

COMMENT ON COLUMN Order_Status2.last_modified
IS 'last_modified stores the date of the order was modified last';

--获取表的注释
SELECT * FROM user_tab_comments WHERE table_name = 'ORDER_STATUS2';

--获取列的注释
SELECT * FROM user_col_comments WHERE table_name = 'ORDER_STATUS2';

--截断表
TRUNCATE TABLE Order_Status2;

--删除表
DROP TABLE Order_Status2;

--创建序列
CREATE SEQUENCE seq_name
[START WITH start_num]
[INCREMENT BY increment_num]
[{MAXVALUE Maxvalue_num|nomaxvalue}]
[{MINVALUE Minvalue_num|Minvalue}]
[{CYCLE|nocycle}]
[{CACHE cache_name|NOCACHE}]
[{ORDER|NOORDER}];

START WITH start_num指定序列从哪个整数开始，默认值为1
INCREMENT BY increment_num指定序列每次增加的增量，默认值为1
MAXVALUE Maxvalue_num指定该序列的最大整数
MINVALUE Minvalue_num指定该序列的最小整数

CREATE SEQUENCE test_seq;

CREATE SEQUENCE test2_seq
START WITH 10 INCREMENT BY 5
MINVALUE 10 MAXVALUE 20
CYCLE CACHE 2 ORDER;

CREATE SEQUENCE test3_seq
START WITH 10 INCREMENT BY -1
MINVALUE 1 MAXVALUE 10
CYCLE CACHE 5;

--获取序列的信息
SELECT * FROM User_Sequences;
SELECT * FROM All_Sequences;

--使用序列
--序列生成一系列数字，一个序列中包含两个伪列currval nextval，可以分别用来获取该序列的当前值和下一个值。
--在检索序列的当前值之前，必须通过检索序列的下一个值对序列进行初始化。
--初始化后，则可以使用currval来获取该序列的当前值了。
SELECT test_seq.nextval FROM dual;

SELECT test_seq.currval FROM dual;

--使用序列来填充主键
CREATE TABLE Order_Status2(
ID INTEGER CONSTRAINT order_status2_pk PRIMARY KEY,
status VARCHAR2(10),
last_modified DATE DEFAULT SYSDATE
);

CREATE SEQUENCE Order_Status2_seq NOCACHE;

INSERT INTO Order_Status2(ID,status,last_modified)
VALUES (Order_Status2_seq.nextval,'PLACED',SYSDATE);

INSERT INTO Order_Status2(ID,status,last_modified)
VALUES (Order_Status2_seq.nextval,'PENDING',SYSDATE);

--删除序列
DROP SEQUENCE test3_seq;

--索引
--创建索引
CREATE [UNIQUE]INDEX index_name ON table_name(column_name[,COLUMN_name ...])
TABLESPACE tab_space;

/*
UNIQUE指定索引列中的值必须是唯一的
*/
SELECT customer_id, first_name, last_name
  FROM customers
WHERE last_name = 'Brown';

CREATE INDEX customers_last_name_idx ON customers(Last_Name);

CREATE UNIQUE INDEX customers_phone_idx ON customers(phone);
INSERT INTO customers VALUES (6, 'aa', 'bb', SYSDATE, '800-555-1214');

CREATE INDEX customers_first_last_name_idx ON employees(first_name,Last_Name);

--获取索引的信息
SELECT * FROM user_indexes;
SELECT * FROM all_indexes;

SELECT index_name, table_name, uniqueness, status
  FROM user_indexes
WHERE table_name IN ('CUSTOMERS', 'EMPLOYEES');

--获取列索引的信息
SELECT * FROM User_Ind_Columns;
SELECT * FROM All_Ind_Columns;

SELECT index_name, table_name, column_name
  FROM user_ind_columns
WHERE table_name IN ('CUSTOMERS', 'EMPLOYEES');

--删除索引
DROP INDEX customers_first_last_name_idx;

--视图
--创建视图
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
[(alias_name[,alias_name ...])] AS subquery
[WITH {CHECK OPTION | READ ONLY}] CONSTRAINT constraint_name];

/*
or replace 若视图存在则替换视图
force即使基表不存在也要创建视图
noforce若基表不存在，就不创建视图，为默认选项
alias_name为子查询中的表达式指定一个别名
subquery指定一个子查询，它对基表进行检索
with check option说明子查询检索的行才能被插入、修改或删除。
constraint_name指定with check option或read only约束的名称。
read only说明只能对基表中的行进行只读访问
*/

--简单视图，包含一个子查询，只从一个基表中检索数据
--复杂视图，包含一个子查询，从多个基表中检索数据，包含分组，函数调用等
--创建并使用简单视图
CREATE OR REPLACE VIEW cheap_products_view AS
SELECT * FROM products
WHERE price <  15;

CREATE VIEW employees_view AS
SELECT employee_id,manager_id,first_name,last_name,title
FROM employees;

--对视图进行select操作
SELECT product_id,NAME,price
FROM cheap_products_view;

SELECT * FROM employees_view;

--对视图进行insert操作
--只能对简单视图执行DML操作，复杂视图不支持DML操作
INSERT INTO cheap_products_view
  (product_id, product_type_id, NAME, price)
VALUES
  (13, 1, 'Western Front', 13.50);
   
SELECT * FROM cheap_products_view;

--没有with check option，可以插入、修改或删除子查询不能检索的行
INSERT INTO cheap_products_view
  (product_id, product_type_id, NAME, price)
VALUES
  (14, 1, 'Eastern Front', 16.50);
   
SELECT * FROM cheap_products_view
WHERE  product_id = 14;

--没有选择的基表列被置为空值,salary为null
INSERT INTO employees_view
  (employee_id, manager_id, first_name, last_name, title)
VALUES
  (5, 1, 'Jeff', 'Jones', 'CTO');

SELECT * FROM employees
WHERE employee_id = 5;

--创建具有check option约束的视图
--指定对视图的DML操作必须满足子查询的条件。
CREATE OR REPLACE VIEW cheap_products_view2 AS
SELECT * FROM products
WHERE price <  15
WITH OPTION CHECK CONSTRAINT cheap_products_view2_price;

INSERT INTO cheap_products_view2
  (product_id, product_type_id, NAME, price)
VALUES
  (15, 1, 'Southern Front', 16.50);

--创建具有read only约束的视图
--指定视图是只读的
CREATE OR REPLACE VIEW cheap_products_view3 AS
SELECT * FROM products
WHERE price <  15
WITH READ ONLY CONSTRAINT cheap_products_view3_read_only;

INSERT INTO cheap_products_view3
  (product_id, product_type_id, NAME, price)
VALUES
  (16, 1, 'Northern Front', 19.50);

--获取有关视图定义的信息
SELECT * FROM User_Views;
SELECT * FROM all_views;

--获取有关视图约束的信息
SELECT constraint_name, constraint_type, status, deferrable, deferred
  FROM user_constraints
WHERE table_name IN ('CHEAP_PRODUCTS_VIEW2', 'CHEAP_PRODUCTS_VIEW3');

--删除视图
DROP VIEW cheap_products_view3;