表操作

创建表

CREATE TABLE TABLE_NAME (
  "ID" NUMBER(11,0) NOT NULL,
  "NICK_NAME" NVARCHAR2(100),
  "NAME" NVARCHAR2(100),
  "CREATE_TIME" DATE,
  "RULE" NCLOB, 
  "DELETED" NUMBER(3,0) DEFAULT 0,
  CONSTRAINT "TABLE_NAME_PK" PRIMARY KEY ("ID")
);

COMMENT ON TABLE TABLE_NAME IS '示例表';

COMMENT ON COLUMN TABLE_NAME.ID IS '主键自增';
COMMENT ON COLUMN TABLE_NAME.NICK_NAME IS '昵称';
COMMENT ON COLUMN TABLE_NAME.NAME IS '姓名';
COMMENT ON COLUMN TABLE_NAME.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN TABLE_NAME.RULE IS '规则';
COMMENT ON COLUMN TABLE_NAME.DELETED IS '逻辑删除（1：已删除）';

删除表

# 一次性彻底删除表
DROP TABLE TABLE_NAME PURGE;

# 删除普通表，并未真正删除，只是把该表放入回收站
DROP TABLE TABLE_NAME;
# 查看回收站中的表
SHOW RECYCLEBIN;
# 清空回收站中的表
PURGE RECYCLEBIN;

创建/删除序列和触发器

• 创建序列和触发器

#                                          最小值        最大值            每次自增值      起始值 
CREATE SEQUENCE "TABLE_NAME_ID_SEQ" MINVALUE 1 MAXVALUE 99999999 INCREMENT BY 1 START WITH 1 NOCACHE;
CREATE TRIGGER "TABLE_NAME_ID_SEQ" BEFORE INSERT ON "TABLE_NAME" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW 
BEGIN
#                                      主键字段（ID）  
 SELECT TABLE_NAME_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

• 查询序列和触发器

# 通过序列名称模糊查询
SELECT sequence_name FROM all_sequences WHERE sequence_name LIKE '%TABLE_NAME%'

# 直接通过表名查询触发器
SELECT trigger_name FROM all_triggers where table_name='TABLE_NAME';

• 删除序列和触发器

# 删除序列
DROP SEQUENCE SEQUENCE_NAME;

# 删除触发器
DROP TRIGGER TRIGGER_NAME;

数据操作

• 添加数据时候需要使用到的格式

# 时间类型
TO_DATE('2022-03-03 11:12:58', 'SYYYY-MM-DD HH24:MI:SS')

• 时间数据格式化为字符串

TO_CHAR(optime, 'YYYY-MM-DD')

• 时间函数

SYSTIMESTAMP

• 拼接过长字符串

update PORTAL_WINDOW set WINDOW_PROTOCOL_DETAIL = TO_CLOB('') 
where WINDOW_CODE = 'ywjj_jfdh_mgzshybnb';
update PORTAL_WINDOW set WINDOW_PROTOCOL_DETAIL = WINDOW_PROTOCOL_DETAIL ||  TO_CLOB('') 
where WINDOW_CODE = 'ywjj_jfdh_mgzshybnb';

• 用 to_char 匹配 clob 数据

SELECT * FROM PORTAL_AD WHERE to_char(EVENT_URL) = 'page'

• 删除表中重复数据（注意两条 SQL 中的查询条件要一致）

# 清理 TABLE_NAME 表中重复数据（以 FIELDS_NAME 字段判断）
DELETE FROM	TABLE_NAME WHERE (FIELDS_NAME) IN
(
	SELECT FIELDS_NAME FROM TABLE_NAME GROUP BY FIELDS_NAME HAVING count(*) > 1	
)
AND rowid NOT IN (
SELECT
	min(rowid)
FROM
	TABLE_NAME
GROUP BY
	FIELDS_NAME
HAVING
	count(*)>1
)

• 同步表中数据到其他表

# 把 TABLE_A 表的全部字段数据插入到 TABLE_B 表中（需要字段名相同）
INSERT INTO TABLE_B SELECT * FROM TABLE_A;

# 把 TABLE_A 表中某些字段的数据插入 TABLE_A 表中
INSERT INTO TABLE_B(FIELDS_NAME_1, FIELDS_NAME_2) SELECT FIELDS_NAME_1, FIELDS_NAME_2 FROM TABLE_A

• insert 中数据来自其他表

# FIELDS_NAME、TABLE_B_ID 表示 TABLE_A 表要插入数据的字段名称；VALUES 中 的查询结果作为 TABLE_B_ID 的值
INSERT INTO TABLE_A(FIELDS_NAME, TABLE_B_ID)
VALUES('字段名', (SELECT FIELDS_ID FROM TABLE_B WHERE FIELDS_ID = 条件));

主键操作

有命名主键

-- 删除主键
ALTER TABLE TABLE_NAME DROP CONSTRAINT "TABLE_NAME_PK";

-- 添加主键
ALTER TABLE TABLE_NAME ADD CONSTRAINT "TABLE_NAME_PK" PRIMARY KEY ("ID");

无命名主键

-- 删除（先查询出来主键名称再删除）
SELECT t.* from user_cons_columns t where t.table_name = 'TABLE_NAME' and t.position is not null;
ALTER TABLE TABLE_NAME DROP CONSTRAINT "SYS_C00914391";

-- 添加主键
ALTER TABLE TABLE_NAME ADD PRIMARY KEY ("ID");

字段操作

添加字段

ALTER TABLE TABLE_NAME ADD UPDATE_TIME DATE;
COMMENT ON COLUMN TABLE_NAME.UPDATE_TIME IS '修改时间';

删除字段

ALTER TABLE TABLE_NAME DROP COLUMN UPDATE_TIME;

修改字段

• 1、当字段没有数据或者要修改的新类型和原类型兼容时，可以直接 modify 修改

• 2、当字段有数据并用要修改的新类型和原类型不兼容时，要间接新建字段来转移

# 增加一个字段 MDF ，数据类型 nchar(20)
ALTER TABLE TABLE_NAME ADD MDF nchar(20);

# 如果字段数据为空，则不管改为什么字段类型，可以直接执行：
ALTER TABLE TABLE_NAME MODIFY (MDF DATE);

# 如果字段有数据，则改为 nvarchar2(20) 可以直接执行：
ALTER TABLE TABLE_NAME MODIFY (MDF nvarchar2(20));

# 如果字段有数据，则改为varchar2(40)执行时会弹出："ORA-01439:要更改数据类型,则要修改的列必须为空"，解决方案如下
# 修改原字段名 MDF 为 MDF_tmp
ALTER TABLE TABLE_NAME RENAME COLUMN MDF TO MDF_TMP;
# 增加一个和原字段名同名的字段 MDF
ALTER TABLE TABLE_NAME ADD MDF varchar2(40);
# 将原字段 MDF_TMP 数据更新到增加的字段 MDF
UPDATE TABLE_NAME SET MDF=TRIM(MDF_TMP);
# 更新完，删除原字段 MDF_TMP
ALTER TABLE TABLE_NAME DROP COLUMN MDF_TMP;

索引

• 创建索引

# 创建普通索引
CREATE INDEX INDEX_NAME ON TABLE_NAME(FIELDS_NAME);

# 创建唯一索引
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(FIELDS_NAME);

--创建主键索引
ALTER TABLE TABLE_NAME ADD CONSTRAINT TABLE_NAME ADD CONSTRAINT INDEX_NAME PRIMARY KEY (PRIMARY_KEY);

• 删除索引

-- 删除普通索引
DROP INDEX FIELDS_NAME;

--删除主键索引
ALTER TABLE TABLE_NAME DROP CONSTRAINT INDEX_NAME;

表分区

创建表时表分区

create table TEST_ACTIVITYWHILTE_LIST 
(
	PHONE NVARCHAR2(15),
	ACTIVITY_ID NVARCHAR2(20),
	PRIZE_ID NUMBER(11,0)
)
partition by list (ACTIVITY_ID)
(
partition p_acid1002 values ('1002'),
partition p_acid1003 values ('1003'),
partition p_acid1004 values ('1004'),
partition p_acid1005 values ('1005'),
partition p_acid1006 values ('1006'),
partition p_acid1007 values ('1007'),
partition p_acid1008 values ('1008')
);

已有表创建分区

利用原表重建分区表，因为白名单数据是预先导入的，不存在重建分区表时候有数据插入情况，可以保证数据的一致性，所以采用原表重建分区的方法

# 1、创建分区表 NEW_TEST_ACTIVITYWHILTE_LIST，从原表获取数据，这里表名只需加个标识以示区分，比如加个 NEW 的前缀
create table NEW_TEST_ACTIVITYWHILTE_LIST 
(PHONE,ACTIVITY_ID,PRIZE_ID)
partition by list (ACTIVITY_ID)
(
partition p_acid1002 values ('1002'),
partition p_acid1003 values ('1003'),
partition p_acid1004 values ('1004'),
partition p_acid1005 values ('1005'),
partition p_acid1006 values ('1006'),
partition p_acid1007 values ('1007'),
partition p_acid1008 values ('1008'))
AS SELECT PHONE,ACTIVITY_ID,PRIZE_ID FROM TEST_ACTIVITYWHILTE_LIST;

# 2、修改原表的表名，这里表名加了个 OLD 的后缀，方便区分
RENAME ACTIVITY_WHITE_LIST TO ACTIVITY_WHITE_LIST_OLD;

# 3、将基于原表新建的分区表修改表名，修改为原表的表名
RENAME NEW_ACTIVITY_WHITE_LIST TO ACTIVITY_WHITE_LIST;

# 4、到此为止，对于已经存在的普通表建分区表结束，需要注意的是分区表别忘了加局部索引
-- 创建局部索引（ACTIVITY_WHITE_LIST表名、ACTIVITY_ID, PHONE, PRIZE_ID局部索引字段）
create index u_acid_phone_prize on ACTIVITY_WHITE_LIST (ACTIVITY_ID, PHONE, PRIZE_ID) local;

查看分区

-- 查看分区数据（ACTIVITY_WHITE_LIST表名、p_acid1002分区名字）
select count(*) from ACTIVITY_WHITE_LIST partition(p_acid1002);

-- 查看有多少分区（ACTIVITY_WHITE_LIST表名）
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='ACTIVITY_WHITE_LIST';

-- 增加分区（ACTIVITY_WHITE_LIST表名、'1009'分区的值）
ALTER TABLE ACTIVITY_WHITE_LIST ADD PARTITION P_ACID1009 VALUES ('1009');

-- 重命名表分区（ACTIVITY_WHITE_LIST表名、P_ACID1009原分区名、P_ACID1010）
ALTER TABLE ACTIVITY_WHITE_LIST RENAME PARTITION P_ACID1009 TO P_ACID1010;