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

Group by grouping

-- number of people per job select count (job) from emp group by job;-- how many jobs are there select count (coutn (job)) from emp group by job;-- maximum wage per job select max (sal) from emp group by job;-- minimum wage per job select min (sal) from emp group by job;-- average wage per job select avg (sal) from emp group by job -- select max (avg (sal)) from emp group by job, the largest average salary of all jobs

Use having to filter groups based on conditions

Select job,count (job) from emp group by job having count (job) > = 3

Where was used before group by

Select job,count (job) from emp where sal > 1000 group by job having count (job) > = 3

Decode function

Decode (value1 if1 then1 if2 then2... Else)

Decode (condition, value 1, return value 1, value 2, return value 2. Value n, return value n, default)

If value1=if1 returns then1

Otherwise, if value1=if2 returns then2

The default return value is else

Select decode (sal,1600,' 1600', 5000 select job,count', 'other') from emp;-- statistics according to the content classification of job, the number of departments whose ID is 30 select job,count (decode (DEPTNO,30,1,null)) deptno from emp group by (job);-- the number of people working as CLERK in each department select deptno,count (decode (job,'CLERK',1,null)) counts from emp group by (deptno) by department classification -- the average salary of each job in each department is calculated according to department classification: select deptno,avg (decode (job,'CLERK',sal,null)) CLERK_AVG_SAL from emp group by (deptno); insert into emp values (8000 select deptno,avg (job,'CLERK',sal,null)) CLERK_AVG_SAL from emp group by (deptno); insert into emp values (8000 LMdtx), "CLERK", 80th, 80th, 1980s, 80th, 80th, 80th, 80th, 80th, 80th, 80th, 80th, 20th, 20th, 20th, 8th, 2nd, 2nd, 2nd, 2nd, 80, 80th, 80th and 80th Insert into emp values (8880 heroic calendar pencils CLERKLERKLERKLERKLERKLERKY 8980pr totaldate ('1982Accord02Accord02and02companyyyyqqdd`), 3000Perry nullpentry null)

Association of tables

The foreign key stores the primary key corresponding to another table in one table.

Connection table

1 queries with empty deptno in the emp table of Cartesian product can not be found.

-- all rows in the emp table * all rows in dept select * from emp,dept;-- need to write filter conditions to filter out unwanted select * from emp,dept where emp.deptno=dept.deptno;select * from emp e.dept d where e.deptno=d.deptno

The result of the connection within 2 is the same as that of Cartesian product with conditions.

Inner join is the same as join (omitting inner). Default is internal connection.

Join...on...from emp e inner join dept d on e.deptno=d.deptno

The name of one foreign key of two tables is the same as the name of the primary key of the other table

From emp inner join dept using (deptno) select * from emp e inner join dept d on e.deptnoxid.deptnowitte select * from emp inner join dept using (deptno)

3 external connection

1 left outer connection (the data in the left table must be all available, where there is no place to supplement null)

From emp left outer join dept using (deptno) select * from emp left outer join dept on emp.deptno=dept.deptno;select ename,nvl (dname,' has no department') from emp2 left outer join dept2 using (deptno)

2 right outer connection

Select * from emp right outer join dept using (deptno);-- query the names and numbers of all departments in descending order of select dname,count (empno) num from emp2 right join dept2 Using (deptno) group by dname order by num desc

3 all external connections (all)

Emp full outer join deptselect * from emp full outer join dept using (deptno);-- query the names of all departments and the number of departments personified as unassigned select nvl (dname,' unassigned'), count (empno) from emp2 full outer join dept2 using (deptno) group by dname order by dname Select nvl (dname,' unassigned department') department name, decode (count (empno), 0je 'unallocated staff', count (empno)) number of from emp2 full outer join dept2 using (deptno) group by dname order by dname

4 self-connecting

Select e1.ename from emp2 e1 inner join emp2 e2 on e1.mgrachee2.empnowitte select e2.ename from emp2 e1 left join emp2 e2 on e1.mgrenameselect nvl (e1.ename on e2 on e1.mgrename e2 on e1.mgr=e2.empno group by e2.ename), from emp2 e1 right join emp2 e2 on e1.mgr=e2.empno group by e2.ename

Subquery

Used in the where clause, single-row and single-column select e1.ename from emp2 E1 inner join emp2 e2 on e1.mgr=e2.empno where e2.enameplates selecting KINGANTH select distinct dname from emp2 e1 join dept2 d2 ON e1.deptno = d2.deptno where e1.ename like'% S% select dname from dept2 where deptno in (select deptno from emp2 where ename like'% S%')

Any one of ANY ()

ALL () all

Select ename,job,sal from emp2 where sal > all (select DISTINCT sal from emp2 where job='MANAGER') and job'MANAGER';select ename job,sal from emp2 where sal > any (select distinct sal from emp2 where job='CLERK') and job'CLERK'

Exists is used to determine whether the subsequent subquery has rows, true or false.

Select ename from emp2 E1 where EXISTS (select 1 from emp2 e2 where e2.mgr=e1.empno); select ename from emp2 E1 where not exists (select 1 from emp2 e2 where e2.mgr=e1.empno)

3 in (1pm 2pm 3) true

3 in (1 in 2 null) false

5 not in (1pm 2pm 3) true

5 not in (1 not in 2 null) false

5 in (1 in 2 null) false

5 not in (null) false

5in (null) false

Null values cannot appear in subqueries of not in

Multi-column query in where

Select e.enamein e.sal, d.deptnored.dname from emp2 e inner join dept2 d on e.deptno = d.deptno where (e.deptnoree.sal) in (select deptno,min (sal) from emp2 where deptno is not null group by deptno); select * from emp2 where (deptno,sal) in (select deptno,min (sal) from emp2 where deptno is not null group by deptno); select deptno,min (sal) from emp2 where deptno is not null group by deptno

Used in having

From emp where deptno is not null group by deptno having avg (sal)

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.