MySQL技巧总结

MySQL 查询结果带行号

select a,
       @i := @i + 1 as b
from (select 'x' as a
      union all
      select 'y' as a
      union all
      select 'z' as a) a,
     (select @i := 0) b

a	b
x	1
y	2
z	3

行转列

2. Based on following score_raw table, transform into three columns name, subject_name, subject_score using sql, and name the table final_score

name Maths Physics Chemistry English Mandarin History
A 67 78 85 63 35 67
B 89 28 68 85 88 86
C 47 97 36 88 68 57
D 79 70 37 87 97 88
E 90 68 82 80 85 96
F 37 85 94 66 75 87

Select * from (
SELECT name, 'Maths' AS subject_name, Maths AS subject_score FROM score_raw
UNION ALL
SELECT name, 'Physics' AS subject_name, Physics AS subject_score FROM score_raw
UNION ALL
SELECT name, 'Chemistry' AS subject_name, Chemistry AS subject_score FROM score_raw
UNION ALL
SELECT name, 'English' AS subject_name, English AS subject_score FROM score_raw
UNION ALL
SELECT name, 'Mandarin' AS subject_name, Mandarin AS subject_score FROM score_raw
UNION ALL
SELECT name, 'History' AS subject_name, History AS subject_score FROM score_raw;
) as final_score;

行转列(使用笛卡尔积)

select  label,
    case when 
    label = '减少此类作品数' then sum(share_count)
    when label = '分享数'   then sum(negative_count)
    end as value
from 
(
    select sum(share_count) as share_count
         , sum(negative_count) as negative_count
    from makepolo.creative_report 
    where date = '2020-12-06'
) a , 
(
    select '减少此类作品数' as label
    union all 
    select  '分享数' as label
) b 
group by 1

为数据增加合计行(使用笛卡尔积)

-- 昨日绑定账户数
select b.label vendor_id
     , sum(bind_acct) bind_acct
from (
       select vendor_id
          , count(distinct id) bind_acct
     from makepolo.entity_vendor_account
     where create_time >=  '2021-03-08 00:00:00' 
       and create_time <= '2021-03-08 23:59:59' 
     group by 1
) a, (
     select  '全部' as label
     union all
     select 1 as label
     union all
     select 2 as label
     union all
     select 4 as label
    ) b
where label = a.vendor_id or label = '全部' -- 笛卡尔积乘开后，这里对原有数据保留正确行，同时并上合计行
group by 1

随机抽样10行

select * from makepolo.material_report
order by rand(212)
limit 10

删除表

• 当你不再需要该表时， 用 drop table;

• 当你仍要保留该表，但要删除所有记录时， 用 truncate;

• 当你要删除部分记录时（always with a WHERE clause), 用 delete.

时间相关函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date 的UNIX 时间戳
FROM_UNIXTIME	返回UNIX 时间戳的日期值
WEEK(date)	返回日期date 为一年中的第几周
YEAR(date)	返回日期date 的年份
HOUR(time)	返回time 的小时值
MINUTE(time)	返回time 的分钟值
MONTHNAME(date)	返回date 的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt 格式化日期date 值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr 和结束时间expr2 之间的天数

date_add() -- 向日期添加指定的时间间隔。

date_add(date,INTERVAL expr type)

date_sub() -- 从日期减去指定的时间间隔。

date_sub(date,INTERVAL expr type)

datediff() -- 返回两个日期之间的天数。
 
datediff(date1, date2)

date_format() -- 用于以不同的格式显示日期/时间数据。
 
date_format(date,format)

select curdate();
# 2020-12-21

select current_date();
# 2020-12-21

select current_time();
# 10:36:32

select date('2020-12-02 23:25:11')
# 2020-12-02

-- 通过日期计算所在的季度
select floor((date_format(curdate(), '%m') +2)/3)) as quartly
# 或
select floor((month(curdate()) +2)/3)) as quartly

join

• 使用left join的时候左表 如果过大，会造成运行缓慢。可以改用join，看查询结果是否一致。总之左表尽量小。

• where 子句不要把函数写在运算符左边

• 使用索引提升查询速度

union

union比union all的执行效率差一些。union是将union all后的结果进行一次distinct，去除重复记录后的结果。

设置唯一键

alter table rpt_fancy.mkpl_creative add unique key (dat, vendor_id, company_id);

• 当一个属性声明为主键时，它将不接受NULL值。另一方面，当声明为Unique的属性时，它可以接受一个NULL值。

• 表中只能有一个主键，但可以有多个唯一键。

• 定义主键时自动创建聚簇索引。相反，Unique键生成非聚集索引。

通过这种方法根据唯一键插入表：

insert into mkpl_creative (dat, vendor_id, company_id, ad_cnt_api)
select * from mkpl_creative_ad_cnt
on duplicate key update ad_cnt_api = values (ad_cnt_api)

修改字段为not null

alter table rpt_fancy.mkpl_creative modify dat varchar(255) not null comment '日期';
alter table rpt_fancy.mkpl_creative modify vendor_id int not null comment '媒体';
alter table rpt_fancy.mkpl_creative modify company_id int not null comment '客户id';

doris设置为货币格式

• 加入千分符

select split_part(money_format(18796355.668) , '.', 1); -- 不保留小数部分
# 18,796,355

select money_format(18796355.668); -- 按惯例保留2位小数
# 18,796,355.67

Having子句的使用

• having子句不仅能用在通过group by聚合后的结果上，还能够使用在对任何select后字段计算的数据筛选中。

• 但是having子句似乎不能过滤窗口函数的结果(普遍性没有得到验证)

字符串处理

locate()函数判断一个字符串在另一个字符串中出现的位置，如果没有出现返回0.

select locate('bar', 'foobar');
#> 4

select locate('bar', 'foooobar');
#> 6

select locate('qtt', 'foobar');
#> 0

统计逗号分隔字段元素的个数

写SQL的时候会遇到如下的问题，统计如下表中project_id字段中id的个数。

company_id	project_id
77	94882,214880,94881,154882,94871,94879
140	2890,2872,3178,4314,4976
6	2173,5101,274884
6	4186,4192,4193
109	214899,94919,94920
305	5000,4999,5011
32	4514,5024,5262
49	1009,1008,1379

注意project_id是varchar(255)类型的，我们并没有现成的方法统计这个形如list的字段元素(数据库并没有list这样的对象)，只能通过字符串处理的方式。其实规律很简单，我们只需要统计,的个数然后+1就可以了。那么如何求逗号个数呢？我们使用原字符串长度 与 替换了逗号后的字符串长度 相减即可，求字符串长度用char_length()函数。

select company_id
     , project_id
     , char_length(project_id) - char_length(replace(project_id,',','')) + 1 as tag_cnt
from makepolo.local_material_tag
order by 3 desc

company_id	project_id	tag_cnt
77	94882,214880,94881,154882,94871,94879	6
140	2890,2872,3178,4314,4976	5
6	2173,5101,274884	3
6	4186,4192,4193	3
109	214899,94919,94920	3
305	5000,4999,5011	3
32	4514,5024,5262	3
49	1009,1008,1379	3

可以看到，使用这个方法准确无误地计算出了逗号分隔字段元素的数量。