Mysql常用SQL语句

Z时代
2024-01-10
分类：综合

database

基础篇

//查询时间，友好提示
$sql = "selectdate_format(create_time, "%Y-%m-%d") asdayfrom table_name";

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

//一个sql返回多个总数
$sql = "selectcount(*) all, " ;
$sql .= "count(casewhenstatus = 1thenstatusend) status_1_num, ";
$sql .= "count(casewhenstatus = 2thenstatusend) 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 同上。

//替换某字段的内容的语句
$sql = "update table_name setcontent = REPLACE(content, "aaa", "bbb") ";$sql .= "where (contentlike"%aaa%")";

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

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

//查找表中多余的重复记录
//单个字段
$sql = "select * from 表名 where 字段名 in";
$sql .= "(select 字段名 from 表名 groupby 字段名 havingcount(字段名) > 1 )";
//多个字段
$sql = "select * from 表名 别名 where (别名.字段1,别名.字段2) in";$sql .= "(select 字段1,字段2from 表名 groupby 字段1,字段2havingcount(*) > 1 )";

//删除表中多余的重复记录(留id最小)
//单个字段
$sql = "deletefrom 表名 where 字段名 in";
$sql .= "(select 字段名 from 表名 groupby 字段名 havingcount(字段名) > 1)  ";
$sql .= "and 主键IDnotin";
$sql .= "(selectmin(主键ID) from 表名 groupby 字段名 havingcount(字段名 )>1) ";
//多个字段
$sql = "deletefrom 表名 别名 where (别名.字段1,别名.字段2) in";
$sql .= "(select 字段1,字段2from 表名 groupby 字段1,字段2havingcount(*) > 1) ";
$sql .= "and 主键IDnotin";$sql .= "(selectmin(主键ID) from 表名 groupby 字段1,字段2havingcount(*)>1) ";

业务篇

连续范围问题

//创建测试表
CREATETABLE`test_number` (
`id`int(10) unsignedNOTNULL AUTO_INCREMENT,
`number`int(11) unsignedNOTNULLDEFAULT"0"COMMENT"数字",
  PRIMARY KEY (`id`)) ENGINE=InnoDBDEFAULTCHARSET=utf8

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

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

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

1-3 5-5 7-8

10-11

//执行Sql
select min(number) start_range,max(number) end_range
from
(
selectnumber,rn,number-rn diff from
    (
selectnumber,@number:=@number+1 rn from test_number,(select @number:=0) asnumber
    ) b) c groupby diff;

签到问题

//创建参考表(模拟数据需要用到)
CREATETABLE`test_nums` (
`id`int(11) unsignedNOTNULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8 COMMENT="参考表";//模拟数据，插入 1-10000 连续数据.

//创建测试表
CREATETABLE`test_sign_history` (
`id`int(10) unsignedNOTNULL AUTO_INCREMENT,
`uid`int(11) unsignedNOTNULLDEFAULT"0"COMMENT"用户ID",
`create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT"签到时间",
  PRIMARY KEY (`id`)) ENGINE=InnoDBDEFAULTCHARSET=utf8 COMMENT="签到历史表";

//创建测试数据
insertinto test_sign_history(uid,create_time)
selectceil(rand()*10000),str_to_date("2016-12-11","%Y-%m-%d")+intervalceil(rand()*10000) minutefrom test_nums whereid<31;

//统计每天的每小时用户签到情况
select
    h,
sum(casewhen create_time="2016-12-11"then c else0end) 11Sign,
sum(casewhen create_time="2016-12-12"then c else0end) 12Sign,
sum(casewhen create_time="2016-12-13"then c else0end) 13Sign,
sum(casewhen create_time="2016-12-14"then c else0end) 14Sign,
sum(casewhen create_time="2016-12-15"then c else0end) 15Sign,
sum(casewhen create_time="2016-12-16"then c else0end) 16Sign,
sum(casewhen create_time="2016-12-17"then c else0end) 17Sign
from
(
select
date_format(create_time,"%Y-%m-%d") create_time,
hour(create_time) h,
count(*) c
from test_sign_history
groupby
date_format(create_time,"%Y-%m-%d"),
hour(create_time)
) agroup by h withrollup;

//统计每天的每小时用户签到情况(当某个小时没有数据时，显示0)
select
    h ,
sum(casewhen create_time="2016-12-11"then c else0end) 11Sign,
sum(casewhen create_time="2016-12-12"then c else0end) 12Sign,
sum(casewhen create_time="2016-12-13"then c else0end) 13Sign,
sum(casewhen create_time="2016-12-14"then c else0end) 14Sign,
sum(casewhen create_time="2016-12-15"then c else0end) 15Sign,
sum(casewhen create_time="2016-12-16"then c else0end) 16Sign,
sum(casewhen create_time="2016-12-17"then c else0end) 17Sign
from
(
select b.h h,c.create_time,c.c from
     (
selectid-1 h from test_nums whereid<=24
     ) b
leftjoin
     (
select
date_format(create_time,"%Y-%m-%d") create_time,
hour(create_time) h,
count(*) c
from test_sign_history
groupby
date_format(create_time,"%Y-%m-%d"),
hour(create_time)
      ) c on (b.h=c.h)
) agroup by h withrollup;

//统计每天的用户签到数据和每天的增量数据
select
type,
sum(casewhen create_time="2016-12-11"then c else0end) 11Sign,
sum(casewhen create_time="2016-12-12"then c else0end) 12Sign,
sum(casewhen create_time="2016-12-13"then c else0end) 13Sign,
sum(casewhen create_time="2016-12-14"then c else0end) 14Sign,
sum(casewhen create_time="2016-12-15"then c else0end) 15Sign,
sum(casewhen create_time="2016-12-16"then c else0end) 16Sign,
sum(casewhen create_time="2016-12-17"then c else0end) 17Sign
from
(
select b.create_time,ifnull(b.c-c.c,0) c,"Increment"typefrom
        (
select
date_format(create_time,"%Y-%m-%d") create_time,
count(*) c
from test_sign_history
groupby
date_format(create_time,"%Y-%m-%d")
        ) b
leftjoin
        (
select
date_format(create_time,"%Y-%m-%d") create_time,
count(*) c
from test_sign_history
groupby
date_format(create_time,"%Y-%m-%d")
        ) c on(b.create_time=c.create_time+ interval1day)
unionall
select
date_format(create_time,"%Y-%m-%d") create_time,
count(*) c,
"Current"
from test_sign_history
groupby
date_format(create_time,"%Y-%m-%d")
) a
group by typeorder bycasewhentype="Current"then1else0enddesc;

//模拟不同的用户签到了不同的天数
insertinto test_sign_history(uid,create_time)
select uid,create_time + intervalceil(rand()*10) dayfrom test_sign_history,test_numswhere test_nums.id <10orderbyrand() limit150;

//统计签到天数相同的用户数量
select
sum(casewhenday=1then cn else0end) 1Day,
sum(casewhenday=2then cn else0end) 2Day,
sum(casewhenday=3then cn else0end) 3Day,
sum(casewhenday=4then cn else0end) 4Day,
sum(casewhenday=5then cn else0end) 5Day,
sum(casewhenday=6then cn else0end) 6Day,
sum(casewhenday=7then cn else0end) 7Day,
sum(casewhenday=8then cn else0end) 8Day,
sum(casewhenday=9then cn else0end) 9Day,
sum(casewhenday=10then cn else0end) 10Day
from
(
select c day,count(*) cn
from
    (
select uid,count(*) c from test_sign_history groupby uid
    ) a
groupby c) b;

//统计每个用户的连续签到时间
select * from (
select d.*,
    @ggid := @cggid,
    @cggid := d.uid,
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(*) countfrom
        (
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 orderby uid,create_time
            ) b, (SELECT @gid := 1, @cgid := 1, @rank := 1) as a
        ) c groupby uid,flag
orderby uid,count(*) desc
    ) d,(SELECT @ggid := 1, @cggid := 1, @grank := 1) as e
)fwhere grank=1;

以上是 Mysql常用SQL语句的全部内容，来源链接： utcz.com/z/533915.html

Mysql常用SQL语句

其他人也看了：