Oracle's latest SQL recall walkthrough 1

 drop table example; CREATE TABLE Example (ID number (4) Not NULL, NAME VARCHAR, constraint example_id primary key (ID));d ROP SEQUENCE example_sequence; CREATE SEQUENCE example_sequenceincrement by 1--add several start with 1 each--starting from 1 counts nomaxvalue--not setting maximum nocycle--always accumulating, not looping Nocach E--Do not build buffer; drop TRIGGER Example_triger;  Create TRIGGER Example_triger before INSERT on example for each ROW time (new.id is null)--Starts the trigger when the ID is null start generate ID number begin Select Example_sequence.nextval into:new.id from Dual;end;insert to example (name) VALUES (' Zhang San '); INSERT into example (ID , name) VALUES (111, ' John Doe '); INSERT into example (name) VALUES (' 31 '); INSERT into example (name) values (' 32 '); Insert to Example (name) VALUES (' 33 '); select * FROM example;drop table xxx;create table XXX as SELECT * from example; --Copy the data and structure of a table, but the constraint will not be copied select * from xxx;--Modify primary key--1 Check primary key SELECT * from user_cons_columns c where c.table_name = ' Example '; SELECT * from user_cons_columns c where c.table_name = ' xxx ';--2 Delete primary Key ALter Table Example Drop constraint example_id;--3 Add new PRIMARY KEY constraint ALTER TABLE example ADD constraint example_id primary KEY (i d)--4 Add column ALTER TABLE example add age number;--5 Delete columns ALTER TABLE example drop column age;--6 modify columns (the precondition for modifying a column type: To delete the existing database) a   Lter Table Example Modify age varchar (2); ALTER TABLE example Modify ID varchar (20),--7 add constraint--foreign key ALTER TABLE example add constraint fk_example_xxx fore        IGN Key (PID) references xxx (PID); Insert into example (name,age) VALUES (' 31 ', 1), insert into example (name,age) VALUES (' 32 ', 2); INSERT into example (name, Age) VALUES (' Zhang 33 ', 113), insert into XXX (name,pid) VALUES (' 32 ', +), insert into XXX (name,pid) VALUES (' 33 ', 1111); select * FROM Example;select * from xxx;drop table xxx;create table XXX as SELECT * from Example;select * from Xxx;--alter table XXX Add PID number (ten);-----processing copy table constraint ALTER TABLE XXX rename column ID to sid;alter table xxx add id number;update xxx Set Id=sid;alter table xxx drop column SID;-----processing add field and PRIMARY key ALTER TABLE XXXAdd PID varchar2; update xxx set pid=id;alter table XXX add constraint Xxx_pid primary key (PID); select * from xxx;---- Add foreign key For example table ALTER TABLE XXX add constraint fk_xxx foreign key (ID) references example (ID);--delete constraint ALTER TABLE XXX drop const Raint fk_example_xxx;---------process field type modification of existing data (the first 4 steps will cause the constraint in the table to be lost)--1 rename field ALTER TABLE example rename column ID to sid;--  2 Add ID field ALTER TABLE example add ID varchar2 (200); --3 Updating Data Update example Set id = sid;--4 Delete field of backup data ALTER TABLE example DROP column SID;--5 new constraint ALTER TABLE example add Constrai NT EXMAPLE_ID primary key (ID);-------------------------------------------------------------------ALTER TABLE Example add unique (age); ALTER TABLE example drop unique (age);--Lookup Table uniqueness constraint (including name, constituent column) Select column_name from User_cons_ Columns cu, user_constraints au where cu.constraint_name = au.constraint_name and Cu.table_name = ' example ';-----view (if Insufficient permissions, grant CONNECT,RESOURCE,DBA to your instance) CREATE table BBB as SELECT * from example; CREATE OR REPLACE VIEW exam_sum_v1 (NAMe,age) as SELECT d.name,d.age from BBB D;insert to EXAM_SUM_V1 (name,age) VALUES (' lisii ', 1221); insert INTO EXAM_SUM_V1 ( Name,age) VALUES (' lisii ', 1221), insert into EXAM_SUM_V1 (name,age) VALUES (' lisii ', 0000); INSERT into EXAM_SUM_V1 (name, Age) VALUES (' Lisii ', 111); select * FROM Exam_sum_v1;select * from Bbb;alter table BBB drop Column Id;select * from example; The column information for the user table is recorded in select * from Xxx;--user_tab_cols select User_tab_cols. table_name as table name, User_tab_cols. column_name as column name, User_tab_cols. Data_type as data type, User_tab_cols. Data_length as length, User_tab_cols. NULLABLE as is empty, user_tab_cols. column_id as column ordinal, user_col_comments.comments as memo from User_tab_cols INNER join user_col_comments on User_co L_comments. table_name = User_tab_cols. TABLE_NAME and user_col_comments. COLUMN_NAME = User_tab_cols. column_name;--all field information associated to the table select Col.column_name, Uc.constraint_type, Case uc.constraint_type when ' P         ' Then ' √ 'Else ' end ' PrimaryKey ' from User_tab_columns Col left join User_cons_columns UCC on ucc.table_name = Col.table_name and Ucc.column_name = Col.column_name left joins user_constraints uc on uc.constraint_name = Ucc.cons Traint_name and Uc.constraint_type = ' P ' where col.table_name = ' example ';--Query the foreign key field name, referenced table name, applied field name in a table select DISTINCT (c Ol.column_name), R.table_name, r.column_name from User_constraints con, user_cons_columns col, (select T2.tab Le_name, T2.column_name, t1.r_constraint_name from user_constraints T1, User_cons_columns T2 where t1.r_c Onstraint_name = t2.constraint_name and T1.table_name = ' example ') r where Con.constraint_name = Col.constraint_ Name and Con.r_constraint_name = r.r_constraint_name and Con.table_name = ' example ';

Oracle's latest SQL recall walkthrough 1