日常问题之收集:SQL篇(持续更新)

2020/01/01 posted in  FAQs
  • 建表规范(2020-06-06)

    按照日期汇总(2020-06-30)

    select count(1), left(time, 10) from <table>
    where time > '2020-06-01'
    group by left(time, 10);
    

    group by order by 同时使用(2020-07-06)

    执行过程:先 group by 之后,每组取出第一个,然后再 order by

    (1)如果想要在每组内进行排序然后取每组的第一个,可以先通过子查询排好序,再对子查询的结果进行 group by order by 就能得到正确的结果。

    (2)如果表A和表B连表并且A和B是一对多的关系,这是如果需求是按照A表排序取一个,那么 group by A.id order by A.id 就可以直接写,因为此时不是组内排序,而是组外排序,组内多个记录 A 相关的字段值都是一样,此时取任何一个都可以,取完再排序。取完A的值之后,再用 A.id 取B表相关的记录即可。

    mysql replace 语法(2020-06-17)

    实例:把'病假' 替换为 '--':

    UPDATE users SET username=REPLACE(username,'病假','--')  WHERE username LIKE '%病假%';
    

    max() 对 date 类型是否可以?(2020-07-03)

    可以

    LIMIT 写法(2020-06-06)

    • LIMIT 5,取5条数据
    • LIMIT 1,5,从位置1开始取5条数据

    连表去重计算总数(2020-06-06)

    SELECT COUNT(DISTINCT t1.id)
    FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
    WHERE ...
    

    重置自增列初始id(2020-06-06)

    ALTER TABLE table_name auto_increment= 1;
    

    建表规范(2020-06-06)

    • 状态字段

      • status, 1-启用/正常,-1-禁用,-2-删除
    • 链接字段

      • 如果只有一个图片,而是是七牛上传的图片,那么可使用 varchar(64)
      • 第三方链接,可使用 varchar(255)
    • text 类型不设默认值

    • 更新字段:

      • updated_at timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP not null comment '更新时间'
    • 创建字段:

      • created_at timestamp default CURRENT_TIMESTAMP not null comment '创建日期'
    • 示例建表SQL

    create table <table-name>
    (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
        `created_at` timestamp default CURRENT_TIMESTAMP not null comment '创建日期'
        `updated_at` timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP not null comment '更新时间'
        primary key (`id`)
    )