Mysql数据库相关笔记

SQL运维操作

连接数据库

mysql -h172.16.17.208 -uwanwan -pwanwan -P3306

数据库连接相关操作

#查询mysql连接
SELECT * FROM information_schema.processlist t;

#批量删除连接
SELECT concat('KILL ',id,';') FROM information_schema.processlist t;

查询数据库使用情况大小

SELECT
  ROUND(SUM(data_length / 1024 / 1024),2)    'Data Size in MB',
  ROUND(SUM(index_length / 1024 / 1024),2)    'Index Size in MB',
  ROUND(SUM((index_length + data_length) / 1024 / 1024),2)    'All Size in MB'
FROM information_schema.TABLES
WHERE table_schema = 'feedback';

查询某个库中所有表占用空间

SELECT
	TABLE_NAME,
	concat( TRUNCATE ( data_length / 1024 / 1024, 2 ), ' MB' ) AS 'data_size',
	concat( TRUNCATE ( index_length / 1024 / 1024, 2 ), ' MB' ) AS 'index_size' ,
  concat( TRUNCATE(SUM((index_length + data_length) / 1024 / 1024),2), ' MB' )    'All Size in MB'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'feedback' 
GROUP BY TABLE_NAME 
ORDER BY TRUNCATE(SUM((index_length + data_length) / 1024 / 1024),2) DESC;

查询所有数据库占用空间

SELECT
	TABLE_SCHEMA,
	concat( TRUNCATE ( sum( data_length ) / 1024 / 1024, 2 ), ' MB' ) AS 'data_size',
	concat( TRUNCATE ( sum( index_length ) / 1024 / 1024, 2 ), 'MB' ) AS 'index_size' ,
  concat( TRUNCATE(SUM((index_length + data_length) / 1024 / 1024),2), ' MB' )    'All Size in MB'
FROM information_schema.TABLES 
GROUP BY TABLE_SCHEMA 
ORDER BY TRUNCATE(SUM((index_length + data_length) / 1024 / 1024),2) DESC;

常用sql

查询所有表名

SELECT
table_name 表名,
table_comment 表注释,
table_rows 数据量
FROM information_schema.tables
WHERE table_schema = 'feedback'
AND table_comment LIKE '%工单%'
ORDER BY table_name;

查询指定表所有字段

SELECT
column_name 字段名,
column_comment 字段注释,
column_type 字段类型
FROM information_schema.columns
WHERE table_name='bad_eval_work_order'
AND table_schema='csfeedback';

查询所有表名和字段

SELECT
a.table_name 表名,
a.table_comment 表注释,
b.column_name 表字段,
b.column_type 字段类型,
b.column_comment 字段注释
FROM information_schema.tables a LEFT JOIN information_schema.columns b ON b.table_name = a.table_name 
WHERE 1=1
AND a.table_schema='csfeedback';