存储过程

1、什么是存储过程

存储过程是一个预编译的SQL语句；是由SQL语句组成的代码块，允许模块化的设计；只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

2、存储过程的调用

（1）可以用一个命令对象来调用存储过程；

（2）可以供外部程序调用，比如：java程序。

3、存储过程的优缺点

优点：

（1）存储过程是预编译过的，执行效率高；

（2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯；

（3）安全性高，执行存储过程需要有一定权限的用户；

（4）存储过程可以重复使用，可减少数据库开发人员的工作量。

 缺点：移植性差

4、存储过程与函数的区别

（1）存储过程用于在数据库中完成特定的操作或者任务（如插入、删除等）；函数用于特定的数据（如选择）；

（2）存储过程程序头部声明用procedure；函数程序头部声明用function；

（3）存储过程程序头部声明时不需描述返回类型； 函数程序头部声明时要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句；

（4）存储过程可作为一个独立的PL/SQL语句来执行；函数不能独立执行，必须作为表达式的一部分调用；

（5）都可以使用in/out/in out 三种模式的参数；存储过程可以通过out/in out 返回零个或多个值；函数通过return语句返回一个值，且该值要与声明部分一致，也可以是通过out类型的参数带出的变量；

（6）SQL语句(DML 或SELECT)中不可调用存储过程；SQL语句(DML 或SELECT)中可以调用函数。

索引

1、什么是索引

（1）索引就一种特殊的查询表，数据库的搜索可以利用它加速对数据的检索。

（2）索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构；

2、索引的作用

它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。

3、索引的优缺点

优点：

（1）加快数据库的检索速度；

（2）降低了插入、删除、修改等维护任务的速度；

（3）唯一索引可以确保每一行数据的唯一性；

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

缺点：

索引需要占物理和数据空间，即减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

4、什么样的字段适合建索引？

唯一、不为空、经常被查询的字段。

5、什么情况下用或不用索引？

如果一张表更多是用于查询而很少插入，那么就可以建立尽量多的索引以优化查询性能。相反如果一张表要经常插入或更新，则尽可能少用索引，有时甚至连主键都不建。

6、索引的类型

MySQL中基本的索引类型：普通索引、唯一索引、主键索引、全文索引。

其他分类：

（1）逻辑上：

Single column 单行索引

Concatenated 多行索引

Unique 唯一索引

NonUnique 非唯一索引

Function-based 函数索引

Domain 域索引

（2）物理上：

Partitioned 分区索引

NonPartitioned 非分区索引

（3）B-tree ：

Normal 正常型B树

Rever Key 反转型B树

Bitmap 位图索引

触发器

1、什么是触发器？

触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。

2、触发器的作用？

（1）可以强化约束，来维护数据的完整性和一致性；可以跟踪数据库内的操作，从而不允许未经许可的更新和变化。

（2）可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

3、触发器分类

触发时间：有before,after.

触发事件：有insert,update,delete三种

触发类型：有行触发、语句触发

事务和锁

1、什么是事务？

事务（Transaction）是并发控制的基本单位。它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

2、事务的四大特性

一致性、原子性、隔离性、持久性。

3、事务的三个常用命令

Begin Transaction、Commit Transaction、RollBack Transaction。

4、什么是锁？

锁：在DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。

5、乐观锁和悲观锁

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

视图

1、什么叫视图？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。相比多表查询，它使得我们获取数据更容易。

2、视图的优缺点

优点：

（1）视图可以有选择性的选取数据库里的一部分。

（2）用户可以从简单的查询中得到结果。

（3）维护数据的独立性，试图可从多个表检索数据。

（4）对于相同的数据可产生不同的视图。

缺点：

查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么就无法更改数据。

3、表和视图的关系

视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。表就是关系数据库中实际存储数据用的。

游标

1、什么是游标？

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录进行处理的机制。

2、什么时候用游标？

一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

3、游标的使用步骤

（1）定义游标：

declare cursor 游标名称 for  select查询语句 [for {readonly|update}]

（2）打开游标：open cursor

（3）从游标中操作数据：fetch... ...   current of cursor

（4）关闭游标：close cursor

主键和外键

1、键的分类

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

2、主键和外键的区别？

主键在本表中是唯一的、不可为空，外键可以重复可以为空；

外键和另一张表的主键关联，不能创建对应表中不存在的外键。

3、在删除主表记录时，一并删除从表相关联的记录？

如果两张表存在主外键关系，那么在删除主键表的记录时，如果从表有相关联的记录，那么将导致删除失败。

在定义外键约束时，可以同时指定3种删除策略：

一是将从表记录一并删除（级联删除）；

二是将从表记录外键字段设置为NULL；

三是将从表记录外键字段设置为默认值。

级联删除示例：

alter table 从表名

add constraint 外键名

foreign key(字段名) references 主表名(字段名)

on delete cascade

数据库约束

1、什么是数据库约束

数据库约束用于保证数据库表数据的完整性（正确性和一致性，包括实体完整性、参照完整性、用户定义完整性）。可以通过定义约束\索引\触发器来保证数据的完整性。

2、数据库约束的分类

主键约束：primary key；

外键约束：foreign key；

唯一约束：unique；

检查约束：check；

空值约束：not null；

默认值约束：default

3、维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

答：尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

数据库三范式

1、简化版

第一范式：表中每个字段都不能再分。

第二范式：满足第一范式并且表中的非主键字段都依赖于主键字段。

第三范式：满足第二范式并且表中的非主键字段必须不传递依赖于主键字段，即消除传递依赖，方便理解，可以看做是“消除冗余”。

2、详细版

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y。

其他

1、列举几种表连接方式？

（1）内联接（Inner Join）：匹配2张表中相关联的记录。

（2）左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。

（3）右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。

（4）全外连接：连接的表中不匹配的数据全部会显示出来。

（5）交叉连接：笛卡尔效应，显示的结果是链接表数的乘积。

2、union和union all有什么不同?

（1）union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表union。

（2）union all只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

（3）从效率上说，union all 要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all。

3、什么是内存泄漏？

答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

4、如何优化查询语句？

（1）建索引；

（2）减少表之间的关联；

（3）优化sql，尽量让sql很快定位数据，不要让sql做全表查询，使用索引,把数据量大的表排在前面；

（4）简化查询字段，没用的字段不要，尽量返回少量数据；

（5）尽量用PreparedStatement来查询，不要用Statement。

Oracle数据库

1、Oracle基本数据类型

（1）字符串类型 char、nchar、varchar、varchar2、nvarchar2

（2）数字类型 number、integer            

（3）浮点类型 binary_float、binary_double、float

（4）日期类型 date、 timestamp

（5）LOB类型 blob、clob、nclob、bfile

2、Oracle语句类型

Oracle语句分三类：DDL、DML、DCL。

（1）DDL（Data Definition Language）数据定义语言，包括：

Create语句：可以创建数据库和数据库的一些对象。

Drop语句：可以删除数据表、索引、触发程序、条件约束以及数据表的权限等。 Alter语句：修改数据表定义及属性。

Truncate语句：删除表中的所有记录,包括所有空间分配的记录被删除。

（2）DML（Data Manipulation Language）数据操控语言，包括：

Insert语句：向数据表张插入一条记录。

Delete语句：删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是它的操作对象仍是记录。

Update语句：用于修改已存在表中的记录的内容。

（3）DCL（Data Control Language）数据库控制语言，包括：

Grant语句：允许对象的创建者给某用户或某组或所有用户（PUBLIC）某些特定的权限。

Revoke语句：可以废除某用户或某组或所有用户访问权限。

3、char、varchar2、varchar

（1）char的长度是固定的，而varchar2的长度是可以变化的，varchar是varchar2的同义词；char的效率要被varchar2的效率高。

（2）目前，工业标准的varchar类型可以存储空字符串； Oracle自己开发了一个数据类型varchar2,将在数据库中存储空字符串的特性改为存储null值。

4、Oracle和Mysql的区别？

（1）库函数不同。

（2）Oracle是用表空间来管理的，Mysql不是。

（3）显示当前所有的表、用户、改变连接用户、显示当前连接用户、执行外部脚本的语句不同。

（4）分页查询时候时候，Mysql用limit ，Oracle用rownum

5、order by与group by

（1）order by 排序查询，[asc升序、desc降序]

（2）group by 分组查询，having 只能用于group by子句、作用于组内，having条件子句可以直接跟函数表达式。

（3）使用group by 子句的查询语句需要使用聚合函数。

6、commit提交

oracle的commit就是DML语句提交数据，在未提交前的操作更新的都是内存，没有更新到物理文件中。 commit即为从内存更新到物理文件。

7、PL/SQL

PL/SQL是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。

8、序列的作用

Oracle使用序列来生成唯一编号，用来处理一个表中自增字段。

Oracle序列是原子对象，并且是一致的。也就是说，一旦您访问一个序列号，Oracle将在处理下一个请求之前自动递增下一个编号，从而确保不会出现重复值。

9、truncate、 delete、drop

（1）truncate table在功能上与不带 where子句的 delete 语句相同；二者均删除表中的全部行，但 truncate table比 delete 速度快，且使用的系统和事务日志资源少。 delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项；truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（2）truncate，delete，drop 放在一起比较：

truncate table：删除内容、释放空间但不删除定义。

delete table：删除内容不删除定义，不释放空间。

drop table：删除内容和定义，释放空间。

（3）drop、delete与truncate分别在什么场景之下使用？

不再需要一张表的时候，用drop；

想删除部分数据行时候，用delete，并且带上where子句；

保留表而删除所有数据的时候用truncate。

10、Oracle获取系统时间

select to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss') from dual;

11、Oracle怎么去除去重

使用distinct关键字

12、Oracle分页查询语句

使用rownum，两种如下：

第一种： select * from (select t.*,rownum row_num from mytable t) b

where b.row_num between 1 and 10

第二种： select * from ( select a.*, rownum rn from mytable a where rownum <= 10 ) where rn >= 1

使用rowid， 如下：

select * from scott.emp where rowid in (select rd from

(select rowid as rd ,rownum as rn from scott.emp ) where rn<=6 and rn>3)

13、Oracle系统主要进程

数据写进程(DBWR)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件；

日志写进程(LGWR)：将重做日志缓冲区中的更改写入在线重做日志文件；

系统监控  (SMON): 检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复；

进程监控  (PMON): 负责在一个Oracle 进程失败时清理资源；

检查点进程(CKPT)：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。；

归档进程  (ARCH)：在每次日志切换时把已满的日志组进行备份或归档；

恢复进程  (RECO): 保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback；

作业调度器(CJQ ):  负责将调度与执行系统中已定义好的job,完成一些预定义的工作。

14、sql语句1

现有表:A(id ,name,regdate) B(id,groupid) C(id,name2) ,写出下面的SQL语句

（1）统计A表中每个月注册用户数

答：

select count(*),to_char(regdate,'yyyymm') from A group by to_char(regdate,'yyyymm'); （2）统计A表中有姓名相同的用户数

答：select count(*) from (select name from A group by name having count(*) >1);

（3）如果表A中有姓名相同的用户,把相同的查出,写入表C中

答：insert into C(name2) select name from A group by name having count(*) >1;

（4）A中ID有多个相同的数据,A中姓名相同的ID只保留注册时间最大的数据 答：delete from E where e.regdate < (select max(regdate) from a X where E.id = X.id);

15、sql语句2

现有表：Student(S#,SN,SD)学生表， Course(C#,CN,T#)课程表， SC（S#,C#,score）成绩表。

（1）查询选了课程‘税收’的学生的学号和名字

答： select SN,SD from Student where S# in( select S# from Course C , SC where C.C#=SC.C# and CN= ’税收基础’);

（2）查询选课数量大于5的学生的学号和名字

答： select SN,SD from Student where S# in ( select S# from SC group by S# having count(distinct C#) > 5); )

（3）建立一个学生表students，包括name,age,head_teacher,id,score(姓名，年龄，班主任，学号，成绩)

答： Create table students ( Id number(9) not null primary key, Name varchar2(40) not null, Age int check(age between 0 and 100), Head_teacher vachar2(40), Score float );

（4）对上表插入一条记录，姓名：张三，年龄：18，班主任：李四，学号：22 答： Insert into student(id,name,age,head_teacher) values(‘22’,’张三’,’18’,’李四’)； （5）对上表中的age+name创建一个索引，并说明它的作用和使用方法

答： Create index student_index on students(age,name);

 

----------------END-----------------

 

（东流不作西归水，落花辞条羞故林）