Oracle Common Advanced SQL operations

Operator arithmetic operators: +-*/You can use the Join operator in a SELECT statement: | |    select deptno| | Dname from dept;  comparison operator:> >= =! = < <= like between was null in logical operator: NOT and or  set operator: intersect, Unio N, union All, minus  requirements: The corresponding collection has the same number of columns and data type       query cannot contain long column       column label is the label of the first collection       When you use order BY, you must use positional ordinals, and you cannot use the column name   1. Copy table structure and its data:  create table table_name_new as SELECT * from Table_name_old 2. Copy table structure only:  create table table_name_new as SELECT * from Table_name_old where 1=2;  or:  create table table_name _new like Table_name_old 3. Copy only table Data:  if the two table structure is the same:  insert into table_name_new select * from table_name_old  if two table structures are different:  insert Into Table_name_new (Column1,column2 ...) select Column1,column2 ... from table_name_old  Pasting----------------------------------------------recursive query-the Zicha parent (through child nodes to the root node.)   Query (special bottom) parent node Select *  from Tb_class T start with t.class_id = ' 1030107742 ' Connect by prior T.SUPER_CLAss_id = T.class_idorder by t.tree_level desc  --Parent wrote (traversing the child nodes through the root node.)   Query (bottom) sub-node: Result select *  from Tb_ Class T start with t.class_id = ' 1030107742 ' connect by prior t.class_id = T.super_class_idorder by T.tree_level desc& The format of the NVL function of the NBSP;1.NVL function is as follows: NVL (EXPR1,EXPR2) means that if the first parameter of Oracle is NULL then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the value of the first parameter is displayed.  oralce sitting connected to the right connection   with the + number of the table is not all displayed, the opposite table is all displayed.  ----------------------------------------------2016-2-24 10:06:492, decode function () syntax: Decode (EXPR,SEARCH1, Result1,search2,result2, ......search n,result N, default) explanation: The Decode function will match the value of expr to each search value, If the value of expr equals the search value, the Oracle database returns its corresponding result value, or the default value if no matching search value is returned, or null if the default value in the function defaults. /*select decode (status,0, ' yes ', 1, ' no ') as end  from Sys_user  where id = 158 */ oracle Advanced UPDATE statement   (bulk UPDATE) Update T_source_phase p set p.lineno =   (select num from t_source_line l where P.lineid = l.id) where P.L Ineid is not null; select at the same time Update oracle advanced query statement   (grouped and sorted by group)   Select t.*, Row_number () over (partition by Planid ORDER by sort ASC) row_number  from T_temp_pathinfo_log t  
Oracle Add Field Syntax: The syntax for adding a field: ALTER TABLE tablename Add (column datatype [default value][null/not null],....); @ Modify the syntax of the field: ALTER TABLE tablename modify (column datatype [default value][null/not null],....); @ Delete field syntax: ALTER TABLE tablename drop (column);//January 21, 2016 09:56:53@ creating sequence create sequence Seq_deptminvalue 1maxvalue 99999999start with 241increment by 1cache 20;       @ Advanced Statement 1.INSERT into SELECT-statement statement form: INSERT into Table2 (field1,field2,...) SELECT value1,value2,... from Table1       Requires that the target table Table2 must exist, because the target table Table2 already exists, so we can insert a constant in addition to inserting the field Table1 the source table. 2.SELECT into from-the statement form is: SELECT vale1, value2 into Table2 from Table1 requires that the target table Table2 not exist because table Table2 is automatically created when inserting, and Copies the specified field data in the Table1 to Table2. Examples are as follows:

Oracle Common Advanced SQL operations