SQL查询语句

1.条件查询

基本查询

SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

关键字

关键字	说明	举例
=, !=, < <=, >, >=	基础的 大于，等于等比较	col_name != 4
BETWEEN … AND …	在两个数之间	col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …	不在两个数之间	col_name NOT BETWEEN 1 AND 10
IN (…)	在一个列表	col_name IN (2, 4, 6)
NOT IN (…)	不在一个列表	col_name NOT IN (1, 3, 5)

操作符

操作符	说明	举例
=	完全等于	col_name = “abc”
!= or <>	不等于	col_name != “abcd”
LIKE	没有用通配符等价于 =	col_name LIKE “ABC”
NOT LIKE	没有用通配符等价于 !=	col_name NOT LIKE “ABCD”
%	通配符，代表匹配0个及以上的字符	col_name LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”) “%AT%” 代表AT 前后可以有任意字符
_	和% 相似，代表1个字符	col_name LIKE “AN_” (matches “AND”, but not “AN”)
IN (…)	在列表	col_name IN (“A”, “B”, “C”)
NOT IN (…)	不在列表	col_name NOT IN (“D”, “E”, “F”)

2: 查询结果过滤（Filtering） 和 排序（sorting）

DISTINCT ，直接删除重复行

SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

ORDER BY col_name，结果排序

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
-- ASC升序，DESC降序
ORDER BY column ASC/DESC;

Limit ，选取部分结果

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
-- 查询第6~10行数据，LIMIT 5 OFFSET 5;
LIMIT num_limit OFFSET num_offset;

3.多表联合查询

INNER JOIN，互相找不到的数据将会舍弃，简写为JOIN。

LEFT JOIN，左连接

RIGHT JOIN ，右连接

FULL JOIN，全连接

SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

4.特殊关键字 NULLs

SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

5.在查询中使用表达式

as，别名

SELECT  particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 >500;

6.在查询中进行统计

常见统计函数

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression;

Function	Description
COUNT(***)**, COUNT(column)	计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column)	找column最小的一行.
MAX(column)	找column最大的一行.
**AVG(**column)	对column所有行取平均值.
SUM(column)	对column所有行求和.

分组统计

GROUP BY ，先WHERE，然后对结果进行分组

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column;

HAVING，对GROUP BY 之后的数据再做SELECT筛选

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

7.查询执行顺序

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;