Oracle PL/SQL开发基础（第六弹：简单查询）

查询表数据

查询特定的列数据

如：

SELECT ename, empno, job, hiredate
  FROM scott.emp;

查询所有列数据

如：

SELECT * FROM emp;

使用DISTINCT查询唯一列数据

可以使用DISTINCT关键字获取列中的唯一值，即去除重复数据，如：

SELECT DISTINCT job FROM emp;

要注意，DISTINCT会导致索引失效，在大型数据集查询中应该尽量避免DISTINCT查询。

在查询中使用表达式

如：

SELECT empno, ename, sal * (1 + 0.12) FROM emp;

在进行算术运算时，如果列包含NULL值，那么结果也为NULL。

使用列别名

如：

SELECT empno 员工名称, ename "员工姓名_NAME", sal * (1 + 0.12) raised_sal, sal AS 薪水 FROM emp;

AS关键字可选。
如果列别名中包含空格、特殊字符或者大小写敏感字符，则要用双引号把别名引起来。

字符串连接

使用||符号，如：

SELECT ename || '的薪资为：' || sal 员工薪水 FROM emp;

指定查询条件

简单WHERE子句

如：

SELECT * FROM emp WHERE deptno = 20;

SELECT empno, ename, job FROM emp WHERE sal >= 1500 AND sal <= 2500;

使用范围操作符

可以使用BETWEEN AND，IN，LIKE。

BETWEEN AND

如：

SELECT empno, ename, job FROM emp WHERE sal BETWEEN 1500 AND 2500;

实际上在使用BETWEEN AND操作符时，Oracle会将该语句转换为一对AND条件，因此，上面的语句会被Oracle翻译成：

SELECT empno, ename, job FROM emp WHERE sal >= 1500 AND sal <= 2500;

因此使用BETWEEN AND并没有提高性能，只是逻辑上给了用户一种更接近自然语言的表达方式。

IN

如：

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE job IN ('SALESMAN', 'CLERK', 'ANALYST');

对于IN查询条件，Oracle会将其转换为一组OR条件，因此对于上面的语句，Oracle会将其转换为如下语句：

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE job = SALESMAN OR job = CLERK OR job = ANALYST;

LIKE

LIKE查询也成为模糊查询，%通配符表示零个或多个字符，_通配符表示一个字符，如“”

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE ename LIKE 'J%';

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE hiredate LIKE '%81';

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE ename LIKE '__A%';

判断NULL值

如：

SELECT empno, ename, job, mgr, hiredate
  FROM emp
 WHERE mgr IS NULL;

SELECT empno, ename, job, mgr, hiredate
  FROM emp
 WHERE mgr IS NOT NULL;

使用逻辑组合

如：

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE deptno = 20 AND hiredate LIKE '%82';

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE deptno = 20 OR hiredate LIKE '%82';

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE job NOT IN ('CLERK', 'MANAGER', 'SALESMAN');

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE sal NOT BETWEEN 1000 AND 2500;

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE ename NOT LIKE '%A%';

排序

ORDER BY指定要对结果集进行排序，可选择ASC表示升序，DESC表示降序。默认为ASC。
ORDER BY子句必须是SELECT语句的最后一个子句。
如：

SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY empno;

SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY empno, ename DESC;

除了直接指定列名之外，还可以根据SELECT语句的字段列表的索引顺序指定排序，如根据SELECT语句中的第4个字段mgr进行排序：

SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY 4 DESC;

如果指定一个不存在的SELECT语句中的索引号，Oracle会提示异常，如：

SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY 8 DESC;

异常为：

ORA-01785:ORDER BY项必须是SELECT-list表达式的数目

统计函数

记录统计

如：

SELECT COUNT(*) 记录条数 FROM emp WHERE deptno=20;

COUNT(*)返回SELECT语句标准的行数，包括重复行和有空值列的行。
如果要统计某个字段的非空值得个数，可以这么写：

SELECT COUNT(comm) 提成员工数 FROM emp;

COUNT默认使用ALL参数，表示提取所有包括重复的记录，因此上面的语句等同于：

SELECT COUNT(ALL comm) 提成员工数 FROM emp;

如果要获得唯一记录数，则可以：

SELECT COUNT(DISTINCT job) 职位个数 FROM emp;

汇总和平均值计算

SUM函数可以用来汇总，如：

SELECT SUM(sal) 薪水总计, SUM(comm) 提成总计 FROM emp;

AVG函数可以用来计算平均值，如：

SELECT AVG(sal) 平均薪资,AVG(comm) 平均提成 FROM emp;

最小值和最大值

如：

SELECT MIN(sal) 最低薪资,MAX(sal) 最高薪资 FROM emp;
SELECT MIN(hiredate) 最早雇佣日期,MAX(hiredate) 最晚雇佣日期 FROM emp;

统计函数的NULL值处理

可以使用NVL函数将NULL替换成别的值，如：

SELECT MIN(NVL(comm,0)) 最低提成,MAX(NVL(comm,0)) 最高提成 FROM emp;

分组统计

使用GROUP BY来分组，如：

SELECT   deptno, SUM (sal) 部门薪资小计
    FROM emp
GROUP BY deptno
ORDER BY SUM (sal);

分组对于初学者来说比较难于掌握，这里就简单地说一下。
在使用GROUP BY子句时，除了作为分组函数（如：SUM，AVG，COUNT等）参数的列不用包含在GROUP BY子句中之外，任何在SELECT列表中的其他列都必须出现在GROUP BY子句中。不过在GROUP BY子句中的列不必一定出现在SELECT列表中。
分组结果通过分组列隐式排序，也可以用ORDER BY指定不同的排序顺序，但也只能用分组函数或分组列进行排序。

HAVING子句

例如如果只是想对部门编号为20和30的员工进行分组统计，使用WHERE子句即可，如：

SELECT   deptno, job, SUM (sal) 薪资小计
    FROM emp
   WHERE deptno IN (20, 30)
GROUP BY deptno, job

如果要对分组的结果进行进一步的过滤，比如要显示分组后薪资小计大于2000的分组结果，那么可以：

SELECT   deptno, job, SUM (sal) 薪资小计
    FROM emp
   WHERE deptno IN (20, 30)
GROUP BY deptno, job
  HAVING SUM (sal) > 2000;

HAVING子句只能用在GROUP BY子句的后面，不能使用WHERE子句来取代HAVING子句，否则将会产生异常。

使用DUAL表

DUAL表是Oracle系统中对所有用户可用的一个实际存在的表，是一个1行1列的表，这个表不能实际存储信息，也不具有任何其他的 意义，在实际应用中仅用来执行SELECT语句，如：

-- 查询当前系统日期
SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
-- 查询当前系统用户
SELECT USER FROM DUAL;
-- 输出静态文本
SELECT 'hello' FROM DUAL;
-- 计算表达式
SELECT 500 * 60 FROM DUAL;