SQL基础三(Oracle)

1、列别名

当SELECT子句中查询的列是一个函数或者表达式时，那么查询出来的结果集中对应的该字段的名字就是这个函数或者表达式的名字。为此可以为这一列添加别名，这样结果集中该字段就使用别名作为该列的名字。若希望别名区分大小写或者含有空格，那么该别名可以使用双引号括起来。

SELECT ename,sal*12 as "s al" FROM emp;

2、AND，OR AND优先级高于OR,可以通过括号提高优先级:

SELECT ename,job,sal FROM emp WHERE sal>1000 AND job='SALESMAN' OR job='CLERK';

3、LIKE可以模糊匹配字符串【Oracle和Mysql通用】:支持两个通配符:%:任意个字符(0-多个)；_:单一的一个字符

ex:查看名字第二个字母是A的员工:

SELECT ename,sal,deptno FROM emp WHERE ename LIKE '_A%';

4、IN(list)与NOT IN(list)判断在列表中或者不在列表中。常用在判断子查询的结果。

5、BETWEEN…AND… 判断在一个区间范围内

ex:查看工资在1500到3000之间的员工信息

SELECT ename,sal,deptno FROM emp WHERE sal BETWEEN 1500 AND 3000;

6、ANY,ALL 配合>,>=,<,<=判断使用，判断一个列表中的内容是否满足要求：

ANY(list):大于列表之一即可(大于最小),
ALL(list):大于列表所有(大于最大)
ANY,ALL通常用在子查询结果的判断上。

7、DISTINCT关键字对结果集指定字段值重复的记录行去除。

SELECT DISTINCT job FROM emp;

-- 对多列去重是指:这这些列的值的组合没有重复
SELECT DISTINCT job,deptno FROM emp

8、ORDER BY子句，用于结果集排序,ORDER BY只能写在DQL的最后一个子句中,ORDER BY可以对结果集按照给定字段的值,进行升序(ASC)或降序(DESC)进行排序 默认升序（ASC）

ex：查看公司的工资排名?

SELECT ename,sal FROM emp ORDER BY sal DESC;

8.1、多字段排序是有优先级的，首先按照第一个字段的排序规则对结果集排序，当第一个字段有重复值时再按照第二个字段的值排序，以此类推。

SELECT ename,deptno,sal FROM emp ORDER BY deptno,sal DESC;

8.2、排序的字段若含有NULL值，NULL被认作最大值。

SELECT ename,comm FROM emp ORDER BY comm;

9、聚合函数聚合函数也称为:多行函数，分组函数聚合函数是用来统计结果集中的数据的四个对值进行统计的函数:MAX | MIN | AVG | SUM；一个对记录数统计的函数:COUNT

ex:统计公司工资的最大值与最小值

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

ex:公司的工资总和和平均值？

SELECT SUM(sal),AVG(sal) FROM emp;

ex:查看公司总共多少人?

SELECT COUNT(ename) FROM emp;

9.1、聚合函数忽略NULL值。**

SELECT SUM(comm),AVG(NVL(comm,0)) FROM emp;
SELECT NVL(comm,0) FROM emp;

统计一张表记录总数常用:

SELECT COUNT(*) FROM emp;
SELECT AVG(sal) FROM emp WHERE deptno=20;

10 、分组 GROUP BY

10.1、GROUP BY子句可以将结果集按照指定字段进行分组，分组原则为该字段值一样的记录看作一组，配合聚合函数可以进行细致的统计。分组的字段必须在查询的字段中,聚合字段不能在Group by 后可以在HAVING 后

ex:查看每个部门的平均工资?

SELECT AVG(sal),deptno FROM emp GROUP BY deptno;

查看每个职位的平均工资与工资总和?

SELECT AVG(sal),SUM(sal),job FROM emp GROUP BY job;

10.2、GROUP BY可以按照多字段分组，分组原则为这几个字段值的组合相同的记录看作一组

ex:查看同部门同职位的员工各多少人?

SELECT COUNT(*),deptno,job FROM emp GROUP BY deptno,job;

ex:查看部门的平均工资，前提是该部门平均工资要高于2000

--错误的sql
SELECT AVG(sal),deptno FROM emp WHERE AVG(sal)>2000 GROUP BY deptno ;
--正确的sql
SELECT AVG(sal),deptno FROM emp  GROUP BY deptno HAVING AVG(sal)>2000;

WHERE中不能使用聚合函数作为过滤条件，原因在于过滤时机不对。WHERE是在第一次查询表中数据进行过滤的，逐行过滤，将满足条件的记录查询出来。聚合函数是建立在结果集基础上进行统计的。这已经是在WHERE之后进行了

11、HAVING子句

11.1、HAVING子句必须跟在GROUP BY子句之后，用来对GROUP BY分组后对每一个分组进行过滤。HAVING可以使用聚合函数并根据结果来取舍分组。

ex:查看部门的平均工资，前提是该部门平均工资要高于2000

SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

ex:查看平均工资高于2000的部门的最高工资与最低工资分别是多少?

SELECT MAX(sal),MIN(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

ex:查看平均工资高于2000的职位各多少人?

SELECT COUNT(*),job FROM emp GROUP BY job HAVING AVG(sal)>2000;

12、查询语句执行顺序依照以下顺序执行：

12.1、from子句：执行顺序为从后往前，从右到左，建议将数量较少的表尽量房后面

12.2、where子句：执行顺序为自上而下，从右到左，建议将能过滤掉最大记录的条件写在 where子句的最后

12.3、group by子句：执行顺序为从左往右分组，最好在group by前用where将不需要的记录在group by 之前过滤掉；

12.4、having 子句：消耗资源，尽量避免使用，having会在检索出所有记录之后，才会对结果集进行过滤，需要排序等操作；

12.5、select子句：少用 *，尽量取字段名字，Oracle在解析的过程中，通过查的数据字典将*号依次转换成所有的列名，消耗时间

12.6、order by 子句：执行顺序从左到右排序消耗资源

13、关联查询（Oracle和MySQL通用）：关联查询的结果集中字段来自多张表联合查询的结果。关联查询的关键在于连接条件，即:表之间数据的对应关系。

ex:查看每个员工以及其所在部门的名称?

SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;

13.1、关联查询中连接条件要与过滤条件同时成立

ex:查看在NEW YORK工作的员工?

SELECT e.ename,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND d.loc='NEW YORK';

13.2、关联查询中不添加链接条件或链接条件无效则会出现笛卡儿积，这通常是一个无意义的结果集。笛卡儿积的记录数是由参与查询的表的记录数乘积得到。

--错误的示范
SELECT d.deptno,d.dname FROM dept d

--不满足连接条件的记录是不会被查询出来的。
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;

13.3、等值连接：连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设为有联系的列，使用“=”连接相关的表

ex:查看KING在哪个城市工作?

SELECT e.ename,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND e.ename='KING';

13.4、关联查询中N张表关联查询，至少要有N-1个连接条件。

13.4.1内连接:内连接也是用来完成关联查询的。内连接返回所有满足连接条件的记录

SELECT e.ename,d.loc FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE e.ename='KING';

13.4.2、外连接:外连接在进行关联查询时除了可以将满足连接条件的记录查询出来之外，也可以将不满足连接条件的记录列出来。外连接分为:左外连接，右外连接，全外连接

左外连接:以JOIN左侧表作为驱动表(所有记录都会列出来)，那么当驱动表中不连接条件的记录来自右侧表中的字段值为NULL。

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

MySQL 内连接、外链接以及全连接的实现

13.4.3、通过“+”实现的外连接[Oracle]

左条件 = 右条件(+) 右条件所在的表必须严格进行相等连接条件的匹配,而左条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据! 也称为左外连接.

SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno(+);

左条件(+) = 右条件 左条件所在的表必须严格进行相等连接条件的匹配,而右条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据! 也称为右外连接.

SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno(+)=d.deptno;

13.4.4、Oracle 建议你用在From语句后用Outer Join语法而不是Oracle的Join操作符(+).而且(+)是要受下面的规则限制的，但Outer Join语法则不受的：

1）你不可以在查询块中使用(+) 当它同时包含 join的from语句中
2）(+)只是在where语句中,并且只能对应一个表或视图的一行字段
3）如果A和B做联接时有多个条件，那么(+)必须完善所有的匹配条件，如果没有 ,oracle不会警告你只是结果自然不同的
4）不可以在作出一个表外查询另张表内查询的(+)联接操作
5）不可以用（+）外联接到自己当然Self Join是可以的
6）含（+）的Where后的注意： OR不可用 IN不可用子查询不可用

13.4.5、自连接

当前表的一条记录可以对应当前表自己的多条记录这样的设定就是自连接。自连接保存属性相同的数据但是之间有存在；上下级关系的树状结构数据使用。

SELECT empno,ename,mgr FROM emp;

ex:查看每个员工的名字以及其上司的名字?

SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno;