这篇文章主要介绍了mysql中如何查询数据库中的表名问题,具有很好的参考价值,希望对大家有所帮助。
查询数据库中的表名
查询一个数据库中含有某关键词的表名
搜索一个数据库中包含一些关键字,词的表。
- SELECT
- TABLE_NAME
- FROM
- information_schema. TABLES
- WHERE
- table_schema = '数据库名'
- AND TABLE_NAME LIKE '%name%';
例:
- mysql> select table_name from information_schema.tables
- -> where table_schema = 'sakila'
- -> and table_name like "%film%";
- +----------------------------+
- | TABLE_NAME |
- +----------------------------+
- | film |
- | film_actor |
- | film_category |
- | film_list |
- | film_text |
- | nicer_but_slower_film_list |
- | sales_by_film_category |
- +----------------------------+
- 7 rows in set (0.00 sec)
查询数据库中所有的表
- show tables;
- -- use 数据库名
- USE sakila;
- SHOW TABLES;
同上面,where条件只查数据库名。还可以同时搜索多个数据库中的表。
- SELECT
- TABLE_NAME
- FROM
- information_schema. TABLES
- WHERE
- table_schema = '数据库名';
例:
- mysql> select table_name from information_schema.tables where table_schema = 'sakila';
- +----------------------------+
- | TABLE_NAME |
- +----------------------------+
- | actor |
- | actor_info |
- | address |
- | category |
- | city |
- | country |
- | customer |
- | customer_list |
- | film |
- | film_actor |
- | film_category |
- | film_list |
- | film_text |
- | inventory |
- | language |
- | nicer_but_slower_film_list |
- | payment |
- | rental |
- | sales_by_film_category |
- | sales_by_store |
- | staff |
- | staff_list |
- | store |
- +----------------------------+
- 23 rows in set (0.00 sec)
- mysql> select table_name from information_schema.tables where table_schema = 'sakila' or table_schema = 'test';
- +----------------------------+
- | TABLE_NAME |
- +----------------------------+
- | actor |
- | address |
- | category |
- | city |
- | country |
- | customer |
- | film |
- | film_actor |
- | film_category |
- | film_text |
- | inventory |
- | language |
- | payment |
- | rental |
- | staff |
- | store |
- | customer_list |
- | film_list |
- | nicer_but_slower_film_list |
- | staff_list |
- | sales_by_store |
- | sales_by_film_category |
- | actor_info |
- | employee |
- | test_alter |
- +----------------------------+
- 25 rows in set (0.01 sec)