使用别名
当一个SELECT 子句中查询的内容是一个函数或者表达式, 那么在结果集中对应的该字段的字段名就是这个函数表达式,可读性差,为此应当为该列添加别名
SELECT ename , sal*12 salary FROM emp 或 SELECT ename , sal*12 AS salary FROM emp 别名不区分 大小写,若希望区分大小写,或者别名中含有空格,可以使用双引号包围 SELECT ename , sal*12 "sa A lary" FROM emp;
使用 < , > , <>(不等于) SELECT ename, sal FROM emp WHERE sal< 2000
SELECT ename,sal,job FROM emp WHERE deptno <> 10;
SELECT ename,sal,hiredate FROM emp WHERE hiredate >TO_DATE('1982-01-01','YYYY_MM_DD')
AND, OR 关键字 SELECT ename, sal, job FROM emp WHERE sal > 1000 AND job='CLERK'
SELECT ename, sal, job FROM emp WHERE sal > 1000 OR job='CLERK'
AND 的优先级高于 OR 所以,可以通过添加括号来提高 OR的优先级
SELECT ename, sal, job FROM emp WHERE sal > 1000 AND (job='SALESMAN' OR job='CLERK')
LIKE 条件
LIKE 用于模糊匹配字符串,它需要借助两个通配符: % : 表示 0 到 多个字符 _ : 表示单个字符
查看员工第二个字符是 A 的员工: SELECT ename, job, sal FROM emp WHERE ename LIKE '_A%'
IN (List) , NOT IN (List)
判断在列表中,不在列表中,常用于子查询中
SELECT ename, job, sal FROM emp WHERE job IN ('MANAGER','CLERK')
BETWEEN…AND…
查询符合某个值域范围的数据
SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000
使用 ANY 和 ALL 条件
用于判断诸如 : > , >= , < , <= 一个列表的内容 >ALL(list) : 大于列表中所有的 ( 大于最大的) >ANY(list) : 大于列表中之一即可 ( 大于最小的)
以下查询结果相同: SELECT empno, ename, job, sal, deptno FROM emp WHERE sal > ALL(1500,2500,2000) SELECT empno, ename, job, sal, deptno FROM emp WHERE sal > ANY(2500,4500,4000)
ANY 和 ALL 的列表通常不会给特定的值,这样没有意义,通常使用在判断一个子查询的结果
查询条件中使用表达式和函数 SELECT ename, sal, job FROM emp WHERE ename = UPPER('scott') SELECT ename, sal, job FROM emp WHERE sal*12>50000
DISTINCT 过滤重复
去除后面指定字段的重复行,必须紧跟在 SELECT 关键字之后
查看员工职位种类数 SELECT DISTINCT job FROM emp DISTINCT 后面可以跟多个字段: 这些字段值的组合没有重复 以下查询不会出现 同一部门相同职位 的人 SELECT DISTINCT job, deptno FROM emp
ORDER BY 排序
可以根据后面指定的字段对结果集进行升序或降序排列,其中 ASC 是升序, DESC 是降序,通常不写 ASC ,默认升序
ORDER BY 子句只能存在 SELECT 语句中最后一个子句上 SELECT ename, sal FROM emp ORDER BY sal
ORDER BY 可以按照多个字段排序,排序是有优先级的,首先按照第一个字段的排序规则偶像,当第一个字段有重复值时,才按照第二个字段排序
SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC
聚合函数又称为多行函数,分组函数,聚合函数可以将多条记录进行统计,然后得出一个结果.所以聚合函数是用来统计使用的
MAX() 和 MIN () : 统计最大值和最小值 查看公司的最高工资是多少 SELECT MAX(sal) FROM emp
AVG() 和 SUM() : 求平均值,总和 SELECT AVG(sal) FROM emp SELECT SUM(sal) FROM emp
COUNT() : 统计记录总数 SELECT COUNT(ename) FROM emp
注意: 聚合函数忽略 NULL 值 举例: SELECT AVG(comm) FROM emp 上述查询,如果comm字段存在 NULL 值则除数不符合实际值,结果不正确 解决: SELECT AVG(NVL(comm,0)) FROM emp
GROUP BY 子句
可以将结果集按照指定的字段值相同的记录看做是一组,配合聚合函数使用可以对不同分组的记录分别进行统计然后得到结果
查看每个部门最高工资和最低工资 SELECT MAX(sal) , MIN(sal) FROM emp GROUP BY deptno
在 SELECT 当中若使用了聚合函数,那么不在聚合函数中的其他单独字段必须出现在 GROUP BY 子句中,反过来不是必须的 GROUP BY 也可以按照多个字段进行分组,分组原则是这些字段值的组合相同的看做一组
查看每个部门每种职位的平均工资 SELECT AVG(sal), deptno||job FROM emp GROUP BY deptno, job
WHERE 的过滤时机: WHERE 是在查询表中每一条数据是进行过滤的,只会将满足 WHERE 条件的记录查询出来 ,所以聚合函数无法使用在 WHERE 后, 解决的该问题可以用 HAVING 子句
HAVING 子句
HAVING 子句必须紧跟在GROUP BY 子句之后,作用是添加条件来过滤不同的分组, HAVING 可以用聚合函数作为过滤条件 查看平均工资高于2000的部门 的平均工资
SELECT AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000查看平均工资高于2000 的部门的最高和最低工资
SELECT MAX(sal) , MIN(sal), deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000查看最高工资>=3000 的职位的平均工资
SELECT AVG(sal) , job FROM emp GROUP BY job HAVING MAX(sal)>=3000查询每个员工的基本信息及其部门信息
SELECT e.ename, e.job, e.sal , d.dname, d.loc, d.deptno FROM emp e, dept d WHERE e.deptno = d.deptno上述 SQL 中: e.deptno = d.deptno 条件是用来联系 emp 和 dept 的数据关系的,这样的条件称为连接条件
在关联查询中,必须要添加连接条件,N 张表关联查询至少要添加 N-1 连接条件.不添加连接条件,会产生笛卡尔积 ( 实际开发要避免 )
关联查询的连接条件与过滤条件要同时成立
SELECT e.ename , e.job, e.sal, e.deptno,d.dname FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname='SALES'内连接 关联查询的另一种写法
SELECT e.ename, d.dname FROM emp e JOIN dept d ON 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.sal, d.dname ,d.loc FROM emp e LEFT OUTER JOIN dept d ON e.deptno= d.deptno右外连接 : 以 JOIN 右侧的表为驱动表,其他特性同上 RIGTH OUTER JOIN ...ON...
全外连接 : JOIN 两侧的表都为驱动表,综合左右外连接内容 FULL OUTER JOIN...ON...
一般关联查询实现外连接效果: (+) 定义在连接条件上,定义在哪边哪边就补 NULL 但是无法实现全外连接
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno(+) = d.deptno自连接
当前表的一条记录对应当前表本身的多条记录, 这种设计就是自连接,自连接是用来解决数据内容相同,但是数据间又存在上下级关系的树状结构情况
查找每个员工及其上司的名字
SELECT e.ename worker, m.ename manager FROM emp e,emp m WHERE e.mgr = m.empno上述结果采用内连接
SELECT e.ename worker, m.ename manager FROM emp e JOIN emp m ON e.mgr = m.empno