Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to check the table structure and existing index information by mysql". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "mysql how to view the table structure and existing index information" it!

View table structure and existing index information

The requirement background is to give a table name and then give the corresponding table structure information and index information.

Common commands are as follows:

Desc tableName; desc employees.employees

Www.2cto.com

Show columns from tableName; show COLUMNS from employees.employees

Describe tableName; DESCRIBE employees.employees

All three show the same results, showing filed,type,null,key,default and extra in the table.

Show create table tableName; show CREATE TABLE employees.employees

This statement displays the table-building statement of the table.

Select * from columns where table_name=' table name'

Select * from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='employees'

The result of this display is more complete.

Next, a bit more complete sql, which is used to synchronize all the sql of the mysql and orac data dictionaries.

Mysql section:

01

# # View all libraries

02

SELECT

03

Lower (schema_name) schema_name

04 www.2cto.com

FROM

05

Information_schema.schemata

06

WHERE

07

Schema_name NOT IN (

08

'mysql'

09

'information_schema'

ten

'test'

eleven

'search'

twelve

'tbsearch'

thirteen

'sbtest'

fourteen

'dev_ddl'

fifteen

)

sixteen

seventeen

# # look at all the tables in a database

eighteen

SELECT

nineteen

Table_name

twenty

Create_time updated_at

twenty-one

Table_type

twenty-two

ENGINE

twenty-three

Table_rows num_rows

24 www.2cto.com

Table_comment

twenty-five

Ceil (data_length / 1024 / 1024) store_capacity

twenty-six

FROM

twenty-seven

Information_schema.TABLES

twenty-eight

WHERE

twenty-nine

Table_schema = 'employees'

thirty

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

thirty-one

thirty-two

# # View all the fields of a table under a library

thirty-three

SELECT

thirty-four

Lower (column_name) column_name

thirty-five

Ordinal_position position

thirty-six

Column_default dafault_value

thirty-seven

Substring (is_nullable, 1,1) nullable

thirty-eight

Column_type data_type

thirty-nine

Column_comment

forty

Character_maximum_length data_length

forty-one

Numeric_precision data_precision

forty-two

Numeric_scale data_scale

forty-three

FROM

forty-four

Information_schema.COLUMNS

45 www.2cto.com

WHERE

forty-six

Table_schema = 'employees'

forty-seven

AND table_name = 'employees'

forty-eight

forty-nine

fifty

# # View the index of a table in a database

fifty-one

fifty-two

SELECT DISTINCT

fifty-three

Lower (index_name) index_name

fifty-four

Lower (index_type) type

fifty-five

FROM

fifty-six

Information_schema.statistics

fifty-seven

WHERE

fifty-eight

Table_schema = 'employees'

fifty-nine

AND table_name = 'employees'

sixty

sixty-one

# # View an index of a table in a database

sixty-two

sixty-three

SELECT

sixty-four

Lower (column_name) column_name

sixty-five

Seq_in_index column_position

sixty-six

FROM

sixty-seven

Information_schema.statistics

sixty-eight

WHERE

sixty-nine

Table_schema = 'employees'

seventy

AND table_name = 'employees'

seventy-one

AND index_name = 'primary'

seventy-two

Www.2cto.com

seventy-three

# # View the comments of a table under a certain library

seventy-four

SELECT

seventy-five

Table_comment comments

seventy-six

FROM

seventy-seven

Information_schema.TABLES

seventy-eight

WHERE

seventy-nine

Table_schema = 'employees'

eighty

AND table_name = 'employees'

eighty-one

eighty-two

# # View the comments of the columns of a table under a certain library

eighty-three

SELECT

eighty-four

Lower (column_name) column_name

eighty-five

Column_comment comments

eighty-six

FROM

eighty-seven

COLUMNS

eighty-eight

WHERE

eighty-nine

Table_schema = 'employees'

ninety

AND table_name = 'employees'

Oracle section:

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

Ceil (sum (bytes) / 1024 / 1024)

013

FROM

014

Dba_segments b

015

WHERE

016

A. OWNER = b. OWNER

017

AND a.table_name = b.segment_name

018

) AS store_capacity

019

FROM

020

Dba_tables a

021

WHERE

022

OWNER =?

023

AND table_name NOT LIKE 'TMP%'

024

025

SELECT

026

Lower (column_name) column_name

027

Column_id position

028

Data_type

029

Data_length

030

Data_precision

031

Data_scale

032

Nullable

033

Data_default default_value

034 www.2cto.com

Default_length

035

FROM

036

Dba_tab_columns

037

WHERE

038

OWNER =?

039

AND table_name =?

040

041

# index

042

SELECT

043

Lower (index_name) index_name

044

Index_type type

045

FROM

046

Dba_indexes

047

WHERE

048

OWNER =?

049

AND table_name =?

050

AND index_name NOT LIKE 'SYS_IL%'

051

052

SELECT

053

Lower (column_name) column_name

054

Column_position

055

Descend

056

FROM

057

Dba_ind_columns

058

WHERE

059

Table_owner =?

060

AND table_name =?

061

AND index_name =?

062

Www.2cto.com

063

# collect description

064

SELECT

065

Comments

066

FROM

067

Dba_tab_comments

068

WHERE

069

OWNER =?

070

AND table_name =?

071

072

SELECT

073

Lower (column_name) column_name

074

Comments

075

FROM

076

Dba_col_comments

077

WHERE

078

OWNER =?

079

AND table_name =?

080

081

# database

082

SELECT

083

Lower (username) username

084

FROM

085

Dba_users

086

WHERE

087

Username NOT IN (

088

'STDBYPERF'

089

'READONLY'

090

'APPQOSSYS'

091

'ANYSQL'

092

'DBFLASH'

093

'SYS'

094

'SYSTEM'

095

'MONITOR'

096

'TBSEARCH'

097

'MANAGER'

098 www.2cto.com

'SYSMAN'

099

'EXFSYS'

one hundred

'WMSYS'

one hundred and one

'DIP'

one hundred and two

'TSMSYS'

one hundred and three

'ORACLE_OCM'

one hundred and four

'OUTLN'

one hundred and five

'DBSNMP'

one hundred and six

'PERFSTAT'

one hundred and seven

'SEARCH'

one hundred and eight

'TOOLS'

one hundred and nine

'TBDUMP'

one hundred and ten

'DMSYS'

one hundred and eleven

'XDB'

one hundred and twelve

'ANONYMOUS'

one hundred and thirteen

'DEV_DDL'

one hundred and fourteen

);

one hundred and fifteen

one hundred and sixteen

# segsize

one hundred and seventeen

SELECT

one hundred and eighteen

Round (sum (bytes) / 1024 / 1024, 0) mbytes

one hundred and nineteen

FROM

120 www.2cto.com

Dba_segments

one hundred and twenty one

WHERE

one hundred and twenty two

OWNER =?

one hundred and twenty three

AND segment_name =?

You can refer to this series of articles about segements in oralce.

Http://book.51cto.com/art/201108/288137.htm

To sum up, the field information for viewing the database table in mysql is in information_schemal, which is a necessary sql for getting data dictionaries.

The statements in mysql in this article have been tested locally. In addition, the structure of oracle should also be familiar.

At this point, I believe that the "mysql how to view the table structure and existing index information" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.