Shulou(Shulou.com)05/31 Report--

Database oracle and mysql in the grammatical difference is what, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Differences: 1, MySQL has no Number type, but oracle has; 2, Date type in MySQL only represents date, Date type in Oracle represents date time; 3, get string length, Oracle uses length (), MySQL uses char_length (), and so on.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Recently, you need to migrate the project to change the database from Oracle to MySQL. Because the syntax of the two is somewhat different, you need to change the functions / types that can be used in Oracle but not in MySQL to those that can be used in MySQL. Here are some of the syntax differences summarized:

I. data type

1. Number type

There is no Number type in MySQL, but there is int/decimal type. Number in Oracle corresponds to decimal in MySQL, and Number (5) corresponds to int (5).

There are many numeric types in MySQL, including tinyint, smallint, mediumint, bigint and so on.

2. Varchar2 (n) type

The replacement type corresponding to the Oracle Varchar2 (n) type in MySQL is the varchar (n) type.

3. Date type

The date-time types in MySQL are Date, Time, Datetime and so on. In MySQL, the Date type only represents the date (year-month-day), the Time type only represents the time (hour: minute: second), and the Datetime type represents the date time (year-month-day hour: minute: second). The Date type in Oracle is the same as the Datetime type in MySQL.

Second, function

1. Length (str) function

Length (str) in Oracle is a function that gets the length of a string, and the corresponding function in MySQL is char_length (str).

2. Sys_guid () function

In Oracle, random sequences can be generated through the sys_guid () function, and random sequences can be generated by MySQL through UUID ().

3. Time formatting function

Convert time to string time MySQL date_format (NOW (),'% YMY% MMI% d') corresponds to to_char (sysdate, 'YYYY-MM-DD') in Oracle of Oracle

Convert the string time to the time type MySQL str_to_date ('2019-01-01-01') corresponding to the to_date in Oracle ('2019-01-01-01) corresponding to the to_date in Oracle (' 2019-01-01-01)

It includes the function conversion of time, minute and second: DATE_FORMAT (NOW (),'% Y-%m-%d% HRV% iRV% s') and str_to_date ('2019-01-01-01-01-01-01-01-01-01-01-01 MMI% MMI% d% HGV% iRO% s').

4. Conditional functions (nvl (), nvl2 (), decode ())

Nvl (tab.columnName, 0): if the tab.columnname value is empty, the return value is 0, otherwise the MySQL function corresponding to tab.columnName; is: ifnull (tab.columnName, 0).

Nvl2 (expr1,expr2,expr3): returns expr2 if expr1 is not null, otherwise the MySQL function corresponding to expr3; is: if (expr1,expr2,expr3).

DECODE (value, val1, val2, val3): return val2 if value is equal to val1, otherwise return val3;MySQL can be expressed as IF function: if (value=val1, val2, val3)

DECODE (value, if1, val1, if2,val2,...,ifn, valn, val): returns val1 if value is equal to if1, and value2... if value is equal to if2 If value is equal to ifn, valn is returned, otherwise val;MySQL is returned. This judgment can be judged by case when then else end;l, that is, case when value=if1 then val1 when value=if2 then val2,when value=ifn then valn elseval end

5. Trunc () function

TRUNC (12.123): returns an integer (12); function corresponding to MySQL: truncate (12.123, 0)

TRUNC (12.123, 2): the return value remains 2 as a decimal (12.12); the function corresponding to MySQL: truncate (12.123, 2)

TRUNC (SYSDATE): return value is (2019-07-26 00:00:00); corresponding to MySQL is cast (now () as datetime): return value is (2019-07-26 14:11:38)

The cast function syntax of MySQL is: CAST (xxx AS type) (available type: binary, effect with binary prefix: BINARY; character type, with parameter: CHAR (); date: DATE; time: TIME; date time type: DATETIME; floating point: DECIMAL; integer: SIGNED; unsigned integer: UNSIGNED)

6. To_char () to_number ()

To_char (123): converts the number 123 into a string 123. The corresponding function for MySQL is CAST (123 AS CHAR (3)).

To_number ('123'): converts the string number 123 to a numeric type; the function corresponding to MySQL is cast (' 123' as SIGNED)

7. Sysdate current time

Sysdate: returns the current date + time; the function corresponding to MySQL is now ()

III. Other

1. Quotation mark

MySQL can recognize double quotes and single quotes, while Oracle can only recognize single quotes.

two。 String hyphen | |

Oracle can use'| |'to connect strings, but MySQL does not support'| | 'connection. MySQL can link strings through the concat () function.

A.studentname of Oracle | |'['| a.studentno | |']'is equivalent to concat of MySQL (a.studentname,'[', a.studentno,']')

3. ROWNUM

Oracle can obtain the first n records through rownum, and MySQL can obtain the first n records through limit, but the two are written slightly differently. In Oracle, rownum is part of the where condition, while in MySQL, limit is not part of the where condition.

-- rownum syntax is as follows: SELECT * FROM XJ_STUDENT WHERE ROWNUM = 1;-- query the first data item SELECT * FROM XJ_STUDENT WHERE ROWNUM 1). For example, the following sql is not supported: SELECT * FROM XJ_STUDENT WHERE ROWNUM > 2 FROM XJ_STUDENT WHERE ROWNUM select * FROM XJ_STUDENT WHERE ROWNUM = 3 limit syntax is as follows: SELECT * from fw_department limit 3 limit-query the first 3 pieces of data SELECT * from fw_department limit 2,4 -- starting with Article 2 (serial number starting with 0), check 4 records

4. Null data sorting (nulls first and nulls last)

-- null value ranked first SELECT * FROM FW_DEPARTMENT An ORDER BY A.REMARK DESC NULLS FIRST-- null value ranked last SELECT * FROM FW_DEPARTMENT An ORDER BY A.REMARK DESC NULLS LAST-- MySQL can achieve the same effect through IF and ISNULL functions-- null value ranked last select * from FW_DEPARTMENT An order by IF (ISNULL (A.REMARK), 1 A.REMARK 0), A.REMARK desc-- null value ranked first select * from FW_DEPARTMENT An order by IF (ISNULL (A.REMARK), 0Magne1), A.REMARK desc

5. Table (left / right) Association (+)

Oracle left connection, right connection can be achieved using (+). MySQL can only use keywords such as left join, right join, etc.

-- Oracle left association select * from taba, tabb where taba.id=tabb.id (+);-- Oracle right association select * from taba, tabb where taba.id (+) = tabb.id;-- MySQL left association select * from taba left join tabb on taba.id=tabb.id;-- MySQL right association select * from taba right join tabb on taba.id=tabb.id

6. Delete syntax

The delete syntax of MySQL is not as casual as Oracle. For example, the following sql can be executed in Oracle, but not in MySQL.

-- Oracle is executable, but cannot be executed in MySQL DELETE FROM FW_DEPARTMENT A WHERE A.DEPID = '111 delete FW_DEPARTMENT WHERE DEPID =' 111 delete FW_DEPARTMENT WHERE DEPID = '111' delete statement format is as follows: delete = '111'

7. Recursive query (start with connect by prior)

MySQL does not support this kind of recursive query for (start with connect by prior), but it can be implemented through custom functions.

-- Oracle Recursive query all sub-departments (including themselves) whose ID is' 1111 'SELECT * FROM FW_DEPARTMENTSTART WITH DEPID='1111'CONNECT BY PRIOR DEPID= PARENTDEPID;-- Oracle Recursive query all parent departments (including themselves) whose ID is' 1111 'SELECT * FROM FW_DEPARTMENTSTART WITH DEPID='1111'CONNECT BY PRIOR PARENTDEPID = DEPID -- MySQL first creates the fun_getDepIDList function, which is used to query department ID string CREATE FUNCTION fun_getDepIDList (rootId VARCHAR (32)) RETURNS VARCHAR (6000) BEGIN DECLARE pTemp VARCHAR (6000); DECLARE cTemp VARCHAR (6000); SET pTemp='$'; SET cTemp=rootId; WHILE cTemp is not null DO set pTemp=CONCAT (pTemp,',',cTemp) SELECT GROUP_CONCAT (depid) INTO cTemp from fw_department WHERE FIND_IN_SET (PARENTDEPID,cTemp) > 0; END WHILE; RETURN pTemp;END;-- query department ID is all sub-departments (including their own) select * from fw_departmentwhere FIND_IN_SET (DEPID, fun_getDepIDList ('1111)) -- query all parent departments whose ID is' 1111'(including their own) select * from fw_departmentwhere FIND_IN_SET ('1111, fun_getDepIDList (DEPID))

8. Merge into

MySQL does not support (merge into), but provides replace into and on duplicate key update to achieve similar functionality.

-- Oracle merge into (SELECT '1111' DEPID,' 'DEPNAME FROM DUAL) BON (A.DEPID = B.DEPID) WHEN MATCHED THEN UPDATE SET A.DEPNAME = B.DEPNAMEWHEN NOT MATCHED THEN INSERT (DEPID, DEPNAME) VALUES (B.DEPID, B.DEPNAME);-- MySQL replace into (features: 1. Delete and then add; 2. Insert / update tables must have primary keys or unique indexes -- 3. Unmodified / new data items, if required, must have a default value)-- 1. Because it is deleted first and then added, one of the following two conditions needs to be met:-- 1. Either there is a default value for the required field;-- 2. Either insert / update is assigned to a required item with no default value, otherwise an error will be reported when it is added. 2. A primary key or unique index is required in the table, otherwise duplicate data will appear in the table if the following statement is executed multiple times. Replace into fw_department (DEPID,PARENTDEPID,DEPNO,DEPNAME) values ('1111111', '1234' penalty 123, '');-- MySQL on duplicate key update (features: 1. Inserted / updated tables must have primary keys or unique indexes -- 2. Unmodified / new data items. If required, there must be a default value) insert into fw_department (depid,parentdepid,depno,depname) select '1111111' depid,' 123' parentdepid, 'e12' depno,' ha 'depnamefrom fw_departmenton duplicate key update parentdepid = values (parentdepid), depno=values (depno), depname=values (depname)

9. With

With can be used to build a temporary table in Oracle, but MySQL does not support with. For temporary tables, MySQL can be handled in parentheses, but the temporary table name must be set for the constructed temporary table.

-- Oracle with uses WITH TMPTAB AS (SELECT A.DEPID FROM FW_DEPARTMENT A) SELECT DEPIDFROM TMPTAB-- MySQL to build temporary tables (here you must set a table name for the temporary tables in parentheses) select b.depidfrom (select depidfrom fw_department) b will it help you to read the above? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.