SELECT 查询

  1. /* Select query for a specific columns */
  2. SELECT column, another_column, …
  3. FROM mytable;
  5. /* Select query for all columns */
  6. SELECT * 
  7. FROM mytable;

具有约束的查询

SELECT col, col, col （找什么？）
FROM table （从哪找？）
WHERE col 条件（AND/OR 连接） （条件是啥？）

  1. /* Select query with constraints */
  2. SELECT column, another_column, …
  3. FROM mytable
  4. WHERE condition
  5.     AND/OR another_condition
  6.     AND/OR …;

约束条件：数字

当查找条件 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 AND 10 不在 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 distinct * from table where col > 1 order by col asc limit 2 offset 2

Keyword Condition SQL Example 解释
DISTINCT Discard rows that have a duplicate column value SELECT DISTINCT col
ORDER BY … ASC/DESC Sort results ORDER BY col ASC/DESC
ORDER BY col1 ASC,col2 DESC		 按 col 升序/降序（单列/多列）
LIMIT OFFSET Commonly used with ORDER BY, The LIMIT reduce the number of rows, the optional OFFSET specify where to begin LIMIT num_limit OFFSET num_offset 从 offset 开始取limit 行 
  1. /* Select query with unique results */
  2. SELECT DISTINCT column, another_column, …
  3. FROM mytable
  4. WHERE condition(s);
  6. /* Select query with ordered results */
  7. SELECT column, another_column, …
  8. FROM mytable
  9. WHERE condition(s)
  10. ORDER BY column ASC/DESC;
  12. /* Select query with limited rows */
  13. SELECT column, another_column, …
  14. FROM mytable
  15. WHERE condition(s)
  16. ORDER BY column ASC/DESC
  17. LIMIT num_limit OFFSET num_offset;

JOIN 多表查询

当查找的数据在多张关联table里
select * from table1 left join table2 on table1.id = table2.id where col > 1

Keyword Condition SQL Example 解释
JOIN .. ON .. Matches rows from the first table and the second table which have the same key t1 JOIN t2 ON t1.id = t2.id 按 ID 连成 1 个表
INNER JOIN Equivalent to join, we continue to use inner-joins because it make the query easier to read once you start using other types of joins 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 
  1. /* Select query with INNER JOIN on multiple tables */
  2. SELECT column, another_table_column, …
  3. FROM mytable
  4. INNER JOIN another_table 
  5.     ON mytable.id = another_table.id
  6. WHERE condition(s)
  7. ORDER BY column, … ASC/DESC
  8. LIMIT num_limit OFFSET num_offset;
  10. /* Select query with LEFT/RIGHT/FULL JOINs on multiple tables */
  11. SELECT column, another_column, …
  12. FROM mytable
  13. INNER/LEFT/RIGHT/FULL JOIN another_table 
  14.     ON mytable.id = another_table.matching_id
  15. WHERE condition(s)
  16. ORDER BY column, … ASC/DESC
  17. LIMIT num_limit OFFSET num_offset;
  19. /* Select query with constraints on NULL values */
  20. SELECT column, another_column, …
  21. FROM mytable
  22. WHERE column IS/IS NOT NULL
  23. AND/OR another_condition
  24. AND/OR …;

带有算式的查询

当需要对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结果做条件

参考

  1. sqlbolt.com - Learn SQL with simple, interactive exercises. 👍
  2. CSDN - 【SQL】学习&练习SQLBolt
  3. xuesql.cn