美文网首页
mysql常用函数及语句笔记

mysql常用函数及语句笔记

作者: 编程放大镜 | 来源:发表于2022-04-27 18:02 被阅读0次

    mysql中group by分组后查询无数据补0

    MySQL对sum()字段进行条件筛选:having

    显示按日期汇总且内数量大于10的记录。
    SELECT date, count(*) as num FROM tbName GROUP BY date HAVING num>10;
    在这里,我们不能用where来筛选超过10的记录,因为表中不存在这样一条记录。相反,having子句可以让我们筛选成组后的各组数据

    count(if)

    SELECT count(distinct(user_id) , COUNT(if(status=2,true,null)) as succ_num, COUNT(if(status=20,true,null)) as refund_num FORM trade
    WHERE ctime>=unix_timestamp()-600;
    

    常用SQL语句

    # 创建表
    CREATE TABLE tb_test (
    id bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name varchar(100) NOT NULL DEFAULT '' COMMENT '名称',
    id_card varchar(40) NOT NULL DEFAULT '' COMMENT '身份证号',
    user_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
    create_time int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
    is_del tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否禁用',
    PRIMARY KEY (id),
    KEY idx_user (name,user_id),
    UNIQUE KEY `idx_id_card` (`id_card`),
    KEY `idx_created` (`create_time`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息';
    

    预估好数据将来的量,尽量往大理想,如使用 bigint 代替 int

    查询昨天

    from_unixtime(created, '%Y-%m-%d %H:%i:%s') >= adddate(curdate(), -1) and
    from_unixtime(created, '%Y-%m-%d %H:%i:%s') < curdate()

    复制表或数据结构

    create table t1_copy as select * from t1 where 1=2

    或者 create table t1_copy as select * from t1 limit 0

    缺少t1表(源表)的索引信息而只有表结构相同。(如果要复制数据,去掉limit 0即可,也就是查询到的所有数据复制)

    as 用来创建相同表结构并复制源表数据。
    like 用来创建完整表结构和全部索引。

    注意:两种方式在复制表的时候是不会复制权限对表的设置。比如说原本对表A做了权限设置,复制表AA后,表AA并不具备类似于表A的权限。

    格式化unix时间戳

    from_unixtime()
    unix_timestamp()
    

    建表语句

    CREATE TABLE `article` (
        `art_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '文章id(自增)',
        `art_excerpt` varchar(500) NOT NULL DEFAULT '' COMMENT '摘录',
        `art_title` varchar(500) NOT NULL DEFAULT '' COMMENT '标题',
        `art_slug` varchar(300) NOT NULL DEFAULT '' COMMENT '固定连接(伪静态地址)',
        `art_content` text NOT NULL COMMENT '内容',
        `art_author` varchar(50) NOT NULL DEFAULT '' COMMENT '对应作者',
        `art_author_id` int(11) NOT NULL DEFAULT '0' COMMENT '对应作者ID',
        `art_status` varchar(30) NOT NULL DEFAULT '' COMMENT '文章状态(publish/auto-draft/inherit等)',
        `art_cate` varchar(50) NOT NULL DEFAULT '' COMMENT '分类',
        `art_tag` varchar(60) NOT NULL DEFAULT '' COMMENT '标签',
        `art_created` int(10) NOT NULL DEFAULT '0' COMMENT '文章发布时间',
      PRIMARY KEY (`art_id`),
      KEY `idx_author_id` (`art_author_id`),
      KEY `idx_slug` (`art_slug`),
      KEY `idx_tag` (`art_tag`),
      KEY `idx_cate` (`art_cate`),
      KEY `idx_created` (`art_created`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='文章表';
    

    随机一条

    select id,name from user where age>18 limit 1, $offset;
    

    变量 $offset 取 rand(0, max_num-1) , max_num 可以放入缓存中

    分支判断

    SELECT id ,name, case type_id
    WHEN 0 THEN 'php'
    WHEN 1 THEN 'java'
    else 'c' end as '计算机语言'
     FROM tb
    

    长度判断

     SELECT * FROM tb WHERE length(app_type)>5
    

    查询近7天

    从昨天起往前推7天

    SELECT * FROM tb where  
    created >= unix_timestamp(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) and 
    created <= unix_timestamp(DATE_SUB(CURDATE(), INTERVAL 0 DAY))
    

    相关文章

      网友评论

          本文标题:mysql常用函数及语句笔记

          本文链接:https://www.haomeiwen.com/subject/lcyilctx.html