询mysql所有表数据、字段信息
根据库名获取所有表的信息
SELECT * FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'erp';
根据库名获取所有表名称和表说明
SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'erp';
view:
根据库名获取所有的字段信息
SELECT TABLE_SCHEMA AS '库名', TABLE_NAME AS '表名', COLUMN_NAME AS '列名', ORDINAL_POSITION AS '列的排列顺序', COLUMN_DEFAULT AS '默认值', IS_NULLABLE AS '是否为空', DATA_TYPE AS '数据类型', CHARACTER_MAXIMUM_LENGTH AS '字符最大长度', NUMERIC_PRECISION AS '数值精度(最大位数)', NUMERIC_SCALE AS '小数精度', COLUMN_TYPE AS 列类型, COLUMN_KEY 'KEY', EXTRA AS '额外说明', COLUMN_COMMENT AS '注释' FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'erp' ORDER BY TABLE_NAME, ORDINAL_POSITION;
view:
根据库名获取所有的库和表字段的基本信息
SELECT C.TABLE_SCHEMA AS '库名', T.TABLE_NAME AS '表名', T.TABLE_COMMENT AS '表注释', C.COLUMN_NAME AS '列名', C.COLUMN_COMMENT AS '列注释', C.ORDINAL_POSITION AS '列的排列顺序', C.COLUMN_DEFAULT AS '默认值', C.IS_NULLABLE AS '是否为空', C.DATA_TYPE AS '数据类型', C.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度', C.NUMERIC_PRECISION AS '数值精度(最大位数)', C.NUMERIC_SCALE AS '小数精度', C.COLUMN_TYPE AS 列类型, C.COLUMN_KEY 'KEY', C.EXTRA AS '额外说明' FROM information_schema.`TABLES` T LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME AND T.TABLE_SCHEMA = C.TABLE_SCHEMA WHERE T.TABLE_SCHEMA = 'erp' ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION;
view:
Navicat目前查看数据库信息的方式并不友好,故想了另外一种方式,供参考,如下
执行存储过程
CREATE PROCEDURE ShowTableDesc(tablename VARCHAR(200))
BEGIN SELECT t2.*,t1.* FROM (select COLUMN_NAME as '字段',COLUMN_DEFAULT as '默认值',IS_NULLABLE as '允许为空',COLUMN_TYPE as '数据类型', COLUMN_COMMENT as '描述' from information_schema.columns where table_schema =(select database() as databasename) and table_name = tablename) t1, (select table_name as '表名',table_comment as '表描述' from information_schema.tables where table_schema = (select database() as databasename) and table_name = tablename) t2; END
调用方式
call ShowTableDes('order');
ShowTableDes为存储过程名字,order 为表名。
作者:阿笨
【官方QQ一群:跟着阿笨一起玩NET(已满)】:422315558
【官方QQ二群:跟着阿笨一起玩C#(已满)】:574187616
【官方QQ三群:跟着阿笨一起玩ASP.NET(已满)】:967920586
【官方QQ四群:Asp.Net Core跨平台技术开发(可加入)】:829227829
【官方QQ五群:.NET Core跨平台开发技术(可加入)】:647639415
【网易云课堂】:https://study.163.com/provider/2544628/index.htm?share=2&shareId=2544628
【腾讯课堂】:https://abennet.ke.qq.com
【51CTO学院】:https://edu.51cto.com/sd/66c64
【微信公众号】:微信搜索:跟着阿笨一起玩NET