本文介绍了Oracle数据库中的查询方式，包括自然连接、内连接、外连接（左连接、右连接、全连接）的概念和用法。此外，还详细解析了SQL查询语句的基本结构，如SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY子句的使用规则。同时，讨论了数据分组、组函数以及子查询的注意事项和使用原则。
- 自然连接
连接原则：两个表包含相同的列名，并且具有相同的列值（共有列)
同名列作为搜寻条件时，即强制添加条件——where t1.column=t1.column
SQL> select id,e.last_name,d.name from s_emp e
2 natural join s_dept d;
ID LAST_NAME NAME
---------- ------------------------- ------------
10 Havel Finance
无同名列时相当于笛卡尔积
注意：不能为共有列指定限定词，即该列名不能标识为a表或b表中的列，（不是它们特有)— NATURAL 联接中使用的列不能有限定词
SQL> select e.id,e.last_name from s_emp e
2 natural join s_dept d;
select e.id,e.last_name from s_emp e
*
第 1 行出现错误:
ORA-25155: NATURAL 联接中使用的列不能有限定词
说明：id 为它们共有列
- 内连接（inner join）
- 可以将共有列作为进行限定
- 将满足条件的A表和B表数据列出来
SQL> select * from emp
2 inner join dept;
inner join dept
*
第 2 行出现错误:
ORA-00905: 缺失关键字
说明：少了on关键字
SQL> select * from emp
2 inner join dept
3 on emp.deptno=dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 09-12月-82 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 12-1月 -83 1100 20 20 RESEARCH DALLAS
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
已选择14行。
说明：不满足on条件的不列出来
- 外连接
以一个数据源为基础，另一个数据源与之进行条件匹配 - 左外连接
以左表为基础，右表与之条件匹配，左表中全部数据列出来，右表中满足条件的数据列出来，右表不满足的不列
SQL> select * from dept d
2 left join emp e
3 on d.deptno=e.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-12月-80 800 20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-4月 -81 2975 20
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 09-12月-82 3000 20
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-11月-81 5000 10
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 12-1月 -83 1100 20
30 SALES CHICAGO 7900 JAMES CLERK 7698 03-12月-81 950 30
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-12月-81 3000 20
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-1月 -82 1300 10
40 OPERATIONS BOSTON
89 adsa aq
已选择16行。
说明：左表中所有数据全列出来了，右表中没有与之条件匹配的就以空占位
SQL> select * from emp e
2 left join dept d
3 on e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- -------------
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 12-1月 -83 1100 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 09-12月-82 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
8526 QIU'XIANG ANALYST 7839 23-1月 -85 1400
9257 HUA'AN CLERK 7839 23-1月 -85 1300
已选择16行。
- 右外连接（right join)
与左连接相反
外连接的简略写法——在where条件中将附属数据源的列使用（+）进行标识
SQL> select e.empno,e.ename,d.deptno,d.dname
2 from emp e,dept d
3 where e.deptno(+)=d.deptno;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
40 OPERATIONS
89 adsa
已选择16行。
- 全连接（full join)
将两个表的数据都列出来
查询
SQL 语言中查询语句的基本格式为:
SELECT [ALL | DISTINCT ] <列名> ①
FROM <表名> ②
[WHERE <条件表达式>] ③
[GROUP BY <列名>] ④
[HAVING <条件表达式>] ⑤
[ORDER BY <列名>] ⑥
其中:
① 以逗号分隔的查询结果集中要显示的列名。
② 查询的表名。
③ 查询条件，可选项，如果省略则查询所有的数据。
④ 分组依据。
⑤ 分组后的筛选条件
⑥ 排序结果集，ASC 是升序（默认），DESC 是降序
数据分组
实际开发中，往往需要将数据进行分组，以便对各个组的数据进行统计。在关系型数据库中，数据分组是通过在 SELECT 语句中加上 GROUP BY 子句、组函数和HAVING 子句来共同完成的。
GROUP BY 用于指定要分组的列的列名(分组的依据)
组函数用于计算和显示统计结果
HAVING 子句用于限制显示统计结果(对分组之后的数据再次筛选)
- 注意
1、Oracle 在 SQL*Plus 中的命令以分号（;）结尾，代表命令完
毕并执行，系统同时会把该命令保存在缓存中，缓存中只保
存最近执行过的命令，如果重新执行缓存中的命令，直接使
用左斜杠符号（/）。如果命令不以分号结尾，该命令只是写
入缓存保存起来，但并不执行。
2、DISTINCT 必须紧跟 SELECT出现，否则就是错误的。比如以下两种查询是就是正确的:
3、avg返回列或表达式的平均值; 参数只能是 number
sum返回列或表达式的总和; 参数只能是 number
count返回非 null 行的行数
4、组函数中可以使用 ALL 和 DISTINCT 选项。其中，ALL 是默认选项，表示
统计所有的行(包括重复的行)； DISTINCT 则不统计重复的行。
5、未在 group by 子句后面出现的列名，不能出现在 select 子句中的非组函数表达式中，但是出现在 group by 子句后面的列名可以不出现在 select 子句的非组函数表达式中
6、不能在 where 子句中使用组函数来限制分组显示(即不能在 where 子句中使用组函数)。
7、为了更改数据类型，相关列的值必须为 null ！！！
若不减小其长度，则可以把数据类型从 char 改为 varchar2，即使相关的列不为空，也可以操作；反之亦可(从 varchar2 到 char)
8、子查询的使用原则：
子查询必须用 ( ) 括起来，否则无法判断子查询的开始和结尾；
子查询中不能包括 order by 子句；
1、where和group by 子句不能出现组函数
2、有group by语句时：在group by子句中出现的列名可以不出现select子句中，但select子句中出现的非组函数列必须出现在group by子句中；4、使用组函数时，需注意以下几点： 组函数只能出现在选择列表(SELECT 后面)、ORDER BY 子句、HAVING 子句中
绝对不能出现在 WHERE 子句和 GROUP BY 子句中
在 SELECT 之后出现的列，要么经过组函数处理，否则必须出现在 GROUP
BY 中
3、order by语句必须放在最后
4、having 子句必须跟在group by 子句之后。(having 子句在 group by 子句之后执行)
- 涉及不同表，可用表连接
- 涉及同张表，可用where、group by、having ，order by
多表查询（表的别名、笛卡尔积、表连接、）子查询（单行、多行、嵌套、多列、关联；分页查询）
1、别名使用总结:
where 子句之后可以跟 表的别名，但不可以跟 列的别名 ；
group by 子句之后可以跟 表的别名 ，但不可以跟 列的别名 ；
order by 子句之后可以跟 表的别名，也可以跟 列的别名，甚至是列的位置 。
2、内连接
所谓内连接，就是查询结果中的数据全部都是满足连接条件的连接查询。
比如查询每个班级及其所属专业，可以使用:
SELECT * FROM t_majors m
INNER JOIN t_classes c
3、所谓等值连接，就是在连接条件中使用了等号之类的比较运算符对两个数值进行比较
该查询属于内连接中的等值连接。
select * from t_majors m
inner join t_classes c
on c.major_id = m.id ;
4、所谓非等值连接，就是在连接条件中不是对两个数值进行比较，而是判断取值是否在某个区间内。
比如，找出每个员工及其相应的工资级别(使用 emp 和 salgrade 表)可以使用:
select e.ename , e.sal , g.grade
from emp e
inner join salgrade g
on e.sal between g.losal and g.hisal ;
5、在连接过程中，如果希望某个表中不满足条件的数据也出现在查询结果中，那么可以使用外连接来实现。
1. 左外连接
除了满足连接条件的数据外，期望将不包含任何班级的专业也列出来，即将左表( t_majors ) 中的不满足条件的数据也列出来，
2、右外连接
除了满足连接条件的数据外，期望将不属于任何专业的班级也列出来，即将右表( t_classes ) 中的不满足条件的数据也列出来，
3. 全外连接
即将右表( t_classes ) 中的不满足条件的数据也列出来，同时将不包含任何班级的专业也列出来，即将左表( t_majors ) 中的不满足条件的数据也列出来
子查询
1 单行子查询
所谓单行子查询，这里指只返回单行单列值的子查询，因此可以把这种子查询作为一个常量来对待。
单行子查询中只能使用单行比较运算符，单行比较运算符包括：
=、>、>=、<、<=、<> 或 != 。
查询跟 SMITH 的职位相同所有人的员工号、姓名、薪水、职位
SQL> select empno , ename , sal , job from emp
2 where job = (
3 select job from emp where ename='SMITH'
4 );
select e.*,a.job
from emp e,(select job from emp where ename='SMITH') a
where e.job=a.job;
哪些职位的平均工资比整个公司的平均工资高?
2、having 子句中的单行子查询
SQL> select job , avg(sal) from emp
2 group by job
3 having avg(sal) > ( select avg(sal) from emp ) ;
3. from 字句中的单行子查询
哪些员工的工资比整个公司的平均工资高
SQL> select e.empno , e.ename , e.sal , e.job , a.avgsal
2 from emp e , ( select avg(sal) avgsal from emp ) a
3 where e.sal > a.avgsal ;
2 、多行子查询
多行子查询可以返回单例多行数据。执行返回单例多行数据的子查询被称为多
行子查询。
多行运算符包括：in 、not in、any、all、exists、not exists 。
显示与部门号为 20 的员工的岗位相同的雇员的信息：
select ename , deptno , job , sal from emp
where job in ( select distinct job from emp where deptno=20 ) ;
示高于部门号为 20 的任意雇员工资的雇员信息：
select ename , deptno , sal , job from emp
where sal > any ( select sal from emp where deptno=20 ) ;
3、多列子查询
子查询返回多个列的数据，那么称该子查询为多列子查询。
显示与 SMITH 在同一个部门、并且岗位相同的所有雇员的信息：
select ename , deptno , sal , job from emp
where (deptno , job) = (
select deptno , job from emp where ename='SMITH'
) ;
4 关联子查询
关联子查询，即相关子查询，指引用了外部 SQL 中某些表或某些列的子查询。
但是，外部 SQL 中不能引用子查询中的表或列！
这里的外部 SQL 可以是一条 select、update、delete 语句！
显示每个部门的最高工资的雇员信息(查询 s_emp 表)：
select id , first_name , salary , dept_id
from s_emp b
where salary = ( select max(salary) from s_emp a where a.dept_id = b.dept_id )
显示每个部门的最高工资：
select deptno ,
( select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a
group by deptno
order by deptno;
select deptno,max(sal)
from emp
group by deptno;
显示工作在 NEW YORK 的雇员信息
select ename , deptno , sal , job
from emp
where exists (
select * from dept
where dept.deptno=emp.deptno and dept.loc='NEW YORK'
) ;
如果父查询中使用了子查询中的表或列，那么将会导致错误：
select ename , deptno , sal , job , loc
from emp
where exists (
select * from dept
where dept.deptno=emp.deptno and dept.loc='NEW YORK'
) ;
Oracle 提示: 第 1 行出现错误: ORA-00904: "LOC": 标识符无效
5、嵌套子查询
嵌套子查询，是指在子查询中再使用子查询。
嵌套越多，效率越低。
扫一扫
3259
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
