oracle常用sql

获取主键

1. 查询主键的两种常用方法

Oracle 的主键信息存储在以下两个视图中：

• USER_CONSTRAINTS：存储当前用户下所有表的约束信息（如主键、外键等）。
  
• USER_CONS_COLUMNS：存储约束对应的列信息。
  

方法 1：直接查询主键列

 

1. SELECT 
2.     cols.column_name
3. FROM 
4.     user_constraints cons
5. JOIN 
6.     user_cons_columns cols 
7. ON 
8.     cons.constraint_name = cols.constraint_name
9. WHERE 
10.     cons.table_name = 'AI_TOOLS'  -- 替换为你的表名
11.     AND cons.constraint_type = 'P';      -- 'P' 表示主键

复制代码

 

 方法 2：分步查询

先查询主键约束名：

1. SELECT
2.     constraint_name
3. FROM
4.     user_constraints
5. WHERE
6.     table_name = 'YOUR_TABLE_NAME'
7.     AND constraint_type = 'P';

复制代码

 再根据约束名查询列名：

1. SELECT
2.     column_name
3. FROM
4.     user_cons_columns
5. WHERE
6.     constraint_name = 'YOUR_PK_CONSTRAINT_NAME';  -- 替换为第一步查到的约束名

复制代码

 
2. 复合主键的情况

 如果主键由多列组成（复合主键），查询结果会返回多行，按列在键中的次序排序

1. SELECT
2.     cols.column_name,
3.     cols.position  -- 列在键中的位置（从1开始）
4. FROM
5.     user_constraints cons
6. JOIN
7.     user_cons_columns cols
8. ON
9.     cons.constraint_name = cols.constraint_name
10. WHERE
11.     cons.table_name = 'YOUR_TABLE_NAME'
12.     AND cons.constraint_type = 'P'
13. ORDER BY
14.     cols.position;

复制代码

 
 3. 检察其他用户/所有主键（需要权限）

 使用 ALL_CONSTRAINTS 和 ALL_CONS_COLUMNS：

1. SELECT
2.     cols.column_name
3. FROM
4.     all_constraints cons
5. JOIN
6.     all_cons_columns cols
7. ON
8.     cons.constraint_name = cols.constraint_name
9. WHERE
10.     cons.table_name = 'YOUR_TABLE_NAME'
11.     AND cons.owner = 'AI_TOOLS'  -- 表所属的用户
12.     AND cons.constraint_type = 'P';

复制代码

 
4. 注意事项

• 表名巨细写：Oracle 默认以大写存储对象名，查询时建议使用大写表名（除非创建时用了引号强制小写）。
  
• 权限标题：访问 DBA_ 或 ALL_ 视图需要相应权限。
  

 获取字段名称

 1. 查询表的根本字段信息

使用 USER_TAB_COLUMNS 视图（当前用户下的表字段信息）： 

1. SELECT
2.     column_name,      -- 列名
3.     data_type,        -- 数据类型（如VARCHAR2、NUMBER等）
4.     data_length,      -- 数据长度
5.     data_precision,   -- 数字类型的精度
6.     data_scale,       -- 数字类型的小数位数
7.     nullable          -- 是否允许NULL（'Y'允许，'N'不允许）
8. FROM
9.     user_tab_columns
10. WHERE
11.     table_name = 'AI_TOOLS';  -- 替换为你的表名（需大写）

复制代码

 

 
2. 查询其他用户或所有表的字段信息         

 ALL_TAB_COLUMNS：查询你有权限访问的所有表的字段信息。

1. SELECT
2.     column_name,
3.     data_type,
4.     data_length
5. FROM
6.     all_tab_columns
7. WHERE
8.     table_name = 'YOUR_TABLE_NAME'
9.     AND owner = 'TABLE_OWNER';  -- 表所属的用户名（需大写）

复制代码

 DBA_TAB_COLUMNS（需 DBA 权限）：
 

1. SELECT
2.     column_name,
3.     data_type
4. FROM
5.     dba_tab_columns
6. WHERE
7.     table_name = 'YOUR_TABLE_NAME';

复制代码

 3. 查询字段的注释

 使用 USER_COL_COMMENTS 视图（当前用户下的字段注释）：

1. SELECT
2.     column_name,
3.     comments  -- 字段注释
4. FROM
5.     user_col_comments
6. WHERE
7.     table_name = 'YOUR_TABLE_NAME';

复制代码

 4. 生成建表语句（包含字段定义）

 使用 Oracle 提供的 DBMS_METADATA 包生成完整的表定义（包括字段、主键、约束等）：
 

1. SELECT
2.     dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME')
3. FROM
4.     dual;

复制代码

 5. 快速检察表结构（雷同DESCRIBE命令）

 在 SQL*Plus 或 SQLcl 中可以直接使用 DESC 命令：
   DESC YOUR_TABLE_NAME;
   6. 导出字段信息到文件

 在 SQL*Plus 中可以使用 SPOOL 命令导出结果到文件：
    SPOOL /path/to/output.txt
SELECT column_name, data_type, data_length FROM user_tab_columns WHERE table_name = 'YOUR_TABLE_NAME';
SPOOL OFF
   
注意事项

• 表名巨细写：Oracle 默认以大写存储对象名，查询时需用大写表名（除非建表时用了引号强制小写）。
  
• 权限标题：
  
  
  
  • 访问 ALL_TAB_COLUMNS 需要访问其他用户表的权限。
    
  • 访问 DBA_TAB_COLUMNS 需要 DBA 权限。
    
  
  
• 扩展需求：若需要字段的默认值、虚拟列等高级信息，可查询 DATA_DEFAULT 和 VIRTUAL_COLUMN 列。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告