Summary of typical Oracle SQL statements @ SQL-plus key functions in series and SQL statement cases @ Chinese sorting details)

Summary of typical Oracle SQL statements @ SQL-plus key functions in series and SQL statement cases @ Chinese sorting details) 1. classic select SQL statement // note: the result obtained by a mathematical expression containing null values is a null value SQL> select sal + comm from emp; // link the employee ID and employee name fields SQL> select empno | ename as "employee ID and employee name" from emp; // SQL> select distinct (deptno) from emp; // SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where sal <> 3000; or SQL> select empno, ename, job, mgr, hiredate, sal, comm, de Ptno from emp where sal! = 3000; // query information about employees whose salaries are greater than or equal to 1600. SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where sal between 1600 and 3000; equivalent to SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where sal> = 1600 and sal <= 3000; // query the employee information of Department number 10, 20 SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where deptno in (10, 20); equivalent to: SQL> select empno, ename, job, mgr, hiredate, sal, comm, Deptno from emp where deptno = 10 or deptno = 20; // query information about employees whose names start with "S" SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where ename like's % '; // query the employee information whose name ends with S. SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where ename like '% s'; // query employee information whose third character is N. SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where ename like '_ N %'; // query employee information whose employee name contains n SQL> sele Ct empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where ename like '% N % '; // query employee information whose last and second characters are % in employee name SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where ename like '% \ % _ 'escape' \ '; // query the total employee information contained in employee names. SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where ename like '% \ % 'escape '\'; relational Database-a hierarchical relationship --- a net-type relationship-a relational database-A object relationship. Note: It can be used in combination with condition statements, but the order by clause is placed at the end. // query the employee information and sort the employee information in descending order according to the employee's Department number. SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp order by deptno asc, empno desc; Note: Understanding the Train of Thought: first query the employee information in ascending order by Department number, then, employees in each department are sorted in descending order by employee numbers. string functions string functions are the most widely used functions in oracle. LOWER: LOWER case UPPER: UPPER case INITCAP: UPPER case CONCAT: connected SUBSTR: truncation (parameter, start, number) LENGTH: returns the LENGTH of the string INSTR :( parameter, letter) returns the location where the letter appears. LPAD: (parameter, length, prefix parameter letters) RPAD: (parameter, length, followed by parameter letters) TRIM: Before Truncation Space replacement: (parameter, parameter [, parameter]): the first parameter operand, the second is the character to be searched, and the third is the replacement character, if there is no third character, delete the search character. // Query the employee information with the name scott (case-insensitive) // query the location where the employee name contains the O character 3. SQL statement of the classic string function // query the location where the employee name contains the O character and obtain the employee name length. SQL> select empno, instr (ename, 'O '), length (ename), job, mgr, hiredate, sal, comm, deptno from emp; // In the query results, the employee's work is filtered out from the third letter, starting with the ERK employee SQL> select empno, instr (ename, 'O'), length (ename), job, mgr, hiredate, sal, comm, deptno from emp where substr (job, 3) = 'er'; // when there are two parameters, from the starting position directly to the end of the parameter; the results are the same as those above, and the number of interceptions is 3 SQL> select empno, in Str (ename, 'O'), length (ename), job, mgr, hiredate, sal, comm, deptno from emp where substr (job, 3, 3) = 'er '; // query employee information. The salary is 10 digits not enough. Fill in the left part. * fill in SQL> select empno, ename, job, mgr, hiredate, LPAD (sal, 10, '*'), comm, deptno from emp; // query employee information. The salary is 10 digits not enough. Fill * in the right section to fill in SQL> select empno, ename, job, mgr, hiredate, RPAD (sal, 10, '*'), comm, deptno from emp; // query employee information to remove SQL> select empno, TRIM ('S' from ename), job, mgr, hiredate, comm, Deptno from emp; equivalent to: SQL> select empno, TRIM (both's 'from ename), job, mgr, hiredate, comm, deptno from emp; // query the employee information and remove the S characters in the employee name from SQL> select empno, TRIM (Leading's 'from ename), job, mgr, hiredate, comm, deptno from emp; // equivalent to SQL> select empno, LTRIM (ename,'s '), job, mgr, hiredate, comm, deptno from emp; // query the employee information and remove the S characters following the employee name from SQL> select empno, TRIM (trailing's 'from ename), job, mgr, hiredate, comm, dept No from emp; equivalent to: SQL> select empno, RTRIM (ename,'s '), job, mgr, hiredate, comm, deptno from emp; 1. String connection | or concat ('','') SQL> select 'A' | 'B' from dual; // oracle-specific symbolic operation 'A' | 'B' -------- AB SQL> select concat ('A', 'B') from dual; CONCAT ('A ', 'B') --------------- AB SQL> select concat ('A', 'B'), 'C') from dual; CONCAT ('A ', 'B'), 'C') ----------------------------- substr ('', starting position, long character Degree) instr ('', 'character ') SQL> select substr ('abcdef', 1) from dual; SUBSTR ('abcdef', 1) ---------------- abcdef remarks: the start position starts from 1. If there are two parameters (substr ('', 2 )) it indicates the string SQL> select substr ('abcdef', 1, 3) from dual; SUBSTR ('abcdef', 1, 3) from the start of Position 2 to the end of the string) -------------------- function dbms_random.string ('A', 6) of the abc random string // The first parameter also has the following meanings. The second parameter represents the length of the generated string. U uppercase letters l lowercase letters a uppercase letters x uppercase letters and numbers p arbitrary characters // a random string of 5 Characters and uppercase characters is generated. Observe the following results: DBMS_RANDOM.STRING ('U', 5) Explain RAVTG DBMS_RANDOM.STRING ('U', 5) ----------------------------------------------------------- the meaning of U, l, a, x, and p in NOAJN remarks. SQL> select dbms_random.string ('l', 5) from dual; // a random string of 5 Characters in lower case, DBMS_RANDOM.STRING ('l', 5) ----------------------------------------------------------- tawgc SQL> select dbms_random.string ('A', 5) from dual; // generate a random string DBMS_RANDOM.STRING ('A', 5) with A length of 5 and a combination of uppercase and lowercase characters) --------------------------------------------------------- TEczi SQL> select dbms_random.string ('x', 5) from dual; // randomly generates a 5-character combination with numbers. DBMS_RANDOM.STRING ('x', 5) limit 4 tduu SQL> select dbms_random.string ('x', 5) from dual; DBMS_RANDOM.STRING ('x', 5) optional bytes Z select dbms_random.string ('P', 5) from dual; // a random string of 5 Arbitrary length DBMS_RANDOM.STRING ('P', 5) bytes 5 = 3) * SQL> selec T dbms_random.string ('P', 5) from dual; DBMS_RANDOM.STRING ('P', 5) ------------------------------------------------------------- zf, @ z 4. number function: ROUND: Rounding TRUNC: truncation MOD: Finding residual ABS: absolute CEIL: returns the smallest integer greater than or equal to value FLOOR: returns the largest integer less than or equal to value SQRT: it is meaningless to return the negative square root of the value. // The rounding result is 46 5. the SQL statement of the classic numeric function is rounded down (for more details, refer to my oracle blog 2) // The result is 1800.11 SQL> select round (1800.11111, 2) from dual; // The result is 1800 SQL> select round (1800.11111,-2) from dual; // The result is 1900 SQL> select round (1899.11111,-2) from dual; // The result is 1800.67 SQL> select round (1800.6666, 2) from dual; // the absolute value is 45.56 SQL> select abs (-45.56) from dual; // The result of a minimum integer greater than or equal to 46 SQL> select ceil (45.56) from dual; // The result of a minimum integer greater than or equal to-45 SQL> Select ceil (-45.56) from dual; // if the value is less than or equal to the maximum integer, the result is-46 SQL> select floor (-45.56) from dual; // returns the remainder of 300 SQL> select mod (1800,500) from dual // when the number of intercepted operations is positive: only the decimal point is 1800.11 SQL> select trunc (1800.11111, 2) from dual; // when the number of truncated operations is negative: The operation is the digit before the decimal point, change the number of digits before the decimal point to 0. the result is 1000 SQL> select trunc (1899.11111,-3) from dual; 6. the datetime function in Oracle actually contains two values: Date and time. The default date format is DD-MON-RR. The datetime function is used to return the date and time of the current system, and to perform processing operations on data of the date and time type. Common functions include add_months (date, count); add count month last_day (date) on the specified date; return months_between (date1, dates) on the last day of the month where date is located ); returns the interval between date1 and date2 for how many months new_time (date, 'this', 'other'); converts the time date from this time zone to the other time zone next_day (day, 'day'); returns the date of the specified date or the day of the first week of the last day, where day is the day of the week sysdate (); obtains the current date current_timestamp () of the system (); get the current time and date value round: returns the mathematical operation of the truncated date of the trunc date in four rounds: add or subtract a number on the date and the result is still the date. Returns the number of days between two dates. You can add or subtract hours to the day period by dividing the number by 24. // Obtain the current time display format of the system. The default format is displayed. Result:-11 11.15.38.390000 AM + 7. classic date SQL statement // The result of adding three months to the current date: 11:18:36 select add_months (sysdate, 3) from dual; // return the result displayed on the last day of the current month: 2011-4-30 select last_day (sysdate) from dual; // return the number of months between the two dates: 4; (sysdate, 4) Here, the number is the number of results. SQL> select months_between (add_months (sysdate, 4), sysdate) from dual; // Date result of converting from GMT time zone to AST time zone SQL> select new_time (sysdate, 'gmt ', 'Ast ') from dual; // returns the date value of the next Monday, which must be written as the 'date' of the Week' SQL> select next_day (sysdate, 'monday') from dual; 8. implicit conversion of conversion functions: Explicit conversions automatically completed by the system during the operation: the corresponding conversion functions must be called during the operation. Explicit conversion: to_char (date, 'format'): converts numeric or date data to a string in the specified format 9. SQL statement of the classical Conversion Function // converts the current date to the format of YYYY/MM/dd SQL> select to_char (current_timestamp, 'yyyy/MM/dd') from dual; // convert the current date to the format of YYYY/MM/DD HH24/MI/ss am SQL> select to_char (current_timestamp, 'yyyy/MM/DD HH24/MI/SS am') from dual; // DD "of" month SQL> select to_char (current_timestamp, 'yyyy DD "of" month hh/MI/SS am') from dual; // returns a string based on $99,999. In addition, the number of digits of the operand cannot be greater than 5 (that is, the number of digits after $). Otherwise, the result is ######## SQL> select to_char (11111, '$99,999') from dual; to_number (char); converts a string containing a number to to_date (string, 'format '); converts a string to date data in the specified format. If foramt format is omitted, the default date format (DD-MON-YY) is used ); // determine the number of days between two dates SQL> select to_date ('1970-07-15 ', 'yyyy-MM-DD')-to_date ('1970-02-08 ', 'yyyy-MM-DD ') from dual; // obtain the weekly SQL> select (to_date ('2017-07-15', 'yyyy-MM-DD ') for the difference between two dates ') -to_date ('20180101' -02-08 ', 'yyyy-MM-DD')/7 as "date" from dual; // The Week is rounded up SQL> select ceil (to_date ('2017-07-15 ', 'yyyy-MM-DD')-to_date ('2017-02-08 ', 'yyyy-MM-DD ')/7) from dual; chartorowid (char); convert the string to rowid type rowidtochar (x); convert the rowid type to character type data 10. common functions these functions apply to any data type and also apply to null values: NVL (expr1, expr2): replace the null values of expr1 with expr2 NVL2 (expr1, expr2, expr3 ): determines whether expr1 is null. If it is null, expr3 is returned. If it is not null, expr2 is returned; equivalent to: expr1! = Null? Exrp2: expr3; NULLIF (expr1, expr2): returns NULL if expr1 is equal to expr2, and returns expr1 COALESCE (expr1, expr2 ,..., exprn): Compared with NVL, COALESCE can process multiple alternate values at the same time. If the first expression is null, return the next expression and perform COALESCE on other parameters. 11. SQL statement of the classic common function // Replace the date with null with the given date SQL> select empno, ename, job, mgr, nvl (hiredate, to_date ('2017-1-10 ', 'yyyy-MM-DD '), sal, nvl (comm, 0), deptno from emp; // calculate the employee's salary for that month = (salary + bonus ); use the nvl function SQL> select empno, ename, job, mgr, hiredate, (nvl (sal, 0) + nvl (comm, 0) as "salary", deptno from emp; // calculate the employee's salary. Use the nvl2 function SQL> select empno, ename, job, mgr, hiredate, nvl2 (comm, sal + comm, sal) as "salary ", deptno from emp; // observe and understand; whether the ename length is equal to the job Length SQL> select ename as "expr1", job as "expr2", nullif (length (ename), length (job) from emp; // Execute SQL> select empno, ename, job, mgr, hiredate, coalesce (comm, sal, 10000) as "salll", deptno from emp; important: single-row functions can be nested. The execution sequence of nested functions is from inner to outer. 12. conditional expression: IF-THEN-ELSE logic Case function usage CASE expr when then return_expr1 [when then return_exprn ELSE else_expr] END Deecode function usage DECODE (col | expression, search1, result1 [, search2, result2,...,] [, default]) // assign a 5000 Yuan Prize to a Manager employee. SQL> select ename, job, case job when 'manager' then nvl (sal, 0) + 5000 end as "salary" from emp; // employee salary SQL> select ename, job, 2 case job when 'manager' then nvl (sal, 0) + 5000 + nvl (comm, 0) 3 else nvl (sal, 0) + nvl (comm, 0) 4 end 5 from emp; // rewrite the statement to decode SQL> select ename, job 2, decode (job, 'manager', nvl (sal, 0) + 5000 + nvl (comm, 0 ), 3 'cler', nvl (sal, 0) + nvl (comm, 0) + 200, 4 nvl (sal, 0) + nvl (comm, 0 )) as "salary" 5 from emp; 13. order by nlssort (column, 'nls _ SORT = SCHINESE_PINYIN_M '); // pinyin order by nlssort (column, 'nls _ SORT = SCHINESE_STROKE_M '); // stroke order by nlssort (column, 'nls _ SORT = SCHINESE_RADICAL_M'); // an example is given below: SQL> select * from student; // query all data sid name dept age ----- ------------------ ---------- --- 1 Zhang San Nong Da 21 2 Li Si Nong Da 22 3 Wang Wu Gong da 21 4 Zhao Liu Gong DA 20 SQL> select * from student order by name; // observe sid name dept age ----- -------------------- ---------- --- 1 Zhang San Nong Da 21 2 Li Si Nong Da 22 3 Wang Wu Gong da 21 4 Zhao Liu Gong DA 20 SQL> select * from student order by nlssort (name, 'nls _ SORT = SCHINESE_PINYIN_M '); // sort sid name dept age by NAME pen ----- ------------------ ---------- --- 2 Li Si Nong Da 22 3 Wang Wu Gong da 21 1 Zhang San Nong Da 21 4 Zhao Liu Gong DA 20 SQL> select * from student order by nlssort (name, 'nls _ SORT = SCHINESE_STROKE_M '); // understand sid name dept age ----- -------------------- ---------- --- 3 Wang Wu Gong da 21 1 Zhang San Nong Da 21 2 Li Si Nong Da 22 4 Zhao Liu Gong DA 20 SQL> select * from student order by nlssort (name, 'nls _ SORT = SCHINESE_RADICAL_M '); // understand sid name dept age ----- ------------------ ---------- --- 1 Zhang Sannong University 21 2 Li sinong University 22 4 Zhao liugong University 20 3 Wang wugong University 21 remarks: the null Value participates in sorting. The null value is the largest.