Detailed comparison of syntax differences between MySQL and Oracle

Comparison of some simple commands of Oracle and mysql
1) SQL> select to_char (sysdate, 'yyyy-mm-dd') from dual;
SQL> select to_char (sysdate, 'hh24-mi-ss') from dual;
Mysql> select date_format (now (), '% Y-% m-% D ');
Mysql> select time_format (now (), '% H-% I-% s ');
Date Functions
Add a month:
SQL> select to_char (add_months (to_date ('201312', 'yyyymmdd'), 1), 'yyyy-mm-dd') from dual;
Result: 2000-02-01
SQL> select to_char (add_months (to_date ('201312', 'yyyymmdd'), 5), 'yyyy-mm-dd') from dual;
Result: 2000-06-01
Mysql> select date_add ('2017-01-01 ', interval 1 month );
Result: 2000-02-01
Mysql> select date_add ('2017-01-01 ', interval 5 month );
Result: 2000-06-01
Truncation string:
SQL> select substr ('abcdefg', 1, 5) from dual;
SQL> select substrb ('abcdefg', 1, 5) from dual;
Result: abcdemysql> select substring ('abcdefg', 2, 3 );
Result: bcd
Mysql> select mid ('abcdefg', 2, 3 );
Result: bcd
Mysql> select substring ('abcdefg', 2 );
Result: bcdefg
Mysql> select substring ('abcdefg' from 2 );
Result: bcdefg
2) in MySQL, if the from table is (select...), an alias must be followed.
3) The connection string is used in Oracle. | + is used in SqlServer and concat ('A', 'B', 'C') is used in MySQL ')

4)
In SqlServer:
Copy codeThe Code is as follows:
Declare @ id varchar (50 );
Set @ id = '4028e4962c3df257012c3df3b4850001 ';
Select * from sims_sample_detect where ID = @ id;

In MySQL:
Copy codeThe Code is as follows:
Set @ a = 189;
Select * from bc_article where id = @ a // do not use declare

Statement in Orcale:

5) MySQL stored procedure:
Copy codeThe Code is as follows:
DELIMITER $
Drop procedure if exists 'sims'. 'transaction _ delsampleinfo' $
Create definer = 'root' @ '%' PROCEDURE 'transaction _ delSampleInfo '(in sampleInfoId varchar (50 ))
BEGIN
Start transaction;
Update sims_sample_info set del = '1' where ID = sampleInfoId;
Update sims_sample_detect set del = '1' where SAMPLE_ID_PARENT = sampleInfoId;
Update sims_sample_detect_info set del = '1' where DETECT_ID in (
Select ID from sims_sample_detect where SAMPLE_ID_PARENT = sampleInfoId
);
Commit;
END $
DELIMITER;

The variable name cannot be the same as the column name; otherwise, the result is 1 = 1 and MySQL is case insensitive.

6) mysql cursor
Mysql does not have a dynamic cursor like orcale, but only a display cursor. The example is as follows:
Copy codeThe Code is as follows:
DELIMITER $
Drop procedure if exists 'test'. 'liyukun '$
Create definer = 'kids' @ 'localhost' PROCEDURE 'liyukun '(out z int)
BEGIN
Declare count1 int;
DECLARE done int default 0;
Declare v_haoma varchar (50 );
Declare v_yingyeting varchar (100 );
DECLARE cur1 cursor for select haoma, yingyeting from eryue where id <2;
Declare continue handler for not found set done = 1;
// Here is different from oracle, and Oracle PL/SQL pointer has a implicit change
% Notfound. Mysql uses an Error handler statement to determine
OPEN cur1;
Cur1: LOOP
FETCH cur1 INTO v_haoma, v_yingyeting;
IF done = 1 THEN // IF no data exists, leave
LEAVE cur1;
ELSE
Select count (*) into count1 from year2012 where haoma = v_haoma;
If (count1 = 0) then
Insert into year2012 (haoma, yingyeting)
Values (v_haoma, v_yingyeting );
Else
Set z = z + 1;
Update year2012 set eryue = '000000' where haoma = v_haoma;
End if;
End if;
End loop cur1;
CLOSE cur1;
END $
DELIMITER;

Run:
Copy codeThe Code is as follows:
Call liyukun (@ );
Select @;

7) mysql's group by statement can select fields not grouped, such
Select id, name, age from A group by age
However, errors are reported in orcale and sqlserver. The retrieved id. The row where name is located is the first row of data in each group.
8) orcale uses decode () to convert data. mysql and sqlserver use case when:
Case t. DETECT_RESULT when '000000' then t. SAMPLEID end (end is required)
9) mysql: Subtract data from two select statements:
(COUNT (distinct (t. SAMPLEID ))-
CONVERT (COUNT (distinct (case t. DETECT_RESULT when '000000' then t. SAMPLEID end), SIGNED) AS NEGATIVE
FROM 'view _ sims_for_report't
10) convert and cast usage
Mysql converts varchar to int
Convert (field name, SIGNED)
Character Set conversion: CONVERT (xxx USING gb2312)
Type conversion is similar to SQL Server, that is, the type parameters are a little different: CAST (xxx AS type), CONVERT (xxx, type)
Available types
Binary, with a BINARY Prefix: binary
Character type, with parameters: CHAR ()
DATE: DATE
TIME: TIME
DATETIME type
Floating Point: DECIMAL
Integer: SIGNED
UNSIGNED integer
11) If no garbled characters are obtained from the mysql database and the Java List contains garbled characters, it is possible that fields in the SQL statement are not of the varchar data type, in this case, you need to convert (field name, type) and Orcale to use the ToChar function.
12) Orcale uses clob for large fields, blob for images, and String for clob fields in Hibernate ing files.
13) execution sequence of mysql, orcale, and sqlserver statements
Start> FROM clause> WHERE clause> group by clause> HAVING clause> order by clause> SELECT clause> LIMIT clause> final result
After each clause is executed, an intermediate result is generated for the subsequent clause. If no clause exists, it is skipped.
14) LPAD Functions
1. There is a function LPAD (String a, int length, String addString) in the oracle database ).
2. Add addString to the left of a. length indicates the length of the returned value.
3. Example
Copy codeThe Code is as follows:
A: SQL> select lpad ('test', 8, 0) from dual;
LPAD ('test', 8, 0)
----------------
0000 test
B: select lpad ('test', 8) from dual;
LPAD ('test', 8)
--------------
Test Note: if you do not specify the last parameter, the function will add a space on the left of the returned value by default.
C: SQL> select lpad ('test', 2, 0) from dual;
LPAD ('test', 2, 0)
----------------
Te
D: SQL> select lpad ('test', 3) from dual;
LPAD ('test', 3)
--------------
Tes

15) There is no TOP in Orcale.
Select * from (select * from A order by id desc) where rownum = 1
Note: you cannot directly write select * from A where rownum = 1 order by id desc because the order of statement execution is first where and then order, in this case, the first entry cannot be obtained by sorting by id.
You cannot write rownum = 2 or rownum> 1, because Orcale must include the first entry by default.
If you have to take the second entry, you can write it as follows:
Copy codeThe Code is as follows:
Select * from (select id, rownum as row_num from lws_q_bl_result r where r. sample_id = 'b10226072 ') where row_num = 2

16) Orcale, MySql while LOOP comparison
Orcale:
Copy codeThe Code is as follows:
While num <10
Loop
Str: = to_char (num );
Num: = num + 1;
End loop;

You can also:
Copy codeThe Code is as follows:
For num in 1... 10 -- such a defect cannot be separated.
Loop
Str: = to_char (num );
End loop;

Mysql:
Copy codeThe Code is as follows:
While num <10
Do
Str: = to_char (num );
Num: = num + 1;
End while;

17) the unique sequence generated by orcale is select sys. guid () from dual, mysql is select uuid () from dual

18) ID auto-increment of MySql and Orcale
Because MySql implements ID auto-increment in the database, to insert the ID of a sequence, you can only use the following method:
Copy codeThe Code is as follows:
Public int insertSign (final SpaceSign sign) throws Exception {
Try {
KeyHolder keyHolder = new GeneratedKeyHolder ();
Final String SQL = "insert into space_sign (userId, userName, nickName, contentText, contentHtml, isPublic, commentCount, userIp, status, insertTime)" +
"Values (?,?,?,?,?,?,?,?,?,?) ";
Template. update (new PreparedStatementCreator (){
Public PreparedStatement createPreparedStatement (Connection connection) throws SQLException {
PreparedStatement ps = connection. prepareStatement (SQL, Statement. RETURN_GENERATED_KEYS );
Ps. setInt (1, sign. getUserId ());
Ps. setString (2, sign. getUserName ());
Ps. setString (3, sign. getNickName ());
Ps. setString (4, sign. getContentText ());
Ps. setString (5, sign. getContentHtml ());
Ps. setInt (6, sign. getIsPublic ());
Ps. setInt (7, sign. getCommnetCount ());
Ps. setString (8, sign. getUserIp ());
Ps. setInt (9, sign. getStatus ());
Ps. setTimestamp (10, new java. SQL. Timestamp (sign. getInsertTime (). getTime ()));
Return ps;
}
}, KeyHolder );
Long generatedId = keyHolder. getKey (). longValue ();
Return generatedId. intValue ();
} Catch (Exception e ){
// TODO Auto-generated catch block
E. printStackTrace ();
Throw new SQLException ("failed to sign", e );
}
}

Because the Orcale ID is obtained through select SEQ_BLOG_ID.nextval from dual before the data is inserted, you can directly return the result. Ps: SEQ_BLOG_ID is the sequence set in the database.