前言

--下文为个人在学习和工作中的简单总结，供个人参考用，如有问题和建议欢迎指正和指导！

正文索引

一、sql优化
二、触发器
三、窗口函数
四、存储过程通过参数生成动态表名
五、字段类型转换及日期格式函数
六、union和union all 的区别

一、sql优化

（1）整体思路

梳理sql逻辑，缩小查询范围，减少不必要的计算量；在where、group by、order by、join等语句涉及的字段设置索引，避免全表扫描，提升查询效率。

（2）创建表时

尽量不给数据库留NULL值，尽量使用NOT NULL进行填充
尽量使用varchar，nvarchar，代替char，nchar，节省字段存储空间

（3）写SQL脚本时

1.写明查询具体列，避免使用，表名过长时使用表的别名*
2.模糊查询like，后的关键字少用%；
3.尽量使用join，避免子查询；
4.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；
--使用短索引。如果在前N个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可--以提高查询速度而且可以节省磁盘空间和I/O操作
--索引列排序。mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
--因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。
--like语句操作。一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。
--不要在列上进行运算。
--不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
--索引要建立在经常进行select操作的字段上。
--索引要建立在值比较唯一的字段上。
--对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。
5.不走索引的语句如下
--where的查询条件里有不等号(where column != …),mysql将无法使用索引。
--如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。
--在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。
--在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
6.多用内部函数
7.where子句后少用！=，<>,not in 以及对字段进行 null 值判断，or 来连接条件；
8.很多时候用 exists 代替 in 是一个好的选择；
9.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引；
--如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
10.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率；
--一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
11.避免频繁创建和删除临时表，以减少系统表资源的消耗；
--临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

（4）性能调优时。定位慢查询

--long_qurry_time 设置慢查询阈值，过滤慢查询语句，便于查找慢查询；
--sql语句前+explain 查看sql脚本的处理流程，查看具体全表扫描语句；

二、触发器

触发器：某个表发生一个事件（增删改操作），然后自动的执行预先编译好的SQL语句，执行相关操作。
触发器事件跟触发器中的SQL语句是原子性的（要么同时执行，要么同时不执行），这样保证了数据的完整性。
是与表事件相关的特殊的存储过程，用于加强数据的完整性约束和业务规则

触发器语法：

drop trigger if exists 数据库名.触发器名
create trigger 触发器名
after/before  insert/delete/update  on 表名
for each row   #这句话在mysql是固定的  
begin
sql语句;
end;

查看触发器：show trigger from 数据库名;
删除触发器：drop trigger if exists 数据库名.触发器名

三、sql窗口函数：

--mysql 8.0 新增SQL语法对窗口函数和CTE的支持;
--在MSSQL和Oracle以及PostgreSQL完整支持窗口函数
常用的窗口函数：
--专用窗口函数--row_number()，rank()，dense_rank()，NTILE()，PERCENT_RANK()。
--在MSSQL和Oracle以及PostgreSQL，使用的语法和表达的逻辑，基本上完全一致。
窗口函数大体可以分为以下两种：
1.能够作为窗口函数的聚合函数（sum，avg，count，max，min）
2.rank，dense_rank。row_number等专用窗口函数。
语法格式：

---<窗口函数名> over (partition by <列清单> order by <列清单> )

---partition by不是窗口函数所必须的
---窗口函数的适用范围：只能在select子句中使用
专用函数的种类：
1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
2.dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
3.row_number函数：赋予唯一的连续位次。
4.取同一字段后n个的值函数 lead（arg1，n） arg1表示列名，arg2表示向后行偏移量，默认为1。当找不到值时返回null 。
5.取同一字段前n个的值函数 lag（arg1，arg2）arg1表示列名，arg2表示向前行偏移量，默认为1。当找不到值时返回null 。

--lead('字段1',1) over(partition by '字段' order by '字段')
#lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
#lag ，lead 分别是向前，向后；
#lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）

四、存储过程通过参数生成动态表名

DELIMITER//
DROP PROCEDURE IF EXISTS `根据参数变化的动态表名`;
CREATE PROCEDURE `根据参数变化的动态表名`(IN date1 date)
BEGIN
    SET @date1_0 = DATE_FORMAT(date1,'%Y%m%d');
    SET @table_name = CONCAT(@date1_0,'_用户订购记录');
    SET @sql1_0 = CONCAT('DROP TABLE IF EXISTS ',@table_name,";");
    SET @sql1 = CONCAT("create table ",@table_name," 
                                        SELECT * FROM `用户订购记录` WHERE `订购日期` =",date1,';');
    PREPARE sql1_0 FROM  @sql1_0;
    PREPARE sql1 FROM  @sql1;
    EXECUTE sql1_0;
    EXECUTE sql1;
    DEALLOCATE PREPARE sql1_0;
    DEALLOCATE PREPARE sql1;
END//
DELIMITER;
--------------------------------------------------------------
CALL `根据参数变化的动态表名`('2019-07-26')；

五、字段类型转换及日期格式函数

mysql类型转换：
--CAST(value as type);
--CONVERT(value, type);

REPLACE(str,from_str,to_str)

DATE_FORMAT(date,format)
--%Y :4位，年
--%y :2位，年
--%M ：英文，月名
--%m ：数值（00：12），月
--%D ：带有英文前缀的月中的天
--%d ：月的天，数值(00-31)
--%e ：月的天，数值(0-31)

set @d = now();  #获取现在时间
set @t = CURRENT_DATE();   #获取当前日期
set @t1=DATE_ADD(@t,INTERVAL -7 DAY);       #获取上周同期
set @t2=DATE_ADD(@t,INTERVAL -day(@t)+1 day);       #获取当月第一天
set @t2_1=date_add(@t2,interval 1 month);       #获取下月同期
set @t3=DATEDIFF(@t2_1,@t2);        #获取当月天数
SELECT @d,@t,@t1,@t2,@t3;

六、union和union all 的区别

1.union去重并排序，union all直接返回合并的结果，不去重也不排序；
2.union all比union性能好；

工作学习中的一些sql总结