Oracle 和 mysql 的对比参照—-开发篇

四 1st, 2011 | Filed under 其他, 系统架构及硬件
Oracle mysql
对比版本 Release 10.2.0.1.0 XE  windowsXP 5.0.45-community-nt-log MySQL Community Edition (GPL)
当作计算器 SQL> select 1+1 from dual; mysql> select 1+1;
mysql> select 1+1 from dual;
显示表结构 SQL> desc 表名 mysql> desc 表名;
SQL> describe 表名 mysql> describe 表名;
mysql> explain  表名;

注意:explain 也可以用来获取执行计划。

需要访问:

User_tables、user_tab_cols、USER_COL_COMMENTS、USER_INDEXES等等字典表。

mysql> show columns from 表名;
bin>mysqlshow 库名 表名
日期的概念 一个完全不同于字符串的类型。日期类型没有所谓的格式,日期就是日期,只有在显示的时候可以指定转换成的字符串的格式。绝对不会允许2008-00-00这种情况出现。对于日期转换为另一个日期相对比较简单,比如select trunc(sysdate) from dual; 可以方便的和字符串进行比较、转换 。允许0000-00-00这种情况出现作为null的替代品方便处理(个人不认为这样比较方便,反而给我造成困惑)。对于字符串转换为日期比较简单,比如CREATE TABLE `datetime` (

`dt` datetime NOT NULL default ‘0000-00-00 00:00:00′,

PRIMARY KEY  (`dt`)

);

INSERT INTO `datetime` VALUES (‘2008-01-01 00:00:00′);

select * from `datetime`;

日期类型 date datetime
Date

日期

Time

时间

Timestamp

如果表中其他字段变化,此类型的字段自动更新为当前系统时间。

Timestamp

保存了毫秒级别的时间

不清楚
日期函数 SQL> select trunc(sysdate) from dual; mysql> select curdate();

mysql> select current_date;

SQL> select sysdate from dual; mysql> select sysdate();
mysql> select now();
SQL> select to_char(sysdate,’HH24:Mi:SS’) from dual;

注意此处返回结果为字符串,而不是日期类型。Oracle不提供和mysql中time类型相应的类型。

mysql> select current_time;

mysql> select curtime();

注意:此处返回的是time类型。就是时间17:22:14这种时间。Oracle中没有此类型。

毫秒级别:

SQL> select current_timestamp from dual;

需要使用函数MICROSECOND。目前还不会。
日期格式化 SQL> select to_char(sysdate,’yyyy-mm-dd’) from dual; mysql> select date_format(now(),’%Y-%m-%d’);
SQL> select to_char(sysdate,’hh24-mi-ss’) from dual; mysql> select time_format(now(),’%H-%i-%S’);
日期函数

(增加一天)

SQL> select sysdate+1 from dual;

结果:2008-2-20 19:34:27

mysql> select date_add(now(),interval 1 day);
mysql>select now()+interval 1 day;
日期函数

(增加一个月)

SQL> select add_months(sysdate,1) from dual;

结果:2008-3-19 19:34:27

mysql> select date_add(now(),interval 1 month);
mysql>select now()+interval 1 month;
别名 SQL> select 1 as a from dual; mysql> select 1 as a;
SQL> select 1 a from dual; mysql> select 1 a;
字符串截取函数 SQL> select substr(‘abcdefg’,1,5) from dual;

结果:abcde

mysql> select substr(‘abcdefg’,1,5);

结果:abcde

去除空格:

select trim(‘abc   ‘) from dual;

去除空格:

select trim(‘abc   ‘) from dual;

字符串拼接:

SELECT CONCAT(‘a’,’ test’) from dual;

结果:a test

SELECT CONCAT(‘a’,’ test’);

结果:a test

注意oracle中||为字符串拼接

select ‘a’||’ test’ from dual;

结果:a test

注意mysql中||为或操作符。

select 0||1;

结果1;

Select 0||0;

结果0。

判断是否包含:

select instr(‘abcd’,'bcd’) from dual;

结果:2

mysql> SELECT INSTR(‘foobarbar’, ‘bar’);

结果:4

另有正则表达式。 另有SUBSTRING_INDEX(str,delim,count)函数
执行sql脚本 SQL >@a.sql 1:mysql> source a.sql
执行外部shell脚本 SQL>host test.sh 目前还不会
SQL>! cd ..
改表名 SQL> alter table T rename to T1; mysql> alter table t rename t1;
mysql> alter table T1 rename to  T;
执行命令 ;<回车> ;<回车>
/ go
r ego
run
distinct用法 SQL> select distinct 列1 from 表1; mysql> select distinct 列1 from 表1;
SQL> select distinct 列1,列2 from 表1; mysql> select distinct 列1,列2 from 表1;
注释
/* */ /*与*/
REM #
限制返回记录条数为5条 SQL> select * from 表名 where rownum<=5; mysql> select * from 表名 limit 5;
分页查询 select *

from (

select row_.*, rownum rownum_

from (

yourSqlHere ) row_

where rownum <= 100)

where rownum_ > 20;

select * from t limit 20,100;
外连接 (+) left join
LEFT OUTER JOIN left outer join
RIGHT OUTER JOIN
查询索引 SQL> select index_name,table_name from user_indexes; mysql> show index from 表名 [FROM 库名];
通配符 “%” “%”和“_”
如果存在则更新,否则插入 Merge into Replace into

参见,mysql的replace没有oracle的merge严格:

http://www.bitbybit.dk/carsten/blog/?p=128#comment-50048

SQL语法 SELECT selection_list 选择哪些列 SELECT selection_list 选择哪些列
FROM table_list 从何处选择行 FROM table_list 从何处选择行
WHERE primary_constraint 行必须满足什么条件 WHERE primary_constraint 行必须满足什么条件
GROUP BY grouping_columns 怎样对结果分组

注意:oracle如果需要排序必须指定order by 子句。

GROUP BY grouping_columns 怎样对结果分组

注意:即使不存在order by子句,mysql也会按照group by 的列进行排序。甚至还能为group by 语句指定asc/desc子句。

HAVING secondary_constraint 行必须满足的第二条件 HAVING secondary_constraint 行必须满足的第二条件
ORDER BY sorting_columns 怎样对结果排序 ORDER BY sorting_columns 怎样对结果排序
Oracle的结果集限定语句见:”分页查询”

注意:oracle的rownum和mysql的Limit完全不同。

LIMIT count 结果限定
对not in null的处理,是关于三值逻辑的问题。 CREATE TABLE a

( code INT );

INSERT INTO a

VALUES (2);

INSERT INTO a

VALUES (NULL);

SELECT 1

FROM DUAL

WHERE 1 NOT IN (SELECT code  FROM a);

结果:无返回结果。

CREATE TABLE a

( code INT );

INSERT INTO a

VALUES (2);

INSERT INTO a

VALUES (NULL);

SELECT 1

FROM DUAL

WHERE 1 NOT IN (SELECT code  FROM a);

结果:无返回结果

对not exists的处理 CREATE TABLE a

( code INT );

INSERT INTO a

VALUES (2);

INSERT INTO a

VALUES (NULL);

SELECT 1

FROM DUAL

WHERE NOT EXISTS (SELECT 1

FROM a WHERE a.code = 1);

结果:1

CREATE TABLE a

( code INT );

INSERT INTO a

VALUES (2);

INSERT INTO a

VALUES (NULL);

SELECT 1

FROM DUAL

WHERE NOT EXISTS (SELECT 1

FROM a WHERE a.code = 1);

结果:1

获取sql语句执行计划 SQL>EXPLAIN PLAN FOR   select * from t;

然后

SQL>select * from table(DBMS_XPLAN.DISPLAY)

Mysql> explain select * from t;
执行计划对照 全表扫描: TABLE ACCESS FULL Type=all
Where条件过滤:FILTER Extra=Using where
排序:SORT ORDER BY Extra=Using filesort
事务管理 默认不自动提交 innoDB支持事务
默认自动提交autocommit
阻塞读取方式
Unique index的区别。

又是关于三值逻辑的问题。

CREATE TABLE hr.t (

ID INT NOT NULL,

DATA CHAR(30) DEFAULT NULL,

UNIQUE (ID, DATA)

);

INSERT INTO hr.t

VALUES (1, NULL);

INSERT INTO hr.t

VALUES (1, NULL);

mysql> CREATE TABLE t (

ID INT NOT NULL,

DATA CHAR(30) DEFAULT NULL,

UNIQUE (ID, DATA)

);

INSERT INTO t

VALUES (1, NULL);

INSERT INTO t

VALUES (1, NULL);

select * from t;

Query OK, 0 rows affected

Query OK, 1 row affected

Query OK, 1 row affected

+—-+——+

| ID | DATA |

+—-+——+

|  1 | NULL |

|  1 | NULL |

+—-+——+

2 rows in set

ORA-00001: 违反唯一约束条件 (HR.SYS_C003999)
Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
标签: ,
目前还没有任何评论.