--创建表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_tempVALUES(1,‘New‘,SYSDATE);SELECT * FROM Order_Status_temp;--获得有关表的信息--对表执行 describe 命令。--查询user_tablesSELECT * 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_Status2VALUES(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_Status2VALUES(3,‘PLACED‘,SYSDATE,4);INSERT INTO Order_Status2VALUES(2,‘PLACED‘,SYSDATE,4);INSERT INTO Order_Status2VALUES(4,‘PLACED‘,SYSDATE,3);--外键对修改的影响SELECT * FROM Order_Status2;SELECT * FROM employees;UPDATE Order_Status2SET modified_by = 4WHERE 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_Status2DROP CONSTRAINT Order_Status2_status_uq;ALTER TABLE  Order_Status2 ADD CONSTRAINT Order_Status2_status_uq UNIQUE(status)DEFERRABLE INITIALLY DEFERRED;INSERT INTO Order_Status2VALUES(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指定序列从哪个整数开始，默认值为1INCREMENT BY increment_num指定序列每次增加的增量，默认值为1MAXVALUE Maxvalue_num指定该序列的最大整数MINVALUE Minvalue_num指定该序列的最小整数CREATE SEQUENCE test_seq;CREATE SEQUENCE test2_seqSTART WITH 10 INCREMENT BY 5MINVALUE 10 MAXVALUE 20CYCLE CACHE 2 ORDER;CREATE SEQUENCE test3_seqSTART WITH 10 INCREMENT BY -1MINVALUE 1 MAXVALUE 10CYCLE 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 ASSELECT * FROM products WHERE price <  15;CREATE VIEW employees_view ASSELECT employee_id,manager_id,first_name,last_name,titleFROM employees;--对视图进行select操作SELECT product_id,NAME,priceFROM 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_viewWHERE  product_id = 14;--没有选择的基表列被置为空值,salary为nullINSERT INTO employees_view  (employee_id, manager_id, first_name, last_name, title)VALUES  (5, 1, ‘Jeff‘, ‘Jones‘, ‘CTO‘);SELECT * FROM employeesWHERE employee_id = 5;--创建具有check option约束的视图--指定对视图的DML操作必须满足子查询的条件。CREATE OR REPLACE VIEW cheap_products_view2 ASSELECT * FROM products WHERE price <  15WITH 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 ASSELECT * FROM products WHERE price <  15WITH 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;