跳至內容

MySQL/Language/Operators

維基教科書,自由的教學讀本

操作符用於寫表達式,可作用於常量值、變量值、列名等。

比較操作符

[編輯]

相等與不等

[編輯]
 SELECT True = True -- returns 1
 SELECT True = False -- returns 0


 SELECT True <> False -- returns 1
 SELECT True != True -- returns 0

IS以及NULL安全的比較

[編輯]

NULL值與非空值比較,結果為NULL。判斷一個值是否為null,應該用IS:

 SELECT (NULL IS NULL) -- returns 1
 SELECT (1 IS NULL) -- returns 0
 SELECT (True IS True) -- returns 1

檢查一個值 非空:

 SELECT (True IS NOT NULL) -- returns 1

運算符<=>同時兼顧了空值比較與普通值比較:

 SELECT NULL <=> NULL -- 1
  SELECT NULL <=> True -- NULL
 SELECT True <=> True -- 1
 SELECT col1 <=> col2 FROM myTable

沒有空值安全的不等比較運算符,變通辦法是:

 SELECT NOT (col1 <=> col2) FROM myTable

IS與布爾比較

[編輯]

IS與IS NOT可用於布爾比較,用於保留字TRUE, FALSE 與 UNKNOWN (同義詞NULL).

 SELECT 1 IS TRUE -- returns 1
 SELECT 1 IS NOT TRUE -- returns 0
 SELECT 1 IS FALSE -- returns 0
 SELECT 0 IS FALSE -- returns 1
 SELECT (NULL IS NOT FALSE) -- returns 1: unknown is not false
 SELECT (NULL IS UNKNOWN) -- returns 1
 SELECT (NULL IS NOT UNKNOWN) -- returns 0

大於,小於...

[編輯]
 SELECT 100 > 0 -- returns 1
 SELECT 4 > 5 -- return 0
 SELECT 1 < 2 -- returns 1
 SELECT 2 < 2 -- returns 0

也可用於文本:

 SELECT 'a' < 'b' -- returns 1

實際上,字符比較使用COLLATION定義的排序規則。

<= 或>=操作符。

BETWEEN

[編輯]
 SELECT 2 BETWEEN 10 AND 100    -- 0
 SELECT 10 BETWEEN 10 AND 100   -- 1
 SELECT 20 BETWEEN 10 AND 100   -- 1

上述範圍是閉區間。

也可以用NOT BETWEEN:

 SELECT 8 NOT BETWEEN 5 AND 10 -- returns 0

IN

[編輯]
 SELECT 5 IN (5, 6, 7) -- returns 1
 SELECT 1 IN (5, 6, 7) -- returns 0
 SELECT 5 IN ('a', 'z', '5')  -- returns 1  ??

值的列表個數沒有限制。

可以使用NOT IN:

 SELECT 1 NOT IN (1, 2, 3) -- returns 0

邏輯運算符

[編輯]

MySQL布爾邏輯

[編輯]

MySQL沒有真正的BOOLEAN類型。FALSE是0的同義詞。在布爾上下文中空字符串等效於FALSE。TRUE是1的同義詞。在不二上下文中所有非空且非FALSE的數據被認為是TRUE

UNKNOWN是NULL的同義詞。

特殊日期0/0/0是NULL.

NOT

[編輯]

NOT是單元運算符

 SELECT NOT 1 -- returns 0
 SELECT NOT FALSE -- returns 1
 SELECT NOT NULL -- returns NULL
 SELECT NOT UNKNOWN -- returns NULL

!是NOT的同義符號:

 SELECT !1

AND

[編輯]

如果一個操作數為NULL,結果為NULL

 SELECT 1 AND 1 -- returns 1
 SELECT 1 AND '' -- return 0
 SELECT '' AND NULL -- returns NULL

&&是AND的同義符號;

 SELECT 1 && 1

OR

[編輯]

如果兩個操作數都為NULL,結果為NULL.

 SELECT TRUE OR FALSE -- returns 1
 SELECT 1 OR 1 -- returns 1
 SELECT FALSE OR FALSE -- returns 0
 SELECT NULL OR TRUE -- returns 1

||是OR的同義:

 SELECT 1 || 0

XOR

[編輯]

NULL值

[編輯]

如果一個操作數為NULL,結果為NULL

 SELECT 1 XOR 0 -- returns 1
 SELECT FALSE XOR TRUE -- returns 1
 SELECT 1 XOR TRUE -- returns 0
 SELECT 0 XOR FALSE -- returns 0
 SELECT NULL XOR 1 -- returns NULL

算術運算符

[編輯]

可以指定數的正號 '+':

 SELECT +1 -- return 1

可以指定數的負號 '-'

 SELECT -1 -- returns -1
 SELECT -+1 -- returns -1
 SELECT --1 -- returns 1

求和:

 SELECT 1 + 1 -- returns 2

減法

 SELECT True - 1 -- returns 0

乘法:

 SELECT 1 * 1 -- returns 1

除法,返回浮點數:

 SELECT 10 / 2 -- returns 5.0000
 SELECT 1 / 1 -- returns 1.0000
 SELECT 1 / 0 -- returns NULL (not an error)

整數除法DIV,返回整數,沒有餘數:

 SELECT 10 DIV 3 -- returns 3

求餘數,用 '%' 或 MOD:

 SELECT 10 MOD 3 -- returns 1

用 + 做數據類型轉換

[編輯]

整型到浮點類型:

 SELECT 1 + 0.0 -- returns 1.0
 SELECT 1 + 0.000 -- returns 1.000
 SELECT TRUE + 0.000 -- returns 1.000

字符到整型:

 SELECT '1' + 0 -- returns 1
 SELECT '1' + FALSE -- returns 1
 SELECT <nowiki>''</nowiki> + <nowiki>''</nowiki> -- returns 0

文本操作符

[編輯]

MySQL沒有文本操作符。

加號+把兩個文本轉換為整型再相加。

應該用CONCAT()函數連接兩個字符串。

LIKE

[編輯]

字符串模糊匹配:

 SELECT * FROM articles WHERE title LIKE 'hello world'

匹配是大小寫不敏感的,例外情況:

  • 如果列被聲明為BINARY
  • 當表達式包含BINARY子句:
 SELECT * 'test' LIKE BINARY 'TEST' -- returns 0

通配符:

  • _ 任何單個字符
  • % 任何長度的字符序列

"\"轉義引號 ("'")不受ESCAPE子句影響。"\"不能轉義自身。

例子:

  • 以"hello"開頭的詞:
 SELECT * FROM articles WHERE title LIKE 'hello%'
  • 以"world"結尾的詞:
 SELECT * FROM articles WHERE title LIKE '%world'
  • 包含"gnu"的詞:
 SELECT * FROM articles WHERE title LIKE '%gnu%'

轉義保留字符:

 SELECT * FROM articles WHERE title LIKE '\_%' -- titles starting with _
 SELECT * FROM articles WHERE title LIKE '\%%' -- titles starting with %

使用其他指定的轉義字符:

 SELECT * FROM articles WHERE title LIKE '/_%' ESCAPE '/'

使用LIKE就不會忽略兩端空格符:

 SELECT 'word' = 'word ' -- returns 0
 SELECT 'word' LIKE 'word ' -- returns 0


LIKE也用於數值:

 SELECT 123 LIKE '%2%' -- returns 1

NOT LIKE:

 SELECT 'a' NOT LIKE 'b' -- returns 1

SOUNDS LIKE

[編輯]
 SELECT `word1` SOUNDS LIKE `word2` FROM `wordList` -- short form
 SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList` -- long form

正則表達式

[編輯]

使用REGEXP表示正則表達式模式

 SELECT 'string' REGEXP 'pattern'

RLIKE是REGEXP的同義。

位運算符

[編輯]

Bit-NOT:

 SELECT ~0 -- returns 18446744073709551615
 SELECT ~1 -- returns 18446744073709551614

Bit-AND:

 SELECT 1 & 1 -- returns 1
 SELECT 1 & 3 -- returns 1
 SELECT 2 & 3 -- returns 2

Bit-OR:

 SELECT 1 | 0 -- returns 1
 SELECT 3 | 0 -- returns 3
 SELECT 4 | 2 -- returns 6

Bit-XOR:

 SELECT 1 ^ 0 -- returns 1
 SELECT 1 ^ 1 -- returns 0
 SELECT 3 ^ 1 -- returns 2

Left shift:

 SELECT 1 << 2 -- returns 4

Right shift:

 SELECT 1 >> 2 -- 0

條件運算符

[編輯]

IF

[編輯]

結構IF ... THEN ... [ELSEIF ... THEN ...] ELSE ... END IF;只能用於存儲過程。表達式中應該使用[1]: IF(condition, ifTrue, ifFalse);.

例如SELECT IF(-1 < 0, 0, 1); returns 0.

CASE

[編輯]

SELECT CASE WHEN condition THEN ifTrue ELSE ifFalse END;

例子: SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END; renvoie 0.

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;


SELECT CASE v
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    WHEN 3 THEN 'c'
    WHEN 4 THEN 'd'
    ELSE 0
    END as value

優先級

[編輯]

運算符優先級

[編輯]

優先級列表:

 INTERVAL
 BINARY, COLLATE
 !
 - (unary minus), ~ (unary bit inversion)
 ^
 *, /, DIV, %, MOD
 -, +
 <<, >>
 &
 |
 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
 BETWEEN, CASE, WHEN, THEN, ELSE
 NOT
 &&, AND
 XOR
 ||, OR
 :=

修改器:

  • PIPES_AS_CONCAT - 如果打開這種SQL模式,||優先級比^高,但比 - 與 ~ 低。
  • HIGH_NOT_PRECEDENCE - 如果打開這種SQL模式, NOT 與 ! 優先級相同。

使用括號

[編輯]
 SELECT (1 + 1) * 5 -- returns 10

為了可讀性:

 SELECT 1 + (2 * 5) -- the same as 1 + 2 * 5

INTERVAL

[編輯]

兩種用法:

SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10); --返回不小于第一个数的的索引。从零开始的索引。这里返回结果为6
SELECT NOW()-INTERVAL 24 HOUR  --  返回 前一天的时刻

賦值運算符

[編輯]
 UPDATE `myTable` SET `uselessField`=0
 SELECT @myvar := 1

SELECT INTO用於一個或多個同時賦值。

參考文獻

[編輯]
  1. http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html