oracle储存过程a表和b表_将a表值插入到b表面试题,数据库面试题

什么是事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

数据库ACID

1. 原子性(Atomicity)原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。

回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

2. 一致性(Consistency)事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性。

3. 隔离性(Isolation)隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

4. 持久性(Durability)一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

1可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

只有满足一致性，事务的执行结果才是正确的。

在无并发的情况下，事务串行执行，隔离性一定能够满足。此时要只要能满足原子性，就一定能满足一致性。

在并发的情况下，多个事务并发执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。

事务满足持久化是为了能应对数据库奔溃的情况。

事务隔离级别

1. 串行化 (Serializable)

所有事务一个接着一个的执行，这样可以避免幻读 (phantom read)，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询的时候，需要获取范围锁，如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需回滚该事务。

2. 可重复读 (Repeated Read)

所有被 Select 获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，即前一个事务有读锁但是没有范围锁，为什么叫做可重复读等级呢？那是因为该等级解决了下面的不可重复读问题。

引申：现在主流数据库都使用 MVCC 并发控制，使用之后RR(可重复读)隔离级别下是不会出现幻读的现象。

3. 读已提交 (Read Committed)

被读取的数据可以被其他事务修改，这样可能导致不可重复读。也就是说，事务读取的时候获取读锁，但是在读完之后立即释放(不需要等事务结束)，而写锁则是事务提交之后才释放，释放读锁之后，就可能被其他事务修改数据。该等级也是 SQL Server 默认的隔离等级。

4. 读未提交 (Read Uncommitted)

最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。

总结

四个级别逐渐增强，每个级别解决一个问题，每个级别解决一个问题，事务级别遇到，性能越差，大多数环境(Read committed 就可以用了)

隔离级别

脏读

不可重复读

幻影读

未提交读

√

√

√

提交读

×

√

√

可重复读

×

×

√

可串行化

×

×

×

存储引擎

对于初学者来说我们通常不关注存储引擎，但是 MySQL 提供了多个存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

简介

MySQL 中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

在MySQL中有很多存储引擎，每种存储引擎大相径庭，那么又改如何选择呢？

MySQL 5.5 以前的默认存储引擎是 MyISAM, MySQL 5.5 之后的默认存储引擎是 InnoDB

不同存储引起都有各自的特点，为适应不同的需求，需要选择不同的存储引擎，所以首先考虑这些存储引擎各自的功能和兼容。

1. MyISAM

MySQL 5.5 版本之前的默认存储引擎，在 5.0 以前最大表存储空间最大 4G，5.0 以后最大 256TB。

Myisam 存储引擎由 .myd(数据)和 .myi(索引文件)组成，.frm文件存储表结构(所以存储引擎都有)

特性

并发性和锁级别 (对于读写混合的操作不好，为表级锁，写入和读互斥)

表损坏修复

Myisam 表支持的索引类型(全文索引)

Myisam 支持表压缩(压缩后，此表为只读，不可以写入。使用 myisampack 压缩)

应用场景

没有事务

只读类应用(插入不频繁，查询非常频繁)

空间类应用(唯一支持空间函数的引擎)

做很多 count 的计算

2. InnoDB

MySQL 5.5 及之后版本的默认存储引擎

特性

InnoDB为事务性存储引擎

完全支持事物的 ACID 特性

Redo log (实现事务的持久性) 和 Undo log(为了实现事务的原子性，存储未完成事务log，用于回滚)

InnoDB支持行级锁

行级锁可以最大程度的支持并发

行级锁是由存储引擎层实现的

应用场景

可靠性要求比较高，或者要求事务

表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

3. CSV

文件系统存储特点

数据以文本方式存储在文件中

.csv文件存储表内容

.csm文件存储表的元数据，如表状态和数据量

.frm存储表的结构

CSV存储引擎特点

以 CSV 格式进行数据存储

所有列必须都是不能为 NULL

不支持索引

可以对数据文件直接编辑(其他引擎是二进制存储，不可编辑)

引用场景

作为数据交换的中间表

4. Archive

特性

以 zlib 对表数据进行压缩，磁盘 I/O 更少

数据存储在ARZ为后缀的文件中(表文件为 a.arz，a.frm)

只支持 insert 和 select 操作(不可以 delete 和 update，会提示没有这个功能)

只允许在自增ID列上加索引

应用场景

日志和数据采集类应用

5. Memory

特性

也称为 HEAP 存储引擎，所以数据保存在内存中(数据库重启后会导致数据丢失)

支持 HASH 索引(等值查找应选择 HASH)和 BTree 索引(范围查找应选择)

所有字段都为固定长度，varchar(10) == char(10)

不支持 BLOG 和 TEXT 等大字段

Memory 存储使用表级锁(性能可能不如 innodb)

最大大小由 max_heap_table_size 参数决定

Memory存储引擎默认表大小只有 16M，可以通过调整 max_heap_table_size 参数

应用场景

用于查找或是映射表，例如右边和地区的对应表

用于保存数据分析中产生的中间表

用于缓存周期性聚合数据的结果表

注意： Memory 数据易丢失，所以要求数据可再生

6. Federated

特性

提供了访问远程 MySQL 服务器上表的方法

本地不存储数据，数据全部放在远程服务器上

使用 Federated

默认是禁止的。如果需要启用，需要在启动时增加Federated参数

问：独立表空间和系统表空间应该如何抉择

两者比较

系统表空间：无法简单的收缩大小(这很恐怖，会导致 ibdata1 一直增大，即使删除了数据也不会变小)

独立表空间：可以通过 optimize table 命令收缩系统文件

系统表空间：会产生I/O瓶颈(因为只有一个文件)

独立表空间：可以向多个文件刷新数据

总结 强烈建议：对Innodb引擎使用独立表空间(mysql5.6版本以后默认是独立表空间)

系统表转移为独立表的步骤(非常繁琐)

使用 mysqldump 导出所有数据库表数据

停止 mysql 服务，修改参数，并且删除Innodb相关文件

重启 mysql 服务，重建mysql系统表空间

重新导入数据

问：如何选择存储引擎

参考条件：

是否需要事务

是否可以热备份

崩溃恢复

存储引擎的特有特性

重要一点： 不要混合使用存储引擎 强烈推荐： Innodb

问：MyISAM和InnoDB引擎的区别

区别：

MyISAM 不支持外键，而 InnoDB 支持

MyISAM 是非事务安全型的，而 InnoDB 是事务安全型的。

MyISAM 锁的粒度是表级，而 InnoDB 支持行级锁定。

MyISAM 支持全文类型索引，而 InnoDB 不支持全文索引。

MyISAM 相对简单，所以在效率上要优于 InnoDB，小型应用可以考虑使用 MyISAM。

MyISAM 表是保存成文件的形式，在跨平台的数据转移中使用 MyISAM 存储会省去不少的麻烦。

InnoDB 表比 MyISAM 表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表(alter table tablename type=innodb)。

应用场景：

MyISAM 管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择。

InnoDB 用于事务处理应用程序，具有众多特性，包括 ACID 事务支持。如果应用中需要执行大量的 INSERT 或 UPDATE 操作，则应该使用 InnoDB，这样可以提高多用户并发操作的性能。

问：为什么不建议 InnoDB 使用亿级大表

索引

索引分类特性

说明

InnoDB

MyISAM

MEMORY

B树索引 (B-tree indexes)

自增ID物理连续性更高， 二叉树，红黑树高度不可控

√

√

√

R树索引 (R-tree indexes)

空间索引

√

哈希索引 (Hash indexes)

无法做范围查询

√

√

全文索引 (Full-text indexes)

√

√

B+Tree 索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

哈希索引

InnoDB 引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE

空间数据索引(R-Tree)

MyISAM 存储引擎支持空间数据索引，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

mysql中的索引类型有哪些，可以从哪些角度来看？

从数据结构角度

1、B+树索引(O(log(n)))：关于B+树索引，可以参考 MySQL索引背后的数据结构及算法原理

2、hash索引：

a 仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询

b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引

c 只有Memory存储引擎显示支持hash索引

3、FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了)

4、R-Tree索引(用于对GIS数据类型创建SPATIAL索引)

从物理存储角度

1、聚集索引(clustered index)聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个

2、非聚集索引(non-clustered index) 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值

2、普通索引或者单列索引 : 即一个索引只包含单个列，一个表可以有多个单列索引

3、多列索引(复合索引)：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

4、唯一索引或者非唯一索引

5、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

索引的缺点创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大

当对表中的数据进行增加、删除和修改的时候，索引也需要维护，降低数据维护的速度

索引的优点创建唯一性索引，保证数据库表中每一行数据的唯一性

大大加快数据的检索速度，这是创建索引的最主要的原因

加速数据库表之间的连接，特别是在实现数据的参考完整性方面特别有意义

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能

在什么情况下适合建立索引为经常出现在关键字order by、group by、distinct后面的字段，建立索引。

在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。

为经常用作查询选择 where 后的字段，建立索引。

在经常用作表连接 join 的属性上，建立索引。

考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

索引失效如果MySQL估计使用全表扫秒比使用索引快，则不适用索引。

例如，如果列key均匀分布在1和100之间，下面的查询使用索引就不是很好：select * from table_name where key>1 and key<90;

如果条件中有or，即使其中有条件带索引也不会使用

例如：select * from table_name where key1=’a’ or key2=’b’;如果在key1上有索引而在key2上没有索引，则该查询也不会走索引

复合索引，如果索引列不是复合索引的第一部分，则不使用索引(即不符合最左前缀)

例如，复合索引为(key1,key2),则查询select * from table_name where key2=’b’;将不会使用索引

如果like是以 % 开始的，则该列上的索引不会被使用。

例如select * from table_name where key1 like ‘%a’；该查询即使key1上存在索引，也不会被使用如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引

如果列为字符串，则where条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用。

例如,select * from table_name where key1=1;如果key1列保存的是字符串，即使key1上有索引，也不会被使用。

如果使用MEMORY/HEAP表，并且where条件中不使用“=”进行索引列，那么不会用到索引，head表只有在“=”的条件下才会使用索引

左连接和右连接区别左连接where只影向右表，右连接where只影响左表。

MySQL 主从复制原理主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，将主库的 binlog 日志拷贝到自己本地，写入一个 relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog 日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。

Mysql

Mysql 的分页 SQL 语句

select * from tablename limit m,n(n是指从第m+1条开始，取n条)

下面提供几个查询优化的建议。

使用explain分析查询语句

前面已经演示过如何使用explain命令分析查询语句了，这里再解释一下其中几个有参考价值的字段的含义：

select_type

select_type表示查询中每个select子句的类型，一般有下面几个值:

simple 简单SELECT,不使用UNION或子查询等。

primary 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY。

union union中的第二个或后面的SELECT语句。

dependent union union 中的第二个或后面的SELECT语句，取决于外面的查询。

union result union 的结果。

subquery子查询中的第一个SELECT。

dependent subquery子查询中的第一个SELECT，取决于外面的查询。

derived派生表的SELECT, FROM子句的子查询。

uncacheable subquery一个子查询的结果不能被缓存，必须重新评估外链接的第一行。

type

type表示MySQL在表中找到所需行的方式，又称“访问类型”，常用的类型有：

ALL, index, range, ref, eq_ref, const, system, NULL。

从左到右，性能从差到好。

ALL： Full Table Scan，MySQL将遍历全表以找到匹配的行。

index: Full Index Scan，index与ALL区别为index类型只遍历索引树。

range: 只检索给定范围的行，使用一个索引来选择行。

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。

const: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。 如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

Key

key列显示MySQL实际决定使用的键(索引)，如果没有选择索引，键是NULL。

possible_keys

possible_keys指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上如果存在索引则该索引将被列出，但不一定被查询使用。

ref

ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows

rows表示MySQL根据表统计信息，以及索引选用的情况，找到所需记录需要读取的行数。这个行数是估算的值，实际行数可能不同。

声明NOT NULL

当数据列被声明为NOT NULL以后，在查询的时候就不需要判断是否为NULL，由于减少了判断，可以降低复杂性，提高查询速度。

如果要表示数据列为空，可以使用0等代替。

考虑使用数值类型代替字符串

MySQL对数值类型的处理速度要远远快于字符串，而且数值类型往往更加节省空间。

例如对于“Male”和“Female”可以用“0”和“1”进行代替。

考虑使用ENUM类型

如果你的数据列的取值是确定有限的，可以使用ENUM类型代替字符串。因为MySQL会把这些值表示为一系列对应的数字，这样处理的速度会提高很多。

1

2

3

4

5

6

7

8

9CREATE TABLE shirts (

name VARCHAR(40),

size ENUM('x-small', 'small', 'medium', 'large', 'x-large')

);

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),

('polo shirt','small');

SELECT name, size FROM shirts WHERE size = 'medium';

总结

索引是一个单独的，存储在磁盘上的数据结构，索引对数据表中一列或者多列值进行排序，索引包含着对数据表中所有数据的引用指针。

本教程从MySQL开始讲起，又介绍了MySQL中索引的使用，最后提供了使用索引的几条原则和优化查询的几个方法。

Oracle

oracle中row_id理解

ORACLE的row_id是一个伪列,其个是为18个字节可将这18个字节用6363来划分,分别表示段编号,数据文件编号,数据块

嵌入式数据库和传统数据库的区别

嵌入式数据库主要像：SQLite、

传统数据库服务器：SQL Server、Oracle、MySQL

嵌入式数据库：SQLite的主要特点：

支持事件，不需要配置，不需要安装，也不需要管理员；

支持大部分SQL92；

一个完整的数据库保存在磁盘上面一个文件，同一个数据库文件可以在不同机器上面使用，最大支持数据库到2T，字符和BLOB的支持仅限制于可用内存；

整个系统少于3万行代码，少于250KB的内存占用(gcc)，大部分应用比目前常见的客户端/服务端的数据库快，没有其它依赖

源代码开放，代码95%有较好的注释，简单易用的API。官方带有TCL的编译版本。

关系数据库特点：

更好的安全性、多用户管理

强大的数据管理能力，如索引、视图等关系对象

强大的数据库编程式的设计，像T-SQL、存储过程、游标

丰富的数据类型

Inserted和deleted的含义

inserted表反映插入或更新操作时插入的记录

deleted表反映删除或更新操作时删除的记录

函数和过程的区别

存储过程：

一般用于在数据库中完成特定的业务或任务

可以定义返回类型，也可以不定义返回类型

SQL语句中不可以调用

函数：

一般用于特定的数据查询或数据转转换处理

申请时必须要定义返回类型，且程序体中必须定义return语句

不能独立执行，必须作为表达式的一部分调用

SQL语句中可以调用

数据库优化的方案

建立主键，为数据库创建索引，建立存储过程，触发器，可提高查询速度。

Oracle中有哪几种索引单列索引与复合索引：一个索引可以由一个或多个列组成，用来创建索引的列被称为“索引列”。单列索引是基于单列所创建的索引，复合索引是基于两列或者多列所创建的索引。

唯一索引与非唯一索引：唯一索引是索引列值不能重复的索引，非唯一索引是索引列可以重复的索引。无论是唯一索引还是非唯一索引，索引列都允许取NULL值。默认情况下，Oracle创建的索引是不唯一索引。

B树索引：B树索引是按B树算法组织并存放索引数据的，所以B树索引主要依赖其组织并存放索引数据的算法来实现快速检索功能。

位图索引：位图索引在多列查询时，可以对两个列上的位图进行AND和OR操作，达到更好的查询效果。

函数索引：Oracle中不仅能够直接对表中的列创建索引，还可以对包含列的函数或表达式创建索引，这种索引称为“位图索引

数据库索引的优点和缺点

优点：

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

触发器有几种

共2种，一种DML触发，就是遇到DML事件时触发执行，像insert\update\delete。一种DDL触发，遇到DDL事件时触发，像Login Datatabase、更改数据库状态、create语句等。

oracle中除了数据库备份，还有什么方法备份

Oracle数据库有三种标准的备份方法，它们分别是导出/导入(EXP/IMP)、热备份和冷备份。导出备份是一种逻辑备份，冷备份和热备份是物理备份。

10G有几种新功能进行备份，像数据磅

写出删除表中重复记录的语句oracle1

2delete from people

where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by