Oracle高级查询

1 DML语言
2 多表查询
    2.1 连接表查询
        2.1.1 内连接
        2.1.2 外连接
    2.2 自连接查询
3 子查询
4 综合练习
5 exists关键字
6 分页查询
7 集合运算符

1 DML语言

1.1 insert

• 插入一条数据到员工表
  insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1001,'李四','研发部',7788,sysdate,100,20000,10);

• 创建一个emp表的备份表emp_bak，要求表结构和emp一样
  create table emp_bak as select * from emp where 1=2;
  注意：条件1=2，表示false的意思，查询的结果是没有数据的。所有使用1=2表示创建一个没有数据的表结构

• 将emp表的数据复制到emp_bak表里面
  insert into emp_bak select * from emp;
  复制数据的语法：insert into <表名> select * from <表名>

1.2 delete

• 删除员工编号为1000的员工
  delete from emp where empno=1000;

• 删除员工表所有数据
  delete from emp;

• 截断表数据（truncate）
  删除员工表的所有数据。（truncate的效率要比delete高）
  truncate table emp;

1.3 update

• 更新员工编号为1001的员工的工资为2000
  update emp set sal=2000 where empno=1001;

2 多表查询

多表查询就是查询语句中有两个以上的表。

2.1 连接表查询

• 查询员工的信息包括部门的信息（以部门编号为连接条件）
  select * from emp,dept where emp.deptno=dept.deptno;
  我们将同时查询两个表，再使用where连接条件的格式的多表查询，称为等值查询

2.1.1 内连接

内连接：同时查询的两个关联表，只要任何一个表找不到关联表对应的数据，就会忽略不显示该数据（等同值查询）。

语法：select * from <左表> inner join <右表> on <关联条件>

• 查询员工的信息，包括部门的信息
  select * from emp inner join dept on emp.deptno=dept.deptno;

2.1.2 外连接

左外连接

左外连接：同时查询两个表，以左表为主表，主表的数据会被全部查询出来；如果找不到对应右表的数据，右表显示的字段值为null。

语法：select * from <左表> left [outer] join <右表> on <关联条件>

• 查询员工的信息包括部门的信息。要求显示所有的员工信息
  select * from emp left outer join dept on emp.deptno=dept.deptno; outer可省略

右外连接

右外连接：同时查询两个表，以右表为主表，主表的数据会被全部查询出来；如果找不到对应左表的数据，左表显示的字段值为null。

• 查询员工的信息包括部门的信息。要求显示所有部门的信息
  select * from emp right outer join dept on emp.deptno=dept.deptno; outer可省略

现实中编写SQL语句尽量使用外连接。因为外连接的效率高于内连接。

---

Oracle中外连接的特殊写法：（了解）

在条件上加“+”，标识的是非主表！

select * from emp inner join dept on emp.deptno(+)=dept.deptno; –右外连接

select * from emp inner join dept on emp.deptno=dept.deptno(+); –左外连接

2.2 自连接查询

自连接查询，就是连接的两个表时相同的。自连接是一个特殊的连接表查询。

• 查询员工上司的名字
  员工的信息：select * from emp;
  上司的名字：select ename from emp;
  连接：select a.*,b.ename,b.empno from emp a inner join emp b on a.mgr=b.empno;

3 子查询

将有两个以上select的查询语句称为组合查询，而嵌套在另一个查询里面的查询语句，称为子查询。

• 查询和SCOTT员工部门相同的员工信息。
  查询SCOTT员工的部门： select deptno from emp where ename = 'SCOTT';
  员工信息：select * from emp;
  组合：select * from emp where deptno = (select deptno from emp where ename = 'SCOTT');

子查询的三种情况：

1. 子查询作为条件的值，在WHERE后面
   select * from emp where deptno = (select deptno from emp where ename = 'SCOTT');

2. 子查询作为表，在FROM后面
   select e.avgsal from (select avg(sal) as avgsal from emp) e;

3. 子查询作为一个查询的返回值，在SELECT后面（了解）
   查询员工的部门名字，不能使用连接表查询：
   select emp.*,(select dname from dept where deptno=emp.deptno) from emp;

4 综合练习

高级查询，就是查询语句里面包括了多表查询以及子查询。

高级查询的思路：遇到复杂的查询语句，先拆分，后组合！

组合的三种情况：单行单列；多行单列；多行多列

单行单列

单行单列就是组合的条件是一个值。

• 查询工资比SCOTT用户高的员工
  SCOTT的员工工资：select sal from emp where ename ='SCOTT';
  员工信息：select * from emp;
  组合：select * from emp where sal>(select sal from emp where ename ='SCOTT') ;

多行单列

多行单列就是组合的条件是多个值的列表。

• 查询工资比名字中带“U”的员工高的员工信息
  名字中带“U”的员工工资：select sal from emp where ename like '%U%';
  员工信息：select * from emp;
  组合：select * from emp where sal > (select max(sal) from emp where ename like '%U%');

多行多列

多行多列就是组合的条件是一个表。

• 查询每一个部门中工资最低的员工信息
  每一个部门中最低的工资 ：select deptno,min(sal) from emp group by deptno;
  员工信息：select * from emp;
  组合：
  select * from emp,(select deptno,min(sal) as minsal from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal = tmp.minsal;

• 查询工资大于本岗位平均工资的员工信息
  本岗位平均工资（按岗位分组，求平均工资）：select job ,avg(sal) from emp group by job;
  员工信息：select * from emp;
  组合：
  select * from emp,(select job ,avg(sal) as avgsal from emp group by job) tmp where emp.job = tmp.job and emp.sal > tmp.avgsal;

• 查询工资大于最小工资且小于最大工资的员工信息
  最大工资和最小工资：select max(sal),min(sal) from emp;
  员工信息：select * from emp;
  组合：
  select * from emp,(select max(sal) as maxsal,min(sal) as minsal from emp) tmp where emp.sal > tmp.minsal and emp.sal < tmp.maxsal;

5 exists关键字

语法：select * from <表> where exsits(<子查询>)

子查询有值：返回true；

select * from <表> where exsits(有值) 等同于 select * from <表> where 1=1

子查询无值：返回false；

select * from <表> where exsits(无值) 等同于 select * from <表> where 1=2

---

• 查询有部门的员工的信息（必须员工表的deptno和部门表的deptno都有对应的记录）
  select * from emp where exists(select deptno from dept where deptno=emp.deptno);

• exsits的应用场景，清除脏数据。
  select * from emp where not exists(select deptno from dept where deptno=emp.deptno);

6 分页查询

Oracle是不支持像MySQL那样的limit关键字的，它是通过伪列rownum实现分页的。

Oracle数据库，在查询时会为每一条记录创建一个序号，这个序号就是伪列rownum。每一个select都会重新产生一个rownum。

---

• 注意：返回结果有两个或两个字段以上，*必须要加上表名或表别名限制范围
  select emp.* , rownum from emp;

• 获得员工表中1-10的数据
  select emp.* , rownum from emp where rownum <=10;

• 需求：获得员工表中5-10的数据
  错误代码：select emp.*,rownum from emp where rownum <=10 and rownum >=5;
  rownum不能直接使用大于或者大于等号（因为第一次判断条件时就为false了）
  只能间接使用。先生成rownum后再使用子查询
  select * from (select emp.* , rownum as r from emp) tmp where tmp.r>=5 and tmp.r<=10;

• 需求：员工表按部门排序后，获得5-10的数据
  select emp.*,rownum from emp order by deptno;
  上面代码排序后rownum序号就乱了。原因：from>where>group by>having>select>order by
  如何解决？使用子查询实现排序后再生成rownum
  先排序，后生成rownum：select tmp.*,rownum as r from(select emp.* from emp order by deptno) tmp;
  再使用子查询解决不能使用大于号的问题：select * from (select tmp.*,rownum as r from(select emp.* from emp order by deptno) tmp)tmp1 where tmp1.r>=5 and tmp1.r<=10;

7 集合运算符（了解）

union

• 求工资在1600~3000 并集 2000-5000的员工信息，包括上下限
  select * from emp where sal between 1600 and 3000 union select * from emp where sal between 2000 and 5000;

union all

• 求工资在1600~3000 并集 2000-5000的员工信息，包括上下限。包括重复的
  select * from emp where sal between 1600 and 3000 union all select * from emp where sal between 2000 and 5000;

intersect

• 求工资在1600~3000 交集 2000-5000的员工信息，包括上下限
  select * from emp where sal between 1600 and 3000 intersect select * from emp where sal between 2000 and 5000;

minus

• 求工资在1600~3000 减去 2000-5000的差集员工信息，包括上下限
  select * from emp where sal between 1600 and 3000 minus select * from emp where sal between 2000 and 5000;

集合运算符效率非常低。能不用尽量不用。

---

以下是SCOTT数据表的关系图