Mysql 常用SQL語句集錦

asoren 發(fā)布于2019-06-27 15:01 / 739人閱讀

摘要：基礎(chǔ)篇查詢時間，友好提示時間戳類型一個返回多個總數(shù)同上。根據(jù)上面的數(shù)據(jù)，應(yīng)該得到的范圍。

基礎(chǔ)篇

//查詢時間，友好提示
$sql = "select date_format(create_time, "%Y-%m-%d") as day from table_name";

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

//一個sql返回多個總數(shù)
$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";

//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 同上。

//替換某字段的內(nèi)容的語句
$sql = "update table_name set content = REPLACE(content, "aaa", "bbb") ";
$sql .= " where (content like "%aaa%")";

//獲取表中某字段包含某字符串的數(shù)據(jù)
$sql = "SELECT * FROM `表名` WHERE LOCATE("關(guān)鍵字", 字段名) ";

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

//查找表中多余的重復(fù)記錄
//單個字段
$sql = "select * from 表名 where 字段名 in ";
$sql .= "(select 字段名 from 表名 group by 字段名 having count(字段名) > 1 )";
//多個字段
$sql = "select * from 表名 別名 where (別名.字段1,別名.字段2) in ";
$sql .= "(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1 )";

//刪除表中多余的重復(fù)記錄(留id最小)
//單個字段
$sql = "delete from 表名 where 字段名 in ";
$sql .= "(select 字段名 from 表名 group by 字段名 having count(字段名) > 1)  ";
$sql .= "and 主鍵ID not in ";
$sql .= "(select min(主鍵ID) from 表名 group by 字段名 having count(字段名 )>1) ";
//多個字段
$sql = "delete from 表名 別名 where (別名.字段1,別名.字段2) in ";
$sql .= "(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1) ";
$sql .= "and 主鍵ID not in ";
$sql .= "(select min(主鍵ID) from 表名 group by 字段1,字段2 having count(*)>1) ";

業(yè)務(wù)篇

連續(xù)范圍問題

//創(chuàng)建測試表
CREATE TABLE `test_number` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) unsigned NOT NULL DEFAULT "0" COMMENT "數(shù)字",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

//創(chuàng)建測試數(shù)據(jù)
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);

實驗?zāi)繕?biāo)：求數(shù)字的連續(xù)范圍。

根據(jù)上面的數(shù)據(jù)，應(yīng)該得到的范圍。

1-3
5-5
7-8
10-11

//執(zhí)行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;

簽到問題

//創(chuàng)建參考表(模擬數(shù)據(jù)需要用到)
CREATE TABLE `test_nums` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="參考表";
//模擬數(shù)據(jù)，插入 1-10000 連續(xù)數(shù)據(jù).

//創(chuàng)建測試表
CREATE TABLE `test_sign_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL DEFAULT "0" COMMENT "用戶ID",
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "簽到時間",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="簽到歷史表";

//創(chuàng)建測試數(shù)據(jù)
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;

//統(tǒng)計每天的每小時用戶簽到情況
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;

//統(tǒng)計每天的每小時用戶簽到情況(當(dāng)某個小時沒有數(shù)據(jù)時，顯示0)
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 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;

//統(tǒng)計每天的用戶簽到數(shù)據(jù)和每天的增量數(shù)據(jù)
select
        type,
        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;

//模擬不同的用戶簽到了不同的天數(shù)
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;

//統(tǒng)計簽到天數(shù)相同的用戶數(shù)量
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;

//統(tǒng)計每個用戶的連續(xù)簽到時間
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(*) 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;

如果大家需要下載上述的相關(guān)數(shù)據(jù)表，進(jìn)行測試。

可以關(guān)注微信公眾號，回復(fù) “簽到數(shù)據(jù)表”，即可獲取數(shù)據(jù)表。

Thanks ~

云服務(wù)器 GPU云服務(wù)器 mysql常用sql語句常用sql語句常用sql查詢語句常用sql語句大全

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/22153.html

發(fā)表評論

登陸后可評論

0條評論

asoren

男|高級講師

我要關(guān)注我要私信

TA的文章

#11.11#搬瓦工VPS全場8.9折優(yōu)惠，$44.49/年起，年付低至7.5折，續(xù)費不漲價

閱讀 3132·2021-11-15 18:14
阿里云服務(wù)器1M帶寬實際下載速度是多少?(帶寬和下載速度的關(guān)系)

閱讀 1783·2021-09-22 10:51
一文帶你斬殺Python之Numpy??Pandas全部操作【全網(wǎng)最詳細(xì)】???

閱讀 3299·2021-09-09 09:34
動態(tài)內(nèi)存管理（下）

閱讀 3514·2021-09-06 15:02
hostyun：美國三網(wǎng)廉價版cn2 gia vps晚高峰簡單測評，看看數(shù)據(jù)和性能~

閱讀 1031·2021-09-01 11:40
CSS—總結(jié)常用垂直居中方法

閱讀 3194·2019-08-30 13:58
前端開發(fā)工具集 eutils

閱讀 2534·2019-08-30 11:04
【20160119】貌美的bootstrap模板和git安裝

閱讀 1088·2019-08-28 18:31

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

Mysql 常用SQL語句集錦

相關(guān)文章