MySQL的運算符詳解與正則使用表達式查詢

04_運算符

1.算術運算符

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

1.加法于減法運算符

#在sql中，+沒有連接的作用，就表示加運算符。此時，會將字符串轉換為數值（飲食i轉換）
SELECT 100+'1';#結果是101
SELECT 100+'a';#結果是100，'a'看做0處理
SELECT 100+NULL;#結果是NULL，NULL參與運算，結果位null

2.乘法與除法運算符

3.求模（求余）運算符

2.比較運算符

1.等號運算符!

2.安全等與運算符

#查詢commission_pct等于0.40
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40;
#如果把0.40改成 NULL 呢？

可以看到，使用安全等于運算符時，兩邊的操作數的值都為NULL時，返回的結果為1而不是NULL，其他返回結果與等于運算符相同。

3．不等于運算符不等于運算符（<>和!=）用于判斷兩邊的數字、字符串或者表達式的值是否不相等，如果不相等則返回1，相等則返回0。不等于運算符不能判斷NULL值。如果兩邊的值有任意一個為NULL，或兩邊都為NULL，則結果為NULL。 SQL語句示例如下：

此外，還有非符號類型的運算符：

空運算符空運算符（IS NULL或者ISNULL）判斷一個值是否為NULL，如果為NULL則返回1，否則返回 0。 SQL語句示例如下：

#查詢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);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

非空運算符非空運算符（IS NOT NULL）判斷一個值是否不為NULL，如果不為NULL則返回1，否則返回0。 SQL語句示例如下：

#查詢commission_pct不等于NULL
SELECT 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。

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

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

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

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

IN運算符 IN運算符用于判斷給定的值是否是IN列表中的一個值，如果是則返回1，否則返回0。如果給定的值為NULL，或者IN列表中存在NULL，則結果為NULL。

SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);

NOT IN運算符 NOT IN運算符用于判斷給定的值是否不是IN列表中的一個值，如果不是IN列表中的一個值，則返回1，否則返回0。

\11. LIKE運算符 LIKE運算符主要用來匹配字符串，通常用于模糊匹配，如果滿足條件則返回1，否則返回 0。如果給定的值或者匹配條件為NULL，則返回結果為NULL。 LIKE運算符通常使用如下通配符：

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

SQL語句示例如下：

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

ESCAPE

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

SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;

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

SELECT job_id
FROM jobs
WHERE 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語句示例如下：

3.邏輯運算符

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

SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

2．邏輯與運算符邏輯與（AND或&&）運算符是當給定的所有值均為非0值，并且都不為NULL時，返回 1；當給定的一個值或者多個值為0時則返回0；否則返回NULL。

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%';

3．邏輯或運算符邏輯或（OR或||）運算符是當給定的值都不為NULL，并且任何一個值為非0值時，則返回1，否則返回0；當一個值為NULL，并且另一個值為非0值時，返回1，否則返回NULL；當兩個值都為 NULL時，返回NULL。

#查詢基本薪資不在9000-12000之間的員工編號和基本薪資
SELECT employee_id,salary FROM employees
WHERE NOT (salary >= 9000 AND salary <= 12000);
SELECT employee_id,salary FROM employees
WHERE salary <9000 OR salary > 12000;
SELECT employee_id,salary FROM employees
WHERE salary NOT BETWEEN 9000 AND 12000;
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';

4．邏輯異或運算符邏輯異或（XOR）運算符是當給定的值中任意一個值為NULL時，則返回NULL；如果兩個非NULL的值都是0或者都不等于0時，則返回0；如果一個值為0，另一個值不為0時，則返回1。

select last_name,department_id,salary
from employees
where department_id in (10,20) XOR salary > 8000;

4.位運算符

1．按位與運算符按位與（&）運算符將給定值對應的二進制數逐位進行邏輯與運算。當給定值對應的二進制位的數值都為1時，則該位返回1，否則返回0。

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

按位或運算符按位或（|）運算符將給定的值對應的二進制數逐位進行邏輯或運算。當給定值對應的二進制位的數值有一個或兩個為1時，則該位返回1，否則返回0。

1的二進制數為0001，10的二進制數為1010，所以1 | 10的結果為1011，對應的十進制數為11。20的二進制數為10100，30的二進制數為11110，所以20 | 30的結果為11110，對應的十進制數為30。

按位異或運算符按位異或（^）運算符將給定的值對應的二進制數逐位進行邏輯異或運算。當給定值對應的二進制位的數值不同時，則該位返回1，否則返回0。

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

再舉例：

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

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

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

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

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

1的二進制數為0000 0001，左移兩位為0000 0100，對應的十進制數為4。4的二進制數為0000 0100，左移兩位為0001 0000，對應的十進制數為16。

5.運算符的優先級

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

正則表達式通常被用來檢索或替換那些符合某個模式的文本內容，根據指定的匹配模式匹配文本中符合要求的特殊字符串。例如，從一個文本文件中提取電話號碼，查找一篇文章中重復的單詞或者替換用戶輸入的某些敏感詞語等，這些地方都可以使用正則表達式。正則表達式強大而且靈活，可以應用于非常復雜的查詢。 MySQL中使用REGEXP關鍵字指定正則表達式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表。

查詢以特定字符或字符串開頭的記錄字符‘^’匹配以特定字符或者字符串開頭的文本。在fruits表中，查詢f_name字段以字母‘b’開頭的記錄，SQL語句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';

查詢以特定字符或字符串結尾的記錄字符‘$’匹配以特定字符或者字符串結尾的文本。在fruits表中，查詢f_name字段以字母‘y’結尾的記錄，SQL語句如下：
```
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
```
用符號"."來替代字符串中的任意一個字符字符‘.’匹配任意一個字符。在fruits表中，查詢f_name字段值包含字母‘a’與‘g’且兩個字母之間只有一個字母的記錄，SQL語句如下：
```
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
```
使用""和"+"來匹配多個字符星號‘’匹配前面的字符任意多次，包括0次。加號‘+’匹配前面的字符至少一次。在fruits表中，查詢f_name字段值以字母‘b’開頭且‘b’后面出現字母‘a’的記錄，SQL語句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';

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

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

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';

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

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

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

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

mysql> SELECT * FROM fruits WHERE f_name like 'on';
Empty set(0.00 sec)

匹配指定字符中的任意一個方括號“[]”指定一個字符集合，只匹配其中任何一個字符，即為所查找的文本。在fruits表中，查找f_name字段中包含字母‘o’或者‘t’的記錄，SQL語句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

在fruits表中，查詢s_id字段中包含4、5或者6的記錄，SQL語句如下：

mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]'

匹配指定字符以外的字符 “[^字符集合]” 匹配不在指定集合中的任何字符。在fruits表中，查詢f_id字段中包含字母a_e和數字12以外字符的記錄，SQL語句如下：

mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

使用{n,}或者{n,m}來指定字符串連續出現的次數 “字符串{n,}”表示至少匹配n次前面的字符；“字符串 {n,m}”表示匹配前面的字符串不少于n次，不多于m次。例如，a{2,}表示字母a連續出現至少2次，也可以大于2次；a{2,4}表示字母a連續出現最少2次，最多不能超過4次。

在fruits表中，查詢f_name字段值出現字母‘x’至少2次的記錄，SQL語句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';

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

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';

課后習題

#1.
SELECT last_name,salary
FROM employees
#WHERE salary NOT BETWEEN 5000 AND 12000;
WHERE salary <5000 OR salary >12000;
#2.
SELECT last_name,department_id
FROM employees
#WHERE department_id in (20,50);
WHERE department_id = 20 OR department_id = 50;
#3.
SELECT last_name,job_id
FROM employees
;WHERE manager_id IS NULL;
WHERE manager_id <=> NULL;
#4
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#5
SELECT  last_name
FROM employees
WHERE last_name LIKE '__c%';
#6
SELECT last_name
FROM employees
#WHERE last_name like '%a%k%' OR last_name LIKE '%k%a%';
WHERE last_name LIKE '%a%' AND last_name LIKE '%k%';
#7
SELECT first_name
FROM employees
#WHERE first_name LIKE '%e';
where first_name REGEXP 'e$';# 以e開頭的寫法'^e'
#8
SELECT last_name,job_id,department_id
FROM employees
#WHERE department_id BETWEEN 80 AND 100;
#WHERE department_id >=80 AND department_id<=100;
WHERE department_id in(80,90,100);

#9
SELECT  last_name,salary,manager_id
FROM employees
WHERE manager_id in(100,101,102);

posted @ 2022-04-22 15:17 輕狂書生han 閱讀(683) 評論(0) 收藏舉報

刷新頁面返回頂部

輕狂書生han