mysql表结构表空间和索引的查询

页面导航:首页 > 数据库 > Mysql > mysql表结构表空间和索引的查询

mysql表结构表空间和索引的查询

来源: 作者: 时间:2016-02-21 09:46 【

mysql表结构表空间和索引的查询1.查询表的结构信息Sql代码 desc tableName; show columns from tableName; describe tableName 上面的结果返回的结果是一样的。2 查询表的列信息。Sql代码 select ...
mysql表结构表空间和索引的查询
 
1.查询表的结构信息
Sql代码  
desc tableName;  
  
show columns from tableName;  
  
describe tableName  
  上面的结果返回的结果是一样的。
2 查询表的列信息。
Sql代码  
select * from   
information_schema.columns   
where table_name='tableName';  
 3 查看库中所有的库
Sql代码  
SELECT LOWER(schema_name) schema_name  
FROM  
 information_schema.schemata  
WHERE  
 schema_name NOT IN (  
 'mysql',  
 'information_schema',  
 'test'  
)  
 4 查询某个库中所有的表
Sql代码  
SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024) store_capacity  
FROM  
 information_schema.TABLES  
WHERE table_schema = 'schema_name' AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'  
 5 查看某一个库下某一个表的所有字段
Sql代码  
SELECT  
    lower(column_name) column_name,  
    ordinal_position position,  
    column_default dafault_value,  
    substring(is_nullable, 1, 1) nullable,  
    column_type data_type,  
    column_comment,  
    character_maximum_length data_length,  
    numeric_precision data_precision,  
    numeric_scale data_scale  
FROM  
    information_schema.COLUMNS  
WHERE  
    table_schema = 'admin_portal'  
AND table_name = 'ap_epiboly_task';  
 
6  查看某一个库下某一张表的索引
 
Sql代码  
<strong>SELECT DISTINCT  
    lower(index_name) index_name,  
    lower(index_type) type  
FROM  
    information_schema.statistics  
WHERE  
    table_schema = 'employees'  
AND table_name = 'employees';</strong>  
 7 查看某一个库下某一个表的注释
 
Sql代码  
SELECT  
    table_comment comments  
FROM  
    information_schema.TABLES  
WHERE  
    table_schema = 'employees'  
AND table_name = 'employees';  
 8
 
1.查看索引
 
(1)单位是GB
 
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; 
+------------------+ 
| Total Index Size | 
+------------------+ 
| 1.70 GB | 
+------------------+
 
(2)单位是MB
 
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'test';
 
   其中“database”为你所要查看的
 
2.查看表空间
 
SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size' 
FROM information_schema.TABLES WHERE table_schema LIKE 'test'; 
+-----------------+ 
| Total Data Size | 
+-----------------+ 
| 3.01 GB | 
+-----------------+
 
3.查看数据库中所有表的信息
 SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', 
CONCAT(ROUND(table_rows/1000000,2),'M') AS 'Number of Rows', 
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') AS 'Data Size', 
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') AS 'Index Size' , 
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'test'; 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<