Oracle basic code learning

Oracle basic code learning

/* ------------ Orcale function ---------------- */1. character function LOWER () LOWER case UPPER () UPPER case INITCAP () connect the first letter to the CONCAT () string (you can also use "| ") functions are connected. // note that in orcale, SUBSTR (column | string, position, and truncation quantity) strings are the same from 0 and start from 1, note that if the position is negative, it is replaced by the length replace () of the truncated LENGTH () string. numeric Function · rounding decimal places: ROUND (), you can also specify the number of digits to rounding, you can also directly rounding Integers to rounding, as long as a negative number (based on the decimal point) · truncation decimal places: TRUNC () is pervasive. The parameter is the same as ROUND.-remainder (Modulo): MOD ('number', 'divisor ') select MOD (489.655523, 2) from dual => 1. 6555233. date function: (SYSDATE-> current day) · Date-/+ number = date · date-date = Number (days) · number of weeks: current date-employment date = days/7 = number of weeks · MONTHS _ () find the number of MONTHS in the given date range. ADD _ MONTHS (); ADD the specified constraint on the specified date, and find the date after. NEXT _ DAY () find the number of the next given date [NEXT_DAY (SYSDATE, 'monday')] nearest to the current LAST _ DAY () to find the last day of the given date LAST_DAY (SYSDATE) july 4. conversion Function: · TO _ CHAR () 1. convert TO_CHAR (hiredate, 'yyyy') to get the Year 2 from the date. the date format is converted to TO_CHAR (hiredate, 'fmyyyy-mm-dd'). There is a leading 0 by default. Use fm to remove the leading 0 3. number formatting: TO_CHAR (sal, '000000') 9 represents a single digit and y represents 4 of January 1, 999,999. digit Area Field: $: Dollar. l: It is the abbreviation of local, and the local language amount shows eg: TO_CHAR (sal, '$999,999') · TO _ NUMBER () is converted TO a NUMBER: convert the string of a number into a number. general function: · NVL converts a null value to the specified content: (sal + NVL (comm, 0) * 12 if comm is null, it is displayed as 0. If the NVL function is not used and comm is NULL, the above description must be the NULL-DECODE () function! Similar to, IF... ELESEIF .... ELSE statement select decode (, 'content is 1', 2, 'content is 2', 3,' content is 3') from dual; DECODE (------- content is 2 visible: the first parameter is a numeric value that is followed by a condition corresponding to a result SELECT empno, ename, DECODE (job, 'cler', 'salesman', 'salesman ', 'manager', 'manager') FROM emp; CLERK --> SALESMAN --> salesperson manager' --> MANAGER sqlplus Common commands ed and @ command connection: conn username/password as sysdba set linesize length set row display quantity set pagesize length set page display quantity

 

/* ------------------- Join query --------------------- */SELECT e. ename employee name, e. sal salary, d. dname department, s. grade salary level FROM emp e, dept d, salgrade s WHERE e. deptno = d. deptno AND e. sal BETWEEN s. losal AND s. hisal;

 

I. Join the left and right join of the table. (+) ON THE = left, the right join is opposite to the following link. (+) on the right side of =, the left join [Default] is based on the table on the left. If there is no data on the left side, it is not displayed even if there is data on the right side.

/* ---------------------- Group function --------------------------- */1. COUNT 2.MAX/ MIN max Max. 3.SUM/ AVG sum/average key: (grouping function) · example: Find the number of employees in each department. SELECT deptno, COUNT (empno) should be grouped by department) FROM emp group by deptno Note: SELECT deptno, COUNT (empno) FROM emp * 1st Line Error: ORA-00937: not a single component GROUP function the above Code cannot be executed because: 1. if the grouping function is used in the program, there are two possible scenarios: · The Program has a group by function and the grouping conditions are set, so that grouping conditions can be queried together. · If grouping is not used, only grouping functions can be used separately. 2. When grouping functions are used, fields other than grouping functions and grouping conditions cannot appear. Join Table query: SELECT d. dname, COUNT (e. empno) FROM emp e, dept dWHERE e. deptno = e. deptnoGROUP BY d. dname shows the Department numbers and average salaries with an average salary greater than two thousand: · The grouping function can only be used in groups and cannot appear in the WHERE statement. If you want to specify the grouping conditions, you can only use the command HAVING for the second condition. SELECT deptno, AVG (sal) FROM empGROUP BY deptno having avg (sal)> 2000;

 

Grouping principle: · only repeated content in a column can be considered. Note: · grouping functions can be nested, but when grouping functions are nested, no query group can appear again.

/* ---------------- Subquery ------------------- */request to query all employees whose salaries are higher than 7654: SELECT * FROM empWHERE sal> (SELECT sal FROM emp WHERE empno = 7654 ); the subquery must contain parentheses. Subquery operations are classified into three types: 1. single Column subquery 2. single Row subquery (multiple columns may be a complete record) 3. multi-row subquery: multiple record examples are returned: SELECT ename, job, salFROM empWHERE sal = (select min (sal) FROM emp); there are three query symbols in the subquery: the INANYALL-IN operator specifies a query range: for example, to obtain the minimum wage information of each department: · The minimum wage of each department, the returned value must be multiple, therefore, you can use IN to specify the operation range SELECT * FROM emp WHERE sal IN (select min (sal) FROM emp group by deptno ); · ANY operation = the functions of the operators of any and in are exactly the same: SELECT * FROM emp WHERE sal = ANY (select min (sal) FROM emp group by deptno);> ANY is larger than the minimum value in SELECT * FROM emp WHERE sal> ANY (select min (sal) FROM emp group by deptno ); <ANY is smaller than the maximum value in SELECT * FROM emp WHERE sal <ANY (select min (sal) FROM emp group by deptno ); · ALL operation> if ALL is greater than the maximum value, <ALL is smaller than the minimum value.

 

/* ------------------- Database update operation ---------------- */copy TABLE: create table myemp as select * FROM emp; · transaction processing: commit: submit transaction rollback: Roll Back, however, you cannot roll back after commit.

 

· A deadlock occurs in transactions. If a session updates data but does not have a commit, other sessions cannot be updated immediately. The update is allowed only after the peer's commit, otherwise, it will remain in the waiting state.