ORACLE和MYSQL⼀些函数和实现效果的对⽐、替换
⼯作中⽤到了oracle和mysql，所以如果sql有差异性就要提供两套。这篇⽂章来总结下oracle和mysql函数之间的区别、是否可替换。
⼀.Oracle的with x AS (⼦查询), y AS (⼦查询)…瑞典美食
这个语法很简单，可以认为是先创建了⼀（多）个临时结果集，再在后⾯使⽤这些结果集操作。其实就是公⽤表表达式（CTE）
WITH A AS(
SELECT USER_NAME,OFFICE_CODE FROM T_SYS_ACCOUNT_INFO
),B AS(
SELECT OFFICE_CODE,OFFICE_NAME FROM T_SYS_OFFICE
)
SELECT A.USER_NAME,B.OFFICE_NAME FROM A LEFT JOIN B ON A.OFFICE_CODE=B.OFFICE
_CODE;
效果：
上⾯的sql其实⼀个表连接就出来，只是作为⼀个简单的例⼦⽽已。实际场景应该是⾸先可能会查不同的表筛选⼀部分数据，最后再汇总这些数据再进⾏筛选查询，可读性很⾼。如果没有这样的语法，可能就是纯表连接查询，想想sql会有臃肿。mysql8才提供该语法。注意：with之后，要紧跟sql语句。
⼆.Oracle递归函数，connect by
基本语法：
lect * from table [start with condition1]项目英语
connect by [prior] id=parentid
1、 start with condition1 and condition2 or condition3…：代表从哪⼀（满⾜条件的）层级作为根节点开始查询
行为习惯有哪些
2、递归查询的⽅式。可以认为当前数据按照什么⽅式去查询下⼀层数据。例如，connect by prior id=parentid的意思就是当前数据的id 当做下⼀层数据的parentid查询（查询⼉⼦）；connect by id=prior parentid的意思是当前数据的parentid是下⼀层数据的id（查询⽗亲）。
3、测试语法，准备数据：
（1）查询china所有⼦级数据：
SELECT CITY_NAME,CITY_CODE,PARENT_CITY_CODE,LEVEL
FROM TEST_CITY START WITH CITY_CODE='000000'CONNECT BY PRIOR CITY_CODE=PARENT_CITY_CODE;
解析：①LEVEL关键字，查看数据所在的层数，1级就是根节点。②START WITH CITY_CODE=‘000000’ 把
CITY_CODE='000000’的数据当做根节点（level为1）③CONNECT BY PRIOR CITY_CODE=PARENT_CITY_CODE，查询当前层级的CITY_CODE是下⼀层的PARENT_CITY_CODE的数据（当前层数数据的⼦级）
（2）查询浙江省杭州市的⽗级数据：
SELECT CITY_NAME,CITY_CODE,PARENT_CITY_CODE,LEVEL
FROM TEST_CITY START WITH CITY_CODE='330100'CONNECT BY CITY_CODE=PRIOR PARENT_CITY_CODE;
结果：
解析：①START WITH CITY_CODE='330100’把浙江省杭州市当做根节点 ②CONNECT BY CITY_CODE=PRIOR
PARENT_CITY_CODE，当前层级的PARENT_CITY_CODE是下⼀层级的CITY_CODE（当前层级数据的⽗级）
（3）START WITH 多个条件，⽗级查询：
SELECT CITY_NAME,CITY_CODE,PARENT_CITY_CODE,LEVEL
FROM TEST_CITY START WITH CITY_CODE='330100'OR CITY_CODE='330700'
CONNECT BY CITY_CODE=PRIOR PARENT_CITY_CODE;
结果：
解析：可以看到结果是START WITH 满⾜条件的数据当做根节点（分开）查询
（4）把START WITH 换成WHERE 以及 没有START WITH 、WHERE，⽗级查询
SELECT CITY_NAME,CITY_CODE,PARENT_CITY_CODE,LEVEL
ps提取印章FROM TEST_CITY WHERE CITY_CODE='330100'CONNECT BY CITY_CODE=PRIOR PARENT_CITY_CODE;
结果：
why？ 莫⽅，往下看，没有START WITH 和 WHERE：
SELECT CITY_NAME,CITY_CODE,PARENT_CITY_CODE,LEVEL
FROM TEST_CITY CONNECT BY CITY_CODE=PRIOR PARENT_CITY_CODE;
解析：这两个情况结合来看，没有START WITH和WHERE的时候，其实就是把所有数据都当做根节点来查询；⽽有WHERE的时候，就是在上⾯情况筛选出符合条件的数据⽽已。
（5）趁热打铁，START WITH 和 WHERE同时存在的语法呢？效果是什么？⽗级查询
SELECT CITY_NAME,CITY_CODE,PARENT_CITY_CODE,LEVEL
FROM TEST_CITY WHERE CITY_CODE='330000'START WITH CITY_CODE='330102'CONNECT BY CITY_CODE=PRIOR PARENT_CITY_CODE ;
结果：
根据（4）的解析和结果的level猜测，这个WHERE还是从递归查询结果筛选，去掉WHERE：
SELECT CITY_NAME,CITY_CODE,PARENT_CITY_CODE,LEVEL
FROM TEST_CITY START WITH CITY_CODE='330102'CONNECT BY CITY_CODE=PRIOR PARENT_CITY_CODE ;
结果：
解析：CONNECT BY 和 WHERE 连⽤，效果是WHERE（后）在CONNECT BY（先）的结果再次筛选
4、⼩结：
（1）START WITH 可以有多个条件；效果其实就是先WHERE后递归查询
（2）如果有WHERE，只能放在START WITH之前，效果是先递归查询后WHERE
吵闹的的英文（3）①CONNECT BY PRIOR COLUNM_A= COLUNM_B，当前层级的COLUNM_A是下⼀层的COLUNM_B②CONNECT BY COLUNM_A=PRIOR COLUNM_B，当前层级的COLUNM_B是下⼀层的COLUNM_A
（4）遗憾的是，mysql并没有⾃带的递归查询函数，想要实现递归查询效果的话就是表关联或者⾃⼰写⼀个存储过程。
三.ORACLE和MYSQL时间、时间戳字段
1、向表⾥新增date、timestamp字段并添加字段说明：
（1）oracle：
ALTER TABLE TEST_CITY ADD(CREATE_DATE DATE,UPDATE_TIMESTAMP TIMESTAMP);
COMMENT ON COLUMN TEST_CITY.CREATE_DATE IS'创建时间，date';
COMMENT ON COLUMN TEST_CITY.UPDATE_TIMESTAMP IS'更新时间，timestamp';
（2）mysql：
ALTER TABLE test_city ADD(CREATE_DATE DATETIME COMMENT'创建时间，datetime',
UPDATE_TIMESTAMP TIMESTAMP(0)NOT NULL DEFAULT CURRENT_TIMESTAMP(0)ON UPDATE CURRENT_TIMESTAMP(0)COMMENT'更新时间，timestamp');
解析：①oracle的timestamp类型的字段，新增的时候值可以为null；mysql不⾏ ②mysql的date类型只有年⽉⽇，datetime是年⽉⽇ 时分秒。②mysql新增timestamp类型字段，(n)代保留毫秒数⼏位，最
⼤6位。具体效果如图：
2、更新数据：
（1）oracle：
UPDATE TEST_CITY SET CREATE_DATE=TO_DATE('2019-11-25 15:23:55','YYYY-MM-DD HH24:mi:ss')WHERE CITY_CODE='000000';
UPDATE TEST_CITY SET UPDATE_TIMESTAMP=TO_TIMESTAMP('2019-11-01 15:15:15','YYYY-MM-DD HH24:mi:ss')WHERE CITY_CODE='000000';
ORACLE添加date类型数据要使⽤到TO_DATE函数，timestamp类型⽤TO_TIMESTAMP函数。
（2）、mysql：
UPDATE TEST_CITY SET CREATE_DATE='2019-11-25 15:23:55'WHERE CITY_CODE='000000';
UPDATE TEST_CITY SET UPDATE_TIMESTAMP='2019-11-01 15:15:15'WHERE CITY_CODE='000000';
没错，mysql就是这么爽，⾃动帮你转换时间类型！
3、格式化时间：
（1）oracle：
SELECT TO_DATE('2019-11-25 15:15:15','YYYY-MM-DD HH24:MI:SS')AS"DATE"FROM DUAL;
SELECT SYSDATE FROM DUAL;
结果：
sysdate查询当前时间的格式就是年-⽉-⽇ 时：分：秒 不需要再to_date格式化
（2）mysql：
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')AS"DATETIME";
结果：
4、顺便说⼀下删除字段：
（1）oracle：
ALTER TABLE TEST_CITY DROP(CREATE_DATE,UPDATE_TIMESTAMP);
（2）mysql：
ALTER TABLE TEST_CITY DROP CREATE_DATE,DROP UPDATE_TIMESTAMP;
5、再赠送⼀个⼩技巧吧，查询当前时间到n天之前之间的所有⽇期（多⾏）：
（1）oracle：ROWNUM <= n是控制间隔天数的关键
SELECT TO_CHAR(SYSDATE-(ROWNUM-1),'YYYY-MM-DD') DATE_TIME,ROWNUM
FROM DUAL围魏救赵的主人公是谁
CONNECT BY信不信由你博物馆
ROWNUM <=7;
效果：
（2）mysql：between date_sub(curdate(),interval n-1 day)是控制间隔天数的关键
mysql没有connect by语法，但是万能的⽹友总有办法：
杨玉环之死Date AS DAY_TIME from(lect DATE_SUB(CURDATE(),interval(a.a +(10* b.a))day)as myDate from
(lect0as a union all lect1union all lect2union all lect3union all lect4union all lect5union all lect6union all lect7union all s elect8union all lect9)as a cross join(lect0as a union all lect1union all lect2)as b )a
Date between date_sub(curdate(),interval7-1day)and curdate();
效果：
四.oracle、mysql指定排序规则
