操作符

2018-08-12 22:09 更新

SQL 操作符

每個操作符都是一個保留字，主要用于在 SQL 語句的 WHERE 子句中執(zhí)行各種操作，例如比較和算術(shù)運算等。

操作符在 SQL 語句中指定了條件，并可以將同一語句中的不同條件連接起來。

  • 算術(shù)運算符
  • 比較運算符
  • 邏輯運算符
  • 用于否定條件的運算符

SQL 算術(shù)運算符

這里一些有關(guān) SQL 算術(shù)運算符如何使用的簡單示例：

    SQL> select 10+ 20;
    +--------+
    | 10+ 20 |
    +--------+
    |     30 |
    +--------+
    1 row in set (0.00 sec)

    SQL> select 10 * 20;
    +---------+
    | 10 * 20 |
    +---------+
    |     200 |
    +---------+
    1 row in set (0.00 sec)

    SQL> select 10 / 5;
    +--------+
    | 10 / 5 |
    +--------+
    | 2.0000 |
    +--------+
    1 row in set (0.03 sec)

    SQL> select 12 %  5;
    +---------+
    | 12 %  5 |
    +---------+
    |       2 |
    +---------+
    1 row in set (0.00 sec)

假設(shè)變量 a 的值為 10， 變量 b 的值為 20，那么：

操作符 描述 示例
+ 相加：將符號兩邊的數(shù)值加起來。 a + b 得 30
- 相減：從最邊的操作數(shù)中減去右邊的操作數(shù)。 a - b 得 -10
* 相乘：將兩邊的操作數(shù)相乘。 a * b 得 200
/ 相除：用右邊的操作數(shù)除以左邊的操作數(shù)。 b / a 得 2
% 取余：用右邊的操作數(shù)除以左邊的操作數(shù)，并返回余數(shù)。 b % a 得 0

SQL 比較運算符

考慮 CUSTOMERS 表，表中的記錄如下所示：

    SQL> SELECT * FROM CUSTOMERS;
    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+
    7 rows in set (0.00 sec)

下面是一些關(guān)于如何使用 SQL 比較運算符的簡單示例：

    SQL> SELECT * FROM CUSTOMERS WHERE SALARY > 5000;
    +----+----------+-----+---------+----------+
    | ID | NAME     | AGE | ADDRESS | SALARY   |
    +----+----------+-----+---------+----------+
    |  4 | Chaitali |  25 | Mumbai  |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal  |  8500.00 |
    |  7 | Muffy    |  24 | Indore  | 10000.00 |
    +----+----------+-----+---------+----------+
    3 rows in set (0.00 sec)

    SQL>  SELECT * FROM CUSTOMERS WHERE SALARY = 2000;
    +----+---------+-----+-----------+---------+
    | ID | NAME    | AGE | ADDRESS   | SALARY  |
    +----+---------+-----+-----------+---------+
    |  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
    |  3 | kaushik |  23 | Kota      | 2000.00 |
    +----+---------+-----+-----------+---------+
    2 rows in set (0.00 sec)

    SQL>  SELECT * FROM CUSTOMERS WHERE SALARY != 2000;
    +----+----------+-----+---------+----------+
    | ID | NAME     | AGE | ADDRESS | SALARY   |
    +----+----------+-----+---------+----------+
    |  2 | Khilan   |  25 | Delhi   |  1500.00 |
    |  4 | Chaitali |  25 | Mumbai  |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal  |  8500.00 |
    |  6 | Komal    |  22 | MP      |  4500.00 |
    |  7 | Muffy    |  24 | Indore  | 10000.00 |
    +----+----------+-----+---------+----------+
    5 rows in set (0.00 sec)

    SQL> SELECT * FROM CUSTOMERS WHERE SALARY <> 2000;
    +----+----------+-----+---------+----------+
    | ID | NAME     | AGE | ADDRESS | SALARY   |
    +----+----------+-----+---------+----------+
    |  2 | Khilan   |  25 | Delhi   |  1500.00 |
    |  4 | Chaitali |  25 | Mumbai  |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal  |  8500.00 |
    |  6 | Komal    |  22 | MP      |  4500.00 |
    |  7 | Muffy    |  24 | Indore  | 10000.00 |
    +----+----------+-----+---------+----------+
    5 rows in set (0.00 sec)

    SQL> SELECT * FROM CUSTOMERS WHERE SALARY >= 6500;
    +----+----------+-----+---------+----------+
    | ID | NAME     | AGE | ADDRESS | SALARY   |
    +----+----------+-----+---------+----------+
    |  4 | Chaitali |  25 | Mumbai  |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal  |  8500.00 |
    |  7 | Muffy    |  24 | Indore  | 10000.00 |
    +----+----------+-----+---------+----------+
    3 rows in set (0.00 sec)

假設(shè)變量 a 的值為 10， 變量 b 的值為 20，那么：

操作符 描述 示例
= 檢查兩個操作數(shù)的值是否相等，是的話返回 true。 (a = b) 不為 true。
!= 檢查兩個操作數(shù)的值是否相等，如果不等則返回 true。 (a != b) 為 true。
檢查兩個操作數(shù)的值是否相等，如果不等則返回 true。 (a b) 為真。
> 檢查左邊的操作數(shù)是否大于右邊的操作數(shù)，是的話返回真。 (a > b) 不為 true。
檢查左邊的操作數(shù)是否小于右邊的操作數(shù)，是的話返回真。 (a
>= 檢查左邊的操作數(shù)是否大于或等于右邊的操作數(shù)，是的話返回真。 (a >= b) 不為 true。
檢查左邊的操作數(shù)是否小于或等于右邊的操作數(shù)，是的話返回真。 (a
! 檢查左邊的操作數(shù)是否不小于右邊的操作數(shù)，是的話返回真。 (a !
!> 檢查左邊的操作數(shù)是否不大于右邊的操作數(shù)，是的話返回真。 (a !> b) 為 true。

SQL 邏輯運算符

考慮 CUSTOMERS 表，表中的記錄如下所示：

    SQL> SELECT * FROM CUSTOMERS;
    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+
    7 rows in set (0.00 sec)

下面是一些關(guān)于如何使用 SQL 邏輯運算符的簡單示例：

    SQL> SELECT * FROM CUSTOMERS WHERE AGE >= 25 AND SALARY >= 6500;
    +----+----------+-----+---------+---------+
    | ID | NAME     | AGE | ADDRESS | SALARY  |
    +----+----------+-----+---------+---------+
    |  4 | Chaitali |  25 | Mumbai  | 6500.00 |
    |  5 | Hardik   |  27 | Bhopal  | 8500.00 |
    +----+----------+-----+---------+---------+
    2 rows in set (0.00 sec)

    SQL> SELECT * FROM CUSTOMERS WHERE AGE >= 25 OR SALARY >= 6500;
    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+
    5 rows in set (0.00 sec)

    SQL>  SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;
    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+
    7 rows in set (0.00 sec)

    SQL> SELECT * FROM CUSTOMERS WHERE NAME LIKE 'Ko%';
    +----+-------+-----+---------+---------+
    | ID | NAME  | AGE | ADDRESS | SALARY  |
    +----+-------+-----+---------+---------+
    |  6 | Komal |  22 | MP      | 4500.00 |
    +----+-------+-----+---------+---------+
    1 row in set (0.00 sec)

    SQL> SELECT * FROM CUSTOMERS WHERE AGE IN ( 25, 27 );
    +----+----------+-----+---------+---------+
    | ID | NAME     | AGE | ADDRESS | SALARY  |
    +----+----------+-----+---------+---------+
    |  2 | Khilan   |  25 | Delhi   | 1500.00 |
    |  4 | Chaitali |  25 | Mumbai  | 6500.00 |
    |  5 | Hardik   |  27 | Bhopal  | 8500.00 |
    +----+----------+-----+---------+---------+
    3 rows in set (0.00 sec)

    SQL> SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 25 AND 27;
    +----+----------+-----+---------+---------+
    | ID | NAME     | AGE | ADDRESS | SALARY  |
    +----+----------+-----+---------+---------+
    |  2 | Khilan   |  25 | Delhi   | 1500.00 |
    |  4 | Chaitali |  25 | Mumbai  | 6500.00 |
    |  5 | Hardik   |  27 | Bhopal  | 8500.00 |
    +----+----------+-----+---------+---------+
    3 rows in set (0.00 sec)

    SQL> SELECT AGE FROM CUSTOMERS 
    WHERE EXISTS (SELECT AGE FROM CUSTOMERS WHERE SALARY > 6500);
    +-----+
    | AGE |
    +-----+
    |  32 |
    |  25 |
    |  23 |
    |  25 |
    |  27 |
    |  22 |
    |  24 |
    +-----+
    7 rows in set (0.02 sec)

    SQL> SELECT * FROM CUSTOMERS 
    WHERE AGE > ALL (SELECT AGE FROM CUSTOMERS WHERE SALARY > 6500);
    +----+--------+-----+-----------+---------+
    | ID | NAME   | AGE | ADDRESS   | SALARY  |
    +----+--------+-----+-----------+---------+
    |  1 | Ramesh |  32 | Ahmedabad | 2000.00 |
    +----+--------+-----+-----------+---------+
    1 row in set (0.02 sec)

    SQL> SELECT * FROM CUSTOMERS 
    WHERE AGE > ANY (SELECT AGE FROM CUSTOMERS WHERE SALARY > 6500);
    +----+----------+-----+-----------+---------+
    | ID | NAME     | AGE | ADDRESS   | SALARY  |
    +----+----------+-----+-----------+---------+
    |  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
    |  2 | Khilan   |  25 | Delhi     | 1500.00 |
    |  4 | Chaitali |  25 | Mumbai    | 6500.00 |
    |  5 | Hardik   |  27 | Bhopal    | 8500.00 |
    +----+----------+-----+-----------+---------+
    4 rows in set (0.00 sec)

下面列出了 SQL 中可用的邏輯運算符。

運算符 描述
ALL ALL 運算符用于將一個值同另一個值集中所有的值進(jìn)行比較。
AND AND 運算符使得在 WHERE 子句中可以同時存在多個條件。
ANY ANY 運算符用于將一個值同條件所指定的列表中的任意值相比較。
BETWEEN 給定最小值和最大值，BETWEEN 運算符可以用于搜索區(qū)間內(nèi)的值。
EXISTS EXISTS 運算符用于在表中搜索符合特定條件的行。
IN IN 運算符用于將某個值同指定的一列字面值相比較。
LIKE LIKE 運算符用于使用通配符對某個值和與其相似的值做出比較。
NOT NOT 操作符反轉(zhuǎn)它所作用的操作符的意義。例如，NOT EXISTS、NOT BETWEEN、NOT IN 等。這是一個求反運算符。
OR OR 運算符用于在 SQL 語句中連接多個條件。
IS NULL NULL Operator 用于將某個值同 NULL 作比較。
UNIQUE UNIQUE 運算符檢查指定表的所有行，以確定沒有重復(fù)。
以上內(nèi)容是否對您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號