SELECT col,col,col 找什么？
FROM table 从哪找？
WHERE col 条件 条件是啥？
当查找条件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 集合 |
当查找条件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
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 | 多列排序 |
当查找的数据在多张关联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的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取别名 |
| ... | 还有很多 |
对查找的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 | 分组后条件 |
一次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结果做条件 |