【围观】麒麟芯片遭打压成绝版，华为亿元投入又砸向了哪里？>>> 递归查询
在Oracle中 建立一个表 create table nayi_180328_connect_test( dept_id varchar2(50), parent_id varchar2(50), dept_name varchar2(100), dept_rank varchar2(2000), val number);
插入语句 insert into nayi_180328_connect_test select 'root', '', '全国', '', 0 from dual union all select 'root_1', 'root', '北京市', '', 2000 from dual union all select 'ln_root', 'root', '辽宁省', '', 200 from dual union all select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual union all select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual union all select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual union all select 'jl_root', 'root', '吉林省', '', 0 from dual union all select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual union all select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual ;
Oracle的递归查询语句如下 select t1.*,CONNECT_BY_ROOT(dept_name) root_name from nayi_180328_connect_test t1 where 1=1 connect by prior t1.dept_id = t1.parent_id start with t1.dept_id = 'root' ;
结果如下

迁移MySQL 8 ,建表如下 create table nayi_180328_connect_test( dept_id varchar(50), parent_id varchar(50), dept_name varchar(100), dept_rank varchar(2000), val int, PRIMARY key (dept_id) )
插入语句与Oracle相同 insert into nayi_180328_connect_test select 'root', '', '全国', '', 0 from dual union all select 'root_1', 'root', '北京市', '', 2000 from dual union all select 'ln_root', 'root', '辽宁省', '', 200 from dual union all select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual union all select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual union all select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual union all select 'jl_root', 'root', '吉林省', '', 0 from dual union all select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual union all select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual
MySQL 8递归查询语句如下 with recursive t1(dept_id,parent_id,dept_name,dept_rank,val,root_name,rownum, order_str) as ( select t0.*,t0.dept_name,@rownum := 1 rn, cast(@rownum as char) sdfsf from nayi_180328_connect_test t0 where t0.dept_id='root' union all select t2.*,t1.root_name,@rownum := @rownum + 1 rn, concat(t1.order_str, '-', @rownum) st from nayi_180328_connect_test t2,t1 where t2.parent_id = t1.dept_id ) select * from t1 order by order_str
结果如下
日期转换字符串
Oracle中 select to_char(sysdate,'yyyy-mm-dd') from dual
结果
2020-05-07
MySQL 8中 select date_format(now(),'%Y-%m-%d') from dual
结果
2020-05-07 字符串转换日期
Oracle中 select to_date('2020-01-01','yyyy-mm-dd') from dual
结果
2020-01-01 00:00:00
MySQL 8中 select str_to_date('2020-01-01','%Y-%m-%d %h:%i:%s') from dual
结果
2020-01-01 00:00:00 判断为空，用其他值代替
Oracle中 select nvl(parent_id,'boot') from nayi_180328_connect_test where dept_id='root'
结果
boot
MySQL 8中 select ifnull(parent_id,'boot') from nayi_180328_connect_test where dept_id='root'
结果
boot 条件判断取值
Oracle中 select decode(parent_id,null,'全国','root','省市','地区') from nayi_180328_connect_test
结果

MySQL 8中 select case when parent_id is null then '全国' when parent_id='root' then '省市' else '地区' end from nayi_180328_connect_test
结果
聚合字段拼接
在Oracle中 WITH TEMP AS( SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL ) SELECT NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY) AS CITIES FROM TEMP GROUP BY NATION
结果

在MySQL 8中 WITH TEMP AS( SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL ) SELECT NATION,GROUP_CONCAT(city order by city) cities FROM TEMP GROUP BY NATION
结果
截取字符串
在Oracle中 select substr('HelloWorld',0,3) value from dual; select substr('HelloWorld',1,3) value from dual;
以上执行结果相同
Hel
在MySQL 8中 select substr('HelloWorld',1,3) value from dual;
在MySQL中，substr()的首索引不能为0
结果
Hel 数字格式化字符串
在Oracle中 select to_char(12345678.657,'999,999,999,999.99') from dual;
结果
12,345,678.66
在MySQL 8中 select format(12345678.657,2) from dual;
结果
12,345,678.66 跨库查询
在Oracle中
select 字段名 from 表名@库名
在MySQL 8中
select 字段名 from 库名.表名 拼接字符串
在Oracle中 select 'ABC' || 'EFG' from dual
结果
ABCEFG
在MySQL 8中 select concat('ABC','EFG') from dual
结果
ABCEFG 表分区
在Oracle中
我们先创建两个表空间 create tablespace CUS_TS01 datafile '/home/oracle/app/oracle/oradata/helowin/cus01.dbf' size 20m; create tablespace CUS_TS02 datafile '/home/oracle/app/oracle/oradata/helowin/cus02.dbf' size 20m;
此时查询表空间 select * from dba_tablespaces;
结果

我们可以看到最后两个表空间是我们刚生成的 CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 )
我们创建一个表，并生成两个表分区CUS_PART1,CUS_PART2.关于分区的分类可以参考 https://www.cnblogs.com/wnlja/p/3979684.html
插入一条数据 insert into customer values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1')
由于该分区是以数据的数目来进行分区的，前10W行在第一个分区，后10W行在第二个分区，所以我们按分区来进行查询时，只能在第一个分区查到该数据，而第二个分区是没有的 select * from customer partition(CUS_PART1)
select * from customer partition(CUS_PART2)

在MySQL 8中
创建同样的表，同样的分区，关于MySQL表分区的分类可以参考 https://www.cnblogs.com/zhouguowei/p/9360136.html CREATE TABLE CUSTOMER ( CUSTOMER_ID int NOT NULL, FIRST_NAME VARCHAR(30) NOT NULL, LAST_NAME VARCHAR(30) NOT NULL, PHONE VARCHAR(15) NOT NULL, EMAIL VARCHAR(80), STATUS CHAR(1), primary key (CUSTOMER_ID) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000), PARTITION CUS_PART2 VALUES LESS THAN (200000) )
插入同样的数据 insert into CUSTOMER values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1')
进行分区查询 select * from CUSTOMER partition(CUS_PART1)
select * from CUSTOMER partition(CUS_PART2)
日期相减
在Oracle中
很多人喜欢用
trunc(日期1)-trunc(日期2)
因为trunc(日期)可以去掉时间部分，比如 select sysdate from dual
结果
2020-05-14 09:59:27 select trunc(sysdate) from dual
结果
2020-05-14 00:00:00
注:trunc(日期,'dd')与trunc(日期)意义相同 select trunc(sysdate ,'dd') from dual ;
结果
2020-05-15 00:00:00
在MySQL 8中
mysql中没有trunc函数，直接用
日期1-日期2
但如果日期带了时间部分，比如 select now() from dual
结果
2020-05-14 02:15:14
如果此时用带时间的日期相减会出错，可以用如下方式处理 select str_to_date(now(),'%Y-%m-%d')-str_to_date('2020-05-01','%Y-%m-%d') from dual
结果
13
为了保险起见，可以将Oracle中的trunc(日期)转换成str_to_date(日期,'%Y-%m-%d') 获取当前年份的第一天
在Oracle中 select TRUNC(SYSDATE, 'yyyy') from dual
结果
2020-01-01 00:00:00
在MySQL 8中 SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY) from dual
结果
2020-01-01
date_sub() 从日期减去指定的时间间隔。
格式： DATE_SUB(date,INTERVAL expr type)
CURDATE() 函数返回当前的日期,不带时间 select curdate() from dual
结果
2020-05-14
DAYOFYEAR() 函数返回指定日期在一年中的位置 select dayofyear('2020-05-13') from dual
结果
134
DATE_SUB()函数的type为以下类型
Type值 MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR YEAR_MONTH
几个月之后(之前)的某天
在Oracle中 select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , 3) from dual union all select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , -3) from dual
结果
2020-04-01 00:00:00
2019-10-01 00:00:00
在MySQL 8中 SELECT ADDDATE('2020-01-01', INTERVAL 3 MONTH) from dual union all SELECT ADDDATE('2020-01-01', INTERVAL -3 MONTH) from dual
结果
2020-04-01
2019-10-01