Oracle 常用

语法优化：

Oracle的解析器按照从右到左的顺序处理From子句中的表名，因此From子句中写在最后的表将最先被处理。一般放数据最少的表，或者多表的交叉表作为基础表。
Oracle采用自下而上的顺序解析Where子句，根据这个原理，表之间的连接必须写在;qitaWhere条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾。
执行顺序：FROM，ON，JOIN，WHERE，GROUP BY，WITH CUBE 或 WITH ROLLUP，HAVING，SELECT，DISTINCT，ORDER BY，TOP

常用字符串处理

WITH a AS (
SELECT
	'123456' AS "A"
FROM
	dual)
SELECT
	'拼接' || '字符串' AS "||拼接",
	concat('拼接' , '字符串') "concat拼接",
	regexp_substr('12.HK', '[^.]+', 1, 1) AS "字符正则截取",
	SUBSTR(a.A, 0) "截取0",
	SUBSTR(t.B, 2) "截取2",
	SUBSTR(a.A,-2) "截取-2",
	SUBSTR(a.A, 0, 5) "截取0-5",
	INSTR('123456789123456', '6', 3, 2) "查找位置",
	REPLACE('替换字符串', '替换', '修改') AS "字符替换",
	to_char(nvl(a.A, 0), 'FM99,999,999,999,990.00')  as "数字格式",
	to_date('20210629 122436', 'yyyymmdd HH24MISS') "time",
	to_char(sysdate,'yy-mm-dd hh24:mi:ss') "timeString",
	CASE
		WHEN LENGTH('123') < 5 THEN TO_CHAR('132' , '00000')
		ELSE '123'
	END AS "数字补零",
	DECODE(regexp_substr('12.HK', '[^.]+', 1, 2), 'HK', concat(SUBSTR('00000' , 0, 5- LENGTH(regexp_substr('12.HK', '[^.]+', 1, 1))) , regexp_substr('12.HK', '[^.]+', 1, 1)), regexp_substr('12.HK', '[^.]+', 1, 1)) AS "if"
FROM
	dual,a
LEFT JOIN (SELECT '123456' AS "A",'456789' AS "B"
FROM
	dual) t ON t.A = a.A;

结果：

添加数据

添加一条数据

insert into tableName (CODE, NAME,REMARK, DATESTR) values ('aa','bb','123','202011');

Oracle添加多条数据

insert all
 into tableName (CODE, NAME,REMARK, DATESTR) values ('aa','bb','123','202011')
 into tableName (CODE, NAME,REMARK, DATESTR) values ('aa','bb','123','202012')
 select 1 from dual;

在mybatis的xml文件中

<insert id="demoList" parameterType="demo">
	INSERT ALL
		<foreach collection="list" item="item" separator="">
			INTO tableName
			(CODE, NAME,REMARK, DATESTR)
			VALUES (#{item.code},
			#{item.name},
			#{item.remark},
			#{item.dateStr})
		</foreach>
	select 1 from dual
</insert>

注意在xml中不要写‘;’分号，会报错。

更新数据

update table1 set id = 'bb' where id='cc';

删除数据

delete from table1 where id ='cc';
删除表
drop table table1;

查询