--启动数据库 ./bin/aty_ctl -D data -l arterydb.log start --连接数据库 ./bin/atysql -d artery --设置环境变量 export LD_LIBRARY_PATH='/home/arterydb/lib' --删除表 DROP TABLE table_name; --查询表是否存在 select count(*) into FYDM from information_schema.tables where table_schema='db_fy' and table_type='BASE TABLE' and table_name='t_aydm_map'; --创建函数 CREATE OR REPLACE FUNCTION Juge_Delete_table() RETURNS void AS $$ DECLARE FYDM INT; begin select count(*) into FYDM from information_schema.tables where table_schema='db_fy' and table_type='BASE TABLE' and table_name='t_aydm_map'; if FYDM>0 then drop table db_fy.t_aydm_map; end if; end $$ language platysql; --调用函数 select Juge_Delete_table(); --查询索引 select count(*) from aty_indexes where indexname = 'i_jg_fy'; --查询条件必须小写 --创建角色及权限 CREATE ROLE admin WITH CREATEDB CREATEROLE; --更改密码 ALTER ROLE davide WITH PASSWORD 'hu8jmn3'; 此部分详见公司内网（http://172.16.6.233/ArteryDB/sql-createrole.html） --创建索引 create index I_JG_FY on DB_FY.T_JG (N_FY); --删除索引 drop index db_fy.i_jg_fy;(索引是对应的,创建db_fy下的索引,对应删除要删shcame"db_fy"下的索引;不指定schame则删除也不用指定) --查找表数量 select count(*) from aty_tables where schemaname like '%imdb%'; sybase->arterydb类型转换 binary->bytea image->bytea tinyint->smallint datetime->timestamp 执行sql文件 ./bin/atysql -h localhost -d artery -U arterydb -f /home/shsql/ods2etl/D_MSAJLX_ZH.txt; bytea类型插入图片 创建存储过程 create or replace function bytea_import(p_path text,p_result out bytea)as $$ declare l_oid oid; r record; begin p_result := ''; select lo_import(p_path) into l_oid; for r in ( select data from aty_largeobject where loid = l_oid order by pageno ) loop p_result = p_result || r.data; end loop; perform lo_unlink(l_oid); end $$ language platysql; 执行插入语句 insert into db_fy.aaa(a) select bytea_import('/home/arterydb/untitled.png'); 查找固定表在哪个字段 SELECT c.relname,col_description (a.attrelid,a.attnum) AS COMMENT,format_type (a.atttypid,a.atttypmod) AS TYPE,a.attname AS NAME,a.attnotnull AS notnull FROM aty_class AS c,aty_attribute AS a WHERE a.attrelid = c.oid AND a.attnum > 0 AND a.attname = 'n_ajbs' 查看表结构 SELECT attname,typname,adsrc FROM aty_attribute INNER JOIN aty_class ON aty_attribute.attrelid = aty_class.oid INNER JOIN aty_type ON aty_attribute.atttypid = aty_type.oid LEFT OUTER JOIN aty_attrdef ON aty_attrdef.adrelid = aty_class.oid AND aty_attrdef.adnum = aty_attribute.attnum LEFT OUTER JOIN aty_description ON aty_description.objoid = aty_class.oid AND aty_description.objsubid = aty_attribute.attnum WHERE aty_attribute.attnum > 0 AND attisdropped <> 't' AND aty_class.relname= 't_aydm' --t_aydm为表名 ORDER BY aty_attribute.attnum ; 查看当前数据库连接数 SELECT aty_stat_get_backend_pid(s.backendid) AS procpid,aty_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT aty_stat_get_backend_idset() AS backendid) AS s; 数字转字符串 select to_char(1252323,'9999999999999'); select to_char(current_timestamp,'YY-MM-DD HH12:MI:SS'); arterydb重启数据库 设置环境变量 export ATYDATA="/home/arterydb/data" 重启