Different collation of Oracle & MySQL

1. Group function Usage Rules

group functions in MySQL can be used at will in a SELECT statement, but in Oracle if there is a group function in the query, the other column names must be processed by the group function, or the column in the GROUP BY clause is otherwise an error
Eg:select name,count from user; This is not a problem in MySQL. There's a problem in Oracle.
2. Automatic growth of data type processing

MySQL has an automatically growing data type, and the data value is automatically obtained when you insert a record without manipulating this field. Oracle does not have an automatically growing data type, an automatic growth sequence number is required, and the next value of the serial number is assigned to the field when the record is inserted.
The name of the CREATE sequence serial number (preferably the table name + serial number tag) INCREMENT by 1 START with 1 MAXVALUE 99999 CYCLE nocache;
The maximum value is determined by the length of the field, and if the defined automatically-growing serial number (6), the maximum is 999999
Insert statement inserts this field value as: The name of the serial number. Nextval
3. Single quotation mark processing
MySQL can be used in double quotes wrap strings, Oracle can only use single quotes wrap strings. A single quotation mark must be replaced before inserting and modifying a string: Replace all occurrences of one single quote with two single quotes.
4. The processing of the SQL statement page
MySQL processing pages of the SQL statement is relatively simple, with limit start position, record number; PHP can also use seek to locate the result set. Oracle handles page-flipping SQL statements is cumbersome. Each result set has only one rownum field that indicates its position, and can only be used rownum<100, not rownum>80.
The following are two of the better Oracle page-Flipping SQL statements (field names with IDs as unique keywords):
Statement one:
SELECT ID, [Field_name,...] From table_name where ID in (select ID from (select RowNum as Numrow, ID from table_name WHERE Condition 1 order by condition 2) WHERE NU Mrow > Numrow <) Order BY condition 3;
Statement two:
SELECT * FROM (select RowNum as Numrow, c.* from (SELECT [Field_name,...] From table_name WHERE Condition 1 order by Condition 2 c) WHERE Numrow > Numrow < (m) Order by condition 3;
5. Processing of long strings
Processing of long strings Oracle also has its own special place. The maximum operable string length for insert and update is less than 4,000 single-byte, and if you want to insert a longer string, consider using the CLOB type for the field to borrow the Dbms_lob package from Oracle. Be sure to do non-null and length judgments before inserting a change record, and you should warn about field values that are not empty and values that exceed the length field, and return to the last action.
6. Processing of date fields
The MySQL date field is divided into date and time two, and the Oracle Date field is only date, containing the minutes and seconds of the year, the system time of the current database is sysdate, accurate to the second, or the string is converted to a date-type function to_date (' 2001-08-01 '), ' YYYY-MM-DD ') year-month-day 24 hours: minutes: SEC format yyyy-mm-dd HH24:MI:SS to_date () There are many different date formats, see Oracle DOC. Date field converted to String function To_char (' 2001-08-01 ', ' yyyy-mm-dd HH24:MI:SS ')
The mathematical formula for the date field is very different. MySQL finds 7 days away from current time with Date_field_name > Subdate (Now (), INTERVAL 7 day) Oracle finds 7 days with Date_field_name >SYSDATE–7;
Several functions that insert the current time in MySQL are: the Now () function returns the current date time with ' Yyyy-mm-dd HH:MM:SS ', and can be saved directly to the DateTime field. Curdate () returns today's date in ' YYYY-MM-DD ' format and can be stored directly in the Date field. Curtime () returns the current time in ' HH:MM:SS ' format, which can be saved directly to the Duration field. Example: INSERT INTO TableName (fieldname) VALUES (now ())
And the current time in Oracle is Sysdate
7. Processing of NULL characters
MySQL's Non-empty field also has empty content, Oracle defined a NON-EMPTY field does not allow empty content. The Oracle table structure is defined according to the NOT null of MySQL, and errors are generated when data is directed. So when you guide the data, you have to judge the null character, if it is null or empty, you need to change it to a blank string.
8. Fuzzy Comparison of strings
MySQL in the field name like% ' String% ', Oracle can also use the field name like% ' String% ' but this method can not use the index, the speed is not fast, with the string comparison function InStr (field name, ' string ') >0 will get more accurate search results.
9. Procedures and functions, the operation of the database work, please note that the result set and the release of the pointer.

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:

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

In MySQL, the wording:

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

The wording in Orcale:

5 MySQL Stored procedure:

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:

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;
//Here and Oracle are different, Oracle's pl/sql pointer has a recessive variable
volume%notfound,mysql is a error handler declaration to judge the
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 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:

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 www.111cn.net
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-&GT;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

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:

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 a flaw is the inability to interval values
Loop
str: = To_char (num);
End Loop;

Mysql:

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:

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.

Summary Work

Same point
1. relational database management system
2. The current most popular database

Different points

Advantages
A.
The advantage of MySQL is that open source is free, easy to use, stable, reliable, and easy to extend is very good.
In fact, the MySQL-backed Google database, Facebook database, mobile flying a database is big enough, MySQL or complete to support a number of large-scale applications, especially in the internet industry, MySQL's replication capabilities make it easy to achieve horizontal expansion after read-write separation
B.
Oracle's advantage is that Oracle has a strong technical backing, features rich and perfect, safe, excellent performance, user-friendly

Disadvantages
C.
The disadvantage of MySQL is that the relative function is less, the object is the general small Web site or system, the large database is rarely used MySQL;
D.
Oracle's disadvantage is that the hardware requirements are very high, the technical requirements of managers. That's basically it.

Other
E.
MySQL is in C + +;
Oracle system core should be written in C, starting from Oracle9i, Oracle formally adopted Java as the main program development language, many Oracle client applications will be written in Java.