3 Oracle多表查询

3.1 执行sql文件

@文件地址名		--执行某个sql文件；

3.2 多表查询

• 想要的数据不在同一张表，就需要多个表进行联查。

• 多表查询也叫做表连接查询，其中的where条件就是连接条件。

• 可以使用join来进行表连接，from 中的逗号换成join ，where换成on即可，join后面还可以另外跟where条件。

  select ...
  from 表1,表2...
  where...
  ...
  

3.3 外连接

• 保留没有通过连接条件筛选的数据的时候使用外连接。

• oracle用法

  • 想保留哪一张表的数据，应该在连接条件等号的另一边使用(+)；

  select d.deptno,d.dname,count(empno)
  from dept d, emp e
  where d.deptno = e.deptno(+)
  group by d.deptno,d.dname
  

• sql用法

  select d.deptno,d.dname,count(empno)
  from dept d join emp e
  on d.deptno = e.deptno
  group by d.deptno,d.dname
  

3.4 自连接

• 表自己跟自己做多表连接查询,要查询的数据在同一张表但是不在同一行，就要使用自连接

  select a.ename||'''s manager is '||nvl(b.ename,' his wife')
  from emp a, emp b
  where a.mgr = b.empno(+)
  

3.5 子查询

• 个查询的select语句里边嵌套了另一个select语句；

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

• 注意事项

  • 父查询和子查询可以是不同的表，子查询返回的结果父查询可以使用即可；

  • 父查询的select、from、where、having都可以嵌套子查询；

  • 单行子查询只能使用单行操作符，多行子查询只能使用多行操作符，单行操作符，就是对某个单一数据的比较操作 =|>|>=|<|<=等；

  • 多行操作符，就是对一个集合的比较操作(IN、ANY、ALL)

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

  • 子查询中返回的null值影响最终计算结果;

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

3.6 集合运算

并集 union

全并集 union all

交集 intersect

差集 minus

select * from emp where deptno = 10
union
select * from emp where deptno = 20

• 用命令break on deptno skip 2来去掉分组重复的deptno，使用break on null恢复；

• 注意事项

  • 参与运算的各个集合必须列数相同，且类型一致；

  • 采用第一个集合的表头作为最终使用的表头，（别名也只能在第一个集合上起）；

  • 可以使用括号修改各个sql执行的优先级；

3.7 新增数据

insert into 表名 values(val1,val2,.....) val1 val2 这些值要对应表的每列顺序

insert into 表名(列名,列名2.....) values(val1,val2,.....)

• 字符串和日期加单引号；

• "&"符号，可简化增删查改操作

  • 例：select * from &t； 再次输入/时，可快速查找；

• 批处理

  --一次性将emp表中所有10号部门的员工，放到新表emp10中来。
  insert into emp10 select * from emp where deptno=10 ;  
  
  --一次性将 emp表中的指定列插入到表emp10中。		
  --注意：insert的列名，要和select的列名一致
  insert into emp10(empno, ename, sal, deptno)
  select empno, ename, sal, deptno	 from emp
  where deptno=10;  		
  --注意没有values关键字了。且列名必须一一对应	
  

3.8 修改数据

• update 表名 set 列名1=值1,列名2=值2..... [where cond]

  • 例：update dept set dname='SUPPORT1',loc=null where deptno=50;

3.9 删除数据

• delete from 表名 [where cond]

  • 例：delete from dept where deptno=50

3.10 rownum

• rownum 是行号，伪列，并不是真实存在于表中的列，最好在排序前生成；

• rownum只能使用<, <=符号，不能使用>，>=符号，除了>=1

  • select rownum,emp.* from emp order by sal desc;