Metadata--mysql method of obtaining metadata

Metadata: Data, information that describes the data is also data, called metadata [MySQL] How to get metadataMySQL provides the following three ways to get the metadata for a database object: 1) Show Statement 2) query the related table from the INFORMATION_SCHEMA database (Information_schema is a virtual database, not physically present, It stores data in the database of Information) 3) command-line programs, such as Mysqlshow, mysqldump --Use the show statement to get the meta dataMySQL is the most common way to get metadata with a show statement, and here are a few typical uses: [Sql]show databases;  --List all databases show create DATABASE db_name; --View the Ddlshow tables of the database;  --List all tables of the default database show tables from db_name;  --Lists all tables for the specified database Show table status;  --View descriptive information for table Show table status from db_name;show create TABLE tbl_name;  --View table ddlshow columns from Tbl_name;  --View column information show index from Tbl_name; --View index information There are several show statements that can also have a like ' pattern ' to restrict the output range of the statement, where ' pattern ' allows the inclusion of '% ' and ' _ ' wildcard characters, such as the following statement, which returns all columns starting with S in the Domaininfo table: [  Sql]show columns from the domaininfo like ' s% ';  All show, like the one above, that supports such words can be rewritten as a WHERE clause, such as: [Sql]show columns from domaininfo where field= ' sysdomain '; Note: The effect of desc tbl_name and explain tbl_name is consistent with show columns from Tbl_name. --Querying related tables from the INFORMATION_SCHEMA databaseINFORMATION_SCHEMA is a system database that comes with MySQL, which stores all the metadata and gets the metadata you want through the related table in select. Compared to the show statement, it is cumbersome, but its benefits are standard SQL statements, more portable, and more flexible, and can get the information you really need through various expressions. The first two methods to get the metadata from the command line must be executed on the MySQL command line, while Mysqlshow and mysqldump provide a way to get the metabase from the OS command line, such as: [Plain]mysqlshow--List all databases Mysqlshow db_ Name--List all tables for a given database mysqlshow db_name tbl_name--List all columns for a given database table Mysqlshow--keys db_name tbl_name--list index information mysqlshow--status Db_name--Lists descriptive information for the database mysqldump lets you see the CREATE TABLE statement (just like the show CREATE TABLE statement), such as: [Sql]mysqldump--no-data db_name [Tbl_ Name] ... * Note: When viewing the table structure with mysqldump, be sure to add--no-data, otherwise you will see the data in the database table. query usage of--mysql database information Information_schemaStarting with MySQL 5, you can see one more system database INFORMATION_SCHEMA. Information_schema stores information for all other databases. Let's take a look at a few examples of using this database: <!--more-->1. Get basic information about INFORMATION_SCHEMA  information_schema is a virtual database that does not exist physically and obtains the appropriate information from other databases at select time.      mysql> show databases;      +--------------------+      | Database           |      +--------------------+      | Information_schema |      | Bugs               |      | MySQL              |      | SugarCRM           |      +--------------------+      4 rows in Set (0.00 sec)      The following is inform Tables in the Ation_schema database .      mysql> use information_schema;      mysql> Show tables;      +---------------------------------------+  &nbsP   | Tables_in_information_schema          |      +---------------------------------------+      | Character_sets                        |      | Collations                          |      | collation_character_set_applicability |      | COLUMNS                               |      | Column_privileges                     |      | Key_column_usage                      |      | PROFILING                           |      | ROUTINES     &NBsp                        |      | Schemata                            |      | Schema_privileges                     |      | STATISTICS                          |      | TABLES                                |      | Table_constraints                     |      | Table_privileges                      |      | TRIGGERS                            |      | User_privileges                       |      | Views                                 |      +---------------------------------------+        2 in Set (0.00 sec) . Tables with more than 1000 rows of data in the query table      The following statements can detect tables with more than 1000 rows of data           mysql> Select Concat (Table_schema, '. ', table_name) as Table_name,table_rows         -from Information_ Schema.tables where table_rows >         , ORDER by table_rows Desc;           +----------------------------------+------------+      | TABLE_NAME                       | Table_rows |      +----------------------------------+------------+    &NBSP; | Bugs.series_data                 |      52778 |      | Bugs.bugs_activity               |      26436 |      | Bugs.longdescs                   |      21473 |      | Bugs.email_setting               |       5370 |      | Bugs.attachments                 |       4714 |      | Bugs.attach_data                 |       4651 |      | bugs.cc                        |       4031 |      | Bugs.bugs                        | &nbsP     2190 |      | Bugs.namedqueries_link_in_footer |       1228 |      +----------------------------------+------------+      9 rows in Set (0.04 sec)    3. Query the table with no primary key      This example gives a list of all the tables without primary key.           SELECT CONCAT (T.table_name, ".", T.table_schema) as table_name      from INFORMATION_SCHEMA. TABLES T      left JOIN information_schema. Table_constraints TC      on t.table_schema = Tc.table_schema      and t.table_name = Tc.tab Le_name      and tc.constraint_type = ' PRIMARY KEY '      WHERE tc.constraint_name is NULL &n bsp;    and T.table_type = ' BASE table ';   4. Implementing the table's historical data information_schema putting the MySQL information_schema to use article implements a history database using th E information schema. The first Half of this article describes the requirements for the history database, and a generic design to implement it. The second half describes the stepwise construction of Code-generator that creates the SQL to construct and load the Histo Ry database. The code-generator is driven by the information schema and some features of the information schema be discussed in detail .    5. Query 5 max tables      mysql> SELECT concat (Table_schema, '. ', TABLE_NAME) TABLE_NAME,          Concat (Round (data_length/(1024*1024), 2), ' M ') data_length          → from Information_schema. TABLES          ORDER by Data_length DESC LIMIT 5;           +--------------------+-------------+      | TABLE_NAME         | Data_length |      +--------------------+-------------+      | Bugs.attach_data   | 706.89M     |      | Bugs.longdescs     | 3.45M       |      | bugs.bugs_activity | 1.45M       |      | Bugs.series_data   | 0.75M       |      | Bugs.attachments   | 0.51M       |      +--------------------+-------------+      5 rows in Set (0.05 sec)   

Metadata--mysql How to get metadata