mysql怎么查詢數據庫有多少表

mysql查詢數據庫有多少表的方法:1、使用MySQL客戶端登錄到MySQL數據庫服務器;2、使用“USE 數據庫名”語句切換到指定的數據庫中;3、使用“SHOW TABLES;”語句列出指定數據庫中的所有表即可。

mysql怎么查詢數據庫有多少表

本教程操作環境: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
喜歡就支持一下吧
點贊14 分享