Oracle_多表操作

1 sqlplus执行sql文件
sqlplus登录之后，使用 @文件地址名 可以执行某个sql文件

@Z:\Public\day01\03_resources\scott\scott.sql

2 多表查询：
想要的数据不在同一张表，就要多个表进行联查
select …
from 表1,表2… --要实现多表查询，from里边就填多个表
where …
…

查询员工信息：员工号，姓名，月薪和部门名称
	select empno,ename,dept.deptno,dname
	from emp,dept
	where emp.deptno=dept.deptno

	多表查询也叫做表连接查询，其中的where条件就是连接条件
	可以使用join来进行表连接，from 中的逗号换成join ，where换成on即可

	select empno,ename,dept.deptno,dname
	from emp join dept
	on emp.deptno=dept.deptno

	join后面还可以另外跟where条件

	等值连接，连接条件是使用等号，不等值连接就是不使用等号的

查询员工信息：员工号，姓名，月薪和月薪级别（salgrade表）
	连接条件： sal >= losal and sal <= hisal

	select empno,ename,sal,grade
	from emp,salgrade
	where sal between losal and hisal

按部门统计员工人数，显示如下信息：部门号，部门名称，人数（注意统计40号部门）
	部门号，部门名称  dept
	人数   连接emp表进行统计
	select d.deptno,d.dname,count(*)
	from dept d, emp e
	where d.deptno = e.deptno
	group by d.deptno,d.dname

	以上没有统计40号部门的员工数量，要使用外连接
	什么时候用外连接：保留没有通过连接条件筛选的数据的时候使用外连接

	oracle用法：
		想保留哪一张表的数据，应该在连接条件等号的另一边使用(+)
		select d.deptno,d.dname,e.*
		from dept d, emp e
		where d.deptno= e.deptno(+)

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

	左外连接
		保留左边表的数据的外连接就是左外连接  left
	右外连接
		保留右边表的数据的外连接就是右外连接 right

	全外连接
		左右的数据都想保留 full

	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

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

	查询员工信息：将员工的主管名字也显示出来（KING的主管是他自己）

	select a.ename , a.mgr , b.empno, b.ename
	from emp a, emp b
	where a.mgr = b.empno

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

3 子查询：

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

查询比scott工资高的员工信息 
	第一步：查询出scott的工资
		select sal from emp where ename = 'SCOTT';
		3000

	第二部：查询出比工资大于3000的员工信息
		select * from emp where sal > 3000;

	子查询：

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

注意事项
* 注意书写风格
* 父查询和子查询可以是不同的表，子查询返回的结果父查询可以使用即可
	查询部门名称是 'SALES' 的员工信息

	1 先查SALES部门编号
		select deptno from dept where dname = 'SALES'
	2 再查对应该编号的员工信息
		select * from emp where deptno = ....

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

​
​ * 父查询的select、from、where、having都可以嵌套子查询
​

	select ...
	from ...
	where ...
	group by ...    --不能使用子查询
	having ...
	order by ..    --不能使用子查询

	select 后置子查询：查询10号部门的员工号、员工姓名、部门编号、部门名称
		select empno,ename,deptno,
			(
				--将10号部门的名称查出来
				select dname
				from dept
				where deptno=10
			)
		from emp
		where deptno = 10

	from 后置子查询： 查询员工的姓名、月薪和年薪（使用select * from _________)

		select *
		from (
			select ename,sal,sal*12 年薪
			from emp
		)

	where后置子查询： 查询与ward相同岗位并且月薪比他高的员工信息
		1 先查出ward的岗位
			select job
			from emp
			where ename = 'WARD'
		2 查出ward的月薪
			select sal
			from emp
			where ename = 'WARD'
		3 最后查出岗位一致，工资又比他高的

			select *
			from emp
			where job = (
				--ward岗位
				select job
				from emp
				where ename = 'WARD'
			) and sal > (
				--ward的月薪
				select sal
				from emp
				where ename = 'WARD'
			)

	having后置子查询：查询部门最低月薪高于30号部门的部门以及其最低月薪
		1 查询30号部门的最低月薪
			select min(sal)
			from emp
			where deptno=30

		2 查询所有部门的最低月薪
			select deptno,min(sal)
			from emp
			group by deptno

		3 做比较，比较出高于30号部门的

			select deptno,min(sal) 
			from emp
			group by deptno
			having min(sal) > (
				--30号部门的最低月薪
				select min(sal)
				from emp
				where deptno=30
			)

* 单行子查询只能使用单行操作符，多行子查询只能使用多行操作符 （这里都是指单列）
	单行操作符，就是对某个单一数据的比较操作
	=|>|>=|<|<=等

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

	查询部门名称为SALES和ACCOUNTING的员工信息 (IN)

		1 从dept表查询sales和accountint 的部门id
			select deptno
			from dept
			where dname ='SALES' or dname = 'ACCOUNTING'

		2 从emp表里边查询deptno 是这两个值的员工信息
			select *
			from emp
			where deptno in (10,30)

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

​
​ 查询月薪比30号部门任意一个（某一个any）员工高的员工信息
​ 条件:只要月薪比30号部门某一个员工月薪高就行了
​

		1 先查询出30号部门的员工最低工资
			select min(sal)
			from emp
			where deptno = 30

		2 查询比该最低工资高的员工信息
			select *
			from emp
			where sal > 950

		换成子查询

			select *
			from emp
			where sal > any (
				--查询30号部门的所有工资
				select sal
				from emp
				where deptno=30
			)

			any的意思只要大于某个就行

​
​ 查询比30号部门所有员工工资都高的员工信息
​ 只要工资比30号部门最高工资高就行
​ 1 先查询出30号部门的员工最高工资
​ select max(sal)
​ from emp
​ where deptno = 30
​

		2 查询比该最低工资高的员工信息
			select *
			from emp
			where sal > 2850

		换成子查询

			select *
			from emp
			where sal > all (
				--查询30号部门的所有工资
				select sal
				from emp
				where deptno=30
			)

​
​ * 注意子查询中返回的null值影响最终计算结果
​

	查询不是主管的员工信息
		1 查询出主管的集合
			select mgr
			from emp
		2 判断员工工号在不在不在这个集合里边

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

		注意 select mgr from emp 的返回值是有null，为什么会影响？

			假设返回结果是 7902,7698,NULL
			有一个员工 7369,不是主管
			not in 的换算成C语法表示

				7369!=7902 && 7369!=7698 && 7369!=null   如果该条件为true，就符合不是主管

				null做任何逻辑运算结果都为假

				如果不是not in ，而是使用in
				7369 == 7902 || 7369 == ...

		最终要筛选一下null
			select *
			from emp
			where empno not in (
				select mgr
				from emp
				where mgr is not null
			)

​
​ * SQL解析：一般先执行子查询（内查询），再执行父查询（外查询）；关联子查询除外
​
非关联子查询，都是将子查询执行一次，得到结果之后再执行外部的父查询

​
4 集合运算：

并集 union
全并集 union all
交集 intersect
差集 minus

部门号是10的员工和部门号是20的员工信息做并集（以及全并集）
	select *
	from emp
	where deptno = 10

	union

	select * 
	from emp
	where deptno = 20

	以下是全并集的测试
	select *
	from emp
	where deptno in (10,20)

	union all

	select * 
	from emp
	where deptno = 20

用10号部门的员工信息和 10,20号部门的员工信息做交集
	select *
	from emp
	where deptno in (10,20)		

	intersect

	select * 
	from emp
	where deptno = 10

用10,30的员工减去10,20的员工信息
	select *
	from emp
	where deptno in (10,30)		

	minus

	select * 
	from emp
	where deptno in (10,20)

查询三个部门的工资信息并分组统计，格式如下：
    DEPTNO JOB                  SUM(SAL)
---------- ------------------ ----------
		10 CLERK                    1300
		   MANAGER                  2450
		   PRESIDENT                5000
									8750

		20 ANALYST                  6000
		   CLERK                    1900
		   MANAGER                  2975
								   10875

		30 CLERK                     950
		   MANAGER                  2850
		   SALESMAN                 5600
									9400

								   29025
提示：
	SQL plus中使用一下命令来去掉分组重复的deptno
		break on deptno skip 2;
	使用以下命令来恢复
		break on null;

	这里有3个集合进行并集

		1 每个部门每个岗位的工资总和
			select deptno,job,sum(sal)
			from emp
			group by deptno,job

		    DEPTNO JOB         SUM(SAL)
		---------- --------- ----------
		        20 CLERK           1900
		        30 SALESMAN        5600
		        20 MANAGER         2975
		        30 CLERK            950
		        10 PRESIDENT       5000
		        30 MANAGER         2850
		        10 CLERK           1300
		        10 MANAGER         2450
		        20 ANALYST         6000

		2 每个部门的工资总和
			select deptno , sum(sal)
			from emp
			group by deptno
			    DEPTNO   SUM(SAL)
			---------- ----------
			        30       9400
			        20      10875
			        10       8750

		3 整个公司的工资总和
			select sum(sal)
			from emp

					  SUM(SAL)
					----------
					     29025

		将以上3个集合并在一起
			select deptno,job,sum(sal)
			from emp
			group by deptno,job

			union

			select deptno ,null , sum(sal)
			from emp
			group by deptno

			union

			select null,null,sum(sal)
			from emp

​
集合运算注意事项：
​ * 参与运算的各个集合必须列数相同，且类型一致
​ * 采用第一个集合的表头作为最终使用的表头，（别名也只能在第一个集合上起）
​ * 可以使用括号修改各个sql执行的优先级
​

​
7 新增数据：

语法：
	insert into 表名 values(val1,val2,.....)   val1 val2 这些值要对应表的每列顺序
	insert into 表名(列名,列名2.....) values(val1,val2,.....)
		插入数据的时候指定要填写哪一列的值，后面val要对应上前面列名的类型

往部门表里边插入以下几行信息
    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
		50 SUPPORT                      WASHINGTON
		60 TEST
		70 PURCHASING

	insert into dept values(50,'SUPPORT','WASHINGTON')

	insert into dept(deptno,dname) values(60,'TEST')
		没有填充的列，数据库都会使用默认值，默认值创表的时候是可以设置，没有设置默认值就是NULL
	insert into dept(dname,deptno,LOC) values('PURCHASING',70,NULL)

8 修改数据：
update 表名 set 列名1=值1,列名2=值2… [where cond]
将60号部门的LOC改成MIAMI
update dept set loc=‘MIAMI’ where deptno=60
将50号部门的部门名字改成 SUPPORT1 ，LOC改为NULL
update dept set dname=‘SUPPORT1’,loc=null where deptno=50
9 删除数据：
删除数据是以行为单位
delete from 表名 [where cond]
删除部门号为50的部门信息
delete from dept where deptno=50

删除部门号大于40的部门信息
	delete from dept where deptno>40

10 事务
银行转账
A 100 -> B 0
A - 100 一条update语句
停电或者B刚好销户
B + 100 一条update语句

	这两条语句要么都执行成功，要么一条都不执行

事务的隔离级别

	读未提交
		a字段默认值是0

		T1开始
									T2开始
		将a设置为100
									读取a，读到100
		提交事务

	读已提交  oracle 默认
		a字段默认值是0

		T1开始
									T2开始
		将a设置为100
									读取a，读到0
		提交事务
									读取a，读到100
									...

	可重复读  mysql 默认
		a字段默认值是0

		T1开始
									T2开始
		将a设置为100
									读取a，读到0
		提交事务
									读取a，读到0
									T2结束

		T3 开始
		读取a，读到100
		....

	串行化  就不允许多个事务同时执行
		a字段默认值是0

		T1开始
		将a设置为100
		提交事务

									T2开始
									读取a，读到100
									T2结束

事务的控制

	在事务的执行过程中创建保存点，回滚的时候可以回滚到对应的保存点

	savepoint  保存点名字

	rollback to 保存点名字

11 课堂练习
找到员工表中工资最高的前三名， 要求按如下格式输出
rownum 是行号，伪列，并不是真实存在于表中的列，而是查询过程中生成

	select rownum,emp.* from emp order by sal desc;   序号乱了
	结论：rownum行号在排序前生成

	解决思路：先排序，后生成行号   ，子查询

找到emp表中薪水大于本部门平均薪水的员工
	注意：多表  ， from 后置多表子查询

统计每年入职的员工个数
	1 将员工的入职日期转年份  to_char(hiredate,'yyyy')

	2 使用group by 做统计？
		使用group by 是会得到以下格式
		1980   1
		1981   2
		1982   ...

		与结果样式不符

	3 提示：

		制作一个中间的表格，01标记法

		1980        1981     1982 ....
		  1	          0        0
		  0           1        0
		  ...      

		然后再做一个汇总，sum 进行统计