Oracle数据基础(二)

一. SQL基础查询

基本查询语句

    1. FROM子句

          SELECT * FROM TABLE;

    2. 使用别名

    3. WHERE子句

          WHERE子句中使用比较操作符限制查询结果

    4. SELECT子句

查询条件

    1. 使用>,<,>=,<=,!=,<>,=

          数据库中标准的不等于：<>

    2. 使用AND，OR关键字

               SELECT * FROM table WHERE sal > 1000 AND job = ‘clerk’;

               SELECT * FROM table WHERE sal > 1000 OR job = ‘clerk’;

          同时使用AND和OR时，AND的优先级高于OR

    3. 使用LIKE条件(模糊查询)

          LIKE需要借助两个通配符：

               — %：表示0到多个字符

               — _：标识单个字符

               SELECT name, job FROM amp WHERE name LIKE ‘_A%’;—单词的第二个字符是A后面未知 

    4. 使用IN和NOT IN

          比较操作符IN(list)用来取出符合列表范围中的数据

          List表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来    

          IN可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表

          NOT IN(list)取出不符合此列表中的数据记录

    5. BETWEEN…AND...

          BETWEEN…(小的值)AND...(大的值)：操作符用来查询符合某个值域范围条件的数据

          最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用

               SELECT name, sal FROM amp WHERE sal BETWEEN 1000 AND 2000;

    6. 使用IS NULL和IS NOT NULL

          NULL比较时不能使用”=“，必须使用IS

    7. 使用ANY和ALL条件

          ALL和ANY不能单独使用，需要配合单行比较操作符>,<=,<,<=一起使用

               > ANY：大于最小

               < ANY：小于最大

               > ALL：大于最大

               < ALL：小于最小

               eg：SELECT name, job sal FROM amp WHERE sal > ANY (2000,3400,4000);

          与IN的相同之处：给定一组数据进行比较

          区别：IN是与给定的数据进行等值或不等值比较

                    ANY和ALL是与给定数据进行范围比较

    8. 查询条件中使用表达式和函数

          查询条件中可以使用算术表达式：+, -, * ,/。优先级符合四则运算默认优先级，可使用括号改变优先级

    9. 使用DISTINCT过滤重复

          对多列去重，可以达到的效果是，这几列的组合是不重复的。

          SELECT DISTINCT deptno from table ;—去掉重复值

排序

    1. 使用ORDER BY子句

          对数据按一定规则进行排序操作，使用ORDER BY子句

          必须出现在SELECT中的最后一个子句

    2. ASC和DESC

          ASC：升序

          DESC：降序

    3. 多列排序

          使用多列进行排序时，左边的列排序优先级高于右面的列

          eg：SELECT name,sal,deptno FROM emp ORDER BY sal,deptno DESC;

          首先按照sal的升序排列，当sal的值相同时，按照deptno的降序排列。若sal的值全表没有重复值，则第二列的排序会被忽略。

聚合函数

    1. 聚合函数

          多行数据参与运算返回一行结果，也称作分组函数，多行函数，集合函数。

    2. MAX和MIN

          用来取得列或表达式的最大，最小值

          可用于统计任何数据类型，包括数字，字符和日期

               SELECT MAX(sal) max_sal, MIN(sal) min_sal FROM emp;

          MAX和MIN是不能出现在WHERE语句中的。

    3. AVG和SUM

          聚合函数忽略空值。所以得到的平均值会少数据。

               eg：SELECT AVG(NVL(comm, 0)) FROM amp;—若为空值则使其值为0

    4. COUNT

          用于统计记录条数

          SELECT COUNT(comm) FROM amp;—忽略空值，若字段comm为kong则不记录在内

    5. 聚合函数对空值的处理

分组

    1. GROUP BY子句

          SELECT MAX(sal), MIN(sal), SUM(sal), AVG(sal) FROM emp GROUP BY deptno; —根据部分分组，得到每组的最高薪资，最低薪资，平均薪资，总薪资

          根据值相同的记录作为一组，进行统计；

          一般对部分数据重复的值进行分组，没有重复的数据分组没有意义；

          只要在SELECT中使用了分组函数，那么，SELECT中其它非分组函数的列若出现，则必须同时出现在GROUP BY子句中，反过来没有限制。

          若GROUP BY中出现了多列，那么就按照这几列组合值相同的记录看作一组。

    2. 分组查询

    3. HAVING字句

          WHERE是用于整张表逐行过滤用的；

          HAVING用于在进行分组查询后，二次过滤数据的；

          HAVING中可以使用分组函数的结果进行过滤；

          HAVING不能独立存在，必须跟在GROUP BY之后。

          eg：筛选出部门平均工资高于1800的部门：SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 1800

    SQL顺序：SELECT —> FROM —> WHERE —> GROUP BY —> HAVING —> ORDER BY

查询语句执行顺序

    1. 查询语句执行顺序（可以改善代码执行效率）

          查询语句的执行顺序依下列子句次序：

          1.1 from子句：执行顺序为从后往前，从右到左

               数量较少的表尽量放在后面

          1.2 where子句：执行顺序为自下而上，从右到左

               将能过滤掉最大数量记录的条件写在where子句的最右

          1.3 group by 执行顺序从左往右分组

               最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉

          1.4 having子句：消耗资源

               尽量避免使用，HAVING会在检索出所有记录之后才对结果进行过滤，需要排序等操作

          1.5 select子句：少用*号，尽量取字段名称

               ORACLE在解析的过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间

          1.6 order by子句：执行顺序为从左到右排序，消耗资源

二. SQL关联查询

关联基础

    1. 关联的概念

          查询两个或两个以上数据表或视图的查询叫做连接查询

          连接查询通常建立在存在相互关心的父子表之间

    2. 笛卡尔积

          笛卡尔积指做关联操作的每个表的每一行都和其它表的每一行做组合，假设两个表的记录条数分别是X和Y，笛卡尔积将返回X*Y条记录。

          两张表不加连接条件关联时会产生笛卡尔积。

    3. 等值连接

          SELECT table1.column, table2.column FROM table1, table2 where table1.column = table2.column;（column分别为table1和table2的主键和外键）

          NATURAL JOIN：自动寻找两张表列名相同的字段（且这两个字段互为主外键关系时）做等值连接。注意：两张表中应当只有一列名字相同才可以使用自然连接

关联查询

    1. 内连接

    2. 外连接

          应用场景：A表中某个字段的值在B表中相应该字段的值找不到时使用

          SELECT table1.column, table2.column FROM table1 LEFT | RIGHT | FULL | OUTER  JOIN  table2 ON table1.column = table2.column2;

          LEFT：左边表为准，右边表不足补齐；

          RIGHT：右边表为准，左边表不足补齐

    3. 全外连接

          左边表不足补齐，右边表不足补齐

    4. 自连接

          最常用于员工上下级关系

          将一张表当成两张表

          eg：SELECT e.name||’的领导是’||m.name FROM amp e, emp m WHERE e.mgr = m.empno;

三. SQL高级查询

子查询

当前查询需要建立在另一个查询的结果基础之上，这里就要利用到子查询

    1. 子查询在WHERE子句中

          在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另一个查询的结果

          为了给查询提供数据而首先执行的查询语句叫做子查询

          子查询是嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中

          子查询嵌入的语句称作主查询或父查询

          主查询可以是SELECT语句，也可以是其它类型的语句比如：DML或DDL

          根据返回结果不同，子查询可以分为单行子查询，多行子查询及多列子查询

          单行单列和多行单列子查询通常用在WHERE子句中作为条件

          多行多列子查询通常用在FROM子句中

          >, <, >=, <=, =, <>这些比较都只能使用单行单列子查询

          比较多行单例子查询时可以使用ANY或ALL或IN

               eg：SELECT name, sal FROM amp WHERE sal > ANY(SELECT sal FROM amp WHERE job = ’SALESMAN’);--大于其中之一

                      SELECT name, sal FROM amp WHERE sal > ALL(SELECT sal FROM amp WHERE job = ’SALESMAN’);--大于最大

                      SELECT name, sal FROM amp WHERE sal > IN(SELECT sal FROM amp WHERE job = ’SALESMAN’);--等于其中之一

          在子查询中需要引用到主查询的字段数据，使用EXISTS关键字

          EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE

               eg：查询比本部门平均工资高的那些员工

                         SELECT e.1deptno, e1.name, e1.sal FROM emp e1

                         WHERE e1.sal > (SELECT AVG(sal) FROM emp e2 WHERE e1.deptno = e2.deptno);

    2. 子查询在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语句中，出现了非字段名的字段，通常是表达式或者函数，那么一定要给他们加上别名。

               eg：查询出薪水比本部门薪水高的员工信息：

               SELECT e.deptno, e.name, 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部分

          外连接的另一种写法，不常用

               eg：查询姓名，薪资，部门名

               SELECT name, sal, (SELECT name FROM deptno d WHERE d.deptno = e.deptno) FROM amp e;  

分页查询

    1. ROWNUM

          被称作伪列，用于返回标识行数据顺序的数字；

          只能从1计数，不能从结果集中直接截取

          ROWNUM是当oracle进行select进行select当查询表数据时，确定一条数据需要时，才会对其进行编号（伪劣的值），这就导致，没有数据被查出前，rownum永远没有值。所以rownum不能在第一次查询时作为where条件。

          rownum：有数据了才有值

          where里出现：有值才有数据

               eg：SELECT ROWUM,empno,name,sal FROM emp WHERE rownum > 3;--查询不到结果

    2. 使用子查询进行分页

         分页三步：

          a. 排序

          b. 编号

          c. 取范围

               eg：

                    a. SELECT empno, name, sal FROM emp ORDER BY empno;

                    b. SELECT ROWNUM rw, e.* FROM () e;

                    c. SELECT * FROM () WHERE rw BETWEEN 1 AND 3;

                     SELECT * FROM (SELECT ROWNUM rw, e.* FROM (SELECT empno, name, sal FROM emp ORDER BY empno) e) WHERE rw BETWEEN 1 AND 3;

                    分页算法：page页数，pagesize一页的条数

                    起始位置：(page-1)*pagesize+1

                    结束位置：page*pagesize

                    MYSQL：limit(1,3)

    3. 分页与ORDER BY

DECODE函数

    1. DECODE函数基本语法

          DECODE(expr, search2, result1[, search3, result2…][,default])

          DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果

          可以有多组search和result对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值

          default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL

    2. DECODE函数在分组查询中的应用

          eg：查询职员表，根据职员的职位计算奖金金额，当职位为MANAGER，ANALYST，SALESMAN时，奖励金额分别是薪水的1.2倍，1.1倍，1.05倍，如果不是这三个职位，则奖励金额取薪水值

          SELECT name, 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 name, 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用在ORDER BY时，可以自定义排序规则：

          SELECT * FROM dept ORDER BY DECODE(name, ‘OPERATIONS’, 0, ‘ACCOUNTING’, 1, ’SALES’, 2, 3);

排序函数

    1. ROW_NUMBER

          ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)

          表示根据col1分组，在分组内部根据col2排序

          此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一

          Rownum是伪列，ROW_NUMBER功能更强，可以直接从结果集中取出子集

          eg：根据部门分组，部门内薪资倒序，部门内生成连续且唯一编号

               SELECT name, sal, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

    2. RANK

          生成组内不连续且不唯一的编号，排序的列若值相同，会得到相同的编号

          不连续排序，如果有并列第二，下一个排序将是四

          eg：根据部门分组，部门内薪资倒序，部门内生成不连续且不唯一编号

               SELECT name,sal,deptno RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

    3. DENSE_RANK

          生成组内连续但不唯一的编号，排序的列若值相同，会得到相同的编号

          连续排序，如果有并列第二，下一个排序将是三，这一点和RANK的不同，RANK是跳跃排序

          eg：根据部门分组，部门内薪资倒序，部门内生成连续但不唯一编号

               SELECT name,sal,deptno DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

集合操作

    1. UNION，UNION ALL

          为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并，交，差

          集合操作符包括UNION，UNION ALL，INTERSECT和MINUS

          多条作集合的SELECT语句的列的个数和数据类型必须匹配

          ORDER BY子句只能放在最后的一个查询语句中

          集合操作的语法：

               SELECT statement1 [UNION | UNION ALL |INTERSECT | MINUS] SELECT statement2;

          UNION:statement1和statement2重复的结果合并，重复的结果出现一次

          UNION:statement1和statement2重复的结果不合并，重复的结果有几个就出现几次

    2. INTERSECT

          获得两个结果的交集，只有同时存在于两个结果集中的数据才被显示输出

          使用INTERSECT操作符后的结果集会以第一列的数据作升序排序

    3. MINUS

          获取两个结果集的差集

          只有在第一个结果集中存在，在第二结果集中不存在的数据才会被显示出来，也就是结果集一减去结果集二的结果

高级分组函数

    1. ROLLUP

          eg：计算出每月总和，每年总和

          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;

    2. CUBE

          GROUP BY CUBE(a,b,c)

          对CUBE的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合

          对于n个参数的CUBE，组合形式有2^n次，如果是a，b，c则是2^3种组合

          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次分组

          eg：SELECT a,b,c,SUM(D) FROM test GROUP BY CUBE(a,b,c)等价于：(等价于，但是内部运行机制不同于UNION ALL，其效率远高于UNION ALL)

                  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 ;

    3. GROUPING SETS

          GROUPING SETS运算符可以生成与使用单个GROUP BY，ROLLUP或CUBE运算符所生成的结果集相同的结果集

          如果不需要获得由完备的ROLLUP或CUBE运算符生成的全部分组，则可以使用GROUPING SETS仅指定所需的分组

          GROUPING SETS列表可以包含重复的分组，即允许参数重复

          eg：GROUP BY GROUPING SETS(a,a)，则对a进行2次GROUP BY

          eg：计算出每年每月，每年每月每日的统计

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