3.3　常見運算符介紹

運算符連接表達式中各個操作數(shù)，其作用是指明對操作數(shù)所進行的運算。運用運算符可以更加靈活地使用表中的數(shù)據(jù)，常見的運算符類型有算術(shù)運算符、比較運算符、邏輯運算符、位操作運算符。本章將介紹各種運算符的特點和使用方法。

3.3.1　運算符概述

運算符是告訴MySQL執(zhí)行特定算術(shù)或邏輯操作的符號。MySQL的內(nèi)部運算符很豐富，主要有四大類，分別是算術(shù)運算符、比較運算符、邏輯運算符、位操作運算符。

1. 算術(shù)運算符

算術(shù)運算符用于各類數(shù)值運算，包括加（+）、減（-）、乘（*）、除（/）、求余（或稱模運算，%）。

2. 比較運算符

比較運算符用于比較運算，包括大于（>）、小于（<）、等于（=）、大于等于（>=）、小于等于（<=）、不等于（!=），以及IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP等。

3. 邏輯運算符

邏輯運算符的求值所得結(jié)果均為1（TRUE）、0（FALSE），這類運算符有邏輯非（NOT或者!）、邏輯與（AND或者&&）、邏輯或（OR或者||）、邏輯異或（XOR）。

4. 位操作運算符

位操作運算符參與運算的操作數(shù)按二進制位進行運算，包括位與（&）、位或（|）、位非（~）、位異或（^）、左移（<<）、右移（>>）6種。

接下來將對MySQL中各種運算符的使用進行詳細介紹。

3.3.2　算術(shù)運算符

算術(shù)運算符是SQL中基本的運算符，MySQL中的算術(shù)運算符如表3.10所示。

表3.10　MySQL中的算術(shù)運算符

下面分別討論不同算術(shù)運算符的使用方法。

【例3.26】創(chuàng)建表tmp14，定義數(shù)據(jù)類型為INT的字段num，插入值64，對num值進行算術(shù)運算。

首先創(chuàng)建表tmp14，輸入語句如下：

     CREATE TABLE tmp14 ( num INT);

向字段num插入數(shù)據(jù)64：

     INSERT INTO tmp14 value(64);

接下來，對num值進行加法和減法運算：

由計算結(jié)果可以看到，可以對num字段的值進行加法和減法的運算，而且由于‘+’和‘–’的優(yōu)先級相同，因此先加后減和先減后加之后的結(jié)果是相同的。

【例3.27】對tmp14表中的num進行乘法、除法運算。

由計算結(jié)果可以看到，對num進行除法運算的時候，由于64無法被3整除，因此MySQL對num/3求商的結(jié)果保存到了小數(shù)點后面4位，結(jié)果為21.3333；64除以3的余數(shù)為1，因此取余運算num%3的結(jié)果為1。

在數(shù)學(xué)運算中，除數(shù)為0的除法是沒有意義的，因此除法運算中的除數(shù)不能為0，如果被0除，返回結(jié)果就為NULL。

【例3.28】用0除num。

由計算結(jié)果可以看到，對num進行除法求商或者求余運算的結(jié)果均為NULL。

3.3.3　比較運算符

一個比較運算符的結(jié)果總是1、0或者NULL，比較運算符經(jīng)常在SELECT的查詢條件子句中使用，用來查詢滿足指定條件的記錄。MySQL中的比較運算符如表3.11所示。

表3.11　MySQL中的比較運算符

下面分別討論不同比較運算符的使用方法。

1. 等于運算符（=）

等于運算符（=）用來判斷數(shù)字、字符串和表達式是否相等。如果相等，返回值就為1，否則返回值為0。

【例3.29】使用‘=’進行相等判斷，SQL語句如下：

由結(jié)果可以看到，在進行判斷時，2=2和‘2’=2的返回值相同，都為1。在進行判斷時，MySQL自動進行了轉(zhuǎn)換，把字符‘2’轉(zhuǎn)換成了數(shù)字2；‘b’=‘b’為相同的字符比較，因此返回值為1；表達式1+3和表達式2+2的結(jié)果都為4，因此結(jié)果相等，返回值為1；由于‘=’不能用于空值（NULL）的判斷，因此返回值為NULL。

數(shù)值比較時有如下規(guī)則：

（1）若有一個或兩個參數(shù)為NULL，則比較運算的結(jié)果為NULL。

（2）若同一個比較運算中的兩個參數(shù)都是字符串，則按照字符串進行比較。

（3）若兩個參數(shù)均為整數(shù)，則按照整數(shù)進行比較。

（4）若一個字符串和數(shù)字進行相等判斷，則MySQL可以自動將字符串轉(zhuǎn)換為數(shù)字。

2. 安全等于運算符（<=>）

這個操作符和=操作符執(zhí)行相同的比較操作，不過<=>可以用來判斷NULL值。在兩個操作數(shù)均為NULL時，其返回值為1而不為NULL；而當(dāng)一個操作數(shù)為NULL時，其返回值為0而不為NULL。

【例3.30】使用‘<=>’進行相等的判斷，SQL語句如下：

由結(jié)果可以看到，‘<=>’在執(zhí)行比較操作時和‘=’的作用是相似的，唯一的區(qū)別是‘<=>’可以用來對NULL進行判斷，兩者都為NULL時返回值為1。

3. 不等于運算符（<>或者!=）

‘<>’或者‘!=’用于判斷數(shù)字、字符串、表達式不相等的判斷。如果不相等，返回值就為1；否則返回值為0。這兩個運算符不能用于判斷空值（NULL）。

【例3.31】使用‘<>’和‘!=’進行不相等的判斷，SQL語句如下：

由結(jié)果可以看到，兩個不等于運算符的作用相同，都可以進行數(shù)字、字符串、表達式的比較判斷。

4. 小于或等于運算符（<=）

‘<=’用來判斷左邊的操作數(shù)是否小于或者等于右邊的操作數(shù)。如果小于或者等于，返回值就為1；否則返回值為0。‘<=’不能用于判斷空值（NULL）。

【例3.32】使用‘<=’進行比較判斷，SQL語句如下：

由結(jié)果可以看到，左邊的操作數(shù)小于或者等于右邊的操作數(shù)時，返回值為1，例如4<=4；當(dāng)左邊的操作數(shù)大于右邊的操作數(shù)時，返回值為0，例如‘good’第3個位置的‘o’字符在字母表中的順序大于‘god’中第3個位置的‘d’字符，因此返回值為0；同樣比較NULL值時返回NULL。

5. 小于運算符（<）

‘<’運算符用來判斷左邊的操作數(shù)是否小于右邊的操作數(shù)，如果小于，返回值就為1；否則返回值為0?！?lt;’不能用于判斷空值（NULL）。

【例3.33】使用‘<’進行比較判斷，SQL語句如下：

由結(jié)果可以看到，當(dāng)左邊的操作數(shù)小于右邊的操作數(shù)時，返回值為1，例如1<2；當(dāng)左邊的操作數(shù)大于右邊的操作數(shù)時，返回值為0，例如‘good’第3個位置的‘o’字符在字母表中的順序大于‘god’中第3個位置的‘d’字符，因此返回值為0；同樣比較NULL值時返回NULL。

6. 大于或等于運算符（>=）

‘>=’運算符用來判斷左邊的操作數(shù)是否大于或者等于右邊的操作數(shù)，如果大于或者等于，返回值就為1；否則返回值為0?！?gt;=’不能用于判斷空值（NULL）。

【例3.34】使用‘>=’進行比較判斷，SQL語句如下：

由結(jié)果可以看到，左邊的操作數(shù)大于或者等于右邊的操作數(shù)時，返回值為1，例如4>=4；當(dāng)左邊的操作數(shù)小于右邊的操作數(shù)時，返回值為0，例如1>=2；同樣比較NULL值時返回NULL。

7. 大于運算符（>）

‘>’運算符用來判斷左邊的操作數(shù)是否大于右邊的操作數(shù)，如果大于，返回值就為1；否則返回值為0?！?gt;’不能用于判斷空值（NULL）。

【例3.35】使用‘>’進行比較判斷，SQL語句如下：

由結(jié)果可以看到，左邊的操作數(shù)大于右邊的操作數(shù)時，返回值為1，例如5.5>5；當(dāng)左邊的操作數(shù)小于右邊的操作數(shù)時，返回0，例如1>2；同樣比較NULL值時返回NULL。

8. IS NULL（ISNULL）、IS NOT NULL運算符

IS NULL和ISNULL檢驗一個值是否為NULL，如果為NULL，返回值就為1，否則返回值為0；IS NOT NULL檢驗一個值是否為非NULL，如果是非NULL，返回值就為1，否則返回值為0。

【例3.36】使用IS NULL、ISNULL和IS NOT NULL判斷NULL值和非NULL值，SQL語句如下：

由結(jié)果可以看到，IS NULL和ISNULL的作用相同，只是格式不同。ISNULL和IS NOT NULL的返回值正好相反。

9. BETWEEN AND運算符

語法格式：expr BETWEEN min AND max。若expr大于或等于min且小于或等于max，則BETWEEN的返回值為1；否則返回值為0。

【例3.37】使用BETWEEN AND進行值區(qū)間判斷，輸入SQL語句如下：

由結(jié)果可以看到，4在端點值區(qū)間內(nèi)或者等于其中一個端點值時，BETWEEN AND表達式返回值為1；12并不在指定區(qū)間內(nèi)，因此返回值為0。對于字符串類型的比較，按字母表中的字母順序進行比較，‘x’不在指定的字母區(qū)間內(nèi)，因此返回值為0；而‘b’位于指定字母區(qū)間內(nèi)，因此返回值為1。

10. LEAST運算符

語法格式：LEAST(值1,值2,...,值n)，其中值n表示參數(shù)列表中有n個值。在有兩個或多個參數(shù)的情況下，返回最小值。若任意一個自變量為NULL，則LEAST()的返回值為NULL。

【例3.38】使用LEAST運算符進行大小判斷，SQL語句如下：

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

11. GREATEST (value1,value2,...)

語法格式：GREATEST(值1, 值2,...,值n)，其中n表示參數(shù)列表中有n個值。當(dāng)有兩個或多個參數(shù)時，返回值為最大值。假如任意一個自變量為NULL，則GREATEST()的返回值為NULL。

【例3.39】使用GREATEST運算符進行大小判斷，SQL語句如下：

由結(jié)果可以看到，當(dāng)參數(shù)中是整數(shù)或者浮點數(shù)時，GREATEST將返回其中最大的值；當(dāng)參數(shù)為字符串時，返回字母表中順序最靠后的字符；當(dāng)比較值列表中有NULL時，不能判斷大小，返回值為NULL。

12. IN、NOT IN運算符

IN運算符用來判斷操作數(shù)是否為IN列表中的其中一個值，如果是，返回值就為1；否則返回值為0。

NOT IN運算符用來判斷表達式是否為IN列表中的其中一個值，如果不是，返回值就為1；否則返回值為0。

【例3.40】使用IN、NOT IN運算符進行判斷，SQL語句如下：

由結(jié)果可以看到，IN和NOT IN的返回值正好相反。

在左側(cè)表達式為NULL的情況下，或者表中找不到匹配項并且表中一個表達式為NULL的情況下，IN的返回值均為NULL。

【例3.41】存在NULL值時的IN查詢，SQL語句如下：

IN()語法也可用于在SELECT語句中進行嵌套子查詢，在后面的章節(jié)中將會講到。

13. LIKE運算符

LIKE運算符用來匹配字符串，語法格式為：expr LIKE匹配條件，若expr滿足匹配條件，則返回值為1（TRUE）；若不匹配，則返回值為0（FALSE）；若expr或匹配條件中任何一個為NULL，則結(jié)果為NULL。

LIKE運算符在進行匹配時，可以使用下面兩種通配符：

（1）‘%’，匹配任何數(shù)目的字符，甚至包括零字符。

（2）‘_’，只能匹配一個字符。

【例3.42】使用運算符LIKE進行字符串匹配運算，SQL語句如下：

由結(jié)果可以看到，指定匹配字符串為“stud”?！皊tud”表示直接匹配“stud”字符串，滿足匹配條件，返回1；“stu_”表示匹配以stu開頭的長度為4個字符的字符串，“stud”正好是4個字符，滿足匹配條件，因此匹配成功，返回1；“%d”表示匹配以字母“d”結(jié)尾的字符串，“stud”滿足匹配條件，匹配成功，返回1；“t _ _ _”表示匹配以‘t’開頭的長度為4個字符的字符串，“stud”不滿足匹配條件，因此返回0；當(dāng)字符‘s’與NULL匹配時，結(jié)果為NULL。

14. REGEXP運算符

REGEXP運算符用來匹配字符串，語法格式為：expr REGEXP匹配條件，若expr滿足匹配條件，則返回1；若不滿足，則返回0；若expr或匹配條件任意一個為NULL，則結(jié)果為NULL。

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

（1）‘^’匹配以該字符后面的字符開頭的字符串。

（2）‘$’匹配以該字符后面的字符結(jié)尾的字符串。

（3）‘.’匹配任何一個單字符。

（4）“[...]”匹配在方括號內(nèi)的任何字符。例如，“[abc]”匹配“a”“b”或“c”。為了命名字符的范圍，使用一個‘-’?！癧a-z]”匹配任何字母，而“[0-9]”匹配任何數(shù)字。

（5）‘*’匹配零個或多個在它前面的字符。例如，“x*”匹配任何數(shù)量的‘x’字符，“[0-9]*”匹配任何數(shù)量的數(shù)字，而“*”匹配任何數(shù)量的任何字符。

【例3.43】使用運算符REGEXP進行字符串匹配運算，SQL語句如下：

由結(jié)果可以看到，指定匹配字符串為“ssky”。“^s”表示匹配任何以字母‘s’開頭的字符串，因此滿足匹配條件，返回1；“y$”表示任何以字母“y”結(jié)尾的字符串，因此滿足匹配條件，返回1；“.sky”匹配任何以“sky”結(jié)尾、字符長度為4的字符串，滿足匹配條件，返回1；“[ab]”匹配任何包含字母‘a(chǎn)’或者‘b’的字符串，指定字符串中沒有字母‘a(chǎn)’，也沒有字母‘b’，因此不滿足匹配條件，返回0。

3.3.4　邏輯運算符

在SQL中，所有邏輯運算符的求值所得結(jié)果均為TRUE、FALSE或NULL。在MySQL中，它們體現(xiàn)為1（TRUE）、0（FALSE）和NULL。其大多數(shù)都與不同的數(shù)據(jù)庫SQL通用，MySQL中的邏輯運算符如表3.12所示。

表3.12　MySQL中的邏輯運算符

接下來，分別討論不同的邏輯運算符的使用方法。

1. NOT或者!

邏輯非運算符NOT或者！表示當(dāng)操作數(shù)為0時，所得值為1；當(dāng)操作數(shù)為非零值時，所得值為0；當(dāng)操作數(shù)為NULL時，所得的返回值為NULL。

【例3.44】分別使用非運算符“NOT”和“！”進行邏輯判斷，SQL語句如下：

由結(jié)果可以看到，前4列“NOT”和“!”的返回值都相同。但是注意最后1列，為什么會出現(xiàn)不同的值呢？這是因為“NOT”與“!”的優(yōu)先級不同。“NOT”的優(yōu)先級低于“+”，因此“NOT 1+1”相當(dāng)于“NOT(1+1)”，先計算“1+1”，再進行NOT運算，因為操作數(shù)不為0，因此NOT 1 + 1的結(jié)果是0；相反，由于“!”的優(yōu)先級別高于“+”運算，因此“! 1+1”相當(dāng)于“(!1)+1”，先計算“!1”，結(jié)果為0，再加1，最后結(jié)果為1。

2. AND或者&&

邏輯與運算符AND或者&&表示當(dāng)所有操作數(shù)均為非零值并且不為NULL時，計算所得結(jié)果為1；當(dāng)一個或多個操作數(shù)為0時，所得結(jié)果為0；其余情況返回值為NULL。

【例3.45】分別使用與運算符“AND”和“&&”進行邏輯判斷，SQL語句如下：

由結(jié)果可以看到，“AND”和“&&”的作用相同?！? AND -1”中沒有0或者NULL，因此結(jié)果為1；“1 AND 0”中有操作數(shù)0，因此結(jié)果為0；“1 AND NULL”中雖然有NULL，但是沒有操作數(shù)0，返回結(jié)果為NULL。

3. OR或者||

邏輯或運算符OR或者||表示當(dāng)兩個操作數(shù)均為非NULL值，且任意一個操作數(shù)為非零值時，結(jié)果為1，否則結(jié)果為0；當(dāng)有一個操作數(shù)為NULL，且另一個操作數(shù)為非零值時，結(jié)果為1，否則結(jié)果為NULL；當(dāng)兩個操作數(shù)均為NULL時，所得結(jié)果為NULL。

【例3.46】分別使用或運算符“OR”和“||”進行邏輯判斷，SQL語句如下：

由結(jié)果可以看到，“OR”和“||”的作用相同。“1 OR -1 OR 0”中有0，但同時包含有非零的值1和-1，返回結(jié)果為1；“1 OR 2”中沒有操作數(shù)0，返回結(jié)果為1；“1 OR NULL”中雖然有NULL，但是有操作數(shù)1，返回結(jié)果為1；“0 OR NULL”中沒有非零值，并且有NULL，返回結(jié)果為NULL；“NULL OR NULL”中只有NULL，返回結(jié)果為NULL。

4. XOR

邏輯異或運算符XOR。當(dāng)任意一個操作數(shù)為NULL時，返回值為NULL；對于非NULL的操作數(shù)，若兩個操作數(shù)都是非零值或者都是零值，則返回結(jié)果為0；若一個為零值，另一個為非零值，則返回結(jié)果為1。

【例3.47】使用異或運算符“XOR”進行邏輯判斷，SQL語句如下：

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

執(zhí)行上面的語句，結(jié)果如下：

由結(jié)果可以看到，“1 XOR 1”和“0 XOR 0”中運算符兩邊的操作數(shù)都為非零值，或者都是零值，因此返回0；“1 XOR 0”中兩邊的操作數(shù)，一個為零值，另一個為非零值，返回結(jié)果為1；“1 XOR NULL”中有一個操作數(shù)為NULL，返回結(jié)果為NULL；“1 XOR 1 XOR 1”中有多個操作數(shù)，運算符相同，因此運算順序從左到右依次計算，“1 XOR 1”的結(jié)果為0，再與1進行異或運算，因此結(jié)果為1。

3.3.5　位運算符

位運算符是在二進制數(shù)上進行計算的運算符。位運算符會先將操作數(shù)變成二進制數(shù)，然后進行位運算，最后將計算結(jié)果從二進制變回十進制數(shù)。MySQL中提供的位運算符有按位或（|）、按位與（&）、按位異或（^）、按位左移（<<）、按位右移（>>）、按位取反（~），如表3.13所示。

表3.13　MySQL中的位運算符

接下來，分別討論不同的位運算符的使用方法。

1. 位或運算符（|）

位或運算的實質(zhì)是將參與運算的幾個數(shù)據(jù)，按對應(yīng)的二進制數(shù)逐位進行邏輯或運算。若對應(yīng)的二進制位有一個或兩個為1，則該位的運算結(jié)果為1，否則為0。

【例3.48】使用位或運算符進行運算，SQL語句如下：

10的二進制數(shù)值為1010，15的二進制數(shù)值為1111，按位或運算之后，結(jié)果為1111，即整數(shù)15；9的二進制數(shù)值為1001，4的二進制數(shù)值為0100，2的二進制數(shù)值為0010，按位或運算之后，結(jié)果為1111，即整數(shù)15。其結(jié)果為一個64位無符號整數(shù)。

2. 位與運算符（&）

位與運算的實質(zhì)是將參與運算的幾個操作數(shù)，按對應(yīng)的二進制數(shù)逐位進行邏輯與運算。若對應(yīng)的二進制位都為1，則該位的運算結(jié)果為1，否則為0。

【例3.49】使用位與運算符進行運算，SQL語句如下：

10的二進制數(shù)值為1010，15的二進制數(shù)值為1111，按位與運算之后，結(jié)果為1010，即整數(shù)10；9的二進制數(shù)值為1001，4的二進制數(shù)值為0100，2的二進制數(shù)值為0010，按位與運算之后，結(jié)果為0000，即整數(shù)0。其結(jié)果為一個64位無符號整數(shù)。

3. 位異或運算符（^）

位異或運算的實質(zhì)是將參與運算的兩個數(shù)據(jù)，按對應(yīng)的二進制數(shù)逐位進行邏輯異或運算。對應(yīng)位的二進制數(shù)不同時，對應(yīng)位的結(jié)果才為1；若兩個對應(yīng)位數(shù)都為0或者都為1，則對應(yīng)位的結(jié)果為0。

【例3.50】使用位異或運算符進行運算，SQL語句如下：

10的二進制數(shù)值為1010，15的二進制數(shù)值為1111，按位異或運算之后，結(jié)果為0101，即整數(shù)5；1的二進制數(shù)值為0001，0的二進制數(shù)值為0000，按位異或運算之后，結(jié)果為0001；1和1本身二進制位完全相同，因此結(jié)果為0。

4. 位左移運算符（<<）

位左移運算符（<<）使指定的二進制值的所有位都左移指定的位數(shù)。左移指定位數(shù)之后，左邊高位的數(shù)值將被移出并丟棄，右邊低位空出的位置用0補齊。語法格式為：expr<<n。這里n指定值expr要移位的位數(shù)。

【例3.51】使用位左移運算符進行運算，SQL語句如下：

1的二進制值為0000 0001，左移兩位之后變成0000 0100，即十進制整數(shù)4；十進制4左移兩位之后變成0001 0000，即變成十進制的16。

5. 位右移運算符（>>）

位右移運算符（>>）使指定的二進制值的所有位都右移指定的位數(shù)。右移指定位數(shù)之后，右邊低位的數(shù)值將被移出并丟棄，左邊高位空出的位置用0補齊。語法格式為：expr>>n。這里n指定值expr要移位的位數(shù)。

【例3.52】使用位右移運算符進行運算，SQL語句如下：

1的二進制值為0000 0001，右移1位之后變成0000 0000，即十進制整數(shù)0；16的二進制值為0001 0000，右移兩位之后變成0000 0100，即變成十進制的4。

6. 位取反運算符（~）

位取反運算的實質(zhì)是將參與運算的數(shù)據(jù)按對應(yīng)的二進制數(shù)逐位反轉(zhuǎn)，即1取反后變?yōu)?，0取反后變?yōu)?。

【例3.53】使用位取反運算符進行運算，SQL語句如下：

邏輯運算5&~1中，由于位取反運算符‘~’的級別高于位與運算符‘&’，因此先對1取反操作，取反之后，除了最低位為0外，其他位都為1，再與十進制數(shù)值5進行與運算，結(jié)果為0100，即整數(shù)4。

可以使用BIN()函數(shù)查看1取反之后的結(jié)果，SQL語句如下：

這樣，讀者就可以明白【例3.53】是如何計算的了。

3.3.6　運算符的優(yōu)先級

運算符的優(yōu)先級決定了不同的運算符在表達式中計算的先后順序。表3.14列出了MySQL中的各類運算符及其優(yōu)先級。

表3.14　運算符按優(yōu)先級由低到高排列

可以看到，不同運算符的優(yōu)先級是不同的。一般情況下，級別高的運算符先進行計算，如果級別相同，MySQL就按表達式的順序從左到右依次計算。當(dāng)然，在無法確定優(yōu)先級的情況下，可以使用圓括號“（）”來改變優(yōu)先級，并且這樣會使計算過程更加清晰。