约束列 USER_CONS_COLUMNS

本文介绍了通过Oracle SQL查询来获取特定表中的约束信息的方法。具体包括如何使用user_constraints和user_cons_columns视图来检索表‘SETT_GLENTRY’的相关约束及其列详情。这有助于理解表结构并进行更精确的数据操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

user_cons_columns
结果:
image 
 
select *  from user_constraints;

注:2个是是VIEW,

Owner 约束的所有者

Constraint_ Name 约束名

Table_Name 与约束关联的表名

Column_Name 与约束关联的列名

Position 约束定义中列的顺序


1、达梦数据库使用以下SQL查询元数据信息发现无法查出主键、外键、索引信息 2、SELECT A.TABLE_NAME TABLE_NAME , A.COLUMN_NAME NAME , A.DATA_TYPE TYPENAME , A.DATA_LENGTH LENGTH , A.DATA_PRECISION PRECISION , A.DATA_SCALE SCALE , A.DATA_DEFAULT , A.NULLABLE , DECODE(B.COMMENTS, NULL, A.COLUMN_NAME, B.COMMENTS) DESCRIPTION, ( SELECT COUNT(*) FROM USER_CONSTRAINTS CONS, USER_CONS_COLUMNS CONS_C WHERE CONS.CONSTRAINT_NAME=CONS_C.CONSTRAINT_NAME AND CONS.CONSTRAINT_TYPE='P' AND CONS.TABLE_NAME =B.TABLE_NAME AND CONS_C.COLUMN_NAME =A.COLUMN_NAME AND CONS.OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ) AS IS_PK, ( SELECT COUNT(*) FROM USER_CONSTRAINTS CONS, USER_CONS_COLUMNS CONS_C WHERE CONS.CONSTRAINT_NAME=CONS_C.CONSTRAINT_NAME AND CONS.CONSTRAINT_TYPE='R' AND CONS.TABLE_NAME =B.TABLE_NAME AND CONS_C.COLUMN_NAME =A.COLUMN_NAME AND CONS.OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ) AS IS_FK, ( SELECT COUNT(*) FROM USER_IND_COLUMNS, USER_INDEXES WHERE USER_IND_COLUMNS.INDEX_NAME = USER_INDEXES.INDEX_NAME AND USER_IND_COLUMNS.TABLE_NAME =B.TABLE_NAME AND USER_IND_COLUMNS.COLUMN_NAME=A.COLUMN_NAME AND USER_INDEXES.TABLE_OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ) AS IS_IDX FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B WHERE A.COLUMN_NAME =B.COLUMN_NAME AND A.OWNER = B.SCHEMA_NAME AND A.TABLE_NAME = B.TABLE_NAME AND upper(A.TABLE_NAME)='ZTESTZ_DEC' AND A.OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ORDER BY A.COLUMN_ID
03-08
SELECT su.user_id, su.user_name, aui.user_parent_id, su1.user_name AS user_parent_name, COUNT(DISTINCT aui1.user_id) AS member_num_one, -- 一级邀请人数 COUNT(DISTINCT aui2.user_id) AS member_num_two, -- 二级邀请人数 COUNT(DISTINCT aui1.user_id) + COUNT(DISTINCT aui2.user_id) AS total_member_num, -- 总邀请人数 ( SELECT IFNULL(SUM(ad.balance), 0) FROM app_detail ad WHERE ad.user_id = su.user_id AND ad.flow = 1 AND ad.type IN (9,22) ) AS total_commissions, -- 总佣金 ( SELECT IFNULL(SUM(ad.balance), 0) FROM app_detail ad WHERE ad.user_id = su.user_id AND ad.flow = 1 AND ad.type IN (9,22) AND create_time >= CURDATE() - INTERVAL 1 DAY ) AS total_yesterday, -- 昨天总佣金 ( SELECT IFNULL(SUM(ad.balance), 0) FROM app_detail ad WHERE ad.user_id = su.user_id AND ad.flow = 1 AND ad.remark = "DirectPush" AND create_time >= CURDATE() - INTERVAL 1 DAY ) AS direct_push_yesterday, -- 昨天直推 ( SELECT IFNULL(SUM(ad.balance), 0) FROM app_detail ad WHERE ad.user_id = su.user_id AND ad.flow = 1 AND ad.type IN (9) AND create_time >= CURDATE() - INTERVAL 1 DAY ) AS transaction_yesterday, -- 昨天流水 ( SELECT IFNULL(SUM(ad.balance), 0) FROM app_detail ad WHERE ad.user_id = su.user_id AND ad.flow = 1 AND ad.type IN (22) AND create_time >= CURDATE() - INTERVAL 1 DAY ) AS recharge_yesterday -- 昨天充值 FROM sys_user su LEFT JOIN app_user_info aui ON su.user_id = aui.user_id LEFT JOIN sys_user su1 ON su1.user_id = aui.user_parent_id LEFT JOIN app_user_info aui1 ON aui.user_id = aui1.user_parent_id LEFT JOIN app_user_info aui2 ON aui1.user_id = aui2.user_parent_id WHERE su.status = 0 <if test="bo.userId != null">AND su.user_id LIKE CONCAT('%', #{bo.userId}, '%')</if> GROUP BY su.user_id, su.user_name 最终结果是app_user_info 有数据才返回所有
最新发布
08-02
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值