操作符
2018-08-12 22:09 更新
SQL 操作符
每個(gè)操作符都是一個(gè)保留字，主要用于在 SQL 語句的 WHERE 子句中執(zhí)行各種操作，例如比較和算術(shù)運(yùn)算等。
操作符在 SQL 語句中指定了條件，并可以將同一語句中的不同條件連接起來。
- 算術(shù)運(yùn)算符
- 比較運(yùn)算符
- 邏輯運(yùn)算符
- 用于否定條件的運(yùn)算符
SQL 算術(shù)運(yùn)算符
這里一些有關(guān) SQL 算術(shù)運(yùn)算符如何使用的簡(jiǎn)單示例：
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，那么：
| 操作符 | 描述 | 示例 |
| + | 相加：將符號(hào)兩邊的數(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)算符的簡(jiǎ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，那么：
| 操作符 | 描述 | 示例 |
| = | 檢查兩個(gè)操作數(shù)的值是否相等，是的話返回 true。 | (a = b) 不為 true。 |
| != | 檢查兩個(gè)操作數(shù)的值是否相等，如果不等則返回 true。 | (a != b) 為 true。 |
| 檢查兩個(gè)操作數(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)算符的簡(jiǎ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)算符用于將一個(gè)值同另一個(gè)值集中所有的值進(jìn)行比較。 |
| AND | AND 運(yùn)算符使得在 WHERE 子句中可以同時(shí)存在多個(gè)條件。 |
| ANY | ANY 運(yùn)算符用于將一個(gè)值同條件所指定的列表中的任意值相比較。 |
| BETWEEN | 給定最小值和最大值，BETWEEN 運(yùn)算符可以用于搜索區(qū)間內(nèi)的值。 |
| EXISTS | EXISTS 運(yùn)算符用于在表中搜索符合特定條件的行。 |
| IN | IN 運(yùn)算符用于將某個(gè)值同指定的一列字面值相比較。 |
| LIKE | LIKE 運(yùn)算符用于使用通配符對(duì)某個(gè)值和與其相似的值做出比較。 |
| NOT | NOT 操作符反轉(zhuǎn)它所作用的操作符的意義。例如，NOT EXISTS、NOT BETWEEN、NOT IN 等。這是一個(gè)求反運(yùn)算符。 |
| OR | OR 運(yùn)算符用于在 SQL 語句中連接多個(gè)條件。 |
| IS NULL | NULL Operator 用于將某個(gè)值同 NULL 作比較。 |
| UNIQUE | UNIQUE 運(yùn)算符檢查指定表的所有行，以確定沒有重復(fù)。 |
以上內(nèi)容是否對(duì)您有幫助：
更多建議：