Skip to content

数据库 SQL 脚本相关

1、字段增删改

新增字段

SQL
ALTER TABLE [表名] ADD [字段名] varchar(100) NULL COMMENT '注释说明' AFTER [指定字段名位于其后];

修改字段

SQL
ALTER TABLE [表名] CHANGE [原字段名] [新字段名] varchar(100) NULL COMMENT '注释说明' AFTER [指定字段名位于其后];

删除字段

SQL
ALTER TABLE [表名] DROP [字段名];

2、查看指定表的字段说明、类型、约束

SQL
select
  column_name '字段名',
  column_comment '字段说明',
  column_type '字段类型',
  column_key '约束'
from information_schema.columns
where table_schema = '数据库名'
and table_name = '表名';

3、容量查询

SQL
-- 查看指定数据库各表容量大小
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 = '数据库名'
order by table_rows desc,data_length desc, index_length desc;

-- 查看指定数据库容量大小
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 = '数据库名';

-- 查看所有数据库容量大小
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
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

-- 查看所有数据库各表容量大小
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
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

4、分组查询取最大日期的记录

SQL
select [分组字段],[日期],[字段1],[字段2]
from [表名] t
where t.[日期] =
(
	select max([日期])
	from [表名]
	where [分组字段] = t.[分组字段]
)
group by t.[分组字段];

5、查询当天、最近一天、最近一周、本月、上一月数据

SQL
-- 今天
select * from [表名] where to_days([时间字段名]) = to_days(now());

-- 昨天
select * from [表名] where to_days(now()) - to_days([时间字段名]) <= 1

-- 7天
select * from [表名] where date_sub(curdate(), interval 7 day) <= date([时间字段名])

-- 近30天
select * from [表名] where date_sub(curdate(), interval 30 day) <= date([时间字段名])

-- 本月
select * from [表名] where date_format([时间字段名], '%Y%m' ) = date_format(curdate(), '%Y%m')

-- 上一月
select * from [表名] where period_diff(date_format(now(), '%Y%m'), date_format([时间字段名], '%Y%m')) = 1
你觉得这篇文章怎么样?
  • 0
  • 0
  • 0
  • 0
  • 0
  • 0
评论
  • 按正序
  • 按倒序
  • 按热度