Important points and examples of select function and group multi table connection in Oracle

Key points of select function and group multi table connection in Oracle

function

– built in and custom functions

- single line function: a record returns a result of

– multiline function | group function | aggregate function: multiple records return one result

Single line function: a record returns a result of

Example:

--Single-Row Functions --current time select distinct sysdate from emp; select sysdate from dual; select current_date from dual; --Addition and subtraction time --2 After heaven select sysdate+2 from dual; --What's the date three days before all employees enter the company select hiredate,hiredate-3 from emp; --Query the 3-month probation period of all employees whose service period expires (the date of employment confirmation) select hiredate,add_months(hiredate,3) from emp;--add_month(Date object, number of months) --Query all employees for several months so far select hiredate,months_between(sysdate,hiredate) from emp;--months_between(Big month,Small month) --Query the last day of the current month select last_day(current_date) from dual;--last_day(Date object) --What is the next Wednesday (the coming Wednesday) select next_day(sysdate,'Wednesday') from dual;--next_day(current time,What day is next week) --to_date(data,format) --to_cahr(data,format) --Set a specific time (convert to date with a specific time string) --Set a time today'2018-9-5 16:18:25' select to_date('2018-9-5 16:18:25','yyyy-mm-dd hh24:mi:ss')+3 from dual; --String that converts a date to a specific format select to_char(sysdate,'yyyy"year"-mm"month"-dd"day" hh12:mi:ss') from dual; --Add a pseudo column after each department. If there are 10 departments, the pseudo column will be displayed as ten, twenty, thirty... --Decision function decode(Judgement field,Value 1,Result 1,Value 2,Result 2,...(,Default result)) select deptno,dname,loc,decode(deptno,10,'Ten',20,'Twenty',30,'Thirty','Forty') Chinese Department No from dept; --Pay all employees in department 20 10%,Display the employee's name, original salary, department number and salary after salary increase select ename,sal,deptno,decode(deptno,20,sal*1.1)After salary increase from emp; --10 Department salary increase 10%，20 Department salary increase 20%，30 Department pay cut 1%，40 Department salary tripled select ename,sal,(case deptno when 10 then sal*1.1 when 20 then sal*1.2 when 30 then sal*0.99 when 40 then sal*3 end)After salary increase from emp;

Multiline function | group function | aggregate function: multiple records return one result

– group function | aggregate function | multiline function: group function calculation on result set

– multiple rows return one result

– count sum (condition) max() min() avg()

– note: group functions cannot be used with ungrouped fields

Example:

--How many employees are there select count(empno) from emp; select count(*) from emp; select count(1) from emp;--Pseudo column is equivalent to adding a pseudo column field 1 after each data --There are several departments in the statistics select count(*) from dept; --Count the total number of departments with employees select count(distinct deptno) from emp; --How many people are there in department 20 select count(deptno) from emp where deptno=20; --Calculate how much the company will spend on salary every month select sum(sal) from emp; --Calculate the monthly wage expenses of 20 departments select sum(sal) from emp where deptno=20; --Query the company's maximum wage and minimum wage select max(sal),min(sal) from emp; --Query the maximum wage and minimum wage of 30 departments select max(sal),min(sal) from emp where deptno=30; --avg average wage select avg(sal) from emp; --Please find out the average wage of 20 departments select avg(sal) from emp where deptno=20; --Calculate the sum of all employees' bonuses select sum(comm) from emp where comm is not null; --Count the total number of employees null Do not participate in operation --Count the number of employees with bonus select count(comm) from emp; --Check the name of the highest paid employee and salary select max(sal) from emp; select ename from emp where sal in(select max(sal) from emp); --Query the number, name and salary of the employee whose salary is lower than the average salary select empno,ename,sal from emp where sal<(select avg(sal) from emp); --Query ratio SMITH Employee information of the same department with high salary select * from emp where (select sal from emp where ename='SMITH')<sal and (select deptno from emp where ename='SMITH')=deptno; --View the names of employees above the average salary of the Department select ename from emp e1 where sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno);

Grouping

– group: group by group field

– query formula: select data from data source where condition group by grouping field 1 having filter group information (filter by group) order by sort field ;

– execution process: from – where – group by – having – select
– order by

- note:

– 1. If a group function appears after select and is grouped, the group function cannot be used with non grouped fields, but with other group functions

– 2. The group function cannot be used after where because there is no group yet. The execution process is problematic

– 3. If grouping is used at one point, select can only be followed by grouping fields or group functions

Example:

--Find out the department number of all employees select deptno from emp group by deptno; --Find out the maximum wage of 20 departments and 30 departments select max(sal) from emp where deptno in(20,30); --Find the maximum wage for each of the 20 and 30 sectors select max(sal),deptno from emp where deptno in(20,30) group by deptno;--Filter before group select max(sal),deptno from emp group by deptno having deptno in(20,30);--Group before filter --Find out the average wage of each department select avg(sal),deptno from emp group by deptno; --Find out the average wage of each department with the wage higher than 1000 select avg(sal) from emp where sal>1000 group by deptno; --Find out the average wage of employees whose wages are higher than 1000 in departments 10 and 20 select avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20); --Not recommended, less efficient than above select * from (select avg(sal),deptno from emp where sal>1000 group by deptno) where deptno in(10,20); --Find out the maximum wage for each department select max(sal),deptno from emp group by deptno; --Find out the department number and average wage of each department whose average wage is higher than 2000 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; select * from (select deptno,avg(sal) avg1 from emp group by deptno) where avg1>2000;--Group function alias can be used --Query average salary by department position select avg(sal) from emp group by job; --Query the positions with average wage greater than 2000 by position select avg(sal) from emp group by job having avg(sal)>2000; --Query department number of minimum average wage select deptno from (select deptno,avg(sal) avg2 from emp group by deptno) where avg2=(select min(avg(sal)) from emp group by deptno); --Count the number of employees in each department select count(deptno) from emp group by deptno; --Query Department average wage and department number with average wage between 1500 and 2000 select deptno,avg(sal) from emp group by deptno having avg(sal) between 1500 and 2000; --Query employees whose salary is higher than the average salary of 20 departments select ename,sal from emp where sal>(select avg(sal) from emp where deptno=20); --No way where Not available in select After field alias, because of the execution process, go first from，Get to where No aliases yet --select ename name,empno from emp where name='SMITH'; error

Subquery and row to column

Row to column conversion can be achieved through the decode function
Example:

--Use a statement to query the names of students whose scores are greater than 80 in each course select name from tb_student group by name having count(distinct course) = (select count(distinct course) from tb_student) and min(score) > 80; --How many courses are there select count(distinct course) from tb_student; --Everyone's lowest score select min(score),name from tb_student group by name; --Row to column select name, min(decode(course, 'Chinese', score)) Chinese, max(decode(course, 'Mathematics', score)) Mathematics, min(decode(course, 'English?', score)) English? from tb_student group by name;

paging

Paging can be done through rownum

– rowid and rownum are pseudo columns

– rowid refers to the address (object address) of the data recorded when it is inserted into the database table, which is not actually the address, but the value obtained according to the address

If there is no primary key in a table and no non repeatable fields, there may be multiple identical data, which can not distinguish duplicate data, and can be differentiated according to rowid.

– select data from data source where line filter condition group by grouping field having group filter information order by sorting

– execution process: from – where – group by – having – select – order by

Use rownum in oracle because rownum rules can be followed. Control the serial number of rownum from 1 to + 1 each time for easy judgment

Example:

--rowid and rownum All are pseudo columns. --rowid It is understood that the address (object address) of the data recorded when it is inserted into the database table is not actually the address, but the value obtained according to the address --If there is no primary key in a table and no non repeatable fields, there may be multiple identical data, which can not distinguish duplicate data. rowid Distinguish --select data from data sources where Line filter condition group by Grouping field having Group filtering information order by sort --Execution process: from -- where -- group by -- having -- select -- order by select deptno,dname,loc,rowid from dept; select empno,ename,rowid from emp; --When there are multiple identical data in a table, duplicate data is removed and only one item is kept. /*insert into tb_student values(1,'Zhang San ',' Chinese ', 81); insert into tb_student values(2,'Zhang San ',' Mathematics', 75); insert into tb_student values(3,'Li Si ',' Chinese ', 81); insert into tb_student values(4,'Li Si ',' Mathematics', 90); insert into tb_student values(5,'Wang Wu ',' Chinese ', 81); insert into tb_student values(6,'Wang Wu ',' Mathematics', 100); insert into tb_student values(7,'Wang Wu ',' English ', 90);*/ select * from tb_student; --Find data to keep select id,name,course,score,min(rowid) from tb_student group by id,name,course,score; select * from tb_student where rowid in(select min(rowid) from tb_student group by id,name,course,score); --Find data to delete select * from tb_student where not rowid in(select min(rowid) from tb_student group by id,name,course,score); --Delete these duplicate data delete from tb_student where not rowid in(select min(rowid) from tb_student group by id,name,course,score); --rownum If there is one result set, there will be one result set rownum select The result set found starts from 1, each time+1 select deptno,dname,loc,rowid,rownum from dept; select empno,ename,deptno,sal,comm,mgr,rowid,rownum from emp; --Paging: in oracle Use in rownum because rownum Rule to follow, control rownum Serial number starts from 1, every time+1，Convenient judgement --query --Sort before confirm rownum select deptno,dname,rownum from dept order by deptno desc;--Sort by primary key field --First determine rownum Reordering select deptno,dname,rownum from dept order by loc;--Sort by non primary key field --Make sure to sort first and then confirm rownum，Nest a layer outside the result set select，this select Of rownum It must be from 1, according to this regular, determined rownum Just judge select deptno,dname,rownum from dept order by loc; select rownum,deptno,dname,num from (select deptno,dname,rownum num from dept order by loc); select empno,ename,sal,comm,rownum from(select empno,ename,sal,comm,rownum from emp order by sal desc); --Paging: --i The number of pages num How many are displayed per page --num=3 i=4 --For each page of data to be displayed rownum First rownum>=num*(i-1)+1 the last one: row<=num*i select * from (select empno, ename, sal, comm, rownum row2 from (select empno, ename, sal, comm, rownum from emp order by sal desc)) where row2 >= 3 * (1 - 1) + 1 and row2 <= 3 * 1; --Query the employees of departments 20 and 30, sort them in ascending order according to the salary, realize paging, query page 3 i Data, 2 per page num select ename,empno,sal,rownum from emp where deptno in(20,30) order by sal asc; select ename,empno,sal,rownum from (select ename,empno,sal,rownum from emp where deptno in(20,30) order by sal asc); select ename, empno, sal from (select ename, empno, sal, rownum n from (select ename, empno, sal, rownum from emp where deptno in (20, 30) order by sal asc)) where n >= 2*(3-1)+1 and n <= 2*3;

Table connection

– when the data to be queried comes from different tables, multi table associated query is required

- 92 syntax 99 syntax

– internal connection: only when the conditions are met can it be displayed; otherwise, it will not be displayed

– outer link: left outer link | left outer link | right outer link | right outer link
The main table is left join on the left of comma and right join on the right of comma

– main table: the data in the main table will be displayed whether the connection conditions are met or not

– query the employee information with the superior and the superior information of the employee

- 92 syntax select data from table 1, table 2, table 3 ;

Example:

--Cartesian product multiplication effect select * from emp,dept; --Equivalent connection select empno,ename,emp.deptno from emp,dept where emp.deptno=dept.deptno;--Note: when using a field with the same name, you need to indicate the source of the field --Non equivalent connection --Query employee information and salary grade of each employee select empno,ename,sal,grade from emp,salgrade where emp.sal between losal and hisal; --2500 What is the salary level select grade from salgrade where 2500 between losal and hisal; --Self join --Query the information of the employee with superior and the superior information of the employee select * from emp e1,emp e2 where e1.mgr=e2.empno; --Outer link: left outer link|Left join Right outer join|The right join main table is the left join on the left side of the comma, and the right join on the right side of the comma --Main table: the data in the main table will be displayed whether the connection conditions are met or not --Query the information of the employee with superior and the superior information of the employee select * from emp e1,emp e2 where e1.mgr=e2.empno(+);--Left connection, determined according to the location of the main table e1 select * from emp e2,emp e1 where e1.mgr=e2.empno(+) and e1.deptno in(10,20) order by e1.empno desc;--Right connection, according to the location of the main table e1 --Find out the employee name and department name of 30 departments select ename,dname,emp.deptno from emp,dept where emp.deptno=30 and emp.deptno=dept.deptno; --Find out the number of employees in all departments and the Department name select dname,count(empno) from emp,dept where emp.deptno(+)=dept.deptno group by dname; select dname,c Number from dept d,(select deptno,count(1) c from emp group by deptno) e where d.deptno=e.deptno; --First, find out the department number and number of departments without any department select deptno,count(1) from emp group by deptno; --The result set above is connected with the Department table select dname,nvl(c,0) Number from dept d,(select deptno,count(1) c from emp group by deptno) e where d.deptno=e.deptno(+); --Find out the Department name of all employees and the number of employees select dname,count(1) from emp,dept where emp.deptno=dept.deptno group by dname; --Query the information of all employees and their departments select * from emp,dept where emp.deptno=dept.deptno(+); --Query employee information, department information, salary grade, superior information select dname Department name, e1.ename Employee name, grade pay grade, e2.ename Name of superior from emp e1, emp e2, dept d, salgrade s where e1.deptno = d.deptno and e1.sal between losal and hisal and e1.mgr = e2.mgr;

• Give the thumbs-up 1
• Collection
• share
• • Article report