    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 比較運(yùn)算符

考慮 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 比較運(yùn)算符的簡單示例：

    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 邏輯運(yùn)算符

考慮 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 邏輯運(yùn)算符的簡單示例：

    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 中可用的邏輯運(yùn)算符。

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

上一篇：SQL 使用序列下一篇：SQL HAVING 子句