MySql

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;