学习内容来自B站SQL进阶教程 | 史上最易懂SQL教程!10小时零基础成长SQL大师!!
1.SELECT
2.WHERE
3.AND,OR,NOT
4.IN
5.BETWEEN
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01';
6.检索特定字符串模式 LIKE
(1)SELECT *
FROM customers
WHERE last_name LIKE 'B%'; (%:匹配任意多个字符,可以放在模式的任意位置)
(2)SELECT *
FROM customers
WHERE last_name LIKE '_o%'; (_:匹配单个字符)
(3)
SELECT *
FROM customers
WHERE address LIKE '%TRAIL%'
OR address LIKE '%AVENUE%';
-- 得到address中含有’trail’或 ‘avenue’ 的顾客
7.正则表达式匹配 REGEXP
$: 以某个字符串结尾,
^: 以某个字符串开头
| : 连接多个模式
-: 表示范围 例如 0-9
还有许多…
SELECT *
FROM customers
WHERE last_name REGEXP 'field$|^mac';
8.IS NULL 或 IS NOT NULL
9.为数据排序 ORDER BY
(1)
-- 以first_name排序,默认为升序,DESC代表降序
SELECT *
FROM customers
ORDER BY first_name DESC;
(2)
-- 以state降序,然后每个state里面first_name升序
SELECT *
FROM customers
ORDER BY state DESC,first_name;
(3)
-- 1 代表first_name, 2代表last_name(使用列位置排序,尽量避免使用,使用列名排序较好)
SELECT first_name,last_name
FROM customers
ORDER BY 1 DESC, 2;
10.LIMIT 子句 (LIMIT永远放在语句最后)
(1)
-- 显示n条记录,如果超过了总数则显示全部
SELECT *
FROM customers
LIMIT 30;
(2)
-- 跳过前n条记录,显示后面的记录(偏移量)
SELECT *
FROM customers
LIMIT 6,3;
(3)
-- 获取积分前三的顾客
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3;