Mysql常用SQL语句收集

基础篇

1. //查询时间，友好提示
2. $sql = "select date_format(create_time, '%Y-%m-%d') as day from table_name";

1. //int 时间戳类型
2. $sql = "select from_unixtime(create_time, '%Y-%m-%d') as day from table_name";

1. //一个sql返回多个总数
2. $sql = "select count(*) all, " ;
3. $sql .= " count(case when status = 1 then status end) status_1_num, ";
4. $sql .= " count(case when status = 2 then status end) status_2_num ";
5. $sql .= " from table_name";

1. //Update Join / Delete Join
2. $sql = "update table_name_1 ";
3. $sql .= " inner join table_name_2 on table_name_1.id = table_name_2.uid ";
4. $sql .= " inner join table_name_3 on table_name_3.id = table_name_1.tid ";
5. $sql .= " set *** = *** ";
6. $sql .= " where *** ";
7.  
8. //delete join 同上。

1. //替换某字段的内容的语句
2. $sql = "update table_name set content = REPLACE(content, 'aaa', 'bbb') ";
3. $sql .= " where (content like '%aaa%')";

1. //获取表中某字段包含某字符串的数据
2. $sql = "SELECT * FROM `表名` WHERE LOCATE('关键字', 字段名) ";

1. //获取字段中的前4位
2. $sql = "SELECT SUBSTRING(字段名,1,4) FROM 表名 ";

1. //查找表中多余的重复记录
2. //单个字段
3. $sql = "select * from 表名 where 字段名 in ";
4. $sql .= "(select 字段名 from 表名 group by 字段名 having count(字段名) > 1 )";
5. //多个字段
6. $sql = "select * from 表名 别名 where (别名.字段1,别名.字段2) in ";
7. $sql .= "(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1 )";

1. //删除表中多余的重复记录(留id最小)
2. //单个字段
3. $sql = "delete from 表名 where 字段名 in ";
4. $sql .= "(select 字段名 from 表名 group by 字段名 having count(字段名) > 1)  ";
5. $sql .= "and 主键ID not in ";
6. $sql .= "(select min(主键ID) from 表名 group by 字段名 having count(字段名 )>1) ";
7. //多个字段
8. $sql = "delete from 表名 别名 where (别名.字段1,别名.字段2) in ";
9. $sql .= "(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1) ";
10. $sql .= "and 主键ID not in ";
11. $sql .= "(select min(主键ID) from 表名 group by 字段1,字段2 having count(*)>1) ";

业务篇

连续范围问题

1. //创建测试表
2. CREATE TABLE `test_number` (
3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
4.   `number` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '数字',
5.   PRIMARY KEY (`id`)
6. ) ENGINE=innodb DEFAULT CHARSET=utf8

1. //创建测试数据
2. insert into test_number values(1,1);
3. insert into test_number values(2,2);
4. insert into test_number values(3,3);
5. insert into test_number values(4,5);
6. insert into test_number values(5,7);
7. insert into test_number values(6,8);
8. insert into test_number values(7,10);
9. insert into test_number values(8,11);

实验目标：求数字的连续范围。

根据上面的数据，应该得到的范围。

1. 1-3
2. 5-5
3. 7-8
4. 10-11

1. //执行Sql
2. select min(number) start_range,max(number) end_range
3. from
4. (
5.     select number,rn,number-rn diff from
6.     (
7.         select number,@number:=@number+1 rn from test_number,(select @number:=0) as number
8.     ) b
9. ) c group by diff;

数字的连续范围

签到问题

1. //创建参考表(模拟数据需要用到)
2. CREATE TABLE `test_nums` (
3.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
4.   PRIMARY KEY (`id`)
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='参考表';
6. //模拟数据，插入 1-10000 连续数据.

1. //创建测试表
2. CREATE TABLE `test_sign_history` (
3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
4.   `uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
5.   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '签到时间',
6.   PRIMARY KEY (`id`)
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='签到历史表';

1. //创建测试数据
2. insert into test_sign_history(uid,create_time)
3. select ceil(rand()*10000),str_to_date('2016-12-11','%Y-%m-%d')+interval ceil(rand()*10000) minute
4. from test_nums where id<31;

1. //统计每天的每小时用户签到情况
2. select
3.     h,
4.     sum(case when create_time='2016-12-11' then c else 0 end) 11Sign,
5.     sum(case when create_time='2016-12-12' then c else 0 end) 12Sign,
6.     sum(case when create_time='2016-12-13' then c else 0 end) 13Sign,
7.     sum(case when create_time='2016-12-14' then c else 0 end) 14Sign,
8.     sum(case when create_time='2016-12-15' then c else 0 end) 15Sign,
9.     sum(case when create_time='2016-12-16' then c else 0 end) 16Sign,
10.     sum(case when create_time='2016-12-17' then c else 0 end) 17Sign
11. from
12. (
13.     select
14.         date_format(create_time,'%Y-%m-%d') create_time,
15.         hour(create_time) h,
16.         count(*) c
17.     from test_sign_history
18.     group by
19.         date_format(create_time,'%Y-%m-%d'),
20.         hour(create_time)
21. ) a
22. group by h with rollup;

统计每天的每小时用户签到情况

 

1. //统计每天的每小时用户签到情况(当某个小时没有数据时，显示0)
2. select
3.     h ,
4.     sum(case when create_time='2016-12-11' then c else 0 end) 11Sign,
5.     sum(case when create_time='2016-12-12' then c else 0 end) 12Sign,
6.     sum(case when create_time='2016-12-13' then c else 0 end) 13Sign,
7.     sum(case when create_time='2016-12-14' then c else 0 end) 14Sign,
8.     sum(case when create_time='2016-12-15' then c else 0 end) 15Sign,
9.     sum(case when create_time='2016-12-16' then c else 0 end) 16Sign,
10.     sum(case when create_time='2016-12-17' then c else 0 end) 17Sign
11. from
12. (
13.     select b.h h,c.create_time,c.c from
14.      (
15.         select id-1 h from test_nums where id<=24
16.      ) b
17.      left join
18.      (
19.         select
20.          date_format(create_time,'%Y-%m-%d') create_time,
21.          hour(create_time) h,
22.          count(*) c
23.         from test_sign_history
24.         group by
25.          date_format(create_time,'%Y-%m-%d'),
26.          hour(create_time)
27.       ) c on (b.h=c.h)
28. ) a
29. group by h with rollup;

统计每天的每小时用户签到情况(当某个小时没有数据时，显示0)

 

1. //统计每天的用户签到数据和每天的增量数据
2. select
3.         type,
4.         sum(case when create_time='2016-12-11' then c else 0 end) 11Sign,
5.         sum(case when create_time='2016-12-12' then c else 0 end) 12Sign,
6.         sum(case when create_time='2016-12-13' then c else 0 end) 13Sign,
7.         sum(case when create_time='2016-12-14' then c else 0 end) 14Sign,
8.         sum(case when create_time='2016-12-15' then c else 0 end) 15Sign,
9.         sum(case when create_time='2016-12-16' then c else 0 end) 16Sign,
10.         sum(case when create_time='2016-12-17' then c else 0 end) 17Sign
11. from
12. (
13.         select b.create_time,ifnull(b.c-c.c,0) c,'Increment' type from
14.         (
15.             select
16.              date_format(create_time,'%Y-%m-%d') create_time,
17.              count(*) c
18.             from test_sign_history
19.             group by
20.              date_format(create_time,'%Y-%m-%d')
21.         ) b
22.         left join
23.         (
24.             select
25.              date_format(create_time,'%Y-%m-%d') create_time,
26.              count(*) c
27.             from test_sign_history
28.             group by
29.              date_format(create_time,'%Y-%m-%d')
30.         ) c on(b.create_time=c.create_time+ interval 1 day)
31.     union all
32.         select
33.          date_format(create_time,'%Y-%m-%d') create_time,
34.          count(*) c,
35.          'Current'
36.         from test_sign_history
37.         group by
38.          date_format(create_time,'%Y-%m-%d')
39. ) a
40. group by type
41. order by case when type='Current' then 1 else 0 end desc;

统计每天的用户签到数据和每天的增量数据

 

1. //模拟不同的用户签到了不同的天数
2. insert into test_sign_history(uid,create_time)
3. select uid,create_time + interval ceil(rand()*10) day from test_sign_history,test_nums
4. where test_nums.id <10 order by rand() limit 150;

1. //统计签到天数相同的用户数量
2. select
3.     sum(case when day=1 then cn else 0 end) 1Day,
4.     sum(case when day=2 then cn else 0 end) 2Day,
5.     sum(case when day=3 then cn else 0 end) 3Day,
6.     sum(case when day=4 then cn else 0 end) 4Day,
7.     sum(case when day=5 then cn else 0 end) 5Day,
8.     sum(case when day=6 then cn else 0 end) 6Day,
9.     sum(case when day=7 then cn else 0 end) 7Day,
10.     sum(case when day=8 then cn else 0 end) 8Day,
11.     sum(case when day=9 then cn else 0 end) 9Day,
12.     sum(case when day=10 then cn else 0 end) 10Day
13. from
14. (
15.     select c day,count(*) cn
16.     from
17.     (
18.         select uid,count(*) c from test_sign_history group by uid
19.     ) a
20.     group by c
21. ) b;

 

统计签到天数相同的用户数量

 

1. //统计每个用户的连续签到时间
2. select * from (
3.     select d.*,
4.     @ggid := @cggid,
5.     @cggid := d.uid,
6.     if(@ggid = @cggid, @grank := @grank + 1, @grank := 1) grank
7.     from
8.     (
9.         select uid,min(c.create_time) begin_date ,max(c.create_time) end_date,count(*) count from
10.         (
11.             select
12.             b.*,
13.             @gid := @cgid,
14.             @cgid := b.uid,
15.             if(@gid = @cgid, @rank := @rank + 1, @rank := 1) rank,
16.             b.diff-@rank flag from (
17.                 select
18.                 distinct
19.                 uid,
20.                 date_format(create_time,'%Y-%m-%d') create_time,
21.                 datediff(create_time,now()) diff
22.                 from test_sign_history order by uid,create_time
23.             ) b, (SELECT @gid := 1, @cgid := 1, @rank := 1) as a
24.         ) c group by uid,flag
25.         order by uid,count(*) desc
26.     ) d,(SELECT @ggid := 1, @cggid := 1, @grank := 1) as e
27. )f
28. where grank=1;

原文链接：http://www.kubiji.cn/topic-id3615.html