Oracle笔记2语法

列别名
当SELECT子句中一个字段是函数或者表达式，
那么在结果集中该字段的名字就是这个函数
或表达式，可读性差，为此可以为列提供一个
别名，这样在结果集中会使用该别名作为该
字段的名字。应当养成一个好习惯，即:凡是
表达式或函数都指定一个别名。


别名本身不区分大小写，若希望别名区分大小
写或者别名中包含空格，那么需要使用双引号
将其括起来。
SELECT ename,sal+NVL(comm,0) "sal"
FROM emp




OR,AND用于连接多个条件使用
OR的优先级低于AND，所以若希望提高OR
的优先级，可以使用括号。


查看工资高于1000，并且职位是
CLERK或者SALESMAN的员工?
SELECT ename,job,sal
FROM emp
WHERE sal>1000
AND (job='SALESMAN'
OR job='CLERK')






LIKE用于模糊匹配字符串
支持两个通配符:
%:0-多个字符(任意个字符)
_:单一的一个字符


查看名字第二个字母是A的员工?
SELECT ename,sal,deptno
FROM emp
WHERE ename LIKE '_A%'




IN(list) 和 NOT IN(listr)
判断在列表中和不在列表中
IN,NOT IN常被用在子查询上使用。


查看职位是CLERK和SALESMAN的员工?
SELECT ename,job,sal
FROM emp
WHERE job IN('CLERK','SALESMAN')






BETWEEN...AND...
判断在一个区间范围内


查看工资在1500到3000之间的员工?
SELECT ename,sal
FROM emp
WHERE sal BETWEEN 1500 AND 3000


查看工资在1000-3000之间，20和30号部门
名字中含有A的员工?
SELECT ename,sal,deptno,job
FROM emp
WHERE sal BETWEEN 1000 AND 3000
AND deptno IN (20,30)
AND ename LIKE '%A%'




ANY(list),ALL(list)
ANY,ALL是配合>,>=,<,<=使用的
>ANY(list):大于列表之一，大于最小
>ALL(list):大于列表所有，大于最大
<ANY(list):小于列表之一，小于最大
<ALL(list):小于列表所有，小于最小
ANY,ALL的列表中不会给定固定值，没有
实际意义，它们通常配合子查询使用。


SELECT empno, ename, job, 
       sal, deptno
FROM emp
WHERE sal>ANY(3500,4000,4500)




DISTINCT关键字
DISTINCT可以将结果集中指定字段值一样
的记录去重。


查看公司有哪些职位?
SELECT DISTINCT job FROM emp


可以按照多字段去重，那么这些字段值的组合
没有重复记录出现在结果集中。
SELECT DISTINCT deptno,job FROM emp






ORDER BY子句
ORDER BY用于对查询的结果集按照指定
字段的值进行排序。
排序分为:升序(ASC),降序(DESC).
默认为升序
ORDER BY子句必须定义在SELECT语句中
的最后一个子句上。


查看公司中工资的排名？
SELECT ename,sal,job
FROM emp
ORDER BY sal DESC


查看20号部门的工资排名?
SELECT ename,sal,deptno
FROM emp
WHERE deptno=20
ORDER BY sal DESC


多字段排序时，ORDER BY是有排序的
优先级的，首先按照第一个字段先进行
排序，当该字段中有记录值重复时，再
将这些记录按照第二个字段排序。依此
类推。


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


若排序的字段中含有NULL值，NULL被
认定为最大值。
SELECT ename,comm
FROM emp
ORDER BY comm




聚合函数
聚合函数又称为分组函数，多行函数。
聚合函数的作用是对结果集指定字段
的值进行统计工作的。
MAX(),MIN(),SUM(),AVG()
求最大值，最小值，总和，平均值


还有一个是对记录数的统计
COUNT()




查看公司的最高工资是多少?
SELECT MAX(sal) FROM emp


SELECT MAX(sal),MIN(sal),
       SUM(sal),AVG(sal) 
FROM emp


查看公司共多少人?
SELECT COUNT(ename) FROM emp
SELECT COUNT(*) FROM emp
SELECT COUNT(1) FROM emp


聚合函数忽略NULL值。
SELECT SUM(comm),AVG(comm)
FROM emp


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




分组
GROUP BY子句
GROUP BY子句可以将结果集按照给定的
字段值一样的记录进行分组。配合聚合函
数使用可以完成分组统计工作。


查看每个部门的平均工资?
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno


当SELECT中含有聚合函数时，那么凡不在
聚合函数中的单独字段，都需要出现在GROUP BY
子句中。


查看每种职位的最高工资与最低工资是多少?
SELECT MAX(sal),MIN(sal),job
FROM emp
GROUP BY job


GROUP BY按照多字段分组时，是将
这些字段值组合一样的记录看做一组。




统计同部门同职位的员工的各多少人?
SELECT COUNT(*),deptno,job
FROM emp
GROUP BY deptno,job




查看部门的平均工资，前提是该部门的
平均工资高于2000
SELECT AVG(sal),deptno
FROM emp
WHERE AVG(sal)>2000
GROUP BY deptno


上面的SQL语句会抛出异常，WHERE子句中
是不能使用聚合函数做为过滤条件的，原因
在于过滤的时机不对。
WHERE是伴随查询表中数据逐行进行过滤使用
的，用来确定结果集中可以查询出的条目数。
而聚合函数是建立在查询出来的数据基础上进行
统计才能得到结果，所以使用聚合函数过滤应当
是在WHERE之后进行的。
HAVING子句可以根据聚合函数进行过滤，并且
是用来根据条件过滤 GROUP BY的分组。
HAVING不能单独出现，不定义GROUP BY子句不能
单独出现HAVING子句。


查看部门的平均工资，前提是该部门的
平均工资高于2000
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000




查看平均工资高于2000的那些部门的最高
工资是多少?
SELECT MAX(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000


关联查询
关联查询的结果集中的字段来自多张
表。这就需要在查询的时候联合多张
表进行查询，根据给定的连接条件将
表与表的数据建立对应关系，再分别
从这些记录中提取要查询的字段来形
成结果集中的每条记录。


查看每个员工的名字以及其所在部门的名字?
SELECT ename,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno


当在关联查询中要查询的字段在这些表中
都出现时，需要使用表名来明确字段所属表。
可以为表名取一个别名来简化复杂度。
SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno


在进行关联查询时若有过滤要求，那么过滤
条件必须与连接条件同时成立。
查看在NEW YORK工作的员工都有谁?
SELECT e.ename,e.deptno,
       d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK'




关联查询中不指定连接条件或者连接条件
无效时，会产生笛卡尔积，这通常是一个
无意义的结果集。
SELECT e.ename,d.dname
FROM emp e, dept d


N表查询至少要有N-1个连接条件。




内连接
内连接也是用来完成关联查询的。
采用JOIN...ON...的形式关联多表。


查看每个员工的名字以及其所在部门名称?
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno


内连接写法:
SELECT e.ename,d.dname
FROM emp e 
JOIN dept d
ON e.deptno = d.deptno


不满足关联条件的记录不会被查询出来。


外链接
外链接在关联查询中除了可以将满足连接条件
的记录查询出来之外，还可以将不满足连接条
件的记录也列出来。
外链接分为:
左外连接，右外连接，全外连接
左外连接:以JOIN左侧表做为驱动表,该表记录都要
查询出来，当某条记录不满足连接条件时，那么在
结果集中该条记录来自JOIN右侧表的字段的值全部
为NULL。


SELECT e.ename,e.job,e.sal,
       d.dname,d.loc
FROM emp e 
 LEFT|RIGHT|FULL OUTER JOIN 
dept d
ON e.deptno=d.deptno




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




自连接
自连接指的是当前表的一条数据可以对应
当前表自己的多条数据。
自连接是为了解决数据的属性相同，但是
本身又存在上下级关系的树状结构数据。


查看每个员工以及其领导的名字?
SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+)


SELECT e.ename,m.ename
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno