MySQL-如何在一个查询中计算每个表的所有行?
您可以借助informatio_schema.tables中的聚合函数计数(TABLE_ROWS)来计数每个表的所有行。语法如下-
SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourDatabaseName';
现在,您可以应用上述语法来获取每个表的所有行。查询如下-
mysql> SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'business';
这是输出-
+ ------------------------------------------------- ----------------- + ------------ +| TABLE_NAME | TABLE_ROWS |+ ------------------------------------------------- ----------------- + ------------ +| accentsearchdemo | 2 || add1daydemo | 2 || addcheckconstraintdemo | 0 || 可加列| 0 || addconstraintdemo | 2 || 加5小时| 2 || 添加自动增量| 2 || 添加列名称演示| 0 || 独特的| 2 || addnotnulldemo | 2 || 从日期开始计算年龄| 0 || allcolumnsdemo | 0 || alphademo | 0 || 可以添加自动增量| 2 || 自动递增| 4 || 自动递增| 5 || autoincrementtozero | 2 || backticksymbol | 4 || bitdemo | 2 || blobtabledemo | 0 || 图书索引| 4 || booleandemo | 0 || chardemo | 0 || checkconstraintdemo | 0 || checkdemo | 0 || checkintegerdemo | 2 || childdemo | 0 || 克隆学生| 3 || 大学| 0 || 颜色表| 0 || columnexistdemo | 0 || columnnameasnumberdemo | 2 || columnnamewithspace | 4 || 列列表| 0 || columntabledemo | 0 || columnvaluenulldemo | 2 || commaseperatedemo | 2 || 评论| 0 || 评论demo2 | 0 || 评论demo3 | 0 || 复合主键| 0 || countrycitydemo | 2 || curdatedemo | 0 || 当前日期时间| 0 || currenttimeadding2hours | 0 || 当前时区| 1 || dateadddemo | 0 || dateandtimepartdemo | 0 || datetimedemo | 2 || defaultdatetimedemo | 0 || deletecolumnnamedemo | 0 || 删除的| 5 || deleterecord | 6 || deletetabledemo | 2 | | 演示| 2 || 演示1 | 0 || 演示| 2 || demoauto | 2 || demobcrypt | 0 || demoemptyandnull | 0 || demoindex | 0 || 恶魔 0 || demoonreplace | 2 || 演示| 0 || 演示| 2 || distcountdemo | 4 || 演示| 8 || 与众不同 4 || 重复的书本索引| 4 || 重复删除 4 || |发现 4 || 员工信息| 2 || 员工记录| 0 || 可员工 0 || 枚举| 2 || 枚举| 0 || 逃脱游戏| 0 || 存在行演示| 4 || findandreplacedemo | 4 || finddemo | 2 || firsttabledemo | 3 || floordemo | 2 || 外表| 2 || 外表1 | 0 || foreigntabledemo | 2 || functionindexdemo | 0 || functiontriggersdemo | 0 || getlastrecord | 4 || 两个或更多| 2 || groupbydemo | 4 || groupconcatenatedemo | 4 || groupdemo | 4 || groupdemo1 | 4 || groupt_concatdemo | 4 || humandatedemo | 0 || iddemo | 4 || ifelsecasedemo | 4 || ifelsedemo | 4 || imagedemo | 2 || incasesensdemo | 4 || indemo | 6 || indexingdemo | 0 || insertdemo | 2 || insertingemojidemo | 0 || insertmultiplerowsdemo | 5 || insertvaluesandsetdemo | 2 || insubquerydemo | 2 || int1demo | 0 || intandbigint20demo | 0 || 诠释| 2 || inwithfielddemo | 12 || ipv4addressdemo | 0 || ipv6demo | 0 || jasonasmysqldemo | 2 || javadatetimedemo | 2 || keydemo | 2 || last10recordsdemo | 12 || lastinsertiddemo | 2 || lastinsertrecordiddemo | 2 || latandlangdemo | 0 || lengthandcharlengthdemo | 0 || limitoffsetdemo | 11 || limitoffsettable | 13 || 低基数| 2 || 小写字母demo | 0 || mergedemo1 | 2 || mergedemo2 | 0 || 毫秒| 0 || Modifycolumndemo | 0 || Modifycolumnnamedemo | 0 || 修改数据类型| 0 || moneydemo | 2 || 资金代表| 0 || moneystoragedemo | 2 || 电影收藏| 6 || multiindexdemo | 0 || 带值的多记录| 4 || myisamtabledemo | 2 || myisamtoinnodbdemo | 0 || mysqltabledemo | 5 || mytable | 0 || mytable1 | 0 || mytabledemo | 2 || nestedtransactiondemo | 2 || 新闻学生| 0 || nextiddemo | 2 || nextpreviousdemo | 9 || nonasciidemo | 4 || nowdemo | 0 || nthrecorddemo | 4 || nulldemo | 0 || nulldemo1 | 4 || nulldemowithzero | 6 || nullwithselect | 6 || numbercolumndemo | 0 || 列数| 2 || ondemo | 4 || orderbydemo | 4 || orderdemo | 2 || 原始表| 4 || parentdemo | 0 || 历史 4 || 现在的历史| 2 || primarykeydemo | 0 || 主表| 2 || primarytable1 | 2 || primarytable1demo | 0 || 主表演示| 2 || proctabledemo | 3 || 产品| 2 || 在两个日期之间查询| 0 || querydatedemo | 0 || qutesdemo | 2 || 随机优化演示| 8 || randoptimizedemo | 26 || Rankdemo | 5 || repairtabledemo | 3 || 用nulldemo替换null | 2 || rowcountdemo | 8 || rowintocolumn | 4 || rownumberdemo | 4 || 行数