SQL base Statements-single-line functions, dual, numeric functions, date functions, table joins, set operations, grouped reports, single-row subqueries, multiline subqueries

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