Oracle表的操作

转载
mb5fd340b104967 2014-07-15 22:21:00
--创建表 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;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。