转贴 Oracle 和 mysql 的一些简单命令对比参照 [2004年6月25日 22:35]

Oracle 和 mysql 的一些简单命令对比参照

http://202.101.18.235/club/bbs/showEssence.asp?id=22161&page=2

项目                 |  Oracle                   |      MySql

========================================

对比版本      | 7.3.4.0.0                    |      3.22.34

--------------------------------------------------------------------

控制台工具       |SVRMGR.EXE          |      mysqladmin.exe
|SVRMGR23.EXE       |

--------------------------------------------------------------------

数据库启动程序| 0start73.exe screen |mysqld-shareware.exe

--------------------------------------------------------------------

关闭数据库命令| ostop73.exe             |mysqladmin.exe -u root shutdown

--------------------------------------------------------------------

带用户启动方式 |

c:/orawin95/bin/sqlplus.exe system/manager@TNS |

c:/mysql/bin/mysql.exe test    c:/mysql/bin/mysql.exe -u root test

--------------------------------------------------------------------

默认用户（库） |sys                         | mysql

                         |system                    | test

                         |scott

--------------------------------------------------------------------

所有表             |SQL> select * from tab;| mysql> show tables;

                        |SQL> select * from cat;| c:/mysql/bin>mysqlshow 库名

-------------------------------------------------------------------

当前用户（库）| SQL> show user       |mysql> connect

--------------------------------------------------------------------

显示表结构      |SQL> desc 表名         | mysql> desc 表名;

                        |SQL> describe 表名   | mysql> describe 表名;

                        |                                  |mysql> show columns from 表名;

                        |                                  |c:/mysql/bin>mysqlshow 库名 表名

--------------------------------------------------------------------

日期函数         |SQL> select sysdate from dual;

                        |                                  |mysql> select now();

                        |                                  |mysql> select sysdate();

                        |                                  |mysql> select curdate();

                        |                                  |mysql> select current_date;

                        |                                  |mysql> select curtime();

                        |                                  |mysql> select current_time;

-----------------------------------------------------------------------

日期格式化      |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');

-----------------------------------------------------------------------

期函数（增加一个月）

                        |SQL> select |to_char(add_months(to_date('20000101','yyyymmdd'),1),'yyyy-mm-dd') from |dual; 结果：2000-02-01

                        |                                  |mysql> select date_add('2000-01-01',interval 1 month);

                        |                                  |结果：2000-02-01

-------------------------------------------------------------------

字符串截取       | substr('abcdefg',1,5)   | mid('abcdefg',2,3);

                        |substrb('abcdefg',1,5)  | substring('abcdefg',2,3);

                        |                                  | substring('abcdefg' from 2);

--------------------------------------------------------------------

外部脚本命令 |SQL >@a.sql               |1：mysql> source a.sql

                      |                                   |2：c:/mysql/bin>mysql <a.sql

                      |                                   |3：c:/mysql/bin>mysql 库名 <a.sql

-------------------------------------------------------------------

导入、导出     |exp.exe                         | mysqldump.exe

                      |exp73.exe                    |mysqlimport.exe

                      |imp.exe                        |

                      |imp73.exe                    |

------------------------------------------------------------------

限制记录条数 |SQL> select * from 表名 where rownum<5;

                        |                                  |mysql> select * from 表名 limit 5;

-------------------------------------------------------------------

外连接           |使用(+)                       |使用left join