Oracle数据库之第二篇

/*
   多表查询  多个数据库表做连接查询
     使用场景： 查询的数据来源为多个表
	*/
	--查询员工信息和员工的部门信息
	select * from emp;
	select * from dept;
	--使用关联条件 过滤无效数据
	select * from emp,dept where emp.deptno=dept.deptno
	/*
	   内连接 隐式内连接  select * from A,B where A.列=B.列
			  显式内连接  select * from A inner join B on A.列=B.列
	   特点 做关联查询的两个表 必须双方条件数据完全匹配 才会提取
	*/
	--使用显式内连接实现
	select * from emp inner join dept on emp.deptno = dept.deptno

	/*
	  外连接   
		   左外连接   select * from A left join B on A.列=B.列
					  
					  以左表为基准 左表数据全部显示 右表数据作为补充显示
					  如果没有数据 显示空
		   
		   右外连接   select * from B right join A on A.列=B.列
					  
					  以右表为基准 右表数据全部显示 左表数据作为补充显示
					  如果没有数据 显示空
	**/
	--查询部门信息和部门下的员工信息 没有员工的部门也要显示
	--左外连接实现
	select * from dept left join emp on dept.deptno = emp.deptno
	--右外连接实现
	select * from emp right join dept on dept.deptno = emp.deptno
	--特定要求部门显示左边
	select dept.*,emp.* from emp right join dept on dept.deptno = emp.deptno
	/*
	 oracle数据库特有的外连接
		语法：使用符号（+） 实现外连接
			使用方法：根据需求 将符号放在 作为补充显示的表的列后面
			select * from A,B where A.列=B.列(+)
	*/
	--使用oracle数据库特有外连接   跟等号左右无关
	select * from emp,dept where emp.deptno(+)=dept.deptno
	select * from emp,dept where dept.deptno=emp.deptno(+)
	/*
	  自连接 自己跟自己做关联查询
		 自连接查询 别名必须加
		 select * from A A1,A A2 where A1.列=A2.列
		 使用场景：
			 关联的记录在同一个表内
	*/
	--查询员工的信息和员工的领导信息
	select e.empno,e.ename,
		   m.empno mgr_no,m.ename  mgr_name
		from emp e,emp m where e.mgr = m.empno
	--在上面基础上 再查询员工的部门信息 dept
	select * from dept
	select e.empno,e.ename,d.dname,
		   m.empno mgr_no,m.ename  mgr_name
		from emp e,emp m,dept d
		where e.mgr = m.empno and d.deptno = e.deptno
	--在上面基础之上 再查询员工的工资等级  salgrade
	select * from salgrade

	select e.empno,
		   e.ename,
		   d.dname,
		   s1.grade,
		   m.empno  mgr_no,
		   m.ename  mgr_name
	 from emp e, emp m, dept d, salgrade s1
	 
	 where e.mgr = m.empno
	   and d.deptno = e.deptno
	   and e.sal between s1.losal and s1.hisal
	--在基础之上查询领导的工资等级
	select e.empno,
		   e.ename,
		   d.dname,
		   s1.grade,
		   m.empno  mgr_no,
		   m.ename  mgr_name,
		   s2.grade mgr_grade
	 from emp e, emp m, dept d, salgrade s1,salgrade s2
	 where e.mgr = m.empno
	   and d.deptno = e.deptno
	   and e.sal between s1.losal and s1.hisal
	   and m.sal between s2.losal and s2.hisal
	--根据查询的数据 一张表一张表加的时候 分析表之间的关联关系
	--oracle的decode函数
	select e.empno,
		   e.ename,
		   d.dname,
		   decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') emp_grade,
		   m.empno  mgr_no,
		   m.ename  mgr_name,
		   s2.grade mgr_grade
	 from emp e, emp m, dept d, salgrade s1,salgrade s2
	 where e.mgr = m.empno
	   and d.deptno = e.deptno
	   and e.sal between s1.losal and s1.hisal
	   and m.sal between s2.losal and s2.hisal



	---错误示例
	select e.empno,
		   e.ename,
		   d.dname,
		   s1.grade,
		   m.empno  mgr_no,
		   m.ename  mgr_name,
		   s1.grade mgr_grade
	 from emp e, emp m, dept d, salgrade s1
	 where e.mgr = m.empno
	   and d.deptno = e.deptno
	   and e.sal between s1.losal and s1.hisal
	   and m.sal between s1.losal and s1.hisal

	/*
	  子查询 在查询语句中嵌套查询语句
		   
			语法： 
				  单行子查询 select * from A where A.列= sql返回的唯一值
				  多行子查询 select * from A where A.列 in  sql返回单列多个值
							 select * from A,(sql语句返回多行多列临时表) t 
										where A.列 = t.列
	*/
	--查询比雇员7654工资高，同时从事和7788相同工作的员工信息？
	--1.查询数据  员工信息
	--2.数据来源  emp表
	--3.查询条件  工资>7654的工资  工作=7788的工作
	select sal from emp where empno=7654  --1250
	select job from emp where empno=7788  --ANALYST
	--使用结果查询员工
	select * from emp where sal > 1250 and job = 'ANALYST'
	--使用sql语句替换查询条件
	select * from emp where 
		 sal > (select sal from emp where empno=7654)
		 and job = (select job from emp where empno=7788)
	--查询每个部门的最低工资，和最低工资的雇员 及他的部门名称
	--1.查询数据 员工信息  最低工资  部门名称
	select deptno,min(sal) d_min from emp group by deptno
	--2.数据来源  emp   sql语句得到的临时表          dept
	--3.查询条件  员工工资=部门最低工资  本部门
	select e.empno,e.ename,e.sal,d_m.d_min ,dept.dname
		   from emp e,
		   (select deptno,min(sal) d_min from emp group by deptno) d_m,
		   dept 
		   where e.deptno = d_m.deptno and e.sal = d_m.d_min
			and e.deptno = dept.deptno
	--查询每个部门最低工资的员工信息
	select * from emp where sal = 
		(select min(sal) d_min from emp group by deptno)   
	select * from emp where sal = (800,950,1300) 

	select * from emp where sal in
		(select min(sal) d_min from emp group by deptno)  
		
	--查询不是领导的员工信息
	--1.员工信息
	--2.emp
	--3.不是领导
	--子查询空值问题 空值判断 用is null is not null  其余判断结果为UNKNOW
	select * from emp where empno not in( select mgr from emp )
	--需要处理空值 
	select * from emp where empno not in( select mgr from emp where mgr is not null )
	select * from emp where empno not in( select nvl(mgr,0) from emp  )
	select * from emp where empno not in( select mgr from emp where mgr >0 )
	/*
	  子查询特殊使用
		exists 存在 表达式 (sql语句)
			判断结果集是否存在 如果存在 exists表达式返回true
								  不存在 返回false
	*/
	--简单示例
	select * from emp where exists(select * from dept)--所有员工信息
	select * from emp where exists(select * from dept where deptno=123)-- 没有记录
	--查询有员工的部门信息
	--1.部门信息
	--2.dept
	--3.部门有员工
	select deptno from emp;  ---得到了有员工的部门编号
	--使用in的方式实现
	select * from dept where deptno in (select deptno from emp) 
	/*
	  普通子查询 执行顺序是 先执行子查询得到结果用于主查询
	  exists表达式执行顺序更改
	*/
	select * from dept where 
		  exists(select * from emp where emp.deptno = dept.deptno)

	/*
	 
	  mySql 使用limit 提取特定记录条数
	  oracle 使用 rownum 实现提取记录  用于分页使用
		  rownum 是oracle数据库查询到记录 生成的一系列的数值 (1,2,3,4)
	  rownum用于做大于判断 没有结果 必须使用子查询先生成rownum
	  rowun用于小于判断可以直接查询出结果
	  
	  rowunm的执行原理 :
		1: 执行sql语句;
		2: 取到第一条记录,赋值rownum为1;
		3: 判断rownum是否满足条件,如果不满足放弃该行,满足返回该行.(不满足条件,rownum还是从1开始进行判断)
		4: 继续提取记录,继续生成rownum;
		5: 循环步骤3;
	  
	*/
	--rownum的示例
	select rownum,emp.* from emp where  rownum >5   --没有任何记录
	select rownum,emp.* from emp where  rownum <5  --前四条记录
	select rownum,emp.* from emp where  rownum =1 --只有一条
	select rownum,emp.* from emp where  rownum >1 --没有
	select rownum,emp.* from emp where  rownum >=1  --所有记录 
	--先生成rownum 再使用rownum过滤5条记录以后
	select * from (select rownum r,emp.* from emp) where r>5

	--查询员工表中 工资最高的前三名
	--工资按照倒序排序 
	select * from emp order by sal desc 
	--加入rownum
	select rownum,emp.* from emp order by sal desc 
	--先按照工资排序 再排序基础之上生成rownum
	select rownum,t.* from (select * from emp order by sal desc)t
	--判断rownum提取前三条
	select rownum,t.* from (select * from emp order by sal desc)t where rownum<4
	--提取6--10条记录
	select * from (
			 select rownum r,t.* from (select * from emp order by sal desc)t) tt
			 where r> 5 and r <11
		  

	--查询员工表中工资大于本部门平均工资的员工信息
	--1.员工信息
	select deptno,avg(sal) d_a from emp group by deptno
	--2.emp
	--3.工资>部门平均工资  必须本部门
	
	行列转换
	Total		1980		1981		1982		1987
	14			1			10			1			2
		
	
	1: 尝试竖起一列
		上面是用年的数值做的别名,下面是年对应的入职员工数
		if年1987显示值是2
			decode(hire_year,'1987',hire_count)
		
	2: 使用聚合函数处理空值	
		
	select *
	  from emp e, (select deptno, avg(sal) d_a from emp group by deptno) d_avg
	 where e.sal > d_avg.d_a
	   and e.deptno = d_avg.deptno  

	--统计每年入职的员工个数
	select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
		  from emp  group by to_char(hiredate,'yyyy')
	--对结果集格式处理  先竖起来一列
	select decode(t.hire_year,'1987',t.hire_count) "1987" from 
	  (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
		  from emp  group by to_char(hiredate,'yyyy') ) t
	--把空值的记录过滤掉 聚合函数忽略空值的记录
	select sum(decode(t.hire_year,'1987',t.hire_count)) "1987" from 
	  (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
		  from emp  group by to_char(hiredate,'yyyy') ) t
	--补全其余的列
	select sum(decode(t.hire_year,'1980',t.hire_count)) "1980",
		   max(decode(t.hire_year,'1981',t.hire_count)) "1981",
		   min(decode(t.hire_year,'1982',t.hire_count)) "1982",
		   avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
	  from 
	  (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
		  from emp  group by to_char(hiredate,'yyyy') ) t
	--使用sum对hire_count做求和运算补上total
	select sum(t.hire_count) total,
		   sum(decode(t.hire_year,'1980',t.hire_count)) "1980",
		   max(decode(t.hire_year,'1981',t.hire_count)) "1981",
		   min(decode(t.hire_year,'1982',t.hire_count)) "1982",
		   avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
	  from 
	  (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
		  from emp  group by to_char(hiredate,'yyyy') ) t
		  
	补充知识点：Oracle 中的分页查询
		ROWNUM:表示行号，实际上只是一个列,但是这个列是一个伪列,此列可以在每张表中出
		现。
		ROWID:表中每行数据指向磁盘上的物理地址。
		  
	/*
	  集合的运算
		 交集 取两个集合共同的部分 intersect A(1,2,3) B(2,3,4) A交B (2,3)
		 并集 取两个集合最大的部分 union A(1,2,3) B(2,3,4) A并B (1,2,3,4)
								   union all                  A并B (1,2,3,2,3,4)
		 差集 从一个集合去掉另外一个集合剩余的部分  minus A差B (1) 
	*/
	--范例：工资大于1500,或者是20部门下的员工
	--不使用集合实现
	select * from emp where sal>1500 or deptno=20
	--使用集合实现 --不包含重复记录
	select * from emp where sal>1500
	union
	select * from emp where deptno=20
	--union all
	select * from emp where sal>1500
	union all
	select * from emp where deptno=20

	--范例：工资大于1500,并且是20部门下的员工
	select * from emp where sal>1500 and deptno=20
	--使用集合实现
	select * from emp where sal>1500
	intersect
	select * from emp where deptno=20

	--1981年入职的普通员工（不包括经理，总裁）  
	--使用以前知识实现
	select * from emp where '1981' = to_char(hiredate,'yyyy')
				  and job not in ('MANAGER','PRESIDENT')

	--使用集合实现
	select * from emp where '1981' = to_char(hiredate,'yyyy')
	minus         
	select * from emp where  job  in ('MANAGER','PRESIDENT')
	/*
	  集合的使用场景
		用于做跨表合并数据使用
	  合并数据规则
		必须合并的列的数量一致  列的数值类型相同
	*/
	--查询公司下所有的员工信息
	select empno buisiness_no,ename  buisiness_name from emp
	union
	select mid,mname from manager;
	--数据类型不一致不能合并
	select empno buisiness_no,ename  buisiness_name from emp
	union
	select mname,mid from manager;


	--领导表
	create table manager(
		   mid number(9),
		   mname varchar(10)
	)
	insert into manager values(1,'zs');
	insert into manager values(2,'lisi');
	commit;