show databases show tables desc tableName ... 查出来后装到树里。
利用 information_schema 数据字典
Chapter 20. INFORMATION_SCHEMA Tables Table of Contents20.1. The INFORMATION_SCHEMA SCHEMATA Table 20.2. The INFORMATION_SCHEMA TABLES Table 20.3. The INFORMATION_SCHEMA COLUMNS Table 20.4. The INFORMATION_SCHEMA STATISTICS Table 20.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table 20.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table 20.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table 20.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table 20.9. The INFORMATION_SCHEMA CHARACTER_SETS Table 20.10. The INFORMATION_SCHEMA COLLATIONS Table 20.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table 20.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table 20.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table 20.14. The INFORMATION_SCHEMA ROUTINES Table 20.15. The INFORMATION_SCHEMA VIEWS Table 20.16. The INFORMATION_SCHEMA TRIGGERS Table 20.17. The INFORMATION_SCHEMA PLUGINS Table 20.18. The INFORMATION_SCHEMA ENGINES Table 20.19. The INFORMATION_SCHEMA PARTITIONS Table 20.20. The INFORMATION_SCHEMA EVENTS Table 20.21. The INFORMATION_SCHEMA FILES Table 20.22. The INFORMATION_SCHEMA PROCESSLIST Table 20.23. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table 20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables 20.25. The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables 20.26. The INFORMATION_SCHEMA PROFILING Table 20.27. Other INFORMATION_SCHEMA Tables 20.28. Extensions to SHOW Statements INFORMATION_SCHEMA provides access to database metadata. Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog. INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them. Here is an example of a statement that retrieves information from INFORMATION_SCHEMA: mysql> SELECT table_name, table_type, engine -> FROM information_schema.tables -> WHERE table_schema = 'db5' -> ORDER BY table_name DESC; +------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | v56 | VIEW | NULL | | v3 | VIEW | NULL | | v2 | VIEW | NULL | | v | VIEW | NULL | | tables | BASE TABLE | MyISAM | | t7 | BASE TABLE | MyISAM | | t3 | BASE TABLE | MyISAM | | t2 | BASE TABLE | MyISAM | | t | BASE TABLE | MyISAM | | pk | BASE TABLE | InnoDB | | loop | BASE TABLE | MyISAM | | kurs | BASE TABLE | MyISAM | | k | BASE TABLE | MyISAM | | into | BASE TABLE | MyISAM | | goto | BASE TABLE | MyISAM | | fk2 | BASE TABLE | InnoDB | | fk | BASE TABLE | InnoDB | +------------+------------+--------+ 17 rows in set (0.01 sec)Explanation: The statement requests a list of all the tables in database db5, in reverse alphabetical order, showing just three pieces of information: the name of the table, its type, and its storage engine. Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL. The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the various SHOW statements that MySQL supports (SHOW DATABASES, SHOW TABLES, and so forth). Using SELECT has these advantages, compared to SHOW: It conforms to Codd's rules. That is, all access is done on tables. Nobody needs to learn a new statement syntax. Because they already know how SELECT works, they only need to learn the object names. The implementor need not worry about adding keywords. There are millions of possible output variations, instead of just one. This provides more flexibility for applications that have varying requirements about what metadata they need. Migration is easier because every other DBMS does it this way. However, because SHOW is popular with MySQL employees and users, and because it might be confusing were it to disappear, the advantages of conventional syntax are not a sufficient reason to eliminate SHOW. In fact, along with the implementation of INFORMATION_SCHEMA, there are enhancements to SHOW as well. These are described in Section 20.28, “Extensions to SHOW Statements”. There is no difference between the privileges required for SHOW statements and those required to select information from INFORMATION_SCHEMA. In either case, you have to have some privilege on an object in order to see information about it. The implementation for the INFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11 Schemata. Our intent is approximate compliance with SQL:2003 core feature F021 Basic information schema. Users of SQL Server 2000 (which also follows the standard) may notice a strong similarity. However, MySQL has omitted many columns that are not relevant for our implementation, and added columns that are MySQL-specific. One such column is the ENGINE column in the INFORMATION_SCHEMA.TABLES table. Although other DBMSs use a variety of names, like syscat or system, the standard name is INFORMATION_SCHEMA. The following sections describe each of the tables and columns that are in INFORMATION_SCHEMA. For each column, there are three pieces of information: “INFORMATION_SCHEMA Name” indicates the name for the column in the INFORMATION_SCHEMA table. This corresponds to the standard SQL name unless the “Res” field says “MySQL extension.” “SHOW Name” indicates the equivalent field name in the closest SHOW statement, if there is one. “Res” provides additional information where applicable. If this field is NULL, it means that the value of the column is always NULL. If this field says “MySQL extension,” the column is a MySQL extension to standard SQL. To avoid using any name that is reserved in the standard or in DB2, SQL Server, or Oracle, we changed the names of some columns ed “MySQL extension”. (For example, we changed COLLATION to TABLE_COLLATION in the TABLES table.) See the list of reserved words near the end of this article: http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5. The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8 where N is at least 64. MySQL uses the default collation for this character set (utf8_general_ci) for all searches, sorts, comparisons, and other string operations on such columns. If the default collation is not correct for your needs, you can force a suitable collation with a COLLATE clause (Section 9.1.6.1, “Using COLLATE in SQL Statements”). Each section indicates what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA, if there is such a statement. For SHOW statements that display information for the current database if you omit a FROM db_name clause, you can often select information for the current database by adding an AND TABLE_SCHEMA = CURRENT_SCHEMA() condition to the WHERE clause of a query that retrieves information from an INFORMATION_SCHEMA table. Note At present, there are some missing columns and some columns out of order. We are working on this and updating the documentation as changes are made. For answers to questions that are often asked concerning the INFORMATION_SCHEMA database, see Section A.7, “MySQL 5.0 FAQ — INFORMATION_SCHEMA”. 20.1. The INFORMATION_SCHEMA SCHEMATA Table A schema is a database, so the SCHEMATA table provides information about databases. INFORMATION_SCHEMA Name SHOW Name Res CATALOG_NAME NULL SCHEMA_NAME Database DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH NULL The following statements are equivalent: SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild']SHOW DATABASES [LIKE 'wild']
show tables
desc tableName
...
查出来后装到树里。
information_schema
数据字典
Table of Contents20.1. The INFORMATION_SCHEMA SCHEMATA Table
20.2. The INFORMATION_SCHEMA TABLES Table
20.3. The INFORMATION_SCHEMA COLUMNS Table
20.4. The INFORMATION_SCHEMA STATISTICS Table
20.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table
20.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
20.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
20.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
20.9. The INFORMATION_SCHEMA CHARACTER_SETS Table
20.10. The INFORMATION_SCHEMA COLLATIONS Table
20.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
20.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
20.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
20.14. The INFORMATION_SCHEMA ROUTINES Table
20.15. The INFORMATION_SCHEMA VIEWS Table
20.16. The INFORMATION_SCHEMA TRIGGERS Table
20.17. The INFORMATION_SCHEMA PLUGINS Table
20.18. The INFORMATION_SCHEMA ENGINES Table
20.19. The INFORMATION_SCHEMA PARTITIONS Table
20.20. The INFORMATION_SCHEMA EVENTS Table
20.21. The INFORMATION_SCHEMA FILES Table
20.22. The INFORMATION_SCHEMA PROCESSLIST Table
20.23. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
20.25. The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
20.26. The INFORMATION_SCHEMA PROFILING Table
20.27. Other INFORMATION_SCHEMA Tables
20.28. Extensions to SHOW Statements
INFORMATION_SCHEMA provides access to database metadata. Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog. INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them. Here is an example of a statement that retrieves information from INFORMATION_SCHEMA: mysql> SELECT table_name, table_type, engine
-> FROM information_schema.tables
-> WHERE table_schema = 'db5'
-> ORDER BY table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| v56 | VIEW | NULL |
| v3 | VIEW | NULL |
| v2 | VIEW | NULL |
| v | VIEW | NULL |
| tables | BASE TABLE | MyISAM |
| t7 | BASE TABLE | MyISAM |
| t3 | BASE TABLE | MyISAM |
| t2 | BASE TABLE | MyISAM |
| t | BASE TABLE | MyISAM |
| pk | BASE TABLE | InnoDB |
| loop | BASE TABLE | MyISAM |
| kurs | BASE TABLE | MyISAM |
| k | BASE TABLE | MyISAM |
| into | BASE TABLE | MyISAM |
| goto | BASE TABLE | MyISAM |
| fk2 | BASE TABLE | InnoDB |
| fk | BASE TABLE | InnoDB |
+------------+------------+--------+
17 rows in set (0.01 sec)Explanation: The statement requests a list of all the tables in database db5, in reverse alphabetical order, showing just three pieces of information: the name of the table, its type, and its storage engine. Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL. The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the various SHOW statements that MySQL supports (SHOW DATABASES, SHOW TABLES, and so forth). Using SELECT has these advantages, compared to SHOW: It conforms to Codd's rules. That is, all access is done on tables. Nobody needs to learn a new statement syntax. Because they already know how SELECT works, they only need to learn the object names. The implementor need not worry about adding keywords. There are millions of possible output variations, instead of just one. This provides more flexibility for applications that have varying requirements about what metadata they need. Migration is easier because every other DBMS does it this way. However, because SHOW is popular with MySQL employees and users, and because it might be confusing were it to disappear, the advantages of conventional syntax are not a sufficient reason to eliminate SHOW. In fact, along with the implementation of INFORMATION_SCHEMA, there are enhancements to SHOW as well. These are described in Section 20.28, “Extensions to SHOW Statements”. There is no difference between the privileges required for SHOW statements and those required to select information from INFORMATION_SCHEMA. In either case, you have to have some privilege on an object in order to see information about it. The implementation for the INFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11 Schemata. Our intent is approximate compliance with SQL:2003 core feature F021 Basic information schema. Users of SQL Server 2000 (which also follows the standard) may notice a strong similarity. However, MySQL has omitted many columns that are not relevant for our implementation, and added columns that are MySQL-specific. One such column is the ENGINE column in the INFORMATION_SCHEMA.TABLES table. Although other DBMSs use a variety of names, like syscat or system, the standard name is INFORMATION_SCHEMA. The following sections describe each of the tables and columns that are in INFORMATION_SCHEMA. For each column, there are three pieces of information: “INFORMATION_SCHEMA Name” indicates the name for the column in the INFORMATION_SCHEMA table. This corresponds to the standard SQL name unless the “Res” field says “MySQL extension.” “SHOW Name” indicates the equivalent field name in the closest SHOW statement, if there is one. “Res” provides additional information where applicable. If this field is NULL, it means that the value of the column is always NULL. If this field says “MySQL extension,” the column is a MySQL extension to standard SQL. To avoid using any name that is reserved in the standard or in DB2, SQL Server, or Oracle, we changed the names of some columns ed “MySQL extension”. (For example, we changed COLLATION to TABLE_COLLATION in the TABLES table.) See the list of reserved words near the end of this article: http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5. The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8 where N is at least 64. MySQL uses the default collation for this character set (utf8_general_ci) for all searches, sorts, comparisons, and other string operations on such columns. If the default collation is not correct for your needs, you can force a suitable collation with a COLLATE clause (Section 9.1.6.1, “Using COLLATE in SQL Statements”). Each section indicates what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA, if there is such a statement. For SHOW statements that display information for the current database if you omit a FROM db_name clause, you can often select information for the current database by adding an AND TABLE_SCHEMA = CURRENT_SCHEMA() condition to the WHERE clause of a query that retrieves information from an INFORMATION_SCHEMA table. Note
At present, there are some missing columns and some columns out of order. We are working on this and updating the documentation as changes are made. For answers to questions that are often asked concerning the INFORMATION_SCHEMA database, see Section A.7, “MySQL 5.0 FAQ — INFORMATION_SCHEMA”. 20.1. The INFORMATION_SCHEMA SCHEMATA Table
A schema is a database, so the SCHEMATA table provides information about databases. INFORMATION_SCHEMA Name SHOW Name Res
CATALOG_NAME NULL
SCHEMA_NAME Database
DEFAULT_CHARACTER_SET_NAME
DEFAULT_COLLATION_NAME
SQL_PATH NULL The following statements are equivalent: SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
[WHERE SCHEMA_NAME LIKE 'wild']SHOW DATABASES
[LIKE 'wild']