mysql一些常用查询语句_mysql 查询占用语句-优快云博客

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

文章目录

MYSQL

版本: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;

CTE迭代计算股权穿透

投资关系表

 CREATE TABLE if NOT EXISTS db_test.tmp_org_invest_dtl (
 `rid` int NOT NULL AUTO_INCREMENT COMMENT '行ID',
 `org_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '投资单位id',
 `inv_org_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '被投资主体单位id',
 `invest` decimal(20, 8) DEFAULT NULL COMMENT '投资比重',
 `source_table` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据来源表',
 PRIMARY KEY (`rid`) USING BTREE,
 KEY `idx_01` (`org_id`) USING BTREE,
 KEY `idx_02` (`inv_org_id`) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='单位对外投资关系明细表';

设置CTE参数

SET group_concat_max_len = 100000;  -- 设置最大长度为10000字符
SET SESSION cte_max_recursion_depth = 1000; -- 最大递归深度，默认1000

CTE语句

WITH RECURSIVE Tree AS (
    -- 锚点成员：选择根节点
    SELECT 
        t1.org_id, 
        t1.inv_org_id, 
        t1.invest as invest, 
        t1.invest as path_invest,
        0 AS path_level, 
        CONCAT(
        CAST(t1.org_id AS CHAR(10000)), ';', CAST(t1.inv_org_id AS CHAR(10000)), 
        CONCAT('[', ifnull(t1.invest, ''), ',', t1.source_table COLLATE utf8mb4_general_ci, ']')
        ) as path
    FROM db_test.tmp_org_invest_dtl t1
    WHERE t1.org_id = "{org_id}"
        AND not isnull(t1.inv_org_id)
    UNION ALL
    -- 递归成员：加入子节点
    SELECT 
        t1.org_id, 
        t1.inv_org_id, 
        t1.invest as invest,
        ROUND(p.path_invest * t1.invest, 8) as path_invest,
        p.path_level + 1, 
        CONCAT(
        p.path, ';', t1.inv_org_id,
        CONCAT('[', ifnull(t1.invest, ''), ',', t1.source_table COLLATE utf8mb4_general_ci, ']')
        ) AS path
    FROM db_test.tmp_org_invest_dtl t1
    JOIN Tree p ON t1.org_id = p.inv_org_id
        WHERE not isnull(t1.inv_org_id)
        AND (
            ROUND(p.path_invest * t1.invest, 8) >= 0.00001 
            or isnull(p.path_invest) or isnull(t1.invest)
            )  -- 筛选有效数据
        AND not instr(p.path, t1.inv_org_id)  -- 防止循环处理
        AND p.path_level <= 9  -- 限制层级数量
)
SELECT org_id, inv_org_id, ROUND(invest * 100, 2) as invest_percent, ROUND(path_invest * 100, 2) as path_invest_percent, path_level, path
FROM Tree
ORDER BY path_level, org_id;

达梦数据库

版本:未知

查询模式列表

SELECT DISTINCT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'SCH'

创建用户

CREATE USER {user} IDENTIFIED BY "{password}";

创建模式

CREATE  SCHEMA {schema}  AUTHORIZATION {user_name};

提权

GRANT DBA TO {user};