mysql查詢數據庫有多少表的方法：1、使用MySQL客戶端登錄到MySQL數據庫服務器；2、使用“USE 數據庫名”語句切換到指定的數據庫中；3、使用“SHOW TABLES;”語句列出指定數據庫中的所有表即可。
本教程操作環境：windows7系統、mysql8版本、Dell G3電腦。
在mysql中，可以利用SHOW TABLES語句來查詢數據庫有多少表，該語句可以列出數據庫的所有表。
要在MySQL數據庫中列出所有表，請按照下列步驟操作：
-
使用MySQL客戶端(如mysql)登錄到MySQL數據庫服務器
-
使用?USE 數據庫名?語句切換到特定的數據庫。
-
使用SHOW TABLES命令。
下面說明了MySQL SHOW TABLES命令的語法：
SHOW?TABLES;
MySQL SHOW TABLES示例
以下示例說明如何列出yiibaidb數據庫中的所有表。
步驟1 – 連接到MySQL數據庫服務器：
C:UsersAdministrator>mysql?-u?root?-p
步驟2 -切換到yiibaidb數據庫：
mysql>?USE?yiibaidb; Database?changed mysql>
步驟3 – 顯示yiibaidb數據庫中的所有表：
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命令可顯示表是基表還是視圖。 要在結果中包含表類型，請使用SHOW TABLES語句，如下所示 –
SHOW?FULL?TABLES;
執行上面語句，如下所示 –
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
我們在yiibaidb數據庫中創建一個名為view_contacts的視圖，其中包括來自employees和customers表的名字，姓氏和電話。
CREATE?VIEW?view_contacts? AS? SELECT?lastName,?firstName,?extension?as?phone? FROM?employees? UNION SELECT?contactFirstName,?contactLastName,?phone? FROM?customers;
現在，執行查詢SHOW FULL TABLES命令：
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
您可以看到，v_contacts,view_contacts,vps等是視圖(VIEW)，而其它表則都是基表(BASE TABLE)。
對于具有很多表的數據庫，一次顯示所有表可能不免直觀。
幸運的是，SHOW TABLES命令提供了一個選項，允許使用LIKE運算符或WHERE子句中的表達式對返回的表進行過濾，如下所示：
SHOW?TABLES?LIKE?pattern; SHOW?TABLES?WHERE?expression;
例如，要顯示yiibaidb數據庫中以字母p開頭的所有表，請使用以下語句：
mysql>?SHOW?TABLES?LIKE?'p%'; +-------------------------+ |?Tables_in_yiibaidb?(p%)?| +-------------------------+ |?payments????????????????| |?price_logs??????????????| |?productlines????????????| |?products????????????????| +-------------------------+ 4?rows?in?set
或者顯示以’es‘字符串結尾的表，可使用以下語句：
mysql>?SHOW?TABLES?LIKE?'%es'; +--------------------------+ |?Tables_in_yiibaidb?(%es)?| +--------------------------+ |?employees????????????????| |?offices??????????????????| |?productlines?????????????| +--------------------------+ 3?rows?in?set
以下語句說明了如何在SHOW TABLES語句中使用WHERE子句列出yiibai數據庫中的所有視圖 –
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
有時，希望看到非當前使用的數據庫中的表。可以使用SHOW TABLES語句的FROM子句來指定要顯示表的數據庫。
以下示例演示如何顯示以’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
以下語句相當于上面的語句，但它使用IN而不是FROM子句，如下所示 –
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
請注意，如果您沒有基表或視圖的權限，則它不會顯示在SHOW TABLES命令的結果集中。
【相關推薦：mysql視頻教程】
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
喜歡就支持一下吧
相關推薦
