Mysql之WHERE子句

以下是我在学习过程中自己总结的一些笔记

一、比较运算符有：>、>=、<、<=、=、！=和<>等价

SELECT *
FROM customers
WHERE points>3000

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'

 二、多条搜索条件（AND-OR-NOR-IN-BETWEEN-LIKE-REGEXP-IS NULL）-AND优先级大于OR

1.-AND

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' AND points > 1000

2.等价实例-OR

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR points > 1000 AND state='VA'

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR
        (points > 1000 AND state='VA')

3.等价实例-NOT

SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000)

SELECT *
FROM customers
WHERE birth_date <= '1990-01-01' AND points <= 1000

4.等价实例-IN

SELECT *
FROM customers
WHERE state='VA' OR state='FL' OR state='GA'

SELECT *
FROM customers
WHERE state IN ('VA','FL','GA')

5.等价实例-NOT IN

SELECT *
FROM customers
WHERE NOT state IN ('VA','FL','GA')

SELECT *
FROM customers
WHERE state NOT IN ('VA','FL','GA')

6.等价实例-BETWEEN（包含临界值）

SELECT *
FROM customers
WHERE points >=1000 AND points<=3000

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000

7.BETWEEN

SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

8.LIKE

  获得以B字母开头的姓名记录

SELECT *
FROM customers
WHERE last_name LIKE 'b%'    # %表示任意字符串

  获得姓氏中有字母'B'或者'b'的字符串

SELECT *
FROM customers
WHERE last_name LIKE '%b%'    # %表示任意字符

  获得以字母'B'或者'b'结尾并仅含三位字符的姓氏

SELECT *
FROM customers
WHERE last_name LIKE '__b'    # _表示一位字符

获得地址中包含Avenue和Trail的客户

SELECT *
FROM customers
WHERE address LIKE '%avenue%' OR
        address LIKE '%trail%'

9.NOT LIKE

SELECT *
FROM customers
WHERE address NOT LIKE '%avenue%'

10.REGEXP-正则表达式

WHERE last_name REGEXP 'field'    # 包含field的都会输出
WHERE last_name REGEXP '^field'    # 以field开头
WHERE last_name REGEXP 'field$'    # 以field结尾
WHERE last_name REGEXP 'field|mac|rose'   # 包含field、mac、rose其中之一即可
WHERE last_name REGEXP '^field|mac|rose'    # 以field开头，包含mac或rose
WHERE last_name REGEXP 'field|mac|rose$'    # 包含field、mac或者以rose结尾
WHERE last_name REGEXP '[gim]e'    # e前面为g、i或m
WHERE last_name REGEXP '[a-q]e'    # e前面为（包含）a-q之间任意字符
WHERE last_name REGEXP 'EY$|ON$'    # 以ey或者on结尾
WHERE last_name REGEXP '^MY|SE'    # 以my开头或者包含se
WHERE last_name REGEXP 'B[RU]'    # 包含BR或BU

11.IS NULL

USE sql_store;
SELECT *
FROM customers
WHERE phone IS NULL

12.IS NOT NULL 

USE sql_store;
SELECT *
FROM customers
WHERE phone IS NOT NULL