如何在MySQL中显示所有数据库，并为每个数据库显示所有表？
为此，您可以使用INFORMATION_SCHEMA。以下是语法-
select my_schema.SCHEMA_NAME, group_concat(tbl.TABLE_NAME) from information_schema.SCHEMATA my_schema left join information_schema.TABLES tbl on my_schema.SCHEMA_NAME=tbl.TABLE_SCHEMA group by my_schema.SCHEMA_NAME;
让我们实现上面的语法，以便显示MySQL中的所有数据库以及每个数据库-
mysql> select my_schema.SCHEMA_NAME, group_concat(tbl.TABLE_NAME) from information_schema.SCHEMATA my_schema left join information_schema.TABLES tbl on my_schema.SCHEMA_NAME=tbl.TABLE_SCHEMA group by my_schema.SCHEMA_NAME;
这将产生以下输出-
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SCHEMA_NAME | group_concat(tbl.TABLE_NAME) | +---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bothinnodbandmyisam | employee,gradedemo,student,student_information | | business | addconstraintdemo,addonedaydemo,autoincrementtozero,booleandemo,college,columnvaluenulldemo,countcolumns,datatoolongdemo,deletedemo,demoemptyandnull,distinctdemo1,employeetable,findandreplacedemo,foreigntabledemo,groupdemo1,indemo,intandbigint20demo,javadatetimedemo,likedemo,modifycolumndemo,multiplerecordwithvalues,nestedtransactiondemo,nulldemo,orderbydemo,primarytable1,randomintegerdemo,retrievefirst40characters,saveintotextfile,selectinwithbetweendemo,sortingdemo,studentrecordwithmyisam,tabledemo3,tblifdemo,textintonumberdemo,trailingandleadingdemo,tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt,uniqueconstrainttable,updatewithlimit,variableastablename,adddatetimewithother,agecalculationfromdatetime,autostoredate,chardemo,colortable,commaseperatedemo,countrycitydemo,datatypedemo,deleterecord,demoindex,dropindex,enumdemo,finddemo,functionindexdemo,groupt_concatdemo,indexingdemo,intandtinyint,keydemo,limitoffsetdemo,modifycolumnnamedemo,multiplerowsinsert,newstudent,nulldemo1,orderdemo,prim | | commandline | caseinsensitivedistinctdemo,insertmaxplus1demo,instructor,negativevaluetozerodemo | | customer-tracker | NULL | | customer_tracker_database | NULL | | customertracker | NULL | | database1 | NULL | | databasesample | NULL | | demo | mytable | | education | student,university | | hb_student_tracker | demotable194,demotable202,demotable210,demotable218,demotable227,reorderintegerexcept0,demotable195,demotable203,demotable211,demotable219,demotable228,student,demotable196,demotable204,demotable212,demotable220,demotable229,demotable197,demotable205,demotable213,demotable221,demotable230,demotable198,demotable206,demotable214,demotable223,demotable231,demotable199,demotable207,demotable215,demotable224,demotable232,demotable192,demotable200,demotable208,demotable216,demotable225,demotable233,demotable193,demotable201,demotable209,demotable217,demotable226,insertcurrentdate | | hello | NULL | | information_schema | COLUMNS,INNODB_CMP,INNODB_FIELDS,INNODB_FT_INDEX_TABLE,INNODB_TRX,PROCESSLIST,ST_SPATIAL_REFERENCE_SYSTEMS,VIEWS,ENGINES,INNODB_CMP_PER_INDEX,INNODB_FOREIGN,INNODB_INDEXES,INNODB_VIRTUAL,PROFILING,STATISTICS,EVENTS,INNODB_CMP_PER_INDEX_RESET,INNODB_FOREIGN_COLS,INNODB_METRICS,KEY_COLUMN_USAGE,REFERENTIAL_CONSTRAINTS,TABLE_CONSTRAINTS,CHARACTER_SETS,FILES,INNODB_CMP_RESET,INNODB_FT_BEING_DELETED,INNODB_TABLES,KEYWORDS,RESOURCE_GROUPS,TABLE_PRIVILEGES,COLLATION_CHARACTER_SET_APPLICABILITY,INNODB_BUFFER_PAGE,INNODB_CMPMEM,INNODB_ FT_CONFIG,INNODB_TABLESPACES,OPTIMIZER_TRACE,ROUTINES,TABLES,COLLATIONS,INNODB_BUFFER_PAGE_LRU,INNODB_CMPMEM_RESET,INNODB_FT_DEFAULT_STOPWORD,INNODB_TABLESPACES_BRIEF,PARAMETERS,SCHEMA_PRIVILEGES,TABLESPACES,COLUMN_PRIVILEGES,INNODB_BUFFER_POOL_STATS,INNODB_COLUMNS,INNODB_FT_DELETED,INNODB_TABLESTATS,PARTITIONS,SCHEMATA,TRIGGERS,COLUMN_STATISTICS,INNODB_CACHED_INDEXES,INNODB_DATAFILES,INNODB_FT_INDEX_CACHE,INNODB_TEMP_TABLE_INFO,PLUGINS,ST_GEOMETRY_COLUMNS,USER_PRIVILEGES | | instant_app | NULL | | javadatabase2 | NULL | | javasampledatabase | NULL | | login | NULL | | mybusiness | NULL | | mydatabase | NULL | | mysql | db,help_category,mytable,selectdistinct_countdemo,slave_worker_info,time_zone_transition,default_roles,help_keyword,password_history,selectnonnumericvalue,slow_log,time_zone_transition_type,employee_information,help_relation,plugin,selectrowdemo,table,user,bugdemo,engine_cost,help_topic,prioritydemo,server,tables_priv,changevaluefrom1toy,func,innodb_index_stats,procs_priv,server_cost,tempvariableassignment,columns_priv,general_log,innodb_table_stats,proxies_priv,servers,time_zone,component,global_grants,limitdemo,removenumericcharaters,slave_master_info,time_zone_leap_second,countoccurrencesdemo,gtid_executed,md5demo,role_edges,slave_relay_log_info,time_zone_name | | onetomanyrelationship | NULL | | onlinebookstore | NULL | | performance_schema | events_errors_summary_by_user_by_error,events_stages_summary_by_user_by_event_name,events_statements_summary_by_digest,events_transactions_history_long,events_waits_history_long,file_summary_by_event_name,memory_summary_by_host_by_event_name,persisted_variables,replication_connection_configuration,session_variables,socket_summary_by_instance,table_lock_waits_summary_by_table,accounts,events_errors_summary_global_by_error,events_stages_summary_global_by_event_name,events_statements_summary_by_host_by_event_name,events_transactions_summary_by_account_by_event_name,events_waits_summary_by_account_by_event_name,file_summary_by_instance,memory_summary_by_thread_by_event_name,prepared_statements_instances,replication_connection_status,setup_actors,status_by_account,threads,cond_instances,events_stages_current,events_statements_current,events_statements_summary_by_program,events_transactions_summary_by_host_by_event_name,events_waits_summary_by_host_by_event_name,global_status,memory_summary_by_user_by_event_name,re | | rdb | boy,girl | | sample | accumulateddemo,addtotaltimedemo,autoincrementdemo,backtick_symboldemo,calculateaveragedemo,changecolumnname,columndoesnotexists,contains_capital_letterdemo,countbooleanfielddemo,crc32demo,decimal_demo,deletefrombasetabledemo,demotable10,demotable108,demotable115,demotable122,demotable13,demotable139,demotable146,demotable153,demotable160,demotable168,demotable175,demotable183,demotable190,demotable23,demotable240,demotable248,demotable255,demotable263,demotable28,demotable35,demotable43,demotable50,demotable58,demotable65,demotable72,demotable8,demotable87,demotable94,displayonlydistinctvalue,employeeinformation,excludecertaincolumnsdemo,findinsetdemo,firstweekofmonth,getfileextensiondemo,groupbydemo,idcolumnadd1demo,insert_prevent,javadatedemo,limitwithstoredprocedure,mergingselectdemo,new_viewdemo,old_tabledemo2,orderbyfield,pipeinsertdemo,primarykeydemo,recievedatefromtable,removeuniquenessconstraint,returndemo,rowsusinglimit,secondtable,selectdistinctdemo,selectrecordsdemo,showsuminlastrowdemo,stoproundi | | sampledatabase | NULL | | schemasample | NULL | | slotgame | NULL | | sys | host_summary_by_file_io,io_by_thread_by_latency,memory_by_user_by_current_bytes,schema_object_overview,session_ssl_status,user_summary,wait_classes_global_by_latency,x$host_summary_by_statement_latency,x$io_global_by_wait_by_bytes,x$processlist,x$schema_table_statistics_with_buffer,x$statements_with_temp_tables,x$wait_classes_global_by_latency,host_summary_by_file_io_type,io_global_by_file_by_bytes,memory_global_by_current_bytes,schema_redundant_indexes,statement_analysis,user_summary_by_file_io,waits_by_host_by_latency,x$host_summary_by_statement_type,x$io_global_by_wait_by_latency,x$ps_digest_95th_percentile_by_avg_us,x$schema_tables_with_full_table_scans,x$user_summary,x$waits_by_host_by_latency,host_summary_by_stages,io_global_by_file_by_latency,memory_global_total,schema_table_lock_waits,statements_with_errors_or_warnings,user_summary_by_file_io_type,waits_by_user_by_latency,x$innodb_buffer_stats_by_schema,x$latest_file_io,x$ps_digest_avg_latency_distribution,x$session,x$user_summary_by_file_io,x$waits_b | | test | absolutedifference,addcolumntocountasserialnumber,addingvaluetoexisting,allnamesstartwithaorborc,appenddatademo,autoincrementtabledemo,bigintdemo,boolorbooleanortinyintdemo,changecurrentautoincrementvalue,collatedemo,comparisononintfield,concatvalues,convertdatedemo,countingdemo,csvformatoutputs,customers,dateformatdemo,datetimedemo,decimaldemo,defaultdate,demoofvertical,differentrows,duplicaterecords,employeemaxandsecondmaxsalary,equivalentofidentityinmysql,findlowercasevalue,firsttablemaxvalue,getdatefromtimestamp,gettotalhoursdemo,ifnotnulldemo,incrementingdemo1,insertnullvalue,integerdemo,javainsertdemo,likebinarydemo,lowercasedemo,morethan2columnconcat,newstable,notnullatcreationoftable,occurrencesofcharacter,orderbycharacterlength,orderbynullfirstdemo,orlikedemo,populatetabledemo,productdemo,removenullrecordsdemo,renameviewdemo,rowendswithspecificstring,searchtextdemo,select_distincttwocolumns,selectindemo,selecttop2fromeachgroup,sessionvariabledemo,sortcolumnzeroatlastdemo,stringreplacedemo,studentinfo | | test3 | groupwithtopndemo,productdemo,studentinformation,bestdateformatdemo,lastrecordbeforelastone,radiansdemo,updatestringdemo,bitdemo,mostrecentdatedemo,returnresultofmd5demo,countdemo,mysqlandconditiondemo,select_timestampdemo,deletedemo,nullcasedemo,selectt extafterlastslashdemo,differentdatetime,order,siglequotesdemo,expandedoutputdemo,orderbydatethentimedemo,singleautoincrementcolumndemo,fieldlessthan5chars,posts,storeddemo | | tracker | preventnegativenumbers | | universitydatabase | NULL | | web | demotable492,demotable500,demotable508,demotable516,demotable525,demotable533,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,demotable605,demotable613,select,demotable484,demotabe619,demotable217,demotable225,demotable233,demotable241,demotable251,demotable259,demotable267,demotable275,demotable283,demotable291,demotable299,demotable307,demotable315,demotable323,demotable331,demotable339,demotable347,demotable355,demotable363,demotable371,demotable379,demotable388,demotable396,demotable404,demotable412,demotable420,demotable428,demotable436,demotable444,demotable452,demotable460,demotable468,demotable476,demotable493,demotable501,demotable509,demotable517,demotable526,demotable534,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,demotable606,demotable614,view_demotable388,demotable485,demotabe620,demotable218,demotable226,demotable234,demotable244,demotable252,demotable260,demotable268,demotable276, | | web_tracker | NULL | | webtracker | NULL | +---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 36 rows in set, 6 warnings (0.18 sec)