mysql一些常用查询语句

雪球干死黄旭东

于 2024-03-25 11:30:37 发布

阅读量372

点赞数 2

分类专栏： Y类分类_关系型数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/yycoolsam/article/details/137009258

版权

Y类分类_关系型数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

本文介绍了如何通过SQL查询在MySQL中获取执行中的进程列表，筛选特定操作，以及分析库和表的空间占用情况，包括数据、索引和总容量。

摘要由CSDN通过智能技术生成

版本:8.0

查询正在执行表或者使用者的执行信息

select t1.* from (select * from information_schema.`PROCESSLIST` ) t1 
WHERE `INFO` like "%sp_aqc_org_patent_m%" 
ORDER BY `TIME` DESC;

select t1.* from (select * from information_schema.`PROCESSLIST` ) t1 
WHERE `HOST` like "%.43:%" 
ORDER BY `TIME` DESC;

查询使用空间

#查询mysql所有库的占用磁盘大小
SELECT 
TABLE_SCHEMA as '数据库',
concat(data_size,' GB') as '数据容量(GB)',
concat(index_size,' GB') as '索引容量(GB)',
concat(data_sum,' GB') as '合计'
FROM (
	SELECT 
	*,
	truncate(data_size + index_size, 2) as 'data_sum'
	from 
	(
		select 
		TABLE_SCHEMA,
		truncate(sum(data_length)/1024/1024/1024,2) as 'data_size',
		truncate(sum(index_length)/1024/1024/1024,2) as 'index_size'
		from information_schema.tables
		group by TABLE_SCHEMA
	) t1
	ORDER BY data_sum desc
) t2;


#查询mysql指定库下表的占用磁盘空间大小
SELECT 
data_schema as '库名',
data_table as '表名',
concat(data_size,' GB') as '数据容量(GB)',
concat(index_size,' GB') as '索引容量(GB)',
concat(data_sum,' GB') as '合计(GB)' 
FROM (
	SELECT 
	 data_schema,
	 data_table,
	 data_size,
	 index_size,
	 round(data_size + index_size, 2) as 'data_sum'
	FROM (
		SELECT 
				table_schema as `data_schema`, 
				table_name AS `data_table`, 
				round(((data_length) / 1024 / 1024 / 1024), 2) as `data_size`, 
				round(((index_length) / 1024 / 1024 / 1024), 2) as `index_size`
		FROM information_schema.tables 
		WHERE table_schema = 'qhdata_support_credit_baidu_m'
	) t1
	ORDER BY data_sum DESC
) t2;

;
#查询库表对应的信息总条数
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024/1024, 2)) as '数据容量(GB)',
sum(truncate(index_length/1024/1024/1024, 2)) as '索引容量(GB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;