Mysql commonly used SQL statement highlights essay | the nuggets technology

Based on article

// Query time, friendly prompt$sql = "select date_format(create_time, '%Y-%m-%d') as day from table_name";
Copy the code

//int Timestamp type$sql = "select from_unixtime(create_time, '%Y-%m-%d') as day from table_name";
Copy the code

// an SQL query returns multiple totals$sql = "select count(*) all, " ;
$sql. =" count(case when status = 1 then status end) status_1_num, ";
$sql. =" count(case when status = 2 then status end) status_2_num ";
$sql. =" from table_name";
Copy the code

//Update Join / Delete Join
$sql = "update table_name_1 ";
$sql. =" inner join table_name_2 on table_name_1.id = table_name_2.uid ";
$sql. =" inner join table_name_3 on table_name_3.id = table_name_1.tid ";
$sql. =" set *** = *** ";
$sql. =" where *** "; //delete join as above.Copy the code

// A statement to replace the contents of a field$sql = "update table_name set content = REPLACE(content, 'aaa', 'bbb') ";
$sql. =" where (content like '%aaa%')";
Copy the code

// Get the data for a field containing a string in the table$sql = SELECT * FROM 'LOCATE' WHERE (SELECT * FROM 'LOCATE');
Copy the code

// Get the first four bits in the field$sql = SELECT SUBSTRING(表名,1,4) FROM 表名;
Copy the code

// Find redundant duplicate records in the table // single field$sql = Select * from table_name where table_name = 1;
$sql. ="(select * from table_name group by table_name having count > 1)"; // Multiple fields$sql = "Select * from 表名 alias where (alias) Field 1, alias. Field 2) in";
$sql. ="(select count(*), count(*) > 1) from (select count(*), count(*) > 1);
Copy the code

// Delete redundant duplicate records from the table (with minimum id) // single field$sql = "Delete from table_name where table_name in";
$sql. ="(select * from table_name group by table_name having count > 1)";
$sql. ="And primary key ID not in";
$sql. ="(select min(ID) from group by having count(ID) >1)"; // Multiple fields$sql = Delete from table name alias where (alias. Field 1, alias. Field 2) in";
$sql. ="(select count(*), count(*) > 1) from (select count(*), count(*) > 1);
$sql. ="And primary key ID not in";
$sql. ="(select min(ID) from table_name group by table_name, table_name having count(*)>1)";
Copy the code

The business report

Continuous range problem

CREATE TABLE 'test_number' (' id 'int(10) unsigned NOT NULL AUTO_INCREMENT, `number` int(11) unsigned NOT NULL DEFAULT'0' COMMENT 'digital',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Copy the code

Insert into test_number values(1,1); Insert into test_number values (2, 2); Insert into test_number values (3, 3); Insert into test_number values (4, 5); Insert into test_number values (5, 7); Insert into test_number values (6, 8); Insert into test_number values (7, 10); Insert into test_number values (8, 11);Copy the code

Objective: To find the continuous range of numbers.

Based on the above data, the range should be obtained.

1-3 5-7-8-10-11Copy the code

Sql select min(number) start_range, Max (number) end_range from (select number,rn,number-rn diff from (select number,@number:=@number+1 rn from test_number,(select @number:=0) as number ) b ) c group by diff;Copy the code

CREATE TABLE 'test_nums' (' id' int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Reference table'; // Insert 1-10000 continuous data.Copy the code

CREATE TABLE 'test_sign_history' (' id 'int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned NOT NULL DEFAULT'0' COMMENT 'user ID',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Check-in time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Check-in History Table';
Copy the code

Insert into test_sign_history(uid,create_time) select ceil(rand()*10000),str_to_date()'2016-12-11'.'%Y-%m-%d')+interval ceil(rand()*10000) minute
from test_nums where id<31;
Copy the code

Select h, sum(select h, sum(case when create_time='2016-12-11' then c else 0 end) 11Sign,
    sum(case when create_time='2016-12-12' then c else 0 end) 12Sign,
    sum(case when create_time='2016-12-13' then c else 0 end) 13Sign,
    sum(case when create_time='2016-12-14' then c else 0 end) 14Sign,
    sum(case when create_time='2016-12-15' then c else 0 end) 15Sign,
    sum(case when create_time='2016-12-16' then c else 0 end) 16Sign,
    sum(case when create_time='2016-12-17' then c else 0 end) 17Sign
from
(
    select
        date_format(create_time,'%Y-%m-%d') create_time,
        hour(create_time) h,
        count(*) c
    from test_sign_history
    group by
        date_format(create_time,'%Y-%m-%d'),
        hour(create_time)
) a
group by h with rollup;
Copy the code

Select h, sum(1, 1, 2, 3, 3, 4)case when create_time='2016-12-11' then c else 0 end) 11Sign,
    sum(case when create_time='2016-12-12' then c else 0 end) 12Sign,
    sum(case when create_time='2016-12-13' then c else 0 end) 13Sign,
    sum(case when create_time='2016-12-14' then c else 0 end) 14Sign,
    sum(case when create_time='2016-12-15' then c else 0 end) 15Sign,
    sum(case when create_time='2016-12-16' then c else 0 end) 16Sign,
    sum(case when create_time='2016-12-17' then c else 0 end) 17Sign
from
(
    select b.h h,c.create_time,c.c from
     (
        select id-1 h from test_nums where id<=24
     ) b
     left join
     (
        select
         date_format(create_time,'%Y-%m-%d') create_time,
         hour(create_time) h,
         count(*) c
        from test_sign_history
        group by
         date_format(create_time,'%Y-%m-%d'),
         hour(create_time)
      ) c on (b.h=c.h)
) a
group by h with rollup;
Copy the code

// Count daily user check-in data and daily increment data selecttype,
        sum(case when create_time='2016-12-11' then c else 0 end) 11Sign,
        sum(case when create_time='2016-12-12' then c else 0 end) 12Sign,
        sum(case when create_time='2016-12-13' then c else 0 end) 13Sign,
        sum(case when create_time='2016-12-14' then c else 0 end) 14Sign,
        sum(case when create_time='2016-12-15' then c else 0 end) 15Sign,
        sum(case when create_time='2016-12-16' then c else 0 end) 16Sign,
        sum(case when create_time='2016-12-17' then c else 0 end) 17Sign
from
(
        select b.create_time,ifnull(b.c-c.c,0) c,'Increment' type from
        (
            select
             date_format(create_time,'%Y-%m-%d') create_time,
             count(*) c
            from test_sign_history
            group by
             date_format(create_time,'%Y-%m-%d')
        ) b
        left join
        (
            select
             date_format(create_time,'%Y-%m-%d') create_time,
             count(*) c
            from test_sign_history
            group by
             date_format(create_time,'%Y-%m-%d')
        ) c on(b.create_time=c.create_time+ interval 1 day)
    union all
        select
         date_format(create_time,'%Y-%m-%d') create_time,
         count(*) c,
         'Current'
        from test_sign_history
        group by
         date_format(create_time,'%Y-%m-%d')
) a
group by type
order by case when type='Current' then 1 else 0 end desc;
Copy the code

Insert into test_sign_history(uid,create_time) select uid,create_time + interval ceil(rand()*10) day insert into test_sign_history(uid,create_time) select UID,create_time + interval ceil(rand()*10) day from test_sign_history,test_numswhere test_nums.id <10 order by rand() limit 150;
Copy the code

// Count the number of users with the same check-in days select sum(case when day=1 then cn else 0 end) 1Day,
    sum(case when day=2 then cn else 0 end) 2Day,
    sum(case when day=3 then cn else 0 end) 3Day,
    sum(case when day=4 then cn else 0 end) 4Day,
    sum(case when day=5 then cn else 0 end) 5Day,
    sum(case when day=6 then cn else 0 end) 6Day,
    sum(case when day=7 then cn else 0 end) 7Day,
    sum(case when day=8 then cn else 0 end) 8Day,
    sum(case when day=9 then cn else 0 end) 9Day,
    sum(case when day=10 then cn else 0 end) 10Day
from
(
    select c day,count(*) cn
    from
    (
        select uid,count(*) c from test_sign_history group by uid
    ) a
    group by c
) b;
Copy the code

Select * from (select d.*, @ggid := @cggid, @cggid := d.id,if(@ggid = @cggid, @grank := @grank + 1, @grank := 1) grank
    from
    (
        select uid,min(c.create_time) begin_date ,max(c.create_time) end_date,count(*) count from
        (
            select
            b.*,
            @gid := @cgid,
            @cgid := b.uid,
            if(@gid = @cgid, @rank := @rank + 1, @rank := 1) rank,
            b.diff-@rank flag from (
                select
                distinct
                uid,
                date_format(create_time,'%Y-%m-%d') create_time,
                datediff(create_time,now()) diff
                from test_sign_history order by uid,create_time
            ) b, (SELECT @gid := 1, @cgid := 1, @rank := 1) as a
        ) c group by uid,flag
        order by uid,count(*) desc
    ) d,(SELECT @ggid := 1, @cggid := 1, @grank := 1) as e
)f
where grank=1;
Copy the code

If you need to download the relevant data sheet above, test it.

You can follow the wechat official account and reply “Check-in data sheet” to obtain the data sheet.

Thanks ~

Mysql commonly used SQL statement highlights essay | the nuggets technology

Related Posts

Summary of SQL optimization for various cases of MySQL index failure

DeepDetect — API unification for machine learning frameworks

SpringAOP + annotations for log management