Comparison of some simple commands 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
Increased by 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
Intercept 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);
Result: BCDEFG
mysql> Select substring (' ABCDEFG ' from 2);
Result: BCDEFG
2 in MySQL from the following table if it is (select ...) This, then there must be an alias behind
3 connection string in Oracle | | , SQL Server uses concat in +,mysql (' A ', ' B ', ' C ')
4)
How to do this in SQL Server:
Copy Code code as follows:
DECLARE @id varchar (50);
Set @id = ' 4028e4962c3df257012c3df3b4850001 ';
SELECT * from Sims_sample_detect where id= @id;
In MySQL, the wording:
Copy Code code as follows:
Set @a = 189;
SELECT * from bc_article WHERE id = @a//No DECLARE
The wording in Orcale:
5 MySQL Stored procedure:
Copy 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;
Variable names cannot be the same as column names, otherwise the effect is 1=1, and MySQL is case-insensitive.
6) MySQL Cursors
MySQL does not have a dynamic cursor like Orcale, only display cursors, examples are as follows:
Copy 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 in that Oracle's pl/sql pointer has a recessive change
The amount of%notfound,mysql is judged by an error handler statement.
OPEN Cur1;
Cur1:loop
FETCH cur1 into v_haoma,v_yingyeting;
If done=1 THEN//If there is no data, then leave
LEAVE Cur1;
ELSE
Select COUNT (*) into the 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 = ' n ' where haoma=v_haoma;
End If;
End IF;
End LOOP Cur1;
Close Cur1;
end$$
DELIMITER;
Perform:
Copy Code code as follows:
Call Liyukun (@a);
Select @a;
7 the MySQL GROUP BY statement can select fields that are not grouped, such as
Select Id,name,age from A GROUP by age
But there are errors in Orcale and SQL Server. This removed id,name is the first row of data in each group.
8) Orcale use decode () to convert the data, mysql,sqlserver with case when:
Case T.detect_result the ' 2402 ' then T.sampleid end (must have end)
9 MySQL: Two select data subtract:
(COUNT (Distinct (T.sampleid))-
CONVERT ((COUNT (DISTINCT (case t.detect_result when ' 2402 ' then T.sampleid)), signed)) as NEGATIVE
From ' View_sims_for_report ' t
) Convert,cast Usage
MySQL converts varchar to int
Convert (field name, signed)
Character Set conversion: convert (XXX USING gb2312)
Type conversions, like SQL Server, are a bit different for type parameters: CAST (xxx as type), convert (XXX, type)
Types that are available
Binary, with the effect of BINARY prefix: BINARY
Character type, with parameters: CHAR ()
Dates: Date
Time: Times
Date-time: DATETIME
Floating point numbers: DECIMAL
Integer: Signed
unsigned integer: UNSIGNED
11 if the MySQL database from the time there is no garbled, and in the Java list is garbled words, then may be the SQL statements in the field is not varchar data type, then need to convert convert (field name, type) conversion, Orcale uses the ToChar function
The large segment of Orcale with CLOB, the image with Blob,clob field in Hibernate mapping file with string can
Mysql,orcale,sqlserver Statement Execution Order
Start->from clause->where clause->group BY clause->having clause->order BY clause->select clause->LIMIT clause-> final result
Each clause is executed to produce an intermediate result for use in the next clause, and if there is no one, skip.
) Lpad function
1 There is a function lpad (String a,int length,string addstring) in Oracle's database.
2 effect: Add AddString to the left of a, length is the value of the return.
3 examples
Copy 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: If you do not write the last argument, the function defaults to a space on the left of the return value.
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
No top in Orcale is passed
SELECT * FROM (SELECT * from an ORDER BY id DESC) where rownum=1
Note: You cannot write a select * from A where rownum=1 the ORDER by id DESC because the execution of the statement is first where and then ordered by, if this is not the first to be sorted by ID.
You cannot write rownum=2 or rownum>1 this, because Orcale must contain the first rule by default.
If you want to take the second article, you can write:
Copy 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 Code code as follows:
While num<10
Loop
str: = To_char (num);
num: = num+1;
End Loop;
You can also:
Copy Code code as follows:
For num in 1..10-such a flaw is the inability to interval values
Loop
str: = To_char (num);
End Loop;
Mysql:
Copy Code code as follows:
While num<10
Todo
str: = To_char (num);
num: = num+1;
End while;
Orcale Generation unique sequence is select Sys.guid () from dual, MySQL is select UUID () from dual
MySQL and Orcale IDs self-increasing
MySQL because it is in the database to implement ID, so if you want to return to insert a sequence of this ID, you can only use the following methods:
Copy 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 ("Publish signature failed", e);
}
}
Since the ID of the Orcale is obtained from the select Seq_blog_id.nextval from dual before inserting the data, it can be returned directly. PS:SEQ_BLOG_ID is the sequence set in the database.
