sql的学习（having,group by, sq的执行顺序，自定义group by）

1. having与sum的区别？

having:对上面已经分组的数据进行过滤的条件  （对分组后的数据进行统计）

2. group by all?

 

3. group by

group by的话 会把第一条遇到的数据，作为展示进行筛选出来。

group by entity_id;把相同entity_id重复的合并

常用的意义：

 

4.聚合函数first？

 

5. count(1)的意义

 

6.什么叫做聚合函数？

 

7. 测试例子，自定义group by 的规则

CREATE TABLE `user_table` (
  `id` int(11) NOT NULL,
  `user_name` varchar(32) DEFAULT NULL,
  `pass_word` varchar(64) DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `activated` tinyint(4) DEFAULT NULL,
  `num` varchar(151) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='            ';

-- ----------------------------
-- Records of user_table
-- ----------------------------
INSERT INTO `user_table` VALUES ('1', 'dream', '123', '1@qq.com', null, '1', '10');
INSERT INTO `user_table` VALUES ('2', 'dream1', '1234', '123@qq.com', '2019-08-05 15:53:15', '0', '11');
INSERT INTO `user_table` VALUES ('3', 'peter', 'abc', 'peter1@qq.com', '2019-08-05 15:53:15', '0', '50');
INSERT INTO `user_table` VALUES ('4', 'peter1', 'abcd', 'peter2@qq.com', '2019-08-05 15:53:15', '0', '100');

 

SELECT
	user_name,
	email,
	count(1) AS 数量
FROM
	`user_table`
GROUP BY
	user_name
HAVING
	数量 = 2;

SELECT
	CASE
WHEN `user_name` IN ('dream', 'dream1') THEN
	'all_dream'
ELSE
	`user_name`
END AS `names`,
 SUM(num)
FROM
	user_table
GROUP BY
	`names`;

SELECT
	CASE
WHEN `user_name` IN ('dream', 'dream1') THEN
	'all_dream'
ELSE
	`user_name`
END AS `names`,
 SUM(num)
FROM
	user_table
GROUP BY
	CASE
WHEN `user_name` IN ('dream', 'dream1') THEN
	'all_dream'
ELSE
	`user_name`
END;

比如按照自定义规则进行分组，把两个名字或者多个属性合成不同的属性，最后在展示！！

8. sql的执行顺序和mysql的执行顺序

查询语句中select from where group by having order by的执行顺序

查询中用到的关键词主要包含六个，并且他们的顺序依次为 

select--from--where--group by--having--order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序 

与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行 

from--where--group by--having--select--order by,  （最后进行order by）

Mysql执行顺序，即在执行时sql按照下面的顺序进行执行：

1. from  
2. on  
3. join  
4. where  
5. group by  
6. avg sum 等等
7. having  
8. select  
9. distinct  
10. union  
11. order by  

9. sql on操作

on也是过滤条件，

在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

也就是on用来 join表的时候使用。

10. order by + limit 混用陷阱

limit的目的是为了分页，

select * from 表名 order by 排序字段 limt M,N。但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下，会很容易出现排序结果与预期不一致的问题。

mysql的官方文档：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

如果你将Limit row_count与order by混用，mysql会找到排序的row_count行后立马返回，而不是排序整个查询结果再返回。

以上是常识！所以使用limiit其实是会更快

我自己试了一下！

这个是使用文件查询！！

如果你将Limit row_count与order by组合使用，mysql会停止分拣只要它从已经排序的结果集中找到了前row_count行，而不是分拣整个结果集。如果是通过索引分拣，会非常快；如果是文件分拣，所有匹配查询的行（不带Limit的）都会被选中，被选中的大多数或者全部会被分拣，直到limit要求的row_count被找到了。如果limit要求的row_count行一旦被找到，Mysql就不会分拣结果集中剩余的行了

但是那个博主发生了，分页不准的情况，因为创建的时间是一样的。

如果order by的字段有多个行都有相同的值，mysql是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。

所以用这种方式排序的结果行并不确定。

那这种情况应该怎么解决呢？

官方给出了解决方案：

如果想在Limit存在或不存在的情况下，都保证排序结果相同，可以额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。

就是ORDER BY create_time,id  再加上limit就行（加个主键！）

参考：https://www.cnblogs.com/thinkingthigh/p/7118343.html

 

11 。 mysql的执行计划是什么？

 

 

12. mysql的文件排序是什么？

 

13. mysql的分表操作，月表年表（数据量很大）

 

14. 索引的最左原则！

与where的条件顺序无关！

索引失效，与where条件的列是否存在有关：
select * from table where b='b' and 'c'

 select * from product where model = 'abc' order by code desc limit 100;
索引分析：
product表已存在mode和code两个单索引，MySQL引擎只会选择其中一个区分度高的索引查询，所以使用的是code的索引，model索引没用上，导致查询效率变慢。
借助联合索引(model, code)可以显著提高查询效率。

联合索引生效：
select * from product where model in ('abc') order by code desc limit 100;

联合索引失效：
select * from product where model in ('abc', 'def') order by code desc limit 100;

联合索引生效：
select * from product force index(model_code) where model in ('abc', 'def') order by code desc limit 100;

in的参数个数为1个，联合索引生效，大于1个，索引失效。所以使用了强制索引使联合索引生效。

第一、取决于B树的数据结构，单参数的IN只会得到一颗基于model子树，该子树的code本身是有序的，所以索引生效，查询效率高；多参数的IN会得到多颗基于model的子树，每颗子树的code字段是有序的，但是总体上可能不是有序的，所以索引失效，查询效率低。
第二、使用强制索引后，理论上无法保证order by的顺序，但是如果数据本身的特性，比如时间递增的这类数据，总体上还是有序的，笔者试过多中途径想要迫使强制索引得到错误的结果，结果都对了。强制索引需进一步研究。

作者：南风nanfeng
链接：https://www.jianshu.com/p/a162b5778ad1
来源：简书

15. limit作为分页的时候，数据量很大，那么优化？

limit rows 比如limit 5 默认偏移量是0，就是返回1 - 10行 

imit m,n    : 意思是跳过m个记录向后取n条数据。

SELECT * FROM `user_table`  limit 3 offset 1; =    SELECT * FROM `user_table`  limit 1,3;

SELECT * FROM `user_table`  limit 0,3;  =  SELECT * FROM `user_table`  limit 1,3;

上面两种都是有等价写法的，0开始和1开始其实都是一样的。

• limit偏移量较小时性能优秀，分页越到后面，偏移量递增，limit的性能会逐渐下降。

> select * from product limit 100;                             //cost time: 0.33s
> select * from product limit 10000, 100;                 //cost time: 0.37s
> select * from product limit 100000, 100;               //cost time: 0.61s
> select * from product limit 1000000, 100;             //cost time: 2.55s
> select * from product limit 10000000, 100;           //cost time: 20.41s

此时，通过子查询优化limit，效果如下：

> SELECT * FROM product WHERE ID >=(SELECT ID FROM product ORDER BY id LIMIT 100,1) LIMIT 100;                 //cost time: 0.37s
> SELECT * FROM product WHERE ID >=(SELECT ID FROM product ORDER BY id LIMIT 10000,1) LIMIT 100;                 //cost time: 0.34s
> SELECT * FROM product WHERE ID >=(SELECT ID FROM product ORDER BY id LIMIT 100000,1) LIMIT 100;                 //cost time: 0.46s
> SELECT * FROM product WHERE ID >=(SELECT ID FROM product ORDER BY id LIMIT 1000000,1) LIMIT 100;                 //cost time: 0.89s
> SELECT * FROM product WHERE ID >=(SELECT ID FROM product ORDER BY id LIMIT 10000000,1) LIMIT 100;                 //cost time: 4.73s

数据量机器打的时候，可以通过子查询先找到对应的Id，然后再向下找需要查询的所有数据。

作者：南风nanfeng
链接：https://www.jianshu.com/p/a162b5778ad1
来源：简书
 

 

TODO:动态数据源切换项目。