Common Oracle DBA Statement 8 (Basic SQL)

########### Basic SQL select ################ select col_name as col_alias from table_name; select col_name from table_name where col1 like '_ o %'; ---- '_' matches a single character/* uses a character function (intercepted on the right, the field contains a character, fill a character to a fixed number of digits on the left, and fill a character to a fixed number of digits on the right. */select substr (col1,-), instr (col2, 'G'), lpad (col3, 10, '$'), rpad (col4, 10, '%') from table_name;/* use a number function (rounded to the right/left, rounded to the integer, and rounded to the remainder) */select round (col1,-2), trunc (col2), MOD (col3) from table_name;/* use the date function (calculate the gap between two days for several weeks, the two days are separated by months, and months are added to a month. The next date of a date, the last date of the month of a date, is rounded to the month of a date, round the month of a date */select (sysdate-col1)/7 week, months_between (sysdate, col1), add_months (col1, 2), next_day (sysdate, 'Friday'), last_day (sysdate), round (sysdate, 'month'), trunc (sysdate, 'month') from table_name; /* use the null function (when expr1 is null, take expr2/When expr1 is null, take expr2, otherwise take expr3/When expr1 = expr2 returns NULL) */select nvl (expr1, expr2), nvl2 (expr1, expr2, expr3), nullif (expr1, expr2) from table_name; select column1, column2, column3, case column2 when '50' then column2 * 1.1 When '30' then column2 * 2.1 When '10' then column3/20 else column3end as tttfrom table_name; ------ use the case function select table1.col1, table2.col2 from Table1 [cross join Table2] | ----- flute joins [natural join Table2] | ----- join with the same name in two tables [join Table2 using (column_name)] | ----- join [join table2on (table1.col1 = table2.col2)] with one or more columns in the same column of two tables | [left | right | Full outer join Table2 ------ equivalent to (+) =, = (+) join, full outer join on (table1.col1 = table2.col2)]; ------ join syntax in SQL 1999; example: Select col1, col2 from Table1 t1join Table2 t2on t1.col1 = t2.col2 and t1.col3 = table3 t3on t2.col1 = t3.col3; select * From table_name where col1 <Any (select col2 from table_name2 where continue group by col3 ); select * From table_name where col1 <all (select col2 from table_name2 where continue group by col3); insert into (select col1, col2, col3 form table_name where col1> 50 with check option) values (value1, value2, value3); merge into table_name table1using table_name2 table2on (table1.col1 = table2.col2) when matched thenupdate rows = table2.col2, table1.col2 = table2.col3 ,... when not matched theninsert values (table2.col1, table2.col2, table2.col3 ,...); ----- merge statement