1. Handle single and double quotation marks

MYSQL can use double quotes to enclose strings, ORACLE can only use single quotes to enclose strings. Be careful when inserting and querying.

2. Keyword processing

MYSQL uses a keyword as an alias by enclosing the keyword with a symbol. Oracle does not use a keyword as an alias by enclosing the keyword with double quotation marks

This error is usually reported, which may be the reason for the keyword.

3. Processing table name and field name length

MYSQL has a length limit of 64, but Oracle has a length limit of 30, including aliases that cannot exceed 30

4. Date field processing

MYSQL uses the current system TIME of the database as now() or SYSDATE(), which is accurate to the second. Oracle uses the system time of the current database as SYSDATE, accurate to the second.

To_date () and to_char() date and string conversions:

To_char (sysdate,'yyyy-mm-dd hh24:mi:ss'Convert string to date format: to_date('2005-12-25, 13:25:59'.'yyyy-mm-dd,hh24:mi:ss')
Copy the code

Mysql DATE_FORMAT() DATE_FORMAT();

Convert string to date format: DATE_FORMAT('the 2017-09-20 08:30:45'.'%Y-%m-%d %H:%i:%S'Format DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')
Copy the code

The mathematical formulas for date fields are quite different. SUBDATE (NOW(), INTERVAL 7 DAY) DATE_FIELD_NAME > sysdate-7;

To_date (); to_char(); to_date(); to_date(); to_char()

5. Fuzzy query comparison of strings

MYSQL CONCAT(‘%’, string,’ %’) MYSQL CONCAT(‘%’, string,’ %’) MYSQL CONCAT(‘%’, string,’ %’) MYSQL CONCAT(‘%’, string,’ %’) MYSQL CONCAT(‘%’, string,’ %’) MYSQL CONCAT(‘%’, string,’ %’

MYSQL > alter table select * from MYSQL; MYSQL > alter table select * from MYSQL

7. String concatenation in Oracle using | |, MySQL using concat (‘ a ‘, ‘b’, ‘c’)

8.Orcale uses decode() to convert data, mysql uses case when

case.. The when usage:

case sex
when '1'  then 'male'
when '2'  then 'woman'
else 'other' end
Copy the code

Decode () usage:

Decode (condition, value 1, return value 1, value 2, return value 2... Value n, return value n, default)Copy the code

9. Processing of paging SQL statements

Mysql > page limit

select * from test limit3, 5; - the querytestWe start at 3, we have 5 numbersCopy the code

Oracle paginates using rownum, which starts at 1

select *
  from (select rownum rn, a.*
          from table_name a
         whereRownum <= x, x = startPage * pageSize)wherern > y; / / Start line, y =(startPage - 1) * pageSizeCopy the code

Mysql > select * from ‘Mysql’ where ‘Mysql’ = ‘1’;

select * from test limit 1
Copy the code

This should be possible in Oracle with rownum:

select * from test where rownum <=1
Copy the code

10. Oracle, and Mysql stored procedures difference blog.csdn.net/wb96a1007/a…