MySQL管理一些基础SQL语句
时间：2021-07-01 10:21:17
帮助过：2人阅读
1、进入information_schema 数据库（存放了其他的数据库的信息）
2 use information_schema;
3
4 2、查询所有数据的大小：
5 select concat(
round(
sum(data_length
/1024/1024),
2),
‘MB‘)
as data
from information_schema.tables;
6
7 3、查看指定数据库的大小：
8 比如查看数据库home的大小
9 select concat(
round(
sum(data_length
/1024/1024),
2),
‘MB‘)
as data
from tables
where table_schema
=‘home‘;
10 查看数据所占的空间大小
11 SELECT CONCAT(
ROUND(
SUM(data_length)
/(
1024*1024),
2)
+ ROUND(
SUM(index_length)
/(
1024*1024),
2),
‘MB‘)
AS ‘DB Size‘
12 FROM information_schema.`TABLES`
WHERE table_schema
=‘dbname‘
13
14
15 4、查看指定数据库的某个表的大小
16 比如查看数据库home中 members 表的大小
17 select concat(
round(
sum(data_length
/1024/1024),
2),
‘MB‘)
as data
from tables
where table_schema
=‘home‘ and table_name
=‘members‘;
18 select concat(
round(
sum(data_length
/1024/1024),
2),
‘MB‘)
as data
from information_schema.tables
where table_schema
=‘dbname‘ and table_name
=‘t_msg_app_new‘;
19
20 5、查看指定数据库的表的大小 大到小
21 SELECT table_name,CONCAT(
ROUND((data_length
/1024/1024),
2),
‘MB‘)
AS DATA
FROM TABLES
WHERE table_schema
=‘dbname‘ ORDER BY data_length
DESC;
22
23 6、查看指定数据库表的创建时间，更新时间。
24 SELECT table_name,create_time,update_time
FROM information_schema.`TABLES`
WHERE table_schema
=‘dbname‘ ORDER BY update_time
DESC
25
26 SELECT table_name,table_rows,create_time,update_time
27 FROM information_schema.`TABLES`
WHERE table_schema
=‘dbname‘ and table_name
=‘tablename‘ ORDER BY update_time
DESC
28 7、
查看所有存储引擎为MyISAM的表
29 SELECT * FROM `information_schema`.`TABLES`
WHERE TABLE_SCHEMA
=‘dbname‘ AND `engine`
=‘MyISAM‘
30
31 8、
查询表中的所有列
32 SELECT GROUP_CONCAT(column_name)
AS namestr
33 FROM information_schema.`COLUMNS`
WHERE table_name
=‘tablename‘ ;
34
35 9、
查询表中的所有列(拼接插入INSERT SQL)
36 SELECT CONCAT(
‘INSERT INTO ‘,table_name,
‘(‘,GROUP_CONCAT(column_name),
‘)VALUES()‘)
AS namestr
37 FROM information_schema.`COLUMNS`
WHERE table_schema
=‘dbname‘ AND table_name
=‘tablename‘ ;
38
39 10、#查看是否开启事件
40 select @@event_scheduler
MySQL管理一些基础SQL语句
标签：
