编写一个与"SHOW TABLES"等同的 MySQL 查询(按排序顺序)?
mysqlmysqli database
使用 INFORMATION_SCHEMA.TABLES 按排序顺序显示表。以下语法将按升序提供排序的表列表:
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA= 'yourDatabaseName' order by TABLE_NAME;
以下是实现与 SHOW TABLES 等同的查询:
mysql> select TABLE_NAME from INFORMATION_SCHEMA.TABLES -> where TABLE_SCHEMA= 'sample' order by TABLE_NAME;
这将产生以下输出 −
+------------------------------------+ | TABLE_NAME | +------------------------------------+ | a | | accumulateddemo | | add10minutedemo | | add1toexistingvalue | | addanumbertocurrentvaluedemo | | addcolumnandindexdemo | | addingcurrentyeardemo | | addprefixtocolumndemo | | addtoexistingvaluedemo | | addtotaltimedemo | | addwhereclausedemo | | aggregatefunctiondemo | | allfiles | | allownulldefaulnotnulldemo | | allrecordsexceptlastone | | ampmtimedisplaydemo | | autoincrementdemo | | autoincrementtabledemo | | averagedemo | | averagestring | | avoidinserterrordemo | | avoidinsertingduplicaterows | | avoidnulldemo | | b | | backtick_symboldemo | | bar | | binarykeyworddemo | | blobsizedemo | | booleandemo | | booleanevaluationdemo | | bulkchangedemo | | calculateaveragedemo | | calculatepercentdemo | | calculatevaluedemo | | casefunctiondemo | | caseinsensitivedemo | | castfunctiondemo | | casttypetobigintdemo | | changecolumnname | | charlengthdemo | | child_table | | childtable | | client_information | | clientdemo | | clients | | columndoesnotexists | | combinetwocolumnsdemo | | commadelimitedlist | | comparedayandmonthdemo | | comparetwostringdemo | | comparingtimestampdemo | | concatandlowerdemo | | concatenatingdemo | | contains_capital_letterdemo | | convertnulltozerodemo | | convertnumbertominute | | correctdatetimedemo | | correlationdemo | | countallcharactersdemo | | countallrowsdemo | | countbooleanfielddemo | | countdifferentdemo | | countrowsdemo | | counttop10demo | | countvaluedemo | | countwithsubquerydemo | | coursedemo | | crc32demo | | createindexdemo | | createviewdemo | | currentweekdemo | | datedemo | | datedifferencedemo | | dateformatdemo | | dateortimedemo | | decimal_demo | | decimaldemo | | defaulmyisam | | defaultdemo | | defaultenginedemo | | delete_view | | deleteafterspacedemo | | deletedemo | | deletefrombasetabledemo | | deleterowdemo | | deleterowsolderthan5demo | | demo | | department | | department_table | | detectbitdemo | | displayonlydistinctvalue | | distinctfromonecolumn | | dividebycountdemo | | donotselectduplicatevaluesdemo | | duplicaterecords | | employee | | employee_table | | employeeinformation | | enforcedatausingregularexpression | | enumdemo | | equivalentdemo1 | | equivalentdemo2 | | errordemo | | eurosymboldemo | | eventdemo | | excludecertaincolumnsdemo | | existincommaseparatedlist | | extractfilenamedemo | | extracttuples | | findandreplacedemo | | findbestmatch | | findcapitallettrsdemo | | finddifferencedemo | | findinsetdemo | | findinvalidemailaddressdemo | | findreplacedemo | | findstringwithgivenprefixdemo | | findvalue | | first_table | | firstandlastdatademo | | firsttable | | firstweekofmonth | | followers | | formatnumberdemo | | formattime | | formdemo | | geom | | get2daysagodemo | | getdatetimefieldsdemo | | getfileextensiondemo | | getlatesthour | | gettingrandomrow | | getyearout | | givenprecisiondemo | | groupandcountbydate | | groupbyandcountdemo | | groupbydatedemo | | groupbydemo | | groupbygroupdemo | | groupbymaxdemo | | groupbyoneselectall | | groupbyrangedemo | | groupbywithwhereclause | | hasconsecutiveprimary | | idcolumnadd1demo | | ifnulldemo | | igonreyeardemo | | inclausedemo | | incrementcolumndemo | | indemo | | indexonintcolumndemo | | inoperatordemo | | insert_prevent | | insertdemo | | insertmultipledemo | | insertonetoanothertable | | insertrecord_selecttable | | insertrecordprevent | | insertvalueinautoincrement | | instructor | | javadatedemo | | javagetdatademo | | javapreparedstatement | | keyreferringtodatedemo | | lagdemo | | largeautoincrement | | lastinsertedrow | | limit0demo | | limitwithstoredprocedure | | location | | locktabledemo2 | | loggingdetails | | manage_table3 | | maxdemo | | maximumoftwocolumnsdemo | | maxofthreecolumnsdemo | | mergingselectdemo | | modified_table2 | | multiplecolumnsortingorderdemo | | multiplegroupbydemo | | my_table1 | | mysqlselectdemo | | mytable | | new_viewdemo | | newlinedemo | | notequaloperator | | notupdatedemo | | null_demo | | nulldemo | | old_tabledemo | | old_tabledemo2 | | onlypositivevalue | | orderbyafunctiondemo | | orderbycasedemo | | orderbydatedemo | | orderbydateformatdemo | | orderbyfield | | orderbylast2charactersdemo | | orderdemo | | pairdemo | | parent_table | | parenttable | | pipeconcatdemo | | pipeinsertdemo | | positivetonegativevaluedemo | | positivevaluedemo | | post | | post_demo | | postmesssagedemo | | preventnegativenumberdemo | | previousandnextrecorddemo | | primarykeydemo | | productinformations | | products1 | | products2 | | productstock | | quotesdemo | | randomnumberdemo | | receive | | recievedatefromtable | | recordsdemo | | regularexpressiondemo | | removedemo | | removeerrordemo | | removehyphensdemo | | removeonupdatecurrenttimestampdemo | | removespacedemo | | removeuniquenessconstraint | | reordersortdemo | | replacevaluedemo | | reservationsystems | | reservingautoincrementdemo | | resetprimarykeydemo | | resettingprimarykeydemo | | retrievetimedemo | | returndemo | | returnnullwheninputisnulldemo | | returnrecordsetorderdemo | | returnrows | | reverseorder | | roundweirddemo | | rowafftectedbydeletedemo | | rownumberdemo | | rowsusinglimit | | scorecountdemo | | searchdateasvarchar | | searchingdemo | | searchingdemo2 | | second_table | | secondlastdemo | | secondreceivedemo | | secondtable | | seespacesdemo | | select1andlimit1demo | | selectalldemo | | selectallentriesdemo | | selectbeforeandafterdemo | | selectdaterangedemo | | selectdatesdemo | | selectdistinctdemo | | selectifdemo | | selectintoequivalentdemo | | selectorderdemo | | selectpartoftimestampdemo | | selectpermonthdemo | | selectperson | | selectrandomrecord | | selectrecordsdemo | | selectuniquevalue | | selfjoindemo | | set+0demo | | setallvaluesdemo | | setautoincrementdemo | | setfirstnameandlastname | | setzerodemo | | showsuminlastrowdemo | | sleepdemo | | sortcertainvalues | | sortedlistdemo | | specialcharactersdemo | | sqlstatementsdemo | | stoproundingdemo | | stoproundingdemo2 | | storecsscolordemo | | storedproceduredemo | | storedprocedureinsertdemo | | storevalue0and1ornulldemo | | stringendswithnumber | | student_information | | student_table_sample | | subtotaldemo | | subtractvaluedemo | | sumdemo | | sumofeverydistinct | | sumtimedemo | | syntaxofaltercommanddemo | | taxonomytable | | tbl1 | | tblupdate | | tematics_field | | ternaryoperationdemo | | texttotimestampdemo | | timedemo | | timestamp_tabledemo | | top2demo | | trimcommasdemo | | uniquebothcolumnvaluesamedemo | | uniquecountbyipadress | | uniquedemo | | university | | unixtimestampformatdemo | | unsigneddemo | | updatecolumndemo | | updatecolumnname | | updatedatedemo | | updatedemo | | updateifconditiondemo | | updatesettypedemo | | updatewithhighestdemo | | useofdesccolumnnamedemo | | useprocedure | | user_informations | | usercommentsview | | userdemo | | userinformation | | userinformationexpire | | userlogindetails | | userlogininformation | | userlogintimeinformation | | usertabledemo | | utfdemo | | valueascolumn | | view_table_demo | | viewautoincrementdemo | | viewtabledemo | | weeklyeventdemo | | whereaftergroupdemo | | wheredemo | | wheresetdemo | | wildcarddemo | | yesterdayrecordsdemo | | yourtable | +------------------------------------+ 342 rows in set (0.11 sec)