mysql笔记

在工作当中，多思考，多学习，多总结

SQL 的 select 语句完整的执行顺序

SQL 之聚合函数

聚合函数是对一组值进行计算并返回单一的值的函数，它经常与 select 语句中的 group by 子句一同使用。 a. avg():返回的是指定组中的平均值，空值被忽略。

b. count():返回的是指定组中的项目个数。

c. max():返回指定数据中的最大值。

d. min():返回指定数据中的最小值。

e. sum():返回指定数据的和，只能用于数字列，空值忽略。

f. group by():对数据进行分组，对执行完 group by之后的组进行聚合函数的运算，计算每一组的值。最后用 having 去掉不符合条件的组，having 子句中的每一个元素必须出现在 select 列表中(只针对于 mysql)。

union 和 union all

UNION用的比较多，union all是直接连接，取到得是所有值，记录可能有重复 union 是取唯一值，记录没有重复。

1、UNION 的语法如下：
     [SQL 语句 1]
      UNION
     [SQL 语句 2]

2、UNION ALL 的语法如下：
     [SQL 语句 1]
      UNION ALL
     [SQL 语句 2]
复制代码

效率： UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。

1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。

2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

从效率上说，UNION ALL要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

union 和 union all都可以将多个结果集合并，而不仅仅是两个，你可以将多个结果集串起来。注意：使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。但列名则不一定需要相同。

优化union查询

通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用，经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。

乐观锁-悲观锁

悲观锁，顾名思义，就是悲观，每次认为去取数据的时候别人都会修改，因此每次去取数据的时候都会进行上锁。也即是说，如果某个事务执行操作应用了锁，那么只有等这个事务释放了锁，其他事务才能操作该数据。

乐观锁，顾名思义，每次都很乐观，认为去取数据的时候，别人不会修改数据，只有当修改提交更新的时候，才会对数据进行冲突与否检测，当数据发生冲突时候，会返回用户错误信息，让用户决定如何去做。

乐观锁-悲观锁的应用

乐观锁两种常见的实现方式

乐观锁一般会使用版本号机制或CAS算法实现。

版本号机制

一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

ABA问题是乐观锁一个常见问题

ABA问题：如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 "ABA"问题。

mysql函数

concat()函数

将多个字符串连接成一个字符串。

concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
复制代码

concat_ws()函数

和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）

concat_ws(separator, str1, str2, ...)
复制代码

把分隔符指定为null，结果全部变成了null：

group_concat函数

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
复制代码

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

SELECT c.id companyId, ifnull(c.`location_type`,0) AS locationType,
 
                GROUP_CONCAT(c.`province_code` SEPARATOR ';') AS provinceCodes,
                GROUP_CONCAT(c.`city_code` SEPARATOR ';') AS cityCodes,
                GROUP_CONCAT(c.`district_code` SEPARATOR ';') AS districtCodes,
                GROUP_CONCAT(m.`hospital_id` SEPARATOR ';') AS hospitalIds
            FROM tob.company c
                LEFT JOIN tob.`company_hospital_management_relation` m ON c.id = m.company_id
            WHERE c.id = {companyId}
                AND c.`location_type` IS NOT NULL;
复制代码

group by order by子句

浅谈Group by

示例：全国医院数量分布按省(provice_code)分布：

select h.`province_code`,count(1) as count from dzj.`b_hospital` h group by h.`province_code`;

select count(*) as count,h.`province_code` from `dzj`.`b_hospital`
h group by h.`province_code` order by count desc limit 50;

复制代码

示例：签约医院分级饼图

SELECT h.level as level,count( * ) count FROM dzj.b_hospital h 
WHERE h.province_code = '610000'
or h.`city_code`='610100'
GROUP BY h.`level` ORDER BY count DESC;
复制代码

Group by count(1)出错

SELECT COUNT(1) AS totalCount
	, CASE 
		WHEN o.patient_gender = 'MALE' THEN o.patient_gender
		WHEN o.patient_gender = 'FEMALE' THEN o.patient_gender
		ELSE 'NULL' 
	END AS gender
FROM 表A o
WHERE o.online_academic_id = '719'
GROUP BY o.patient_gender;
复制代码

总结：group by count(patient_gender) 对NULL记录不统计 group by count(1) 对表的一行记录做统计，包括NULL记录统计

in子查询

select (select count(1) from doctor_info d inner join b_hospital h on d.hospital_id = h.id where h.city_code ='310100' and d.user_id 
in (select distinct consultant_id from health_consultant_relation))  healthyConsultantNum;
复制代码

优化

优化limit分页

最简单的办法是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。

offset会导致MySQL扫描大量不需要的行然后再抛弃掉，如果可以记录上次取数据的位置，下次就可以直接从该记录的位置开始扫描，可以避免使用offset
使用预先计算的汇总表，或者关联到一个冗余表。

优化group by 和 distinct

使用索引优化
当无法使用索引时，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组
尽可能的将WITH ROLLUP（超级聚合）功能移动应用程序中处理。

优化子查询

尽可能使用关联查询代替，如果使用MySQL5.6以上或MariaDB则可以忽略这个建议

优化关联查询

确保ON或者USING子句中的列上有索引
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列
当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方