觉得不错点赞收藏下吧 随时更新哦 有问题可以交流
1. 数据库的启停
到 Server/bin 目录下执行进入数据库：ksql -U用户名 -W -d数据库1. 起库：sys_ctl -D /home/kingbase/data start停库：sys_ctl -D /home/kingbase/data stop2.初始化数据库实例：
initdb -USYSTEM -x密码 -D /abc/data(大小写敏感)initdb -USYSTEM -x密码 -D /abc/data --enable-ciV008R006 C005 之后的版本适用 (大小写不敏感 on是不敏感)initdb -USYSTEM -x密码-D /abc/data --case-insensitive V008R006 C004 之前适用 (大小写不敏感on敏感)3.更改 search_path 解决查表不加模式名时报错：关系 xxx 不存在
ALTER DATABASE 数据库名 SET search_path to "$user", 模式名, public, sys_catalog, sys, pg_catalog;然后执行 select sys_reload_conf(); 重载配置文件。4.设置返回字段大写
ALTER DATABASE 数据库名 SET enable_upper_colname=on;然后执行 select sys_reload_conf(); 重载配置文件。5.兼容MySQL的 GROUP BY 子句中缺省字段名的语法
ALTER DATABASE 数据库名 SET sql_mode='';然后执行 select sys_reload_conf(); 重载配置文件。6.忘记或没有设置 SYSTEM 密码导致无法登录数据库
先去 data 目录下找到 sys_hba.conf 打开后找到最下面 IPv4 local connections: 项，到第一行，把 scram-sha-256 改为 trust 然后保存，重启数据库后免密登录数据库，再执行下面的语句修改system 密码ALTER USER system PASSWORD ' 新密码 ';7.kingbase7去除死锁的语句是什么？已经查到pid
SELECT sys_cancel_backend(pid);8.windows跟换授权后显示write license control file faild
修改目录权限9.windows中54321端口被占用
netstat -ano |findstr '54321'tasklist | findstr "进程号"taskkill /pid 进程号10.查询表准确膨胀率的sql
select relname,n_live_tup,n_dead_tup from sys_stat_user_tables order by n_dead_tup desc; 11.输入一个日期，让sql往后或者往前推60年
select date'2022-3-5' + interval '1 year';- 配置 ’ ’ 代替 null
ALTER DATABASE 数据库 set ora_input_emptystr_isnull=true;select sys_reload_conf();13.应用代码中事务自动提交的问题
conn.setAutoCommit(false); 14.查询模式下的所有的记录数
SELECT count(table_name) FROM information_schema.TABLES WHERE table_schema =‘information_schema’;--schema填自己的实际的schema_name或者：SELECT relname, reltuplesFROM sys_catalog.sys_class r JOIN sys_catalog.sys_namespace nON (relnamespace = n.oid)WHERE relkind = 'r' AND n.nspname = 'public'order by reltuples desc15.模糊查询字段
SELECT * FROM TABLE_NAME WHERE CONCAT(FIELD1,FIELD2) LIKE '%STRING%'16.日期采用date，不要后缀00:00:00
ALTER DATABASE TEST set ORA_STYLE_NLS_DATE_FORMAT to 'on';select sys_reload_conf();17.查看事务隔离级别
SHOW TRANSACTION ISOLATION KINGBASE_INTERNAL_LEVEL; 18.R6非交互式的方式登录数据库
ksql "host=1.1.1.1 port=54321 user=system dbname=test password=123456"19.非交互式还原数据库
PGPASSWORD=123456 sys_restore -h127.0.0.1 -Usystem -d abc xxx.dmp20.数据库迁移编码报错问题
配置文件添加参数，重启数据库后生效 ignore_char_null_check=on21.授权序列
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to www; 22.查看当前账号会话数
select count(*), usename from sys_stat_activity group by usename;23.设置varchar和char之间匹配导致索引失效的问题
set ora_enable_varchar_match_bpchar to on24.查看表的存储路径
select sys_relation_filepath('表名');25.查看会话进程
select datname,pid,usename,state,queryfrom sys_stat_activity;26.通过copy还原、导出csv格式的数据
\copy table_1 from '/home/kingbase/b.csv'csv通过copy还原csv格式的数据到表中\copy table_1to'/home/kingbase/b.csv' csv 导出表数据到csv文件27.查看历史执行的sql命令
修改配置文件kingbase.conf,开启参数 log_statement='all'，sys_stat_statements.track='all' ，重启数据库。进入数据库执行select query,callsfrom sys_stat_statements28.查看sql的执行时间
select query,calls,round(mean_exec_time,2)as mean_exec_time from sys_stat_statements order by mean_exec_time desc; 29.两表关联更新字段
update A set A.1 = B.1 from B where A.id = B.id30.授权A用户访问B用户的表
grant usage on schema 模式名 to 用户名; grant select on table 表名 to 用户名 31.时间戳转换为时间
select to_char(to_timestamp(1628088734), 'yyyy-mm-dd hh24:mi:ss')32.设置时间分区
set ora_func_style = false;create table part_test(id int, info text, crt_time timestamp not null);select create_range_partitions('PART_TEST'::regclass, 'crt_time','2018-10-01 00:00:00'::timestamp, interval '1 month',24,false) ;33.生成UUID的命令
select sys_guid();34.将日期格式化成特定格式
DATE_FORMAT(date TIMESTAMP,format TEXT);35.初始化编码问题
初始化添加参数 --locate=C36.将一个字段中的值拼接
string_agg(字段A,'分隔符') 37.将字符串转换为数字类型 后面99999表示保留的位数，数字只能用9
to_number('12345','99999') 38.实现mysql的date_add功能
select current_date + interval '0 hours'; - 获取月份的函数
select date_part('month',now());- 查看所有的序列属性
SELECT * FROM "pg_class" "c" WHERE "c"."relkind" = 'S';- 查看字段名称，类型，注释
SELECT a.attname as 字段名,format_type(a.atttypid,a.atttypmod) as 类型, a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释 FROM pg_class as c,pg_attribute as a where a.attrelid = c.oid and a.attnum>0 and c.relname = '你的表名';- 设置中文占多少字节
配置文件添加这个参数char_default_type=''char'' V8R6默认就是char不需要设置43.查看字段类型
select sys_typeof(name )from aa44.查看库表数据量【不是太准】
select pg_size_pretty(pg_database_size('库名'));- 查询所有的函数
SELECTpg_proc.proname AS "函数名称",pg_type.typname AS "返回值数据类型",pg_proc.pronargs AS "参数个数"FROMpg_procJOIN pg_type ON (pg_proc.prorettype = pg_type.oid) - 查看所有的函数，类型，拥有者（R6）
SELECT n.nspname as "Schema",p.proname as "Name",pg_catalog.pg_get_function_result(p.oid) as "Result data type", CASE p.prokindWHEN 'a' THEN 'agg'‘’WHEN 'w' THEN 'window'WHEN 'p' THEN 'proc'ELSE 'func' END as "Type", pg_catalog.pg_get_userbyid(p.proowner) as "Owner"FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolangWHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname 'pg_catalog'AND n.nspname 'information_schema'AND n.nspname 'sys'AND n.nspname 'sys_catalog'ORDER BY 1, 2, 4;- 用sql命令查看ddl语句
create extension dbms_metadataSELECT dbms_metadata.get_ddl('table', 'aa'); - 用sql命令查看ddl语句（2）
CREATE OR REPLACE FUNCTION tabledef(text,text) RETURNS textLANGUAGE sql STRICT AS $$WITH attrdef AS (SELECT n.nspname, c.relname, c.oid, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts,c.relpersistence, a.attnum, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype, (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault,a.attnotnull, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation t.typcollation) as attcollation,a.attidentity, a.attgenerated FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON a.attrelid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE n.nspname = $1 AND c.relname = $2 AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum), coldef AS (SELECT attrdef.nspname, attrdef.relname, attrdef.oid, attrdef.relopts, attrdef.relpersistence, pg_catalog.format('%I %s%s%s%s%s', attrdef.attname, attrdef.atttype, case when attrdef.attcollation is null then '' else pg_catalog.format(' COLLATE %I', attrdef.attcollation) end, case when attrdef.attnotnull then ' NOT NULL' else '' end, case when attrdef.attdefault is null then '' else case when attrdef.attgenerated = 's' then pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault) when attrdef.attgenerated '' then ' GENERATED AS NOT_IMPLEMENTED' else pg_catalog.format(' DEFAULT %s', attrdef.attdefault)endend, case when attrdef.attidentity'' then pg_catalog.format(' GENERATED %s AS IDENTITY', case attrdef.attidentity when 'd' then 'BY DEFAULT' when 'a' then 'ALWAYS' else 'NOT_IMPLEMENTED' end) else '' end ) as col_create_sqlFROM attrdefORDER BY attrdef.attnum), tabdef AS ( SELECT coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence, concat(string_agg(coldef.col_create_sql, E',\n') , (select concat(E',\n',pg_get_constraintdef(oid)) from pg_constraint where contype='p' and conrelid = coldef.oid))as cols_create_sql FROM coldefGROUP BY coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence)SELECT FORMAT( 'CREATE%s TABLE %I.%I%s%s%s;', case tabdef.relpersistence when 't' then ' TEMP' when 'u' then ' UNLOGGED' else '' end, tabdef.nspname, tabdef.relname, coalesce( (SELECT FORMAT( E'\nPARTITION OF %I.%I %s\n', pn.nspname, pc.relname, pg_get_expr(c.relpartbound, c.oid) ) FROM pg_class c JOIN pg_inherits i ON c.oid = i.inhrelidJOIN pg_class pc ON pc.oid = i.inhparentJOIN pg_namespace pn ON pn.oid = pc.relnamespaceWHERE c.oid = tabdef.oid ),FORMAT( E' (\n%s\n)', tabdef.cols_create_sql) ),case when tabdef.relopts '' then format(' WITH (%s)', tabdef.relopts) else '' end,coalesce(E'\nPARTITION BY '||pg_get_partkeydef(tabdef.oid), '') ) as table_create_sqlFROM tabdef$$;调用:select tabledef('模式名','表名');- kingbase强制走索引【需要安装hint插件】
select /*+ index(student ss)*/ * from student where name='xx'- 给函数创建索引
create index 索引名 ON 表名(to_char(字段,'格式'))；- sql强行走索引
select /*+ index(student ss)*/ * from student where name='xx'- 查询表的所有记录数
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables where schemaname='public'ORDER BY n_live_tup DESC; - 查询结果加一列序列
ROW_NUMBER() over(ORDER bY name DESC ) AS num- 当前时间的时间戳
select floor(extract(epoch from now())); - 获取某个用户下的所有表
select * from pg_tables where TABLEOWNER='用户名';- 获取指定年份的所有数据
1、创建year函数之后调用CREATE OR REPLACE INTERNAL FUNCTION YEAR(dtime DATE) RETURNS BIGINT AS 'BEGIN RETURN extract(year from dtime);END;' LANGUAGE plsql;select * from date_test where year(日期字段)='年份';2、使用to_char函数获年份实现
select * from date_test where to_char(日期字段,'YYYY')='年份';- 获取数据库中所有模式的所有表的结构(表名，表注释，字段名，字段类型，是否为空，注释)
SELECT c.relname as 表名,cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述", a.attname as 字段名, format_type(a.atttypid,a.atttypmod) as 类型, a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释 FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypidwherea.attrelid = c.oid and a.attnum>0;- 指定模式下指定表的结构（所有表可以把指定表名的条件去掉）
selectcol.table_schema,col.table_name,col.ordinal_position,col.column_name,col.data_type,col.character_maximum_length,col.numeric_precision,col.numeric_scale,col.is_nullable,col.column_default,des.descriptionfrominformation_schema.columns col left join pg_description des oncol.table_name::regclass = des.objoidand col.ordinal_position = des.objsubidwheretable_schema = 'public'and table_name = 'table_name'order byordinal_position;- 查看某个用户下有哪些数据库（u6是用户名）
SELECT a.datname from sys_database a,pg_authid b where a.datdba=b.oid and b.rolname='u6';- 兼容mysql的date_format()函数
兼容敏感
create or replace function date_format(para1 timestamp,para2 text) return textasdeclareform1 text;beginform1=replace(para2,'%M','Month');form1=replace(form1,'%W','Day');form1=replace(form1,'%D','DDth');form1=replace(form1,'%Y','YYYY');form1=replace(form1,'%y','yy');form1=replace(form1,'%a','Dy');form1=replace(form1,'%d','DD');form1=replace(form1,'%e','DD');form1=replace(form1,'%m','MM');form1=replace(form1,'%c','MM');form1=replace(form1,'%b','Mon');form1=replace(form1,'%j','DDD');form1=replace(form1,'%H','HH24');form1=replace(form1,'%k','HH24');form1=replace(form1,'%h','HH');form1=replace(form1,'%I','HH');form1=replace(form1,'%l','HH');form1=replace(form1,'%i','MI');form1=replace(form1,'%r','HH:MI:SS');form1=replace(form1,'%T','HH24:MI:SS');form1=replace(form1,'%S','SS');form1=replace(form1,'%s','SS');form1=replace(form1,'%%','%');return to_char(para1,form1);end;可以进行改写
Mysql：select date_format(CURRENT_TIMSTAMP, ‘%Y-%m-%d %H:%i:%s’)
KES：select to_char (CURRENT_TIMSTAMP, ‘yyyy-mm-dd hh24:mi:ss’)
60、兼容mysql的date_sub函数
create or replace function date_sub(v_date text , v_interval interval)returns text as $$declarev_rt text;beginselect to_char(v_date::timestamp(0) - v_interval,'yyyy-mm-dd hh24:mi:ss' ) into v_rt;if length(v_date) = 10 and v_rt like '% 00:00:00' thenselect substr(v_rt,0,10) into v_rt;end if;return v_rt;end;$$LANGUAGE plsql;