3. Single-line function
3.1 Conversion functions
Select ASCII (' A '), Chr (+) from dual;
Select To_char (1243123), 1231451 from dual;
On the left is the string, on the right is the number
Select To_char (123512a121) from dual; --wrong notation, no quotes for numbers, but numbers contain letters, illegal input values
Select To_number (' 123141211 ') from dual;
Select To_number (' 12314a1211 ') from dual; --Illegal input value
Select To_date (' 20140610121212 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;
Illegal input value:
Select To_date (' 20141310121212 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;
Select To_date (20140610121212, ' Yyyy-mm-dd hh24:mi:ss ') from dual; --Date unquoted, numeric type
Convert a date to a fixed format string
Select To_char (sysdate, ' Yyyy/mm/dd hh24:mi:ss ') from dual;
Select To_char (sysdate, ' YYYYMMDD ') from dual;
Select To_number (To_char (sysdate, ' YYYYMMDD ')) from dual;
3.2 Operation of the string
(1) Connection of strings
Select Concat (' 123124 ', ' ASDFDA ') from dual; --only two parameters can be passed, but | | Can be constantly splicing
Select ' 123124 ' | | ' ASDFDA ' from dual;
(2) Seeking length
Select Length (' Adsfasgsadfasdf ') from dual;
(3) Interception of strings
Select substr (' Asdfasdfasdfasdf ', 5,4) from dual;
4 byte length from left to right 5th bit
Select substr (' Asdfasdfasdfasdf ', -5,4) from dual;
4 byte length from right to left 5th bit
Select Length (substr (' asdfasdfasdfasdf ', -5,7)) from dual; --until the end of interception
(4) Finding the location of the substring
Select InStr (' Asdfasdfasdfasdf ', ' asdf ', 3,1) from dual;
Starting from left-to-right 3rd, the 1th occurrence of ' asdf ' index position
Select InStr (' Asdfasdfasdfasdf ', ' asdf ', 3,2) from dual;
Starting from left-to-right 3rd, the 2nd occurrence of ' ASDF ' index position
Select InStr (' Asdfasdfasdfasdf ', ' asdf ', -3,2) from dual; --9 (red for the first, * * * start for the second, that is, the fifth A is the second, and then the order from the right fourth start to count as 1, so that is the nineth bit);
Starting from right-to-left 3rd, the 2nd occurrence of the index position of ' asdf '
It means starting from the third position on the right, that is, starting from the fourth position on the right and starting from 1, and then the second occurrence of the word A is still from the left, that is, a S d f a S D f a, but the number is from the right.
9 8 7 6 5 4 3 2 1
Select InStr (' Asdfasdfasdfasdf ', ' asdf ', -3,1) from dual; --13
Starting from right-to-left 3rd, the 1th occurrence of the index position of ' asdf '
(5) Uppercase and lowercase conversions
Select Upper (' Asdfasgas '), lower (' Ghjajdgj ') from dual;
Select Lower (ename), lower (job) from EMP;
(6) Replacement
Select replace (' Asdfasdfasdfasdf ', ' asdf ', ' H ') from dual;
(7) Complement string
Select Lpad (' asdf ', ' a ', ' * '), Rpad (' asdf ', ', ' * ') from dual;
(8) Go to space
Select Trim (' SADF ') as A,ltrim (' asdf ') as B,rtrim (' asdf ') as C from dual;
(9) Go to the middle of the space
Select replace (' asdf SDF dfasdf sdf ', ' ', ') as a from dual;
' String ', delimiter ' match condition ' match result
Asdfsdfdfasdfsdf
###########################################################################################
4. Numeric functions
(1) 1-bit rounding after decimal point
Select Round (2.76,1) from dual;
(2) rounding an integer
Select Round (2.76) from dual;
(3) Rounding up, ceil means ceiling
Select Ceil (4.3) from dual;
(4) Rounding down, floor represents flooring
Select Floor (4.8) from dual;
(5) Seeking remainder
Select mod (10,3) from dual;
(6) Truncation
Select Trunc (10.454212348211,2), Trunc (21154.1454211,-2) from dual;
Note: The second argument is a positive number, truncating the two digits after the decimal point
The second argument is a negative number, truncating the two digits preceding the decimal point
##########################################################################################
5. Date function
(1) Truncation date
Select Trunc (to_date (' 20140610 ', ' yyyy-mm-dd '), ' DD '), Trunc (to_date (' 20140610 ', ' yyyy-mm-dd '), ' mm '),
Trunc (to_date (' 20140610 ', ' yyyy-mm-dd '), ' yyyy ') from dual;
Select Trunc (to_date (' 20140610121212 ', ' yyyy-mm-dd hh24:mi:ss '), ' hh '),
Trunc (to_date (' 20140610121212 ', ' yyyy-mm-dd hh24:mi:ss '), ' mi ') from dual;
(2) Number of days between two dates = two date subtraction
Select Sysdate-to_date (' 20130610095000 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;
(3) month before and after push
Select Add_months (sysdate,-12), Add_months (sysdate,3) from dual;
The second argument is a negative number, pushing forward
The second argument is a positive number and pushes backwards
(4) Count the number of months of two date intervals
Select Months_between (sysdate,to_date (' 20130610100000 ', ' yyyy-mm-dd hh24:mi:ss ')) from dual;
Example:
To calculate the distribution of the number of years in the employment period:
A: Within 1 years
B:1~3 year
C:3~5 year
D:5 years or more
Select Rs1,count (*) from
(select Empno, rs<=1 then ' BELOW 1 '
When Rs>1 and rs<3 then ' on '
When rs>=3 and rs<5 then ' the "
When Rs>=5 then ' over 5 ' end) as Rs1
From
Select Empno,round ((Months_between (sysdate,hiredate)/12)) as RS
From EMP
)
) Group by Rs1;
(5) Decode function
F=decode (P1,P2,P3,P4,P5...,PN)
Meaning: if p1 = P2,f=p3
If p1 = P4,f=p5
.....
If all is not satisfied, F=PN
Select Decode (deptno,10, ' dept10 ', ' dept20 ', ' x ', ' Dept30 ', ' dept40 ') as × from dept;
=
Select (case if deptno=10 then ' dept10 '
When deptno=20 and then ' dept20 '
When deptno=30 and then ' Dept30 '
Else ' DEPT40 ' end
) as X
From dept;
(6) NVL function
NVL (comm,9999)--If the first argument is null, the second argument is displayed
Example: When the first number is NULL, the value is 9999, otherwise it is equal to the original value
Select EMPNO,NVL (comm,9999) as X,decode (Comm,null,9999,comm) as Y, (case when Comm was null then 9999 else comm end) as Z F ROM emp;
###########################################################################################
6. Connection of tables
6.1 Equivalent connections
Select B.deptno,a.ename from dept B (Inner) join EMP A on (A.DEPTNO=B.DEPTNO)
The equivalent connection is actually the inner connection.
6.2 Left Connection
Select B.deptno,a.ename from dept B Left (outer) joins EMP A on (A.DEPTNO=B.DEPTNO);
Dept--Main Table
EMP--sub-table
Writing method: Main Table left join secondary table, ensure that the main table data is not lost
6.3 Right Connection
Select B.deptno,a.ename from EMP a right joins Dept B on (A.DEPTNO=B.DEPTNO);
Dept--Main Table
EMP--sub-table
Writing Method: Secondary table Right join Main table, ensure that the data of the main table is not lost
Oracle syntax: Unify the left and right connections
Select B.deptno,a.ename from emp A, dept b where A.deptno (+) =b.deptno;
Select B.deptno,a.ename from emp A, dept b where B.deptno=a.deptno (+);
The table opposite (+) is the main table
Dept table is the main table
The EMP table is a sub-table
Attention:
A: How to meet the effects of the left and right connections if there are filter conditions on the secondary table
Select B.deptno,a.ename from dept B left JOIN EMP A on (A.DEPTNO=B.DEPTNO) where a.sal>1000;
In order to add the filter condition, the data of the main table is lost, the meaning of the left connection is lost at this time
Select B.deptno,a.ename from dept B left JOIN EMP A on (A.deptno=b.deptno and a.sal>1000);
=
Select B.deptno,a.ename from Dept b,emp a where A.deptno (+) =b.deptno and a.sal (+) >1000;
B: There is a filter on the main table, this problem does not exist
Mathematical principles:
Left join: A∪ (A∩B)
Equivalent connection: A∩B
6.4 Multi-Table Association
SELECT * from A,b,c,d where a.x=b.x and B.y=c.y and c.z=d.z;
Special connections: Cartesian product
SELECT * from a a B
6.5 Non-equivalent connections
Select E.ename,s.grade,e.sal
From EMP E,salgrade s
where e.sal between S.losal and S.hisal;
Salgrade: Salary Scale
6.6 Self-connect
Query the correspondence of the direct boss of all employees, expressed as "Employee name work for Boss name", generally used on the table with hierarchical relationship
Select Worker.ename | | ' work for ' | | Manager.ename
From EMP worker,emp Manager
where Worker.mgr=manager.empno;
##########################################################################################
7 Integrated operations
7.1 Finding the difference set
A. Not exists operation and not in operation
Select B.deptno,b.dname,b.loc from Dept b
Where NOT EXISTS (select A.deptno from emp a where a.deptno=b.deptno);
=
Select B.deptno,b.dname,b.loc from Dept b
where B.deptno not in (select A.deptno from emp a where a.deptno=b.deptno);
B. Minus operation
Select B.deptno from Dept b
Minus
Select A.deptno from EMP A;
7.2 Finding the intersection
A exists operation and in operation
Select B.deptno,b.dname,b.loc from Dept b
where exists (select A.deptno from emp a where a.deptno=b.deptno);
=
Select B.deptno,b.dname,b.loc from Dept b
where B.deptno in (select A.deptno from emp a where a.deptno=b.deptno);
B equivalent Connection
SELECT DISTINCT b.deptno,b.dname,b.loc from emp a,dept b where a.deptno=b.deptno;
7.3 Seeking and gathering
A around connection (where one collection is a subset of another set)
Select B.deptno,a.ename from dept B left JOIN EMP A on (A.DEPTNO=B.DEPTNO);
B Union operation
Select B.deptno from Dept B--After merging to remove weight
Union
Select A.deptno from EMP A;
Select B.deptno from Dept B--No weight after merging
UNION ALL
Select A.deptno from EMP A;
7.4 The Complete Collection
SELECT * from EMP;
7.5 Seeking a subset
SELECT * from emp where ename like ' t% ';
7.6 Seeking complement set
SELECT * from emp where ename ' t% ';
7.7 Seeking Mappings
Select Ename,job from emp where ename like ' t% ';
While manipulating the Ename collection, query the value of the job field mappings at the same time
###########################################################################################
8. Grouped reports
Select B.DEPTNO,B.DNAME,NVL (Floor (AVG (a.sal)), 0) as X,NVL (sum (a.sal), 0) as Y,NVL (min (a.sal), 0) as Z,NVL (Max (a.sal), 0) As L
From EMP a,dept b
where A.deptno (+) =b.deptno
GROUP BY B.deptno,b.dname
Order BY B.deptno,b.dname;
Having a clause--filtering the result of an aggregate function
Select B.DEPTNO,B.DNAME,NVL (Floor (AVG (a.sal)), 0) as X,NVL (sum (a.sal), 0) as Y,NVL (min (a.sal), 0) as Z,NVL (Max (a.sal), 0) As L
From EMP a,dept b
where A.deptno (+) =b.deptno
GROUP BY B.deptno,b.dname
Having avg (a.sal) >2000
Order BY B.deptno,b.dname;
NOTE: The HAVING clause must be placed in front of order by, after group by
##########################################################################################
9. Single-line subquery with multiple rows of subqueries
(1) Single-line subquery: Returns a row of subqueries
Requirement: Displays Scott employee's name, salary, department number and the same information as the person in the department
Select Ename,sal,deptno from emp
where deptno= (select Deptno from emp where ename= ' SCOTT ')
=
Select Ename,sal,deptno from emp
where Deptno in (select Deptno from emp where ename= ' SCOTT ')
(select Deptno from emp where ename= ' SCOTT ') returns a single row, called a single-row subquery. =, in is called a single-line operator
(2) Multiline subquery: A subquery that returns multiple rows
Requirements: Display department 10 all job job in the company corresponding to the same position of the name, salary, department number
Select Ename,sal,deptno from emp
where job in (the Select Job from EMP where deptno=10)
In here is used as a multiline operator
Select Ename,sal,deptno from emp
where job = (select Job from emp where deptno=10)
Wrong notation, = is a single-line operator
A use the any operator in a multiline subquery
Requirements: Displays the name, salary, department number of the employee whose salary is higher than the department's 10 employees
Select Ename,sal,deptno from emp
where Sal > any (select Sal from emp where deptno=10)
> is a single-line operator, and the subquery is multi-row, with any to resolve the syntax conflict, to meet the ' any ' demand
B using the all operator in multiline subqueries
Requirement: Shows the employee's name, salary, department number, and salary of all employees above the department 20
Select Ename,sal,deptno from emp where sal > All (select Sal from emp where deptno=20)
> is a single-line operator, and the subquery is multiline, with all to resolve the syntax conflict, to meet the ' all ' requirement
Attention:
1. Cannot write order by in subquery
2. Multiline subqueries cannot be matched by single-line operators
3. Single-line subqueries can match multiple line operators
4. Subqueries must be written in parentheses
5. Sub-query to be placed after the comparison operator
10. Nested subqueries (multiple subqueries)
Select Ename,sal,deptno from emp
Where Sal > (select sal from EMP where empno=7876) and job = (select Job from EMP where empno=7369);
SQL base Statements-single-line functions, dual, numeric functions, date functions, table joins, set operations, grouped reports, single-row subqueries, multiline subqueries