通过oracle学mysql

郑全

16.移动数据

    oracle:

             外部表，sqlloader,expdp\exp /impdp/imp

 

    mysql:

             select into outfile / load data [local] infile 对应 sqlloader

            

             下面是例子：

             

select * into outfile '/u01/test/emp2.sql'
   fields terminated by ','
from emp;

[root@sztech1 test]# more emp2.sql
100,smitty,5000,2015-04-05
200,\N,\N,\N
300,\N,\N,\N

 

mysql> desc emp_bak
    -> ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| empid     | int(11)     | YES  |     | NULL    |       |
| last_name | varchar(20) | YES  |     | NULL    |       |
| salary    | double      | YES  |     | NULL    |       |
| hire_date | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

load data infile '/u01/test/emp2.sql'
 into table emp_bak
 fields terminated by ',' ;

         

   

            外部表好像没有看到

            mysqldump 对应 oracle的 exp/imp

            

     

[此贴子已经被作者于2015-04-22 22:54:13编辑过]

bikong123

本来Oracle就具代表性，所以有如此情况！学习的话，还是先学Oracle感觉更有好处！当然直接学MySQL的话要简单些，Oracle难度要大些。

郑全

17.设置日志文件大小

     oracle: 

                创建联机日志文件时，指定大小

                alter database add logfile group xxx ('/home/oracle/redo01.dbf') size 500m;

 

     mysql :

                修改变量来实现：

                innodb_log_file_size=500m

 

 

郑全

18.查看执行计划：

    比如下面这条sql语句：

    SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population, Continent
 FROM Country JOIN City
 ON CountryCode = Code
 GROUP BY Continent
 ORDER BY Population;

 

 

    oracle：

               可以使用

             explain plan

             for

           SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population, Continent
             FROM Country JOIN City
 ON CountryCode = Code
 GROUP BY Continent
 ORDER BY Population;

             然后，使用 select * from table(dbms_xplan.display);进行查看

 

   mysql:

           explain

             SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population, Continent
             FROM Country JOIN City
 ON CountryCode = Code
 GROUP BY Continent
 ORDER BY Population;

           显示：

               +----+-------------+---------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref                | rows | Extra                           |
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
|  1 | SIMPLE      | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  239 | Using temporary; Using filesort |
|  1 | SIMPLE      | City    | ref  | CountryCode   | CountryCode | 3       | world.Country.Code |    9 | Using index                     |
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+---------------------------------+

 

           

登录/注册后可看大图

郑全

19.监控
   oracle: oem
   mysql:monitor