Oracle基操笔记（四）：DQL

旧城等待， 2022-03-30 02:49 1451阅读 0赞

**本文包含大量实际操作，功能可通过索引找到**

一、DISTINCT去重

二、WHERE过滤

三、IN范围内匹配

四、BETWEEN...AND...范围查询

五、LIKE模糊匹配

六、AND与OR多条件查询

七、ORDER BY排序

八、GROUP BY分组查询

九、ROWNUM限制查询结果行数

十、使用聚合函数查询

十一、连接查询

十二、子查询

十三、合并查询

十四、表和字段取别名

十五、正则表达式查询

十六、综合案例

--------------------

**所谓DQL就是各种查询Query了、**

**套公式：**

SELECT  {* | <字段列表>}
            [
                FROM <表1>,<表2>...        
                [WHERE <表达式>]
                [GROUP BY <以...为分组>]
                [HAVING <表达式> [<算子> <表达式>...] ]
                [ORDER BY <字段1,字段2...> [DESC|ASC] ]
                [LIMIT [<offset>,] <行数>]    
            ]
    
    SELECT [字段1, 字段2...]
      FROM [表/视图]
     WHERE [表达式];

## 假设定义一个数据表fruits ##

CREATE TABLE fruits
    (
        f_id    varchar2(10)     	NOT NULL,
        s_id    number(6)        	NOT NULL,
        f_name  varchar(255)  	NOT NULL,
        f_price number (8,2)  	NOT NULL
    );

SELECT * FROM fruits;

f\_id  s\_id     f\_name       f\_price    
 a1     101   apple        5.20   
 a2     103   apricot       2.20   
 b1    101    blackberry    10.20   
 b2     104   berry         7.60   
 b5     107   xxxx         3.60  
 bs1    102   orange        11.20   
 bs2    105   melon         8.20  
 c0     101   cherry         3.20  
 l2     104   lemon         6.40   
 m1    106   mango        15.60  
 m2    105   xbabay        2.60   
 m3    105   xxtt          11.60  
 o2    103   coconut        9.20  
 t1     102   banana         10.30  
 t2     102   grape           5.30   
 t4     107   xbababa         3.60

=============================================================

## 一、DISTINCT去重 ##

**查询fruits表中s\_id字段的值，返回s\_id字段值且不得重复**

SELECT DISTINCT s_id FROM fruits;

查询结果如下：  
S\_ID   
\------  
101  
103   
104   
107   
102   
105  
106

## 二、WHERE过滤 ##

## 三、IN范围内匹配 ##

NOT IN就表示不在该范围

**查询s\_id为101和102的记录**

SELECT s_id,f_name, f_price 
      FROM fruits 
     WHERE s_id IN (101,102) 
     ORDER BY f_name;

## 四、BETWEEN...AND...范围查询 ##

NOT BETWEEN...AND...就是不在区间的记录

**查询价格在2.00元到10.20元之间的水果名称和价格**

SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;

## 五、LIKE模糊匹配 ##

**"%" 为0个或多个字符**

**"\_" 为匹配1个字符**

**查找所有以’b’字母开头的水果**

SELECT f_id, f_name
      FROM fruits
     WHERE f_name LIKE 'b%';

**查询f\_name中包含字母’g’的记录**

SELECT f_id, f_name
      FROM fruits
     WHERE f_name LIKE '%g%';

如果要匹配多个字符，则需要使用相同个数的’\_’。

**查询以字母’y’结尾，且’y’前面只有4个字母的记录**

SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____y';

## 六、AND与OR多条件查询 ##

**查询s\_id = 101，并且f\_price大于等于5的水果价格和名称**

SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;

F\_ID  F\_PRICE  F\_NAME     
\------  ---------  ------------  
a1    5.20   apple      
b1    10.20  blackberry|

前面的语句检索了s\_id=101的水果供应商所有价格大于等于5元的水果名称和价格。WHERE子句中的条件分为两部分，AND关键字指示Oracle返回所有同时满足两个条件的行。即使是id=101的水果供应商提供的水果，如果价格<5，或者是id不等于’101’的水果供应商里的水果不管其价格为多少，均不是要查询的结果。  
        上述例子的WHERE子句中只包含了一个AND语句，把两个过滤条件组合在一起。实际上可以添加多个AND过滤条件，增加条件的同时增加一个AND关键字。

## 七、ORDER BY排序 ##

正序(默认)：ASC

倒序：DESC

## 八、GROUP BY分组查询 ##

**根据s\_id对fruits表中的数据进行分组**

SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;

S\_ID   TOTAL  
\------  -----------------------------  
101   3  
102   3  
103   2    
104  2  
105  3  
106   1  
107   2

**HAVING过滤分组**

**根据s\_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息**

SELECT s_id, LISTAGG(f_name,',') within group (order by s_id ) AS Names 
      FROM fruits 
     GROUP BY s_id HAVING COUNT(f_name) > 1;

**LISTAGG(column\_name,',')同列合并到同一行，并使用“,”分隔。 **

S\_ID   NAMES    
\------  -----------------------------  
101   apple,blackberry,cherry  
102   grape,banana,orange     
103   apricot,coconut         
104   lemon,berry            
105   xbabay,xxtt,melon       
106   mango                
107   xxxx,xbababa

**查询订单价格大于100的订单号和总订单价格**

SELECT o_num,  SUM(quantity * item_price) AS orderTotal
      FROM tb_orderitems
     GROUP BY o_num
    HAVING SUM(quantity*item_price) >= 100
     ORDER BY orderTotal;

O\_NUM  ORDERTOTAL    
\---------   --------------  
30004    125.00    
30005    236.85    
30001    268.80    
30003    1000.00

**根据s\_id对fruits表中的数据进行分组，并显示记录数量**

**使用ROLLUP记录总和**

SELECT s_id, COUNT(*) AS Total 
      FROM fruits 
     GROUP BY ROLLUP(s_id) ;

S\_ID   TOTAL  
\------  -----------------------------  
101   3  
102   3  
103   2    
104  2  
105  3  
106   1  
107   2

由结果可以看到，经过Group分组后，在显示的结果后添加了一行“16”，该行Total列的值就是上面所有数值之和。

group by 后带 rollup 子句所返回的结果集，可以理解为各个分组所产生的结果集的并集且没有去掉重复数据

**fruits表中查找不同供应商提供的价格最低的水果**

SELECT s_id, MIN(f_price) AS min_price  
      FROM fruits   
     GROUP BY s_id;

顺序：From xxx表，按某字段Group分组，再求最小，最后Select查出来

## 九、ROWNUM限制查询结果行数 ##

不同与MySQL的Limit N

SELECT * FROM tb_emp WHERE ROWNUM<=4;

ID         NAME                   DEPTID      SALARY  
\------------ ------------------------- ------------ -----------  
           8 Simon                               21     9800.00  
           9 Leo                                 20    19099.00  
          10 Polo                                21     7600.00  
          11 Sysn                                25     9856.00

## 十、使用聚合函数查询 ##

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>作用</td> 
  </tr> 
  <tr> 
   <td>AVG()</td> 
   <td>返回某列平均数【尽量加WHERE过滤】</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>返回某列行数【用*就计NULL】</td> 
  </tr> 
  <tr> 
   <td> <p>MAX()</p> </td> 
   <td>求某列最大【字符串/日期也可】</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>求最小</td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td>求某列和【忽略NULL】</td> 
  </tr> 
 </tbody> 
</table>

**在fruits表中，查询s\_id=103的供应商的水果价格的平均值 **

SELECT AVG(ALL f_price) AS avg_price  
      FROM fruits   
     WHERE s_id = 103;

AVG\_PRICE   
\-----------  
  5.7

**在fruits表中，查询每一个供应商的水果价格的平均值**

SELECT s_id,AVG( ALL f_price) AS avg_price  
      FROM fruits  
     GROUP BY s_id;

S\_ID   AVG\_PRICE   
\------  --------------  
101   6.2  
102   8.933333   
103   5.7   
104   7   
105   7.466667   
106   15.6  
107   3.6

**fruits表中查找不同供应商提供的价格最低的水果**

SELECT s_id, MIN(f_price) AS min_price  
      FROM fruits   
     GROUP BY s_id;

S\_ID  MIN\_PRICE   
\--------  ---------  
101    3.2   
102    5.3   
103    2.2   
104    6.4   
105    2.6   
106    15.6   
107    3.6   
由结果可以看到，GROUP BY 关键字根据s\_id字段对记录进行分组，然后计算出每个分组中的最小值。

### GROUP BY 先分组，后分别求各组最小(大)值 ###

**employee表中，计算每个部门有多少员工**

SELECT dept_no, COUNT(*)
      FROM employee
     GROUP BY dept_no;

###  GROUP BY 先分组，后分别统计行数 ###

## 十一、连接查询 ##

连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。

## 1.内连接 ##

（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

**首先创建数据表suppliers**

CREATE TABLE suppliers
    (	
      s_id      number(9)     NOT NULL,
      s_name    varchar2(50)  NOT NULL,
      s_city    varchar2 (50) NULL,
      s_zip     varchar2 (10) NULL,
      s_call    varchar2 (50) NOT NULL,
      PRIMARY KEY (s_id)
    ) ;

**在fruits表和suppliers表之间常规查询 **

**查询之前，查看两个表的结构**

SQL> DESC fruits;
    SQL> DESC suppliers;

**fruits**:

名称      空值         类型              
\------- -    ------- -       ------------   
F\_ID      NOT NULL  VARCHAR2(10)    
S\_ID      NOT NULL  NUMBER(6)       
F\_NAME  NOT NULL  VARCHAR2(255)   
F\_PRICE  NOT NULL  NUMBER(8,2)

**suppliers:**

名称       空值       类型             
\------       --------      ------------   
S\_ID      NOT NULL  NUMBER(9)      
S\_NAME  NOT NULL  VARCHAR2(50)   
S\_CITY               VARCHAR2(50)   
S\_ZIP                 VARCHAR2(10)   
S\_CALL   NOT NULL  VARCHAR2(50)

fruits表和suppliers表中都有相同数据类型的字段s\_id，两个表通过s\_id字段建立联系。

**接下来从fruits表中查询f\_name、f\_price字段，从suppliers表中查询s\_id、s\_name**

SELECT suppliers.s_id, s_name,f_name, f_price 
      FROM fruits, suppliers  
     WHERE fruits.s_id = suppliers.s_id;

S\_ID  S\_NAME      F\_NAME    F\_PRICE   
\------  ----------------  ----------------  -------------  
101   FastFruit Inc.  apple          5.20    
103   ACME       apricot         2.20    
101   FastFruit Inc.  blackberry     10.20  
104   FNK Inc.     berry          7.60    
107   DK Inc.      xxxx          3.60    
102   LT Supplies   orange        11.20    
105   Good Set     melon          8.20    
101   FastFruit Inc.  cherry         3.20    
104   FNK Inc.     lemon         6.40    
106   Just Eat Ours   mango        15.60    
105   Good Set      xbabay        2.60    
105  Good Set      xxtt           11.60    
103  ACME        coconut        9.20   
102   LT Supplies    banana       10.30    
102   LT Supplies    grape         5.30    
107   DK Inc.       xbababa       3.60

在这里，SELECT语句与前面所介绍的一个最大的差别是：SELECT后面指定的列分别属于两个不同的表，（f\_name，f\_price）在表fruits中，而另外两个字段在表supplies中；同时FROM字句列出了两个表fruits和suppliers。

WHERE子句在这里作为过滤条件，指明只有两个表中的s\_id字段值相等的时候才符合连接查询的条件。返回的结果可以看到，显示的记录是由两个表中的不同列值组成的新记录。

因为fruits表和suppliers表中有相同的字段s\_id，因此在比较的时候，需要完全限定表名（格式为“表名.列名”），如果只给出s\_id，Oracle将不知道指的是哪一个，并返回错误信息。

下面的内连接查询语句返回与前面完全相同的结果。

**在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询**

SELECT suppliers.s_id, s_name,f_name, f_price  
      FROM fruits 
     INNER JOIN suppliers 
        ON fruits.s_id = suppliers.s_id;

在这里的查询语句中，两个表之间的关系通过INNER JOIN指定。使用这种语法的时候，连接的条件使用ON子句给出而不是WHERE，ON和WHERE后面指定的条件相同。

使用WHERE子句定义连接条件比较简单明了，而INNER JOIN语法是ANSI SQL的标准规范，使用INNER JOIN连接语法能够确保不会忘记连接条件，而且，WHERE子句在某些时候会影响查询的性能。

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

**查询供应f\_id= ‘a1’的水果供应商提供的其他水果种类**

SELECT f1.f_id, f1.f_name 
      FROM fruits  f1, fruits  f2  
     WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

F\_ID  F\_NAME     
\------  ------------  
a1   apple       
b1   blackberry   
c0   cherry

此处查询的两个表是相同的表，为了防止产生二义性，对表使用了别名，fruits表第1次出现的别名为f1，第2次出现的别名为f2，使用SELECT语句返回列时明确指出返回以f1为前缀的列的全名，WHERE连接两个表，并按照第2个表的f\_id对数据进行过滤，返回所需数据。

## 2.外连接 左连接 右连接 ##

连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。  
        但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。

**外连接分为左外连接和右外连接：**  
LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。  
RIGHT JOIN（右连接）：返回包括右表中的所有记录和右表中连接字段相等的记录。

创建一个订单表

CREATE TABLE orders
    (
      o_num  number(9)   NOT NULL,
      o_date  date         NOT NULL,
      c_id    number(9)    NOT NULL,
      PRIMARY KEY (o_num)
    ) ;

**在customers表和orders表中，查询所有客户，包括没有订单的客户 **

SELECT customers.c_id, orders.o_num  
      FROM customers 
      LEFT JOIN orders 
        ON customers.c_id = orders.c_id;

C\_ID   O\_NUM    
\-------  -------   
10001  30001  
10001  30005  
10002      
10003  30002   
10004  30003

**右连接是左连接的反向连接，将返回右表的所有行。**

## 3.复合条件连接查询 ##

复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。  
        在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息

SELECT customers.c_id, orders.o_num  
      FROM customers 
     INNER JOIN orders  
        ON customers.c_id = orders.c_id AND customers.c_id = 10001;

C\_ID   O\_NUM   
\-------   -------  
10001   30001   
10001   30005

结果显示，在连接查询时指定查询客户ID为10001的订单信息，添加了过滤条件之后返回的结果将会变少，因此返回结果只有两条记录。

**使用连接查询，并对查询的结果进行排序。**

**在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果排序**

SELECT suppliers.s_id, s_name,f_name, f_price  
      FROM fruits INNER JOIN suppliers  
        ON fruits.s_id = suppliers.s_id
     ORDER BY fruits.s_id;

S\_ID   S\_NAME     F\_NAME    F\_PRICE    
\------   ----------------  --------------   -----------   
101    FastFruit Inc.   apple        5.20  
101    FastFruit Inc.   blackberry   10.20  
101    FastFruit Inc.   cherry       3.20  
102    LT Supplies    grape        5.30  
102    LT Supplies    banana      10.30  
102    LT Supplies    orange      11.20  
103    ACME        apricot      2.20  
103    ACME        coconut      9.20  
104    FNK Inc.      lemon       6.40  
104    FNK Inc.      berry        7.60  
105    Good Set      xbabay       2.60  
105    Good Set      xxtt          11.60   
105    Good Set      melon        8.20   
106    Just Eat Ours   mango        15.60  
107    DK Inc.       xxxx          3.60  
107    DK Inc.       xbababa       3.60

## 十二、子查询 ##

子查询指一个查询语句嵌套在另一个查询语句内部的查询。此特性从Oracle4.1开始引入。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有ANY（SOME）、ALL、IN、EXISTS。子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<”、“<=”、“>”、“>=”和“!=”等。

## ##

### ANY (SOME) ###

**将tbl1中的num1的值与tbl2中的num2列进行比较，只要大于num2的任何1个值，即为符合查询条件的结果**

SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);

**返回tbl1表中比tbl2表num2 列所有值都大的值 **

SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);

## ##

### EXISTS ###

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

**查询suppliers表中是否存在s\_id=107的供应商，如果存在，则查询fruits表中的记录**

SELECT * 
      FROM fruits   
     WHERE EXISTS  (SELECT s_name FROM suppliers WHERE s_id = 107);

### IN ###

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

**在orderitems表中查询f\_id为c0的订单号，并根据订单号查询具有订单号的客户c\_id**

SELECT c_id 
      FROM orders 
     WHERE o_num IN  (SELECT o_num  FROM orderitems WHERE f_id = 'c0');

## 十三、合并查询 ##

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；

使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。

基本语法格式如下：  
        SELECT column,... FROM table1   
        UNION \[ALL\]  
        SELECT column,... FROM table2

**UNION将多个SELECT语句的结果组合成一个结果集合。【合集】**

**查询所有价格小于9的水果的信息，查询s\_id等于101和103所有的水果的信息，使用UNION连接查询结果**

SELECT s_id, f_name, f_price 
      FROM fruits
     WHERE f_price < 9.0
     UNION
    SELECT s_id, f_name, f_price 
      FROM fruits
    WHERE s_id IN(101,103);

S\_ID  F\_NAME    F\_PRICE   
\------  --------------- ----------   
101   apple       5.2   
103   apricot      2.2  
104   berry       7.6  
107   xxxx       3.6  
105   melon      8.2  
101   cherry      3.2  
104   lemon      6.4  
105   xbabay     2.6   
102   grape       5.3   
107   xbababa     3.6   
101   apple       5.2  
103   apricot      2.2   
101  blackberry    10.2   
101   cherry       3.2  
103   coconut      9.2

**可以分开查看每个SELECT语句的结果：**

SELECT s_id, f_name, f_price  
      FROM fruits
     WHERE f_price < 9.0;

S\_ID   F\_NAME  F\_PRICE   
\------   -----------  ---------   
101    apple      5.2  
103    apricot     2.2   
104    berry      7.6   
107    xxxx      3.6   
105    melon     8.2   
101    cherry     3.2   
104    lemon     6.4   
105    xbabay     2.6  
102    grape      5.3   
107   xbababa    3.6

SELECT s_id, f_name, f_price  
       FROM fruits
      WHERE s_id IN(101,103);

VS\_ID  F\_NAME  F\_PRICE   
\------  ------------  ---------  
101   apple      5.2  
103   apricot     2.2  
101   blackberry  10.2  
101   cherry      3.2  
103   coconut     9.2

由分开查询的结果可以看到，第1条SELECT语句查询价格小于9的水果，第2条SELECT语句查询供应商101和103提供的水果。使用UNION将两条SELECT语句分隔开，执行完毕之后把输出结果组合成单个的结果集，并删除重复的记录。

使用UNION ALL包含重复的行，在前面的例子中，分开查询时，两个返回结果中有相同的记录。UNION从查询结果集中自动去除了重复的行，如果要返回所有匹配行，而不进行删除，可以使用UNION ALL。

**查询所有价格小于9的水果的信息，查询s\_id等于101和103的所有水果的信息，使用UNION ALL连接查询结果**

SELECT s_id, f_name, f_price 
      FROM fruits
     WHERE f_price < 9.0
     UNION ALL
    SELECT s_id, f_name, f_price 
      FROM fruits
     WHERE s_id IN(101,103);

S\_ID  F\_NAME     F\_PRICE  
\------  ----------------  ---------   
101   apple        5.2  
103   apricot       2.2   
104   berry        7.6   
107   xxxx        3.6  
105   melon       8.2  
101   cherry       3.2   
104   lemon       6.4   
105   xbabay       2.6  
102   grape        5.3   
107   xbababa      3.6  
101   apple        5.2   
103   apricot       2.2  
101   blackberry    10.2  
101   cherry        3.2  
103   coconut       9.2

###         这里总的记录数等于两条SELECT语句返回的记录数之和，连接查询结果并没有去除重复的行。         ** ** UNION和UNION ALL的区别：使用UNION ALL的功能是不删除重复行，加上ALL关键字语句执行时所需要的资源少，所以尽可能的使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用UNION ALL以提高查询效率。 ###

## 十四、表和字段取别名 ##

表名 \[AS\] 表别名

列名 \[AS\] 列别名

(略)

## 十五、正则表达式查询 ##

LIKE  是 标准的 [SQL][] 处理。 [SQL][] Server, DB2 , My[SQL][] 等大部分数据库， 都支持的写法。

REGEXP\_LIKE 是 [Oracle][] 特有的， 正则表达式的 LIKE 的处理。

REGEXP_LIKE ( expression, pattern [, match_parameter ] )

**参数**

*  `expression` \- 字符表达式，例如列或字段。它可以是`VARCHAR2`，`CHAR`，`NVARCHAR2`，`NCHAR`，`CLOB`或`NCLOB`数据类型。
 *  `pattern` \- 正则表达式匹配信息。它可以是以下的组合：

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>^</code></td> 
   <td>匹配字符串的开头。如果使用<code>match_parameter</code>为'<code>m</code>'，它匹配表达式中任意位置的一行的开始。</td> 
  </tr> 
  <tr> 
   <td><code>$</code></td> 
   <td>匹配字符串的结尾。如果使用<code>match_parameter</code>为'<code>m</code>'，它匹配表达式中任意位置的一行的结尾。</td> 
  </tr> 
  <tr> 
   <td><code>*</code></td> 
   <td>匹配<span style="color:#f33b45;">零个或多个</span>匹配项。</td> 
  </tr> 
  <tr> 
   <td><code>+</code></td> 
   <td>匹配一个或多个匹配项。<span style="color:#f33b45;">(至少一次)</span></td> 
  </tr> 
  <tr> 
   <td><code>?</code></td> 
   <td>匹配<span style="color:#f33b45;">零或一次</span>出现。</td> 
  </tr> 
  <tr> 
   <td><code>.</code></td> 
   <td>匹配除<code>NULL</code>之外的<span style="color:#f33b45;">任意一个字符</span>。</td> 
  </tr> 
  <tr> 
   <td> <p>|</p> </td> 
   <td>使用像“OR”一样指定多个替代。</td> 
  </tr> 
  <tr> 
   <td><code>[]</code></td> 
   <td>用于指定要在其中尝试匹配列表中任何一个字符的匹配列表。</td> 
  </tr> 
  <tr> 
   <td><code>[^]</code></td> 
   <td>用于指定一个不匹配列表，要尝试匹配<span style="color:#f33b45;">除</span>列表中的<span style="color:#f33b45;">字符之外</span>的任何字符。</td> 
  </tr> 
  <tr> 
   <td><code>()</code></td> 
   <td>用于将表达式分组为子表达式。</td> 
  </tr> 
  <tr> 
   <td><code>{m}</code></td> 
   <td>匹配<code>m</code>次</td> 
  </tr> 
  <tr> 
   <td><code>{m,}</code></td> 
   <td>匹配至少<code>m</code>次。</td> 
  </tr> 
  <tr> 
   <td><code>{m,n}</code></td> 
   <td>匹配至少<code>m</code>次，但不超过<code>n</code>次。</td> 
  </tr> 
  <tr> 
   <td><code>\n</code></td> 
   <td><code>n</code>是<code>1</code>和<code>9</code>之间的数字。匹配在遇到<code>\n</code>之前在<code>()</code>中找到的第<code>n</code>个子表达式。</td> 
  </tr> 
  <tr> 
   <td><code>[..]</code></td> 
   <td>匹配一个可以是多个字符的排序规则元素。</td> 
  </tr> 
  <tr> 
   <td><code>[::]</code></td> 
   <td>匹配字符类。</td> 
  </tr> 
  <tr> 
   <td><code>[==]</code></td> 
   <td>匹配等价类。</td> 
  </tr> 
  <tr> 
   <td><code>\d</code></td> 
   <td>匹配<span style="color:#f33b45;">数字</span>字符。</td> 
  </tr> 
  <tr> 
   <td><code>\D</code></td> 
   <td>匹配非数字字符。</td> 
  </tr> 
  <tr> 
   <td><code>\w</code></td> 
   <td>匹配<span style="color:#f33b45;">字母</span>字符。</td> 
  </tr> 
  <tr> 
   <td><code>\W</code></td> 
   <td>匹配非字母字符。</td> 
  </tr> 
  <tr> 
   <td><code>\s</code></td> 
   <td>匹配空格字符。</td> 
  </tr> 
  <tr> 
   <td><code>\S</code></td> 
   <td>匹配非<span style="color:#f33b45;">空格</span>字符。</td> 
  </tr> 
  <tr> 
   <td><code>\A</code></td> 
   <td>匹配字符串的开头或匹配在换行符之前的字符串的结尾。</td> 
  </tr> 
  <tr> 
   <td><code>\Z</code></td> 
   <td>匹配字符串的结尾。</td> 
  </tr> 
  <tr> 
   <td><code>*?</code></td> 
   <td>与前前面的模式匹配零个或多个匹配项。</td> 
  </tr> 
  <tr> 
   <td><code>+?</code></td> 
   <td>匹配前面一个模式一次或多次。</td> 
  </tr> 
  <tr> 
   <td><code>??</code></td> 
   <td>匹配前面一个模式零或一个发生。</td> 
  </tr> 
  <tr> 
   <td><code>{n}?</code></td> 
   <td>匹配前面的模式<code>n</code>次。</td> 
  </tr> 
  <tr> 
   <td><code>{n,}?</code></td> 
   <td>与前一个模式匹配至少<code>n</code>次。</td> 
  </tr> 
  <tr> 
   <td><code>{n,m}?</code></td> 
   <td>与前一个模式匹配至少<code>n</code>次，但不超过<code>m</code>次。</td> 
  </tr> 
 </tbody> 
</table>

*  `match_parameter` \- 可选的。它允许修改`REGEXP_LIKE`条件的匹配行为。它可以是以下的组合：

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>'c'</td> 
   <td>执行区分大小写匹配。</td> 
  </tr> 
  <tr> 
   <td>'i'</td> 
   <td>执行不区分大小写的匹配。</td> 
  </tr> 
  <tr> 
   <td>'n'</td> 
   <td>允许句点字符(<code>.</code>)与换行符匹配。 默认情况下，句点是通配符。</td> 
  </tr> 
  <tr> 
   <td>'m'</td> 
   <td>表达式假定有多个行，其中<code>^</code>是行的开始，<code>$</code>是行的结尾，不管表达式中这些字符的位置如何。默认情况下，表达式假定为单行。</td> 
  </tr> 
  <tr> 
   <td>'x'</td> 
   <td>忽略空格字符。默认情况下，空格字符与任何其他字符一样匹配。</td> 
  </tr> 
 </tbody> 
</table>

**返回其`last_name`为`Anderson`，`Andersen`或`Andersan`的联系人。 “|” 可匹配字母“`o`”，“`e`”或“`a`”。**

SELECT last_name
      FROM contacts
     WHERE REGEXP_LIKE (last_name, 'Anders(o|e|a)n');

**使用`REGEXP_LIKE`条件来匹配字符串的开头  (^n) 。 **

返回其`last_name`以'`n`'字母开头的所有联系人。

SELECT last_name
      FROM contacts
     WHERE REGEXP_LIKE (last_name, '^A(*)');

**使用`REGEXP_LIKE`条件来匹配字符串的结尾  (n$)。**

返回其`last_name`以'`n`'字母结尾的所有联系人。

SELECT last_name
      FROM contacts
     WHERE REGEXP_LIKE (last_name, '(*)n$');

部分來源：简书  
作者：易百教程  
链接：https://www.jianshu.com/p/dcf2be130108

**fruits表中，查询f\_name字段值出现字母’x’至少2次的记录**

SELECT * 
      FROM fruits 
     WHERE REGEXP_LIKE(f_name,'x{2,}');

**fruits表中，查询f\_name字段值出现字符串“ba”最少1次，最多3次的记录**

SELECT * 
      FROM fruits 
     WHERE REGEXP_LIKE(f_name,'ba{1,3}');

### 【注意】 ###

1.  WHERE子句尽量加圆括号 ( )，以便明确操作符的次序。
2.  使用REGEXP( )函数时，可能会出现“明明通配符正确，就是无法查出结果”的情况，Oracle在存储字符串数据时，可能会将两端带有空格的字符存入记录中，而在查询表中记录时，DBMS并不能明确地显示空格。解决方案：使用TRIM( ) 函数剔除字符串两端空格。

## 十六、综合案例 ##

[SQL]: https://www.baidu.com/s?wd=SQL&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[Oracle]: https://www.baidu.com/s?wd=Oracle&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao