Tian Yi 05 -- Summary of Han shunping's Oracle video (01)

Desc table name. (You can view the detailed information of the table)

Showuser; (view the current user)

Modify Field name: alter table [Table name] rename column old field name to new field name;

1) Select B. ID, B. Title, B. ndate, A. manag, ename from bbs B, addmanage awhere B. ID = A. Aid

And a. managename = 'tianyi ';

 

2)Self-join (self-join refers to the connection query in the same table. The self-join query can make one table into two tables for query)

Display the name of the superior leadership of an employee, for example, who is the superior leader of 'Ford:

Select worker. ename, boss. ename from EMP worker, empboss where worker. Mgr = boss. empno and

Worker. ename = 'Ford ';

3)Subquery (subquery refers to the SELECT statement embedded in other SQL statements, also called nested query)

 

Single-row subquery (single-row subquery refers to a subquery statement that returns only one row of data)

How to display Smith new employees in the same department: Select * from EMP wheredeptno=(Select deptno

From EMP where ename = 'Smith ');

Multi-row subquery (multi-row subquery refers to a subquery that returns multi-row data)

How to query the names, positions, salaries, and department numbers of employees with the same job as department 10:

Select * from EMP where jobIn(SelectDistinctJob from EMP where deptno = 10 );

(The distinct keyword indicates that no duplicate data exists in the queried data)

Use the all operator in multi-row subqueries

How to display the name, salary, and department number of an employee whose salary is higher than that of all employees in the department 30:

Select ename, Sal, deptno from EMP where SAL> All (select Sal from empwhere

Deptno = 30 );

Another method:

Select ename, Sal, deptno from EMP where SAL> (select max (SAL) from empwhere

Deptno = 30 );

Use the any operator in multi-row subqueries

How to display the name, salary, and department number of an employee whose salary is higher than that of any employee of Department 30:

Select ename, Sal, deptno from EMP where SAL> Any (select Sal from empwhere

Deptno = 30 );

Another method:

Select ename, Sal, deptno from EMP where SAL> (select Min (SAL) from empwhere

Deptno = 30 );

Whether it is a single-row subquery or multiple-row subquery, The subquery returns only one column rather than multiple columns.

 

Multi-column subquery:

Single-row subquery refers to a subquery that returns only single-column and single-row data.

A multi-row subquery returns multiple rows of data in a single column.

WhileMulti-column subquery refers to the subquery statement that queries and returns data of multiple columns.

How to query all employees who have the same department and position as Smith:

Select * from EMP where (Deptno, job) = (SelectDeptno, jobFrom EMP where

Ename = 'Smith ');(Deptno, must be consistent before and after the job)

 

 

Use subquery in the from clause

How to display aboveOwn departmentAverage wage information of employees:

Step 1: first query the average salary and department number of each department:

Select deptno, AVG (SAL) mysal from EMP group by deptno;

Step 2: view the result value of the preceding query as a sub-table.

Selecta2.ename, a2.sal, a2.deptno, a1.mysal from EMP A2, (select deptno, AVG (SAL) mysalfrom EMP group by deptno) A1 where a2.deptno = a1.deptno and a2.sal> a1.mysal;

 

 

Paging Query

Oracle paging has three methods: 1. Dividing by rowid 2. Dividing by analysis function 3. Dividing by rownum (the first is the fastest, the third is the second, and the second is the slowest)

1. rownum paging:

1) show all data:

Select A1. *, rownum rn from (select * from EMP) A1;

2) only the first 10 rows of data are displayed:

Select A1. *, rownum rn from (select * from EMP) A1 whererownum <= 10;

3) only 6 to 10 rows of data are queried:

Select * from (select A1. *, rownum rn from (select * from EMP) A1 whererownum <= 10) A2 where s2.rn> = 6;

4) Only the names and salaries of employees from 6 to 10 rows can be queried: (in this case, you only need to change the bottom-layer conditions, that is, the preceding statement: (select * from EMP ))

Select * from (select A1. *, rownum rn from (select ename, Sal from EMP) a1where rownum <= 10) A2 where s2.rn> = 6; or:

(Select * from (select a. Aid, A. managename, rownum rn from addmanage awhere rownum <= 10) A1 where a1.rn> = 6)

Only the names and salaries of employees from 6 to 10 rows are queried and sorted by salary from low to high: (all changes need to be changed at the bottom: (select ename, Sal from EMP)

Select * from (select A1. *, rownum rn from (select ename, Sal from EMP order by SAL) a1where rownum <= 10) A2 where s2.rn> = 6;

 

Create a new table with query results

This command is a quick table creation method:

Create tablemytable (ID, name, Sal, job, deptno)AsSelect empno, ename, Sal, job, deptno from EMP;

 

 

Use a subquery to insert data (the three field attributes in the newly created table KKK must be consistent with the attributes of the queried field; otherwise, an error is returned)

Insert into KKK (myid, myname, mydept) select aid, managename, managepass from addmanage whereaid = 23;

Use the subquery to modify data (the field attributes must also be consistent)

We hope that Scott's position, salary, and subsidy will be the same as Smith's:

Update empset (job, Sal, comn) = (select job, Sal, comn from EMP where ename = 'Smith ') whereename = 'Scott ';