MySQL和Oracle的DDL、DML语法对比
DML(数据内容)
Oracle
-- 1、插入数据,插入数据时字段名可以使用双引号(MySQL不行)
INSERT INTO
TABLE_NAME (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5)
VALUES ('VALUE1','VALUE2','VALUE3',NULL, 'VALUE5');
INSERT INTO
TABLE_NAME ("COLUMN1","COLUMN2", "COLUMN3", "COLUMN4", "COLUMN5")
VALUES ('VALUE1', 'VALUE2', 'VALUE3', NULL, 'VALUE5');
-- 2、删除数据
DELETE FROM TABLE_NAME WHERE COLUMN='VALUE';
-- 3、更新数据
UPDATE TABLE_NAME SET COLUMN1 = 'VALUE1' WHERE COLUMN2 = 'VALUE2'
-- 示例:UPDATE student SET sex = '1' WHERE id = 5
UPDATE TABLE_NAME SET COLUMN1=TRIM(COLUMN2); -- 将字段COLUMN2的值赋给字段COLUMN1
MySQL
-- 1、插入数据
INSERT INTO
TABLE_NAME (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5)
VALUES ('VALUE1', 'VALUE2', 'VALUE3', NULL, 'VALUE5');
-- 2、删除数据
DELETE FROM TABLE_NAME WHERE COLUMN='VALUE'
-- 3、更新数据
UPDATE TABLE_NAME SET COLUMN1 = 'VALUE1' WHERE COLUMN2 = 'VALUE2'
-- 示例:UPDATE student SET sex = '1' WHERE id = 5
UPDATE TABLE_NAME SET COLUMN1=TRIM(COLUMN2); -- 将字段COLUMN2的值赋给字段COLUMN1
总结:
就DML语句而言,MySQL和Oracle两者的语法几乎没有区别,MySQL能执行的DML语句Oracle依然可以执行。我们在写SQL时一定要注意语法格式
DDL(表的格式)
Oracle
-- 1、增加字段
ALTER TABLE TABLE_NAME ADD COLUMN varchar(10)
-- 2、删除字段
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN1;
-- 3、修改字段
ALTER TABLE TABLE_NAME MODIFY COLUMN NUMBER(10); --修改字段类型
ALTER TABLE TABLE_NAME MODIFY COLUMN DEFAULT 0; --修改字段默认值
COMMENT ON COLUMN TABLE_NAME.COLUMN IS '注释内容' --修改字段注释
ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN1 TO COLUMN2; --修改字段名字
-- 示例:alter table STUDENT rename column A to B; --将字段名字由A修改为B
MySQL
-- 增加字段
ALTER TABLE TABLE_NAME ADD IF NOT EXISTS COLUMN varchar(10) COMMENT 'abc666';
-- 删除字段
ALTER TABLE TABLE_NAME DROP IF EXISTS COLUMN;
-- 修改字段
ALTER TABLE TABLE_NAME MODIFY COLUMN INT(10); -- 修改字段类型
ALTER TABLE TABLE_NAME MODIFY COLUMN CHAR(14) DEFAULT 20; -- 修改字段默认值
ALTER TABLE TABLE_NAME MODIFY COLUMN CHAR(14) COMMENT '这里是注释'; -- 修改字段注释
ALTER TABLE TABLE_NAME CHANGE COLUMN1 COLUMN2 varchar(100) -- 修改字段名字
-- 示例:alter table STUDENT change NAME nickname varchar(100); --将字段名字由name修改为nickname
总结
- 对于增加和删除字段,Oracle没有IF NOT语法
- Oracle在使用删除字段时比MySQL要多一个COLUMN
- MySQL在修改字段时,必须要带上字段的类型
- Oracle和MySQL修改注释和修改字段名字的方法有区别
可重复执行SQL脚本补充
在实际开发中,我们的SQL脚本往往是希望它能够重复执行的,所以就有了接下来的操作。
由于MySQL中存在IF NOT语法,所以在DDL的增加和删除字段中,直接配合IF NOT使用(使用方式前文已给出),就能达到可重复执行的效果。
但是Oracle中想要达到可重复执行的效果则要使用其他的方式。
我这里使用的方法是,给增加和删除添加一个存储过程(TRYADDTABCOLUMN、TRYDROPTABCOLUMN),具体的存储过程如下:
create or replace procedure TRYADDTABCOLUMN(tabName in varchar2, colName in varchar2, colType in varchar2) is
n_col int;
stmt VARCHAR(2000);
begin
select count(*) into n_col from cols
where table_name = upper(tabName) and column_name = upper(colName);
if n_col<1 then
stmt :='alter table '||tabName||' add '||colName||' '||colType;
execute immediate stmt;
end if;
end;
create or replace procedure TRYDROPTABCOLUMN(tabName in varchar2, colName in varchar2) is
n_col int;
stmt VARCHAR(2000);
begin
select count(*) into n_col from cols
where table_name = upper(tabName) and column_name = upper(colName);
if n_col>0 then
stmt :='alter table '||tabName||' drop column '||colName;
execute immediate stmt;
end if;
end;
使用方式:
-- 增加
CALL TRYADDTABCOLUMN('TABLE_NAME','COLUMN','NUMBER(19)');
-- 删除
CALL TRYDROPTABCOLUMN('TABLE_NAME','COLUMN');
补充:Oracle修改字段时,字段类型不一致的处理办法(已存在数据),
强制转换会出现如下错误提示:ORA-01439:要更改数据类型,则要修改的列必须为空
-- 当需要修改的字段有数据并用要修改的新类型和原类型不兼容时(char(1)--> number(10),char字段的值为数字),可以新建字段来间接修改。
-- ①新建一个新字段
CALL TRYADDTABCOLUMN('TABLE_NAME','COLUMN1','number(10)');
-- ②将旧字段的值赋值给新字段
UPDATE TABLE_NAME SET COLUMN2=TRIM(COLUMN1); --字段的赋值
-- ③删除旧字段
CALL TRYDROPTABCOLUMN('TABLE_NAME','COLUMN1');
-- ④修改新字段的名字
ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN2 TO COLUMN1; -- 修改字段名