文章詳情頁

MySQL算術/比較/邏輯/位/運算符與正則舉例詳解

瀏覽：86日期：2023-02-18 16:43:32

1. 算術運算符

算術運算符主要用于數學運算，其可以連接運算符前后的兩個數值或表達式，對數值或表達式進行加（+）、減（-）、乘（*）、除（/）和取模（%）運算。

加法與減法運算符

SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5FROM dual;

在Java中，+的左右兩邊如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示數值相加。如果遇到非數值類型，先嘗試轉成數值，如果轉失敗，就按0計算。

MySQL 中字符串拼接要使用字符串函數 CONCAT() 實現

乘法與除法運算符

SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual;

一個數除以整數后，不管是否能除盡，結果都為一個浮點數；
一個數除以另一個數，除不盡時，結果為一個浮點數，并保留到小數點后4位；
在數學運算中，0不能用作除數，在MySQL中，一個數除以0為NULL。

求模（求余）運算符

將t22表中的字段i對3和5進行求模（求余）運算。

SELECT 12 % 3, 12 MOD 5 FROM dual;

案例：

#篩選出employee_id是偶數的員工SELECT * FROM employeesWHERE employee_id MOD 2 = 0;

2. 比較運算符

比較運算符用來對表達式左邊的操作數和右邊的操作數進行比較，比較的結果為真則返回1，比較的結果為假則返回0，其他情況則返回NULL。

比較運算符經常被用來作為SELECT查詢語句的條件來使用，返回符合條件的結果記錄。

等號運算符

等號運算符（=）判斷等號兩邊的值、字符串或表達式是否相等，如果相等則返回1，不相等則返回 0。
在使用等號運算符時，遵循如下規則：
- 如果等號兩邊的值、字符串或表達式都為字符串，則MySQL會按照字符串進行比較，其比較的是每個字符串中字符的ANSI編碼是否相等。
- 如果等號兩邊的值都是整數，則MySQL會按照整數來比較兩個值的大小。
- 如果等號兩邊的值一個是整數，另一個是字符串，則MySQL會將字符串轉化為數字進行比較。如果字符串不能隱式地轉為數字，則會等價數字0。
- 如果等號兩邊的值、字符串或表達式中有一個為NULL，則比較結果為NULL。
對比：SQL中賦值符號使用 :=

SELECT 1 = 1, 1 = "1", 1 = 0, "a" = "a", (5 + 3) = (2 + 6), "" = NULL , NULL = NULL;

安全等于運算符

安全等于運算符（<=>）與等于運算符（=）的作用是相似的， 唯一的區別是‘<=>’可以用來對NULL進行判斷。

在兩個操作數均為NULL時，其返回值為1，而不為NULL；
當一個操作數為NULL時，其返回值為0，而不為NULL。

SELECT 1 <=> "1", 1 <=> 0, "a" <=> "a", (5 + 3) <=> (2 + 6), "" <=> NULL,NULL <=> NULL FROM dual;

不等于運算符

不等于運算符（<>和!=）用于判斷兩邊的數字、字符串或者表達式的值是否不相等.

如果不相等則返回1，相等則返回0。
等于運算符不能判斷NULL值。如果兩邊的值有任意一個為NULL，或兩邊都為NULL，則結果為NULL。

SQL語句示例如下：

SELECT 1 <> 1, 1 != 2, "a" != "b", (3+4) <> (2+6), "a" != NULL, NULL <> NULL;

非符號類型的運算符

空運算符

空運算符（IS NULL或者ISNULL）判斷一個值是否為NULL

如果為NULL則返回1，否則返回0。

SQL語句示例如下：

SELECT NULL IS NULL, ISNULL(NULL), ISNULL("a"), 1 IS NULL;

示例：

#查詢commission_pct等于NULL。比較如下的四種寫法SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL;SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);

非空運算符

非空運算符（IS NOT NULL）判斷一個值是否不為NULL。

如果不為NULL則返回1，否則返回0。

SQL語句示例如下：

SELECT NULL IS NOT NULL, "a" IS NOT NULL, 1 IS NOT NULL;

示例：

#查詢commission_pct不等于NULLSELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL;SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

最小值運算符

語法格式為：LEAST(值1，值2，…，值n)。其中，“值n”表示參數列表中有n個值。在有兩個或多個參數的情況下，返回最小值。

當參數是整數或者浮點數時，LEAST將返回其中最小的值；
當參數為字符串時，返回字母表中順序最靠前的字符；
當比較值列表中有NULL時，不能判斷大小，返回值為NULL；

SELECT LEAST (1,0,2), LEAST("b","a","c"), LEAST(1,NULL,2);

由結果可以看到，當參數是整數或者浮點數時，LEAST將返回其中最小的值；當參數為字符串時，返回字母表中順序最靠前的字符；當比較值列表中有NULL時，不能判斷大小，返回值為NULL。

最大值運算符

語法格式為：GREATEST(值1，值2，…，值n)。其中，n表示參數列表中有n個值。當有兩個或多個參數時，返回值為最大值。

當參數中是整數或者浮點數時，GREATEST將返回其中最大的值；
當參數為字符串時，返回字母表中順序最靠后的字符；
當比較值列表中有NULL時，不能判斷大小，返回值為NULL。

SELECT GREATEST(1,0,2), GREATEST("b","a","c"), GREATEST(1,NULL,2);

BETWEEN AND運算符

BETWEEN運算符使用的格式通常為SELECT D FROM TABLE WHERE C BETWEEN A AND B，此時，當C大于或等于A，并且C小于或等于B時，結果為1，否則結果為0。注意是閉區間。

SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, "b" BETWEEN "a" AND "c";

IN運算符

IN運算符用于判斷給定的值是否是IN列表中的一個值。

如果是則返回1，否則返回0。
如果給定的值為NULL，或者IN列表中存在NULL，則結果為NULL。

SELECT "a" IN ("a","b","c"), 1 IN (2,3), NULL IN ("a","b"), "a" IN ("a", NULL);

NOT IN運算符

NOT IN運算符用于判斷給定的值是否不是IN列表中的一個值。

如果不是IN列表中的一個值，則返回1，否則返回0。

SELECT "a" NOT IN ("a","b","c"), 1 NOT IN (2,3);

LIKE運算符

LIKE運算符主要用來匹配字符串，通常用于模糊匹配。

如果滿足條件則返回1，否則返回0。
如果給定的值或者匹配條件為NULL，則返回結果為NULL。

LIKE運算符通常使用如下通配符：

“%”：匹配0個或多個字符。
“_”：只能匹配一個字符。

SQL語句示例如下：

SELECT NULL LIKE "abc", "abc" LIKE NULL;

SELECT first_nameFROM employeesWHERE first_name LIKE "S%";

SELECT last_nameFROM employeesWHERE last_name LIKE "_o%";

ESCAPE

回避特殊符號的：使用轉義符。例如：將[%]轉為[ %]、[]轉為[ ]，然后再加上[ESCAPE‘$’]即可。

# 這里采用了\表示轉義SELECT job_idFROM jobsWHERE job_id LIKE ‘IT\_%‘;

如果使用\表示轉義，要省略ESCAPE。如果不是\，則要加上ESCAPE。

# 這里采用了ESCAPE轉義SELECT job_idFROM jobsWHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;

REGEXP運算符

REGEXP運算符用來匹配字符串，語法格式為： expr REGEXP 匹配條件。

如果expr滿足匹配條件，返回1；如果不滿足，則返回0。
若expr或匹配條件任意一個為NULL，則結果為NULL。

REGEXP運算符在進行匹配時，常用的有下面幾種通配符：

（1）‘^’匹配以該字符后面的字符開頭的字符串。
（2）‘$’匹配以該字符前面的字符結尾的字符串。
（3）‘.’匹配任何一個單字符。
（4）“[...]”匹配在方括號內的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。為了命名字符的范圍，使用一
個‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何數字。
（5）‘*’匹配零個或多個在它前面的字符。例如，“x*”匹配任何數量的‘x’字符，“[0-9]*”匹配任何數量的數字，
而“*”匹配任何數量的任何字符。

SQL語句示例如下：

SELECT "timerring" REGEXP "^t", "timerring" REGEXP "g$", "timerring" REGEXP "rr";

3. 邏輯運算符

邏輯運算符主要用來判斷表達式的真假，在MySQL中，邏輯運算符的返回結果為1、0或者NULL。

MySQL中支持4種邏輯運算符如下：

邏輯非運算符

邏輯非（NOT或!）運算符表示當給定的值為0時返回1；當給定的值為非0值時返回0；當給定的值為NULL時，返回NULL。

SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;

示例

SELECT last_name, job_idFROM employeesWHERE job_id NOT IN ("IT_PROG", "ST_CLERK", "SA_REP");

邏輯與運算符

邏輯與（AND或&&）運算符是

當給定的所有值均為非0值，并且都不為NULL時，返回1；
當給定的一個值或者多個值為0時則返回0；
否則返回NULL。

SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;

邏輯或運算符

邏輯或（OR或||）運算符是

當給定的值都不為NULL，并且任何一個值為非0值時，則返回1，否則返回0；
當一個值為NULL，并且另一個值為非0值時，返回1，
否則返回NULL；當兩個值都為NULL時，返回NULL。

SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;

示例：
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000OR job_id LIKE "%MAN%";
注意：
OR可以和AND一起使用，但是在使用時要注意兩者的優先級，由于AND的優先級高于OR，因此先
對AND兩邊的操作數進行操作，再與OR中的操作數結合。

邏輯異或運算符

邏輯異或（XOR）運算符是當

給定的值中任意一個值為NULL時，則返回NULL；
如果兩個非NULL的值都是0或者都不等于0時，則返回0；
如果一個值為0，另一個值不為0時，則返回1。

SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;

示例：

select last_name,department_id,salaryfrom employeeswhere department_id in (10,20) XOR salary > 8000;

4. 位運算符

位運算符是在二進制數上進行計算的運算符。位運算符會先將操作數變成二進制數，然后進行位運算，最后將計算結果從二進制變回十進制數。

MySQL支持的位運算符如下：

按位與運算符

按位與（&）運算符將給定值對應的二進制數逐位進行邏輯與運算。

當給定值對應的二進制位的數值都為1時，則該位返回1，否則返回0。

# 1的二進制數為0001，10的二進制數為1010，所以1 & 10的結果為0000，對應的十進制數為0。# 20的二進制數為10100，30的二進制數為11110，所以20 & 30的結果為10100，對應的十進制數為20。SELECT 1 & 10, 20 & 30;

按位或運算符

按位或（|）運算符將給定的值對應的二進制數逐位進行邏輯或運算。

當給定值對應的二進制位的數值有一個或兩個為1時，則該位返回1，否則返回0。

# 1的二進制數為0001，10的二進制數為1010，所以1 ^ 10的結果為1011，對應的十進制數為11。# 20的二進制數為10100，30的二進制數為11110，所以20 ^ 30的結果為01010，對應的十進制數為10。SELECT 1 ^ 10, 20 ^ 30;

按位異或運算符

按位異或（^）運算符將給定的值對應的二進制數逐位進行邏輯異或運算。

當給定值對應的二進制位的數值不同時，則該位返回1，否則返回0。

# 1的二進制數為0001，10的二進制數為1010，所以1 ^ 10的結果為1011，對應的十進制數為11。# 20的二進制數為10100，30的二進制數為11110，所以20 ^ 30的結果為01010，對應的十進制數為10。SELECT 1 ^ 10, 20 ^ 30;

示例：

SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;

按位取反運算符

按位取反（~）運算符將給定的值的二進制數逐位進行取反操作，即將1變為0，將0變為1。

# 由于按位取反（~）運算符的優先級高于按位與（&）運算符的優先級，所以10 & ~1，首先，對數字1進行按位取反操作，結果除了最低位為0，其他位都為1，然后與10進行按位與操作，結果為10。SELECT 10 & ~1;

按位右移運算符

按位右移（>>）運算符將給定的值的二進制數的所有位右移指定的位數。

右移指定的位數后，右邊低位的數值被移出并丟棄，左邊高位空出的位置用0補齊。

# 1的二進制數為0000 0001，右移2位為0000 0000，對應的十進制數為0。# 4的二進制數為0000 0100，右移2位為0000 0001，對應的十進制數為1。SELECT 1 >> 2, 4 >> 2;

按位左移運算符

按位左移（<<）運算符將給定的值的二進制數的所有位左移指定的位數。左移指定的位數后，左邊高位的數值被移出并丟棄，右邊低位空出的位置用0補齊。

SELECT 1 << 2, 4 << 2;

5. 運算符的優先級

數字編號越大，優先級越高，優先級高的運算符先進行計算。可以看到，賦值運算符的優先級最低，使用“()”括起來的表達式的優先級最高。

拓展：使用正則表達式查詢

正則表達式通常被用來檢索或替換那些符合某個模式的文本內容，根據指定的匹配模式匹配文本中符合要求的特殊字符串。

例如，從一個文本文件中提取電話號碼，查找一篇文章中重復的單詞或者替換用戶輸入的某些敏感詞語等，這些地方都可以使用正則表達式。正則表達式強大而且靈活，可以應用于非常復雜的查詢。

MySQL中使用REGEXP關鍵字指定正則表達式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表。

查詢以特定字符或字符串開頭的記錄

字符‘^’匹配以特定字符或者字符串開頭的文本。

在fruits表中，查詢f_name字段以字母‘b’開頭的記錄，SQL語句如下：
SELECT * FROM fruits WHERE f_name REGEXP "^b";

查詢以特定字符或字符串結尾的記錄

字符‘$’匹配以特定字符或者字符串結尾的文本。

在fruits表中，查詢f_name字段以字母‘y’結尾的記錄，SQL語句如下：
SELECT * FROM fruits WHERE f_name REGEXP "y$";

用符號"."來替代字符串中的任意一個字符

字符‘.’匹配任意一個字符。在fruits表中，查詢f_name字段值

包含字母‘a’與‘g’且兩個字母之間只有一個字母的記錄，SQL語句如下：
SELECT * FROM fruits WHERE f_name REGEXP "a.g";

使用"*“和”+"來匹配多個字符

星號‘*’匹配前面的字符任意多次，包括0次。加號‘+’匹配前面的字符至少一次。

在fruits表中，查詢f_name字段值以字母‘b’開頭且‘b’后面出現字母‘a’的記錄，SQL語句如下：
SELECT * FROM fruits WHERE f_name REGEXP "^ba*"; # 任意多次包括0次SELECT * FROM fruits WHERE f_name REGEXP "^ba+"; # 至少一次

匹配指定字符串

正則表達式可以匹配指定字符串，只要這個字符串在查詢文本中即可，如要匹配多個字符串，多個字符串之間使用分隔符‘|’隔開。

在fruits表中，查詢f_name字段值包含字符串“on”的記錄，SQL語句如下：

SELECT * FROM fruits WHERE f_name REGEXP "on";

在fruits表中，查詢f_name字段值包含字符串“on”或者“ap”的記錄，SQL語句如下：

SELECT * FROM fruits WHERE f_name REGEXP "on|ap";

之前介紹過，LIKE運算符也可以匹配指定的字符串，

但與REGEXP不同，LIKE匹配的字符串如果在文本中間出現，則找不到它，相應的行也不會返回。
REGEXP在文本內進行匹配，如果被匹配的字符串在文本中出現，REGEXP將會找到它，相應的行也會被返回。對比結果如下所示。

在fruits表中，使用LIKE運算符查詢f_name字段值為“on”的記錄，SQL語句如下：

SELECT * FROM fruits WHERE f_name like "on";# Empty set(0.00 sec)

匹配指定字符中的任意一個

方括號“[]”指定一個字符集合，只匹配其中任何一個字符，即為所查找的文本。

在fruits表中，查找f_name字段中包含字母‘o’或者‘t’的記錄，SQL語句如下：

SELECT * FROM fruits WHERE f_name REGEXP "[ot]";

匹配指定字符以外的字符

“[^字符集合]” 匹配不在指定集合中的任何字符。

在fruits表中，查詢f_id字段中包含字母ae和數字12以外字符的記錄，SQL語句如下：

SELECT * FROM fruits WHERE f_id REGEXP "[^a-e1-2]";

使用{n,}或者{n,m}來指定字符串連續出現的次數

“字符串{n,}”表示至少匹配n次前面的字符；“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。

在fruits表中，查詢f_name字段值出現字符串“ba”最少1次、最多3次的記錄，SQL語句如下：

SELECT * FROM fruits WHERE f_name REGEXP "ba{1,3}";

練習題

1.選擇工資不在5000到12000的員工的姓名和工資

SELECT last_name, salaryFROM employeesWHERE salary NOT BETWEEN 5000 and 12000;

2.選擇在20或50號部門工作的員工姓名和部門號

SELECT last_name, department_idFROM employeesWHERE department_id IN (20, 50);

3.選擇公司中沒有管理者的員工姓名及job_id

SELECT last_name, job_idFROM employeesWHERE manager_id IS NULL;

4.選擇公司中有獎金的員工姓名，工資和獎金級別

SELECT last_name, salary, commission_pctFROM employeesWHERE commission_pct IS NOT NULL;

5.選員工姓名的第三個字母是a的員工姓名

SELECT last_nameFROM employeesWHERE last_name LIKE "__a%";

6.選擇姓名中有字母a和k的員工姓名

SELECT last_nameFROM employeesWHERE last_name LIKE "%a%k%" OR last_name LIKE "%k%a%";# 注意這里OR前后要寫完整的語句。只寫WHERE last_name LIKE "%a%k%" OR LIKE "%k%a%";是錯的

7.顯示出表 employees 表中 first_name 以 'e’結尾的員工信息

SELECT employee_id,first_name,last_nameFROM employeesWHERE first_name LIKE "%e";

SELECT employee_id,first_name,last_nameFROM employeesWHERE first_name REGEXP "e$";

8.顯示出表 employees 部門編號在 80-100 之間的姓名、工種

SELECT last_name,job_idFROM employeesWHERE department_id BETWEEN 80 AND 100;

9.顯示出表 employees 的 manager_id 是 100,101,110 的員工姓名、工資、管理者id

SELECT last_name,salary, manager_idFROM employeesWHERE manager_id IN (100, 101, 110);

總結

到此這篇關于MySQL算術/比較/邏輯/位/運算符與正則舉例的文章就介紹到這了,更多相關MySQL算術比較邏輯位運算符正則內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！

MySQL

上一條：MySQL該如何判斷不為空詳析下一條：一文總結MySQL中數學函數有哪些

排行榜

					
					在Redhat Enterprise server 3上安裝oracle9iR2的注意事項
淺談MySQL中的自增主鍵用完了怎么辦
MySQL存儲過程in、out和inout參數示例和總結
目前學習到的常用命令之Mariadb
SQLite數據庫安裝及基本操作指南
DB2 自動遞增字段實現方法
使用DB2look 重新創建優化器訪問計劃(3)（1）
sql語句LEFT JOIN拼接表詳解
DB2 Performance Expert 簡化性能管理和調優（1）（1）
ubuntu下使用SQLite3的基本命令
國內學院派專家對DB2 9新產品贊不絕口
				