Oracle 고급 쿼리에 대해 이야기해 보겠습니다(자세한 예).

이 기사는 그룹 쿼리, 다중 테이블 쿼리 및 하위 쿼리를 포함한 Oracle 고급 쿼리에 대한 관련 지식을 제공하는 것이 모든 사람에게 도움이 되기를 바랍니다.

Oracle Advanced Query

고급 쿼리는 데이터베이스 개발 프로세스에서 널리 사용됩니다. Oracle의 고급 쿼리는 그룹 쿼리, 다중 테이블 쿼리 및 하위 쿼리의 세 가지 측면에서 도입됩니다.

그룹 쿼리

그룹 쿼리는 특정 규칙에 따라 그룹화하는 것입니다. 그룹화한 후에는 집계 함수를 사용해야 하지만, 그룹화의 키워드는 그룹화입니다. 에 의해.

일반적으로 사용되는 집계 함수에는 maximum max(), 최소 min(),average avg(), sum(), count()가 있습니다.

count 함수는 열 이름을 사용할 때 자동으로 null 값을 무시합니다.

nvl 함수는 count가 빈 값을 자동으로 무시하는 것을 방지할 수 있습니다. 해당 기능은 comm이 비어 있을 때 0을 반환하는 것입니다. 0은 비어 있지 않기 때문에 전체 통계에 입력됩니다.

group by subquery

집계 함수에 포함되지 않은 선택 목록의 모든 열은 group by 절에 포함되어야 합니다.

단일 열 그룹화

각 부서의 평균 급여를 구하고 부서 번호와 부서의 평균 급여를 표시합니다.

select deptno,avg(sal) from emp group by deptno order by deptno

다중 컬럼 그룹화

부서별, 직급별 직원 총 급여 통계

select detpno,job,sum(sal) from emp group by deptno,job order by deptno

필터 그룹화

had 절 사용법

where와 had의 차이

• 사용할 수 없습니다. where 절 집계 함수를 사용하고 먼저 필터링한 다음 그룹화
• had 절에서 집계 함수를 사용하고 먼저 그룹화한 다음 필터링할 수 있습니다

참고: SQL 최적화 관점에서 where를 사용하세요. 왜냐하면 where를 사용하세요. 그룹화된 레코드 수를 크게 줄여 효율성을 향상시킵니다.

평균연봉이 2000이상인 부서를 찾아보세요

select deptno,avg(sal) from emp where(avg(sal)) > 2000 group by deptno

where절에서는 집계함수를 사용할 수 없으므로, 오류를 보고할 경우에는 has xxx절로 변경하면 됩니다.

select deptno,avg(sal) from emp group by deptno having(avg(sal)) > 2000

그룹 쿼리에서 order by

를 사용하여 각 부서의 평균 급여를 구하고, 부서 번호와 해당 부서의 평균 급여를 표시하고, 급여 오름차순으로 정렬합니다.

select deptno,avg(sal) from emp group by deptno order by avg(sal)

열의 별칭으로 정렬할 수도 있습니다.

select deptno,avg(sal) avgsal from emp group by deptno order by avgsal

열의 일련번호로 정렬할 수도 있습니다. 평균 급여는 두 번째 열입니다.

select deptno,avg(sal) from emp group by deptno order by 2

내림차순으로 정렬하고 desc를 추가합니다.

select deptno,avg(sal) from emp group by deptno order by 2 desc

중첩

부서 평균연봉 최대값 요청

select max(avg(sal)) from emp group by deptno

명세서별 그룹화 기능

은 주로 명세서별 그룹화 기능으로 사용됩니다

부서마다 서로 다른 직위를 설치하고 총 급여를 구하고, 부서 요약 및 요약.

롤업 기능

select deptno,job,sum(sal) from emp group by rollup(deptno,job)

을 사용하고 표시 형식을 설정할 수 있습니다. deptno에서 break는 동일한 부서 번호 중 하나만 표시된다는 의미이고, 건너뛰기 1은 서로 다른 부서 번호 사이에 빈 줄이 있음을 의미합니다. 부서 번호.

보고서 표시 개선

제목, 페이지 번호 등 추가

ttitle col 15 'My Report' col 35 sql.pno

제목을 설정하고 15열을 비워서 내 보고서를 표시한 다음 표시할 빈 35개 열 페이지 번호

col deptno 제목 부서 번호

col 직업 제목 위치

col sum(sal) 제목 총 급여

위 3줄에 열 머리글 설정

break on deptno 건너뛰기 1

표시 형식 설정, 동일한 부서 번호만 표시, 서로 다른 부서 번호 사이에 빈 줄 포함

이 설정을 SQL 파일에 저장하세요(ANSI 인코딩으로 변경해야 합니다. 그렇지 않으면 잘못된 문자가 나타나고 설정이 변경됩니다). 유효하지 않음), get 명령을 통해 읽고 실행합니다. 쿼리문을 다시 실행하여 다음 보고서를 얻습니다. 여러 페이지가 있는 경우 보기 좋게 표시하기 위해 한 페이지에 더 많은 행을 표시하도록 설정할 수 있습니다. 예를 들어 각 페이지에 100개의 행을 표시하도록 설정합니다. 페이지 크기 100을 설정합니다. 테이블에서 데이터 쿼리부터 시작하겠습니다. 테이블.

데카르트 집합을 피하기 위해 유효한 연결 조건을 어디에 추가할 수 있는지 실제 상황에서는 데카르트 완전 집합의 사용을 피해야 합니다.

等值连接

实例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称

需要查询员工表和部门表，通过部门号进行等值连接查询，where xxx=xxx

select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno

로그인 후 복사

不等值连接

示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水级别

需要查询员工表和薪水等级表，通过薪水等级上下限进行不等值连接查询。where xxx between xxx and xxx，注意：小值在between前面，大值在between后面

select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal

로그인 후 복사

外连接

示例：按部门统计员工人数，要求显示：部门号，部门名称，人数

需要查询部门表和员工表

以下是通过等值连接的方式查询，虽然总人数没有问题，但是少了一个部门，因为一个部门没有员工。

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname

로그인 후 복사

外连接一般通过join来实现，一张图看懂SQL的各种join用法。

使用join语句重新实现示例功能

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname

로그인 후 복사

自连接

示例：查询员工姓名和员工的老板姓名

核心：通过别名，将同一张表视为多张表

select e.ename 员工姓名,b.ename 老板姓名 from emp e, emp b where e.mgr=b.empno

로그인 후 복사

这种方式会产生笛卡尔集，不适合大表的查询，可以使用层次查询来解决。connect by xxx start with xxx

level是层次查询提供的伪列，需要显示使用才会查询这个伪列。

select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1

로그인 후 복사

子查询

子查询语法中的小括号

必须要有小括号，书写风格要清晰如下图所示：

示例：查询比FORD工资高的员工

select * from emp where sal > (select sal from emp where ename='FORD')

로그인 후 복사

可以使用子查询的位置

select，from，where，having

select位置的子查询只能是单行子查询，也就是只能返回一条结果

select empno,ename,sal,(select job from emp where empno='7839') job from emp

로그인 후 복사

having位置的子查询

示例：查找部门平均工资大于30号部门最大工资的部门号及其平均工资

select deptno,avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno=30)

로그인 후 복사

from位置的子查询

查询结果也可以当成表

select * from (select empno,ename,sal from emp)

로그인 후 복사

增加1列年薪，使用sal*12得到年薪

select * from (select empno,ename,sal,sal*12 annsal from emp)

로그인 후 복사

主查询和子查询可以不是同一张表

示例：查询部门名称是SALES的员工信息

使用子查询的方式：

select * from emp where deptno=(select deptno from dept where dname='SALES')

로그인 후 복사

使用多表查询的方式:

select e.* from emp e, dept d where e.deptno=d.deptno and d.dname='SALES'

로그인 후 복사

子查询的排序

一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序

示例：找到员工表中工资最高的前三名，如下格式：

rownum，行号，oracle自动为表分配的伪列。

• 行号永远按照默认的顺序生成
• 行号只能使用,>=

select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<h3><strong>子查询执行顺序</strong></h3><p>一般先执行子查询，再执行主查询；单相关子查询例外。</p><p>相关子查询示例：找到员工表中薪水大于本部门平均薪水的员工</p><pre class="brush:php;toolbar:false">select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno=e.deptno)

로그인 후 복사

单行子查询和多行子查询

单行子查询返回一个结果，只能使用单行操作符；

多行子查询返回多个结果，只能使用多行操作符。

单行操作符：

操作符	含义
=	等于
>	大于
>=	大于等于
	小于
	小于等于
	不等于

多行操作符：

操作符	含义
in	等于列表中的任何一个
any	和子查询返回的任意一个值比较
all	和子查询返回的左右值比较

单行子查询示例1：

查询员工信息，要求：

职位与7566员工一样，薪水大于7782员工的薪水

select * from emp where job=(select job from emp where empno=7566) and sal >（select sal from emp where empno=7782）

로그인 후 복사

单行子查询示例2：

查询最低工资大于20号部门最低工资的部门号和部门的最低工资

select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20)

로그인 후 복사

多行子查询示例：

查询部门名称是SALES和ACCOUNTING的员工信息

使用多行子查询的方式：

select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING')

로그인 후 복사

使用多表查询的方式：

select e.* from emp e, dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING')

로그인 후 복사

子查询中的空值问题

查询不是老板的员工

注意：当子查询中包含null值时，不要使用not in。

a not in (10,20,null)

a != 10 and a != 20 and a != null, a != null 永远不成立，所以整个表达式永远返回false。

可以在子查询中把null值过滤掉再使用not in。

select * from emp where empno not in (select mgr from emp where mgr is not null)

로그인 후 복사

推荐教程：《Oracle教程》

위 내용은 Oracle 고급 쿼리에 대해 이야기해 보겠습니다(자세한 예).의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!