Oracle SQL 查询技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/CJerrybird/article/details/88070794

用例：Oracle自带的scott用户里的例子

1 分组查询

group by分组

select deptno,job,count(*) from scott.emp group by deptno,job order by deptno;
-- group by <字段1> 表示按照<字段>进行分组
-- group by <字段1>[,<字段2>,...,<字段n>] 表示按照<字段1>下的<字段2>下的<字段n>进行分组
select deptno,job,count(*) from scott.emp group by deptno,job  where deptno=10 order by deptno;-- 会报错。使用group by无法利用where条件进行过滤,可以利用having进行过滤

having过滤分组

select deptno,job,count(*) from scott.emp group by deptno,job having deptno=10 order by deptno;-- 按部门下的职位进行分组，且只显示部门编号为10的

注：having <expr> 。having需搭配group by使用

2 多表查询

2.1相同表结构(上下合并)

2.1.1 set运算符

<select_expr1> union <select_expr2>:将两个select表达式的输出去重、合并后显示.(集合A、集合B，A minus B相当于 A+B-AB)
<select_expr1> union all. <select_expr2>:将两个select表达式的输出直接合并后显示.(集合A、集合B，A minus B相当于 A+B)
<select_expr1> intersect <select_expr2>:将两个select表达式的输出的交集(都右的记录)输出.(集合A、集合B，A minus B相当于 AB)
<select_expr1> minus <select_expr2>:第一个select表达式的输出中去掉与第二个select表达式中相同的输出之后，再显示。(集合A、集合B，A minus B相当于 A-B)

2.2不同表结构(左右拼接)

2.2.1笛卡尔积

假设表A有x，y,z三个记录；表B有1，2，3三个记录，则表A和表B的笛卡尔积如下，有9个记录：

笛卡尔积在数据库中的应用：
select * from <tableA_name>,<tableB_name>;

2.2.2等值查询

又名内连接查询，只输出表A和表B的<比较字段>相符合的记录，会忽略掉为表A和biaoB里<比较字段>为值空的记录
例

select * from <tableA_name>,<tableB_name> where <tableA_name>.<colA_name> = <tableB_name>.<colB_name>;
(建议)select * from <tableA_name> [inner] join <tableB_name> on <tableA_name>.<colA_name> = <tableB_name>.<colB_name>;

2.2.3外连接查询

（1）方式一：
不同于上面内连接查询的是多了一个(+)符号

左外连接
select * from <tableA_name>,<tableB_name> where <tableA_name>.<colA_name>= <tableB_name>.<colB_name>[(+)];
(+)放在左边表示右边表里的记录全部输出；
右外连接
select * from <tableA_name>,<tableB_name> where <tableA_name>.<colA_name>[(+)] = <tableB_name>.<colB_name>;
(+)放在右边表示左边表里的记录全部输出；

（2）方式2

左外连接
select * from <tableA_name> left join <tableB_name> on <tableA_name>.<colA_name>= <tableB_name>.<colB_name>
右外连接
select * from <tableA_name> right join <tableB_name> on <tableA_name>.<colA_name>= <tableB_name>.<colB_name>

2.2.4自连接查询

同一张表进行查询，如一张员工表里面有员工的名称，员工的编号，员工上级的编号。现要求显示员工和他的上级的姓名
select a.ename 员工,a.empno 员工编号,a.mgr 员工上级,b.ename 上级名称 from scott.emp a,scott.emp b where a.mgr=b.empno(+);

注：table要起别名，不然会报错显示未指明table

2.2.5sql 1999标准

<tableA_name> inner join <tableB_name> on <expr>:内连接。相当于两集合的交集
<tableA_name> left join <tableB_name> on <expr>:左外连接
<tableA_name> right join <tableB_name> on <expr>:右外连接
<tableA_name> cross join <tableB_name>:笛卡尔积
<tableA_name> natural join <tableB_name>:自然连接。自动寻找两表中相同的字段名，相当于内连接
<tableA_name> join <tableB_name> using(<col_name>):将两表中<col_name>相同的记录返回，相当于内连接。
<tableA_name> full join <tableB_name> on <expr>:完全连接。两表中的空记录也返回。相当于两集合的全集

3 子查询

将用select查询出来的输出作为结果，可跟在where后面做判断，也可跟在from后面做一个字表

3.1单行子查询

例：列出和smith同一个部门的所有员工中薪资最高的员工的信息

select * from scott.emp where sal = (select max(sal) from scott.emp where scott.emp.job = (select job from scott.emp where ename = 'SMITH'));

3.2多行子查询

in(多个value): 满足值在多个value之中
<运算符> all(多个value):值需要<运算符>所有的多个value才满足。如当运算为>时，表示值需要大于所有给出的多个value
<运算符> any(多个value):只要值<运算符>所有的多个value中的任意一个value即满足。。如当运算为>时，表示值只需要大于多个value中的任意一个value