SQL 详解--Oracle

2019独角兽企业重金招聘Python工程师标准>>>

>>>>>>1.SQL(Structured Query Language):结构化查询语言<<<<<<<<<<<<<<
◆注意：在数据中，字符串的字面量使用单引号括起来，这与JAVA不同，注意区分，虽然SQL语句不区分大小写，但是字符串内容是区分大小写的。
1.SQL可以分为：
1)数据定义语言(DDL): Data Definition Language
    >>>>>用于建立、修改、删除数据库对象
包括：
    --CREATE：创建表或者其他对象的结构
    --DROP：删除表或其他对象的结构
    --TRUNCATE：删除表数据，保留表结构
    --RENAME :用于修改表名
        例：RENAME old_name TO new_name
    --ALTER：修改表或其他对象的结构
    --增加的列只能排在最后
          例1：ALTER TABLE mytable ADD(增加列) 
    --删除不要的列
          例2：ALTER TABLE mytable DROP(column列名);
    --修改列的属性
          例3：ALTER TABLE mytable MODIFY(改变列)
    ◆注意：修改表尽量在表中没有数据时进行
2)数据操纵语言(DML):Data Manipulation Language
    >>>>>用于改变数据表中的数据。
       和事务相关，必须通过事务控制语句提交后才真正修改数据库
包括：
    --INSERT：将数据插入到数据表中
    --UPDATE：更新数据表中与存在的数据
    ◆注意：在修改表的数据时，通常添加where条件，否则将修改所有数据
    --DELETE：删除数据表中的数据
    ◆注意：删除表中数据，通常会添加where来删除满足条件的记录，如不添加where表示清空表
    --TRUNCATE ：删除表数据，保留表结构,清除表的磁盘空间，不受事务限制
3)事务控制语言(TCL):Transaction Control Language
    >>>>>用来维护数据库的一致性。
包括：
    --COMMIT：提交，确认已经进行的数据改变
    --ROLLBACK：回滚，取消已经精细的数据改变
    --SAVEPOINT：保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变
4)数据查询语言(DQL):Date Query Language
    >>>>>用来查询所需要的数据。
包括：
    --SELECT：用于查询数据表
5)数据控制语言(DCL):Data Control Language
    >>>>>用于执行权限的授予和回收操作
包括：
    --GRANT：授予，用于给用户或角色授予权限
    --REVOKE：用于收回用户或角色已有的权限
    --CREATE USER：创建用户
>>>>>>>>>>>>>>>>>>>2.Oracle数据类型<<<<<<<<<<<<<<<<<<
1.number 数字类型
    --number(p,s)
    --p:表示数字的总位数，p取值1-38
    --s:表示小数点后面的位数
2.char 和 varchar2  字符类型
    1）char：表示固定长度的字符类型
        --char(N) N表示占用N个固定的字节数
        --最大2000字节
    2）varchar2：变长的字符类型
        --varchar2(N) : N表示最多能占用N个字节数
        --最大4000字节
3.clob 字符串类型
    --存储定长或变成字符串，最多达4GB的字符串数据
4.DATE 和 timestamp  时间类型
1）Date
    --用于定义日期时间的数据
    --长度是7个字节
    --默认：DD-MON-RR 例：11-APR-71
2）timestamp
    --保存日期和时间，精确度到ns

>>>>>>>>>>>>>>>>>>>3.字符串函数<<<<<<<<<<<<<<<<<<
1)CONCAT和”||”
    --Concat(char1,char2):返回两个字符串连接后的结果。
    --等价操作：连接操作字符“||”
2)LENGTH(char)
    --用于返回字符串的长度
3)UPPER 、LOWER、INITCAP
    --Upper(char):用于将字符串转换为大写形式
    --Lower(char):用于将字符串转换为小写形式
    --Initcap(char):用于将字符串中每个单词的首字符大写，其他字符小写，单词之间用空格分开
4)TRIM、LTRIM、RTRIM
    --trim(char c1 from String c2) 去除c2两边的重复字符
    --LTRIM(String c1,String c2)//去除c1中左边c2重复的字符
    --RTRIM(String c1,String c2)/去除c1中右边c2重复的字符
    --select trim('e' from 'eeeelelllashsaeeeeetee') from dual;
    --select ltrim('eeeelelllashsaeeeeetee','e') from dual;
    --select rtrim('eeeelelllashsaeeeeetee','e') from dual;
5)LPAD 、RPAD补位函数
    --select LPAD(sal,10,' ') from emp_zxc;
    --select rpad(sal, 5, '*') from emp_zxc;
6)SUBSTR截取字符串
    --substr(char,n,m) 返回char中从M位开始取m个字符,m大于长度即取到末尾.
        n为负数，则从尾部开始
    --select substr('abcdefg',-5,4) from dual;
7)instr查看字符串的位置
    --select instrb('thinking in java','in',4,2) from dual;

>>>>>>>>>>>>>>>>>>>4.oracle数值操作<<<<<<<<<<<<<<<<<<
1.round(n[,m]) 用于四舍五入
--n: 被处理的数字
--m: 正数取到小数点后第m位，负数取到小数点前m位，缺省默认值为0
2.trunc(n[,m]) 用于截取
    --用法与round相似
3.mod(m,n) 
    --mod(m,n) :返回m除以n后的余数
    --n 为 0 ，直接返回m
4.ceil(n)和floor(n)
    --ceil(n): 取大于等于n的最小整数
    --floor(n):取小于等于n的最大整数

>>>>>>>>>>>>>>>>>>>5.oracle日期操作<<<<<<<<<<<<<<<<<<
--DATE 存储固定为7个字节，格式为：
    第1字节：世纪+100
    第2字节：年
    第3字节：月
    第4字节：天
    第5字节：小时+1
    第6字节：分+1
    第7字节：秒+1
--TIMESTAMP 与Date 的区别为还能保存小数秒，最高精度到ns
    第8-11字节；纳秒
--查询 SELECT SYSDATE/SYSTIMESTAMP FROM DUAL;

--日期转换函数
    1）TO_DATE(char[,fmt[,nlsparams]])
        --将字符串按照定制的格式转换为日期类型
        char:要转换的字符串
        fmt:格式
        nlsparams:指定日期语言
        ---例：TO_DATE('2002-01-01','YYYY-MM-DD')
    2)TO_CHAR(date,[fmt[,nlsparams]])
        --将其他类型的数据转换为字符类型
        ---例：TO_CHAR(date,'YYYY"年"MM"月"DD"日"')
--日期常用函数
    1）LAST_DAY(date):返回日期date所在月的最后一天
    2）ADD_MONTHS(date,i):返回日期date加上i个月后的日期值
        --如果i是小数，会被截取整数后再运算
        --如果i是负数，则获得减去i个月后的日期值
        ---例：ADD_MONTHS(date,20*12)  //12年后
    3）months_between(date1,date2) 计算两个日期间隔多少个月
        --返回结果是带小数位的(间隔整月除外)
        ---例：2009-9-1到2009-10-10 返回1.29个月
    4）next_day(date,char) 返回date日期数据的下一周几
        --数字1-7 表示周日-周六
        ---例：next_day(sysdate,4) 查询下一个周三是几号
    5）least(expr1[,expr2[,expr3]]...) 返回最小值
       greatest(expr1[,expr2[,expr3]]...) 返回最大值
           ---例：least(sysdate,'10-10月 -08')
    6)extract(date from datetime)
        --从参数datetime中提取参数date指定的数据，如年,月,日,小时等
        ---例：extract(year from sysdate) 

>>>>>>>>>>>>>>>>>>>6.oracle NULL操作<<<<<<<<<<<<<<<<<<
--隐式插入和显示插入
--空值函数
    1）NVL(expr1,expr2):将NULL转变为非NULL值
        --如果expr1为null,则取值expr2,expr2为实际值
        --expr1和expr2可以是任何类型，当两者数据类型必须一致
        ---例：nvl(comm,0) //如果comm为null，返回0
    2）NVL2(expr1,expr2,expr3):将NULL转变为非NULL值
        --判断expr1是否为null，不是，返回expr2；是，返回expr3
        ---例：NVL2(comm,sal+comm,sal)

>>>>>>>>>>>>>>>>>>>7.oracle SQL基础查询<<<<<<<<<<<<<<<<<<
1.使用别名
    --语法:列的别名跟在列名后，中间可以加或不加一个“AS”关键字
2.查询条件：
    1）>, <, >=, <=, !=（不等于）, <>（不等于）, =
        --例: SELECT * FROM emp WHERE deptno <> 10;
    2)AND，OR关键字
        --如果希望返回的结果必须满足多个条件，
            应该使用AND逻辑操作符连接这些条件，
        --如果希望返回的结果满足多个条件之一即可，
            应该使用OR逻辑操作符连接这些条件
    3)LIKE(模糊查询)
        -- %：表示0到多个字符
        -- _：标识单个字符
        ---例：查询职员姓名中第二个字符是‘A’的员工信息
            SELECT * FROM emp WHERE ename LIKE '_A%';
    4）IN和NOT IN （后接list）
        --IN(list)取出符合列表范围中的数据，
        --NOT IN(list) 取出不符合此列表中的数据记录
        ---例：SELECT * FROM emp WHERE job IN ('MANAGER','CLERK');
    5）BETWEEN…AND…
        --BETWEEN…AND…操作符用来查询符合某个值域范围条件的数据，
        --最常使用在数字类型的数据上，但对字符类型和日期类型数据也适用

    6）IS NULL和IS NOT NULL
        --空值是一个特殊的值，比较时不能使用”=”号，
            必须使用IS NULL，否则不能得到正确的结果
    7）ALL和ANY :表示“全部”和“任一”
        > ANY:大于最小
        < ANY:小于最大
        > ALL:大于最大
        < ALL:小于最小
        ---例：查询薪水比职位是“SALESMAN”的人高的员工信息
        SELECT empno, ename, job, sal, deptno
        FROM emp
        WHERE sal> ANY (
        SELECT sal FROM emp WHERE job = 'SALESMAN');
    8）DISTINCT :过滤重复
        ---例：SELECT DISTINCT deptno FROM emp;
3.排序
    1）ORDER BY :对查询出的数据按一定规则进行排序操作
        ---例：SELECT ename, sal FROM emp ORDER BY sal;
    2）ASC和DESC ：升序和降序，默认是升序
        ---例：SELECT ename, sal FROM emp ORDER BY sal desc;
    3)多个列排序
        --当以多列作为排序标准时，先按照第一列进行排序，
            如果第一列数据相同，再以第二列排序，以此类推.
        ---例：先按照部门编码正序排列，再按照薪水降序排列
            SELECT ename, deptno, sal FROM emp
                    ORDER BY deptno ASC, sal DESC;
4.聚合函数**重要**
    --查询时需要做一些数据统计时
        比如：查询职员表中各部门职员的平均薪水，各部门的员工人数。
    --当需要统计的数据并不能在职员表里直观列出，
        而是需要根据现有的数据计算得到结果，
        这种功能可以使用聚合函数来实现，
        即：将表的全部数据划分为几组数据，每组数据统计出一个结果。
        因为是多行数据参与运算返回一行结果，
        也称作分组函数、多行函数、集合函数。
    --用到的关键字：
        --GOURP BY :按什么分组
        --HAVING :进一步限制分组结果
    --聚合函数忽略NULL值

    1）MAX和MIN :用来取得列或表达式的最大、最小值
        --可以用来统计任何数据类型，包括数字、字符和日期
        ---例：获取机构下的最高薪水和最低薪水
            SELECT MAX(sal), MIN(sal) FROM emp;
    2）AVG和SUM :用来统计列或表达式的平均值和和值,并忽略NULL值
        --这两个函数只能操作数字类型，
        ---例：获得机构下全部职员的平均薪水和薪水总和
            SELECT AVG(sal),SUM(sal) FROM emp;
    3）COUNT ：用来计算表中的记录条数,并忽略NULL值
        ---例:获取职员表中一共有多少名职员记录
             SELECT COUNT(*) FROM emp;
5.分组GROUP BY  **重要**
    --having必须跟在GROUP BY后面，不能单独存在
    ---例如查询每个部门的最高薪水，且最高薪水大于4000
         SELECT deptno, MAX(sal) max_sal FROM emp
            GROUP BY deptno HAVING MAX(sal) >4000;

◆查询语句的执行顺序
    1.FROM 子句：执行顺序为从后往前、从右到左。
        --数据量较少的表尽量放在后面。
    2.WHERE子句：执行顺序为自下而上、从右到左。
        --将能过滤掉最大数量记录的条件写在WHERE子句的最右。
    3.GROUP BY：执行顺序从左往右分组，
        --最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
    4.HAVING 子句：消耗资源。
        --应避免使用,会在检索出所有记录之后才对结果集进行过滤,需要排序等操作。
    5.SELECT子句：少用*号，尽量取字段名称。
        --数据库查询数据字典将*号依次转换成所有的列名,消耗时间。
    6.ORDER BY子句：执行顺序为从左到右排序，消耗资源。

6.关联查询
    --查询两个或两个以上数据表或视图的查询叫做连接查询
    --连接查询通常建立在存在相互关系的父子表之间
    ---语法1：
        SELECT table1.column, table2.column
        FROM table1, table2
        WHERE table1.column1 = table2.column2;
    ---语法2：
        SELECT table1.column, table2.column
        FROM table1JOIN table2
        ON(table1.column1 = table2.column2);
    1）笛卡尔积
        --两个表关联查询时,不写连接条件,得到的结果即是笛卡尔积
        --假设两个表的记录条数分别是X和Y，笛卡尔积将返回X*Y条记录
    2)等值连接
        --通常在有主外键关联关系的表间建立，
            并将连接条件设定为有关系的列，使用等号”=”连接相关的表
    3) 内连接: 返回两个关联表中所有满足连接条件的记录
        ---例：SELECT e.ename, d.dname
                FROM emp e, dept d
                WHERE e.deptno = d.deptno
    4）外连接:返回那些不满足连接条件的记录
        --语法：
            SELECT table1.column, table2.column
            FROM table1 [LEFT | RIGHT | FULL] JOIN table2
            ON table1.column1 = table2.column2;
    5）全连接
        --全外连接是指除了返回两个表中满足连接条件的记录，
            还会返回不满足连接条件的所有其它行。
        --即是左外连接和右外连接查询结果的总和
        ---例：
            SELECT e.ename, d.dname
                FROM emp e FULL OUTER JOIN dept d
                ON e.deptno = d.deptno;
    6）自连接
        --特殊的连接查询，数据的来源是一个表，
        --即关联关系来自于单表中的多个列
        ---例如查出每个职员的经理名字，以及他们的职员编码
            SELECT *
            FROM emp worker join emp manager
            ON worker.mgr = manager.empno;

>>>>>>>>>>>>>>>>>>>8.oracle 高级查询<<<<<<<<<<<<<<<<<<
1.子查询
    --在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，
        而是来自于另外一个查询的结果。
        为了给查询提供数据而首先执行的查询语句叫做子查询
    --根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。
    ---例：查找薪水比整个机构平均薪水高的员工：
        SELECT deptno, ename, sal
        FROM emp e
        WHERE sal> (SELECT AVG(sal) FROM emp);
    1）EXISTS关键字
    ---例：
        SELECT deptno, dname FROM dept d
        WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno);
    2）子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分
    ---例：查询列出最低薪水高于部门30的最低薪水的部门信息：
        SELECT deptno, MIN(sal) min_sal
        FROM emp
        GROUP BY deptno
        HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);
    3）子查询在FROM部分
    --如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，
        这个子查询也称作行内视图或者匿名视图
    ---例：查询出薪水比本部门平均薪水高的员工信息
        SELECT e.deptno, e.ename, e.sal
        FROM emp e,
        (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
        WHERE e.deptno = x.deptno and e.sal>x.avg_sal
        ORDER BY e.deptno;
    4）子查询在SELECT部分
        --把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式
        SELECT e.ename, e.sal, e.deptno,
           (SELECT d.deptno FROM dept d 
            WHERE d.deptno = e.deptno)  deptno
        FROM emp e;
2. ORACLE 分页查询**重要**
    1）ROWNUM ：伪列,用于返回标识行数据顺序的数字,只能从1计数
        --如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图
            SELECT  * FROM 
              (SELECT  ROWNUMrn , e.* FROM emp e )  
             WHERE rn BETWEEN 8 AND 10;
    2)分页与order by 
        SELECT  * FROM 
           (SELECT  ROWNUMrn , t.* FROM 
           (SELECT empno,ename,sal FROM emp ORDER BY sal DESC) t)  
         WHERE rn BETWEEN 8 AND 10;
    3)使用子查询进行分页
        --PageN: (n - 1) * pageSize + 1 至 n * pageSize
3.DECODE函数
    --语法：DECODE(expr,search1,result1[,search2,result2…][,default])
    --比较参数expr的值，
        如果匹配到哪一个search条件，就返回对应的result结果，
        可以有多组search和result的对应关系，
        如果任何一个search条件都没有匹配到，则返回最后default的值。
    --default参数是可选的，如果没有提供default参数值，
        当没有匹配到时，将返回NULL。
    ---例：查询职员表，根据职员的职位计算奖励金额，
        当职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，
        奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，
        如果不是这三个职位，则奖励金额取薪水值：
        SELECT ename, job, sal,
            DECODE(job, 'MANAGER', sal * 1.2,
                           'ANALYST', sal * 1.1,
                          'SALESMAN', sal * 1.05,
                    sal
                 ) bonus
            FROM emp;
        --和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。
            SELECT ename, job, sal,
             CASE job WHEN 'MANAGER' THEN sal * 1.2
              WHEN 'ANALYST' THEN sal * 1.1
              WHEN 'SALESMAN' THEN sal * 1.05
              ELSE sal END
            bonus
            FROM emp;

    --DECODE函数在分组查询中的应用
    ---例如：计算职位的人数，analyst/manager职位属于vip，
        其余是普通员工operation
        SELECT DECODE(job, 'ANALYST', 'VIP', 
            'MANAGER', 'VIP', 'OPERATION') job,COUNT(1) job_cnt
        FROM emp
        GROUP BY 
        DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');

4. 排序函数
    4.1. ROW_NUMBER
    --语法：
        ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
    --表示根据col1分组，在分组内部根据col2排序。
        此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。
    --ROWNUM是伪列，
         ROW_NUMBER功能更强，可以直接从结果集中取出子集。
    --场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码
        SELECT *,ROW_NUMBER()
           OVER (PARTITION BY deptno ORDER BY empno) AS emp_id
           FROM emp;
    4.2. RANK
    --语法如下：
        RANK() OVER(PARTITION BY col1 ORDER BY col2)
    --表示根据col1分组，在分组内部根据col2给予等级标识，即排名，
         相同的数据返回相同排名。
    --特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，
         则两行数据都标记为2，但下一位将是第四名。
    --和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。
    --场景：按照部门编码分组，同组内按薪水倒序排序，
        相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示
        SELECT *,RANK() 
            OVER (PARTITION BY deptno ORDER BY sal DESC,comm) "Rank_ID"
               FROM emp；
    4.3. DENSE_RANK
    --语法：
        DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2)
    --表示根据col1分组，在分组内部根据col2给予等级标识，即排名，
        相同的数据返回相同排名。
    --特点是连续排序，如果有并列第二，下一个排序将是三，
        这一点是和RANK的不同，RANK是跳跃排序。
    --场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，
        列出员工的部门名字、姓名和薪水：
        SELECT d.dname, e.ename, e.sal, DENSE_RANK()
           OVER (PARTITION BY e.deptno ORDER BY e.sal) AS drank
           FROM emp e join dept d
           one.deptno = d.deptno;

5. 高级分组函数
    5.1. ROLLUP
    --ROLLUP、CUBE 和 GROUPING SETS 运算符是 GROUP BY 子句的扩展，
        可以生成与使用 UNION ALL 来组合单个分组查询时相同的结果集，
        用来简化和高效的实现统计查询。语法形式如下：
        --GROUP BY ROLLUP(a, b, c)
        --GROUP BY CUBE(a, b, c)
        --GROUP BY GROUPING SETS ( (a), (b))
    --假设有表test，有a、b、c、d四个列。
        SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);
    等价于：
        SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
        UNION ALL
        SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
        UNION ALL
        SELECT a,null,null,SUM(d) FROM test GROUP BY a
        UNION ALL
        SELECT null,null,null,sum(d) FROM test;
    --对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。对于n个参数的ROLLUP，有n+1次分组。
    --表－1 数据样例表
    --准备数据：
        SQL>DROP TABLE sales_tab;
        SQL>CREATE TABLE sales_tab (
            year_id   NUMBER NOT NULL,
            month_id   NUMBER NOT NULL,
            day_id   NUMBER NOT NULL,
            sales_value NUMBER(10,2) NOT NULL);
        SQL>INSERT INTO sales_tab
            SELECT TRUNC(DBMS_RANDOM.value(low => 2010, high => 2012)) AS year_id,
            TRUNC(DBMS_RANDOM.value(low => 1, high => 13)) AS month_id,
            TRUNC(DBMS_RANDOM.value(low => 1, high => 32)) AS day_id,
            ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
            FROM   dual
            CONNECT BY level <= 1000;
        SQL>COMMIT;
        --复习组函数的用法：
        SQL>SELECT SUM(sales_value) AS sales_value FROM   sales_tab;
        SQL>SELECT year_id, COUNT(*) AS num_rows,
            SUM(sales_value) AS sales_value
            FROM   sales_tab
            GROUP BY year_id
            ORDER BY year_id;
        SQL>SELECT year_id, month_id,
            COUNT(*) AS num_rows,
            SUM(sales_value) AS sales_value
            FROM   sales_tab
            GROUP BY year_id, month_id
            ORDER BY year_id, month_id;
    --ROLLUP函数的用法：
        SELECT year_id, month_id, 
        SUM(sales_value) AS sales_value
        FROM   sales_tab
        GROUP BY 
        ROLLUP (year_id, month_id)
        ORDER BY year_id, month_id;

        SELECT year_id,month_id,day_id,SUM(sales_value) AS sales_value
        FROM   sales_tab
        GROUP BY ROLLUP (year_id, month_id, day_id)
        ORDER BY year_id, month_id, day_id;

    5.2. CUBE
        --CUBE函数的语法形式：
            GROUP BY CUBE(a, b, c)
        --每个参数可以理解为取值为参与分组和不参与分组两个值的一个维度，
            所有维度取值组合的集合就是分组后的集合。
            对于n个参数的cube，有2^n次分组。
        如果GROUP BY CUBE(a,b,c)，
        首先对(a,b,c)进行GROUP BY，
        然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，
        最后对全表进行GROUP BY操作，所以一共是2^3=8次分组。
        SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);
    --等价于：
        SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
        UNION ALL
        SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b
        UNION ALL
        SELECT a,NULL,c,SUM(d) FROM test GROUP BY a,c
        UNION ALL
        SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a
        UNION ALL
        SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c
        UNION ALL
        SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b
        UNION ALL
        SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c
        UNION ALL
        SELECT NULL,NULL,NULL,SUM(d) FROM test ;
    --等价于只是方便理解，其内部运行机制并不相同，其效率远高于UNION ALL。
    --在sales_value表中使用cube函数：
        SELECT year_id, month_id,
        SUM(sales_value) AS sales_value
        FROM   sales_tab
        GROUP BY CUBE (year_id, month_id)
        ORDER BY year_id, month_id;

        SELECT year_id,  month_id, day_id,
        SUM(sales_value) AS sales_value
        FROM   sales_tab
        GROUP BY CUBE (year_id, month_id, day_id)
        ORDER BY year_id, month_id, day_id;
        1.5.3. GROUPING SETS

        --GROUPING SETS运算符可以生成与使用单个GROUP BY ROLLUP或CUBE 
            运算符所生成的结果集相同的结果集，但是使用更灵活。
            如果不需要获得由完备的ROLLUP或CUBE运算符生成的全部分组，
            则可以使用GROUPING SETS仅指定所需的分组。
        --GROUPING SETS 列表可以包含重复的分组。
            GROUPING SETS示例：
            SELECT year_id, month_id, SUM(sales_value)  
            FROM sales_tab
            GROUP BY CUBE (year_id,month_id)
            order by 1, 2; 
            SELECT year_id, month_id, SUM(sales_value)  
            FROM sales_tab
            GROUP BY GROUPING SETS (  (year_id), (month_id))
            order by 1, 2
        --其中分组方式示例如下：
            --使用GROUP BY GROUPING SETS(a,b,c)，
                则对(a),(b),(c)进行GROUP BY
            --使用GROUP BY GROUPING SETS((a,b),c), 
                则对(a,b),(c)进行GROUP BY
            --GROUPING BY GROUPING SET(a,a) , 
                则对(a)进行2次GROUP BY,GROUPING SETS的参数允许重复
6. 集合操作
    6.1. UNION、UNION ALL
    --为了合并多个SELECT语句的结果，可以使用集合操作符，
        实现集合的并、交、差。
    --集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。
    --多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。
    --ORDER BY子句只能放在最后的一个查询语句中。
    --语法如下：
        SELECT statement1
        [UNION | UNION ALL | INTERSECT | MINUS]
        SELECT statement2;
    --UNION和UNION ALL用来获取两个或两个以上结果集的并集：
    --UNION操作符会自动去掉合并后的重复记录。
    --UNION ALL返回两个结果集中的所有行，包括重复的行。
    --UNION操作符对查询结果排序，UNION ALL不排序。
    --例:合并职位是’MANAGER’的员工和薪水大于2500的员工集合，
        查看两种方式的结果差别：
    --Union
        SELECT ename, job, sal FROM emp
        WHERE job = 'MANAGER'
        SELECT ename, job, sal FROM emp
        WHERE sal> 2500;
    --Union all
        SELECT ename, job, sal FROM emp
        WHERE job = 'MANAGER'
        SELECT ename, job, sal FROM emp
        WHERE sal> 2500;
    6.2. INTERSECT (intersect)
    --INTERSECT函数获得两个结果集的交集，
        只有同时存在于两个结果集中的数据，才被显示输出。
    --使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。
    ---例:显示职位是’MANAGER’的员工和薪水大于2500的员工的交集：
        SELECT ename, job, sal FROM emp
        WHERE job = 'MANAGER'
        INTERSECT
        SELECT ename, job, sal FROM emp
        WHERE sal> 2500;
    6.3. MINUS
    --MINUS函数获取两个结果集的差集。
    --只有在第一个结果集中存在，在第二个结果集中不存在的数据，
        才能被显示出来。也就是结果集一减去结果集二的结果。
    ---例：列出职位是MANAGER但薪水低于2500的员工记录：
        SELECT ename, job, sal FROM emp
        WHERE job = 'MANAGER'
        MINUS
        SELECT ename, job, sal FROM emp
        WHERE sal> 2500;

转载于:https://my.oschina.net/u/3010171/blog/812664