WHERE子句

当我们需要根据指定条件从表中查询数据时,就可以在SELECT语句中添加WHERE子句,从而过滤掉我们不需要数据。WHERE子句构成一个行选择表达式,用于获取满足指定条件的数据,如果给定的条件满足,才返回从表中提取到的具体数值。

语法格式

  1. SELECT
  2. { * | [column, ...] }
  3. [ FROM from_item [, ...] ]
  4. [ WHERE condition ];

参数说明

  • WHERE子句

    WHERE子句构成一个行选择表达式,用来缩小SELECT查询的范围。在WHERE子句中可以使用比较运算符或逻辑运算符,例如 >, <, =, LIKE, NOT 等等。

  • condition

    condition是返回值为布尔型的任意表达式,任何不满足该条件的行都不会被检索。

示例

使用逻辑运算符读取表customer_t1。

  • AND

    找到c_customer_id为hello且c_customer_sk为3869的行。

    1. openGauss=# SELECT * FROM customer_t1 WHERE c_customer_sk = 3869 AND c_customer_id = 'hello';
    2. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
    3. ---------------+---------------+--------------+-------------+--------
    4. 3869 | hello | Grace | | 1000
    5. 3869 | hello | Grace | | 1000
    6. 3869 | hello | | |
    7. 3869 | hello | | |
    8. (4 rows)
  • OR

    找到c_customer_sk大于6985或者c_customer_id为hello的行。

    1. openGauss=# SELECT * FROM customer_t1 WHERE c_customer_sk > 6985 OR c_customer_id = 'hello';
    2. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
    3. ---------------+---------------+--------------+-------------+--------
    4. 3869 | hello | Grace | | 1000
    5. 3869 | hello | Grace | | 1000
    6. 3869 | hello | | |
    7. 3869 | hello | | |
    8. 9976 | world | James | | 5000
    9. (5 rows)
  • NOT NULL

    找到customer_t1中c_last_name字段不为空的行。

    1. openGauss=# SELECT * FROM customer_t1 WHERE c_last_name IS NOT NULL;
    2. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
    3. ---------------+---------------+--------------+-------------+--------
    4. (0 rows)
  • BETWEEN

    找到c_customer_sk在4000和9000之间的行。

    1. openGauss=# SELECT * FROM customer_t1 WHERE c_customer_sk BETWEEN 4000 AND 9000;
    2. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
    3. ---------------+---------------+--------------+-------------+--------
    4. 6985 | maps | Joes | | 2200
    5. 4421 | Admin | Local | | 3000
    6. (2 rows)