SELECT*FROM customers
WHERE state ='VA'//表示筛选在VA的数据WHERE state <>'VA'//表示筛选不在VA的数据-- 注意:字符串需要用单引号引起WHERE birth_date >'1990-01-01'-- 筛选日期时,也要将日期用单引号引起。-- MySQL标准或默认日期表述形式:4位年份-2位月份-2位日期
-- 小小练习SELECT*FROM orders
WHERE order_date >='2019-01-01 '
AND,OR,NOT运算符
优先级:AND > OR
SELECT*FROM customers
WHERE birth_date >'1990-01-01'OR(points >1000AND state ='VA')
-- 小小练习SELECT*FROM order_items
WHERE order_id =6AND unit_price * quantity >30
IN运算符
SELECT*FROM customers
-- 查询多个条件时用OR比较麻烦WHERE state ='VA'OR state ='GA'OR state ='FL'-- 此时可以用IN运算符WHERE state IN('VA','FL','GA')-- 可用NOT IN查询不在IN范围内的WHERE state NOTIN('VA','FL','GA')
-- 小小练习SELECT*FROM products
WHERE quantity_in_stock IN(49,38,72)
-- 小小联系-- 1SELECT*FROM customers
WHERE address LIKE'%trail%'OR
address LIKE'%avenue%';-- 2SELECT*FROM customers
WHERE phone LIKE'%9';
REGEXP运算符
正则表达式
^ 代表字符串开头
¥ 代表字符串结尾
| 代表逻辑或,可以给多个搜索模式
[abcd] 匹配任意在括号里列举的单字符
[a-f] 匹配从a到f的字符
SELECT*FROM customers
WHERE last_name REGEXP'field|mac|rose'-- 逻辑或,搜索姓名中有field、mac或rose的数据
SELECT*FROM customers
WHERE last_name REGEXP'^rose'-- 代表rose开头,搜索以rose开头的数据
SELECT*FROM customers
WHERE last_name REGEXP'field$'-- 代表field结尾,搜索以field结尾的数据
SELECT*FROM customers
WHERE last_name REGEXP'[gim]e'-- 任何姓氏中有ge、ie或me的数据WHERE last_name REGEXP'[a-h]e'-- 任何姓氏中有ae,be,ce…… he的数据
-- 小小练习-- 踩坑:注意引号内的内容不能随便加空格,加空格的话就会被认为需要匹配一个空格SELECT*FROM customers
WHERE last_name REGEXP'EY$|ON$'SELECT*FROM customers
WHERE first_name REGEXP'ELKA|AMBUR'SELECT*FROM customers
WHERE last_name REGEXP'^MY|SE'SELECT*FROM customers
WHERE last_name REGEXP'B[RU]'
IS NULL运算符
IS NULL
IS NOT NULL
-- 小小练习SELECT*FROM orders
WHERE shipped_date ISNULL