[Oracle] Table query

View the table structure:
Desc table name;

Cancel duplicate columns in distinct.

Set timing on; show operation time

Insert into users (userid, username, userpass) Select * from users;
Insert into student (XH, XM, sex, birthday, Sal, classid) Select * from student; Copy Original data

Case Sensitive Content

The arithmetic expression can be used directly. If a null Column exists in the operation, the final result is null.

Nvl (Comm, 0); if it is null, It is 0.

Wildcard
%: 0-n characters
_: Any single character

Select * from EMP where empno in (123,456,789); In is highly efficient.

Select max (SAL), min (SAL) from EMP;

Select * from EMP where sal = (select max (SAL) from EMP); displays the information of the person with the highest salary

Select AVG (SAL), deptno from EMP group by deptno; displays the average salary of each department

Select AVG (SAL), deptno from EMP group by deptno having AVG (SAL)> 2000;
Grouping functions can only appear after selecting columns, having, and order by, but cannot appear after where.

The Cartesian set must be excluded from multi-table queries. The condition number must be no less than the number of tables-1, and the result may be correct;

Select * from Dept, EMP where Dept. deptno = EMP. deptno and EMP. deptno = 10;
Select * From salgrade, EMP where EMP. Sal between salgrade. losal and salgrade. hisal;

Self-connection:
Select * from emp B, emp a where a. empno = B. Mgr;

The query is executed from right to left.

Subquery

Single Row subquery: only one data subquery is returned.
How do I display employees in the same department as Smith?
Select * from EMP where deptno = (select deptno from EMP where ename = 'Smith ');

Multi-row subquery:
How do I query the information of employees who work in the same way as department 10?
Select * from EMP where job in (select job from EMP where deptno = 10 );

How do I display information about employees with higher salaries than all employees in the department 30?
All
Select * from EMP where SAL> All (select Sal from EMP where deptno = 30 );
Select * from EMP where SAL> (select max (SAL) from EMP where deptno = 30); this article is highly efficient.

How can I display the information of an employee whose salary is higher than that of any employee of Department 30?
Any
Select * from EMP where SAL> Any (select Sal from EMP where deptno = 30 );
Select * from EMP where SAL> (select Min (SAL) from EMP where deptno = 30 );

Multi-column subquery:
How do I query all employees who have the same department and position as Smith?
Select * from EMP where job = (select job from EMP where ename = 'Smith ') and deptno = (select deptno from EMP where ename = 'Smith ');
Select * from EMP where (job, deptno) = (select job, deptno from EMP where ename = 'Smith '); multi-column subquery

How do I query the information of employees who are higher than the average salary of my own department?
Select * from emp a, (select deptno, AVG (SAL) mysal from EMP group by deptno) B where. deptno = B. deptno and Sal> mysal; you cannot add the

Paging Query

1. rownum page: rownum is the row number allocated by Oracle.
Select * from (select a. *, rownum rn from (select * from EMP) A where rownum <= 10) Where rn> = 6;
Select * from (select a. *, rownum rn from (select * from EMP) A where rownum <= 10) B where B. Rn> = 6;
For example, to specify the number of columns and sorting, you only need to modify the subquery at the lowest layer.

2. rowid -- the highest execution efficiency
Select * from EMP where rowid in (select rid from (select rownum RN, rid from (select rowid RID, Sal from EMP order by Sal DESC) Where rownum <10) where rn> 5) order by Sal DESC;

3. query by analysis function-the lowest efficiency
Select * from (select T. *, row_number () over (order by Sal DESC) rk from EMP t) Where rk <10 and rk> 5;

Create a new table with query results

Create Table mytable (ename, Sal) as select ename, Sal from EMP;

Merge Query

Union: the number of columns must be consistent. Duplicate rows are canceled.
This operator is used to obtain the union of two result sets. When this operator is used, repeated rows in the result set are automatically removed.
Select * from EMP where SAL> 2000 Union select * from EMP where job = 'manager ';
Select * from EMP where SAL> 2000 or job = 'manager ';

Union all: do not cancel duplicate rows
Intersect: intersection
Minus: returns the result set that exists in the first result and does not exist in the second result.