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;

MySQL SHOW TABLES Example

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 yiibaidbCreate 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!