笛卡尔积的概念

​ 笛卡尔集的行数 = table1的行数 x table2的行数

​ 笛卡尔集的列数 = table1的列数 + table2的列数

在操作笛卡尔集的时候，应该避免使用“笛卡尔全集”，因为里面含有大量错误信息。

多表查询就是按照给定条件(连接条件)，从笛卡尔全集中选出正确的结果。

笛卡尔积的数据集合中有一些是无效的数据, 需要进行过滤,

对于有2个表的的笛卡尔积, 需要一个条件, 对于n个表需要n-1个条件.

对于多表查询, 首先会生成笛卡尔积, 笛卡尔积里面会有无效的数据, 此时需要过滤筛选.

等值连接(结合笛卡尔积讲解)

查询员工信息：员工号 姓名 月薪(emp)和部门名称(dept)

分析: 
	员工号 姓名 月薪 这三个字段在emp表中, 部门名称在dept表中
	select e.empno, e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno;

不等值连接(结合笛卡尔积讲解)

查询员工信息：员工号 姓名 月薪(emp) 和 薪水级别(salgrade表)

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;

按部门统计员工人数, 显示如下信息： 部门号 部门名称 人数

select d.deptno, d.dname, count(e.empno) from emp e, dept d where e.deptno=d.deptno group by d.deptno, d.dname;
部门表中40号部门没有被统计上, 如何将40号部门也统计上呢???

右外连接

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

解释一下count(*)和count(e.empno)的区别

count(*): 只要一行中有一个字段不为空就被统计上
count(e.empno): 只有e.empno不为空才会被统计上

左外连接

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

自连接

查询员工信息：xxx的老板是 yyy

分析: emp表中的mgr列表示员工的老板的员工编号, 可以将emp表分别看做员工表和老板表, 员工表的老板是老板表的员工:

select e.ename || ' 的老板是 ' ||  nvl(b.ename, ' HIS WIFE') from emp e, emp b where e.mgr=b.empno(+);

子查询(sql嵌套sql)

查询比SCOTT工资高的员工信息

分析: 可以分两部查找,
	  	 第一步: 查找SCOTT的工资
	  	 	select sal from emp where ename = 'SCOTT';  ---3000.00

	  	 第二步: 将上一步得到的结果作为第二部查询的条件
			select * from emp where sal>3000;
	  	 
	  	 可以使用子查询方法将两步合成一步: 
			select * from emp where sal>(select sal from emp where ename = 'SCOTT');

关于子查询的10点注意事项:

1  合理的书写风格 (如上例，当写一个较复杂的子查询的时候，要合理的添加换行、缩进), 可以借助PL/SQL Developer工具
	2  使用小括号
	3  主查询和子查询可以是不同表，只要子查询返回的结果主查询可以使用即可
	   --查询部门名称是"SALES"的员工信息:
	     分析: 部门名称在dept表中, dept表与emp表是通过deptno联系在一起的.
	     --使用子查询
	   	 	select * from emp where deptno=(select deptno from dept where dname='SALES');
	   	 --使用多表查询
	   	    select e.* from emp e, dept d where e.deptno=d.deptno and d.dname='SALES';
	   	    
	4 可以在主查询的where、select、having、from后都可以放置子查询
	     a 在select后使用子查询的情况: 
	       查询10号部门员工号, 员工姓名, 部门编号, 部门名称
	       select e.empno, e.ename, e.deptno,(select dname from dept where deptno=10) from emp e where e.deptno=10;
		   注意点: 在select后面使用子查询必须保证子查询结果只返回一条记录.
	     
	     b 在having后面使用子查询的情况:
	       查询部门平均工资高于30号部门平均工资的部门和平均工资
	       1 先查询30号部门的平均工资
	       	 select avg(sal) from emp where deptno=30;
	       2 查询每个部门的平均工资
	         select deptno, avg(sal) from emp group by deptno;
	       3  
	        select deptno, avg(sal)
			  from emp
			 group by deptno
			having avg(sal) > (select avg(sal) from emp where deptno = 30);
				
	5 不可以在主查询的group by后面放置子查询 (SQL语句的语法规范)
	
	6 强调：在from后面放置的子查询(***), from后面放置是一个集合(表、查询结果)
	
	7 单行子查询只能使用单行操作符, 多行子查询只能使用多行操作符(前面的> < = 都是单行操作符, 接下来说的是多行操作符)
	  多行操作符有 IN  ANY(任意一个)  ALL （所有）
	  
	  	--查询部门名称为SALES和ACCOUNTING的员工信息
			select * from emp where deptno in (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

	    --查询薪水比30号部门任意一个(某一个 ANY )员工高的员工信息
			select * from emp where sal >(select min(sal) from emp where deptno=30);
			select * from emp where sal >(select sal from emp where deptno=30); --错误
	    	select * from emp where sal >any(select sal from emp where deptno=30);
	    	
	    --查询比30号部门所有人工资高的员工信息
	    	select * from emp where sal>(select max(sal) from emp where deptno=30);
			select * from emp where sal>all(select sal from emp where deptno=30);
  			
  8 一般不在子查询中使用order by, 但在Top-N分析问题中，必须使用order by  
  
  9 一般先执行子查询(内查询)，再执行主查询(外查询)；但是相关子查询除外
  
  10 子查询中的null值
    --查询不是老板的员工信息
      分析: 可以先查询谁是老板
      	select distinct mgr from emp;
      	select * from emp where empno not in (select distinct mgr from emp);
      	
      	select * from emp where empno not in (select distinct mgr from emp where mgr is not null);

      总结: not in 后面集合中不能出现null
      empno not in(a, b, null)---> empno!=a and empno!=b and empno!=null;

思考题?
        查询员工的姓名、薪水和年薪?  select * from ____________;
        	select * from (select ename, sal, sal*12 from emp);
        	总结: 可以将查询的结果集当成一张表来使用.
        	       也就是说, from后面既可以是表名, 也可以是select查询的结果集.
   
            
     	c 在where后使用子查询的情况:
       		--查询比平均工资高的员工的信息?
       		1 查询平均工资
       			select avg(sal) from emp;
       		2 select * from emp where sal>2073;
       		合成一步:
       			select * from emp where sal>(select avg(sal) from emp);
       			
       	  	
        d 在having后使用子查询的情况:
          --查询最低薪水高于30号部门最低薪水的部门及其最低薪水
            1 先得到30部门的最低薪水
               	select min(sal) from emp where deptno=30;  --950
            2 得到各个部门的最低薪水
            	select deptno, min(sal) from emp group by deptno;
            3 使用条件进行过滤
  			    select deptno, min(sal)
				  from emp
				 group by deptno
				having min(sal) > 950;
            	 
            将三步合并写成: 
                select deptno, min(sal)
				  from emp
				 group by deptno
				having min(sal) > (select min(sal) from emp where deptno = 30);