Oracle 数据库基础（三）(查询)

本文由 简悦 SimpRead 转码， 原文地址 https://blog.csdn.net/qq_43437465/article/details/90731539

一、SQL（基础查询）

1.1 基本查询语句

1.1.1 FROM 子句

• SELECT <* , column [ alias] , …> FROM table;
• SELECT 用于指定要查询的列
• FROM 指定要从哪个表中查询

❤️ 注意：
（1）如果要查询所有列，可以在 SELECT 后面使用 * 号
（2）如果只查询特定的列，可以直接在 SELECT 后面指定列名，列名之间用逗号隔开。

eg 1 ：

SELECT * FROM emp;

eg 2 ：

SELECT ename,sal,job FROM emp;

1.1.2 使用别名

• 在 SQL 语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义
• 使用语法是列的别名跟在列名后，中间可以加或不加一个 “AS” 关键字
• 如果希望别名中区分大小写字符，或者别名中包含字符或空格，则必须用双引号引起来

❤️ 注意：
（1）别名本身不区分大小写，而且不能含有空格。
（2）若希望别名区分大小写或含有空格，那么可以在别名上使用双引号括起来

eg 1 :

--这两句的效果是一样的
SELECT ename,sal*12   sal FROM emp;
SELECT ename,sal*12 AS sal FROM emp ;

eg 2 :

--起别名
SELECT ename,sal*12 AS "s a l  " FROM emp ;

1.1.3 WHERE 子句

• 在 SELECT 语句中，可以在 WHERE 子句中使用比较操作符限制查询结果
• 如果和数字比较，可以使用单引号引起来，也可以不用
• 如果和字符及日期类型的数据比较，则必须用单引号引起来

eg 1 :

/*
WHERE子句
*/
--查询部门10下的员工信息
SELECT * FROM emp WHERE deptno=10;

eg 2 :

--查询职员表中职位是“SALESMAN”的职员
SELECT ename,sal,job FROM emp WHERE job='SALESMAN';

1.1.4 SELECT 子句

• 如果只查询表的部分列，需要在 SELECT 后指定列名
• SELECT ename,sal FROM emp;
  eg:

SELECT ename,sal FROM emp;

1.2 查询条件

1.2.1 使用 >,<,<=,>=,!=,<>,=

eg 1：

--查询员工表中薪水低于2000元的职员信息
SELECT ename,sal FROM emp WHERE sal<2000;

eg 2:

--查询职员表中不属于部门10的员工信息（不等于号“<>”）
SELECT ename,sal,deptno,job FROM emp WHERE deptno <> 10;

1.2.2 使用 AND,OR 关键字

• 在 SQL 操作中，如果希望返回的结果必须满足多个条件，应该使用 AND 逻辑操作符连接这些条件
• 在 SQL 操作中，如果希望返回的结果满足多个条件之一即可，应该使用 OR 逻辑操作符连接这些条件

eg 1 ：

--查询薪水大于1000并且职位是‘CLERK’的职员信息
SELECT ename,sal FROM emp WHERE job = 'CLERK' AND sal > 1000;

❤️ 注意：

AND 的优先级高于 OR，可以通过括号来提高 OR 的优先级

eg 2:

SELECT ename,sal,job FROM emp WHERE sal>1000 AND job='SALESMAN'  OR job ='CLERK' ;

SELECT ename,sal,job FROM emp WHERE sal>1000 AND (job='SALESMAN'  OR job ='CLERK') ;

1.2.3 使用 LIKE 条件（模糊查询）

• LIKE 用于模糊匹配字符串，支持两个通配符：
  _: 单一的一个字符
  %: 任意个字符
  eg:

--查看名字第二个字母是L最后一个字母是N的
SELECT ename FROM emp WHERE ename LIKE '_L%N';

1.2.4 使用 IN 和 NOT IN

• 比较操作符 IN（list）用来取出符合列表范围中的数据
• List 表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为 TRUE，该条记录则被显示出来
• IN 也可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表
• NOT IN（list）取出不符合此列表中的数据记录

eg 1:

--查询职位是MANAGER或CLERK的员工
SELECT ename,job FROM emp WHERE job IN('MANAGER','CLERK');

eg 2:

--查询不是部门10或20的员工
SELECT ename,deptno,job FROM emp WHERE deptno NOT IN (10,20);

1.2.5 BETWEEN … AND…

• BETWEEN … AND… 操作符用来查询符合某个值域范围条件的数据
• 最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用

eg：

--查询工资在1500到3000之间的员工(小的在左边大的在右边)
SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;

1.2.6 使用 IS NULL 和 IS NOT NULL

空值 NULL 是一个特殊的值，比较的时候不能使用 “=” 号，必须使用 IS NULL，否则得不到正确的结果

eg：

--查询那些职员的奖金数量为NULL
SELECT ename,sal,comm FROM emp WHERE comm IS NULL;

1.2.7 使用 ANY 和 ALL 条件

• ANY 和 ALL 不能单独使用，需要配合单行比较操作符 >,>=,<,<= 一起使用

>ANY(list)：大于列表中最小的
>ALL(list)：大于列表中最大的
<ANY(list)：小于列表中最大的
<ALL(list)：小于列表中最小的
ANY和ALL常用于子查询

eg 1 :

SELECT ename,job,sal,deptno FROM emp WHERE sal>ANY(500,5000,8000);

1.2.8 使用函数或者表达式作为过滤条件

• 当查询需要对选出的字段进行进一步计算，可以在数字列上使用算术表达式（+，-，*，/）
• 表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号
• 算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示在一个日期值上加或减一个天数

eg 1 :

SELECT ename,sal,job FROM emp WHERE ename = UPPER('scott');

eg 2:

SELECT ename,sal,job FROM emp WHERE sal*12>50000;

1.2.9 使用 DISTINCT 关键字

• 数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显示所有行，不管查询结果是否有重复数据
• 当重复数据没有实际意义，经常会需要去掉重复值，使用 DISTINCT 实现

eg1 :

--查看公司共有多少种职位
SELECT DISTINCT job FROM emp;

eg 2 :

--多字段去重，是对这些字段值的组合进行去重
SELECT DISTINCT job, deptno FROM emp;

1.3 排序

1.3.1 ASC 和 DESC

ORDER BY 可以根据其后指定的字段对结果集按照该字段的值进行升序或者降序排列。

• ASC：升序，不写默认就是升序
• DESC: 降序

eg 1 :

--降序
SELECT ename,sal FROM emp ORDER BY sal DESC;

eg 2：

--升序(ASC可以省略，默认按照升序排列)
SELECT ename,sal FROM emp ORDER BY sal ASC;

1.3.2 使用 ORDER BY 子句

• ORDER BY 按照多个字段排序
• ORDER BY 首先按照第一个字段的排序方式对结果集进行排序，当第一个字段有重复值时才会按照第二个字段排序方式进行排序，以此类推，每个字段都可以单独指定排序方式。

eg:

SELECT ename,deptno,sal FROM emp ORDER BY deptno DESC , sal DESC;

❤️ 注意：
排序的字段中含有 NULL 值，NULL 被认作最大值

eg:

SELECT ename,comm FROM emp ORDER BY comm DESC;

1.3.3 多个列排序

• 当以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推
• 多列排序时，不管正序还是倒序，每个列需要单独设置排序方式

1.4 聚合函数

1.4.1 什么是聚合函数

• 聚合函数
• 聚合函数又叫多行函数，分组函数
• 聚合函数就是对结果集某些字段的值进行统计的

1.4.2 MAX 和 MIN

MAX,MIN 求给定字段的最大值或最小值
eg 1 :

--查看公司的最高工资和最低工资？
SELECT MAX(sal),MIN(sal) FROM emp;

1.4.3 AVG,SUM

• AVG,SUM 求平均值和总和
• 只能操作数字类型
• 忽略 NULL 值

eg:

-AVG,SUM求平均值和总和
SELECT AVG(sal),SUM(sal) FROM emp;

1.4.4 COUNT 函数

• COUNT 函数不是对给定的字段的值进行统计的，而是对给定字段不为 NULL 的记录数统计的
• 实际上所以聚合函数都忽略 NULL 值统计

eg：

SELECT COUNT(ename) FROM emp;

--通常查看表的记录数可以使用COUNT(*)
SELECT COUNT(*) FROM emp;

1.4.5 聚合函数对空值的处理

• 聚合函数忽略 NULL 值
• 当 emp 表中的 comm 列有 NULL 值，比如某新入职员工没有绩效，比较两条语句的结果：

/*
查看平均绩效
*/
SELECT AVG(comm),SUM(comm) FROM emp;

SELECT AVG(NVL(comm,0)),SUM(comm) FROM emp;

❤️ 说明：
因为求 SUM 时忽略掉了绩效为 NULL 的员工，所以以第一种方法求每个员工的获得绩效的平均值是不准确的。因此为了获得正确的每个员工的平均绩效值就得用到 NVL 函数。

1.5 分组

1.5.1 GROUP BY 子句

• 当希望得到每个部门的平均薪水，而不是整个机构的平均薪水
• 把整个数据表按部门划分成一个个小组，每个小组中包含一行或多行数据，在每个小组中再使用分组函数进行计算，每组返回一个结果
• 划分的小组有多少，最终的结果集行数就有多少

eg 1：

--查看每个部门的平均工资
SELECT AVG(sal) ,deptno  FROM emp GROUP BY deptno;

eg 2:

--查看每个职位的最高工资
SELECT MAX(sal),job FROM emp  GROUP BY job;

1.5.2 分组查询

GROUP BY 也可以根据多个字段分组，分组原则为这几个字段值都相同的记录看做一组。
eg 1 :

--查看同部门同职位的平均工资
SELECT AVG(sal) ,deptno,job FROM emp GROUP BY deptno,job;

❤️ 注意：
当 SELECT 子句中含有聚合函数时，那么凡不在聚合函数中的其他单独字段都必须出现在 GROUP BY 子句中，反过来则不是必须的。

1.5.3 HAVING 子句

• HAVING 子句用来对分组后的结果进一步限制，比如按部门分组，得到每个部门的最高薪水，可以继续限制输出结果
• HAVING 子句必须在 GROUP BY 后面，不能单独存在

eg:

--查看平均工资高于2000的部门的最高工资和最低工资分别是多少？
SELECT MAX(sal),MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;

❤️ 1.6 查询语句执行顺序（面试重点）

查询语句的执行顺序依下列子句次序：

• （1）FROM 子句：执行顺序为从后往前、从右往左
  ❤️ 注意：数据量较少的表尽量放在后面

• （2）WHERE 子句：执行顺序为自下而上、从右往左
  ❤️ 注意：将能过滤掉最大数量记录的条件写在 WHERE 子句的最右

• （3）GROUP…BY… 子句：执行顺序从左往右分组
  ❤️ 注意：最好在 GROUP BY 前使用 WHERE 将不需要的记录在 GROUP BY 之前过滤掉

• （4） HAVING 子句：消耗资源
  ❤️ 注意：尽量避免使用，HAVING 会在检索出所以记录之后才对结果进行过滤，需要排序等操作

• （5）SELECT 子句：少于 * 号，尽量取字段名称
  ❤️ 注意：
  Oracle 在解析的过程中，通过查询数据字典将 * 号依次转换成所有的列名，消耗时间

• （6）ORDER BY 子句：执行顺序为从左到右排序，消耗资源

二、关联查询

2.1 关联基础

2.1.1 关联的概念

• 实际应用中所需要的数据，经常会需要查询两个或两个以上的表
• 这种查询两个或两个以上数据表或视图的查询叫做连接查询
• 连接查询通常建立在存在相互关系的父子表之间
  图析：
  
  代码实现：

跨表操作：

--查看每个员工的名字以及其所在部门的名字
SELECT ename,dname FROM emp,dept
WHERE emp.deptno=dept.deptno;

❤️ 注意：

• 当两张表有同名字段时，SELECT 子句中必须明确指定该字段来自哪张表。
• 在关联查询中，表名也可以添加别名，这样可以简化 SELECT 语句的复杂度。

eg:

--查看每个员工的名字以及其所在部门的名字
SELECT  e.ename,d.dname FROM emp e,dept d
WHERE e.deptno=d.deptno;

效果是完全一样的！！！

2.1.2 笛卡尔积

• 笛卡尔积指做关联操作的每个表的每一行都和其他表的每一行做组合，假设两个表的记录条数分别是 X 和 Y，笛卡尔积将返回 X*Y 条记录。

图析：

代码验证：

SELECT  e.ename,d.dname FROM emp e,dept d

会产生 56 行数据记录！！！

❤️ 注意：

• 关联查询要添加连接条件，否则会产生笛卡尔积
• 笛卡尔积通常是一个无意义的结果集，它的记录数是所有参与查询的表的记录数乘积的结果
• 要避免出现，数据量大时极易出现内存溢出等现象
• N 张表关联查询至少要有 N-1 个连接条件

eg:

--查看在NEW YORK工作的员工
SELECT e.ename,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK';

--查看工资高于3000的员工的名字，工资，部门名以及所在地
SELECT e.ename,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.sal>3000;

2.1.3 等值连接

• 连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设定为有关系的列，使用等号 “=” 连接相关的表

2.2 关联查询

2.2.1 内连接

• 内连接返回所有满足连接条件的记录

图析：

❤️ 注意：
不满足连接条件的记录是不会在关联查询中被查询出来的

2.1.2 外连接

• 内连接返回满足连接条件的数据记录
• 有些情况下，需要返回那些不满足连接条件的记录，需要使用外连接
• 外连接不仅返回满足连接条件的记录，还将返回不满足连接条件的记录

外连接的分类：
（1）左外连接：以 JOIN 左侧表作为驱动表（所有数据都会被查询出来），那么当该表中的某条记录不满足连接条件时来自右侧表中的字段全部填 NULL。

--实现左外连接的一种简单写法
SELECT e.ename,e.deptno,d.deptno,d.dname
FROM emp e  JOIN dept d
ON e.deptno=d.deptno(+);

（2）右外连接：以 JOIN 右侧表作为驱动表（所有数据都会被查询出来），那么当该表中的某条记录不满足连接条件时来自左侧表中的字段全部填 NULL。

--实现右外连接的一种简单写法
SELECT e.ename,e.deptno,d.deptno,d.dname
FROM emp e  JOIN dept d
ON e.deptno(+)=d.deptno;

（3）全外连接：全外连接是指，除了返回满足条件的记录，还会返回不满足连接条件的所有其他行。全外连接是左外连接和右外连接查询结果的总和。

❤️ 简记：
（1）（+）写哪边，哪边加 NULL
（2）全外连接不能采用这种写法

2.1.3 自连接

• 自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单表中的多个列
• 表中的列参照同一个表中的其他列的情况称作自参照表
• 自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接。

图析：

简记：

• 自连接即当前表的一条记录可以对应当前表自己的多条记录
• 自连接是为了解决同类型数据但是有存在上下级关系的树状结构数据时使用

eg:

--查看每个员工及其领导的名字
SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno;

小试牛刀：
案例：查看 SMITH 的上司在哪个城市工作？

方法一：

SELECT e.ename,m.ename,m.deptno,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno
AND m.deptno=d.deptno
AND e.ename='SMITH';

方法二：

SELECT e.ename,m.ename,m.deptno,d.loc
FROM emp e JOIN emp m
ON e.mgr=m.empno
JOIN dept d
ON m.deptno=d.deptno
WHERE e.ename='SMITH';

❤️ 结果：
对比两种方法，尽量优先第二种方案，查询快。