Shulou(Shulou.com)05/31 Report--

This article mainly shows you "mysql how to query the database how many tables", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql how to query the database how many tables" this article.

Mysql query database how many tables: 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 the "SHOW TABLES;" statement to list all the 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, which lists all the tables in the database.

To list all tables in the MySQL database, follow these steps:

Log in to the MySQL database server using a MySQL client such as mysql

Use the USE database name statement to switch to a specific database.

Use the SHOW TABLES command.

The syntax of the MySQL SHOW TABLES command is explained below:

SHOW TABLES;MySQL SHOW TABLES example

The following example shows how to list all tables in the yiibaidb database.

Step 1-Connect to the MySQL database server:

C:\ Users\ Administrator > mysql-u root-p

Step 2-switch to the yiibaidb database:

Mysql > USE yiibaidb;Database changedmysql >

Step 3-display all tables in the 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

The SHOW TABLES command shows whether the table is a base table or a view. To include the table type in the result, use the SHOW TABLES statement, as follows-

SHOW FULL TABLES

Execute the above statement, as follows-

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 | | 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 create a view called view_contacts in the yiibaidb 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 UNIONSELECT 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 | | BASE TABLE | organization | VIEW | | payments | BASE TABLE | Price_logs | BASE TABLE | | productlines | BASE TABLE | | products | BASE TABLE | | saleperorder | VIEW | | user_change_logs | user_change_logs | | v_contacts | VIEW | | view_contacts | VIEW | | vps | VIEW | +-+-+ 25 rows in set

As you can see, vicious contactsviewviewcontactsetc. VPs are views (VIEW), while other tables are BASE TABLE tables.

For databases with many tables, it may be intuitive to display all tables at once.

Fortunately, the SHOW TABLES command provides an option to filter the returned table using the LIKE operator or an expression in the WHERE clause, as follows:

SHOW TABLES LIKE pattern;SHOW TABLES WHERE expression

For example, to display all tables in the yiibaidb database that begin with the letter 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 a table ending with a 'es' string, 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 shows how to use the WHERE clause in the SHOW TABLES 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 a database that is not currently in use. You can use the FROM clause of the SHOW TABLES statement to specify the database in which you want to display the table.

The following example shows how to display a table that starts 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 the IN instead of the FROM clause, as follows-

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

Note that if you do not have permissions for the base table or view, it does not appear in the result set of the SHOW TABLES command.

The above is all the contents of the article "how to query how many tables in the database by mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.