Oracle 学习之 DML 语句

1. 数据库

2. 表

2.1. SELECT

SELECT * 
FROM SYS_USER
WHEREAGE > 18;

2.2. INSERT

INSERT INTO SYS.SYS_USER(ID, USER_NAME, AGE, SEX, CRT_BY, CRT_TM,UPD_BY,UPD_TM
) VALUES (sys_guid(), 'ZHANGSAN', 18, '1', '100001', sysdate,'100001',to_date('2024-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
);

2.3. UPDATE

UPDATESYS_USER
SET USER_NAME = 'ZHANGSAN',AGE = 18
WHEREUSER_ID = '0001';

2.4. DELETE

DELETE SYS_USER
WHERE USER_ID = '0001';

2.5. MERGE INTO

MERGE INTO SYS_USER su
USING (<foreach collection = "list" item = "item" separator="UNION ALL">SELECT #{req.id} AS id,#{req.userName} AS userName,#{req.age} AS age,#{req.sex} AS sex,#{req.crtBy} AS crtBy,#{req.crtTm} AS crtTm,#{req.updBy} AS updBy,#{req.updTm} AS updTmFROM dual</foreach>
) t
ON (su.id = t.id
)
WHEN MATCHED THEN UPDATE SET su.USER_NAME = t.userName,su.AGE = t.age,su.SEX = t.sex,su.UPD_BY = t.updBy,su.UPD_TM = t.updTm
WHEN NOT MATCHED THEN INSERT (su.ID,su.USER_NAME,su.AGE,su.SEX,su.CRT_BY,su.CRT_TM,su.UPD_BY,su.UPD_TM) VALUES (sys_guid(),t.userName,t.age,t.sex,t.crtBy,t.crtTm,t.updBy,t.updTm)

3. 字段

3.1. 数据类型

数据类型	分类	简述	示例	默认	大小
NUMBER	数字	精度：[1, 38] 小数位数：[-64, 127]	NUMBER(18,4)，其中18 为精度，4 为小数位数，	精度：38 小数位数：0
BINARY_DOUBLE	数字	用于存储双精度的64位浮点数
BINARY_FLOAT	数字
CHAR	字符串
VARCHAR	字符串
VARCHAR2	字符串
NVARCHAR2	字符串			sys_guid()
LONG	字符串
LONG RAW	字符串
BLOB	字符串
CLOB	字符串
NCLOB	字符串
DATE	时间
TIMESTAMP	时间
TIMESTAMP WITH TIME ZONE	时间
TIMESTAMP WITH LOCAL TIME ZONE	时间
INTERVAL_DAY_TO_SECOND	时间
INTERVAL_YEAR_TO_MONTH	时间
RAW	字符串

3.2. 特殊场景

场景	函数	示例
主键	sys_guid()
时间	sysdate	TO_DATE(‘2024-01-01 08:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) / TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’)

4.