MYSQL基本查询语句

MYSQL练习网站自学SQL网(教程 视频 练习全套) (xuesql.cn)

SELECT查询语句：

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

Operator（关键字）	Condition（意思）	SQL Example(例子）
=, !=, < <=, >, >=	Standard numerical operators 基础的 大于，等于等比较	col_name != 4
BETWEEN … AND …	Number is within range of two values (inclusive) 在两个数之间	col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …	Number is not within range of two values (inclusive) 不在两个数之间	col_name NOT BETWEEN 1 AND 10
IN (…)	Number exists in a list 在一个列表	col_name IN (2, 4, 6)
NOT IN (…)	Number does not exist in a list 不在一个列表	col_name NOT IN (1, 3, 5)

字符串操作

Operator（操作符）	Condition（解释）	Example（例子）
=	Case sensitive exact string comparison (notice the single equals)完全等于	col_name = "abc"
!= or <>	Case sensitive exact string inequality comparison 不等于	col_name != "abcd"
LIKE	Case insensitive exact string comparison 没有用通配符等价于 =	col_name LIKE "ABC"
NOT LIKE	Case insensitive exact string inequality comparison 没有用通配符等价于 !=	col_name NOT LIKE "ABCD"
%	Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符	col_name LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS") "%AT%" 代表AT 前后可以有任意字符
_	Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符	col_name LIKE "AN_" (matches "AND", but not "AN")
IN (…)	String exists in a list 在列表	col_name IN ("A", "B", "C")
NOT IN (…)	String does not exist in a list 不在列表	col_name NOT IN ("D", "E", "F")

去重

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

结果排序（ordered results）

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

limited查询

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

用INNER JOIN 连接表的语法

SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
    ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条)
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

用LEFT/RIGHT/FULL JOINs 做多表查询 

SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

在查询条件中处理 NULL 

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

包含表达式的例子

SELECT  particle_speed / 2.0 AS half_particle_speed (对结果做了一个除2）
FROM physics_data
WHERE ABS(particle_position) * 10.0 >500
            （条件要求这个属性绝对值乘以10大于500）;

属性列和表取别名的例子 

SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

对全部结果数据做统计 

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所有行求和.

Docs: MySQL, Postgres, SQLite, 

用分组的方式统计

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

用HAVING进行筛选 

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

这才是完整的SELECT查询 

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

SELECT中的表达式已经执行完了。此时可以用AS别名.