Mysql method to query how many tables a database has: 1. Use the MySQL client to log in to the MySQL database server; 2. Use the "USE database name" statement to switch to the specified database; 3. Use "SHOW TABLES; " statement lists all tables in the specified database.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
In mysql, you can use theSHOW TABLESstatement to query how many tables there are in the database. This statement can list all tables in the database.
To list all tables in a MySQL database, follow these steps:
Use a MySQL client (such asmysql) Log in to the MySQL database server
Use theUSE database namestatement to switch to a specific database.
Use theSHOW TABLEScommand.
The following illustrates the syntax of the MySQLSHOW TABLEScommand:
SHOW TABLES;
The following example illustrates how to column Export all tables in theyiibaidbdatabase.
Step 1- Connect to MySQL database server:
C:\Users\Administrator>mysql -u root -p
Step 2- Switch toyiibaidbdatabase:
mysql> USE yiibaidb; Database changed mysql>
Step 3- Display all tables inyiibaidbdatabase:
mysql> show tables; +--------------------+ | Tables_in_yiibaidb | +--------------------+ | aboveavgproducts | | article_tags | | bigsalesorder | | contacts | | customerorders | | customers | | departments | | employees | | employees_audit | | officeinfo | | offices | | offices_bk | | offices_usa | | orderdetails | | orders | | organization | | payments | | price_logs | | productlines | | products | | saleperorder | | user_change_logs | | v_contacts | | vps | +--------------------+ 24 rows in set
SHOW TABLEScommand can display that the table is a base table Or the view. To include the table type in the results, use theSHOW TABLESstatement as shown below -
SHOW FULL TABLES;
Execute the above statement as shown below-
mysql> SHOW FULL TABLES; +--------------------+------------+ | Tables_in_yiibaidb | Table_type | +--------------------+------------+ | aboveavgproducts | VIEW | | article_tags | BASE TABLE | | bigsalesorder | VIEW | | contacts | BASE TABLE | | customerorders | VIEW | | customers | BASE TABLE | | departments | BASE TABLE | | employees | BASE TABLE | | employees_audit | BASE TABLE | | officeinfo | VIEW | | offices | BASE TABLE | | offices_bk | BASE TABLE | | offices_usa | BASE TABLE | | orderdetails | BASE TABLE | | orders | BASE TABLE | | organization | VIEW | | payments | BASE TABLE | | price_logs | BASE TABLE | | productlines | BASE TABLE | | products | BASE TABLE | | saleperorder | VIEW | | user_change_logs | BASE TABLE | | v_contacts | VIEW | | vps | VIEW | +--------------------+------------+ 24 rows in set
We are atyiibaidbCreate a view namedview_contactsin the database, which includes the first name, last name and phone number from theemployeesandcustomerstables.
CREATE VIEW view_contacts AS SELECT lastName, firstName, extension as phone FROM employees UNION SELECT contactFirstName, contactLastName, phone FROM customers;
Now, execute the querySHOW FULL TABLEScommand:
mysql> SHOW FULL TABLES; +--------------------+------------+ | Tables_in_yiibaidb | Table_type | +--------------------+------------+ | aboveavgproducts | VIEW | | article_tags | BASE TABLE | | bigsalesorder | VIEW | | contacts | BASE TABLE | | customerorders | VIEW | | customers | BASE TABLE | | departments | BASE TABLE | | employees | BASE TABLE | | employees_audit | BASE TABLE | | officeinfo | VIEW | | offices | BASE TABLE | | offices_bk | BASE TABLE | | offices_usa | BASE TABLE | | orderdetails | BASE TABLE | | orders | BASE TABLE | | organization | VIEW | | payments | BASE TABLE | | price_logs | BASE TABLE | | productlines | BASE TABLE | | products | BASE TABLE | | saleperorder | VIEW | | user_change_logs | BASE TABLE | | v_contacts | VIEW | | view_contacts | VIEW | | vps | VIEW | +--------------------+------------+ 25 rows in set
You can see,v_contacts,view_contacts,vpsand so on are views (VIEW), while other tables are base tables (BASE TABLE).
For databases with many tables, it may be intuitive to display all tables at once.
Fortunately, theSHOW TABLEScommand provides an option that allows the use ofLIKEoperators or expression pairs in aWHEREclause The returned tables are filtered as follows:
SHOW TABLES LIKE pattern; SHOW TABLES WHERE expression;
For example, to display all tables in theyiibaidbdatabase that begin with the lettersp, use the following statement:
mysql> SHOW TABLES LIKE 'p%'; +-------------------------+ | Tables_in_yiibaidb (p%) | +-------------------------+ | payments | | price_logs | | productlines | | products | +-------------------------+ 4 rows in set
Or to display tables ending with the string 'es', you can use the following statement:
mysql> SHOW TABLES LIKE '%es'; +--------------------------+ | Tables_in_yiibaidb (%es) | +--------------------------+ | employees | | offices | | productlines | +--------------------------+ 3 rows in set
The following statement explains how toSHOW TABLESUse theWHEREclause in the statement to list all views in theyiibaidatabase -
mysql> SHOW FULL TABLES WHERE table_type = 'VIEW'; +--------------------+------------+ | Tables_in_yiibaidb | Table_type | +--------------------+------------+ | aboveavgproducts | VIEW | | bigsalesorder | VIEW | | customerorders | VIEW | | officeinfo | VIEW | | organization | VIEW | | saleperorder | VIEW | | v_contacts | VIEW | | view_contacts | VIEW | | vps | VIEW | +--------------------+------------+ 9 rows in set
Sometimes, you want to see tables in the database that are not currently in use. You can use theFROMclause of theSHOW TABLESstatement to specify the database in which the table is to be displayed.
The following example demonstrates how to display tables starting with 'time';
mysql> SHOW TABLES FROM mysql LIKE 'time%'; +---------------------------+ | Tables_in_mysql (time%) | +---------------------------+ | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | +---------------------------+ 5 rows in set
The following statement is equivalent to the above statement, but it usesINInstead of theFROMclause, as shown below -
mysql> SHOW TABLES IN mysql LIKE 'time%'; +---------------------------+ | Tables_in_mysql (time%) | +---------------------------+ | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | +---------------------------+ 5 rows in set
Please note that if you do not have permissions on the base table or view, it will not show up inSHOW TABLESThe results of the command are concentrated.
[Related recommendations:mysql video tutorial]
The above is the detailed content of How to query how many tables a database has in mysql. For more information, please follow other related articles on the PHP Chinese website!