【MySQL】官网文档学习之查询语句sql注意事项

前言

MySQL5.7 SELECT 文档。

• 测试数据脚本

CREATE TABLE `class` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `james`.`class` (`id`, `name`) VALUES (1, '一班');
INSERT INTO `james`.`class` (`id`, `name`) VALUES (2, '二班');
INSERT INTO `james`.`class` (`id`, `name`) VALUES (3, '三班');

INSERT INTO `james`.`student` (`id`, `name`, `age`, `score`) VALUES (1, 'james', 18, 60);
INSERT INTO `james`.`student` (`id`, `name`, `age`, `score`) VALUES (2, 'tom', 18, 80);
INSERT INTO `james`.`student` (`id`, `name`, `age`, `score`) VALUES (3, 'jerry', 20, 40);
INSERT INTO `james`.`student` (`id`, `name`, `age`, `score`) VALUES (4, 'candy', 16, 70);
INSERT INTO `james`.`student` (`id`, `name`, `age`, `score`) VALUES (5, 'kobe', 20, 60);

查询语句

可禁用group by的默认排序（减少排序开销）

原文： If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL:

释义：使用group by 时，默认会对group by的列排序（如上图图二，按age正序排列）。如果要取消这种默认行为，可以加上order by null。

select * from student group by age order by null;

在selete中使用order by group by排序的时候不要忽略 max_sort_length

原文： When you use ORDER BY or GROUP BY to sort a column in a SELECT, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable.

释义：使用order by或group by排序的时候，字段中用于排序的长度是小于全局变量max_sort_length 规定的字节数（单位为bytes）。

show variables like 'max_sort_length';

group by [ ] with rollup 带出统计信息

HAVING 的注意事项

原文：The HAVING clause, like the WHERE clause, specifies selection conditions. The WHERE clause specifies conditions on columns in the select list, but cannot refer to aggregate functions. The HAVING clause specifies conditions on groups, typically formed by the GROUP BY clause. The query result includes only groups satisfying the HAVING conditions. (If no GROUP BY is present, all rows implicitly form a single aggregate group.)

释义（主要是理解）：

• where 是对返回的结果集进行筛选，言下之意是：where在select 语句执行之前执行，如果存在group by 语句，where只能看到分组后的结果集，无法读到分组前中各组的详细内容。
• having 是对分组后的结果集进行过滤，言下之意是：having可以用聚合函数写条件，也可以不用聚合函数写条件（相当于where的功能，但是会引起歧义）。

-- 学生按年龄分组，并且只要平均分大于50的组
select age from student group by age having avg(score) > 50;

• 由于as操作在where执行之后，同样的需求无法用以下语句实现

select age, avg(score) as b from student group by age where b > 50; // 错误

• where 本身于聚合函数无关，所以也无法用聚合函数作为语句

select age from student group by age where avg(score) > 50; // 错误

• having 不跟 group by 一起用，可以执行，此时若having后不使用聚合函数，语义与where一致，建议改成where.

select * from student having score > 50; // 不妥
 -- 修改成where
select * from student where score > 50; 

• having 不跟 group by 一起用，若此时having使用聚合函数，视为所有结果集为一组；
  

limit 注意两个参数的语义

• 获取前n行记录

select * from student limit 3;

• 从偏移量为3的记录开始，向后取1条记录
  第一行的偏移量为0，所以是从id=4的值开始取
  

显式指定索引 index hints

MySQL5.7 index hints 用于声明查询使用的索引（不一定被查询优化器采用）

SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

原文： Index hints apply to SELECT and UPDATE statements. They also work with multi-table DELETE statements, but not with single-table DELETE,
释义：index hints的语法可以用在select 和update上，当且仅当多表参与时（子查询或连接）时才能用在delete上

不要使用列下标

• group by / order by 语句中可以使用别名，甚至可以使用下标，值得一提的是列的下标从1 开始, 但是不要这么做

SELECT college, region, seed FROM tournament ORDER BY 2, 3;

原文： Use of column positions is deprecated because the syntax has been removed from the SQL standard.

释义：使用下标的用法已经被sql标准删除