开发笔记

#开发笔记| 来源: 网络整理| 查看: 265

场景描述：

项目开发中，模型设计经常用powerdesigner来操作，当我们需要给别人介绍表结构或者一些文档交付的时候，需要Excel表格样式的数据，

这个时候，可以用工具Navicat来实现。

操作步骤：

注：本文针对postgresql数据库

1. 执行如下sql语句，查看表结构

SELECT A.attnum AS "序号", ---C.relname AS "表名", ---CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述", A.attname AS "字段名", A.attnotnull as 是否为空, ---IF(A.attnotnull='f','是','否') AS '必填', concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS "数据类型", d.description AS "注释" FROM pg_class C, pg_attribute A, pg_type T, pg_description d WHERE C.relname = '实际表名' AND A.attnum > 0 AND A.attrelid = C.oid AND A.atttypid = T.oid AND d.objoid = A.attrelid AND d.objsubid = A.attnum ORDER BY C.relname DESC, A.attnum ASC

2. 点击运行，小三角，切换到查询结果界面

3. 如果执行正常，确认结果后，点击上边的“导出结果”按钮，

弹出如下窗口：

点击“Export Current Result”，勾选Excel文件，Continue继续，按着提示继续保存就可以。

--OK

参考：

Navicat导出mysql表结构：https://www.cnblogs.com/xianxiaobo/p/10254737.html

SELECT COLUMN_NAME 字段名称, COLUMN_TYPE 数据类型, IF(IS_NULLABLE='NO','是','否') AS '必填', COLUMN_COMMENT 注释 FROM INFORMATION_SCHEMA.COLUMNS where -- Finance为数据库名称，到时候只需要修改成你要导出表结构的数据库即可 table_schema ='Finance' AND -- user为表名，到时候换成你要导出的表的名称 -- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了 table_name = 'user'

https://blog.csdn.net/u010189683/article/details/84947193

查询Postgresql系统表，显示列名...: https://blog.csdn.net/weixin_43236610/article/details/88077484

select a.attname as 列名, format_type(a.atttypid, a.atttypmod) as 类型, (case when a.attlen > 0 then a.attlen else a.atttypmod - 4 end) as 长度, a.attnotnull as 是否可为空, d.adsrc as 默认值, col_description(a.attrelid, a.attnum) as 备注 from pg_class c, pg_attribute a left join (select a.attname, ad.adsrc from pg_class c, pg_attribute a, pg_attrdef ad where relname = 'table_name' and ad.adrelid = c.oid and adnum = a.attnum and attrelid = c.oid) as d on a.attname = d.attname where c.relname = 'table_name' and a.attrelid = c.oid and a.attnum > 0;

【本文地址】

公司简介

联系我们