Oracle 查询(SELECT)语句（一）

简介

本文介绍 Oracle 中查询(SELECT)语句的使用，在 SQL 中 SELECT 语句相对增删改(CUD/DML)的语句知识点较多，也是比较重要 SQL 编程技术，所以这里拿出来单独学习。

首先，我们先来理一下思路，我们知道查询通常包含以下内容：

Ø  查询所有列(*)

Ø  查询指定的列

Ø  根据指定的条件查询，即 WHERE 条件

Ø  去除重复行，即 DISTINCT 子句

Ø  查询数据聚合，即 COUNT()、MAX()、MIN() 等

Ø  按条件输出，即 CASE WHEN THEN 子句

Ø  排序(ORDER BY)

Ø  分组(GROUP BY)与分组过滤(HAVING)

Ø  连接查询（INNER JOIN、LEFT JOIN 等）

Ø  子查询（SELECT 子查询、WHERE 子查询等）

Ø  其他

好了，既然知道了有这些查询功能，下面我们就一一突破，准备了以下内容：

1.   准备数据

2.   SELECT 语法

3.   基本用法

4.   WHERE 子句

5.   聚合查询

6.   CASE WHEN THEN 子句

7.   排序

8.   分组与过滤

9.   多表查询

10.  子查询（单行与多行子查询）

11.  连接查询

1.   准备数据

1)   创建表结构

CREATE TABLE JNUser (

  UserId NUMBER(10) NOT NULL,

  Name VARCHAR2(8) NOT NULL,

  Sex NUMBER(1) NOT NULL,    --性别（0 女，1 男，2 未知）

  Age NUMBER(2) NOT NULL,

  Birthday DATE NOT NULL,

  City VARCHAR2(6) NOT NULL,

  IdNumber CHAR(18) NOT NULL,

  Salary FLOAT,

  Remarks VARCHAR2(4000) NOT NULL,

  PRIMARY KEY (UserId),

  CONSTRAINT UQ_JNUser_IdNumber UNIQUE (IdNumber),

  CONSTRAINT CK_JNUser_Sex CHECK (Sex >= 0 AND Sex <= 2)

);

CREATE TABLE JNOrder (

  OrderId NUMBER(10) NOT NULL,

  UserId NUMBER(10) NOT NULL,

  OrderNo VARCHAR2(16) NOT NULL,

  TotalAmount FLOAT NOT NULL,

  OrderDate DATE NOT NULL,

  Remarks VARCHAR2(4000),

  PRIMARY KEY (OrderId),

  CONSTRAINT UQ_JNOrder_OrderNo UNIQUE (OrderNo)

);

2)   插入数据

INSERT ALL

--用户数据

INTO JNUser VALUES(1, '孙器璇', 2, 17, to_date('2002/11/18', 'yyyy/mm/dd'), '北京', '17730094858437637X', 10400, 'hello')

INTO JNUser VALUES(2, '周器璇', 0, 22, to_date('1966/05/12', 'yyyy/mm/dd'), '武汉', '534668342907162757', 15000, '用户2')

INTO JNUser VALUES(3, '张二娃', 1, 24, to_date('1967/01/24', 'yyyy/mm/dd'), '杭州', '36935324879542561X', 7600, '大家好，我是张二娃')

INTO JNUser VALUES(4, '陈悴', 2, 22, to_date('1984/10/11', 'yyyy/mm/dd'), '武汉', '585823614699788016', 7100, '你好，贵姓？')

INTO JNUser VALUES(5, '钱悴', 2, 58, to_date('1961/10/21', 'yyyy/mm/dd'), '深圳', '449489822531507067', 15320, 'hello 张二娃')

INTO JNUser VALUES(6, '张无忌', 1, 61, to_date('1988/02/24', 'yyyy/mm/dd'), '杭州', '385929993868497572', 17800, '哈啰，我是张无忌')

INTO JNUser VALUES(7, '张大彪', 1, 24, to_date('1995/05/16', 'yyyy/mm/dd'), '上海', '466484458398445233', 1200, '我说了，我是彪爷')

INTO JNUser VALUES(8, '冯小二', 1, 29, to_date('1976/09/28', 'yyyy/mm/dd'), '广州', '40016865591929392X', 5700, '客观，吃点莫子？')

INTO JNUser VALUES(9, 'DBA', 2, 32, to_date('1997/04/28', 'yyyy/mm/dd'), '上海', '488500420672587475', 16000, '小崽子们，你们好，我是 DBA')

INTO JNUser VALUES(10, '陈双', 0, 18, to_date('1995/02/07', 'yyyy/mm/dd'), '武汉', '942310204386191671', 10343, '我是双儿')

--订单数据

INTO JNOrder VALUES(1, 2, '58977924501badf7', 2620, to_date('2019/06/10', 'yyyy/mm/dd'), '又是双十一，我卖完再剁手啦')

INTO JNOrder VALUES(2, 3, '316626433f743978', 1115, to_date('2019/08/19', 'yyyy/mm/dd'), '其他没什么，就是想买')

INTO JNOrder VALUES(3, 4, '8698789361c78946', 1734, to_date('2019/03/16', 'yyyy/mm/dd'), '')

INTO JNOrder VALUES(4, 5, '58716471589e3df2', 897, to_date('2019/11/13', 'yyyy/mm/dd'), ' ')

INTO JNOrder VALUES(5, 8, '5583165337e0ee25', 2097, to_date('2019/01/21', 'yyyy/mm/dd'), '我已下单，快点发货，老板')

INTO JNOrder VALUES(6, 8, '395799340826d6f2', 304, to_date('2019/02/27', 'yyyy/mm/dd'), '   ')

INTO JNOrder VALUES(7, 3, '887799782996b3f1', 1246, to_date('2019/09/20', 'yyyy/mm/dd'), '老板，给我来个好看的包装盒，我要送老丈人')

INTO JNOrder VALUES(8, 8, '39482046468266d6', 306, to_date('2019/02/28', 'yyyy/mm/dd'), NULL)

SELECT * FROM DUAL;

COMMIT;

2.   SELECT 语法

SELECT <列名1> [<,列名2>]… FROM <表名或视图名>

[WHERE <条件表达式>]

[GROUP BY <列名1> [HAVING <条件表达式>]

[ORDER BY <列名1> [ASC | DESC]]

说明：如果输出所有列，可以指定为"*"。这里简单阐述下(SELECT * 与 SELECT 所有列)的一些区别：

提示：如有其他看法，欢迎讨论。

3.   基本语法

1)   查询所有列

SELECT * FROM JNUser;

SELECT * FROM JNOrder;

2)   查询指定列

SELECT UserId, Name FROM JNUser;

3)   定义表和列的别名

SELECT UserId 用户Id, Name 用户名, Age AS "性 别+-", U.City, U.City "City", '关键字作为列名' AS "Select" FROM JNUser U;

说明：

1.   定义表别名时，不能使用 AS 关键字，定义别名后可以使用或不使用别名；

2.   定义列别名时，可以使用 AS 关键字也可以不使用；

3.   定义列别名时，如果无特殊字符（中文或英文），后面直接跟上别名即可；

4.   定义列别名时，使用("")双引号可以解决以下两个问题：

1)   别名中包含特殊字符，如：空格、-、+等字符；

2)   别名中的英文显示为指定的大小写（默认为大写）；

3)   别名使用到对象名或保留字（关键字）时；

4)   ("")双引号表示列名将按所指定的显示。

4)   算术运算符(+、-、*、/)

SELECT Salary, Salary + 200, Salary - 200, Salary * 12, Salary / 30 FROM JNUser;

5)   比较运算符(>、>=、<、<=、=、!= | <>)

SELECT * FROM JNUser WHERE City <> '上海'; --或者使用 !=

6)   列连接

SELECT UserId || ' - ' || Name || ' - ' || City AS UserDesc FROM JNUser;

提示：|| 在 Oracle 中用于字符串拼接，类似其他语言中字符串拼接的 + 号。

7)   构建表达式

SELECT ('SELECT * FROM ' || TABLE_NAME || ';') AS SEL FROM ALL_TABLES WHERE OWNER = 'USER01';

8)   去除重复行

SELECT DISTINCT Age, City FROM JNUser;

说明：去除每列相同的数据行，只返回任意两行不重复的记录。

9)   处理 NULL 值

SELECT ORDERNO, NVL(REMARKS, '无备注') AS REMARKS FROM JNOrder; --当 REMARKS 字段为 NULL 时，将返回“无备注”

10)  判断 NULL 与非 NULL

SELECT * FROM JNOrder WHERE REMARKS IS NULL;   --非空使用 IS NOT NULL

4.   WHERE 子句

1)   复合条件

SELECT * FROM JNUser WHERE Salary > 10000; --查出工资大于10000的用户

SELECT * FROM JNUser WHERE Sex = 0 AND Salary > 10000; --查出性别为女性，且工资大于10000的用户

SELECT * FROM JNUser WHERE (Sex = 0 OR Sex = 2) AND Salary > 10000; --查出性别为女性或者未知，且工资大于10000的用户

2)   IN 子句

IN 子句表示取出值包含在列举值范围内的记录。

SELECT * FROM JNUser WHERE City IN('武汉', '上海', '北京') ORDER BY City; --查出所在城市在武汉、上海、北京的用户，并按城市升序排序

3)   BETWEEN 子句

BETWEEN 子句表示取出值在起始值与结束值之间的记录，且包含起始值与结束值（它是包头包围的闭区间）。

SELECT * FROM JNUser WHERE Salary BETWEEN 10400 AND 16000; --查出工资在10400（包含）至16000（包含）之间的用户

4)   NOT 取反

NOT 子句用于对条件表达式进行取反，例如：

SELECT * FROM JNUser WHERE NOT(AGE > 30); --实际查出了年龄小于或等于30的用户

5)   EXISTS、NOT EXISTS

EXISTS 子句可以用于判断是否存在结果集，EXISTS 表示当存在结果集时为 true，否则为 false；而 NOT EXISTS 则对 EXISTS 的结果进行取反。

SELECT * FROM JNUser t1 WHERE EXISTS(SELECT 1 FROM JNOrder t2 WHERE t1.userid = t2.userid); --查询下过订单的用户

6)   LIKE 模糊匹配

Ø  %表示零到多个字符；

Ø  _表示任意单个字符；

Ø  []表示在列举范围内的字符；

SELECT * FROM JNUser WHERE Remarks LIKE 'h%'; --查出备注以 h 开头的用户（注意：匹配时区分大小写）

SELECT * FROM JNUser WHERE Remarks LIKE '%好%'; --查出备注中包含“好”字的用户

SELECT * FROM JNUser WHERE Name LIKE '张__'; --查出姓张的，并且名为2个字的用户

注意：在实际应用场景中不到万不得已，尽量避免使用 LIKE 模糊查询，因为使用模糊的字段就不能使用索引了，影响查询效率。

7)   是否区分大小写

SELECT * FROM JNUser WHERE REMARKS = 'hello';  --1条记录

SELECT * FROM JNUser WHERE REMARKS = 'Hello';  --0条记录

结论：Oracle 中的值是区分大小写的，但是执行语句或表名、列名、等对象名不区分大小写。

8)   日期比较（查询1988年之后出生的用户）

SELECT * FROM JNUser WHERE BIRTHDAY >= '1988-01-01';  --ORA-01861: 文字与格式字符串不匹配

SELECT * FROM JNUser WHERE BIRTHDAY >= to_date('1988-01-01', 'yyyy-mm-dd');   --使用 to_date() 函数转为日期类型

SELECT * FROM JNUser WHERE to_char(BIRTHDAY, 'yyyy-mm-dd') >= '1988-01-01';   --或者使用 to_char() 函数转为字符类型

SELECT * FROM JNUser WHERE to_char(BIRTHDAY, 'mm') >= '05';   --大于5月份的用户

9)   使用&变量

SELECT * FROM JNUser WHERE City = '&City' AND Salary > &Salary;

说明：该查询方式只适合在 PL/SQL Developer 中使用，提供一个条件参数占位符，用于在窗口中输入参数值。

5.   聚合查询

聚合函数是 Oracle 提供的内置函数，用于计算某一列的聚合计算（如：数量、平均值等）

SELECT COUNT(*) AS COUNT FROM JNUser WHERE City = '上海'; --统计有多少上海用户，*可以改为数字1

SELECT MAX(SALARY) AS MAX FROM JNUser WHERE City = '上海'; --查询上海用户的最高薪资

SELECT MIN(SALARY) AS MIN FROM JNUser WHERE City = '上海'; --查询上海用户的最低薪资

SELECT SUM(SALARY) AS SUM FROM JNUser WHERE City = '上海'; --查询上海用户的总薪资

SELECT AVG(SALARY) AS AVG FROM JNUser WHERE City = '上海'; --查询上海用户的平均薪资

SELECT COUNT(DISTINCT t."OrderId") FROM temp01 t WHERE t."CategoryId" = t1."CategoryId"    --统计时进行去重

注意：

1.   MAX 和 MIN 函数计算出两个或两个以上的最大值或最小值时，同样之后只返回一个值。

2.   COUNT、SUM 和 AVG 函数计算统计时，为 NULL 的值将不参与计算。

6.   CASE WHEN THEN 子句

CASE WHEN THEN 定义在 SELECT 与 FROM 之间，用于判断当条件符合 WHEN 时，就返回 THEN 对应的值，否则返回 ELSE 中的值。

1)   第一种写法

SELECT City, (CASE City WHEN '上海' THEN 'SH' WHEN '武汉' THEN 'WH' ELSE 'WZ' END) AS JianCheng FROM JNUser;

2)   第二种写法

SELECT City, (CASE WHEN City = '上海' THEN 'SH' WHEN City = '武汉' THEN 'WH' ELSE 'WZ' END) AS JianCheng FROM JNUser;