A detailed comparison of the syntax differences between MySQL and Oracle
- 2020-05-15 02:24:53
- OfStack
Compare Oracle and mysql's 1 simple commands
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
Add 1 month:
SQL > select to_char(add_months(to_date ('20000101','yyyymmdd'),1),'yyyy-mm-dd') from dual;
Results: the 2000-02-01
SQL > select to_char(add_months(to_date('20000101','yyyymmdd'),5),'yyyy-mm-dd') from dual;
Results: the 2000-06-01
mysql > select date_add('2000-01-01',interval 1 month);
Results: the 2000-02-01
mysql > select date_add('2000-01-01',interval 5 month);
Results: the 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);
Results: bcd
mysql > select mid('abcdefg',2,3);
Results: bcd
mysql > select substring('abcdefg',2);
Results: bcdefg
mysql > select substring('abcdefg' from 2);
Results: bcdefg
2) if the table after from in MySQL is (select...) This, then, must be followed by an alias
3) connection string in Oracle using ||,SqlServer using +,MySQL using concat('a','b','c')
4)
In SqlServer:
In MySQL:
In Orcale:
5)MySQL stored procedure:
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 a display cursor. Examples are as follows:
Perform:
7) mysql group by statement can select not grouped fields, such as
select id,name,age from A group by age
But in orcale and sqlserver, errors are reported. The row in which id and name are fetched is the first row in each group.
8)orcale USES decode() to convert data, mysql,sqlserver USES case when:
case t. DETECT_RESULT when '2402' then t. SAMPLEID end (end is required)
9)mysql: subtract data from two select:
(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
10) convert,cast
mysql converts varchar to int
convert(field name, SIGNED)
Character set conversion: CONVERT(xxx USING gb2312)
The type conversion is a little different from SQL Server1, which is the type parameter: CAST(xxx AS type), CONVERT(xxx type)
Available types
Base 2, with the binary prefix: BINARY
Character type, with parameter: CHAR()
Date: DATE
Time: TIME
Date time type: DATETIME
Floating point: DECIMAL
Integer: SIGNED
Unsigned integer: UNSIGNED
11) if there is no scrambled code when fetching from mysql database, and the code is scrambled in Java List, then there may be fields in SQL statement that are not of the data type of varchar. In this case, it is necessary to convert convert(field name, type) to 1, and Orcale USES ToChar function
12) clob is used for large fields of Orcale, blob is used for images, and String is used for fields of clob in the mapping file of Hibernate
13) mysql,orcale,sqlserver statement execution order
Start - > FROM clause - > WHERE clause - > GROUP BY clause - > HAVING clause - > ORDER BY clause - > SELECT clause - > LIMIT clause - > The final result
After each clause is executed, an intermediate result is produced for the use of the next clause, and if a clause does not exist, it is skipped.
14) LPAD function
1 there is a function LPAD(String a,int length,String addString) in the database of oracle.
Effect: add addString to the left of a. length is the length of the return value.
Example 3
15)Orcale without TOP is passed
select * from (select * from A order by id desc) where rownum=1
Note: you cannot write select * from A id rownum=1 order by id desc because the order of statement execution is where first and order by first.
You cannot write rownum=2 or rownum > 1 this way, because Orcale must include clause 1 by default.
If article 2 must be used, it can be written as:
16)Orcale,MySql while cyclic comparison
Orcale:
Can also:
mysql:
17)orcale generates the only 1 sequence that is select sys.guid() from dual, mysql is select uuid() from dual
18)MySql and Orcale ID self-increment
Since MySql implements the self-increment of ID in the database, if you want to return the inserted ID of a sequence, you can only use the following method:
Since ID of Orcale was obtained through select SEQ_BLOG_ID.nextval from dual before the data was inserted, it is possible to return directly. ps:SEQ_BLOG_ID is sequence set in the database.
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
Add 1 month:
SQL > select to_char(add_months(to_date ('20000101','yyyymmdd'),1),'yyyy-mm-dd') from dual;
Results: the 2000-02-01
SQL > select to_char(add_months(to_date('20000101','yyyymmdd'),5),'yyyy-mm-dd') from dual;
Results: the 2000-06-01
mysql > select date_add('2000-01-01',interval 1 month);
Results: the 2000-02-01
mysql > select date_add('2000-01-01',interval 5 month);
Results: the 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);
Results: bcd
mysql > select mid('abcdefg',2,3);
Results: bcd
mysql > select substring('abcdefg',2);
Results: bcdefg
mysql > select substring('abcdefg' from 2);
Results: bcdefg
2) if the table after from in MySQL is (select...) This, then, must be followed by an alias
3) connection string in Oracle using ||,SqlServer using +,MySQL using concat('a','b','c')
4)
In SqlServer:
declare @id varchar(50);
set @id='4028e4962c3df257012c3df3b4850001';
select * from sims_sample_detect where ID= @id;
In MySQL:
set @a = 189;
select * from bc_article where id = @a // Don't have to declare
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 ;
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 a display cursor. 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 There's a difference, Oracle the PL/SQL There is a hidden change in the pointer to
The amount %notfound . Mysql Is through the 1 a Error handler To make a judgment
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 = ' 100' where haoma=v_haoma;
end if;
END IF;
END LOOP cur1;
CLOSE cur1;
END$$
DELIMITER ;
Perform:
call liyukun(@a);
select @a;
7) mysql group by statement can select not grouped fields, such as
select id,name,age from A group by age
But in orcale and sqlserver, errors are reported. The row in which id and name are fetched is the first row in each group.
8)orcale USES decode() to convert data, mysql,sqlserver USES case when:
case t. DETECT_RESULT when '2402' then t. SAMPLEID end (end is required)
9)mysql: subtract data from two select:
(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
10) convert,cast
mysql converts varchar to int
convert(field name, SIGNED)
Character set conversion: CONVERT(xxx USING gb2312)
The type conversion is a little different from SQL Server1, which is the type parameter: CAST(xxx AS type), CONVERT(xxx type)
Available types
Base 2, with the binary prefix: BINARY
Character type, with parameter: CHAR()
Date: DATE
Time: TIME
Date time type: DATETIME
Floating point: DECIMAL
Integer: SIGNED
Unsigned integer: UNSIGNED
11) if there is no scrambled code when fetching from mysql database, and the code is scrambled in Java List, then there may be fields in SQL statement that are not of the data type of varchar. In this case, it is necessary to convert convert(field name, type) to 1, and Orcale USES ToChar function
12) clob is used for large fields of Orcale, blob is used for images, and String is used for fields of clob in the mapping file of Hibernate
13) mysql,orcale,sqlserver statement execution order
Start - > FROM clause - > WHERE clause - > GROUP BY clause - > HAVING clause - > ORDER BY clause - > SELECT clause - > LIMIT clause - > The final result
After each clause is executed, an intermediate result is produced for the use of the next clause, and if a clause does not exist, it is skipped.
14) LPAD function
1 there is a function LPAD(String a,int length,String addString) in the database of oracle.
Effect: add addString to the left of a. length is the length of the return value.
Example 3
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: do not write last 1 The function will add to the left of the return value by default 1 A blank 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
15)Orcale without TOP is passed
select * from (select * from A order by id desc) where rownum=1
Note: you cannot write select * from A id rownum=1 order by id desc because the order of statement execution is where first and order by first.
You cannot write rownum=2 or rownum > 1 this way, because Orcale must include clause 1 by default.
If article 2 must be used, it can be written as:
select * from (select id,rownum as row_num from lws_q_bl_result r where r.sample_id = 'B10226072') where row_num=2
16)Orcale,MySql while cyclic comparison
Orcale:
while num<10
loop
str := to_char(num);
num := num+1;
end loop;
Can also:
for num in 1..10 -- Such a defect is the inability to interval values
loop
str := to_char(num);
end loop;
mysql:
while num<10
do
str := to_char(num);
num := num+1;
end while;
17)orcale generates the only 1 sequence that is select sys.guid() from dual, mysql is select uuid() from dual
18)MySql and Orcale ID self-increment
Since MySql implements the self-increment of ID in the database, if you want to return the inserted ID of a sequence, you can only use the following method:
set @a = 189;
select * from bc_article where id = @a // Don't have to declare
Since ID of Orcale was obtained through select SEQ_BLOG_ID.nextval from dual before the data was inserted, it is possible to return directly. ps:SEQ_BLOG_ID is sequence set in the database.