Operations for Oracle Tables

  --Creating table Create [GLOBAL Temporary] table table_name (coloum_name TYPE [CONSTRAINT constraint_def DEFAULT Default_exp][,colou M_name TYPE [CONSTRAINT constraint_def DEFAULT default_exp] ...]) [on COMMIT {DELETE | PRESERVE} rows]tablespace Tab_space;/*global temporary indicates that the table's rows are temporary tables, which are called temporary tables. The validity of the row is specified by the on COMMIT clause on the on commit control temporary table's validity period. Delete indicates that the rows are to be deleted at the end of the transaction. PRESERVE indicates that these lines are to be deleted at the end of the session. TABLESPACE specifies the table space for the table. If not specified, the table is stored in the user's default table space. */create TABLE order_status2 (ID INTEGER CONSTRAINT order_status2_pk PRIMARY key,status VARCHAR2 (Ten), Last_modified DATE DEFAULT sysdate); CREATE GLOBAL Temporary TABLE order_status_temp (ID integer,status VARCHAR2 (Ten), last_modified DATE DEFAULT sysdate) on COMMIT PRESERVE Rows;insert into order_status_tempvalues (1, ' New ', sysdate); SELECT * from order_status_temp;--gets information about the table--executes the describe command against the table. --Query 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 ' );--The letter that gets the column of the tableSELECT * 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 ';--Modify Table--alter table/* Add, modify, or delete a column add or remove constraint enable or disable constraint */--Add column 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;--Modify column/* Modify the length of a column modify the precision of a numeric column the default value of the modified column's data type */-- Modify the length of a column--the length of the column can be reduced only if there are no rows in the table or if this is null value ALTER TABLE ORDER_STATUS2 MODIFY status VARCHAR2 (15);-- Modify the precision of a numeric column-you can reduce the precision of a column only if there are no rows in the table or if this is a null value ALTER TABLE ORDER_STATUS2 ADD ID number (4); ALTER TABLE ORDER_STATUS2 MODIFY ID number (5);--Modify the data type of the column--if there is no or null value in the table, you can modify it to any type, otherwise only modify it to a compatible data type ALTER TABLE Order_ Status2 MODIFY Status char (15);--Modify the default value of the column alter TABLE ORDER_STATUS2 MODIFY last_modified default sysdate-1;--Delete column alter TABL E order_status2 DROP COLUMN initially_created;-- Add Constraint----------------------------------------------------------------------------------------------constraint constraint type meaning-------------------------------------------------------------------------- --------------------CHECK C Specifies that the values of a column or set of columns must meet a specific constraint-------------------------------------------- --------------------------------------------------NOT NULL C specifies that a column is not allowed to store null values.  This is actually a mandatory check constraint----------------------------------------------------------------------------------------------PRIMARY Key P Specifies the primary key for the table. A primary key consists of one or more columns.  It uniquely identifies a row of the table----------------------------------------------------------------------------------------------FOREIGN Key F Specifies the foreign key for the table. A foreign key refers to a column in another table, and a column in the table is referenced in a self-referential case----------------------------------------------------- -----------------------------------------Unique U Specifies that a column or set of columns can store only unique values--------------------------  --------------------------------------------------------------------CHECK OPTION V              Specifies that DML operations performed on the view must satisfy the criteria of the subquery.                ----------------------------------------------------------------------------------------------READ only O Specifies that the view is read-only------------------------------------------------------------------------------------------------(1) Add C Heck constraint 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) Add NOT NULL constraint 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 to order_status2values (1, ' PLACED ', sysdate,null);--(3) Add PRIMARY KEY constraint 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) Add Foregin KEY constraint 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);--There is no column, add foreign key ALTER TABLE ORDER_STATUS2 add C Onstraint ORDER_STATUS2_MODIFIED_BY_FK modified_by REFERENCES Employees (employee_id);-presence column, adding foreign key alter TABLE Order_ Status2 ADD CONSTRAINT order_status2_modified_by_fk FOREIGN KEY (modified_by) REFERENCES employees (EMPLOYEE_ID); The effect of the SELECT * from order_status2;--foreign key on inserts insert into Order_status2values (3, ' PLACED ', sysdate,4); INSERT INTO Order_ Status2values (2, ' PLACED ', sysdate,4); INSERT into Order_status2values (4, ' PLACED ', sysdate,3);--effect of foreign key on modification select * FROM Order_status2; SELECT * FROM Employees; UPDATE order_status2set modified_by = 4WHERE order_status_iD = Effect of 2;--foreign key on deletion delete employees where employee_id = 4;--Use an ON delete foreign clause with a CASCADE key constraint, you can specify that when a row of records is deleted in the parent table, All rows that match in the child table--will also be deleted. 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 D ELETE cascade;--when a row of records is deleted from the Employees table, the rows that match the Order_status2 table are also deleted. Delete employees WHERE employee_id = 4;--Use an ON delete set NULL clause with a FOREIGN KEY constraint, you can specify that when a row of records is deleted in the parent table, The foreign key of the matching row in the child table--will be set to null value 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 D ELETE SET null;--When a row of records is deleted from the Employees table, the foreign key column containing the row that matches the Order_status2 table is placed NULL. DELETE employees WHERE employee_id = 4;--(5) Add unique constraint alter TABLE ORDER_STATUS2 add CONSTRAINT order_status2_status_uq UN IQUE (status);--delete constraint ALTER TABLE ORDER_STATUS2 DROP CONSTRAINT order_status2_status_uq;--disable constraint ALTER TABLE ORDER_STATUS2 ADD CONSTRAINT Order_status2_status_UQ UNIQUE (status) DISABLE; ALTER TABLE ORDER_STATUS2 DISABLE CONSTRAINT order_status2_modified_by_nn;--enable constraint-all rows in the table must satisfy the constraint ALTER TABLE Order_ STATUS2 enable CONSTRAINT order_status2_status_uq;--enabling constraints on new data alter TABLE ORDER_STATUS2 enable Novalidate CONSTRAINT order_status2_status_uq;--delay constraint--deferred constraint is a constraint that is enforced when a transaction is committed. --When you add a constraint, you can use the deferrable clause to specify that the constraint is a delay constraint. --When you add a delay constraint, you can identify it as initally immediate or initally deferred. --initally immediate Check this constraint every time you add data to a table, modify data, or delete data--initally deferred this constraint is checked only when the transaction is committed. 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);--If you add rows to the Order_status2 table, the order_status2_status is only executed when you execute the commit. _uq constraint Check--Gets the information of the constraint select * from user_constraints; SELECT * from All_constraints; SELECT constraint_name, Constraint_type, status, Deferrable, deferred from user_constraints WHERE table_name = ' Order_sta TUS2';--Get information about column constraints 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_STAT US2 ';--Rename table rename Order_status2 to Order_state; RENAME Order_state to order_status2;--adding comments to a table-comments help to remember the purpose of the table or column comment on table order_status2 is ' Order_status2 stores the S Tate of an order '; COMMENT on COLUMN order_status2.last_modified is ' last_modified stores the date of the Order were modified last ';--get the notes of the table SE Lect * FROM user_tab_comments where table_name = ' order_status2 ';--get the comment of the column select * FROM user_col_comments where table_name = ' Order_status2 ';--Truncate tables truncate TABLE order_status2;--delete tables drop table order_status2;--create sequence creation 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 specifies the integer from which the sequence starts, the default value is 1INCREMENT by increment_num specifies the increment of the sequence increments each time, the default value is 1MAXVALUE Maxvalue_ NUM Specifies the maximum integer of the sequence Minvalue minvalue_num specifies the smallest integer of the sequence, create SEQUENCE test_seq; CREATE SEQUENCE Test2_seqstart with ten INCREMENT by 5MINVALUE MAXVALUE 20CYCLE CACHE 2 ORDER; CREATE SEQUENCE Test3_seqstart with INCREMENT by-1minvalue 1 MAXVALUE 10CYCLE CACHE 5;--Get sequence Information select * from user_sequ ences; SELECT * from all_sequences;--uses a sequence-sequence to generate a series of numbers, a sequence containing two pseudo-columns Currval nextval, which can be used to get the current and next value of the sequence, respectively. --before retrieving the current value of a sequence, the sequence must be initialized by retrieving the next value of the sequence. --After initialization, you can use Currval to get the current value of the sequence. SELECT Test_seq.nextval from dual; SELECT Test_seq.currval from dual;--uses a sequence to populate the primary key create TABLE order_status2 (ID INTEGER CONSTRAINT order_status2_pk PRIMARY Key,status VARCHAR2 (Ten), 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);--Delete sequence drop SEQUENCE test3_seq;--index--Create index creation [Unique]index index_name on TABLE_NAME (Column_name[,column_name ...]) Tablespace Tab_space;/*unique The value in the specified index column must be 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) inserts into customers VALUES (6, ' AA ', ' BB ', sysdate, ' 800-555 -1214 '); CREATE index CUSTOMERS_FIRST_LAST_NAME_IDX on Employees (first_name,last_name);--Get the information of the index select * from User_indexes; SELECT * from All_indexes; SELECT index_name, TABLE_NAME, uniqueness, status from User_indexes WHERE table_name in (' CUSTOMERS ', ' EMPLOYEES ');--Get the Lasso The information of the citation 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 ');--Delete Index drop Indexes customers_first_last_name_idx;--View--CREATE view created [OR REPLACE] [{force| Noforce}] VIEW view_name [(Alias_name[,alias_name ...]) As Subquery[with {CHECK OPTION | READ only}] CONSTRAINT Constraint_name];/*or Replace view If the view is present force even if the base table does not exist and you want to create the view Noforce the destructors table does not exist, the view is not created, and the default option is Alias_ Name specifies an alias for the expression in the subquery subquery specifies a subquery that retrieves the base table with CHECK option to indicate that the row retrieved by the subquery can be inserted, modified, or deleted. CONSTRAINT_NAME Specifies the name of the WITH CHECK option or the Read only constraint. Read only shows read-only access to rows in the base table */--simple view, containing a subquery, retrieving data from only one base table-complex view, containing a subquery, retrieving data from multiple base tables, including grouping, function calls, etc.-creating and using a simple view 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 operation on view select Product_id,name,pricefrom Cheap_products_view; SELECT * FROM employees_view;--insert operation on view-DML operations can only be performed on simple views, complex views do not support DML operations insert into Cheap_products_view (product_id  , product_type_id, NAME, Price) VALUES (1, ' Western Front ', 13.50); SELECT* FROM cheap_products_view;--without check option, you can insert, modify, or delete rows that cannot be retrieved by a subquery insert into Cheap_products_view (product_id,  product_type_id, NAME, Price) VALUES (1, ' Eastern Front ', 16.50); SELECT * from Cheap_products_viewwhere product_id = 14;--No selected base table column is set to null value, salary is 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;--Creates a view with a check OPTION constraint--Specifies that the DML operation on the views must satisfy the criteria of the subquery. CREATE OR REPLACE VIEW cheap_products_view2 asselect * FROM Products WHERE Price < 15WITH OPTION CHECK CONSTRAINT Chea P_products_view2_price;insert to Cheap_products_view2 (product_id, product_type_id, NAME, Price) VALUES (1, "South" Ern Front ', 16.50);--Create a view with Read only constraint--Specifies that the view is read-only create OR REPLACE view CHEAP_PRODUCTS_VIEW3 asselect * FROM Products wher E Price < 15WITH READ only CONSTRAINT Cheap_products_view3_read_only;insert into CHEAP_PRODUCTS_VIEW3 (product_id, PR oduct_type_id, NAME, Price) VALUES (1, ' Northern Front ', 19.50);--Get information about the view definition select * from User_views; SELECT * from all_views;--for information about view constraints Select Constraint_name, Constraint_type, status, Deferrable, deferred from User_con Straints WHERE table_name in (' Cheap_products_view2 ', ' cheap_products_view3 ');--Delete the View drop-view cheap_products_view3;