MySQL select语法（一）：一般查询

MySQL 查询语法会涉及：一般查询语句，JOIN联接查询，UNION联合查询，括号查询表达式

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}


table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

index_hint: {
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
}

index_list:
    index_name [, index_name] ...

1、select_expr 表示希望获取的列，至少要有一个 select_expr
2、table_references 表示从哪些表中获取数据行
3、可以使用 PARTITION 从句指定分区列表，所查到的数据都是这些分区中的数据
4、WHERE 从句给出过滤条件，确定要查询的行。如果没有条件where，则查询所有行。where_condition 是一个表达式，可以使用MySQL支持的除聚合函数外的所有函数和操作符。
5、select 语句可以不引用任何表

mysql> SELECT 1 + 1;
        -> 2

# 为了方便使用FROM 从句获取其他从句而不实际引用表，查询语句允许使用虚拟表名 DUAL 来替代表名
mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

6、各个从句是有一定顺序的，如：HAVING 语句必须在 GROUP BY 后面，ORDER BY 在 HAVING 后面。INTO 语句可以出现在多个位置，但只能出现一次。
7、select_expr 可以是指定的列名，表达式，以及 *
- 1）select_expr 仅仅包含 * 表示查询所有表的所有列
- 2）tbl_name.* 表示查询该表的所有列
- 3）如果表包含隐藏的列 * 和 tbl_name.* 并不包含这些列，需要明确指出列名
- 4）如果在有其他项的时候，使用 * ，会发生解析错误，为避免这个问题，需用 tbl_name.*

# t1 t2 的全部列
SELECT * FROM t1 INNER JOIN t2 ...

#明确指出 t1 t2 的全部列
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

# 有聚合函数，不能单纯使用 *
SELECT AVG(score), t1.* FROM t1 ...

6、select_expr 可以使用别名： AS alias_name，这别名可以用在 GROUP BY, ORDER BY, or HAVING 中，且关键字 AS 是可选的。在 WHERE 从句中不要使用列的别名，因为条件执行时，别名的值可能还没有确定下来。

SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;

SELECT CONCAT(last_name,', ',first_name) full_name
  FROM mytable ORDER BY full_name;

# 注意 逗号，有逗号就是两列，没有逗号就是别名
SELECT columna, columnb FROM mytable;

7、FROM table_references 从句指定了数据表，如果不止一个表，那么是一个连接查询。每个表都可以指定别名。使用索引提示可以为优化器提供在查询中如何选择索引的信息。

tbl_name [[AS] alias] [index_hint]

8、表名也可以使用别名： tbl_name AS alias_name / tbl_name alias_name

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;

9、在ORDER BY 和 GROUP BY 从句中可以使用 SELECT 查询的列名、列的别名、列的位置（从1开始的整数）。使用整数位置的方式已经过时了。
ORDER BY 默认是 ASC（升序排列）。排序会使用系统变量 max_sort_length 的值，该值表示最多比较多少个字节的字符。

# 先按 region 升序排，当region相同时，按 seed 降序排
SELECT college, region, seed FROM tournament
  ORDER BY region ASC, seed DESC;

# 结果同上
SELECT college, region AS r, seed AS s FROM tournament
  ORDER BY r, s DESC;

# 都是升序排
SELECT college, region, seed FROM tournament
  ORDER BY 2, 3;

10、HAVING 从句和 WHERE 类似都是指定条件的，但WHERE 不能使用聚合函数，HAVING可以。一般HAVING 都是跟在 GROUP BY 后面。

SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;

11、WINDOW 从句，定义命名的窗口，用于 window functions。
12、在 ORDER BY 从句中，MySQL解析未修饰的列，先是从 select_expr 中查找，然后在 FROM 从句后的表中查找。而 GROUP BY 或 HAVING 从句是先从 FROM 从句的表中查找，然后再从 select_expr 中查找。
13、LIMIT 从句是用来限制返回数据行的，有两个参数，都是非负整数。
- 1）在预处理语句中，可以使用问号 ? 作为占位符
- 2）在存储过程中，可以使用整数的路由参数或本地变量。
- 3）两个参数，第一个是返回行的第一行的偏移数（从0 开始），第二参数是可以返回数据最大行数。如果只有一个参数，则表示偏移量为0

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

# 如果要返回 某个偏移量后的所有行，第二个参数可以是一个很大的数
SELECT * FROM tbl LIMIT 95,18446744073709551615;

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

# 预处理语句
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;

14、 FOR UPDATE，FOR SHARE， NOWAIT
- 1）在使用页或行锁的存储引擎（如，InnoDB）中使用 FOR UPDATE，会在查询过程中，给所要查找的行加上锁，直到当前事务结束。
- 2）FOR SHARE 和 LOCK IN SHARE MODE 可以分享锁，允许其他事务读取被查询的行，但不能更新和删除。
- 3）FOR UPDATE 和 FOR SHARE 都支持 NOWAIT, SKIP LOCKED, 以及 OF tbl_name 选项。LOCK IN SHARE MODE 和 FOR SHARE 是一个意思，但不支持 NOWAIT, SKIP LOCKED, 以及 OF tbl_name 选项
- 4）NOWAIT 可以让 FOR UPDATE，FOR SHARE 查询立即执行，如果查询结果有被另一个事务上了锁的，会返回错误。
- 5）SKIP LOCKED可以让 FOR UPDATE，FOR SHARE 查询立即执行，如果查询结果有被另一个事务上了锁的，会排除这些上锁的结果行。
- 6）NOWAIT and SKIP LOCKED 选项对于基于语句的复制集是不安全的
- 7）OF tbl_name 选项是用来为 FOR UPDATE and FOR SHARE 查询指定表名的。如果省略了 OF tbl_name 选项，那么在查询中所有被引用的表都会加上锁。给已经上锁的表，在上锁会报错。
- 8）如果语句中给表指定了别名，上锁语句只会给别名上锁；相反，如果没有指定表的别名，那么会给实际的表名上锁。

SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;

15、在关键字 SELECT 之后，可以跟一些修饰符
- 1）ALL 默认的修饰符，所有符合条件的行都会返回，包括重复的行
- 2）DISTINCT 移除重复出现的行（即只保留一个）。ALL 和 DISTINCT 只能出现其中一个。DISTINCTROW 和 DISTINCT 一样的作用
- 3）HIGH_PRIORITY 将 SELECT 语句的优先级设置得高于 UPDATE 语句。查询的优先级比更新的高，这会使得表锁住，而更新语句处于等待状态。该修饰符只在使用表级锁的存储引擎（如 MyISAM, MEMORY, and MERGE）起作用，且不能在 UNION 中使用。
- 4）STRAIGHT_JOIN 可以使MySQL优化器将在 FROM 后面的表按顺序连接，可以加速查询。该修饰符也可用在 table_references 中。
- 5） SQL_BIG_RESULT 和 SQL_SMALL_RESULT 可以用在 GROUP BY 或 DISTINCT 语句中，告诉优化器查询结会很大或很小。对于 SQL_BIG_RESULT ，如果有临时表的话，会使用基于硬盘的临时表用于排序。对于SQL_SMALL_RESULT会使用基于内存的临时表来存储结果，而不是排序。
- 6） SQL_BUFFER_RESULT 会强制将结果存储在临时表中，有利于MySQL更早地释放表的锁，也会在需要很长时间来发送结果给客户端的情况下有所帮助。该修饰符只能在顶级的 SELECT 语句中使用，而不能在子查询或 UNION 中使用。
- 7）SQL_CALC_FOUND_ROWS 会让 MySQL 计算结果集会有多少行，会无视 LIMIT 从句。行数可以通过 SELECT FOUND_ROWS() 函数来获取。
- 8） SQL_CACHE and SQL_NO_CACHE 是在 MySQL8.0 之前的版本中的，SQL_CACHE 已经移除，SQL_NO_CACHE 已废弃，且不起作用了。

https://dev.mysql.com/doc/refman/8.0/en/select.html
https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_sort_length
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html
https://dev.mysql.com/doc/refman/8.0/en/join.html

posted @ 2022-02-23 17:00 zhanglw 阅读(384) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

zhanglw

MySQL select语法（一）：一般查询

公告