SELECT col,col,col 找什么？
FROM table 从哪找？
WHERE col 条件 条件是啥？
条件：数字(where)
当查找条件col是数字
select * from table where col = 1;
Operator | Condition | SQL Example | 解释 |
=, !=, < ,<=, >, >= | Standard numerical operators | col != 4 | 等于 大于 小于 |
BETWEEN … AND … | Number is within range of two values (inclusive) | col BETWEEN 1.5 AND 10.5 | 在 X 和 X之间 |
NOT BETWEEN … AND … | Number is not within range of two values (inclusive) | co NOT BETWEEN 1 AND10 | 不在 X 和 X之间 |
IN (…) | Number exists in a list | col IN (2, 4, 6) | 在 X 集合 |
NOT IN (…) | Number does not exist in a list | col NOT IN (1, 3, 5) | 不在 X 集合 |
条件：文本(where)
当查找条件col是文本
select * from table where col like '%jin';
Operator | Condition | SQL Example | 解释 |
= | Case sensitive exact string comparison (notice the single equals) | col = "abc" | 等于 |
!= or <> | Case sensitive exact string inequality comparison | col != "abcd" | 不等于 |
LIKE | Case insensitive exact string comparison | col LIKE "ABC" | 等于 |
NOT LIKE | Case insensitive exact string inequality comparison | col NOT LIKE "ABCD" | 不等于 |
% | Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) | col LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS") | 模糊匹配 |
_ | Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) | col LIKE "AN_" (matches "AND", but not "AN") | 模糊匹配单字符 |
IN (…) | String exists in a list | col IN ("A", "B", "C") | 在集合 |
NOT IN (…) | String does not exist in a list | co NOT IN ("D", "E", "F") | 不在集合 |
排序(rows)
需要对结果rows排序和筛选部分rows
select * from table where col > 1 order by col asc limit 2 offset 2
Operator | Condition | SQL Example | 解释 |
ORDER BY | . | ORDER BY col ASC/DESC | 按col排序 |
ASC | . | ORDER BY col ASC/DESC | 升序 |
DESC | . | ORDER BY col ASC/DESC | 降序 |
LIMIT OFFSET | . | LIMIT num_limit OFFSET num_offset | 从offset取limit |
ORDER BY | . | ORDER BY col1 ASC,col2 DESC | 多列排序 |
join:连表(table)
当查找的数据在多张关联table里
select * from table1 left join table2 on table1.id = table2.id where col > 1
Operator | Condition | 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 *,col*2 from table where col/2 > 1
Operator | Condition | 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 | Condition | SQL Example | 解释 |
COUNT(*), COUNT(column) | A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column. | count(col) | 计数 |
MIN(column) | Finds the smallest numerical value in the specified column for all rows in the group. | min(col) | 最小 |
MAX(column) | Finds the largest numerical value in the specified column for all rows in the group. | max(col) | 最大 |
AVG(column) | Finds the average numerical value in the specified column for all rows in the group. | avg(col) | 平均 |
SUM(column) | Finds the sum of all numerical values in the specified column for the rows in the group. | 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 | Condition | SQL Example | 解释 |
（select -）as tmp | （select -）as tmp | select结果做子表 | |
in（select -） | in（select -） | select结果做条件 | |
avg（select -） | avg（select -） | select结果做条件 |