A detailed comparison of the syntax differences between MySQL and Oracle

Some simple command comparisons for 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 function
Added one months:
Sql> Select To_char (add_months (to_date (' 20000101 ', ' YYYYMMDD '), 1), ' YYYY-MM-DD ') from dual;
Results: 2000-02-01
Sql> Select To_char (add_months (to_date (' 20000101 ', ' YYYYMMDD '), 5), ' YYYY-MM-DD ') from dual;
Results: 2000-06-01
Mysql> Select Date_add (' 2000-01-01 ', Interval 1 month);
Results: 2000-02-01
Mysql> Select Date_add (' 2000-01-01 ', Interval 5 month);
Results: 2000-06-01
To intercept a string:
Sql> Select substr (' ABCDEFG ', 1,5) from dual;
Sql> Select SUBSTRB (' ABCDEFG ', 1,5) from dual;
Results:abcdemysql> Select substring (' ABCDEFG ', 2, 3);
Result: BCD
Mysql> Select mid (' ABCDEFG ', 2, 3);
Result: BCD
mysql> Select substring (' ABCDEFG ', 2);
Results: BCDEFG
mysql> Select substring (' ABCDEFG ' from 2);
Results: BCDEFG
2) in MySQL after the from table if Yes (select ...) This, then there must be an alias behind it.
3) connection string in Oracle | | , SQL Server uses +,mysql in Concat (' A ', ' B ', ' C ')

4)
notation in SQL Server:

Copy the code code as follows:
DECLARE @id varchar (50);
Set @id = ' 4028e4962c3df257012c3df3b4850001 ';
SELECT * from Sims_sample_detect where id= @id;

Syntax in MySQL:

Copy the code code as follows:
Set @a = 189;
SELECT * from bc_article WHERE id = @a//No DECLARE

In the Orcale:

5) MySQL stored procedure:

Copy the code code 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 effect is 1=1, and MySQL is not case-sensitive.

6) MySQL cursor
MySQL does not have a dynamic cursor like Orcale, only the cursor is displayed, as in the following example:

Copy the code code as follows:
DELIMITER $$
DROP PROCEDURE IF EXISTS ' test '. ' Liyukun ' $$
CREATE definer= ' IDs ' @ ' 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;
This differs from Oracle, where Oracle's PL/SQL pointers have an implicit change
Volume%notfound,mysql is judged by an error handler declaration.
OPEN Cur1;
Cur1:loop
FETCH cur1 into v_haoma,v_yingyeting;
If done=1 then//If there is no data, 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 = ' + ' where haoma=v_haoma;
End If;
END IF;
END LOOP Cur1;
CLOSE Cur1;
end$$
DELIMITER;

Perform:

Copy the code code as follows:
Call Liyukun (@a);
Select @a;

7) The GROUP BY statement for MySQL can select fields that are not grouped, such as
Select Id,name,age from A GROUP by
But in Orcale and SQL Server, there will be an error. The row where the id,name is taken is the first row of data in each grouping.
8) Orcale uses decode () to convert data, mysql,sqlserver with case:
Case T.detect_result "2402" then T.sampleid end (must have end)
9) MySQL: Two select out of data subtraction:
(COUNT (Distinct (T.sampleid))-
CONVERT ((COUNT (DISTINCT (case t.detect_result when ' 2402 ' then T.sampleid end)), signed)) as negative
From ' View_sims_for_report ' t
Ten) Convert,cast usage
MySQL convert varchar to int
Convert (field name, signed)
Character Set conversion: convert (XXX USING gb2312)
Type conversions are like SQL Server, where type parameters are a little different: CAST (xxx as type), convert (XXX, type)
Types of available
Binary, with binary prefix effect: binary
Character type, with parameters: CHAR ()
Date: Date
Time:
DateTime Date/Time type
Floating point number: DECIMAL
Integer: Signed
unsigned integer: UNSIGNED
11) If it is not garbled from the MySQL database, and the Java list is garbled, then it is possible that there is a field in the SQL statement is not a varchar data type, then convert convert (field name, type) to convert, Orcale uses the ToChar function
Orcale of the large section of the CLOB, the image with the Blob,clob field in the Hibernate mapping file with a string can be
Mysql,orcale,sqlserver Statement Execution Order
Start->from clause->where clause->group BY clause->having clause->order BY clause->select clause-&GT;LIMIT clause-end result
After each clause executes, it produces an intermediate result that is used by the next clause, and skips if there are no clauses.
) Lpad function
1 There is a function lpad (String a,int length,string addstring) in the Oracle database.
2 function: Add addstring to the left of a, length is the return value.
3 examples

Copy the code code as follows:
A:sql> Select Lpad (' Test ', 8,0) from dual;
Lpad (' TEST ', 8,0)
----------------
0000test
B:select lpad (' Test ', 8) from dual;
Lpad (' TEST ', 8)
--------------
Test Note: Without writing the last argument, the function will default to the left of the return value with a space.
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

Orcale no top in the
SELECT * FROM (SELECT * from A ORDER BY id DESC) where rownum=1
Note: You cannot directly write select * from A where rownum=1 the ORDER by id DESC because the order in which the statements are executed is the first where and then order by, and if this is not the first one to sort by the ID.
You cannot write rownum=2 or rownum>1 this way, because Orcale must contain the first one by default.
If you want to take a second article, you can write:

Copy the code code 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

Orcale,mysql while loop comparison
Orcale:

Copy the code code as follows:
While num<10
Loop
str: = To_char (num);
num: = num+1;
End Loop;

You can also:

Copy the code code as follows:
For num in 1..10-the flaw is that the value cannot be spaced
Loop
str: = To_char (num);
End Loop;

Mysql:

Copy the code code as follows:
While num<10
Do
str: = To_char (num);
num: = num+1;
End while;

Orcale Generate unique sequence is select Sys.guid () from dual, MySQL is select UUID () from dual

MySQL and Orcale ID self-increment
MySQL because it is implemented in the database ID self-increment, so if you want to return to insert a sequence of this ID, can only use the following methods:

Copy the code code 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 (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 publish signature", E);
}
}

Since the ID of the Orcale is obtained from the select Seq_blog_id.nextval from dual before inserting the data, the direct return is possible. PS:SEQ_BLOG_ID is the sequence that is set in the database.

Http://www.cnblogs.com/ZhuRenWang/p/4681179.html

Detailed comparison of syntax differences between MySQL and Oracle (GO)