mysql入门| day01 |基本查询语句

查询语句

1、and or not

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

SELECT *
FROM customers
WHERE not (birth_date>'1990-01-01' or points > 1000)

练习：求出编号为6且总价大于三十的项目

SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price*quantity > 30

2、IN运算符

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


SELECT *
FROM customers
WHERE state ='VA'OR 'GA'
这种写法不对是因为用OR合并多个条件，or后只能跟boolean value 

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

练习：查询现货库存数量为49，38，72的产品

SELECT *
FROM products
WHERE quantity_in_stock IN ('49','38','72products')

3、REGEXP运算符（正则表达式）

SELECT *
FROM customers
WHERE first_name regexp 'r$|l|^a'

r$ 表示以r结尾
l  表示名字中包含l
^a 表示以a开头

SELECT *
FROM customers
WHERE last_name regexp '[gi]e'

ge
ie
【】中任意字母都能出现在e前边

SELECT *
FROM customers
WHERE last_name regexp '[a-h]e'

表示a-h之间任意字母出现在e前边

练习1:获取名字是ELKA 或AMbur的顾客

SELECT *
FROM customers
WHERE first_name regexp 'ELKA|AMBUR'

练习2：获取姓氏以ey或者on结尾的顾客

SELECT *
FROM customers
WHERE last_name regexp 'ey$|on$'

练习3：获取姓氏以my开头或者包含se的顾客

SELECT *
FROM customers
WHERE last_name regexp '^my|se'

练习4：获取姓氏包含b后边跟着r或u

SELECT *
FROM customers
WHERE last_name regexp 'b[ru]'

4、LIMIT子句

SELECT *
FROM orders
LIMIT 6, 3

-- 6属于偏移量 
-- page 1 : 1-3
-- page 2 : 4-6
-- page 3 : 7-9

上边语句执行出来查询的是7-9