ORACLE Unit03 SQL（基础查询） 、 SQL（关联查询）

列别名

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

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

AND，OR

AND优先级高于OR,可以通过括号提高优先级。

SELECT ename,job,salFROM empWHERE sal>1000AND job='SALESMAN'OR job='CLERK'

LIKE可以模糊匹配字符串

支持两个通配符:
%:任意个字符(0-多个)
_:单一的一个字符

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

SELECT ename,sal,deptnoFROM empWHERE ename LIKE '_A%'

IN(list)与NOT IN(list)

判断在列表中或者不在列表中。
常用在判断子查询的结果。

BETWEEN…AND…

判断在一个区间范围内

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

SELECT ename,sal,deptnoFROM empWHERE sal BETWEEN 1500 AND 3000

ANY,ALL

配合>,>=,<,<=判断使用，判断一个列表中的内容是否满足要求
ANY(list):大于列表之一即可(大于最小)
ALL(list):大于列表所有(大于最大)
ANY,ALL通常用在子查询结果的判断上。

DISTINCT关键字

对结果集指定字段值重复的记录行去除。

SELECT DISTINCT job FROM emp

对多列去重是指:这这些列的值的组合没有重复

SELECT DISTINCT job,deptno FROM emp

ORDER BY子句，用于结果集排序

ORDER BY只能写在DQL的最后一个子句中
ORDER BY可以对结果集按照给定字段的值
进行升序(ASC)或降序(DESC)进行排序

查看公司的工资排名?

SELECT ename,salFROM empORDER BY sal DESC

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

SELECT ename,deptno,salFROM empORDER BY deptno,sal DESC

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

SELECT ename,commFROM empORDER BY comm 

聚合函数

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

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

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

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

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

查看公司总共多少人?

SELECT COUNT(ename)FROM emp

聚合函数忽略NULL值。

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

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

SELECT COUNT(*)FROM empSELECT AVG(sal) FROM empWHERE deptno=20

分组

GROUP BY子句

GROUP BY子句可以将结果集按照指定字段进行分组，分组原则为该字段值一样的记录看作一组，配合聚合函数可以进行细致的统计。

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

SELECT AVG(sal),deptnoFROM empGROUP BY deptno

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

SELECT AVG(sal),SUM(sal),jobFROM empGROUP BY job

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

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

SELECT COUNT(*),deptno,jobFROM empGROUP BY deptno,job

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

SELECT AVG(sal),deptnoFROM empWHERE AVG(sal)>2000GROUP BY deptno

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

HAVING子句

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

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

SELECT AVG(sal),deptnoFROM empGROUP BY deptnoHAVING AVG(sal)>2000

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

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

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

SELECT COUNT(*),jobFROM empGROUP BY jobHAVING AVG(sal)>2000

关联查询

关联查询的结果集中字段来自多张表联合查询的结果。
关联查询的关键在于连接条件，即:表之间数据的对应关系。

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

SELECT e.ename,e.deptno,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno

关联查询中连接条件要与过滤条件同时成立!
查看在NEW YORK工作的员工?

SELECT e.ename,d.dname,d.locFROM emp e,dept dWHERE e.deptno=d.deptnoAND d.loc='NEW YORK'

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

SELECT d.deptno,d.dnameFROM dept d

不满足连接条件的记录是不会被查询出来的。

SELECT e.ename,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno

查看KING在哪个城市工作?

SELECT e.ename,d.locFROM emp e,dept dWHERE e.deptno=d.deptnoAND e.ename='KING'

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

内连接

内连接也是用来完成关联查询的。

SELECT e.ename,d.locFROM emp e JOIN dept dON e.deptno=d.deptnoWHERE e.ename='KING'

外连接

外连接在进行关联查询时除了可以将满足连接条件的记录查询出来之外，也可以将不满足连接条件的记录列出来。
外连接分为:左外连接，右外连接，全外连接
左外连接:以JOIN左侧表作为驱动表(所有记录都会列出来)，那么当驱动表中不满足连接条件的记录来自右侧表中的字段值为NULL。

SELECT e.ename,d.dnameFROM emp e  LEFT|RIGHT|FULL OUTER JOIN dept dON e.deptno=d.deptnoSELECT e.ename,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno(+)

自连接

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

SELECT empno,ename,mgrFROM emp

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

SELECT e.ename,m.enameFROM emp e,emp mWHERE e.mgr=m.empno