三种子查询

3种子查询
一般1对1链接查询 链接查询相当于两张表有数据相关 且两张表的数据都要查出来
多对一子查询  子查询一般就是一张表为了另外一张表提供条件

1.where条件子查询
    
    请思考：显示与SMITH同部门的所有员工？ 
    思路：
    1 查询出SMITH的部门号 
    select deptno from emp WHERE ename = 'SMITH'; 
    2 显示 
    SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 
    数据库在执行sql 是从左到右扫描的， 如果有括号的话，括号里面的先被优先执行。 

    多行子查询
    多行子查询指返回多行数据的子查询 

    请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号 
    SELECT DISTINCT job FROM emp WHERE deptno = 10; 
    SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); 
    （注意：不能用job=..，因为等号=是一对一的） 

     在多行子查询中使用all操作符
    问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？ 
    SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 
    扩展要求： 
    大家想想还有没有别的查询方法。 
    SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 
    执行效率上， 函数高得多 

    在多行子查询中使用any操作符
    问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？ 
    SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); 
    扩展要求： 
    大家想想还有没有别的查询方法。 
    SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30); 

    多列子查询
    单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。 

    请思考如何查询与SMITH的部门和岗位完全相同的所有雇员。 
    SELECT deptno, job FROM emp WHERE ename = 'SMITH'; 
    SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); 

2.在from子句中使用子查询

    在from子句中使用子查询
    请思考：如何显示高于自己部门平均工资的员工的信息 
    思路： 
    1. 查出各个部门的平均工资和部门号 
    SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 
    2. 把上面的查询结果看做是一张子表 
    SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 
    小总结： 
    在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。 
    注意：别名不能用as，如：SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 
    在ds前不能加as，否则会报错  （给表取别名的时候，不能加as；但是给列取别名，是可以加as的） 

3.在返回条件中子查询 （这个子查询返回只能有一个值）
    https://www.cnblogs.com/GreenLeaves/p/5818787.html
    下面改变当前随笔2中的需求,需要显示Customers表中的每个顾客的订单总数。

    简单分析下思路,因为Customers表中没有订单总数这个字段,所以目前我们只能用一个计算字段来代替订单总数,而这个计算字段的列值必须是每个顾客的订单总数。

    (1)先从Customers表中检索出顾客列表Id

    (2)对于检索出来的每个顾客，统计其在Orders表中的订单数目

    一般情况下统计一个顾客在Orders(订单表)中的订单总数,可以这样做:

    select count(*)  from Orders where CustomerId=1
    上面代码统计了顾客编号为1的订单总数,但是这里需要统计的是所有用户的订单总数,显然这样写不对,我们可以这样写:

    select name,(select COUNT(*) from Orders where Orders.CustomerId=Customers.Id) as orders from Customers
    这段代码实际并不能检索出来结果,因为他将一个计算字段,被插入到其他的SELECT语句中一起被返回,该查询对对检索出的每个顾客ID执行一次 select COUNT(*) from Orders where dbo.Orders.CustomerId=当前顾客Id的运算;因为有5个顾客,所以会进行5次运算。返回每个顾客的总订单数。

    下面是完整代码:

    select name,(select COUNT(*) from Orders where Orders.CustomerId=Customers.Id) as orders from Customers
    子查询中的WHERE子句（where Orders.CustomerId=Customers.Id）与前面使用的WHERE子句稍有不同,因为它使用了完全限定列名,而不只是列名。他指定列名和表名(Orders.CustomerId和Customers.Id),这个Where子句等于告诉SQL比较Orders的CustomerId和当前正从Customers表中检索的Id