【Oracle数据库系列笔记】基本查询

SELECT子句

基本使用

SELECT的主要作用是检索数据（从数据库中将需要的数据查询出来）

基本语法：

SELECT 列1,列2...,列n FROM 表名 WHERE 限定词; having,order by,group by 子句

选择所有列：

-- select * from table_name;
-- 示例表存储的是某公司的员工信息，包含员工的ID，员工名，所属部门，月薪等等
SELECT * FROM emp;

选择指定列：

-- 查询所有员工的员工ID，员工名，月薪
SELECT empno,ename,sal FROM emp;

-- 查询月薪大于2000员工的ID、员工名，月薪，并限定查询结果
SELECT empno,ename,sal FROM emp WHERE sal>2000;

DUAL表

在Oracle系统中有一个很特殊的表：DUAL表，也可以说是在Oracle中使用频率最高的一个表。在以后的例子当中，我们会经常使用到这个数据表。DUAL数据表是属于SYS用户的，其相关属性如下：

SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME='DUAL';
SELECT DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS') FROM DUAL;

DUAL表是Oracle中的一个实际存在的表，任何用户都可以读取，常用在没有目标的SELECT 语句块中。
DUAL表的主要作用是为了保证Oracle和用户程序都能引用一个已知的值，这个表只有一个伪列：

SELECT * FROM DUAL;

DUAL表的用户示例：

-- 第一个，查询当前连接的用户：
SELECT USER FORM DUAL;
-- 第二个，查询当前的系统时间：
SELECT SYSDATE FROM DUAL;
-- 第三个，进行数学运算：
SELECT 1+3 FROM DUAL;

DUAL表是Oracle中的一个非常重要的表格，系统正常运行也离不开这个表。如果这个表被不小心删除了，那么数据库系统运行将会出现问题，比如说无法正常启动等等。这就需要我们进行手工修复才能恢复。

计算表达式

本节的例子使用SCOTT用户下的示例表和DUAL表：

-- 基本的算数运算
SELECT 100+20 FROM dual;
SELECT 100-20 FROM dual;
SELECT 100*20 FROM dual;
SELECT 100/20 FROM dual;
SELECT (100*20+30)/8 FROM dual;

-- 日期运算
SELECT SYSDATE FROM dual;
SELECT SYSDATE-to_date('2014-10-22 22:30:00','yyyy-MM-DD hh24:mi:ss') FROM dual;

-- 四舍五入
SELECT (100*20+30)/8 FROM dual;
SELECT ROUND((100*20+30)/8,1) FROM dual;

-- 取模/求余
SELECT MOD(100,7) FROM dual;

-- 计算出某公司员工的剔除奖金的年薪
SELECT empno,ename,sal*12 FROM emp;

-- 计算出某公司员工的包含奖金的年薪
SELECT empno,ename,(sal+comm)*12 FROM emp;
SELECT * FROM emp;

-- NVL(expr1,expr2) 转换一个空值到一个实际的值
SELECT empno,ename,(sal+nvl(comm,0))*12 FROM emp;

-- 列别名
SELECT empno,ename,(sal+nvl(comm,0))*12 AS salary FROM emp;
SELECT empno,ename,(sal+nvl(comm,0))*12 salary FROM emp;

-- 中文列别名
SELECT empno AS "编号",ename AS "姓名",(sal+nvl(comm,0))*12 AS "年薪" FROM emp;

-- 字符串和别名
SELECT '列别名示例' AS "列别名示例" FROM dual;

-- 字符串连接
SELECT ename || ' 是 ' || hiredate || ' 进入公司的。' FROM emp;

WHERE子句

WHERE子句中可以使用的几个条件表达式：

• 比较运算符：大于、小于、不等于等等
• 逻辑运算符：非、或、和
• 范围、模糊查询：like、between、in
• 逻辑运算符
• 正则查询，可以通过regexp_like、regexp_instr等函数实现

比较运算符

比较运算符包括：

• 大于：>
• 等于：=
• 小于：<
• 不等于：<> 或 !=

示例如下：

-- 工资大于3000的员工
SELECT * FROM emp WHERE sal>3000;

-- 工资等于3000的员工
SELECT * FROM emp WHERE sal=3000;

-- 工资小于3000的员工
SELECT * FROM emp WHERE sal<3000;

-- 工资大于等于3000的
SELECT * FROM emp WHERE sal>=3000;

-- 工资不等于3000的员工
SELECT * FROM emp WHERE sal<>3000;
SELECT * FROM emp WHERE sal!=3000;

-- 部门为10的员工中，工资大于部门20中的最大工资值
SELECT * FROM emp
WHERE deptno=10 
  AND sal > ALL(SELECT sal 
                FROM emp 
                WHERE deptno=20)
-- > ALL 大于最大的
-- < ALL 小于最小的
-- > ANY 大于最小的
-- < ANY 小于最大的

范围查询

范围查询主要包括：

• BETWEEN …AND…
• IN
• NOT IN

示例如下：

-- 表示一个有序范围的between...and...
SELECT * FROM emp WHERE sal BETWEEN 3000 AND 5000;
-- between..and..相当于>=和<=配合起来使用
SELECT * FROM emp WHERE sal>=3000 AND sal<=5000;

-- 表示一个列表范围的in
SELECT * FROM emp WHERE ename NOT IN ('SCOTT','KING','JAMES');

模糊查询

示例如下：

-- 查询员工当中，名字以S开头的所有员工信息
SELECT * FROM emp WHERE ename LIKE 'S%';
SELECT * FROM emp WHERE ename LIKE 'S___H';

-- 通配符
-- %(百分号) ：表示0个或多个字符
-- _(下划线) ：表示单个字符，一般用它来限制匹配长度
SELECT * FROM info;
SELECT * FROM info WHERE NAME LIKE '%%%'

-- 转义字符
SELECT * FROM info WHERE NAME LIKE '%\%%' ESCAPE '\';
SELECT * FROM info WHERE NAME LIKE '%m_%' ESCAPE 'm';

逻辑运算符

逻辑运算符包括：

• AND:逻辑与，表示两个条件必须同时满足
• OR: 逻辑或，表示两个条件中有一个条件满足即可
• NOT:逻辑非，返回与某条件相反的结果

逻辑运算符运算的优先顺序是NOT，AND，OR。如果要改变优先顺序，可以使用括号 。

示例如下：

-- 查询出部门编号为10的员工中，工资大于2000的信息
SELECT * FROM emp WHERE deptno=10 AND sal>2000;
-- 查询出部门编号为10的员工和SMITH的信息
SELECT * FROM emp WHERE deptno=10 OR ename='SMITH';
-- 查询出奖金为非空的员工信息
SELECT * FROM emp WHERE comm IS NOT NULL;