Oracle Study Notes-NVL () function usage skills, learning notes-nvl

Oracle Study Notes-NVL () function usage skills, learning notes-nvl

1. Rules for alias columns: special characters (except # $), numbers, and spaces must be added with double quotation marks; Chinese characters can be added with double quotation marks without double quotation marks; as keywords can be omitted.

-- Query the employee name, employee ID, and employee's work.
SELECT empno AS "employee ID", ename "employee name", job FROM emp;
Select empno as "employee ID", ename "employee name", job from emp; -- syntax error, space in alias "work" without double quotation marks
Select empno as "employee ID", ename "employee name", job "job" from emp; -- correct, double quotation marks with spaces
Select empno as "employee No.", ename "Employee @ name", job "job" from emp; -- correct, double quotation marks with special characters
Select empno as "employee ID", ename "123", job "job" from emp; -- correct, double quotation marks are displayed
Select empno as employee $ No., ename "123", job "job" from emp; -- correct, except for special characters (#, $)

Ii. Use of the NVL () function: Remember that the null value is involved in the operation and the result is null.

Emp table: employees with no bonuses

-- Calculate the annual salary of an employee (monthly salary x 12 + bonus)
SELECT sal * 12 + comm annual salary FROM emp;

If the nvl function is not used, the query result is as follows:

-- Nvl (v1, v2), where v1 is the original value and v2 is the value involved in calculation when it is null
SELECT sal * 12 + NVL (comm, 0) AS annual salary FROM emp;

When the nvl function is used, the query results are as follows:

Iii. distinct keyword and oracle-specific connector "|"

Requirement: display the employee ID and employee name in a column. Method: Use the connector unique to oracle

Select 'empno: '| empno | ''| 'ename:' | Personal Profile of ename from emp;

Query results:

Remove duplicate keywords in the query results

Select distinct job from emp;

4. Condition query (the purpose is to filter out results that meet the conditions)

Where is the condition keyword, followed by the condition

Comparison OPERATOR: ><==! =

Logical OPERATOR: and or not

Other arithmetic expressions: like fuzzy query % matches 0 or multiple characters

In not in judgment range

Between and judgment interval, including boundary

Is null, is not null determines whether it is null

--- Query employee information that is not a MANAGER
SELECT * FROM emp WHERE job! = 'Manager ';
SELECT * FROM emp WHERE job <> 'manager ';
-- Query Information about employees who work as a MANAGER and have a salary of more than 2000
SELECT * FROM emp WHERE job = 'manager' AND sal> 2000;
-- Query the employee information of the MANAGER whose salary is greater than 2000 or whose job is the MANAGER.
SELECT * FROM emp WHERE sal> 2000 OR job = 'manager ';
-- Query employee information whose employee name contains M
SELECT * FROM emp WHERE ename LIKE '% M % ';
-- Query the employee information of the second employee M.
SELECT * FROM emp WHERE ename LIKE '_ M % ';
-- Query whether the employee's job is MANAGER or studio PRESIDENT
SELECT * FROM emp WHERE job = 'manager' OR job = 'manager ';
-- Implement with in
SELECT * FROM emp WHERE job IN ('manager', 'President ');
-- Query Information about employees with salaries between 1500 and 3000
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;
-- Query Information about employees with bonuses
SELECT * FROM emp WHERE comm is not null;
-- Null is not equal to any value. If null is compared, UNKUOW is judged.
SELECT * FROM emp WHERE comm = NULL;

5. Use of the sort keyword 'ORDER'

-- Sort employee data by salary from small to large
SELECT * FROM emp order by sal;
-- Sort employee data by bonus Flashback
SELECT * FROM emp order by comm DESC;
-- Null reverse sorting null specify null above to end nulls last
SELECT * FROM emp order by comm desc nulls last;

Vi. database functions: single-row functions (numeric, character, date, conversion, and general functions), multiple-row functions (max, min, sum, count, avg)

1. Rounding round (v1, v2) v1 is the original value v2 is the number of reserved decimal places (v2 defaults to 0)
Select round (43.726) from dual;
Select round (43.726, 0) from dual; -- 44
Select round (43.726, 1) from dual; -- 43.7
Select round (43.726, 2) from dual; -- 43.73
Select round (43.726,-1) from dual; -- 40
Select round (43.726,-2) from dual; -- 0
Select round (53.726,-2) from dual; -- 100

2. -- trunc (v1, v2) is truncated. v1 is the original value. v2 is the number of reserved decimal places. v2 is 0 by default)
-- 43.726
Select trunc (43.726) from dual;
Select trunc (43.726, 0) from dual; -- 43
Select trunc (43.726, 1) from dual; -- 43.7
Select trunc (43.726, 2) from dual; -- 43.72
Select trunc (43.726,-1) from dual; -- 40
Select trunc (43.726,-2) from dual; -- 0
Select trunc (53.726,-2) from dual; -- 0

3. -- mod returns the remainder.
Select mod (10, 3) from dual; -- 1

4. Obtain the length (v1) of a character)
-- Abcde
Select length ('abcde') from dual;

5. truncate the string substr (v1, v2, v3) v1. The original string v2 begins to truncate the position v3.
-- Truncates a string starting from 0 and starting from 1.
Select substr ('abcde', 0, 2) from dual; -- AB
Select substr ('abcde', 1, 2) from dual; -- AB
Select substr ('abcde',-1, 2) from dual; -- e
Select substr ('abcde',-2, 2) from dual; -- de

6. replace (v1, v2, v3) v1 original string v2 replaced character v3 replaced character
-- Replace string hello with all matched characters
Select replace ('hello', 'l', 'O') from dual; -- heooo
Select replace ('hello', 'h', 'L') from dual; -- lllo
Select replace ('hello', 'hes', '') from dual; -- llo

7. case-insensitive conversion upper () lower ()
Select * from emp;
Select * from emp where ename = 'Smith'
Select * from emp where ename = 'Smith'
Select * from emp where ename = upper ('Smith ');
Select * from emp where ename = upper ('Smith ');
Select * from emp where ename = upper ('Smith ');

8. Remove space trim ()

-- Remove space _ abc_de _ remove space at both ends of the string
Select trim ('abc de') from dual; -- abc_de

VII. Date Functions

-- View the current system time

Select sysdate from dual;

-- Add a month and query the date after 3 months
SELECT add_months (SYSDATE, 3) FROM dual;
-- Calculate the number of months of employment for all employees so far
SELECT months_between (SYSDATE, hiredate) FROM emp;
---- Calculate the number of days and dates for all employees. The calculation result is the number of days.
Select round (SYSDATE-hiredate) FROM emp;

VIII. conversion functions

-- String to numeric
SELECT to_number ('123') + 1 FROM dual;
-- Numeric to string
Select to_char (sal, '$99,999.00') from emp;
-- Convert date to string
SELECT to_char (SYSDATE, 'yyyy-mm-dd') FROM dual;
SELECT to_char (SYSDATE, 'hh: mi: ss') FROM dual;
SELECT to_char (SYSDATE, 'yyyy-MM-dd HH: mi: ss') FROM dual;
-- String to date
SELECT to_date ('1970-05-03 ', 'yyyy-mm-dd') FROM dual;
SELECT to_date ('2017-05-03 6:08:32 ', 'yyyy-mm-dd hh: mi: ss') FROM dual; -- the hour can only be 1-12 values.
SELECT to_date ('2017-05-03 16:08:32 ', 'yyyy-mm-dd hh24: mi: ss') FROM dual; -- The Hour value can be 1-24.

9. Use of aggregate and grouping Functions

--- Query the average salary of each department
Select * from emp;
SELECT avg (sal) from emp group by deptno;
-- Display the Department ID
Select deptno, avg (sal) from emp group by deptno;
-- It is not allowed to add other columns. The select statement can only query the columns and Aggregate functions of a group.
Select job, deptno, avg (sal) from emp group by deptno;
-- When grouping multiple columns, the values of multiple columns must be the same.
Select job, deptno, count (*) from emp group by deptno, job;
-- Query the Department ID of the average department salary> 2000
Select deptno, avg (sal) from emp group by deptno having avg (sal)> 2000;
-- MySql grouping can start alias Filtering
Select deptno, avg (sal) s from emp group by deptno having s> 2000;