查看内容

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

  • 2019-12-12 14:15
  • 新浦京计算机网络
  • Views

mysql查看表构造及原来就有索引消息

mysql表构造表空间和目录的询问

 

 

需求背景是给一个表名然后交由相应的表布局音讯及索引音信

1.查询表的协会信息

 

Sql代码  

常用的命令有如下:

desc tableName;  

 

  

desc tableName; desc employees.employees;

show columns from tableName;  

  www.2cto.com  

  

show columns from tableName; show COLUMNS from employees.employees;

describe tableName  

 

  下边包车型大巴结果回到的结果是大同小异的。

describe tableName; DESCRIBE employees.employees;

2 查询表的列音讯。

 

Sql代码  

那多个显示的结果都以同风姿洒脱的,突显表中filed,type,null,key,default及extra。

select * from   

 

information_schema.columns   

show create table tableName; show CREATE TABLE employees.employees;

where table_name='tableName';  

 

 3 查看库中持有的库

本条语句会显示这些表的建表语句。

Sql代码  

 

SELECT LOWER(schema_name) schema_name  

select * from columns where table_name='表名';
select * from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='employees';

FROM  

 

 information_schema.schemata  

以此呈现的结果就比较全了。

WHERE  

 

 schema_name NOT IN (  

接下去,来点更全的sql,这么些是用来一同mysql和orac数据字典的具有sql。

 'mysql',  

 

 'information_schema',  

mysql部分:

 'test'  

 

)  

01

新浦京棋牌手机版下载, 4 查询有个别库中具有的表

## 查看全体的库

Sql代码  

02

SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024) store_capacity  

SELECT

FROM  

03

 information_schema.TABLES  

    lower(schema_name) schema_name

WHERE table_schema = 'schema_name' AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'  

04  www.2cto.com  

 5 查看某三个库下某三个表的兼具字段

FROM

Sql代码  

05

SELECT  

    information_schema.schemata

    lower(column_name) column_name,  

06

    ordinal_position position,  

WHERE

    column_default dafault_value,  

07

    substring(is_nullable, 1, 1) nullable,  

    schema_name NOT IN (

    column_type data_type,  

08

    column_comment,  

        'mysql',

    character_maximum_length data_length,  

09

    numeric_precision data_precision,  

        'information_schema',

    numeric_scale data_scale  

10

FROM  

        'test',

    information_schema.COLUMNS  

11

WHERE  

        'search',

    table_schema = 'admin_portal'  

12

AND table_name = 'ap_epiboly_task';  

        'tbsearch',

 

13

6  查看某一个库下某一张表的目录

        'sbtest',

 

14

Sql代码  

        'dev_ddl'

<strong>SELECT DISTINCT  

15

    lower(index_name) index_name,  

    )

    lower(index_type) type  

16

FROM  

 

    information_schema.statistics  

17

WHERE  

## 产看某八个库中的全部表

    table_schema = 'employees'  

18

AND table_name = 'employees';</strong>  

SELECT

 7 查看某三个库下某一个表的注释

19

 

    table_name,

Sql代码  

20

SELECT  

    create_time updated_at,

    table_comment comments  

21

FROM  

    table_type,

    information_schema.TABLES  

22

WHERE  

    ENGINE,

    table_schema = 'employees'  

23

AND table_name = 'employees';  

    table_rows num_rows,

 8

24  www.2cto.com  

 

    table_comment,

1.查看索引

25

 

    ceil(data_length / 1024 / 1024) store_capacity

(1)单位是GB

26

 

FROM

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; 

27

+------------------+ 

    information_schema.TABLES

| Total Index Size | 

28

+------------------+ 

WHERE

| 1.70 GB | 

29

+------------------+

    table_schema = 'employees'

 

30

(2)单位是MB

AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'

 

31

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'test';

 

 

32

   个中“database”为你所要查看的数据库

##翻开某三个库下某多个表的具有字段

 

33

2.查看表空间

SELECT

 

34

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size' 

    lower(column_name) column_name,

FROM information_schema.TABLES WHERE table_schema LIKE 'test'; 

35

+-----------------+ 

    ordinal_position position,

| Total Data Size | 

36

+-----------------+ 

    column_default dafault_value,

| 3.01 GB | 

37

+-----------------+

    substring(is_nullable, 1, 1) nullable,

 

38

3.翻看数据库中全部表的音信

    column_type data_type,

 SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', 

39

CONCAT(ROUND(table_rows/1000000,2),'M') AS 'Number of Rows', 

    column_comment,

CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') AS 'Data Size', 

40

CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') AS 'Index Size' , 

    character_maximum_length data_length,

CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'test'; 

41

1.查询表的社团信息Sql代码 desc tableName; show columns from tableName; describe tableName 上边包车型地铁结果回到的结果是同样...

    numeric_precision data_precision,

42

    numeric_scale data_scale

43

FROM

44

    information_schema.COLUMNS

45  www.2cto.com  

WHERE

46

    table_schema = 'employees'

47

AND table_name = 'employees';

48

 

49

 

50

## 查看某三个库下某一张表的目录

51

 

52

SELECT DISTINCT

53

    lower(index_name) index_name,

54

    lower(index_type) type

55

FROM

56

    information_schema.statistics

57

WHERE

58

    table_schema = 'employees'

59

AND table_name = 'employees';

60

 

61

## 查看某叁个库下某一张表的某三个目录

62

 

63

SELECT

64

    lower(column_name) column_name,

65

    seq_in_index column_position

66

FROM

67

    information_schema.statistics

68

WHERE

69

    table_schema = 'employees'

70

AND table_name = 'employees'

71

AND index_name = 'primary';

72

   www.2cto.com  

73

## 查看某二个库下某一个表的讲授

74

SELECT

75

    table_comment comments

76

FROM

77

    information_schema.TABLES

78

WHERE

79

    table_schema = 'employees'

80

AND table_name = 'employees';

81

 

82

## 查看某四个库下某贰个表的列的注释

83

SELECT

84

    lower(column_name) column_name,

85

    column_comment comments

86

FROM

87

    COLUMNS

88

WHERE

89

    table_schema = 'employees'

90

AND table_name = 'employees';

oracle部分:

  www.2cto.com  

 

001

#table structure:

002

SELECT

003

    lower(table_name) table_name,

004

    TEMPORARY,

005

    tablespace_name,

006

    num_rows,

007

    duration,

008

    'ORACLE' table_type,

009  www.2cto.com  

    partitioned,

010

    (

011

        SELECT

012

上一篇:没有了 下一篇:没有了