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

查询表数据

查询特定的列数据

如：

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

查询所有列数据

如：

SELECT * FROM emp;
1

使用DISTINCT查询唯一列数据

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

SELECT DISTINCT job FROM emp;
1

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

在查询中使用表达式

如：

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

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

使用列别名

如：

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

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

字符串连接

使用||符号，如：

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

指定查询条件

简单WHERE子句

如：

SELECT * FROM emp WHERE deptno = 20;

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

使用范围操作符

可以使用BETWEEN AND，IN，LIKE。

BETWEEN AND

如：

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

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

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

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

IN

如：

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

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

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

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%';
1
2
3
4
5
6
7
8
9
10
11

判断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;
1
2
3
4
5
6
7

使用逻辑组合

如：

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%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

排序

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;
1
2
3
4
5
6
7
8
9

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

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

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

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

异常为：

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

统计函数

记录统计

如：

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

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

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

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

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

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

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

汇总和平均值计算

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

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

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

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

最小值和最大值

如：

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

统计函数的NULL值处理

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

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

分组统计

使用GROUP BY来分组，如：

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

分组对于初学者来说比较难于掌握，这里就简单地说一下。
在使用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
1
2
3
4

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

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

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;
1
2
3
4
5
6
7
8