ORACLE数据库总结03

字段的别名

当SELECT子句中使用了函数或者表达式

时，结果集该字段的名字就是这个函数

或者表达式，可读性差，这时候可以使用

别名。

当别名中希望出现空格或者区分大小写时，

可以使用双引号括起来

SELECT ename,sal*12 "Annual Salary"

FROM emp

 

查询职员表中薪水低于2000元的职员信息

 

查询职员表中不属于部门 10 的员工信息 （<>）

 

查询职员表中在1982年1月1号以后入职的职员信息

 

 

AND和OR连接多个条件

查看工资高于1000的CLERK和SALESMAN

SELECT ename,job,sal

FROM emp

WHERE sal>1000

AND (job='SALESMAN'

OR job='CLERK')

 

AND的优先级高于OR，所以要想提高OR的

优先级，需要使用括号。

 

 

LIKE关键字用于添加模糊匹配字符串

的条件，支持两个通配符:

“_”:表示任意一个字符

“%”:表示任意个字符(0-多个)

 

查看第二个字母是A并且地四个字母是T的?

SELECT ename,sal,job

FROM emp

WHERE ename LIKE '_A_T%'

 

 

IN(list),NOT IN(list)

判断是否在列表中或不在列表中

IN常用在子查询的判断中使用

 

SELECT ename, job 

FROM emp  

WHERE 

 job NOT IN ('MANAGER', 'CLERK')

 

BETWEEN... AND ...

判断在两者之间

查看工资在1500到3000之间(包含)

SELECT ename, sal 

FROM emp  

WHERE 

 sal BETWEEN 1500 AND 3000

 

 

ANY(list),ALL(list)

当需要判断>,>=,<,<=一个别表中的

数据时，要搭配ANY与ALL使用

通常判断的内容不是确定值，而是用在

判断子查询的结果。

>ANY:大于最小的

<ANY:小于最大的

>ALL:大于最大的

<ALL:小于最小的

SELECT empno, ename, job, sal, deptno

FROM emp

WHERE sal > ANY (3500,4000,4500);

 

DISTINCT可以将结果集中指定字段

值重复的记录去除，DISTINCT应当

紧跟在SELECT之后。

 

查看公司有哪些职位?

SELECT DISTINCT job

FROM emp

 

 

DISTINCT对多个字段去重

去重原则:多字段值的组合没有重复

SELECT DISTINCT job,deptno

FROM emp

 

结果集排序

ORDER BY子句

ORDER BY可以根据其后指定的字段按照

升序或降序进行排序

ORDER BY子句只能写在SELECT语句的

最后一个子句上。

 

查看公司工资的排名

SELECT ename,sal,deptno

FROM emp

ORDER BY sal DESC

 

多字段排序时有优先级，ORDER BY

会按照第一个字段排序结果集，当第一个

字段的值有重复的时，才会按照第二个字段

排序这几条记录，以此类推。

SELECT ename,sal,deptno

FROM emp

ORDER BY deptno,sal DESC

 

排序的字段中若含有NULL值，NULL

被认为是最大值

SELECT ename,comm

FROM emp

ORDER BY comm DESC

 

 

 

 

聚合函数

用来统计结果集指定字段的值

 

MAX,MIN:求最大值与最小值

 

查看公司的最高工资与最低工资?

SELECT MAX(sal),MIN(sal)

FROM emp

 

查看公司平均工资与工资总和?

SELECT AVG(sal),SUM(sal)

FROM emp

 

聚合函数忽略NULL值

SELECT NVL(comm,0)

FROM emp

 

SELECT AVG(NVL(comm,0)),SUM(comm)

FROM emp

 

COUNT函数

COUNT统计的是指定字段不为NULL的记录

总数

 

查看公司总共多少人?

SELECT COUNT(ename)

FROM emp

 

通常可是使用COUNT(*)统计记录数

 

查看公司职位是"MANAGER"的人数?

SELECT COUNT(*)

FROM emp

WHERE job='MANAGER'

 

查看公司中CLERK的平均工资是多少?

SELECT AVG(sal)

FROM emp

WHERE job='CLERK'

 

 

分组

GROUP BY子句

GROUP BY子句可以将查询结果集按照其后

指定的字段值相同的记录划分为一组。

分组的目的是配合聚合函数作细化的统计工作

 

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

SELECT AVG(sal),job

FROM emp

GROUP BY job

 

当SELECT子句中使用了聚合函数，那么

凡是不在聚合函数中的单独字段必须出现

在GROUP BY子句中，反过来则不是必须的

 

SELECT MAX(sal),MIN(sal),deptno

FROM emp

GROUP BY deptno

 

SELECT COUNT(*),job

FROM emp

GROUP BY job

 

查看部门的平均工资，前提是该部门的

平均工资高于2500

SELECT AVG(sal),deptno

FROM emp

WHERE AVG(sal)>2500

GROUP BY deptno

 

WHERE子句中不能使用聚合函数作为

过滤条件，原因在于过滤时机不对。

WHERE是在查询表获取结果集的过程

中对表中数据逐条过滤，以形成结果集。

聚合函数是建立在结果集基础上进行的，

所以使用聚合函数的结果过滤是在WHERE

之后进行。

 

HAVING子句

HAVING子句必须跟在GROUP BY子句之后

HAVING可以使用聚合函数作为过滤条件

HAVING决定着GROUP BY分组的取舍

 

SELECT MAX(sal),MIN(sal),deptno

FROM emp

WHERE job='MANAGER'

GROUP BY deptno

HAVING AVG(sal)>2500

ORDER BY deptno

 

SELECT sal,deptno

FROM emp

ORDER BY deptno

 

关联查询

结果集中的字段来自多张表关联查询。

 

当查询的字段在多张表中都有时，需要明确

指定字段来自那张表，表可以定义别名，来

简化SQL语句的复杂度。

 

查看每个员工的信息以及其所在部门的信息

SELECT e.ename,e.deptno,d.dname

FROM emp e,dept d

WHERE e.deptno=d.deptno

 

多表关联查询通常要添加连接条件，否则

会产生笛卡儿积，这通常是一个无意义的结果

集，并且开销巨大。

SELECT e.ename,d.dname

FROM emp e,dept d

 

过滤条件要与连接条件同时成立

查看SALES部门的员工?

SELECT e.ename,e.sal,e.job,d.dname

FROM emp e,dept d

WHERE e.deptno=d.deptno

AND d.dname = 'SALES'

 

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

 

 

内链接

SELECT e.ename,d.dname

FROM emp e JOIN dept d

ON e.deptno=d.deptno

WHERE d.dname='SALES'

 

关联查询忽略不满足连接条件的记录

 

 

外连接

外连接在进行关联查询时除了可以将

满足连接条件的记录查询出来之外，还

可以将不满足连接条件的记录查询出来

外连接分为:

左外连接:以JOIN左侧的表作为驱动表

        驱动表中的数据都要显示出来，那么

        当来某条记录不满足连接条件时，那

        来自JOIN右侧表的字段全部补NULL值

右外连接:

全外连接:

 

SELECT e.ename,e.job,d.dname,d.loc

FROM emp e 

  LEFT|RIGHT|FULL OUTER JOIN 

     dept d

ON e.deptno=d.deptno

 

 

SELECT e.ename,e.job,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

 

自连接

自连接的设计用于解决保存性质相同的数据

但是有存在上下级关系的树状结构数据时使用

 

SELECT empno,ename,mgr

FROM emp

 

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

SELECT e.ename,m.ename

FROM emp e JOIN emp m

ON e.mgr=m.empno

 

查看FORD的上司是谁?

SELECT m.ename

FROM emp e JOIN emp m

ON e.mgr = m.empno

WHERE e.ename='FORD'

 

查看FORD的上司在哪个城市工作?

SELECT e.ename,m.ename,d.loc

FROM emp e,emp m,dept d

WHERE e.mgr=m.empno

AND m.deptno=d.deptno

AND e.ename='FORD'

 

内连接

SELECT e.ename,m.ename,d.loc

FROM emp e JOIN emp m

ON e.mgr=m.empno

JOIN dept d

ON m.deptno=d.deptno

WHERE e.ename='FORD'

 

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

SELECT e.ename,m.ename

FROM emp e JOIN emp m

ON e.mgr=m.empno

 

查看KING的下属?

SELECT e.ename

FROM emp e,emp m

WHERE e.mgr=m.empno

AND m.ename='KING'