1、MySQL与Oracle语法对比,Oracle中有NUMBER；MySQL中有INT/INTEGER/DECIMAL，INT=NUMBER(10)，DECIMAL=NUMBER(10,2) Oracle中有VARCHAR2；MySQL有VARCHAR Oracle中有DATE，包含年月日时分秒信息；MySQL中有DATE/TIME/DATETIME,MySQL与Oracle语法比较之数据类型,MySQL与Oracle语法比较之基本语法(一),- Oracle SELECT a.* FROM t_test_a a, t_test_b b WHERE a.id = b.id(+); - MySQL
2、 SELECT a.* FROM t_test_a a LEFT JOIN t_test_b b ON a.id = b.id;,- Oracle SELECT a.* FROM t_test_a a WHERE ROWNUM 10; - MySQL SELECT a.* FROM t_test_a a LIMIT 4;,- Oracle SELECT * FROM t_test_a a WHERE a.address = ; - MySQL SELECT * FROM t_test_a a WHERE a.address IS NULL; SELECT * FROM t_test_a a W
3、HERE a.address = ;,- Oracle SELECT SYSDATE FROM DUAL; - MySQL SELECT NOW(); SELECT SYSDATE(); SELECT CURDATE(); SELECT CURRENT_DATE(); SELECT CURRENT_DATE; SELECT CURRENT_TIME;,连接可以使用(+)来实现；MySQL只能使用LEFT JOIN ，RIGHT JOIN等关键字 Oracle中可通过ROWNUM来实现分页；MySQL中只能使用LIMIT Empty Sting作为参数传入后，Oracle中会将Empty Str
4、ing识别为NULL；MySQL中Empty String还是Empty String MySQL中做查询时，FROM后如果是一个子查询，那么必须指定子查询的别名，否则会报错 Oracle中存在DUAL虚拟表用于构成SELECT 的语法规则；MySQL中的SELECT语句中可以省略FROM表达式 连接字符串在Oracle中用| ； MySQL中用CONCAT函数,MySQL与Oracle语法比较之基本语法(二),Oracle中使用函数TO_CHAR对日期数据的操作；MySQL中使用DATE_FORMAT/TIME_FORMAT/YEAR/DATE_ADD等 Oracle用DECODE()来转换
5、数据，MySQL 用CASE WHEN Oracle用SUBSTR截取字符串，MySQL中存在更多的处理函数，如：LEFT/RIGHT/SUBSTR/SUBSTRING/SUBSTRING_INDEX/MID，SUBSTRING函数功能非常强大和灵活，因此我们经常用SUBSTRING Oracle 对于GROUP BY是严格的，所有要SELECT出来的字段必须在GROUP BY后边出现；MySQL则不同，如果SELECT出来的字段在GROUP BY 后面没有，则会随机取出来一个值 Oracle使用WM_CONCAT函数实现字段合并，MySQL中使用GROUP_CONCAT函数,- Oracle
6、 SELECT a.*, DECODE(a.sex, 1, 男, 女) sex_txt FROM t_test_a a; - MySQL SELECT a.*, (CASE a.sex WHEN 1 THEN 男 ELSE 女 END) sex_txt FROM t_test_a a;,- Oracle SELECT a.age, count(1) FROM t_test_a a GROUP BY a.age; - MySQL SELECT a.* FROM t_test_a a GROUP BY a.age;,- Oracle SELECT TO_CHAR(ADD_MONTHS(TO_DAT
7、E(20000101,yyyymmdd),5),yyyy-mm-dd) FROM DUAL; SELECT TO_DATE(2009-3-6,yyyy-mm-dd) - TO_DATE(2009-1-6,yyyy-mm-dd) VAULE FROM DUAL - MySQL - 日期格式化 SELECT DATE_FORMAT(NOW(),%Y-%m-%d %H-%i-%S); - 获取年月日 SELECT YEAR(NOW(), MONTH(NOW(), DAY(NOW(); - 向日期添加指定的时间间隔 SELECT DATE_ADD(NOW(),INTERVAL 2 MONTH); -
8、向日期减去指定的时间间隔 SELECT DATE_SUB(NOW(),INTERVAL 2 MONTH); -返回两个日期之间的天数 SELECT DATEDIFF(STR_TO_DATE(2008-12-30,%Y-%m-%d), STR_TO_DATE(2008-12-26,%Y-%m-%d); SELECT DATEDIFF(2008-12-30,2008-12-26);,MySQL中INT主键可自增长，在建表时指定主键自增长即可；Oracle则需使用序列 MySQL里可以用双引号包起字符串，Oracle里只可以用单引号包起字符串 MySQL中使用ALTER TABLE old_name
9、 RENAME new_name更改表名；Oracle中使用RENAME old_name TO new_name,MySQL与Oracle语法比较之基本语法(三),MySQL与Oracle语法比较之条件循环语句,Oracle和MySQL中的IF语句使用起来完全相同，除了ELSIF/ELSEIF关键字有一个单词的差别之外 Oracle 使用FORLOOPEND LOOP结构实现FOR循环；MySQL中使用label:LOOPEND LOOP Oracle中使用WHILE expression LOOPEND LOOP结构实现WHILE循环；MySQL中使用WHILE expression DO
10、END WHILE,Mysql - IF 语句 IF SCORE =0 AND SCORE = 60 AND SCORE = 80 AND SCORE 90 THEN 良好 ELSE 优秀 END IF;,mysql- WHILE 语句 CREATE PROCEDURE prc_test_while() BEGINDECLARE i INT;SET i=0;WHILE i5 DOINSERT INTO t_test_a(name, age, score) VALUES(name7, 20, 90);SET i = i + 1;END WHILE; END;,- FOR 语句 CREATE PR
11、OCEDURE prc_test_loop() BEGINDECLARE i INT DEFAULT 0;label: LOOPINSERT INTO t_test_a(name, age, score) VALUES (name6, 20, 90);SET i = i + 1;IF i=5 THENLEAVE label;END IF;END IF; END;,MySQL与Oracle语法比较之存储过程(一),Oracle使用CREATE OR REPLACE PROCEDURE 语法创建存储过程；MySQL使用CREATE PROCEDURE创建，如果已经存在，只能先DROP，再CREAT
12、E MySQL存储过程名字后面的”()”是必须的，即使没有一个参数 Oracle中参数类型IN/OUT/INOUT写在参数名后面且都不能省略；MySQL参数类型IN/OUT/INOUT写在参数名前面，如果是IN,则可以省略，如果是OUT或INOUT则不能省略 MySQL 存储过程的参数不能指定默认值，不能在参数名称前加“” Oracle变量的声明在BEGINEND体之前；MySQL变量的声明在BEGINEND体内，BEGIN之后其他内容之前,变量名不能和列名相同， MySQL不区分大小写,1.用户变量：以“”开始，形式为“变量名”，用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使
13、用的客户端生效 2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set global.变量名 ，对所有客户端生效。只有具有super权限才可以设置全局变量 3.会话变量：只对连接的客户端有效。 4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量 declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量,Oracle存储过程和方法都可以使用RETURN退出当前过程；MySQL存储过程中只能使用LEAVE退出当前存储过程，不可以使用RETURN，如：LEAVE label； Oracle中NO_DATA_
14、FOUND是游标的一个属性，当SELECT没有查到数据就会出现 No Data Found的异常，程序不会向下执行；MySQL中则没有NO_DATA_FOUND属性，但可以使用FOUND_ROWS()方法得到SELECT语句查询出来的数据，如果FOUND_ROWS()得到的值为0，就进入异常处理逻辑. Oracle中调用存储过程直接写存储过程名即可，如：prc_Procedure_Name(参数)，没有参数时可省略“()”；MySQL需用CALL关键字，如CALL prc_Procedure_Name(参数)，即使没有一个参数也需要“()”,MySQL与Oracle语法比较之存储过程(二),-
15、 创建存储过程 DROP PROCEDURE IF EXISTS prc_test; CREATE PROCEDURE prc_test(parameter1 VARCHAR(20), OUT parameter2 VARCHAR(20) label : BEGIN DECLARE param1 VARCHAR(20); IF parameter1 = THENSELECT 空;LEAVE label; END IF; SET param1 := parameter1; SET param1 = param1; SET parameter2 = parameter1; SELECT param
16、1; END;,MySQL与Oracle语法比较之触发器,Oracle使用CREATE OR REPLACE TRIGGER 语法创建触发器；MySQL使用CREATE TRIGGER创建触发器，如果已经存在，只能先DROP，再CREATE Oracle可以在一个触发器触发INSERT、DELETE及UPDATE事件；MySQL每个触发器只支持一个事件，也就是说，目前每个TRIGGER需要拆分成3个MySQL TRIGGER Oracle的TRIGGER中使用:NEW和:OLD取得新老数据；MySQL的TRIGGER中使用NEW和OLD关键字来取得新老数据，NEW和OLD不区分大小写,- 触发
17、器 DROP TRIGGER IF EXISTS tr_update_age; CREATE TRIGGER tr_update_age AFTER UPDATE ON t_test_b FOR EACH ROW UPDATE t_test_a a SET a.age = new.age WHERE a.id = new.id;,MySQL与Oracle语法比较之特有语法,SELECT NOW(); UPDATE t_test_a a, t_test_b b SET a.age = b.age WHERE a.id = b.id; SELECT a.* FROM t_test_a a LEFT
18、 JOIN (t_test_b b, t_test_c c) ON (a.id = b.id AND a.id = c.id); ALTER TABLE t_test_a ADD COLUMN phone varchar(255) NULL AFTER age; INSERT INTO t_test_a(name, age) VALURS(name7, 20), (name8, 20);,UPDATE t_test_a a SET a.age = (SELECT b.age FROM t_test_b b WHERE a.id = b.id) WHERE EXISTS(SELECT 1 FRO
19、M t_test_b b WHERE a.id = b.id);,MySQL中的DEFINER和INVOKER,创建存储过程、事件、视图、触发器以及函数的时候可以指定 SQL SECURITY属性，设置为 DEFINER 或者INVOKER，用来告诉MySQL在执行存储过程、视图等的时候，是以DEFINER用户的权限来执行，还是以调用者的权限来执行。默认情况下，使用DEFINER方式，此时调用存储过程的用户必须有存储过程的EXECUTE权限，并且DEFINER指定的用户必须是在mysql.user表中存在的用户。DEFINER模式下，默认DEFINER=CURRENT_USER，在存储过程执行
20、时，MySQL会检查DEFINER定义的用户user_namehost_name的权限；INVOKER模式下，在存储过程执行时，会检查存储过程调用者的权限。,- 案例一：DEFINER CREATE DEFINER = adminlocalhost PROCEDURE prc_account_count() BEGIN SELECT Number of accounts:, COUNT(*) FROM mysql.user; END; 在这个案例中，不论哪个用户调用存储过程，存储过程都会以adminlocalhost的权限去执行，即使这个用户没有查询mysql.user表的权限。,- 案例二：INVOKER CREATE DEFINER = adminlocalhost PROCEDURE prc_account_count() SQL SECURITY INVOKER BEGIN SELECT Number of accounts:, COUNT(*) FROM mysql.user; END; 在这个案例中，虽然存储过程语句中仍然带有DEFINER参数，但是由于SQL SECURITY指定了INVOKER，所以在存储过程执行的时候，会以调用者的额身份去执行。此时这个存储过程是否能成功执行，取决于调用者是否有mysql.user表的查询权限。,
