1. 通用

将表A数据批量插入表B

insert into co_saas_customer_add_1
  (id, tenant_id, user_id, external_user_id, state, add_time, created_at, updated_at, add_way)
select id,tenant_id,user_id,external_id,state,add_time,created_at,updated_at,add_way
from co_saas_customer_related_1
where id in (6251713, 6251819);



-- 1.如果2张表的字段一致，并且希望插入全部数据，可以用这种方法：
INSERT INTO 目标表 SELECT * FROM 来源表;

-- 2.比如要将 articles 表插入到 newArticles 表中，则是：
INSERT INTO newArticles SELECT * FROM articles;

-- 3.如果只希望导入指定字段，可以用这种方法：
INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;

统计查询出来数据的总数

select count(1)
from (SELECT distinct customer.external_id, score.score
      FROM score_statistics_record score
               left join customer_relate customer on score.union_id = customer.union_id
      where score.corp_id = 'ww4964b5a92f3ae1e1') as tablename2

union查询多个表

select * from bestseller_order_0 where order_ouid = 'AAFdAevUAAAto30AAfTjLUgF'
union
select * from bestseller_order_1 where order_ouid = 'AAFdAevUAAAto30AAfTjLUgF'
union
select * from bestseller_order_2 where order_ouid = 'AAFdAevUAAAto30AAfTjLUgF'

用表1的数据更新表2

update score_statistics_record t1
set score_old = t2.score
from score_statistics_record_history t2
where t1.union_id = t2.union_id
and t1.score != t2.score

2. PgSql

找出前缀为qr的所有数据库

SELECT
	CONCAT('drop table ',table_name,';')
FROM
	information_schema. TABLES
WHERE
	table_name LIKE 'qr_%';

查询每个表的大小

SELECT relname                                       AS Table,
pg_size_pretty(pg_total_relation_size(relid)) AS Size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

3. MySql

查询所有数据库中表大小

select table_schema                            as '数据库',
       table_name                              as '表名',
       table_rows                              as '记录数',
       truncate(data_length / 1024 / 1024, 2)  as '数据容量(MB)',
       truncate(index_length / 1024 / 1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查看dev库容量大小

select table_schema                                 as '数据库',
       sum(table_rows)                              as '记录数',
       sum(truncate(data_length / 1024 / 1024, 2))  as '数据容量(MB)',
       sum(truncate(index_length / 1024 / 1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema = 'dev';

查看dev库各表容量大小

select table_schema                            as '数据库',
       table_name                              as '表名',
       table_rows                              as '记录数',
       truncate(data_length / 1024 / 1024, 2)  as '数据容量(MB)',
       truncate(index_length / 1024 / 1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema = 'dev'
order by data_length desc, index_length desc;

模糊查询库下所有表名

SELECT table_name FROM information_schema.tables
WHERE table_schema = '库名' AND table_name LIKE '表名前缀%'

查询同一前缀的所有表的数据量

SELECT sum(table_rows)
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'prod_bestseller'
  and table_name like 'bestseller_order_%'

ORDER BY table_rows DESC;

按天统计

例如，每一天中每个小时网站的访问量，某路口每半个小时通过的车辆数量等

SELECT DATE(TimeStart) AS date, COUNT(*) AS num
FROM track
WHERE Flag = 0 AND Duration >= 300 
GROUP BY date
ORDER BY date;

按小时统计

SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS num
FROM track
WHERE Flag = 0 AND Duration >= 300
GROUP BY time
ORDER BY time;

结果如下：

按半小时统计

SELECT time, COUNT( * ) AS num 
FROM
	(
	SELECT Duration,
		DATE_FORMAT(
			concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 30 ) * 30 ),
			'%Y-%m-%d %H:%i' 
		) AS time 
	FROM tarck
	WHERE Flag = 0  AND Duration >= 300 
	) a 
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY time;

结果如下：

按N分钟统计

将上面的SQL语句稍微修改下，就可以实现按任意N分钟为时间片的分组统计，如按10分钟统计，先上代码：

SELECT time, COUNT( * ) AS num 
FROM
	(
	SELECT Duration,
		DATE_FORMAT(
			concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 10 ) * 10 ),
			'%Y-%m-%d %H:%i' 
		) AS time 
	FROM tarck
	WHERE Flag = 0  AND Duration >= 300 
	) a 
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY time;

基本思路： 将datetime类型的时间转化为相应时间片的时间，例如将‘2017-03-01 01:08:19’ 转化为‘2017-03-01 01:00:00’，然后group by即可。

按分钟统计

将按小时统计的SQL语句稍微修改下，就可以实现按分钟统计

SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:%i:00') AS time, COUNT(*) AS num
FROM track 
WHERE Flag = 0 AND Duration >= 300
GROUP BY time
ORDER BY time;

DATE_FORMAT功能强大，可以根据format字符串格式化date值，参考下面链接 http://www.w3school.com.cn/sql/func_date_format.asp