Mysql Common SQL statement Collection

Basic article

Query time, friendly hint $sql = "Select Date_format (create_time, '%y-%m-%d ') as day from table_name";//int timestamp type $sql = "Select From_unix Time (Create_time, '%y-%m-%d ') as day from table_name ";//A SQL returns multiple totals $sql =" SELECT count (*) all, "; $sql. = "Count (case is status = 1 then status end) Status_1_num,"; $sql. = "Count (case where status = 2 then status end) Status_2_num"; $sql. = "from table_name";//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 Ibid. The statement that replaces the contents of a field $sql = "UPDATE table_name SET content = replace (content, ' AAA ', ' BBB ')"; $sql. = "WHERE (content like '%aaa% ')";//Gets the data of a field in a table containing a string $sql = "SELECT * from ' table name ' where LOCATE (' keyword ', field name)";//Gets the first 4 in the field Bit $sql = "Select SUBSTRING (field name, 1,4) from table name"//Find extra duplicate record in table///single Field $sql = "SELECT * from table name where field name in"; $sql. = "(select field name FROM table name Group By field name having count (field name) > 1) ";//multiple Fields $sql =" SELECT * from table name alias where (alias. Field 1, alias. Field 2) in "; $sql. = "(select field 1, Field 2 from Table name Group By field 1, Field 2 have count (*) > 1)";//delete redundant duplicate records in table (minimum of left ID)//single Field $sql = "Delete from Table name where field name in "; $sql. = "(select field name from Table name Group By field name having count (field name) > 1)  "; $sql. = "and primary key ID not in"; $sql. = "(select min (primary key ID) from table name Group By field name having count (field name) >1)";//multiple fields $sql = "Delete from table name alias where (alias. Field 1, don't Name. Field 2) in "; $sql. = "(select field 1, Field 2 from Table name Group By field 1, Field 2 having count (*) > 1)"; $sql. = "and primary key ID not in"; $sql. = "(select min (primary key ID) from table name Group By field 1, Field 2 having count (*) >1)";

Business Chapter

Continuous range issues

Creating a test Table create table ' Test_number ' (' id ' int () unsigned not null auto_increment, ' number ' int (one) unsigned NOT null DE FAULT ' 0 ' COMMENT ' number ', PRIMARY KEY (' id ')) engine=innodb DEFAULT charset=utf8//Create test data insert into Test_number values ( ); 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);

Experimental objective: To find the continuous range of numbers.

According to the above data, the range should be obtained.

1-35-57-810-11//performs Sqlselect min (number) Start_range,max (number) End_rangefrom (select Number,rn,number-rn diff from (select number, @number: [email protected]+1 rn from Test_number, (select @number: =0) as number] b) C GROUP by D Iff

Continuous range of numbers

Sign in question

Create a reference table (required for simulation data) CREATE TABLE ' Test_nums ' (  ' id ' int (one) unsigned not NULL auto_increment,  primary KEY (' id ') Engine=innodb DEFAULT Charset=utf8 comment= ' reference table ';//analog data, insert 1-10000 continuous data.//Create a Test table ' Test_sign_history ' (& nbsp; ' id ' int (ten) unsigned NOT NULL auto_increment,   ' uid ' int (one) unsigned not null DEFAULT ' 0 ' COMMENT ' user ID ', &nbs p; ' Create_time ' timestamp not NULL DEFAULT current_timestamp COMMENT ' sign-in time ',  primary KEY (' id ')) engine=innodb DEFA ULT Charset=utf8 comment= ' Registration History table ';//create test data 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) Minutefrom test_nums where id<31;// Statistics daily user Check-in status 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 create_time= ' 2016-12-1 3 ' then C else 0 end] 13Sign,    sum (case Create_time= ' 2016-12-14 ' then C else 0 end) 14Sign,    sum if 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) 17Signfrom (   select        date_format (Create_time, ' %y-%m-%d ') Create_time,        hour (create_time) H,        count (*) c   &NBSP ; from Test_sign_history    group by        date_format (create_time, '%y-%m-%d '),   & nbsp    hour (Create_time)) a group by H with Rollup;

Count the daily user sign-in per hour

Statistics per hour of daily user attendance (when there is no data for an hour, show 0) Select    h,    sum (case time 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 CR Eate_time= ' 2016-12-13 ' then C else 0 end] 13Sign,    sum (case if create_time= ' 2016-12-14 ' then C else 0 end) 1 4Sign,    sum (case is create_time= ' 2016-12-15 ' then C else 0 end) 15Sign,    sum (case when Create_ti Me= ' 2016-12-16 ' then C else 0 end] 16Sign,    sum (case when create_time= ' 2016-12-17 ' then C else 0 end) 17SIGNFR Om (   select b.h h,c.create_time,c.c from     (       select id-1 h from Test_nu Ms where id<=24    ) b     LEFT join     (       select   &NBS P     Date_format (create_time, '%y-%m-%d ') create_time,         hour (Create_time) H,   &N Bsp     CounT (*) c        from test_sign_history        group by         D Ate_format (create_time, '%y-%m-%d '),         hour (create_time)      ) C on (b.h=c.h)) a GROUP by H with Rollup;

To count the daily user check-in status (0 if there is no data for an hour)

Statistics daily user check-in data and daily incremental data select        type,        sum (case when Create_time= ' 2016-12-11 ' then C else 0 end] 11Sign,        sum (case if 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,     &NBSP ;  sum (case is create_time= ' 2016-12-14 ' then C else 0 end) 14Sign,        sum (case when Create_ti Me= ' 2016-12-15 ' then C else 0 end) 15Sign,        sum if create_time= ' 2016-12-16 ' then C else 0 End) 16Sign,        sum (case is create_time= ' 2016-12-17 ' then C else 0 end) 17Signfrom (  &nbs P    select B.create_time,ifnull (b.c-c.c,0) C, ' Increment ' type from         (    &NB Sp      select             date_format (create_time, '%y-%m-%d ') create_time, &NB Sp     &NBSP;     COUNT (*) c            from test_sign_history         &NBSP ;  group by             date_format (create_time, '%y-%m-%d ')        ) b        left join         (           select             date_format (create_time, '%y-%m-%d ') create_time,           &N Bsp COUNT (*) c            from test_sign_history            group By             date_format (create_time, '%y-%m-%d ')        ) C on (b.creat E_time=c.create_time+ Interval 1 day)    union all        select         Date_format (create_time, '%y-%m-%d ') create_time,         COUNT (*) C,         ' Cur Rent '   &NBsp    from test_sign_history        group by         Date_format (create_tim E, '%y-%m-%d ')) a group by type Order by case when type= ' current ' then 1 else 0 end desc;

Statistics daily user check-in data and daily incremental data

Simulate different users sign in for different days insert into Test_sign_history (uid,create_time) Select Uid,create_time + Interval ceil (rand () *10) Day From Test_sign_history,test_nums where test_nums.id <10 order by rand () limit 150;//count the number of users in same number select    s Um (case-Day=1 then CN-Else 0 end) 1Day,    sum (case is day=2 then cn else 0 end) 2Day,    sum (c ASE when Day=3 and cn else 0 end) 3Day,    sum (case if Day=4 then CN else 0 end) 4Day,    sum (case When Day=5 then CN else 0 end) 5Day,    sum (case is day=6 then cn else 0 end) 6Day,    sum Day=7 then CN-Else 0 end) 7Day,    sum (case is day=8 then cn else 0 end) 8Day,    sum =9 then CN-Else 0 end) 9Day,    sum (case if day=10 then CN else 0 End) 10Dayfrom (   select c Day,co UNT (*) cn    from     (       select uid,count (*) c from test_sign_history Group B Y uid    ) a    group by c) b;

Count the number of users who have the same number of sign-in days

Count the consecutive check-in times for each user select * FROM (   select d.*,     @ggid: = @cggid,     @cggid: = D.uid,    if (@ggid = @cggid, @grank: = @grank + 1, @grank: = 1) grank    from     (      & Nbsp;select uid,min (c.create_time) begin_date, Max (c.create_time) end_date,count (*) count from       & nbsp (           select            b.*,             @gid: = @cgid,             @cgid: = B.uid,         &N Bsp  if (@gid = @cgid, @rank: = @rank + 1, @rank: = 1) rank,           &NBSP;[EMAIL&NBSP;PROTECTE D] Flag from (               select             &NB Sp  distinct                uid,             &NBSP ;  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 UI D,flag        order by Uid,count (*) desc    ) d, (SELECT @ggid: = 1, @cggid: = 1, @grank: = 1 ) as E) F where grank=1;

Count the consecutive sign-in times for each user

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

You can get the data sheet by following the public number and replying to the "check-in Data Sheet".

Thanks ~

Mysql Common SQL statement Collection