查看数据库 sakila 中包含多少张表
进入 sakila 数据库后, 我们一般使用 show tables; 可以看到其中的所有表. 这里认为视图(view)也算表.
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| 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)
可以看到有 23 张表. 但如果要直接获得 23 这个数字, 该如何查询?
Ans. 数据库的结构一般放在 informaiton_schema 数据库中. 使用下面的查询即可获得数据表的个数.
mysql> select count(table_name) from information_schema.tables where table_schema='sakila';
+-------------------+
| count(table_name) |
+-------------------+
| 23 |
+-------------------+
1 row in set (0.00 sec)
如果使用 SHOW FULL TABLES; 则会显示表的具体属性, 是基础表(BASE TABLE) 还是试图(VIEW).
mysql> show full tables;
+----------------------------+------------+
| Tables_in_sakila | Table_type |
+----------------------------+------------+
| actor | BASE TABLE |
| actor_info | VIEW |
| address | BASE TABLE |
| category | BASE TABLE |
| city | BASE TABLE |
| country | BASE TABLE |
| customer | BASE TABLE |
| customer_list | VIEW |
| film | BASE TABLE |
| film_actor | BASE TABLE |
| film_category | BASE TABLE |
| film_list | VIEW |
| film_text | BASE TABLE |
| inventory | BASE TABLE |
| language | BASE TABLE |
| nicer_but_slower_film_list | VIEW |
| payment | BASE TABLE |
| rental | BASE TABLE |
| sales_by_film_category | VIEW |
| sales_by_store | VIEW |
| staff | BASE TABLE |
| staff_list | VIEW |
| store | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.05 sec)
因此, 如果要列出 sakila 中含有视图的个数, 则使用下面的语句.
mysql> select count(table_name) from information_schema.tables where table_schema='sakila' and table_type='view';
+-------------------+
| count(table_name) |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)