表结构，查询

最新推荐文章于 2024-11-22 17:05:31 发布

原创最新推荐文章于 2024-11-22 17:05:31 发布 · 640 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

17 篇文章

订阅专栏

本文深入解析了SQL查询语句的构造方法，通过一个具体案例展示了如何从数据库中精确获取所需字段信息，包括字段名称、说明、数据类型、长度、是否为空、默认值等。文章详细解释了左连接和条件过滤的使用，以及如何避免查询临时表，确保数据准确性和效率。

效果图

执行语句：

SELECT
   t1.Column_Name AS "字段名称",
   t2.Comments AS "字段说明",
   t1.Data_Type AS "数据类型",
   t1.Data_Length AS "长度",
   t1.NullAble AS "是否为空",
   --t1.Table_Name AS "表名称",
   --t3.comments AS "表说明",
   t1.Data_Default "默认值"
FROM
   cols t1
LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name
AND t1.Column_Name = t2.Column_Name
LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name
WHERE
   NOT EXISTS (
       SELECT
           t4.Object_Name
       FROM
           User_objects t4
       WHERE
           t4.Object_Type = 'TABLE'
       AND t4. TEMPORARY = 'Y'
       AND t4.Object_Name = t1.Table_Name
AND t1.Table_Name ='KQ_CERTIFICATE_ZP'--查询指定表名
   )
ORDER BY
   t1.Table_Name,
   t1.Column_ID;