1- 选择语句| The SELECT statement
USE sql_store;
SELECT * FROM customers
-- WHERE customers_id = 1
ORDER BY first_name
2、选择子句
SELECT
last_name, -- 指定返回列
first_name,
points,
(points + 10) *100 AS 'discount_factor' -- 用AS给列加上别名
FROM customersSELECT DISTINCT state
FROM customers
练习:
SELECT name, unit_price, unit_price * 1.1 AS new_price -- 注意空格
FROM products
3、WHERE 子句
用where语句筛选数据
SELECT *
FROM Customers
WHERE points > 3000
练习题
Get the orders placed this year
SELECT *
FROM orders
WHERE order_date >= '2019-01-01'
4、AND、OR、NOT运算符
SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR points > 1000 AND state = 'VA' -- 返回 1990-01-01之后出生的 或者积分大于1000且VA州的顾客信息,要注意运算符的优先级 :AND > ORWHERE NOT (birth_date > '1990-01-01' OR points > 1000) -- 返回 1990-01-01之前出生并且积分并且积分小于等于1000顾客信息,要注意不是补集!!!
练习题
-- From the order_item table, get the items
-- for order #6
-- where the total price is greater than 30
SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price * quantity > 30
5、IN运算符
SELECT *
FROM customersWHERE state IN ('VA', 'GA', 'FL')
--WHERE state = 'VA' OR state = 'GA' OR state = 'FL' --这两条意思一样--WHERE state NOT IN ('VA', 'GA', 'FL') --这里的NOT是取补集
练习
-- Return products
-- quantity in stock equal to 49,38,72
SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72)
6、between 运算符
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
练习:
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01' -- 注意日期的格式
7、LIKE运算符
SELECT *
FROM customers
WHERE last_name LIKE 'brush%' -- 返回以brush开头的名字的顾客信息--WHERE last_name LIKE 'b%' -- 返回以b开头的名字的顾客信息,不论大小写
--WHERE last_name LIKE '%b%' -- 返回名字中间有b的顾客信息
--WHERE last_name LIKE '_____y' -- 返回名字y前有五个字母的顾客信息
-- % any number of characters
-- _ sigle character
练习题
SELECT *
FROM customers
WHERE address LIKE '%TRAIL%' OR
address LIKE '%AVENUE%'--WHERE phone NOT LIKE '%9'
8、REGEXP运算符
正则表达式(regular expression):用于搜索字符串
SELECT *
FROM customers
WHERE last_name REGEXP 'field' -- 返回名字里有field的顾客信息--
-- ^ 表示开头
--$ 表示结尾
-- | 表示OR,给出多个搜索模式,如'ELKA|AMBUR','EY$|ON$'
--[ ] 提供一个搜索范围,如 ’[gim]e‘ 也可以 'e[a-h]' 匹配任意在括号里列举的单字符
-- 优先级^和$ 高于|
9、NULL运算符
SELECT *
FROM customers
WHERE phone IS NULL --返回没有电话号码的顾客--WHERE phone IS NOT NULL --也可以加NOT
练习题
SELECT *
FROM orders
WHERE shipper_id IS NULL
10、ORDER BY子句
SELECT *, quantity*unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY quantity*unit_price DESC
11、LIMIT子句
SELECT *
FROM order_items
LIMIT 3 -- 返回前三名顾客--LIMIT 6, 3 --跳过前6个顾客选择7、8、9
练习题
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3 --积分前三名的用户,LIMIT语句永远放后面