1.LIKE

LIKE子句一般配合通配符使用，常用的通配符有%和_。

  • %表示任何字符出现任意次数
  • _表示单个字符

    1.1语法

    ```sql SELECT FROM table_name WHERE column LIKE ‘XXXX%’; —可以根据自己的需求指定格式 —‘%a’,以a开头的 —‘a%’,以a结束的 —‘%aaa%’包含aaa的 …..

SELECT FROM tablename WHERE column LIKE ‘XXXX‘; — ‘_00%’，第二和第三个位置上有 00 的 — ‘2_3’，以2开头以3结尾的的3位数

  1. **注意**：postgreSQL中integer类型需要显式的作为字符串来展示
  2. ```sql
  3. runoobdb=# SELECT * FROM company WHERE age LIKE '2%';
  4. 错误:  操作符不存在: integer ~~ unknown
  5. 第1行SELECT * FROM company WHERE age LIKE '2%';
  6.                                      ^
  7. 提示:  没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
  10. runoobdb=# SELECT * FROM company WHERE age::text  LIKE '2%';
  11.  id | name  | age |                      address                       | salary
  12. ----+-------+-----+----------------------------------------------------+--------
  13.   2 | Allen |  25 | Texas                                              |  15000
  14.   3 | Teddy |  23 | Norway                                             |  20000
  15.   4 | Mark  |  25 | Rich-Mond                                          |  65000
  16.   5 | David |  27 | Texas                                              |  85000
  17.   6 | Kim   |  22 | South-Hall                                         |  45000
  18.   7 | James |  24 | Houston                                            |  10000
  19. (6 行记录)

1.2示例

以company表为例，表中内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
  6 | Kim   |  22 | South-Hall                                         |  45000
  7 | James |  24 | Houston                                            |  10000
(7 行记录)

找出 AGE 以 2 开头的数据：

runoobdb=# SELECT * FROM company WHERE age::text  LIKE '2%';
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
  6 | Kim   |  22 | South-Hall                                         |  45000
  7 | James |  24 | Houston                                            |  10000
(6 行记录)

找出 address 字段中含有 - 字符的数据：

runoobdb=# SELECT * FROM company WHERE address LIKE '%-%';
 id | name | age |                      address                       | salary
----+------+-----+----------------------------------------------------+--------
  4 | Mark |  25 | Rich-Mond                                          |  65000
  6 | Kim  |  22 | South-Hall                                         |  45000
(2 行记录)

找出name字段中以A开头的数据：

runoobdb=# SELECT * FROM COMPANY WHERE name LIKE 'A%';
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  2 | Allen |  25 | Texas                                              |  15000
(1 行记录)

找出name 字段中以J开头第三个字母是m且以s结尾的数据，

runoobdb=# SELECT * FROM company WHERE name LIKE 'J_m%s';
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  7 | James |  24 | Houston                                            |  10000
(1 行记录)

2.LIMIT

2.1语法

--和SELECT一起使用
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

--和OFFSET一起使用
--OFFSET指定从第几行开始提取数据（默认比设定的值加一，比如OFFSET 1,就是从第2行开始）
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

2.2示例

以company表为例：

runoobdb=# SELECT * FROM company ;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
  6 | Kim   |  22 | South-Hall                                         |  45000
  7 | James |  24 | Houston                                            |  10000
(7 行记录)

限定找出的数据只显示4条：

--只显示前四条数据
runoobdb=# SELECT * FROM company LIMIT 4;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
(4 行记录)

从第三位开始提取 3 个记录：

runoobdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
(3 行记录)

--从第一行开始提取3行数据
runoobdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 0;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
(3 行记录)

--从第二行开始提取4行数据
runoobdb=# SELECT * FROM COMPANY LIMIT 4 OFFSET 1;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
(4 行记录)