Shulou(Shulou.com)06/01 Report--

This article is a follow-up to http://huangsir007.blog.51cto.com/6159353/1854818 's film.

About database language query:

SQL > show parameter nls_language

NAME TYPE VALUE

-

Nls_language string AMERICAN supports AMERICAN.

SQL > select * from nls_session_parameters where parameter='NLS_LANGUAGE'

PARAMETER

VALUE

NLS_LANGUAGE

AMERICAN

There is also a language of time:

SQL > show parameter nls_date_language

NAME TYPE VALUE

-

Nls_date_language string

SQL > select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE'

PARAMETER

VALUE

NLS_DATE_LANGUAGE

Otherwise, the code will be garbled.

Format query about time:

SQL > select * from nls_session_parameters where parameter='NLS_DATE_FORMAT'; this is the default time format

PARAMETER

VALUE

NLS_DATE_FORMAT

DD-MON-RR this is the default time format DD-MON-RR

If you can't find out the way of SQL > show parameter nls_date_format;, use the method above.

NAME TYPE VALUE

-

Nls_date_format string

View current time format

SQL > select * from student

XH XM SEX BIRTHDAY CLASSID

1? F 09-JUL-16 1

2 xiaobai F 06-SEP-16 2

3 xiaohua F 2

Then temporarily modify the time format of the current session (the current session session is modified, the change will be lost when turned off, and the yyyy-mm-dd format can be changed only after modification)

SQL > alter session set nls_date_format='yyyy-mm-dd'

Session altered.

Query again after modification, as follows

SQL > select * from student

XH XM SEX BIRTHDAY CLASSID

1? F 2016-07-09 1

2 xiaobai F 2016-09-06 2

3 xiaohua F 2

In this way, the error of the time format can be easily solved.

Inquire according to hiredate: entry time; date of employment; make judgment

SQL > select ename,HIREDATE from emp where HIREDATE > '1982-1-1-1 employees; employees after entering the job at this time

ENAME HIREDATE

--

SCOTT 1987-04-19

ADAMS 1987-05-23

MILLER 1982-01-23

Make a query according to a certain range of salary

SQL > select ename,sal from emp where sal > 2000 and sal select ename from emp where ename like's%'

ENAME

-

SMITH

SCOTT

Select the employee whose third letter is capital O (underscore to match any one,% match any one)

SQL > select ename from emp where ename like'_ O%'

ENAME

-

SCOTT

Keyword in

Query the employee number in a certain range

SQL > select empno,ename from emp where empno in (79007934)

EMPNO ENAME

--

7900 JAMES

7934 MILLER

Query in order with the keyword order by (default is ascending asc, descending order is desc)

SQL > select ename,sal from emp order by sal

ENAME SAL

--

SMITH 800

JAMES 950

ADAMS 1100

WARD 1250

MARTIN 1250

MILLER 1300

TURNER 1500

ALLEN 1600

CLARK 2450

BLAKE 2850

JONES 2975

SCOTT 3000

FORD 3000

KING 5000

Sort by employee's annual salary

SQL > select ename,sal*12 nianxin from emp order by nianxin (asc | desc); the role of aliases

ENAME NIANXIN

--

SMITH 9600

JAMES 11400

ADAMS 13200

WARD 15000

MARTIN 15000

MILLER 15600

TURNER 18000

ALLEN 19200

CLARK 29400

BLAKE 34200

JONES 35700

SCOTT 36000

FORD 36000

KING 60000

Descend the sal of the same department and ascend the department number

SQL > select ename,sal,deptno from emp order by sal desc,deptno

ENAME SAL DEPTNO

KING 5000 10

FORD 3000 20

SCOTT 3000 20

JONES 2975 20

BLAKE 2850 30

CLARK 2450 10

ALLEN 1600 30

TURNER 1500 30

MILLER 1300 10

WARD 1250 30

MARTIN 1250 30

ADAMS 1100 20

JAMES 950 30

SMITH 800 20

Query maximum, minimum, average, keyword is max,min,avg

SQL > select max (sal), min (sal), avg (sal) from emp

MAX (SAL) MIN (SAL) AVG (SAL)

5000 800 2073.21429

For employees who find out the maximum value of sal, first query the maximum value of sal, and then make the maximum value of sal=sal to make a judgment query.

SQL > select ename,sal from emp where sal= (select max (sal) from emp)

ENAME SAL

--

KING 5000

Find out the employees whose wages are higher than the average

1. Find out what the average salary is first.

SQL > select avg (sal) from emp

AVG (SAL)

-

2073.21429

2. Then query sal and compare it with the average salary

SQL > select ename,sal from emp where sal > (select avg (sal) from emp)

ENAME SAL

--

JONES 2975

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

FORD 3000

Group query the maximum sal and minimum sal of each department, and the keyword group by

SQL > select max (sal), min (sal), deptno from emp group by deptno

MAX (SAL) MIN (SAL) DEPTNO

2850 950 30

3000 800 20

5000 1300 10

Find out the department number whose maximum sal is greater than 3000, and make a judgment with the keyword having.

SQL > select max (sal), min (sal), deptno from emp group by deptno having max (sal) > 3000

MAX (SAL) MIN (SAL) DEPTNO

5000 1300 10

1. Grouping functions can only appear in selected columns, having, order by sentences.

2. If group by,having,order by is also included in the select statement, then their order is group by,having,order by

3. If there are columns, expressions, and grouping functions in the selected column, then one of these columns and expressions must appear in the group by sentence, otherwise an error will occur.

SQL > select deptno,avg (sal), max (sal) from emp group by deptno having avg (sal) > 2000 order by avg (sal)

DEPTNO AVG (SAL) MAX (SAL)

20 2175 3000

10 2916.66666 5000

Multi-table combined query

Query according to the same field deptno if not according to this condition is 14 records 4 records 56 records

ENAME DNAME DEPTNO

CLARK ACCOUNTING 10

KING ACCOUNTING 10

MILLER ACCOUNTING 10

JONES RESEARCH 20

FORD RESEARCH 20

ADAMS RESEARCH 20

SMITH RESEARCH 20

SCOTT RESEARCH 20

WARD SALES 30

TURNER SALES 30

ALLEN SALES 30

JAMES SALES 30

BLAKE SALES 30

MARTIN SALES 30

Find out the employee name and salary of department number 10, as well as the department (dname)

SQL > select e.enameree.enamered.dnamered.deptno from emp eRect d where e.deptno=d.deptno=10

Select e.ename,e.ename,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno=10

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

Such a combination of e.deptno=d.deptno=10 is not allowed, correct as follows:

SQL > select e.enameree.salre d.dname.deptno from emp eRect d where e.deptno=d.deptno and d.deptno=10

ENAME SAL DNAME DEPTNO

--

CLARK 2450 ACCOUNTING 10

KING 5000 ACCOUNTING 10

MILLER 1300 ACCOUNTING 10

Table salgrade is the salary scale, as follows:

SQL > select * from salgrade

GRADE LOSAL HISAL

Salary scale between 700,700 and 1200 in the 1200 range

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

Query the employee's name and salary, as well as the salary grade, keyword between and

SQL > select e.enameree.salrecovers.grade from emp eReport salgrade s where e.sal between s.losal and s.hisal

ENAME SAL GRADE

SMITH 800 1

JAMES 950 1

ADAMS 1100 1

WARD 1250 2

MARTIN 1250 2

MILLER 1300 2

TURNER 1500 3

ALLEN 1600 3

CLARK 2450 4

BLAKE 2850 4

JONES 2975 4

SCOTT 3000 4

FORD 3000 4

KING 5000 5

Multi-table query combined with order by keyword

Query employee name, salary, department number and department by department number

SQL > select e.enameree.salre d.dname.deptno from emp eRect d where e.deptno=d.deptno order by d.deptno

ENAME SAL DNAME DEPTNO

--

CLARK 2450 ACCOUNTING 10

KING 5000 ACCOUNTING 10

MILLER 1300 ACCOUNTING 10

JONES 2975 RESEARCH 20

FORD 3000 RESEARCH 20

ADAMS 1100 RESEARCH 20

SMITH 800 RESEARCH 20

SCOTT 3000 RESEARCH 20

WARD 1250 SALES 30

TURNER 1500 SALES 30

ALLEN 1600 SALES 30

JAMES 950 SALES 30

BLAKE 2850 SALES 30

MARTIN 1250 SALES 30

Find out each superior employee name of the employee in the emp table (different queries in one table)

SQL > select e.enameform.ename from emp eDivergent emp m where e.mgronomm.empno; the judgment condition is that the employee superior number is equal to the employee number, the left is the employee, and the right is the corresponding superior number of the employee.

ENAME ENAME

--

FORD JONES

SCOTT JONES

TURNER BLAKE

ALLEN BLAKE

WARD BLAKE

JAMES BLAKE

MARTIN BLAKE

MILLER CLARK

ADAMS SCOTT

BLAKE KING

JONES KING

CLARK KING

SMITH FORD

Find out the superiors of an employee

SQL > select e.enamedirection m.ename from emp eDirection emp m where e.mgr=m.empno and e.enamebooks

ENAME ENAME

--

WARD BLAKE

Subquery

Find out the employees in the same department as Smith, when the result is that when the line sentence, select the equal sign =

1. Find out the department number of the smith employee.

SQL > select deptno from emp where ename='SMITH'

DEPTNO

-

twenty

2. Then use the department number as the judgment condition to query the employees of the department number.

SQL > select ename,deptno from emp where deptno= (select deptno from emp where ename='SMITH')

ENAME DEPTNO

--

SMITH 20

JONES 20

SCOTT 20

ADAMS 20

FORD 20

Subquery

When the result is multiple lines, select in to match some of them

1. Find out all the positions with department number 10

SQL > select job from emp where deptno=10; Select the keyword distinct when there are duplicate lines

JOB

-

MANAGER

PRESIDENT

CLERK

SQL > select distinct job from emp where deptno=10

2. Then find out all the fields in all positions to meet the job

SQL > select * from emp where job in (select distinct job from emp where deptno=10)

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO

-

7782 CLARK MANAGER 7839 1981-06-09 2450

ten

7698 BLAKE MANAGER 7839 1981-05-01 2850

thirty

7566 JONES MANAGER 7839 1981-04-02 2975

twenty

EMPNO ENAME JOB MGR HIREDATE SAL COMM

Find out all the employees whose wages are higher than those in department 30. Keyword all

1. Find out the salary of the employee with the department number 30 first.

SQL > select sal,deptno from emp where deptno=30

SAL DEPTNO

--

1600 30

1250 30

1250 30

2850 30

1500 30

950 30

2. Then find the employees who are higher than the number of this department.

SQL > select ename,sal,deptno from emp where sal > all (select sal from emp where deptno=30)

ENAME SAL DEPTNO

JONES 2975 20

SCOTT 3000 20

FORD 3000 20

KING 5000 10

Of course, there is a second way to express it, as follows:

SQL > select ename,sal,deptno from emp where sal > (select max (sal) from emp where deptno=30)

ENAME SAL DEPTNO

JONES 2975 20

SCOTT 3000 20

KING 5000 10

FORD 3000 20

Find out any employee whose salary is higher than that of the employee in department 30 (the condition is higher than the minimum salary of employee 30).

Use the keyword any or min (sal)

Find out the employees with the same department number and the same position as smith

1. Find out the department number and position where Smith is located

SQL > select deptno,job from emp where ename='SMITH'

DEPTNO JOB

--

20 CLERK

2. Then inquire according to the request

SQL > select * from emp where (job,deptno) = (select deptno,job from emp where ename='SMITH')

Select * from emp where (job,deptno) = (select deptno,job from emp where ename='SMITH')

*

ERROR at line 1:

ORA-01722: the judgment condition of an invalid number query must correspond to the job,deptno corresponding to the following subquery.

SQL > select * from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH')

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO

-

7369 SMITH CLERK 7902 1980-12-17800

twenty

7876 ADAMS CLERK 7788 1987-05-23 1100

twenty

Find out the average salary of each department number and group it, and sort it according to the department number.

SQL > select deptno,avg (sal) from emp group by deptno

DEPTNO AVG (SAL)

--

30 1566.66667

20 2175

10 2916.66667

Find out the employees in their own department whose salary is higher than the average salary in their department.

The above has found out the average salary and department number as a table, and then combined with the two tables to query

SQL > select e.enameree.salre e.deptnoreb. Avg from emp e, (select deptno,avg (sal) avg from emp group by deptno) b where e.deptno=b.deptno and e.sal > b.avg

ENAME SAL DEPTNO AVG

--

ALLEN 1600 30 1566.66667

JONES 2975 20 2175

BLAKE 2850 30 1566.66667

SCOTT 3000 20 2175

KING 5000 10 2916.66667

FORD 3000 20 2175

Where e.deptno=b.deptno and e.sal > b.avg: query with two tables. The conditions must be accurate and the logic must be clear.

An embedded view is a subquery that is used as a table, giving the subquery an alias.

Aliases for tables cannot be added with as, columns can be added.

Paging query of oracle

1. The unique rownum paging rownum assigned to oracle

SQL > select e.calligraphy rownum rn from (select * from emp) e; rownum as a page, adding a field rn as the page line number, or replacing it with another value (xm), that is, the page line number

The same effect has been changed from rn to xm. Select E. from emp xm from (select * rownum) e

SQL > select e.calligraphy Rownum rn from (select * from emp) e

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO RN

--

7369 SMITH CLERK 7902 1980-12-17800

20 1

SQL > select e.calligraphy Rownum xm from (select ename,sal from emp) e

ENAME SAL XM

SMITH 800 1

ALLEN 1600 2

WARD 1250 3

JONES 2975 4

Rn and xm are the number of rows paged. Now you can allocate how many rows per page based on conditions, and do paging query.

SQL > select e. Makeshift Rownum xm from (select ename,sal from emp) e where xm select e.Cool xm from (select ename,sal from emp) e where rownum2

SP2-0734: unknown command beginning "WARD..."-rest of line ignored.

This is wrong, and the correct approach is as follows:

SQL > select * from (select. Xm from (select ename,sal from emp) e where rownum2

ENAME SAL XM

WARD 1250 3

Or the second is as follows:

Think of the following query results as another word list

SQL > select e.calligraphy Rownum xm from (select ename,sal from emp) e

ENAME SAL XM

SMITH 800 1

ALLEN 1600 2

WARD 1250 3

JONES 2975 4

MARTIN 1250 5

SQL > select * from (select e. Xm from xm from (select ename,sal from emp) e) where xm > 2 and xm select count (*) from emp

COUNT (*)

-

fourteen

Create a new table with the query results

SQL > create table myemp (id,ename,sal) as select empno,ename,sal from emp; is followed by the result of the query

Table created

SQL > desc myemp

Name Type Nullable Default Comments

--

ID NUMBER (4) Y

ENAME VARCHAR2 (10) Y

The new table created by SAL NUMBER (7) Y is exactly the same as the result of the query.

Merge query:

The operation symbol union,union all,intersect,minus that can be used (minus)

Union: get the union of two results and remove the same intersection of the query

SQL > select ename,sal,job from emp where sal > 2500

ENAME SAL JOB

JONES 2975.00 MANAGER

BLAKE 2850.00 MANAGER

SCOTT 3000.00 ANALYST

KING 5000.00 PRESIDENT

FORD 3000.00 ANALYST

SQL > select ename,sal,job from emp where job='MANAGER'

ENAME SAL JOB

JONES 2975.00 MANAGER

BLAKE 2850.00 MANAGER

CLARK 2450.00 MANAGER

Then union the two, merge them, and remove the intersection.

SQL > select ename,sal,job from emp where sal > 2500 union

2 select ename,sal,job from emp where job='MANAGER'

ENAME SAL JOB

BLAKE 2850.00 MANAGER

CLARK 2450.00 MANAGER

FORD 3000.00 ANALYST

JONES 2975.00 MANAGER

KING 5000.00 PRESIDENT

SCOTT 3000.00 ANALYST

Take the same part once

Union all does not cancel duplicate lines

SQL > select ename,sal,job from emp where sal > 2500 union all select ename,sal,job from emp where job='MANAGER'

ENAME SAL JOB

JONES 2975.00 MANAGER

BLAKE 2850.00 MANAGER

SCOTT 3000.00 ANALYST

KING 5000.00 PRESIDENT

FORD 3000.00 ANALYST

JONES 2975.00 MANAGER

BLAKE 2850.00 MANAGER

CLARK 2450.00 MANAGER

Directly is the sum of the two queries, without removing duplicate lines

Intersect take intersection

SQL > select ename,sal,job from emp where sal > 2500 intersect select ename,sal,job from emp where job='MANAGER'

ENAME SAL JOB

BLAKE 2850.00 MANAGER

JONES 2975.00 MANAGER

Minus takes the difference set, and the large set minus the small set (whose query comes first is subtracted from the collection of subsequent queries)

If the small one minus the big one is an empty set.

The collection operation is fast.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.