mysql和oracle

Oracle

1,rowid, rownum的定义 
   1. rowid和rownum都是虚列 
   2. rowid是物理地址，用于定位oracle中具体数据的物理存储位置 
   3. rownum则是sql的输出结果排序，从下面的例子可以看出其中的区别。 

 2,Oracle是怎样分页的？

     Oracle中使用rownum来进行分页, 这个是效率最好的分页方法，hibernate也是使用rownum来进行oralce分页的 
      select * from   ( select rownum r,a from tabName where rownum <= 20 ) where r > 10 

  3,Oralce怎样存储文件，能够存储哪些文件？

      Oracle 能存储 clob、nclob、 blob、 bfile 
      Clob  可变长度的字符型数据，也就是其他数据库中提到的文本型数据类型 
      Nclob 可变字符类型的数据，不过其存储的是Unicode字符集的字符数据 
      Blob  可变长度的二进制数据 
      Bfile  数据库外面存储的可变二进制数据

 4,Oracle中where条件查询和排序的性能比较？

     Order by使用索引的条件极为严格，只有满足如下情况才可以使用索引， 
     1). order by中的列必须包含相同的索引并且索引顺序和排序顺序一致 
     2). 不能有null值的列 
          所以排序的性能往往并不高，所以建议尽量避免order by

  5, 解释冷备份和热备份的不同点以及各自的优点？

       冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法 
      热备份是在数据库运行的情况下，采用归档方式备份数据的方法 
        冷备的优缺点： 
             1)．是非常快速的备份方法（只需拷贝文件）  
              2)．容易归档（简单拷贝即可）  
              3)．容易恢复到某个时间点上（只需将文件再拷贝回去）  
              4)．能与归档方法相结合，作数据库“最新状态”的恢复。  
              5)．低度维护，高度安全。  
        冷备份不足：  
             1)．单独使用时，只能提供到“某一时间点上”的恢复。  
             2)．在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。  
              3)．若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。  
              4)．不能按表或按用户恢复。  

        热备的优缺点 
              1)．可在表空间或数据文件级备份，备份时间短。  
              2)．备份时数据库仍可使用。  
              3)．可达到秒级恢复（恢复到某一时间点上）。  
              4)．可对几乎所有数据库实体作恢复。  
              5)．恢复是快速的，在大多数情况下在数据库仍工作时恢复。  
       热备份的不足是：  
              1)．不能出错，否则后果严重。  
              2)．若热备份不成功，所得结果不可用于时间点的恢复。  
              3)．因难于维护，所以要特别仔细小心，不允许“以失败而告终”

   6,解释data block , extent 和 segment的区别？           

                data block 数据块，是oracle最小的逻辑单位，通常oracle从磁盘读写的就是块 
                extent 区，是由若干个相邻的block组成 
                segment段，是有一组区组成 
                tablespace表空间，数据库中数据逻辑存储的地方，一个tablespace可以包含多个数据文件 

    7,比较truncate和delete命令 ？                 

            1). Truncate 和delete都可以将数据实体删掉，truncate 的操作并不记录到 rollback日志，所以操作速度较快，但同时这个数据不能恢复 
            2). Delete操作不腾出表空间的空间 
            3). Truncate 不能对视图等进行删除 
            4). Truncate是数据定义语言（DDL），而delete是数据操纵语言(DML) 

       8,解释什么是死锁，如何解决Oracle中的死锁？            

            简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行 
            解决办法： 
           1). 查找出被锁的表 
            select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;   
           select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time; 
          2). 杀进程中的会话 
           alter system kill session "sid,serial#"; 

      9,简述oracle中 dml、ddl、dcl的使用          

          Dml 数据操纵语言，如select、update、delete，insert 
          Ddl 数据定义语言，如create table 、drop table 等等 
          Dcl 数据控制语言， 如 commit、 rollback、grant、 invoke等 

      10,说说oracle中的经常使用到得函数 

          Length 长度、 lower 小写、upper 大写， to_date 转化日期， to_char转化字符 Ltrim 去左边空格、 rtrim去右边空格，substr取字串，add_month增加或者减掉月份、to_number转变为数字

      11,. 怎样创建一个存储过程, 游标在存储过程怎么使用, 有什么好处?             
           1 .使用游标可以执行多个不相关的操作.如果希望当产生了结果集后,对结果集中的数据进行多种不相关的数据操作 
           2. 使用游标可以提供脚本的可读性 
           3. 使用游标可以建立命令字符串,使用游标可以传送表名,或者把变量传送到参数中,以便建立可以执行的命令字符串. 
           但是个人认为游标操作效率不太高，并且使用时要特别小心，使用完后要及时关闭 
           存储过程优缺点： 
           优点： 
         1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。 
         2. 可保证数据的安全性和完整性。 
         3． 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。 
                通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。 
         3. 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 
         4. 可以降低网络的通信量, 不需要通过网络来传送很多sql语句到数据库服务器了 
         5. 使体现企业规则的运算程序放入数据库服务器中，以便集中控制 
       当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的 运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。 
             缺点： 
              1. 可移植性差 
              2. 占用服务器端多的资源，对服务器造成很大的压力 
              3. 可读性和可维护性不好 

      12,怎样创建一个一个索引,索引使用的原则,有什么优点和缺点              

              创建标准索引： 
             CREATE  INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 
             创建唯一索引: 
             CREATE unique INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 
             创建组合索引: 
             CREATE INDEX 索引名 ON 表名 (列名1,列名2)  TABLESPACE 表空间名; 
             创建反向键索引: 
             CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名; 
             索引使用原则： 
             索引字段建议建立NOT NULL约束 
             经常与其他表进行连接的表，在连接字段上应该建立索引； 
             经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引； 
             可选择性高的关键字 ，应该建立索引； 
              可选择性低的关键字，但数据的值分布差异很大时，选择性数据比较少时仍然可以利用索引提高效率 
              复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： 
             A、正确选择复合索引中的第一个字段，一般是选择性较好的且在where子句中常用的字段上； 
             B、复合索引的几个字段经常同时以AND方式出现在Where子句中可以建立复合索引；否则单字段索引； 
             C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； 
              D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； 
              E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 
             频繁DML的表，不要建立太多的索引； 
             不要将那些频繁修改的列作为索引列； 
             索引的优缺点： 
                   优点： 
                  1. 创建唯一性索引，保证数据库表中每一行数据的唯一性 
                  2. 大大加快数据的检索速度，这也是创建索引的最主要的原因 
                  3. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
                   . 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
             缺点： 
                   1. 索引创建在表上，不能创建在视图上 
                   2. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 
                   3. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大 
                   4. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度 

      13，怎样创建一个视图,视图的好处, 视图可以控制权限吗? 

                  create view 视图名 as select 列名 [别名]  …  from 表 [unio [all] select … ] ] 
                  好处： 
                 1. 可以简单的将视图理解为sql查询语句，视图最大的好处是不占系统空间 
                 2. 一些安全性很高的系统，不会公布系统的表结构，可能会使用视图将一些敏感信息过虑或者重命名后公布结构 
                 3. 简化查询 
                    可以控制权限的，在使用的时候需要将视图的使用权限grant给用户 

      14，怎样优化Oracle数据库，有几种方式？ 

                 数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化， 物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化 
                物理优化的一些原则： 
               1). Oracle的运行环境（网络，硬件等） 
               2). 使用合适的优化器 
               3). 合理配置oracle实例参数 
               4). 建立合适的索引（减少IO） 
               5). 将索引数据和表数据分开在不同的表空间上（降低IO冲突） 
               6). 建立表分区，将数据分别存储在不同的分区上（以空间换取时间，减少IO） 
               逻辑上优化： 
               1). 可以对表进行逻辑分割，如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用 
               2). Sql语句使用占位符语句，并且开发时候必须按照规定编写sql语句（如全部大写，全部小写等）oracle解析语句后会放置到共享池中 
              如： select * from Emp where name=?  这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好 
              3). 数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能 
              4). 尽量不使用*号，如select * from Emp，因为要转化为具体的列名是要查数据字典，比较耗时 
              5). 选择有效的表名 
                  对于多表连接查询，可能oracle的优化器并不会优化到这个程度， oracle 中多表查询是根据FROM字句从右到左的数据进行的，那么最好右边的表（也就是基础表）选择数据较少的表，这样排序更快速，如果有link表（多对多中间表），那么将link表放最右边作为基础表，在默认情况下oracle会自动优化，但是如果配置了优化器的情况下，可能不会自动优化，所以平时最好能按照这个方式编写sql 
              6). Where字句 规则 
                 Oracle 中Where字句时从右往左处理的，表之间的连接写在其他条件之前，能过滤掉非常多的数据的条件，放在where的末尾， 另外!=符号比较的列将不使用索引，列经过了计算（如变大写等）不会使用索引（需要建立起函数）， is null、is not null等优化器不会使用索引 
              7). 使用Exits Not Exits 替代 In  Not in 
              8). 合理使用事务，合理设置事务隔离性 
               数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数

 

MySQL

   1, 数据库事务的四个特性及含义

         数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
         原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
        一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
        隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。
         持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

   2,视图的作用，视图可以更改么？

          视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

    3,drop,delete与truncate的区别        

     drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

    （1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

   （2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

   （3） 一般而言，drop > truncate > delete

   （4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

  （5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

   （6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

   （7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

   （8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

   （9） 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

   （10） Truncate table 表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

  （11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

  （12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

      4,连接的种类

         左连接：left join 或 left outer join

         左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。

          右连接：right join 或 right outer join

          右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

           完整外部联接:full join 或 full outer join

             完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

        内连接：join 或 inner join

            交叉连接：cross join (不带条件where...)

            没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）

     5,数据库优化的思路       

               1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
               2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
                        select id from t where num is null
                        可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
                        select id from t where num=0
               3）很多时候用 exists 代替 in 是一个好的选择
               4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

     6,MySQL中myisam与innodb的区别，至少5点

          1.InnoDB支持事物，而MyISAM不支持事物

          2.InnoDB支持行级锁，而MyISAM支持表级锁

          3.InnoDB支持MVCC, 而MyISAM不支持

          4.InnoDB支持外键，而MyISAM不支持

          5.InnoDB不支持全文索引，而MyISAM支持。
          6.InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器， myisam 支持
          7.InnoDB表支持多种行格式， myisam 不支持
          8.InnoDB是索引组织表， myisam 是堆表