Java38: Database II (Oracle)

GROUP BY group

--Number of jobs per job from the EMP group by job;--How much work select COUNT (coutn) from the EMP group by job;--the maximum wage for each job select M Ax (SAL) from EMP Group by job;--minimum wage per work Select min (sal) from EMP Group by job;--per work average salary select AVG (SAL) from EMP Group B Y job;--The maximum average wage for all work select Max (SAL) from the EMP group by job;

Filter groups by condition using having

Select Job,count (Job) from EMP GROUP by Job have Count (job) >=3;

Where is used before group by

Select Job,count (Job) from EMP where sal>1000 GROUP by job has 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 value)

If VALUE1=IF1 returns THEN1

Otherwise, if Value1=if2 returns THEN2

The default return value is else

Select Decode (sal,1600, ' 1600 ', 5000, ' 5,000 ', ' other ') from emp;--according to the job's content, the number of people with the Department ID is 30 select job,count (Decode, 30,1,null)) Deptno from the EMP Group by (Job);--the number of people working in each department by sector clerk Select Deptno,count (Decode (Job, ' clerk ', 1,null)) Counts from EMP GROUP by (DEPTNO);--Calculates the average wage for each of the clerk in each department according to the departmental classification select DEPTNO,AVG (Decode (Job, ' clerk ', sal,null)) Clerk_ Avg_sal from EMP Group by (DEPTNO);

INSERT into EMP values (8000, ' LMDTX ', ' Clerk ', 8080,to_date (' 1980/09/09 ', ' yyyy/mm/dd '), 30000,null,null); EMP VALUES (8880, ' dsy ', ' Clerk ', 8980,to_date (' 1982/02/02 ', ' yyyy/mm/dd '), 3000,null,null);

Association of Tables

A foreign key has a primary key that corresponds to another table in a table

Connection table

1 • The Cartesian product emp Table deptno empty query not

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

2 • Internal connection The result is the same as the result of the Cartesian product condition.

INNER JOIN and join (omit inner) by default is inner join

Join...on...from EMP E INNER JOIN dept D on E.deptno=d.deptno

Two tables with the same name.

From EMP INNER JOIN dept using (DEPTNO)

SELECT * FROM EMP e inner joins dept D on E.deptno=d.deptno;select * from EMP INNER JOIN dept using (DEPTNO);

3 • External connection

1 • Left add-on (the data on the left table must be all, no place to fill 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, ' no department ') from EMP2 to outer join Dept2 using (DEPTNO);

2 · Right external

SELECT * FROM EMP right outer JOIN dept using (DEPTNO);--Query all departments for name and number of departments select Dname,count (empno) num from emp2 in descending order of number Join Dept2 Using (DEPTNO) group by dname ORDER by num DESC;

3 • Full Add-on (all available)

EMP Full OUTER JOIN Dept

SELECT * FROM EMP full outer JOIN dept using (DEPTNO);--Query the names of all departments and the number of departments without the Department of human beings into 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 (em pno), 0, ' Unassigned Hands ', Count (empno)) Number of people from EMP2 full outer join DEPT2 using (DEPTNO) group by dname Order by Dname;

4 • Self-connect

Entity table

Association tables

Dictionary table

This article is from the "Romantic Laugh" blog, make sure to keep this source http://lmdtx.blog.51cto.com/6942028/1835724

Java38: Database II (Oracle)