Oracle学习 sql基本语法（三）

子查询
    当一步不能求解时，可以使用子查询；
 
  分为：
     单行的子查询
     多行子查询
  单行操作符对应单行子查询，多行操作符对应多行子查询
 

   可以再主查询的select ，from，where ，having 都可以放子查询
        不可以在主查询的group by中放子查询
    在select中放子查询时，要求只能是单行子查询
 
 in：

 any ：
     小于某集合中的任意一个值，就是小于集合中最大值
      大于某集合中的任意一个值， 就是大于最小值   

   例：select *from emp where sal < any (select sal from emp where deptno=10)
    
   
all：
      小于某集合中的所有值，就是小于集合中的最小值；
      大于集合中的所有值，就是大于集合中的最大值；
    
   例：select *from emp where sal < all (select sal from emp where deptno=10) order by sal asc



  题：
  1.查询工资最低的员工信息
    select *from emp where sal=(select min(sal) from emp );
  2. 找到薪水大于本部门平均薪水的员工
    select *from emp a1 where sal > (select avg(sal) from emp a2 where a1.deptno=a2.deptno)
   
   查看每一个部门的平均工资：select deptno 部门编号,avg(sal) 部门平均工资 from emp group by deptno
  3.找到员工表中工资最高的前三名

   在oracle中有一个：
    rownum，是一个伪列，表示查询结果的行号
    1. 一旦生成就不会变化（会先按没有排序的时候生成rownum）
    2.杜宇rownum ，只能使用<或=，不能使用>或>=与=
  
      select rownum, empno,ename,sal
      from(
           select empno,ename,sal from emp order by sal desc
           )
      where rownum<4

     思路： 因为rownum 一旦生成不能改变，所以先将sal排好序。
             最后条件限制时才使用rownum；

1，函数

    字符函数：
        lower    全部转为小写
        upper    全部转为大写
        initcap    首字母转为大写
        -------
        concat    连接两个字符串
        substr    在字符串str中从第m个位置开始取n个字符（位置从1开始）
        length    求长度
        instr    从字符串strA中找出str所在的位置（返回第找到的1个，位置从1开始）
        lpad    把字符串str补齐到n个长度，不足就在左边加指定字符c；
                如果str.length>n就取str的前n个字符
        rpad    把字符串str补齐到n个长度，不足就在右边加指定字符c；
                如果str.length>n就取str的前n个字符（也是从前面取）
        trim    从str的两端去掉字符，要是指定的字符时才去掉，使用方式特殊：
                例：trim('a' from 'aaITCASTaa') 结果为 'ITCAST'
                    trim('C' from 'aaITCASTaa') 结果为 'aaITCASTaa'
                注意：前面只能指定一个字符。
        replace    例：replace('aaITCASTaa', 'a', '=') 结果为 '==ITCAST=='

    数字函数：
        round    四舍五入，例 round('45.923', 2) 表示保存两位小数，保留的位数可以指定正、零、负数。
        trunc    截断，舍掉后面的数，保留的位数可以指定正、零、负数。
        mod        求余，如 mod(12, 5) 结果为2

    日期函数：
        日期可以相减，但不能相加，因为没有意义。
            select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;
            select ename,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
        add_months
        next_day    从某个日期算起，下一个出现该星期几的日期是哪天： select next_day(sysdate,'星期三') from dual;
        last_day    当月的最后一天
        round
        trunc

    转换函数：
        隐式数据类型转换 与 显式数据类型转换
        to_char(date, format)
        to_date(string [,format])
        to_char(number, format)
        to_number(string [,format]) 如： to_number('22') 或 to_number('$22', '$99')

    空值处理函数：
        NVL (expr1, expr2)
            可以使用的数据类型有日期、字符、数字
        NVL2 (expr1, expr2, expr3)
            expr1不为NULL，返回expr2；为NULL，就返回expr3（ expr1 != null ? expr2 : expr3 ）

    条件表达式：
        用于实现 IF-THEN-ELSE 逻辑。
        CASE 表达式：SQL99的语法，比较繁琐。
        DECODE 函数：Oracle自己的语法，类似Java，比较简洁。
        例：
            根据员工的职位涨工资：总裁1000 经理800 其他400
            PRESIDENT
            MANAGER
   
    函数嵌套：
        嵌套函数的执行顺序是由内到外。


=================================================
2，集合运算
    查询属于部门10与部门20的所有员工信息，还可以这样查
        select * from emp where deptno=10
        加上（这里写集合运算符）
        select * from emp where deptno=20;
    这就是集合运算。

    并集：
        UNION        集合a + b的结果，没有重复记录。
        UNION ALL    集合a + b的结果，保留所有重复的记录。（用的比较少）
        例：查询属于部门10与部门20的所有员工信息。
        例：查询工资在500~1500或在1000~2000范围的员工信息（这是两个工资级别）。
    交集
        Intersect    既属于集合a又属于集合b的记录。
        例：查询工资在500~1500又在1000~2000范围的员工信息（这是两个工资级别）。
    差集
        Minus        集合a - b的结果，即从a中去除所有属于集合b的元素，注意a-b与b-a的结果是不一样的。
        例：查询属于500~1500但不属于1000~2000范围的员工信息。

注意：
    1，Select语句中参数类型和个数要一致
        如果不一致，需要想办法补齐。
        例如要补个字符串，不能写个'a'、'b'等，要不影响结果，应补一个null，还要指定类型。
            如果是字符串，可以写 to_char(null);
            如果要补数字类型，则写 to_number(null)
    2，结果集采用第一个select的表头作为表头。
        在第一个select上起别名才有用。
        在后面的select上起别名就没有用。
    3，如果有order by子句
        必须放到最后一句查询语句后。
       

=================================================
3，多表查询
   
    类型：
        1. 等值连接
        2. 不等值连接
        3. 外连接
        4. 自连接
   
    等值连接：
        例：查询员工信息，要求显示员工的编号，姓名，月薪和部门名称
        select e.empno,e.ename,e.sal,d.dname
        from emp e,dept d
        where e.deptno=d.deptno;
   
    不等值连接：
        例：查询员工的工资级别：编号 姓名 月薪和级别
        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;
        结果：
            DEPTNO DNAME          COUNT(E.EMPNO)
        ---------- -------------- --------------
                10 ACCOUNTING                  3
                20 RESEARCH                    5
                30 SALES                       6
       
        SQL> select * from dept;
            DEPTNO DNAME          LOC           
        ---------- -------------- ------------- 
                10 ACCOUNTING     NEW YORK      
                20 RESEARCH       DALLAS        
                30 SALES          CHICAGO       
                40 OPERATIONS     BOSTON        
       
        SQL> select * from emp where deptno=40;
            未选定行

    期望效果：当连接条件不成立时，仍然希望在结果中包含某些不成立的记录。这就要用到外连接。

    外连接：
        左外连接：where e.deptno=d.deptno  当连接条件不成立时，等号左边所代表的表的信息仍然显示
        右外连接：where e.deptno=d.deptno  当连接条件不成立时，等号右边所代表的表的信息仍然显示

        左外连接的写法： where e.deptno=d.deptno(+)
        右外连接的写法： where e.deptno(+)=d.deptno

    实现上面的效果：
        select d.deptno,d.dname,count(e.empno)
        from emp e,dept d
        where e.deptno(+)=d.deptno
        group by d.deptno,d.dname
        order by 1;

    自连接：利用表的别名，将同一张表视为多张表
        例：查询员工信息：xxx的老板是yyy
        select e.ename||'的老板是'||b.ename
        from emp e, emp b
        where e.mgr=b.empno;

使用SQL99标准的连接查询（JOIN..ON..）
    内连接
        只返回满足连接条件的数据（两边都有的才显示）。
        select e.*, d.*
        from emp e
        inner join dept d
        on e.deptno=d.deptno
        -- 也可以省略inner关键字。
       
    左外连接
        左边有值才显示。
        select e.*, d.*
        from emp e
        left outer join dept d
        on e.deptno=d.deptno
        -- 也可以省略outer关键字
       

    右外连接
        右边边有值才显示。
        select e.*, d.*
        from emp e       
        right outer join dept d
        on e.deptno=d.deptno
        -- 也可以省略outer关键字
   
    满外联接
        任一边有值就会显示。
        select e.*, d.*
        from emp e
        full outer join dept d       
        on e.deptno=d.deptno
        -- 也可以省略outer关键字
   
    交叉连接：
        叉集，就是笛卡尔积
        select e.*, d.*
        from emp e
        cross join dept d
        -- 没有连接条件
               

=================================================
4，处理数据（DML，增删改）
    DML，Data Manipulation Language，数据操作语言
   
    插入数据（Insert into）：
        插入全部列
        插入部分列
        插入空值

    使用 & 变量（创建脚本）
        例：
            SQL> insert into emp (empno, ename, sal) values (&empno, &ename, &sal)
            SQL> insert into emp (empno, ename, sal) values (&empno, '&ename', &sal)
            SQL> update emp set ename='&new_name' where empno=&empno
            SQL> select empno,ename,&col from emp;
        可以在命令行sqlplus或是iSQL*Plus中演示。

    从其它表中拷贝数据
        insert into mytable
        select mycolums from ...

    更新数据（Update）

    删除数据（Delete）

    删除所有的记录：
        Delete
        Truncate