Mysql常用SQL语句集锦

最新推荐文章于 2024-08-15 16:05:29 发布
最新推荐文章于 2024-08-15 16:05:29 发布
阅读量286 收藏
点赞数
分类专栏： 值得一看的转载小文

基础篇

//查询时间，友好提示
$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返回多个总数
$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 同上。
//替换某字段的内容的语句
$sql = "update table_name set content = REPLACE(content, 'aaa', 'bbb') ";
$sql .= " where (content like '%aaa%')";
//获取表中某字段包含某字符串的数据
$sql = "SELECT * FROM `表名` WHERE LOCATE('关键字', 字段名) ";
//获取字段中的前4位
$sql = "SELECT SUBSTRING(字段名,1,4) FROM 表名 ";
//查找表中多余的重复记录
//单个字段
$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 )";
//删除表中多余的重复记录(留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) ";

业务篇

  • 连续范围问题
//创建测试表
CREATE TABLE `test_number` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '数字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
//创建测试数据
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);

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

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

1-3
5-5
7-8
10-11
//执行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;

数字的连续范围

  • 签到问题
//创建参考表(模拟数据需要用到)
CREATE TABLE `test_nums` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='参考表';
//模拟数据，插入 1-10000 连续数据.
//创建测试表
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='签到历史表';
//创建测试数据
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;
//统计每天的每小时用户签到情况
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;

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

//统计每天的每小时用户签到情况(当某个小时没有数据时，显示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;

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

//统计每天的用户签到数据和每天的增量数据
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;

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

//模拟不同的用户签到了不同的天数
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;
//统计签到天数相同的用户数量
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;

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

//统计每个用户的连续签到时间
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;

统计每个用户的连续签到时间
CapsLockWY
关注
专栏目录
MySQL工作中常用语句（最全）
qq_43115961的博客
09-05 1806
MySQL工作中常用语句（最全）
MySQL高级SQL语句
weixin_55609814的博客
06-30 2万+
MySQL高级SQL语句MySQL高级SQL语句函数数学函数聚合函数字符串函数 MySQL高级SQL语句 use kgc; create table location (Region char(20),store_name char(20)); insert into location values ('East','Boston') ; insert into location values ('East','New York'); insert into location values ('west',
Mysql常用SQL语句集锦 | 掘金技术征文
weixin_33690367的博客
12-12 129
基础篇 //查询时间，友好提示 $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"; 复制代码//一个sq...
MySQL 常用 SQL 语句大全
最新发布
admin_Object的博客
08-15 3702
这些 SQL 语句和技巧覆盖了数据库操作的广泛领域，从基础查询到高级管理和优化。掌握这些可以帮助你更高效地管理和操作 MySQL 数据库。使用 GROUP_CONCAT 函数。删除表中的所有记录(也叫截断表)内连接（INNER JOIN）右连接（RIGHT JOIN）基于其他表的更新(关联更新)左连接（LEFT JOIN）全连接（FULL JOIN）在 WHERE 子句中。计算字段的平均值和总和。在 FROM 子句中。查看当前的数据库状态。
Mysql中常用的sql语句汇总
柯烽博客
04-15 238
(1)数据记录筛选： sql= "select * from 数据表 where 字段名 = 字段值 order by 字段名 [desc]" sql = "select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]" sql = "select top10 * from 数据表 where...
mysql 中常用的语句
ziwenCSDN的博客
12-04 301
mysql 中常用的语句. i, 匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串; ii,ORDER BY 排序：ORDER BY DESC|ASC　　　　=>按数据的降序和升序排列 iii,group by 分组.条件使用Having; having 和group by 是形影不离的好朋友.SELECT COUNT() FROM tb_name GROUP BY
MySQL之多表连接
蔡政洁的博客
04-26 3130
目录（1）表的横向连接（1.1）表连接的方式（1.1.1）left join（1.1.2）right join（1.1.3）inner join（1.1.4）outer join（1.2）表连接的类型（1.2.1）一对一（1.2.2）一对多（1.2.3）多对多（1.3）多张表连接（2）表的纵向连接（3）横向连接的底层原理（3.1）Simple Nested-Loop Join（简单的嵌套循环联接）（3.2）Index Nested-Loop Join（索引嵌套循环联接）（3.3）Block Nested-L
mysql常用sql语句总结
07-13
sql语句
Mysql常用sql语句汇总
12-16
1、mysql 导出文件： SELECT `pe2e_user_to_company`.company_name, `pe2e_user_to_company`.company_code, `users`.name, `users`.uid, `users`.mail, `pe2e_email_notification_email`.`email_cc` FROM `users` ,...
MYSQL数据库常用SQL语句
08-09
MYSQL数据库常用SQL语句
mysql中常用的语句整理
Y20122580的博客
11-14 148
mysql中常用的语句: 1:创建带自增长的主键的表 DROP TABLE IF EXISTS user_login ; CREATE TABLE user_login ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_name VARCHAR(50) DEFAULT NULL, PRIMARY KE...
Mysql是测试代码片段
05-15
Mysql是测试代码片段Mysql是测试代码片段
mysql中常用的语句_mysql中常用的语句整理
weixin_39807352的博客
01-26 112
mysql中常用的语句:1:创建带自增长的主键的表DROP TABLE IF EXISTS user_login ;CREATE TABLE user_login (user_id INT UNSIGNED NOT NULLAUTO_INCREMENT,user_name VARCHAR(50) DEFAULT NULL,PRIMARY KEY user_id) ENGINE=MYISAMAUT...
mysql中常用的语句_mysql数据库中常用的语句（建议收藏）
weixin_29295541的博客
01-26 150
mysql 中常用的语句i, 匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串;ii,ORDER BY 排序：ORDER BY DESC|ASC　　　　=>按数据的降序和升序排列iii,group by 分组.条件使用Having; having 和group by 是形影不离的好朋友.SELECT COUNT() FROM tb_name GROUP BY score HAVIN...
MySQL常用语句大全
热门推荐
热爱编程、热爱生活、热爱探索
08-03 3万+
本文主要记录了常用的SQL语句，欢迎收藏，如果觉得对你有帮助就动动你的小手指给我一个赞👍，你的支持将是我持续输出的动力
mysql中常用的语句_[SQL]常用的MySQL基本语句（整理中）
weixin_42408865的博客
01-28 176
一、数据库的基本操作 数据库是用来存储表的地方，在数据库开始创建的时候，要先创建好DATABASE然后才能在里面继续进行数据表的操作。 1.创建DATABASE CREATE DATABASE 用于创建数据库，基本语法如下： CREATE DATABASE database_name 下面来举个例子： 我们一、数据库的基本操作数据库是用来存储表的地方，在数据库开始创建的时候，要先创建好DATABA...
mysql sql语句大全
dibodang1423的博客
11-21 1931
1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwin...
MySQL常用SQL查询语句
漫游学海之旅
12-17 938
1 查询数据库中表个数 SELECT count(*) TABLES，table_schema FROM information_schema.TABLES WHERE table_schema='对应的数据库名称' GROUP BY table_schema   说明：count() 函数用于计算数目，后面的 TABLES , table_schema 是返回后的表名，
MySQL数据库入门与常用SQL语句详解
"MySQL数据库介绍及常用SQL语句（整理） MySQL是一个开源且免费的关系型数据库管理系统，常用于中小型项目，由Oracle公司维护。它提供了一种高效、可靠的方式来存储和管理数据。相比于非关系型数据库，如Redis，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值