Oracle User Login and connection query, special sort, over (), rank (), decode (), case when, union/union all

First, login issues

1, forget the user name password:

(1) Default User name password:

System/manager Sys/change_on_install Scott/tiger

(2) CMD log in as a system administrator:

C:\users\samsung>sqlplus System/manager as Sysdba

View all User:

Sql> select username from dba_users;

To modify a user password:

Sql> alter user Scott identified by Tiger;

To delete a user:

sql> drop user WX cascade;

To create a user:

Sql> Conn System/manager as Sysdba

Sql> create user wx identified by LOVEWX;

Sql> Grant Connect,resource to WX;

To change the logged in User:

Sql> Conn Scott/tiger

Second, connection query

(1) equal connection (with = specified connection condition)

Sql> Select Empno,ename,sal,comm,emp.deptno,dname from emp,dept where emp.deptno = Dept.deptno;

(2) Unequal connection

Sql> Select Empno,ename,sal,grade from Emp,salgrade where Sal between Losal and Hisal;

(3) Self-connected

Sql> Select E1.empno,e1.ename,e2.empno mgrno,e2.ename mgrname from emp e1,emp e2 where e1.mgr = E2.empno;

(4) Internal connection (returns all records that meet the criteria)

Sql> Select Dname,ename from emp,dept where Emp.deptno = Dept.deptno and dept.de

Ptno = ' 10 ';

Sql> Select Dname,ename from emp inner joins dept on emp.deptno = Dept.deptno and

Dept.deptno = ' 10 ';

Sql> Select Dname,ename from dept natural join EMP;

(5) External connection

Left Outer connection:

Sql> Select Dname,ename from dept left join EMP on dept.deptno = Emp.deptno and

Dept.deptno = ' 10 ';

When there is an employee not assigned to the department, the employee is the department, the department name.

Right outer connection:

Full outer joins:

Sql> Select Ename,dname from EMP full Join dept on emp.deptno = Dept.deptno;

Third, Over ()

Problem: Output the highest-paid personnel information for each department

(1) sql> SELECT * from emp E, (select Deptno,max (SAL) maxsal from EMP Group by Deptno) T where e.sal = T.maxsal and E.D Eptno = T.deptno;

(2) Sql> SELECT * from emp where (deptno,sal) in (select Deptno,max (SAL) from EMP GR

• OUP by DEPTNO);

(3) sql> Select Empno,ename,job,mgr,sal,comm,hiredate,deptno from (select Emp.*,max (SAL) over (partition by Deptno) Maxsal from emp) where Sal = Maxsal;

Four, Rank function

(1) Each department is sorted by salary:

Select Empno,deptno,sal,

Rank (partition by Deptno ORDER by Sal) rank,

Dense_rank () over (partition by Deptno ORDER by Sal) Denserank,

Row_number () over (partition by Deptno ORDER by Sal) Row_number

from EMP;

NULLS first/last Usage:

Sql> SELECT * from emp ORDER by Sal Nulls first;

Five, special situation sort:

Question: Check out all employees ' empno,ename,job, Mgr,mgrname,deptno,dname,sal,comm, and then sort by salary (Salrank) by department, sort by salary plus bonus for department (Salcommrank) .

(1) Select B.dname, A.*,

Rank () over (partition by A.deptno ORDER by NVL (a.sal,0) desc) Rk1,

Rank () over (partition by A.deptno order by (NVL (a.sal,0) +NVL (a.comm,0)) desc) Rk2 from

(select t1.*, t2.ename as mgrname from emp T1 LEFT join EMP

T2 on t1.mgr = t2. EMPNO) a LEFT JOIN dept b on a.deptno = B. deptno;

(2) Select Empno,t.ename,mgr,mgrname,t.deptno,dname,sal,comm,

Rank () over (partition by T.deptno ORDER by NVL (sal,0) desc) Salrank,

Rank () over (partition by T.deptno order by (NVL (sal,0) +NVL (comm,0)) desc) Salcommrank

From (select t1.*, T2.ename mgrname by emp T1 left join EMP t2 on t1.mgr = t2. empno) T

Left joins dept D on t.deptno = D.deptno;

Issue: Specify the first or last record row

1.Case When to use:

(1) Convert fields

Select emp.*,case when ename = ' KING ' Then ' BOSS ' Else ' EMPLOYEE ' end as role from EMP;

(2) Sort

A. Rank 7788 in the first place

SELECT * from emp order by case empno to 7788 then 1 else 2 end;

B. Put 7788 rows First, 7839 rows second ...

SELECT * from emp order by case Empno if 7788 then 1 when 7839 then 1 else 2 end;

2.Decode usage:

(1) Compare size: Compare employee wages above average or below average

Select E.*,decode (sign (sal-avgsal), 1, "High", 0, ' equal ', ' low ') Comparetoavg from (select Emp.*,avg (SAL) over () avgsal from EMP) e;

The sign () function returns 0, 1, 1, depending on whether a value is 0, positive, or negative.

(2) Sort by:

A. Rank 7788 in the first place

SELECT * FROM emp ORDER by decode (empno,7788,1,2);

B. Put 7788 rows First, 7839 rows second ...

SELECT * FROM emp ORDER by decode (empno,7788,1,7839,2,3);

(3) Sort by:

A. Rank 7788 in the first place

SELECT * from emp where empno= ' 7788 ' UNION ALL SELECT * from emp where empno<> ' 7788 ';

B. Put 7788 rows First, 7839 rows second ...

SELECT * from emp where empno= ' 7788 ' UNION ALL SELECT * FROM emp where empno = ' 7839 ' UNION ALL SELECT * from EMP where EMPN O<> ' 7788 ' and empno<> ' 7839 ';

Vi. union/union All usage:

(1) Union: Remove duplicate records and sort

SELECT * FROM EMP Union SELECT * FROM EMP;

SELECT * from emp where empno= ' 7788 ' union SELECT * from emp where empno<> ' 7788 ';

(2) UNION ALL: Keep duplicate records unordered

SELECT * FROM EMP UNION ALL SELECT * from EMP;

Seven, Rownum/rowid

1. RowNum: >,>=,=,between...and is not supported, only with the above symbol (<, <=,! =) RowNum is a pseudo-column added to the result set, that is, the result set is first found after the addition of a column (emphasis: first, the result set). Simply put, the rownum is the serial number that matches the conditional result. It always starts at 1. So your chosen result cannot be no more than 1, and there are other values greater than 1.

Also note:rownum cannot be prefixed with the name of any base table.

(1) Take the first five records

SELECT * from emp where rownum <= 5;

(2) Remove all records from the first five records.

SELECT * FROM (select EMP. *,rownum no from EMP) where no > 5;

(3) ranking (by Sal in each department)

Select E.*,rownum No from (SELECT * from emp where deptno= ' ORDER by NVL (sal,0)) E UNION ALL

Select E.*,rownum No from (SELECT * from emp where deptno= ' "ORDER by NVL (sal,0))" E UNION All

Select E.*,rownum No from (SELECT * from emp where deptno= ' "ORDER by NVL (sal,0))" E UNION All

Select E.*,rownum No from (SELECT * from emp where deptno are null ORDER by NVL (sal,0)) E

2. Rowid:rowid can be said to be physically present, indicating that the unique location ID recorded in the tablespace is unique in the db. As long as the record has not been moved, ROWID is unchanged.

Select rowid,emp.* from EMP;

Select emp.* from emp where rowid = ' aaaq+jaaeaaaaaeaai ';

Oracle User Login and connection query, special sort, over (), rank (), decode (), case when, union/union all