> For the complete documentation index, see [llms.txt](https://blog.zj0395.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://blog.zj0395.com/sql/mysql.md).

# 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;
  ```