Maison > Article > base de données > Parlons des requêtes avancées Oracle (exemples détaillés)
Cet article vous apporte des connaissances pertinentes sur les requêtes avancées Oracle, y compris les requêtes de groupe, les requêtes multi-tables et les sous-requêtes. J'espère qu'il vous sera utile.
Les requêtes avancées sont largement utilisées dans le processus de développement de bases de données. Les requêtes avancées d'Oracle sont introduites sous trois aspects : requête de groupe, requête multi-tables et sous-requête.
La requête de groupe consiste à regrouper selon certaines règles. Après le regroupement, les données seront agrégées, mais le regroupement n'est pas nécessaire pour utiliser les fonctions d'agrégation. par.
Les fonctions d'agrégation couramment utilisées incluent : maximum max(), minimum min(), moyenne avg(), sum(), count()
La fonction count ignorera automatiquement les valeurs nulles lors de l'utilisation des noms de colonnes
.
La fonction nvl peut empêcher count d'ignorer automatiquement les valeurs vides. Sa fonction est de renvoyer 0 lorsque comm est vide. Parce que 0 n'est pas vide, il sera entré dans les statistiques totales.
Toutes les colonnes de la liste de sélection qui ne sont pas incluses dans la fonction d'agrégation doivent être incluses dans la clause group by.
Trouvez le salaire moyen de chaque département, affichez le numéro du département et le salaire moyen du département.
select deptno,avg(sal) from emp group by deptno order by deptno
Statistiques sur le salaire total des employés par département et différents postes
select detpno,job,sum(sal) from emp group by deptno,job order by deptno
Utilisation de la clause avoir
La différence entre où et avoir
Remarque : du point de vue de l'optimisation SQL, essayez d'utiliser où, car où réduit considérablement le nombre d'enregistrements regroupés, améliorant ainsi l'efficacité.
Trouvez les départements dont le salaire moyen est supérieur à 2000
select deptno,avg(sal) from emp where(avg(sal)) > 2000 group by deptno
Vous ne pouvez pas utiliser de fonctions d'agrégation dans la clause Where, donc si vous signalez une erreur, remplacez-la simplement par la clause have xxx.
select deptno,avg(sal) from emp group by deptno having(avg(sal)) > 2000
dans la requête de groupe pour trouver le salaire moyen de chaque département, afficher le numéro de département, le salaire moyen du département et trier par ordre croissant de salaire.
select deptno,avg(sal) from emp group by deptno order by avg(sal)
Vous pouvez également trier par l'alias de la colonne
select deptno,avg(sal) avgsal from emp group by deptno order by avgsal
Vous pouvez également trier par le numéro de série de la colonne
select deptno,avg(sal) from emp group by deptno order by 2
Trier par ordre décroissant et ajouter desc
select deptno,avg(sal) from emp group by deptno order by 2 desc
Demande La valeur maximale du salaire moyen du département
select max(avg(sal)) from emp group by deptno
est principalement utilisée dans la fonction de rapport groupe par relevé
Chaque département, installer différents postes, trouver le salaire total, résumé du département et résumé.
Vous pouvez utiliser la fonction rollup
select deptno,job,sum(sal) from emp group by rollup(deptno,job)
et définir le format d'affichage. break sur deptno signifie qu'un seul des mêmes numéros de département sera affiché, et sauter 1 signifie qu'il y a une ligne vide entre les différents. numéros de département.
Ajouter un titre, un numéro de page, etc.
ttitle col 15 'Mon rapport' col 35 sql.pno
Définissez le titre, videz 15 colonnes pour afficher mon rapport, puis vider 35 colonnes à afficher Numéro de page
col deptno en-tête numéro de département
col job head position
col sum(sal) en-tête salaire total
Définir les en-têtes de colonnes dans les 3 lignes ci-dessus
pause sur deptno sauter 1
Définir le format d'affichage, le même numéro de département uniquement. Affichez-en un, avec 1 ligne vide entre les différents numéros de département
Enregistrez ces paramètres dans un fichier SQL (notez qu'il doit être modifié en codage ANSI, sinon des caractères tronqués apparaîtront et les paramètres seront invalide), puis lu et exécuté via la commande get. Exécutez à nouveau l'instruction de requête et obtenez le rapport suivant. S'il y a plusieurs pages, afin de les afficher magnifiquement, vous pouvez définir une page pour afficher plus de lignes, par exemple, définir chaque page pour afficher 100 lignes : définir la taille des pages 100. Interroger les données d'une table. Commençons par interroger les données de plusieurs tables. tableaux.
Afin d'éviter l'ensemble cartésien, des conditions de connexion valides peuvent être ajoutées là où, dans des circonstances pratiques, l'utilisation de l'ensemble cartésien complet doit être évitée.
实例:查询员工信息,要求显示:员工号,姓名,月薪,部门名称
需要查询员工表和部门表,通过部门号进行等值连接查询,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
示例:查找部门平均工资大于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 | 和子查询返回的左右值比较 |
查询员工信息,要求:
职位与7566员工一样,薪水大于7782员工的薪水
select * from emp where job=(select job from emp where empno=7566) and sal >(select sal from emp where empno=7782)
查询最低工资大于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教程》
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!