In MySQL, view all base tables under the current database, excluding views

Environment Description :

• MySQL version: 5.5.57-log
• Operating system version: Red Hat Enterprise Linux Server Release 6.6 (Santiago)

Requirements Description :

View all tables in the currently used database, but do not query the view or show the view, that is, all the base tables are queried

the following SQL statements are implemented :

SELECT     table_name,    table_schema,    table_type,    ENGINEfrom    information_schema . TABLESWHERE= ' BASE TABLE ' and      =DATABASE ();

Description: the database () function will query out the "currently used DB test01" as the Table_schema filter value. Alternatively, you can put the test01 directly to the right of the Table_schema equals sign.

Query Result :

Note: The above query comes out of all test01 database tables or base tables, the results of the query does not include views.

If you use the following command to display all the tables, the view is included:

Show tables;

Query Result :

Note: In the 16 records that were queried, there were 2 views starting with V_, so the 14 base tables that were previously queried were correct, that is, all tables subtract the view, because the view is a virtual table, so it is displayed in the show Tables command.

Document creation Time: March 8, 2018 10:33:24

In MySQL, view all base tables under the current database, excluding views