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:

DECLARE @id varchar (50); Set @id = ' 4028e4962c3df257012c3df3b4850001 '; SELECT * from Sims_sample_detect where id= @id;

Syntax in MySQL:

Set @a = 189;
SELECT * from bc_article WHERE id = @a//No DECLARE

In the Orcale:

5) MySQL stored procedure:

DELIMITER $$ DROP PROCEDURE IF EXISTS ' SIMS '. ' Transaction_delsampleinfo ' $$ CREATE definer= ' root ' @ '% ' PROCEDURE ' Transaction_delsampleinfo ' (in sampleinfoid varchar ()) 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:

DELIMITER $$ DROP PROCEDURE IF EXISTS ' test '. ' Liyukun ' $$ CREATE definer= ' IDs ' @ ' localhost ' PROCEDURE ' Liyukun ' (out Zint) BEGIN declare count1int; DECLARE done INT DEFAULT0; 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 don't FOUND SET done=1; //This differs from Oracle, where Oracle's PL/SQL pointers have an implicit changeAmountNotfound,mysql is an OPEN cur1 that is judged by an error handler declaration; Cur1:loop FETCH Cur1 into v_haoma,v_yingyeting; IF Done=1 Then//If there is no data, then leaveLEAVE Cur1; ELSE Select COUNT (*) into Count1 from year2012 where haoma=V_haoma;if(count1=0Then INSERT into year2012 (Haoma, yingyeting) VALUES (v_haoma,v_yingyeting); ElseSet Z= Z+1; update year2012 set Eryue= ' + ' where haoma=v_haoma; Endif; END IF; END LOOP Cur1; CLOSE Cur1; end$$ DELIMITER;

Execution:

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->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

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:

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:

 while num<10 loop str:= to_char (num); num:= num+1; end loop;

You can also:

 for Num in 1..10-- Such defects are unable to interval the value of loop str:= to_char (num); end loop;

Mysql:

 while  Do str:= to_char (num); num:= num+1while;

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:

1  2 public int insertsign (final spacesign sign) throws Exception {3 try{4 keyholder keyholder = new generatedKeyHolder ();5 final String sql = "INSERT into Space_sign (Userid,username,nickname,contenttext,contenthtml,ispublic, Commentcount,userip,status,inserttime) "+6 "VALUES (?,?,?,?,?,?,?,?,?,?)";7 template.update (New PreparedStatementCreator () {8 Public PreparedStatement createpreparedstatement (Connection Connection) throws SQLException {9 PreparedStatement PS = connection.preparestatement (sql,statement.return_generated_keys);Ten ps.setint (1, Sign.getuserid ()); One ps.setstring (2, Sign.getusername ()); A ps.setstring (3, Sign.getnickname ()); - ps.setstring (4, Sign.getcontenttext ()); - ps.setstring (5, sign.getcontenthtml ()); the 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; + }  A }, Keyholder);  at 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); in }  -}

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.

A detailed comparison of the syntax differences between MySQL and Oracle