Primary Knowledge database (function)

function

--Single-line function

--Function: Ability to receive parameters, complete certain calculations, and return results

--Test table: Dual

SELECT * FROM dual

--String function

--Uppercase and lowercase conversion functions

--Convert ' I love SQL ' to uppercase, lowercase, first letter

Select UPPER (' I Love SQL '), lower ("I love SQL"), Initcap (' I Love SQL ') from dual

--string concatenation

--Connect ' I ', ' Love ', ' SQL ' together

Select ' I ' | | ' Love ' | | ' SQL ' from dual

Select Concat (concat (' I ', ' Love '), ' SQL ') from dual

--intercept function: substr (source string, M[,n])

--M: Intercept from the beginning of the source string

--n: Intercept from the beginning of the interception of several

Select substr (' I love sql ', 3,4) from dual

Select substr (' I Love sql ', -5) from dual

Select substr (' I Love sql ', -5,3) from dual

--Query the EMP table where the position is the employee information that ends with ' man '

SELECT * from emp where substr (job,-3) = ' man '

--Query employee information for the first letter ' K '

SELECT * from emp where substr (ename,1,1) = ' K '

--String length function: Length (p1)

--Return ' I Love sql ' length

Select Length (' I Love SQL ') from dual

--Returns the position of the substring in the source string: InStr (source string, substring [, M, [n]])

--m: Starting from the first few

--n: The first occurrence of the subscript position

Select InStr (' I Love sql ', ' L ') from dual

Select InStr (' I love SqL ', ' L ', 4,1) from dual

Select InStr (' I love SqL ', ' L ', up to) from dual

InStr (' 192.168.101.123 ')

--Query the EMP table employees whose names contain at least one ' E '

SELECT * FROM emp

where InStr (ename, ' E ', 1, 1)! = 0

--Fill function: Lpad ()-left padding, rpad ()-Right padding

--lpad (source string, M, substring), rpad (source string, M, substring)

Select Lpad (' abc ', Ten, ' * ') from dual

--Query the employee's salary, 1000, show A *, 2000, show two *

Select Ename, Lpad (' * ', sal/1000, ' * ')

From EMP

--Remove function: trim (character from source string)

--Remove the characters from both ends of the source string

Select Trim (' L ' from ' level ') from dual--default both

Select Trim (Leading ' L ' from "level") from dual

Select Trim (Trailing ' L ' from "level") from dual

Select Trim (both ' L ' from "level") from dual

--Replace function

--replace (source string, lookup string, replacement string)

--Replace all found strings in the source string with replacement strings

--replace ' sql ' in ' I Love SQL ' with Oracle

Select replace (' I love sql ', ' QL ', ' Oracle ') from dual

--Numeric functions

--Rounding: Round (data source, number of bits)

Select Round (657.36,1) from dual

Select Round (657.36,0) from dual

Select Round (657.36,-1) from dual

--Truncation function: trunc (data source, number of bits)

Select Trunc (657.32,1) from dual

Select Trunc (657.32,-1) from dual

--Take the Surplus mod (m,n)

Select mod (10,3) from dual

--Absolute ABS (m)

Select ABS ( -5.2) from dual

--Date function

--sysdate: Time to return the current system, no arguments, no brackets required

Select Sysdate from dual

--months_between (P1,P2) returns the number of months before a two date

--Query the EMP table for how many months have all the employees worked

Select Months_between (sysdate,hiredate) from EMP

Select Months_between (hiredate,sysdate) from EMP

-Calculate your own age

Select Trunc (Months_between (sysdate, ' October-February-1987 ')/12,2)

From dual

--add_months (specified date, n) returns the date of a specified date plus n months

--Calculate each employee's regularization date in the EMP table

Select Add_months (hiredate,3) from EMP

-Note: If the given date is the last day of a month, the result is the last day of the month

--Returns the day of the week within seven days of the given date: Next_day (given date, ' Day of the Week ')

Select Next_day (sysdate, ' Tuesday ') from dual

--Returns the last day of the month for a given date

--last_day (given date)

Select Last_day (sysdate) from dual

Type Conversions

--Implicit conversion

SELECT * from emp where deptno = ' 10 '

SELECT * from emp where Deptno = 10

--Explicit conversion

--to ensure the correctness of SQL statements

--Convert the corresponding data type using Oracle to the provided conversion function

--to_char (date | number, ' FMT ') string conversion function

--Query the current system date, format 2016-03-22, Tuesday

Select To_char (sysdate, ' yyyy-mm-dd dy ') from dual

--Query the current system time, Format: Time format: minutes: Seconds

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss dy ') from dual

--Query the current system time

--format: xxxx year XX month XX day

Select To_char (sysdate, ' yyyy "year" MM "month" DD "Day") from dual

--Convert numbers into strings

--Query the salary of king in the employee table, format required to be displayed in renminbi format

Select To_char (Sal, ' l99,999.99 ') from emp where ename = ' KING '

--Query The EMP table employee who entered the job on December 17, 1980

SELECT * from emp where hiredate = to_date (' 1980-12-17 ', ' yyyy-mm-dd ')

Select To_number (' 123 ') from dual

--Null value conversion function

--1, NVL (P1,P2) If P1 is not null, return P1, otherwise return p2

--Query Employee table, employee name, salary, annual salary

Select ENAME,SAL,SAL*12+NVL (comm,0)

From EMP

--2, NVL2 (P1,P2,P3) If P1 is not null, return P2, otherwise return p3

Select ENAME,SAL,SAL*12+NVL2 (comm,comm,0)

From EMP

--3, COALESCE (P1,P2,P3,P4 ...) returns the first non-null parameter value

Select Ename,sal,coalesce (sal+comm,sal,0) *12

From EMP

--case-expression

--Grammatical structure

Case Column | expression

When value 1 then return value 1

When value 2 then return value 2

When value 3 then return value 3

When value 4 then return value 4

... ...

When value n then return value n

else default return value

End

--Demand: Query staff information in employee table 10, request the position in Chinese output

Select Ename,job, (case job

When the ' clerk ' then ' clerk '

When ' salesman ' and ' manager '

When the ' president ' then ' chairman '

Else ' employees '

End

From emp where deptno = ' 10 '

--decode-expression

Grammatical structure

Default return value

--decode (column | expression, value 1, return value, value 2, return value ..., return value N)

Select Ename,job,decode (Job, ' clerk ', ' clerk ', ' salesman ', ' manager ', ' president ', ' chairman ', ' employee ')

From emp where deptno = ' 10 '

Multi-Table Query

--Combine multiple tables together for querying

--You need to connect the table to the table.

--Write the join condition after the FROM clause

--Query the names of employees, department numbers, and department names in the EMP table

SELECT *

From Emp,dept

--If the two tables are simply connected, a Cartesian product is generated

--Create an associated field for two tables, giving the associated condition

--related fields, chivalrous angle: Two fields column names are the same, data types are the same, content is in the same range

--Generalized angle: two columns to have the same meaning

SELECT * FROM Dept

Select Ename,e.deptno

From EMP e,dept D

where E.deptno = D.deptno

--in Multi-table Association

--1, equivalent connection: Two common fields of a table are connected with =

--2, non-equivalent connection: Two common fields of a table do not use = Connect

--Query employee name, salary, and salary level

Select Ename, Sal,grade

From EMP E,salgrade s

where e.sal between S.losal and S.hisal

--Query The EMP table for department number 30 employee name, salary, department number, and salary level, department name

Select E.ename,e.sal,e.deptno,s.grade,d.dname

From EMP e,dept D,salgrade s

where E.deptno = D.deptno

and e.sal between S.losal and S.hisal

--note that when n tables are connected, at least (n-1) conditions are required, or a Cartesian product will appear.

--3, outer joins: Connecting with symbols (+)

--(+) always written on one end of the lack of tables

If you want to query which table information, the other table is the lack of a table

--Find out what employees are in each department, even if they don't have employees

Select E.empno,e.deptno,d.dname

From EMP e,dept D

where E.deptno (+) = D.deptno

ORDER BY E.deptno

--Query employee information, even if the employee has no department

Select E.empno,e.deptno,d.dname

From EMP e,dept D

where E.deptno = D.deptno (+)

ORDER BY E.deptno

--4, self-connect: Query results and query conditions need to be in the same table

--Query the employee table and Smith are employees in the same department, not including Smith

Select E1.ename,e1.deptno

From EMP e1,emp E2

where E1.deptno = E2.deptno

and e2.ename = ' SMITH '

and E1.ename <> ' SMITH '

SQL99 Connection Syntax

--1, Cross joins: Crossing joins, generating Cartesian product

--Query employee tables and departmental tables

Select emp.*,dept.*

From EMP Cross JOIN Dept

--2, natural join: Natural Connection

--for connecting two tables with the same column name and the same type

--Query employee tables and departmental tables

Select Ename,dname

From EMP Natural Join Dept

--3, using byte connection, write condition required

--Query employee tables and departmental tables

SELECT *

From EMP Join dept using (DEPTNO)

--4, ON clause connection, specifying join conditions

--Query employee tables and departmental tables

SELECT *

From EMP Join dept on (Emp.deptno = Dept.deptno)

--5, LEFT OUTER join: outer JOIN, also known as left connection

--Display the query results, all of the left table, and the right table to meet the criteria of the data

SELECT *

From EMP left OUTER JOIN Dept

On (Emp.deptno = Dept.deptno)

--6, right outer join: Left OUTER join, also called Right join

--Displays the results of the query, all of the right table, and the data on the left table that match the criteria

SELECT *

From EMP right OUTER JOIN Dept

On (Emp.deptno = Dept.deptno)

--7, full outer join: All-out connection

--Two tables of data are displayed

SELECT *

From EMP full OUTER JOIN Dept

On (Emp.deptno = Dept.deptno)

--8, INNER join: Inner JOIN, two tables with matching data displayed

SELECT *

From EMP INNER JOIN Dept

On (Emp.deptno = Dept.deptno)

Primary Knowledge database (function)