Home >Database >Mysql Tutorial >How to query how many tables a database has in mysql
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 the SHOW TABLES
statement 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 as mysql
) Log in to the MySQL database server
Use the USE database name
statement to switch to a specific database.
Use the SHOW TABLES
command.
The following illustrates the syntax of the MySQL SHOW TABLES
command:
SHOW TABLES;
The following example illustrates how to column Export all tables in the yiibaidb
database.
Step 1 - Connect to MySQL database server:
C:\Users\Administrator>mysql -u root -p
Step 2 - Switch to yiibaidb
database:
mysql> USE yiibaidb; Database changed mysql>
Step 3 - Display all tables in yiibaidb
database:
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 TABLES
command can display that the table is a base table Or the view. To include the table type in the results, use the SHOW TABLES
statement 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 at yiibaidb
Create a view named view_contacts
in the database, which includes the first name, last name and phone number from the employees
and customers
tables.
CREATE VIEW view_contacts AS SELECT lastName, firstName, extension as phone FROM employees UNION SELECT contactFirstName, contactLastName, phone FROM customers;
Now, execute the query SHOW FULL TABLES
command:
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
, vps
and 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, the SHOW TABLES
command provides an option that allows the use of LIKE
operators or expression pairs in a WHERE
clause The returned tables are filtered as follows:
SHOW TABLES LIKE pattern; SHOW TABLES WHERE expression;
For example, to display all tables in the yiibaidb
database that begin with the letters p
, 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 to SHOW TABLES
Use the WHERE
clause in the statement to list all views in the yiibai
database -
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 the FROM
clause of the SHOW TABLES
statement 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 uses IN
Instead of the FROM
clause, 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 in SHOW TABLES
The 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!