项目去O过程遇到的一些mysql和oracle语法区别

最新推荐文章于 2021-02-16 04:38:30 发布

ะัี潪ิื

最新推荐文章于 2021-02-16 04:38:30 发布

阅读量662

点赞数 3

分类专栏： sql 文章标签： sql oracle mysql 数据库

本文链接：https://blog.csdn.net/cz1803472613/article/details/108707498

版权

sql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1、获取当前时间

select now() from dual; --mysql
select sysdate() from dual; --mysql:

--两个都是可查询出当前时间的一般都是用NOW()，因为SYSDATE获取当时实时的时间，这有可能导致主库和从库是执行的返回值是不一样的，导致主从数据不一致。
select sysdate form dual; --Oracle

2、字符串拼接

select concat('a','b','c') from dual; --mysql
SELECT CONCAT("a",CONCAT('acb')) FROM DUAL; --mysql
select  'a'||'b'||'c' from dual; --Oracle

3、日期转换

SELECT * FROM ad.ca_bill_0_202006 WHERE end_date > STR_TO_DATE('2020-06-01 00:00:00','%Y-%m-%d'); --mysql(%Y 表示yyyy(2019), %y 表示yy(19))

SELECT * FROM ad.ca_bill_0_202006 WHERE end_date 
> TO_DATE('2020-06-01 00:00:00','yyyy-mm-dd 
hh24:mi:ss'); --Oracle

to_date('01-01-2020 02:02:18', 'dd-mm-yyyy hh24:mi:ss') -- Oracle
STR_TO_DATE'11-01-2013 18:12:58', '%Y-%m-%d %H:%i:%s') --mysql

yyyymmdd 转换为 yyyy-mm-dd
select to_date('20201001','yyyy-mm-dd') from 
dual; --Oracle
SELECT DATE_FORMAT(STR_TO_DATE('20201001', '%Y%m%d'),'%Y-%m-%d') FROM DUAL; --mysql

4、As用法有区别

create table tableName As select * from table2 --mysql,Oracle通用

create table tableName(c1 varChar(64)) As select c2 from table2 --mysql可以，oracle报错（不能指定数据类型）

create table tableName(c1) As select c2 from table2 --mysql报错，Oracle可以

5、order by 语法

Oracle中：（asc与本例相反）
select * from table1 order by id desc; --默认null排在最前

例如：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xLp40PL0-1600662794295)(en-resource://database/967:1)]

mysql中：（asc与本例相反）
select * from table1 order by id desc; --默认null排在最后

例如：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CH1GOAS1-1600662794298)(en-resource://database/971:1)]

6、关键字

Oracle :

select * from v$reserved_words; --所有关键字

select * from v$reserved_words  where RESERVED = 'Y'; -- 不能作为列名的关键字

关键字做为列名查询是需要用引号'c1'
Mysql :
mysql使用关键字作为字段查询或者修改时需要使用反引号`c1`

7、group by语法

select c1,c2 from table1 group by c1; --mysql可以，oracle会报错（不是group by 表达式）

8、字符转DATE

select date_format(end_date,'%Y-%m-%d') 
end_date , date_format(begin_date,'%Y-%m-%d') 
begin_date from ad.ca_inv_data -- mysql

select to_char(end_date,'yyyymmdd') end_date , 
to_char(begin_date,'yyyymmdd') begin_date from 
ad.ca_inv_data --Oracle

9、Oracle和mysql日期增加

Oracle

增减一小时：

sysdate+1/24
sysdate-1/24

增减一天：

sysdate+1
sysdate-1

增减一月：

add_months(sysdate, 1)
add_months(sysdate, -1)

增减一季度：

add_months(sysdate, 3)
add_months(sysdate, -3)

增减一年：

add_months(sysdate, 12)
add_months(sysdate, -12)

Mysql

增减一小时：

date_sub(sysdate(), interval -1 hour)
date_sub(sysdate(), interval 1 hour)

增减一天：

date_sub(sysdate(), interval -1 day)
date_sub(sysdate(), interval 1 day)

增减一月：

date_sub(sysdate(), interval -1 month)
date_sub(sysdate(), interval 1 month)

增减一季度：

date_sub(sysdate(), interval -3 month)
date_sub(sysdate(), interval 3 month)

增减一年：

date_sub(sysdate(), interval -1 year)
date_sub(sysdate(), interval 1 year)

10、Oracle中的minus语法

   select *
     from (select a.item_id,
                  a.name,
                  a.service_spec_id,
                  a.item_type,
                  a.sub_type,
                  a.priority,
                  a.description
             from pd.pm_price_event a
           minus
           select b.item_id,
                  b.name,
                  b.service_spec_id,
                  b.item_type,
                  b.sub_type,
                  b.priority,
                  b.description
             from jd.pm_price_event_crm b) t --最终的结果只取第一个sql中的值，相当于找第一个sql结果相对第二个sql结果的不同。

例子：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PitGSZlw-1600662794300)(en-resource://database/977:1)]

11、Oralce 序列sequences

 select AD.CA_SYS_BASE_DATA_LOAD_SEQ.nextval 
from dual ;

mysql没有sequences

12、Oraclez中Decode函数

select decode('a','b','c','d','e','f') from dual; --oracle

该语句相当于java中的

         // java
         if ("a".equals("b")) {
         
                System.out.println("c");
         } else if ("a".equals("d")) {
         
                System.out.println("e");
         } else {
         
                System.out.println("f");
         }

该语句相当于mqsql中的

13、minus语法Oracle和mysql的写法

oracle:

select * from test_a minus select * from test_b; --oracle

mysql:

select a.name, a.age, a.school, a.home, a.ext
  from (select * from test_a) a
  left join (select * from test_b) b
    on b.name = a.name
   and b.age = a.age
   and a.school = b.school
 where b.name is null;

实战：
oracle:

select a.accumulate_item,
                  a.item_id,
                  a.policy_id,
                  a.cal_type,
                  a.ext_bill_item,
                  a.priority
             from PD.PM_ACCUMULATE_ITEM_REL a 
           minus
           select b.accumulate_item,
                  b.item_id,
                  b.policy_id,
                  b.cal_type,
                  b.ext_bill_item,
                  b.priority
             from JD.PM_ACCUMULATE_ITEM_REL_CRM 
b

转换为mysql:(注意null不可以比较)

select a.accumulate_item,
                  a.item_id,
                  a.policy_id,
                  a.cal_type,
                  a.ext_bill_item,
                  a.priority
   from PD.PM_ACCUMULATE_ITEM_REL a
   left join JD.PM_ACCUMULATE_ITEM_REL_CRM b
     on a.accumulate_item = b.accumulate_item 
    and a.item_id = b.item_id
    and (a.policy_id = b.policy_id or 
(a.policy_id is null and b.policy_id is null))
    and a.cal_type = b.cal_type
    and (a.ext_bill_item = b.ext_bill_item or 
(a.ext_bill_item is null and b.ext_bill_item is 
null))
    and a.priority = b.priority
  where 
    b.accumulate_item is null
    and b.item_id is null
    and b.policy_id is null
    and b.ext_bill_item is null
    and b.priority is null
    and b.cal_type is null;

14、add_months的转换

ADDDATE(t2.valid_date, INTERVAL t1.duration MONTH) < t2.expire_date --mysql

add_months(t2.valid_date, t1.duration) < t2.expire_date --oracle

例如：

SELECT ADDDATE(NOW(),INTERVAL 10 MONTH) FROM DUAL; --2021-06-20 17:13:09  --mysql

select add_months(sysdate, 10) from dual; --2021/6/20 17:06:27 --Oracle

15、Oracle高性能，多线程并行执行

oracle:

create table ad.ca_account_0_bak as select * 
from ad.ca_account_0; --耗时 18ms

create table ad.ca_account_0_bak_bak parallel 10 
nologging as select * from ad.ca_account_0; --耗时 3ms

mysql: 目前还没有遇到

16、Oracle和mysql查看数据库中所有表

select * from user_tables; --oracle
select * from information_schema.TABLES --mysql
```### 1、获取当前时间
```sql
select now() from dual; --mysql
select sysdate() from dual; --mysql:

--两个都是可查询出当前时间的一般都是用NOW()，因为SYSDATE获取当时实时的时间，这有可能导致主库和从库是执行的返回值是不一样的，导致主从数据不一致。
select sysdate form dual; --Oracle

mysql:

16、Oracle和mysql查看数据库中所有表

select * from user_tables; --oracle
select * from information_schema.TABLES --mysql

17、Oracle 和Mysq修改字段长度、类型

ALTER TABLE AD.CA_CYCLE_RUN_25 MODIFY COLUMN acct_id BIGINT; --mysql
alter table AD.CA_CYCLE_RUN_25 modify acct_id 
number(17);  --oracle

18、查看建表语句

SHOW CREATE TABLE AD.CA_CYCLE_RUN_25 ; --mysql 
select 
DBMS_METADATA.get_ddl('TABLE','CA_CYCLE_RUN_25') from dual; --oracle(不需要加用户名)

例如：
在这里插入图片描述

19、Oracel中rownum替代方案

SELECT DISTINCT 
    ext1 
  FROM
    cd.cm_customer_0 b 
  WHERE  b.expire_date > sysdate
    AND b.valid_date <= sysdate
    AND rownum = 1; --oracle
    
    SELECT DISTINCT 
  ext1 
FROM
  cd.cm_customer_0 b 
WHERE b.expire_date > NOW() 
  AND b.valid_date <= NOW() 
LIMIT 1 ; --mysql

20、字段大小写问题

MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写.

oracle不区分大小写。

21、时间增加指定秒

select to_date('1970-01-01 08:00:00','yyyy-mm-dd 
hh24:mi:ss')+ 1600240752 / 24 / 3600 from dual; --Oracle

SELECT STR_TO_DATE(
    '1970-01-01 08:00:00',
    '%Y-%m-%d %H:%i:%s'
  ) + INTERVAL 1600240752 SECOND FROM DUAL; --mysql

22、substr截取问题

-- Oracle起始位置从0和1开始结果都是一样的。
SELECT SUBSTR('hello',0,3) FROM DUAL; --oracle

-- mysql不可以从0开始，从0开始不报错，但是返回为空
SELECT SUBSTR('hello',1,3) FROM DUAL; --mysql

23、to_number


SELECT CAST('11' AS UNSIGNED INT) FROM DUAL; /*整型*/ --mysql
select to_number('11') from dual; --oracle

24、rownum

select rownum from tablea; -- oracle;
SELECT @rownum:=@rownum+1 rownum FROM (SELECT @rownum:=0) r,netdisc_sysconf;--mysql其中netdisc_sysconf是表

25、查看版本号：


select version(); --mysql

26、oracle中的‘’和 null 与mysql中的‘’ 和 null 比对

** mysql区分null 和 ‘’**
`[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gSu18KhB-1611840370802)(en-resource://database/1239:1)]

oracle 不区分null和‘’
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FWDhXGd1-1611840370804)(en-resource://database/1241:1)]

27、各种编码格式

mysql:


1、查看所有字段的编码格式：mysql>show full columns from tableName;
2、查看表的编码格式：mysql>show create table tableName;
3、修改数据库的编码格式：mysql>alter database databasename character set utf8
4、修改表的编码格式mysql>alter table tablename character set utf8
5、修改字段的编码格式mysql>alter table tablename modify fieldname varchar(50) character set utf8

28、分页查询：

  select * from (select tb__.*, rownum rid__ 
from 
         (select acct_id from  
aid.sp_vgop_batch_user order by acct_id asc) 
tb__ where rownum <= 100) where 
rid__ >= 1 -- oracel(从1开始到100)，100为跨度，1为起始值

SELECT acct_id FROM ad.ca_bill_0_202011 LIMIT 0,100; --mysql

29、mysql解析结果集中的json

SELECT json_extract(input_soap, '$**.op_id') AS op_id FROM jd.ims_json_soap_1_0; --myslq

30、mysql 截取json结果集中的指定字符并排序

SELECT SUBSTRING(op_id,2,9) AS op_id  FROM (SELECT json_extract(input_soap, '$**.op_id') AS op_id FROM jd.ims_json_soap_1_0) a  ORDER BY op_id --mysql

ะัี潪ิื

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
项目去O过程遇到的一些mysql和oracle语法区别

1、获取当前时间select now() from dual; --mysqlselect sysdate() from dual; --mysql:--两个都是可查询出当前时间的一般都是用NOW()，因为SYSDATE获取当时实时的时间，这有可能导致主库和从库是执行的返回值是不一样的，导致主从数据不一致。select sysdate form dual; --Oracle2、字符串拼接select concat('a','b','c') from dual; --mysqlSELECT
复制链接

扫一扫