目录

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;