Oracle-sql Base Statement

1 --Remove duplicate rows: Distinct2 --scope: Is the combination of all subsequent fields3 Select distinctDeptno Sal fromEMP;4 SELECT *  fromEmpORDER  bySal;5 --Condition of (date type)6 SELECT *  fromEmpWHEREHireDate='1 July-December on -80';7 --use a like fuzzy query (% for 0 or more characters, _ for one character, special characters for escape identifiers)8 SELECT *  fromEmpWHEREEname like 's%';9 SELECT *  fromEmpWHEREEname like '_m%';Ten SELECT *  fromEmpWHEREEname like '%\_%' ESCAPE '\'; One --using the IS null query A SELECT *  fromEmpWHEREComm is NULL; - SELECT *  fromEmpWHEREComm is  not NULL; - --SQL Optimizations: the -----and put a less-than-retrieved condition behind - -----OR put the conditions of the search result in the back - --Oracle functions are divided into single-line functions and multi-line functions - -----Single-line function is divided into character function, numerical function, date function, conversion function, passing function + -----Multiline functions into sum (), AVG () Count () Max () min () - --First Letter capital: Initcap + --full turn lowercase: lower A --full turn capital: Upper at --left Remove: ltrim example: LTrim (' abcdef ', ' ab '); - --Right Removal: rtrim Example: RTrim (' abcdef ', ' ef '); - --Replacement: replace example: replace (' Jack and Jue ', ' j ', ' bl '); Result: black and blue; - --find the location of the string: InStr Example: InStr (' Worldwide ', ' d '); Results: 5 - --Intercept String: substr example: substr (' ABCDEFG ', 3,2); Result: CD - --Connection string: contact example: (' Hello ', ' world '); Result: HelloWorld in --Numeric Functions - --take absolute value: ABC () to --Rounding up: ceil () + --sine: sin () - --cosine: cos () the --Take symbol: sign () * --Rounding Down: Floor () $ --n power of M: Power (m,n);Panax Notoginseng --take remainder: MOD (m,n); - --rounded: ROUND (m,n); the --truncation: TRUNC (m,n); + --square Root: SORT () A --Date Function the --sysdate: Current date Time + --Months_between: Returns the month of the two-day period - --add_months: Returns the new date on which the month is added to the date $ --next_day: Returns the new date for the week after the specified date $ --Last_day: Returns the last day of the month on which the specified date is - --Round: Rounding a date by a specified date format - --trunc: Truncate the date in the specified way the SELECT *  fromdual; - --(automatic type conversion)Wuyi Select '12.5'+ -  fromdual; the --(forced type conversion) - SELECT '12.5' || -  fromdual; Wu --Conversion of characters and numbers to each other & characters and dates - VARCHAR2 OR CHAR-- Number About VARCHAR2 OR CHAR --Date $  Number --varchar2 -DATE--varchar2 - --implicit conversion between data types is still recommended, with display conversion functions, a well-controlled design style - --common type Conversion functions have To_char () to_date () To_number () A  +  the --multi-line functions operate on a set of data, returning only one result for a set of data (multiple rows of records), also known as a grouping function - SELECT AVG(SAL),MAX(SAL),MIN(SAL),sum(SAL) fromEMP; $ --Use the NVL () function to force a multiline function to handle null values the Select Count(NVL (Comm,0)),sum(NVL (Comm,0)),avg(NVL (Comm,0)) fromEMP; the COUNT(*) Returns the total number of records in a group the SELECT COUNT(*) fromEMP; the SELECT *  fromEMP; - COUNT(EXPreturns the number of records for which an exp value is not empty in Count(distinct(Exp) returns the number of non-empty records that the expression exp value does not duplicate the --the GROUP BY clause groups the data in a table the SELECTDeptno,job,COUNT(*),AVG(SAL) fromEmpGROUP  byDeptno,job; About --Note: the -----1. The field that appears in the select list, if it is not contained in a multiline function, must appear in the GROUP BY clause the -----2. Fields that are included in the GROUP BY clause do not have to appear in the select list the --The case where a field (one-line function) is not allowed to be mixed with a multiline function---3.SELECT list + -----4. Do not allow the use of multiline functions in the WHERE clause - --The result of having a clause filtered and grouped, it can only appear after the GROUP BY clause, where filter row, having filter group, having support all the where operators the --1. In the EMP table, list positions with a minimum wage value of less than 2000. Bayi SELECTJobMIN(SAL) the  fromEMP the GROUP  byJob -  having MIN(SAL)< -; - 2List of departments and work-mix combinations with average wage greater than $1200 the SELECT *  fromEMP; the SelectDeptno, Job,avg(SAL) the  fromEMP the Group  byDeptno,job -  having AVG(SAL)> - the ORDER  byDeptno,job the 3average wage of a department with a statistical population of less than 4 the SELECTDeptnoAVG(SAL)94  fromEMP the GROUP  byDeptno the  having COUNT(*)<4; the 4. Statistics of the number of departments98 SELECTDeptno,COUNT(*) About  fromEMP - GROUP  byDeptno;101 5statistics of the highest wages in all sectors, excluding the department with a maximum wage of less than 3000102 SelectDeptnoMax(SAL)103  fromEMP104 Group  byDeptno the  having  not Max(SAL)< the;106 --Querying all Tables107 SELECTtable_name fromUser_tables;108 --View Table Structure

Oracle-sql Base Statement