文章目录
时间戳转日期
select *,FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i%s') as date from user where FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i%s') > curdate() group by date order by date desc;
上面的sql语句，把create_time字段格式化为日期，并且按格式话后的字段查找和排序。
细看可能会发现，group和order我都直接使用了格式化后的别名dete，而where查询则重新格式化了字段，这跟mysql的执行顺序有关。执行到where时，结果还没被查询出来，别名自然无法生效，需要注意下。
条件判断
①IFNULL
select IFNULL(score1,score2) from score;
如果score1是NUll，则返回score2；否则返回score1
②IF
select IF(sex,'男','女') from user;
第一个参数是条件。若为浮点数，认为是0，返回‘女’
第一个参数也可以写成比较式：sex=1
③聚合函数中条件判断
select count(case when age>10 then id end) from user;
上面sql语句查询年龄大于10的用户数量
中文字段排序
把数据表中的数据按中文字段值得拼音首字母排序，假设该字段为name
如果name的字符编码是latin_swedish_ci
select * from user order by birary(name) asc;
如果name的字符编码是utf8_general_ci
select * form user where 1 order by convert(name using gbk) collate gbk_chinese_ci asc;
按指定的顺序排序
select * form user where id in(1,2,3,4,5) order by field(id,5,4,3,2,1);
返回的值会根据id 5,4,3,2,1排序
拼接字段
select concat(id,name,'666') from user;
concat()中的参数，可以使字符串，也可以是数据表中的字段。当参数中的字段有一个为NULL时，则给行数据就为NULL了。若想避免这种情况，可以使用CONCAT_WS（）,用法略有不同
初始化自增id的起始值为1
alter table 表名 auto_increment=1;
group相关
①获取其他字段的预期值
使用group把指定字段相同的行合为一行后，除了group字段是我们预期的值，行里其他字段可能不是我们所预期的，group会把某一行的值作为聚合后行的值。
比如说我想要合并后，时间字段create_time的值是最大的那个，但很多时候我们不确定这个create_time是最大还是最小，这时候我们需要自己在select 时增加自己想要的字段，比如：
select *,count(id),MAX(create_time) as last_time from pay_list group by uid;
②获取某一列的所有值
使用group by 聚合函数后，符合条件的数据只有一行被保留，如果我们想获取某个字段的所有行的值，可以只用group_concat函数，它可以把聚合后指定列的值使用指定分隔符（默认为逗号）连接起来，语法如下
select count(id) as num,group_concat(DISTINCT name) as name_str group by class_id;
group_concat 函数
第一个参数：DISTINCT 去除重复值
第二个参数：要返回的字段
第三个参数：分隔符，不填默认是逗号
③开窗函数
group_concat虽然可以获取分组查询后，指定的列的所有值，但当想获取多个列的值是，需要多次调用group_concat，显然是比较麻烦的
mysql8.0引入了开窗函数 ，可以按组查询排列数据，且给每一行值都返回指定的聚合值，语法如下：
开窗函数 over([PARTITION BY 分组字段(多个用,链接)] [ORDER BY 排序字段(同前)])
开窗函数分为两类：
聚合开窗函数：sum,avg,count,max,min等
专用开窗函数：Rank,Dense_Rank,Row_Number 等
注意：聚合开窗函数使用ORDER BY时，必须跟在PARTITION BY之后。不能单独使用
详细教程可参看：Mysql开窗函数
mysql的一些简单的运行原理
① mysql执行查询语句时，关于为where条件的执行顺序，看网上很多说是从左到右顺序执行的，因此在查询数据量较大的表时，最好把能排除最多无效数据的条件放在最左（就算不对，也没啥损失）。
② 关于mysql in 条件的运行原理
select * from a where id in(id_str);
该语句的执行顺序类似于以下过程。
$a = select * from a;
$b = id_str;
$result = [];
foreach($a as $v){
foreach($b as $vv){
if($v['id'] == $vv){
$resulr[] = $v;
}
}
}
当where 需要多个in条件，且条件之间有关联时
比如我们想筛选：北京，年龄在21,22 ；上海，年龄在22，23的人员
传统的写法：select * from A where address in (‘北京’,‘上海’) and age in (21,22,23); 是不对的，会筛选出：上海，年龄21 的无效数据
我们可以把上面语句改为：select * from A where (address ,age) in ((‘北京’,‘21’),(‘北京’,‘22’),(‘上海’,‘22’),(‘上海’,‘23’),);
千万级数据count（）
数据较多时使用count查询数量，即使有索引，也要几十秒时间，效率太低。网上看到可以使用mysql的 explain 函数获取行数。
这个函数是专门用于查看sql语句的执行效率的，网上可供参考的文章很多。
定义： explain 命令速度很快，因为 explain 用并不真正执行查询，而是查询优化器【估算】的行数。
我们使用explain之后，会看到返回很多参数
取rows即行数
百万级数据分页优化
其实不到百万级数据，我的数据表才不到40万条数据，在联表情况下取最后面的分页数据时，速度达到了3秒左右。最后发现问题出现在order by 上。
可以看到，使用order by 导致查询速度慢了10倍。
使用order by 时，mysql使用了Using filesort，表示在索引之外,需要额外进行外部的排序动作。aid是主键索引，也没有用到。
搜索之后知道，想要在order by的字段使用索引，select的字段必须和order by保持一致
这种情况下，我们查询全部数据期并且使用order by排序就变的不可行了，因为我们不可能把需要的列都加上索引。这个时候我可以先只查询主键id，获取到某一页的数据后，使用in查询，查询全部数据。
select * from ask_question where aid in (select t.aid from (select aid from ask_question limit 30000,10) t);
可以看到，使用了双重子查询。这是因为mysql中规定在子查询中不能使用limit，但是在字查询中再嵌套一层子查询就可以了。
数据库字段长度设置
数据库数值型字段，选择完字段类型后，长度指定或不指定对插入结果没有影响。
比如，
设置type字段类型为tinyint，tinyint的2进制长度为8位，值的范围在-128到127之间。mysql中设置type长度为1（即1字节），但仍然可以插入-128到127之间的值（选无符号时，范围为0到255），长度在这里没有约束作用，只起到一个注释的作用。
但是当，字段是字符串类型时，字段长度的设置是有约束作用的，vachar设置5，该字段就最多只能插入5个字符。
关于order by desc limit 1的bug
我们首先看一条sql的运行状态
使用索引username查询指定数据，并排序，执行时间是0.051秒
然后，我们取其中的一条数据，sql运行结果如下
我们看到，当加了limit 1 之后，使用的索引变为主键，表的扫描类型也变为index（全表索引扫描），本来查询更少的数据，执行速度时间不但不减，反而执行速度下降了将近10倍。为什么会出新这种情况呢？
查看其他博主的回答后，发现是mysql优化器的问题
由于我们使用了 order by id asc 这种基于 id 的排序写法，优化器认为排序是个昂贵的操作，所以为了避免排序，并且它认为 limit n 的 n 如果很小的话即使使用全表扫描也能很快执行完，这样使用全表扫描也就避免了 id 的排序（全表扫描其实也就是基于 id 主键的聚簇索引的扫描，本身就是基于 id 排好序的）
作者：码海
链接：https://juejin.cn/post/7012093121907392519
然而执行结果我们也看到了，优化器的这一判断导致执行效率几何倍的下降。
我测试时使用的是MDB10.1.9，这个问题官方仍没有优化
如何解决呢，我们可以通过强制指定索引，如下
或者加大limit的数量，经测试limit 40 以上，就不会出现该问题
查询字段中的json字符串
当字段保存的是json字符串，我们需要直接使用json中的某个字段，或者以json中的某个字段为条件查询时，可以使用JSON_EXTRACT函数进行查询
select user_id,replace(JSON_EXTRACT(pre_sale_info,'$.step2StartTime'),'"','') s2 from cp2_register_log
where pay_status=3 and status=0
and replace(JSON_EXTRACT(pre_sale_info,'$.step2StartTime'),'"','')>=1638633600 GROUP BY user_id
在把json当查询条件时，最好放在最后，前面的条件先确保查询出的内容中json字段不为空，否则报错
把不同结构的两个表的全部数据，做分页查询
需求：项目迭代过程中，新建了订单表，数据结构与原订单表不一致。现在需要把用户的新老订单都展示出来，并按时间排序、分页
使用视图可解决这个问题，基本思路如下
#第一步：从老订单表中，抽出几个关键字段建立一个视图
create or replace view order1_for_union_order (order_id,pay_status,`status`,created_at) as
select c.order_sn,c.pay_status,c.`status`,c.created_at from order1 c
left join cp2_assess a on a.id = c.assess_id
where a.type = 2 group by c.order_sn
select * from order1_for_union_order
#第二部：从新订单表中，抽出几个关键字段建立一个视图，两个视图的字段保持一致
create or replace view cp5_for_union_order (order_id,pay_status,`status`,created_at) as
select id,pay_status,`status`,created_at from cp5_goods_order
select * from cp5_for_union_order
#第三步 合并两个视图到一个新的视图
create or REPLACE VIEW cp2_and_cp5_order as
select * from cp2_for_union_order
union all
select * from cp5_for_union_order
select * from cp2_and_cp5_order order by created_at desc
这样就可以从视图中进行主要数据的分页和基础查询
然后根据查询出的数据，再进一步查询订单各自的信息
联表更新
需求：A表中的a字段等于B表中的a字段时，把A表中的b字段，更新为B表中的b字段
update A,B set A.b=B.b where A.a = B.a
除了关联字段的比较条件外，也可以添加其他筛选条件，
如下,表示在a字段相等，且A中b字段不为空时，才把A中b字段更新为B中b字段
update A,B set A.b=B.b where A.a = B.a and A.b<>“”
扫一扫
1470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
