MySql
Oracle
别名 oracle中不能用 file 做别名
Oracle中长字符串处理方法
update mall_config set category_info='安全防护:3003,' where id=1
update mall_config set category_info=category_info||'|标准件:1040140,1035382,' where id=1
或者
update mall_config set category_info=concat(category_info,'|标准件:1040140,1035382,') where id=1
oracle实现mysql中的limit
mysql:
SELECT * FROM basedata_equ_file WHERE etag_code='0000-1306-0110-0100-0102-0000' AND organization_id=2 limit 1
oracle:
SELECT * FROM basedata_equ_file WHERE etag_code='0000-1306-0110-0100-0102-0000' AND organization_id=2 AND rownum=1;
oracle 中时间需要 to_timestamp
const char* TIME_STR_FORMAT = "yyyy-mm-dd hh24:mi:ss";
return "to_timestamp('" + src + "', '" + TIME_STR_FORMAT + "')";
提升为管理员:
conn sys/oracle as sysdba
创建用户:
create user aims_test identified by 12345;
grant create session,resource to aims_test ;
删除用户:
drop user aims_test cascade;
删除列:
alter table test_position_list drop column test_id;
新建列:
alter table table_name add (rowName number(11));
带外键:
ALTER table Organ_position_list ADD CONSTRAINT organ_position_list_ordId
FOREIGN KEY (organization_id)
REFERENCES Organ_organization (id);
查看约束:
select table_name from user_constraints where constraint_name='SYS_C0024257';
重命名列:
alter table test_table rename column "ORDER" to order_sort;
选择序列:
select trigger_name from all_triggers where table_name='BASEDATA_RFID';
select trigger_body from user_triggers where trigger_name='BASEDATA_RFID_TR';
查看序列:
select max(id) from patrol_list;
desc user_sequences;
select sequence_name from user_sequences;
创建序列:
create sequence prtrolSeq minvalue 1 maxvalue 99999999999999 start with 1 increment by 1 cache 20;
应用序列:
CREATE OR REPLACE TRIGGER testSeq
BEFORE INSERT ON patrol_list FOR EACH ROW WHEN (new.id is null)
begin
select testSeq.nextval into:new.id from dual;
end;
/
修改序列:
ALTER SEQUENCE testSeq INCREMENT BY 1000;
SELECT testSeq.NEXTVAL FROM DUAL;
ALTER SEQUENCE testSeq INCREMENT BY 1;