mysql和oracle区别

由于工作的需要，将学习下mysql，自然而然就拿oracle进行比较，以下只是个人的一些笔记，不对之处还请指正。

环境：mysql 5.1.22+windowsxp
oracle9.2.0.4+linux as 4 x86_64(前面的有部分是在10.2.0.3下测试)

一、启动、连接、关闭
C:\Program Files\MySQL\MySQL Server 5.1\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

登陆MySQL的方式
在命令行输入：
mysql -h host -u username -p
如果是登录到本机的MySQL Server，则不需要 -h host 部分；
如果希望在命令行中指定密码，则必须紧贴着 -p 参数，例如 -p1234，但是出于安全性的考虑，不推荐这样使用。

C:\Program Files\MySQL\MySQL Server 5.1\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

启动oracle
sqlpluls /nolog
@>conn sys/pswd as sysdba
Connected to an idle instance.
SYS@bosscenter>startup
ORACLE instance started.

连接oracle方式
sqlplus /nolog
conn user/pswd

关闭数据库
SYS@bosscenter>shutdown immdiate;

二、版本查询

显示MySQL的版本
mysql> SELECT version();
+---------------------+
| version() |
+---------------------+
| 5.1.22-rc-community |
+---------------------+
1 row in set (0.06 sec)

显示oracle版本
select * from v$version;

BANNER
-----------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

三、查看数据库参数
mysql> show variables;

SYS@bosscenter>show parameter

四、查看当前时间
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2007-10-22 11:25:28 |
+---------------------+
1 row in set (0.00 sec)

SYS@bosscenter>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2007-10-22 11:27:43

五、查看数据库实例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)

SYS@bosscenter>select instance_name,version,status,database_status from v$instance;

INSTANCE_NAME VERSION STATUS DATABASE_STATUS
-------------------------------- -------------------- ------------------------ ------------------
bosscenter 10.2.0.3.0 OPEN ACTIVE

六、查看当前模式下的所有表
mysql> show tables;

SYS@bosscenter>select table_name from user_tables;

七、查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@loc

alhost |
+----------------+

SYS@bosscenter>show user
USER is "SYS"

八、字符集
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | utf8
|
| character_set_filesystem | binary
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)

SYS@bosscenter>select * from nls_database_parameters;

PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET UTF8
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 10.2.0.3.0

20 rows selected.

九、当前用户权限
mysql> SHOW GRANTS;
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
|
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8A8
5934FFBB0E2A8B4A14B18133B4619A86E44F5' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT * FROM USER_PRIVILEGES;

sys@bosscenter>select * from session_privs;

十、输出到文件
mysql> tee c:/01.txt
L

ogging to file 'c:/01.txt'

sys@bosscenter>spool '/u01/app/oracle/01.txt'
本文来自: IXPUB技术社区(https://www.360docs.net/doc/a07277079.html,) 详细出处参考：https://www.360docs.net/doc/a07277079.html,/thread-850333-1-1.html

十一、mysql的database和oracle的schema、tablespace

给我的感觉是mysql的create database就像oracle的表空间一样，把这个模式下的数据都存放在指定的地方，又有点像schema，存放一些对象的集合。

下面做下测试。

mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)

mysql> use mytest;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mytest |
+------------+
1 row in set (0.00 sec)

创建用户mytest指定database为mytest，并给于所有操作在mytest下的权限。
mysql> grant all privileges on mytest.* to mytest@'localhost' identified by 'mytest';
Query OK, 0 rows affected (0.06 sec)

mysql> grant all privileges on mytest.* to mytest@'%' identified by 'mytest';
Query OK, 0 rows affected (0.02 sec)
'%'表示能从其他主机连接，oracle没有这个限制。

用mytest连接，如下：
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u mytest -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.22-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.02 sec)

mysql> use mysql
ERROR 1044 (42000): Access denied for user 'mytest'@'localhost' to database 'mysql'
mysql> use mytest
Database changed

oracle创建用户mytest指定表空间mytest，给于指定权限。
SQL> create tablespace mytest datafile '/u01/app/oracle/oradata/boss/mytest01.dbf' size 100m;
Tablespace created.

SQL> create user mytest identified by mytest default tablespace mytest;
User created.

SQL> grant connect,resource,imp_full_database,exp_full_database,create public synonym,drop public synonym to mytest;
Grant succeeded.

用mytest连接，如下
SQL> conn mytest/mytest
Connected.

SQL> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ------------------------------ ------------------------------
MYTEST MYTEST TEMP

mysql对database的大小无法控制，没有限制，也就是无限大。oracle对表空间有大小的限制，当然它也能自动扩展，也可以不自动扩展。

比较起来mysql的database和oracle的schema的相似之处还是比较明显的，比如你在当前的database或则schema下，想查看别的模式下的数据（前提是你有这个权限），如下：

mysql> select count(*) from https://www.360docs.net/doc/a07277079.html,er;
ERROR 1142 (42000): SELECT command denied to user 'm

ytest'@'localhost' for table 'user'

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.22-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mytest
Database changed
mysql> select count(*) from https://www.360docs.net/doc/a07277079.html,er;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

SQL> select count(*) from https://www.360docs.net/doc/a07277079.html,er$;
select count(*) from https://www.360docs.net/doc/a07277079.html,er$
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn / as sysdba
Connected.
SQL> select count(*) from https://www.360docs.net/doc/a07277079.html,er$;

十二、mysql的AUTO_INCREMENT和oracle的SEQUENCE

以后的测试为了保证2个数据库的数据一致，我使用了oracle经典的scott模式的数据
oracle>conn mytest/mytest
Connected.

oracle>@$ORACLE_HOME/sqlplus/demo/demobld.sql

oracle>select table_name from user_tables;

TABLE_NAME
------------------------------------------------------------
BONUS
DEPT
DUMMY
EMP
SALGRADE

使用MySQL Migration Toolkit把mytest模式数据全部迁移到mysql数据，具体省略，有兴趣可以迁移查看相关文档。这样2个数据库的数据就是一样了。

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| bonus |
| dept |
| dummy |
| emp |
| salgrade |
+------------------+
5 rows in set (0.11 sec)

mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| 1 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 2 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 3 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 4 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 5 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 6 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 7 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 8 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 |
| 9 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 10 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 |
| 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 13 |

FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> alter table emp change column empno empno int unsigned NOT NULL auto_increment,ADD PRIMARY KEY (empno);
Query OK, 14 rows affected (0.36 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES('MARTIN', 'SALESMAN',7698,now(), 1250, 1400, 30);
Query OK, 1 row affected (0.08 sec)

mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| 1 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 2 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 3 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 4 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 5 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 6 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 7 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 8 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 |
| 9 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 10 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 |
| 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 13 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 15 | MARTIN | SALESMAN | 7698 | 2007-10-24 11:13:15 | 1250.00 | 1400.00 | 30 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.01 sec)

mysql> select last_insert_id()
-> ;
+------------------+
| last_insert_id() |
+------------------+
| 15 |
+------------------+
1 row in set (0.00 sec)

last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。

mysql> delete from emp where empno=15;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES('MARTIN', 'SAL

ESMAN',7698,now(), 1250, 1400, 30);
Query OK, 1 row affected (0.03 sec)

mysql> select * from emp order by empno desc limit 5;
+-------+--------+----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+---------------------+---------+---------+--------+
| 16 | MARTIN | SALESMAN | 7698 | 2007-10-24 11:21:17 | 1250.00 | 1400.00 | 30 |
| 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 13 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 |
+-------+--------+----------+------+---------------------+---------+---------+--------+
5 rows in set (0.00 sec)

mysql> delete from emp where empno=16;
Query OK, 1 row affected (0.03 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table emp AUTO_INCREMENT=15;
Query OK, 14 rows affected (0.27 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES('MARTIN', 'SALESMAN',7698,now(), 1250, 1400, 30);
Query OK, 1 row affected (0.03 sec)

mysql> select * from emp order by empno desc limit 5;
+-------+--------+----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+---------------------+---------+---------+--------+
| 15 | MARTIN | SALESMAN | 7698 | 2007-10-24 11:33:53 | 1250.00 | 1400.00 | 30 |
| 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 13 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 |
+-------+--------+----------+------+---------------------+---------+---------+--------+
5 rows in set (0.00 sec)

oracle>CREATE SEQUENCE emp_sequence INCREMENT BY 1.START WITH 1 NOMAXVALUE NOCYCLE CACHE 10;
Sequence created.
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环

oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);
1 row created.

oracle>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- -------------------- ------------------ ---------- --------- ---------- ---------- ----------
1 SMITH CLERK 7902 17-DEC-80 800

20
2 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
3 WARD SALESMAN 7698 22-FEB-81 1250 500 30
4 JONES MANAGER 7839 02-APR-81 2975 20
5 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
6 BLAKE MANAGER 7839 01-MAY-81 2850 30
7 CLARK MANAGER 7839 09-JUN-81 2450 10
8 SCOTT ANALYST 7566 09-DEC-82 3000 20
9 KING PRESIDENT 17-NOV-81 5000 10
10 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
11 ADAMS CLERK 7788 12-JAN-83 1100 20
12 JAMES CLERK 7698 03-DEC-81 950 30
13 FORD ANALYST 7566 03-DEC-81 3000 20
14 MILLER CLERK 7782 23-JAN-82 1300 10
1 LEWIS CLERK 7902 24-OCT-07 1200 20
15 rows selected.

oracle>rollback;
Rollback complete.

oracle>ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY(empno);
Table altered.

oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);
INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20)
*
ERROR at line 1:
ORA-00001: unique constraint (MYTEST.PK_EMP) violated

oracle>alter sequence emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE;

oracle>alter sequence emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE;
alter sequence emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE
*
ERROR at line 1:
ORA-02283: cannot alter starting sequence number
你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create .

oracle>drop SEQUENCE emp_sequence;
Sequence dropped.

oracle>CREATE SEQUENCE emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE;
Sequence created.

oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);
1 row created.

oracle>select * from (select * from emp order by empno desc) where ROWNUM<6;

EMPNO ENAME JOB MGR HIRE

DATE SAL COMM DEPTNO
------- -------------------- ------------------ ---------- --------- ---------- ---------- ----------
15 LEWIS CLERK 7902 24-OCT-07 1200 20
14 MILLER CLERK 7782 23-JAN-82 1300 10
13 FORD ANALYST 7566 03-DEC-81 3000 20
12 JAMES CLERK 7698 03-DEC-81 950 30
11 ADAMS CLERK 7788 12-JAN-83 1100 20

oracle>select emp_sequence.nextval from dual;
NEXTVAL
----------
16

oracle>select emp_sequence.nextval from dual;
NEXTVAL
----------
17

mysql一个表只能有一个AUTO_INCREMENT属性,且该属性必须为主键的一部分,且初始值可以通过alter来修改。oracle的sequence是数据库的一个object，不一定非得在主键，不能通过alter初始值，只能删除重建，且sequence的灵活性明显比较大

十三、分页查询--mysql的limit和oracle的rownum

查询前5条记录
mysql> select * from emp limit 0,5;
+-------+--------+----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+---------------------+---------+---------+--------+
| 1 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 2 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 3 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 4 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 5 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+---------------------+---------+---------+--------+
5 rows in set (0.00 sec)

oracle>select * from emp where rownum<6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- -------------------- ------------------ ---------- --------- ---------- ---------- ----------
1 SMITH CLERK 7902 17-DEC-80 800 20
2 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
3 WARD SALESMAN 7698 22-FEB-81 1250 500 30
4 JONES MANAGER 7839 02-APR-81 2975 20
5 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

Elapsed: 00:00:00.01

查询6-10条记录

mysql> select * from emp limit 5,5;
+-------+--------+-----------+------+---------------------+---------+------

+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+------+--------+
| 6 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 7 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 8 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 |
| 9 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 10 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
+-------+--------+-----------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)

oracle>select * from (select rownum rn,emp.* from emp where rownum<11) where rn>5;

RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- -------------------- ------------------ ---------- --------- ---------- ---------- ----------
6 6 BLAKE MANAGER 7839 01-MAY-81 2850 30
7 7 CLARK MANAGER 7839 09-JUN-81 2450 10
8 8 SCOTT ANALYST 7566 09-DEC-82 3000 20
9 9 KING PRESIDENT 17-NOV-81 5000 10
10 10 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

Elapsed: 00:00:00.01

oracle>select * from emp where rownum<11
2 minus
3 select * from emp where rownum<6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- -------------------- ------------------ ---------- --------- ---------- ---------- ----------
6 BLAKE MANAGER 7839 01-MAY-81 2850 30
7 CLARK MANAGER 7839 09-JUN-81 2450 10
8 SCOTT ANALYST 7566 09-DEC-82 3000 20
9 KING PRESIDENT 17-NOV-81 5000 10
10 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

Elapsed: 00:00:00.00

相对来说limit比较简单，oracle使用rownum也能达到目的，只是稍微复杂了点。

回复引用 TOP

shengang34 发短消息
加为好友
shengang34 (态度决定一切)当前离线

UID298008 帖子39 精华0 积分25 阅读权限20 在线时间0 小时注册时间2005-3-28 最后登录2009-2-16
普通会员

社区积分-1 技术积分26 10楼发表于 2007-11-1 09:13 | 只看该作者十四、日志

14.1 mysql的host_name.err 和oracle的alter

sid.log

mysql错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。参数log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。默认是安装目录的host_name.err。

alterSID.log记录了系统内部错误(Ora-600)；数据块损坏(Ora-1578,Ora-1498)；影响数据库结构，初始化参数等的操作记录；Instance启动时记录所有非默认值的初始化参数；（如果初始化参数log_checkpoints_to_alert设置为true，则还会记录checkpoit的开始和结束时间，未完成的checkpoint，归档日志需要的时间，实例恢复的开始和结束时间等）存放的位置由BACKGROUND_DUMP_DEST参数决定，默认的位置是$ORACLE_HOME/rdbms/log目录。

毫无疑问，如果数据库出现了问题，第一时间就是查找这2个日志的报错信息。

14.2 mysql通用查询日志、二进制日志和oracle重做日志、归档日志。

通用查询日志log [= file]把所有的连接以及所有的SQL命令记入日志，按照它接收的顺序记录语句到查询日志。; 如果没有给出file参数，MySQL将在数据库目录里创建一个hostname.log文件作为这种日志文件(hostname是服务器的主机名)。比如：
733089920 Connect xxxxxx@10.0.0.71 on
733089920 Init DB xxxxxx
733089920 Query SET NAMES 'UTF8'
733089920 Query UPDATE user SET last_activity='1193363367' ,last_login='1193363367',last_ip='116.224.7.249' WHERE uid='682743'
733089921 Connect xxxxxx@10.0.0.72 on
733089921 Init DB xxxxxx
733089921 Query SET NAMES 'UTF8'
733089921 Query UPDATE user_search_m SET children='0',comp lete='35' WHERE uid='10663691'
733089921 Quit

二进制日志log-bin [= filename] 把对数据进行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命令)以二进制格式记入日志(二进制变更日志，binary update log)。这种日志的文件名是filename.n或默认的hostname.n，其中n是一个6位数字的整数(日志文件按顺序编号)。
max_binlog_size = n 二进制日志文件的最大长度(默认设置是1GB)。在前一个二进制日志文件里的信息量超过这个最大长度之前，MySQL服务器会自动提供一个新的二进制日志文件接续上。
binlog-do-db = dbname 只把给定数据库里的变化情况记入二进制日志文件，其他数据库里的变化情况不记载。如果需要记载多个数据库里的变化情况，就必须在配置文件使用多个本选项来设置，每个数据库一行。
binlog-ignore-db = dbname 不把给定数据库里的变化情况记入二进制日志文件。

mysql> show variables where Variable_n

ame like 'log%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log | ON |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
+---------------------------------+-------+
7 rows in set (0.00 sec)

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句，你应使用一般查询日志。二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。

Oracle通过Redo来保证数据库的事务可以被重演，从而使得在故障之后，数据可以被恢复。
通过select * from v$logfile查看路径，缺省情况下，数据库创建时会建立3个日志组。
oracle>select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- -------------- -------------- ----------------------------------------
1 STALE ONLINE /u01/app/oracle/oradata/boss/redo01.log
2 ONLINE /u01/app/oracle/oradata/boss/redo02.log
3 ONLINE /u01/app/oracle/oradata/boss/redo03.log

当一个日志文件写满之后，会切换到另外一个日志文件，这个切换过程称为Log Switch。Log Switch会触发一个检查点，促使DBWR进程将写满的日志文件保护的变更数据写回到数据库。在检查点完成之前，日志文件是不能够被重用的。

在线重做日志大小毕竟是有限的，当都写满了的时候，就面临着2个选择，第一个就是把以前在线重做日志从头擦除开始继续写，第二种就是把以前的在线重做日志先进行备份，然后对被备份的日志擦除开始写新的在线Redo File。这种备份的在线重做日志就是归档日志。而数据库如果采用这种生成归档日志的模式的话，就是归档日志模式（ARCHIVELOG模式），反之如果不生成归档日志，就是非归档日志模式（NOARCHIVELOG模式）。
oracle>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/9.2.0.4/dbs/arch
Oldest online log sequence 310
Current log sequence 312
使用归档模式,后台进程ARCH会将重做日志的内容保存到归档日志中.当数据库出现介质失败时,使用数据文件备份,归档日志和重做日志可以完全恢复数据库.

参数LOG_ARCHIVE_DEST配置归档位置。
oracle>show parameter LOG_ARCHIVE_DEST;

NAME TYPE

VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest string

mysql二进制日志和oracle重做日志、归档日志的作用都是用来保证数据库的事务可以被重演，从而使得在故障之后，数据可以被恢复。它们都可以用自己的工具进行挖掘，比如mysql的mysqlbinlog、oracle的logminer。至于mysql的查询日志，个人感觉含义不太大，有点类似oracle的审计，但是没有审计这样灵活，而且执行操作的时间也无法确定。

14.3 mysql慢速查询日志和oracle跟踪日志

用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。获得初使表锁定的时间不算作执行时间。语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。
mysql> show variables where Variable_name like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+------------------+-------+
2 rows in set (0.00 sec)

例如：
# User@Host: xxxxxxx @ [10.0.0.21]
# Query_time: 3 Lock_time: 0 Rows_sent: 8 Rows_examined: 8
SELECT pid,status,hidden FROM user_photo_f WHERE uid='2388090' AND status<2;

oracle每一个服务进程、后台进程都有一个与之相关的跟踪调试文件（trace file）。当进程检查出一个内部错误时，就将错误信息导出到她的跟踪调试文件中。跟踪调试文件中的一些信息供数据库管理员使用，还有些是供Oracle技术支持（Oracle Support Services）使用的。跟踪调试文件的内容还可以被用做应用与实例的调优。

当然，也可以根据需要，对sql进行跟踪，例如设置参数oracle_trace_enable = TRUE，或者使用dbms_system.set_sql_trace_in_session包。但需要使用tkprf工具对跟踪文件进行分析。

oracle>conn mytest/mytest
Connected.

用SYS用户连接，另外打开一个窗口
oracle>conn / as sysdba
Connected.

oracle>select b.spid,a.sid,a.serial#,https://www.360docs.net/doc/a07277079.html,ername,a.machine from v$session a,v$process b where a.paddr = b.addr;

SPID SID SERIAL# USERNAME MACHINE
------------------------ ---------- ---------- ------------------------------ ------------------------------
4200 1 1 https://www.360docs.net/doc/a07277079.html,
4202 2 1 https://www.360docs.net/doc/a07277079.html,
4204 3 1 https://www.360docs.net/doc/a07277079.html,
4206 4 1 https://www.360docs.net/doc/a07277079.html,
4208 5 1 https://www.360docs.net/doc/a07277079.html,
4210

6 1 https://www.360docs.net/doc/a07277079.html,
4212 7 1 https://www.360docs.net/doc/a07277079.html,
30641 18 2806 BOSSCENTER WORKGROUP\EC9266D1AF614BD
30775 19 703 SYS https://www.360docs.net/doc/a07277079.html,
30839 21 293 MYTEST https://www.360docs.net/doc/a07277079.html,

10 rows selected.

oracle>exec dbms_system.set_sql_trace_in_session(21,293,true);
PL/SQL procedure successfully completed.

回到mytest窗口
oracle>conn mytest/mytest
Connected.
oracle>select * from emp;

oracle>select * from emp where empno=1;

返回sys窗口
oracle>exec dbms_system.set_sql_trace_in_session(21,293,false);
PL/SQL procedure successfully completed.

查看生产trace文件目录
oracle>select value from v$parameter where name = 'user_dump_dest';

VALUE
--------------------
/u01/app/oracle/admin/boss/udump

会生成以刚才SPID命名的跟踪文件
[oracle@oracle udump]$tkprof boss_ora_30839.trc mytest.sql explain=mytest/mytest@boss sys=no;

[oracle@oracle udump]$ cat mytest.sql

TKPROF: Release 9.2.0.4.0 - Production on Mon Oct 29 13:46:36 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: boss_ora_30807.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

select *
from
emp

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 4 0 15

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (MYTEST)

Rows Row Source Operation
------- ---------------------------------------------------
15 TABLE ACCESS FULL EMP (cr=4 r=0 w=0 time=145 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
15 TABLE ACCESS

GOAL: ANALYZED (FULL) OF 'EMP'

********************************************************************************

select *
from
emp where empno=1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (MYTEST)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'EMP'
0 INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE)

可以看到刚才执行的2条语句的情况都记录了。

mysql的慢查询日志和oracle的跟踪日志，目的都是为了查找有问题的语句，快速定位问题，相对来说mysql设置比较简单，但是如果long_query_time参数设置不好，将给数据库带来巨大压力。相比之下，oracle的跟踪文件比较灵活，当然，如果你的系统有问题，后台的跟踪文件也会不断产生，给数据库带来巨大压力。

14.4 日志文件维护

mysql必须定期清理这些文件，确保日志不会占用太多的硬盘空间，特别是32位的操作系统有2G的文件限制（一般日志也不会这样大）。

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

把当前日志备份，清空日志。

https://www.360docs.net/doc/a07277079.html,