sql 单行和多行子查询

单行和多行子查询

单行和多行子查询

--子查询(单行子查询,多行子查询)
--查询比"CLARK"工资高的员工的信息?
  --[1]'CLARK'的工资是多少?
  SELECT SAL FROM EMP WHERE ENAME ='CLARK';   --2450
  
  --[2]比2450高的员工信息
  SELECT * FROM EMP WHERE SAL >2450;  
  
----子查询:
--将一个查询的结果作为另外一个查询的条件来使用 
 SELECT * FROM EMP WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME ='CLARK');
      
    语法:
    SELECT 字段列名 FROM 表名
    WHERE 条件 比较符 (SELECT * FROM 表名);
             
-- 特点:
    --子查询在主查询前执行一次
    --主查询使用子查询的结果
        
--【1】子查询可以作为另外的一个查询的条件来使用
    --查询工资高于平均工资的雇员的名字和工资
    --[1]平均工资
        SELECT AVG (SAL) FROM EMP;
        
        SELECT ENAME ,SAL 
        FROM EMP 
        WHERE SAL >( SELECT AVG (SAL) FROM EMP)
        ORDER BY SAL;
                      
--查询和SCOTT同一部门且比他工资低的雇员的名字和工资
    --'SCOTT'在哪个部门?
    SELECT DEPTNO  FROM EMP WHERE ENAME ='SCOTT';   --20
    --'SCOTT'的工资?
    SELECT SAL FROM EMP WHERE ENAME ='SCOTT';   --3000
                      
    SELECT ENAME,SAL
    FROM EMP 
    WHERE DEPTNO = ( SELECT DEPTNO  FROM EMP WHERE ENAME ='SCOTT')
    AND SAL<(SELECT SAL FROM EMP WHERE ENAME ='SCOTT');   
               
    --注意:子查询的字段不能多于一个,只能有一个
              
    --【2】子查询可以作为insert语句的值来使用
    --今天新入职一个员工,与'SCOTT'同一个部门
    INSERT INTO EMP (EMPNO,ENAME,DEPTNO,HIREDATE)
    VALUES
    (1001,'码上成功',
    (SELECT DEPTNO  FROM EMP WHERE ENAME ='SCOTT'),SYSDATE);
               
    SELECT * FROM emp;
               
    --【3】子查询可以作为update的条件或修改的值来使用
    --将 ‘新员工’ 的工作修改为与‘SMITH’的工作相同
    --[1]smith的工作
    SELECT job FROM EMP WHERE ENAME = 'SMITH';
               
    UPDATE EMP 
    SET JOB =( SELECT job FROM EMP WHERE ENAME = 'SMITH') 
    WHERE ENAME = '成功';
               
    --将比FORD工资低的员工都加1000块钱
    --[1]'FORD'的工资是
    SELECT SAL FROM EMP WHERE  ENAME = 'FORD';
               
    UPDATE EMP SET SAL =SAL+1000
    WHERE SAL<(SELECT SAL FROM EMP WHERE  ENAME = 'FORD');
    SELECT * FROM EMP;
               
    --【4】子查询可以作为delete的条件使用
    --比FORD工资高的都删除
    DELETE EMP 
    WHERE SAL >(SELECT SAL FROM EMP WHERE  ENAME = 'FORD'); 
               
               
    --【5】子查询的结果可以作为一个表来使用
    SELECT EMPNO AS 经理编号,ENAME AS 经理姓名 FROM EMP; 
    --查询员工的编号，姓名，经理姓名
                
    SELECT E.EMPNO 员工编号,E.ENAME 员工姓名 ,M.经理姓名
    FROM EMP E,( SELECT EMPNO AS 经理编号,ENAME AS 经理姓名 FROM EMP) M
    WHERE E.MGR = M.经理编号(+);     --92标准
                
    SELECT E.EMPNO 员工编号,E.ENAME 员工姓名 ,M.经理姓名
    FROM EMP E LEFT JOIN 
    ( SELECT EMPNO AS 经理编号,ENAME AS 经理姓名 FROM EMP) M
    ON E.MGR = M.经理编号;      --99

--多行子查询
--子查询的结果返回是 多行数据
    --all：和子查询返回的所有值比较
    --any：和子查询返回的任意一个值比较
    --in ：等于列表中的任何一个

--查询工资低于任何一个“CLERK”岗位的工资的雇员信息。
    --查询CLERK这个职位的工资是多少?   1300  
    SELECT SAL FROM EMP WHERE JOB = 'CLERK';
         
    SELECT MAX(SAL) FROM EMP WHERE JOB = 'CLERK';
         
    SELECT * FROM EMP 
    WHERE SAL <(SELECT MAX(SAL) FROM EMP WHERE JOB = 'CLERK');
         
    SELECT * FROM emp;
    SELECT * FROM EMP 
    WHERE SAL < ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK');
       
--查询工资比所有的'SALESMAN'职位都高的雇员的编号,名字和工资
    --[1]'SALESMAN'工资都是多少?
    SELECT SAL FROM EMP WHERE JOB = 'SALESMAN';
       
    SELECT EMPNO,ENAME,SAL 
    FROM EMP 
    WHERE SAL >ALL (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN') ;
       
       
--查询部门20中的职务同部门10的雇员一样的雇员信息.
    --[2]部门10是什么职务
    SELECT JOB FROM EMP WHERE DEPTNO = 10;
       
    SELECT * FROM EMP 
    WHERE JOB IN(SELECT JOB FROM EMP WHERE DEPTNO = 10)
    AND DEPTNO = 20;
       
--查询在雇员中那些是经理人
--查询经理人的编号
       
       
    --查询每个部门平均薪水的等级
       
       
--小结:
--[1]多表连接查询  --92标准
    --等值连接查询    -->两个表之间,存在主外键关系
    --非等值连接       -->两个表之间没有直接关系    >= ,<= .....
    --以上两个查询,参与查询的表是平级关系
        
    --左外连接,右外连接  --参与查询的两个表之间有主次之分
        
        
--[2]多表连接查询--99标准
    --交叉连接  croos join -->笛卡尔积
    --自然连接   natural join
    -->相当于等值连接(前提是两个参与表中有同名列,要求同名列类型完全一致)
    --using连接   
    -->参与查询的两个表中多个同名列,使用using制定使用特定的列进行连接
    --inner join ....on...where  --等值连接
    --左外链接   left join 
    --右外连接   right join 
    --全连接  full join 
         
--[3] 单行子查询
    --子查询的结果是单行单列    
    --使用>=,<=,=,<,>,<> 进行where后的条件判断
          
--[4]多行子查询    
    --子查询的结果是多行单列的
    --使用 any :任意一个 ,  all(全部),  in(等值)