• 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

    行转列(使用笛卡尔积)

    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

    unionunion 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_idvarchar(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

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

    个人公众号:ApocalypseNow。分享互联网数据分析行业经验。
  • 相关阅读:
    poj 1743 Musical Theme 后缀数组
    poj 1743 Musical Theme 后缀数组
    cf 432D Prefixes and Suffixes kmp
    cf 432D Prefixes and Suffixes kmp
    hdu Data Structure? 线段树
    关于position和anchorPoint之间的关系
    ios POST 信息
    CALayers的代码示例
    CALayers详解
    ios中得sqlite使用基础
  • 原文地址:https://www.cnblogs.com/malcolmwallace/p/13848256.html
Copyright © 2020-2023  润新知