一、SELECT子句
(1)基本使用:
SELECT (points * 10 + 20) AS discount_factor
FROM customers
(2)运算顺序:
• 括号
• 乘除法
• 加减法
(3)移除因子:
SELECT DISTINCT state
FROM customers
二、WHERE子句
一般使用WHERE语句设立条件过滤数据。
(1)比较符号:
• Greater than: >
• Greater than or equal to: >=
• Less than: <
• Less than or equal to: <=
• Equal: =
• Not equal: <> 或者 !=
(2)逻辑符号:
AND
SELECT * FROM customers WHERE birthdate > ‘1990-01-01’ AND points > 1000
OR
SELECT * FROM customers WHERE birthdate > ‘1990-01-01’ OR points > 1000
NOT
SELECT * FROM customers WHERE NOT (birthdate > ‘1990-01-01’)
(3)其他符号:
IN(一定程度上减少OR的使用)
筛选来自这些州的顾客:VA, NY, CA
SELECT * FROM customers WHERE state IN (‘VA’, ‘NY’, ‘CA’)
BETWEEN
SELECT * FROM customers WHERE points BETWEEN 100 AND 200
LIKE
筛选出last name中以b开头的顾客:
SELECT * FROM customers WHERE first_name LIKE ‘b%’
• %: any number of characters
• _: exactly one character
REGEXP(一定程度上代替LIKE)
筛选出first name以a开头的顾客:
SELECT * FROM customers WHERE first_name REGEXP ‘^a’
• ^:字符串的开始
• $:字符串的结束
• |: 逻辑OR
• [abc]: abc中的任意一个
• [a-d]: a-d中的任意一个
例子:
Returns customers whose first name ends with EY or ON
WHERE first_name REGEXP ‘ey$|on$’
Returns customers whose first name starts with MY or contains SE
WHERE first_name REGEXP ‘^my|se’
Returns customers whose first name contains B followed by R or U
WHERE first_name REGEXP ‘b[ru]’
IS NULL
筛选出电话号码登记为空的顾客:
SELECT * FROM customers WHERE phone IS NULL
如果要查询“不为空”,就替换成IS NOT NULL
三、其他子句
ORDER BY
首先点击上图中三个icon中的🔧icon
进入这个页面之后我们发现customer_id前面的图标是黄色的🔑,代表“主键”的意思,主键必须是一个唯一识别表里所有记录的值。
--正序 Select * From customers ORDER BY first_name; --倒序 Select * From customers ORDER BY first_name DESC; --先按州的正序排列,然后每一个州里顾客按firstname正序排列 Select * From customers ORDER BY state, first_name;
MySQL和其他数据库的一个重要区别:MySQL中可以使用任何列排序数据,不管这一列是否包含在SELECT语句作用的对象中。比如下面:
SELECT first_name, last_name FROM customers ORDER BY birth_date; --比如这里birth_date就不在SELECT中
ORDER BY语句中还可以插入“运算”:
SELECT * FROM order_items WHERE order_id = 2 ORDER BY (quantity * unit_price) DESC
LIMIT
只返回前三名顾客:
SELECT * FROM customers LIMIT 3
有的时候在一些网页数据库中,比如每一页有三条顾客记录:
·Page1:1~3
·Page2:4~6
·Page3:7~9
如果此时我们希望跳过前两页,选择第三页的三条顾客记录:
SELECT * FROM customers LIMIT 6, 3
(其中,6表示被跳过的记录的数量,3表示我们希望查询到的数量)
🌟EXERCISE: Get the TOP3 loyal customers
SELECT * FROM customers ORDER BY points DESC LIMIT 3
LIMIT子句要永远放在最后!!!
四、总结
编写语句的时候一定要按照顺序,否则SQL会自动报错
遵循以下顺序:
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
LIMIT ...