欧阳简之 > 常用SQL整理

热爱代码,对每一行代码保持一颗敬畏之心。

查询重复数据及重复次数

select mobile, count(0) as count from users group by mobile having count(0)>1;

AB表结构相同,B表数据更新到A表(更新数据)

update a inner join b on a.id=b.id set a.cpu = b.cpu, a.memory = b.memory;

AB表结构相同,B表数据更新到A表(插入数据)

insert into a.table select distinct * from b.table where id < 177390;

统计数据库 表大小

use information_schema;
select table_schema, concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema like 'tablename%' group by table_schema;

数据库结果用逗号拼接

select group_concat(id) from host where buy_time>'2018-01-01 00:00:00';  --单个字段
select group_concat(id, ',', ip) from servers where buy_time>'2018-01-01 00:00:00';  --多个字段

Mysql增加时间

select date_add(a.buy_time,interval 10 minute) as buy_time from servers;

新增mysql用户并授权

grant select, insert, update, delete, create, drop, index, alter, create temporary tables, lock tables, create view, show view, event, trigger on `db_name`.* to 'db_user'@'%' identified by 'password';

按年月周日统计

<!-- 按日查询 -->
select date_format(date_time,'%Y-%m-%d') as time, sum(money) money from users_money group by time;
<!-- 按月查询 -->
select date_format(date_time,'%Y-%m') as time, sum(money) money from users_money group by time;
<!-- 按年查询 -->
select date_format(date_time,'%Y') as time, sum(money) money from users_money group by time;
<!-- 按周查询 -->
select date_format(date_time,'%Y-%u') as time, sum(money) money from users_money group by time;

查找连续ID中断的ID

select id, num from
(
select id,1 as num from (select id from users_money where order by id asc) t where not exists (select 1 from users_money where id=t.id-1)
union
select id,2 as num from (select id from users_money where order by id asc) t where not exists (select 1 from users_money where id=t.id+1)
)T 
order by id desc limit 10;

查找连续ID中断的ID

select vnc_port+1 as vncport from
(select vnc_port from servers where orderStatus <> -2 and master_id = 42 order by vnc_port asc) t
where vnc_port>59005 and not exists (select 1 from servers where orderStatus <> -2 and master_id = 42 and vnc_port=t.vnc_port+1) limit 11;

tagged by mysql