SELECT col, col, col; //找什么
FROM table; //从哪找
WHERE col条件 ; //条件是啥
条件:数字(where)
当查找条件col是数字:
select * from table where col = 1;
Operator | SQL Example | 解释 |
---|---|---|
, !=, < <=, >, >= | col !=4 | 等于 大于 小于 |
BETWEEN … AND … | col BETWEEN 1 AND 10 | 在X和X之间 |
NOT BETWEEN … AND | NOT BETWEEN 1 AND 10 | 不在X和X之间 |
IN (…) | col IN (2, 4, 6) | 在X集合中 |
NOT IN (…) | col NOT IN (1, 3, 5) | 不在集合X中 |
条件:文本(where)
当查找的条件col是文本:
select * from table where col like ‘%jin’;
Operator | SQL Example | 解释 |
---|---|---|
= | col =“abc” | 等于 |
!= or <> | col <> “abc” | 不等于 |
LIKE | col LIKE “ABC” | 等于 |
NOT LIKE | col NOT LIKE “ABC” | 不等于 |
% | col LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”) | 模糊匹配 |
_ | col LIKE “AN_” (matches “AND”, but not “AN”) | 模糊匹配(单字符) |
IN (…) | col IN (“A”, “B”, “C”) | 在集合中 |
NOT IN (…) | col NOT IN (“D”, “E”, “F”) | 不在集合中 |
排序(rows)
需要对结果rows排序和筛选部分rows:
select * from table where col > 1 order by col asc limit 2 offset 2
Operator | SQL Example | 解释 |
---|---|---|
ORDER BY | ORDER BY col ASC/DESC | 按col排序 |
ASC | ORDER BY col ASC | 升序 |
DESC | ORDER BY col DESC | 降序 |
LIMIT OFFSET | LIMIT num_limit OFFSET num_offset | 从offset取limit |
ORDER BY | ORDER BY col1 ASC,col2 DESC | 多列排序 |
连表(join)
当查找的数据在多张关联table里:
select * from table1 left join table2 on table1.id = table2.id where col > 1
Operator | SQL Example | 解释 |
---|---|---|
JOIN … ON … | t1 JOIN t2 ON t1.id = t2.id | 按ID连成1个表 |
INNER JOIN | t1 INNER JOIN t2 ON t1.id = t2.id | 只保留id相等的row |
LEFT JOIN | t1 LEFT JOIN t2 ON t1.id = t2.id | 保留t1的所有row |
RIGHT JOIN | t1 RIGHT JOIN t2 ON t1.id = t2.id | 保留t2的所有row |
IS/IS NOT NULL | col IS/IS NOT NULL | col是不是为null |
算式(select / where)
当需要对select的col 或 where条件的col 经过一定计算后才能使用:
select ,col2 from table where col/2 > 1
Operator | SQL Example | 解释 |
---|---|---|
+ - * / % | col1 + col2 | col加减乘除 |
substr | substr(col,0,4) | 字符串截取 |
AS | col * 2 AS col_new | col取别名 |
统计(select)
对查找的rows需要按col分组统计的情况:
select count(*),avg(col),col from table where col > 1 group by col
Operator | SQL Example | 解释 |
---|---|---|
COUNT(*), COUNT(column) | count(col) | 计数 |
MIN(column) | min(col) | 最小值 |
MAX(column) | max(col) | 最大值 |
AVG(column) | avg(col) | 平均值 |
SUM(column) | sum(col) | 求和 |
GROUP BY | group by col,col2 | 分组 |
HAVING | HAVING col>100 | 分组后条件 |
子表(table)
一次select的结果rows作为下一次select的临时table才能得到最终结果:
select * from (select * from table where col > 1) as tmp where col < 1
Operator | SQL Example | 解释 |
---|---|---|
(select -)as tmp | (select -)as tmp | select结果做子表 |
in(select -) | in(select -) | select结果做条件 |
avg(select -) | avg(select -) | select结果做条件 |