---
# OpenGauss 数据库 SQL 脚本汇总
---
---
## 0.0 数据库 - 0.0 GAUSS 杀死死锁.sql
```sql
-- 查看锁表信息
select l.locktype,l.database,c.relname,l.relation,l.pid,mode
from pg_locks l, pg_class c
where l.relation=c.oid and c.relname='omp_project_t';
-- 查看进程执行的 SQL
select datname,pid,usename,state,query
from pg_stat_activity
where pid in (140438048450304,140438216263424,140445507057408);
-- 查看会话信息
SELECT sid,serial#,username
FROM dv_sessions
where sid in (140438048450304,140438216263424,140445507057408);
-- 杀死会话
ALTER SYSTEM KILL SESSION '140438048450304, 0' IMMEDIATE;
ALTER SYSTEM KILL SESSION '140438216263424, 0' IMMEDIATE;
ALTER SYSTEM KILL SESSION '140445507057408, 0' IMMEDIATE;
```
---
## 0.0 数据库 - 0.0 GAUSS 空间大小分析.sql
### 查询表空间大小（含视图、索引等）
```sql
SELECT
row_number() OVER (ORDER BY pg_total_relation_size(c.oid) DESC) AS serial_number,
n.nspname AS schema_name,
c.relname AS table_name,
d.description AS table_comment,
pg_total_relation_size(c.oid) AS table_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS table_size_pretty
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN
pg_description d ON d.objoid = c.oid AND d.objsubid = 0
WHERE
c.relkind = 'r' -- 只选择表
AND (
c.relname ~ '^[0-9]'
OR c.relname ~ '^[a-z][0-9]'
OR c.relname ~ '^[A-Z][0-9]'
OR c.relname ~ '[0-9]$'
OR c.relname ~ '[a-z][0-9]$'
OR c.relname ~ '[A-Z][0-9]$'
OR c.relname LIKE 'temp\_%'
OR c.relname LIKE 'bak\_%'
OR c.relname LIKE 'backup\_%'
OR c.relname LIKE 'archive\_%'
OR c.relname LIKE 'snapshot\_%'
OR c.relname LIKE '%\_temp$'
OR c.relname LIKE '%\_bak$'
OR c.relname LIKE '%\_backup$'
OR c.relname LIKE '%\_archive$'
OR c.relname LIKE '%\_snapshot$'
)
ORDER BY
table_size DESC;
```
### 查询函数和存储过程所占空间
```sql
select
n.nspname as schema_name,
p.proname as procedure_name,
pg_total_relation_size(p.oid) as procedure_size,
pg_size_pretty(pg_total_relation_size(p.oid)) as procedure_size_pretty
from
pg_proc p
join
pg_namespace n on n.oid = p.pronamespace
where
p.proname ~ '^[0-9]'
OR p.proname ~ '^[a-z][0-9]'
OR p.proname ~ '^[A-Z][0-9]'
OR p.proname ~ '[0-9]$'
OR p.proname ~ '[a-z][0-9]$'
OR p.proname ~ '[A-Z][0-9]$'
OR p.proname LIKE 'temp\_%'
OR p.proname LIKE 'bak\_%'
OR p.proname LIKE 'backup\_%'
OR p.proname LIKE 'archive\_%'
OR p.proname LIKE 'snapshot\_%'
OR p.proname LIKE '%\_temp$'
OR p.proname LIKE '%\_bak$'
OR p.proname LIKE '%\_backup$'
OR p.proname LIKE '%\_archive$'
OR p.proname LIKE '%\_snapshot$'
order by
procedure_size desc;
```
### 查询包所占空间
```sql
select
n.nspname as schema_name,
p.pkgname as package_name,
pg_total_relation_size(p.oid) as package_size_bytes,
pg_size_pretty(pg_total_relation_size(p.oid)) as package_size_pretty
from
gs_package p
join
pg_namespace n on n.oid = p.pkgnamespace
where
p.pkgname in ('omp_resource_pkg', 'omp_personfile_pkg', 'omp_employee_pkg')
order by
package_name;
```
---
## 0.0 数据库 - 0.0 字段批量排查.sql
### 查询字段是否存在于视图中
```sql
select
a.attnum as "序号",
c.relname as "表名",
cast(obj_description(c.oid) as varchar) as "表名描述",
a.attname as "列名",
t.typname,
format_type(a.atttypid,a.atttypmod),
substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$'),
concat(t.typname,substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$')) as "字段类型",
d.description as "备注"
from
pg_attribute a
left join
pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join
pg_class c on a.attrelid = c.oid
left join
pg_type t on a.atttypid = t.oid
where
a.attnum >= 0
and a.attname='incentive_budget_ratio'
and c.relname like '%_v'
and c.relname not like '%temp'
and c.relname not like '%ttemp%'
and c.relname not like '%tmp'
and c.relname not like '%bak'
and c.relname not like '%back'
and c.relname !~ '[0-9]'
order by
c.relname desc, a.attnum asc;
```
### 查询字段是否存在于表中
```sql
select
a.attnum as "序号",
c.relname as "表名",
cast(obj_description(c.oid) as varchar) as "表名描述",
a.attname as "列名",
t.typname,
format_type(a.atttypid,a.atttypmod),
substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$'),
concat(t.typname,substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$')) as "字段类型",
d.description as "备注"
from
pg_attribute a
left join
pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join
pg_class c on a.attrelid = c.oid
left join
pg_type t on a.atttypid = t.oid
where
a.attnum >= 0
and a.attname='rank_type'
and c.relname not like '%_v'
and c.relname not like '%temp'
and c.relname not like '%ttemp%'
and c.relname not like '%tmp'
and c.relname not like '%bak'
and c.relname not like '%back'
and c.relname !~ '[0-9]'
order by
c.relname desc, a.attnum asc;
```
---
## 0.0 数据库 - 0.1 GAUSS 表定义.sql
### 查询所有表注释
```sql
-- 查询所有表注释
select tb.table_name, d.description
from information_schema.tables tb
join pg_class c on c.relname = tb.table_name
left join pg_description d on d.objoid = c.oid and d.objsubid = '0'
where tb.table_schema = 'omp'
and tb.table_name !~ '[0-9]'
and tb.table_name !~ '^[a-z][0-9]'
and tb.table_name !~ '^[A-Z][0-9]'
and tb.table_name !~ '[0-9]$'
and tb.table_name !~ '[a-z][0-9]$'
and tb.table_name !~ '[A-Z][0-9]$'
and tb.table_name not LIKE 'temp\_%'
and tb.table_name not LIKE 'bak\_%'
and tb.table_name not LIKE 'backup\_%'
and tb.table_name not LIKE 'archive\_%'
and tb.table_name not LIKE 'snapshot\_%'
and tb.table_name not LIKE '%\_temp$'
and tb.table_name not LIKE '%\_bak$'
and tb.table_name not LIKE '%\_backup$'
and tb.table_name not LIKE '%\_archive$'
and tb.table_name not LIKE '%\_snapshot$'
order by tb.table_name;
```
### 查看表结构定义
```sql
-- 查看表结构
select
c.relname,
case when c.relkind='r' then pg_get_tabledef(c.oid) else null end as def
from
pg_class c
join pg_namespace n on n.oid=c.relnamespace
and n.nspname='omp'
where
c.relkind in ('r')
order by relname;
```
### 查看索引信息
```sql
-- 查看索引
select
c.relname as table_name,
i.relname as index_name,
am.amname as index_type,
idx.indisunique as is_unique,
idx.indisprimary as is_primary
from
pg_index idx
join
pg_class c on c.oid = idx.indrelid
join
pg_class i on i.oid = idx.indexrelid
join
pg_am am on i.relam = am.oid
where
c.relname = 'omp_tm_month_payment_draft_t';
```
### 查询表字段信息（按字段详细信息）
```sql
-- 查询表名为 ** 的表信息
select
a.attnum as "序号",
c.relname as "表名",
cast(obj_description(c.oid) as varchar) as "表名描述",
a.attname as "列名",
t.typname,
format_type(a.atttypid,a.atttypmod),
substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$'),
concat(t.typname,substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$')) as "字段类型",
substring(format_type(a.atttypid,a.atttypmod) from '([0-9,]+)') as "字段长度",
'y' as "是否可空",
'n' as "是否入湖",
d.description as "备注"
from
pg_attribute a
left join
pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join
pg_class c on a.attrelid = c.oid
left join
pg_type t on a.atttypid = t.oid
where
a.attnum >= 0
and c.relname ='omp_require_change_query_t'
order by
c.relname desc, a.attnum asc;
```
---
## 0.0 数据库 - 0.2 GAUSS 包定义.sql
### 查询包定义
```sql
select
row_number() over (order by P.PKGNAME) as serial_number,
P.PKGNAME,
'CREATE OR REPLACE PACKAGE '||N.NSPNAME||'.'||P.PKGNAME||' AS '||CHR(13)||REPLACE(PKGSPECSRC,' PACKAGE DECLARE',NULL) ||P.PKGNAME||';'||CHR(13)||'/'||chr(13)
||
decode(PKGBODYDECLSRC,null,null,
'CREATE OR REPLACE PACKAGE BODY '||N.NSPNAME||'.'||P.PKGNAME||' AS '||CHR(13)||REPLACE(PKGBODYDECLSRC,' PACKAGE DECLARE',NULL) ||P.PKGNAME||';'||CHR(13)||'/' ) ddl
from gs_package p
left join PG_NAMESPACE N on P.PKGNAMESPACE=N.OID
where P.PKGNAME in ('omp_resource_pkg','omp_personfile_pkg','omp_employee_pkg')
order by P.PKGNAME;
```
### 查询包权限
```sql
select n.nspacl from gs_package p
left join pg_namespace n on p.pkgnamespace = n.oid
where p.pkgname in ('omp_resource_pkg','omp_personfile_pkg','omp_employee_pkg');
```
### 对包进行授权
```sql
grant execute on package omp_archive_pkg to omp_dev;
```
### 对包进行撤权
```sql
revoke execute on package omp_archive_pkg to omp_app_fr;
```
---
## 0.0 数据库 - 0.3 GAUSS 存储过程定义.sql
### 查看存储过程定义
```sql
SELECT
row_number() over (order by proname) as serial_number,
decode( prokind, 'f', 'function', 'procedure' ),
proname,
( pg_get_functiondef ( P.OID ) ).definition def
FROM
pg_proc P
JOIN pg_namespace n ON n.nspname = 'omp'
AND P.pronamespace = n.OID
WHERE
NOT propackage
AND proname in (
'sync_trip_query_single_sp',
'updwfrecords',
...
)
ORDER BY 1,2;
```
### 查看存储过程权限（方式一）
```sql
select
routine_name,
grantee,
privilege_type
from
information_schema.routine_privileges
where
routine_schema = 'omp'
and routine_name in (
'sync_trip_query_single_sp',
'updwfrecords',
...
);
```
### 查看存储过程权限（方式二）
```sql
select
n.nspname as schema_name,
p.proname as function_name,
pg_get_userbyid(p.proowner) as owner,
(select array_to_string(array(
select
'[' ||
split_part(unnest::text, '=', 1) || ']=' ||
split_part(split_part(unnest::text, '=', 2), '/', 1)
from unnest(p.proacl) as unnest
), ', ')) as privileges
from
pg_proc p
left join
pg_namespace n on p.pronamespace = n.oid
where p.proname in (
'sync_trip_query_single_sp',
'updwfrecords',
...
);
```
### 存储过程授权
```sql
grant execute on function omp.sync_emp_passcert_data_sp to omp_app_fr;
```
### 存储过程撤权
```sql
revoke execute on function omp.sync_emp_passcert_data_sp to omp_app_fr;
```
---
## 0.0 数据库 - 0.4 GAUSS 视图定义.sql
### 排查修改的表字段精度在哪些视图中
```sql
select a.attnum as "序号",
c.relname as "表名",
cast(obj_description(c.oid) as varchar) as "表名描述",
a.attname as "列名",
t.typname,
format_type(a.atttypid,a.atttypmod),
substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$'),
concat(t.typname,substring(format_type(a.atttypid,a.atttypmod) from '$$.*$$')) as "字段类型",
d.description as "备注"
from pg_attribute a
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid
left join pg_type t on a.atttypid = t.oid
where a.attnum >= 0
and a.attname='hw_dept_code'
order by c.relname desc, a.attnum asc;
```
### 排查视图是否依赖特定字段
```sql
select * from pg_views where viewname in ('omp_project_query_v') and definition like '%rank_type%';
```
### 查看视图定义（带创建头）
```sql
select '1' as "序号", 'omp_project_query_v' as "视图" , 'create or replace view omp_project_query_v as ' || pg_get_viewdef('omp_project_query_v', true) union all
...
```
### 查看视图依赖关系
```sql
select '1' as "序号", 'omp_project_query_v' as "依赖视图", viewname, definition from pg_views where definition like '%omp_project_query_v%' union all
...
```
### 查看视图权限
```sql
select a.table_name,a.table_schema,a.grantor,a.grantee,STRING_AGG(a.privilege_type, ', ') AS privileges
from (
select '1' as "序号",* from information_schema.role_table_grants where table_name in ('omp_project_query_v') union all
...
) a
group by a.table_name,a.table_schema,a.grantor,a.grantee
order by a.table_name,a.table_schema,a.grantor,a.grantee;
```
### 添加视图注释
```sql
call p_execute_sql('comment on view omp_tm_month_payment_v is ''TM月度付款信息视图'';');
call p_execute_sql('comment on view omp_payment_review_info_v is ''付款审核信息视图'';');
...
```
---
## 0.0 数据库 - 0.5 GAUSS 移动JSON获取.sql
```sql
select
row_number() over (order by activity_define_key) as serial_number,
t.process_define_key as processdefinekey,
t.activity_define_key as activitydefinekey,
a.name_ as activitydefinename,
t.mobile_json as mobilejson,
t.created_by as createdby,
t.creation_date as creationdate,
t.last_updated_by as lastupdatedby,
t.last_update_date as lastupdatedate
from
tpl_wf_mobile_setting_t t
inner join (
select proc_def_id_, name_ , task_def_key_ from act_ru_task
where proc_def_id_ in (
select res.id_ from act_re_procdef res
where res.key_ in (
'FpTaskPayment','OpmTripProcess','RequirementChangeProcess','TimelyRewardPayProcess','TmMonthPayment'
)
and res.version_ = (select max(version_) from act_re_procdef where key_ = res.key_)
order by res.id_ asc limit 2147483647 offset 0
)
group by proc_def_id_, name_ , task_def_key_
) a on t.activity_define_key = a.task_def_key_
and a.proc_def_id_ like concat(t.process_define_key ,'%')
and t.is_mobile_activity = 1
order by processdefinekey,activitydefinekey;
```
---
## 0.0 数据库 - 0.6 GAUSS 授权.sql
### 表或视图授权
```sql
grant select on omp_emp_rank_v to EIP_OMP;
grant select on omp_emp_rank_v to etl_ompopm_op;
grant select,insert,update,delete,truncate on omp_emp_rank_v to jdbc_opm;
...
```
### 序列授权
```sql
grant usage on sequence tpl_wf_hi_procform_split_t_pk_s to omp_app_fr;
revoke usage on sequence tpl_wf_hi_procform_split_t_pk_s to omp_app_fr;
```
### 包授权
```sql
grant execute on package omp_archive_pkg to omp_dev;
revoke execute on package omp_archive_pkg to omp_app_fr;
```
### 存储过程授权
```sql
grant execute on function sync_emp_passcert_data_sp to omp_app_fr;
revoke execute on function sync_emp_passcert_data_sp to omp_app_fr;
```
---
## 0.0 数据库 - 0.7 GAUSS 生成 SELECT/INSERT/UPDATE 语句.sql
### 生成 SELECT 语句（驼峰型）
```sql
select
'select ' ||
string_agg(
'a.' || a.attname || ' as ' ||
lower(left(replace(initcap(a.attname), '_', ''),1))||
substr(replace(initcap(a.attname), '_', ''),2,100),
', ') ||
' from ' || c.relname || ' a'
from
pg_attribute a
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid
left join pg_type t on a.atttypid = t.oid
where
a.attnum >= 0
and a.attname not like '%pg.dropped%'
and c.relname ='omp_require_change_query_t'
group by c.relname;
```
### 生成 SELECT 语句（原始型）
```sql
select
'select ' ||
string_agg('v.' || a.attname, ', ') ||
' from ' || c.relname || ' v'
from
pg_attribute a
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid
left join pg_type t on a.atttypid = t.oid
where
a.attnum >= 0
and a.attname not like '%pg.dropped%'
and c.relname ='omp_require_change_query_t'
group by c.relname;
```
### 生成 UPDATE 语句
```sql
select
'update ' ||
c.relname ||
' set ' ||
string_agg('v.' || a.attname || ' = m.' || a.attname, ', ') ||
' from ' || c.relname || ' v, ' || c.relname || ' m'
from
pg_attribute a
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid
left join pg_type t on a.atttypid = t.oid
where
a.attnum >= 0
and a.attname not like '%pg.dropped%'
and c.relname ='omp_require_change_query_t'
group by c.relname;
```
### 生成 INSERT 语句
```sql
select
'insert into ' ||
c.relname ||
' (' ||
string_agg('q.' || a.attname, ', ') ||
') values (' ||
string_agg('m.' || a.attname, ', ') ||
')'
from
pg_attribute a
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid
left join pg_type t on a.atttypid = t.oid
where
a.attnum >= 0
and a.attname not like '%pg.dropped%'
and c.relname ='omp_require_change_query_t'
group by c.relname;
```
---
## 0.0 数据库 - 0.8 GAUSS 定时任务查询.sql
### 查询用户创建的定时任务
```sql
select * from pg_job;
```
### 查询定时任务信息（关联执行的脚本）
```sql
select p.what, j.job_id,
to_char(j.start_date, 'YYYY-MM-DD HH24:MI:SS') start_date,
to_char(j.last_start_date, 'YYYY-MM-DD HH24:MI:SS') last_start_date,
to_char(j.last_end_date, 'YYYY-MM-DD HH24:MI:SS') last_end_date,
to_char(j.next_run_date, 'YYYY-MM-DD HH24:MI:SS') next_run_date,
j.failure_msg
from pg_job j
left join pg_job_proc p on j.job_id = p.job_id
order by j.start_date desc;
```
### 查询运行报错的定时任务
```sql
select * from pg_job where failure_msg > ' ';
```
### 查询 running 状态的 job
```sql
select job_id,log_user,priv_user,job_status,job_name from pg_job where job_status='r';
```
---
## 0.0 数据库 - 1.1 MySQL 表定义.sql
### 查看表结构（字段、类型、注释）
```sql
select
t.table_name,
t.table_comment,
c.column_name,
c.column_type,
c.column_comment
from
information_schema.`columns` c,
information_schema.`tables` t
where c.table_name = t.table_name
and t.table_schema = 'omp_cs'
and t.table_name='omp_workflow_approval_t';
```
---
## 0.0 数据库 - 1.2 MySQL 存储过程定义.sql
### 查看存储过程定义
```sql
select routine_definition from information_schema.ROUTINES
where routine_schema='omp_cs' and routine_name='omp_change_local_org_process_init';
```
### 查看存储过程 DDL
```sql
show create procedure omp_change_local_org_process_init;
```
### 查看某个数据库下的所有存储过程
```sql
select * from mysql.proc where db = 'omp_cs' and `type` = 'PROCEDURE';
```
---
### OpenGauss 常用函数
1. `WHERE your_column !~ '[0-9]$';`
- 使用正则表达式筛选 `your_column` 中**不以数字结尾**的记录。
2. `substr(app_id,0,13)`
- 从 `app_id` 字段中提取**从第 0 位开始共 13 位的子字符串**。
3. `position(concat(#{0,jdbctype=varchar}, '_') in prpo.app_id::text)`
- 在 `prpo.app_id` 中查找由参数拼接出的字符串 `#{0,jdbctype=varchar} + '_'` 的位置。
4. `substr(require_number::text,1,position('_' in require_number::text)-1)`
- 提取 `require_number` 字段中下划线前的部分。
5. `substring(pit.app_id :: text from position ( '_' in pit.app_id :: text ) + 1)`
- 提取 `pit.app_id` 中下划线后的内容。
6. `approve_steps = replace(approve_steps, regexp_substr(approve_steps, '-->权签人,[0-9]+'), concat('-->权签人,',#{info.authorizeUser,jdbcType=VARCHAR}))`
- 替换 `approve_steps` 字段中匹配的“-->权签人,数字”为指定用户。
7. `to_char(creation_date, 'YYYY-MM-DD HH24:MI:SS')`
- 将 `creation_date` 时间戳格式化为 **年-月-日 时:分:秒** 的字符串。
8. `to_char(last_update_date, 'YYYY-MM-DD HH24:MI:SS')`
- 同上，格式化 `last_update_date`。
9. `select exec_sql,exec_status,to_char(exec_time, 'yyyy-MM-dd hh24:mi:ss') exec_date,exec_error from t_datachange_execption_sql_log order by exec_time desc;`
- 查询日志表并按时间倒序排列，时间格式化。
---
### MySQL 常用函数
1. `select DATE_FORMAT( exec_time, '%Y-%m-%d %h-%m-%S' ) exec_t, exec_sql, exec_error from t_datachange_execption_sql_log order by exec_time desc;`
- 使用 `DATE_FORMAT` 格式化 `exec_time` 字段。
2. `SELECT * FROM tpl_property_t where value like 'http%' and (value like '%rndomp%' or value like '%opmtask%');`
- 查询 `value` 以 `http` 开头，并包含 `rndomp` 或 `opmtask` 的记录。
3. `SELECT * FROM tpl_lookup_item_t where ITEM_NAME like 'http%' and (ITEM_NAME like '%rndomp%' or ITEM_NAME like '%opmtask%');`
- 同上，对 `ITEM_NAME` 字段进行类似筛选。
---
1、不要更改我的内容，帮我排版
2、排版中所有标题前加上图标
3、能用表格展示的尽量用表格展示
最新发布
本文探讨了如何使用SQL子句SUBSTR在T3.KEYVALUE中提取特定部分，通过'/''的位置来截取前后数据。重点在于理解字符串操作在数据处理中的作用。
扫一扫
7370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
