常见数据库知识总结 + 30个数据库常见面试题讲解

常见数据库知识总结
MYAQL：
事务：事务是并发控制的基本单元，事务是一个操作序列，要么都执行，要么都不执行，他是一个不可分割的工作单位，事务是维护数据库一致性的单位。

四个ACID基本性质：

1.原子性：要么都执行，要么都不执行。
2.一致性：合法的数据才可以被写入。
3.隔离性：允许多个用户并发访问。
4.持久性：事务结束后，事务处理的结果必须得到固化。即一旦提交，对数据库改变是永久的。

事物的语句：

1.开始事务：BEGIN TRANSACTION
2.提交事务：COMMIT TRANSACTION
3.回滚事务：ROLLBACK TRANSACTION

慢查询：

1，开启慢查询，可以让mysql记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好地优化数据库系统的性能。
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志的存放位置
long_query_time查询超过多少秒才记录

2，可以通过设置全局变量的方法设定：
例如：set gloable slow_query_long on 开启慢查询状态
service mysqld restart 即可

3，查询对应值：
show variables like'slow_query%';
show variables like 'long_query_time';

4，测试慢查询是否正确开启：
select sleep(2); 执行慢查询语句，查看是否有对应的慢查询日志生成。

常见的sql语句：（sql语句中大小写均可执行，规范是大写）

1，说明：创建数据库
create database westos；
2，说明：删除数据库
drop database westos;
3，说明：查询user表中的Host,User,Password信息
select host,user,password from user;
4，说明：查询user表的数据结构
desc user;
5，说明：新建数据库westos中的表格
use westos;
create table linux(
username varchar(50) not null,
password varchar(50) noe null
);
6，说明：在linux表格中插入信息
insert into linux values('xyy','123');
insert into 表名(字段名...) select 字段名 from 查询表名;
7，说明：修改linux表格中的信息
update linux set password '456' where username='xyy';
8，说明：在linux表格中添加age字段
alter table linux add age varchar(4) after username;
9，说明：在linux表格中移除age字段
alter table linux drop age;
10，说明：修改表格名字
alter table linux rename haha;
11，说明：删除表格中的某一行
delete from haha where username='xyy';
12，说明：删除表格
drop table haha;
13, 说明: 清除表中数据
truncate table 表名;
delete from 表名;

数据库的锁机制：

数据库的大并发要考虑锁和锁的性能问题，加锁是为了实现并发控制。数据库是一个多用户资源，
若对并发控制不加控制会读取和存储不正确的数据，破坏数据的一致性（脏读，不可重复读，幻读等）可能会产生死锁。
锁机制保证在一个事务释放锁之前其他事务不可以进行修改。
锁：行级锁，表级锁，悲观锁，乐观锁
悲观锁：事务每次操作之前假设有其他事务会修改需访问的数据，会要求上锁。
乐观锁：事务每次操作之前假设没有其他事务会修改需访问的数据，不会要求上锁。

共享锁：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源
共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的
事务只能读数据，不能修改数据。
排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务
既能读数据，又能修改数据。
共享锁下其它用户可以并发读取，查询数据。但不能修改，增加，删除数据。资源共享。

mysql两种引擎的区别：

Innodb引擎：提供了对数据库ACID事务的支持，并且实现了sql标准的四种隔离级别，事务安全的，支持行级锁，不支持全文索引。
MyIASM引擎：mysql的默认引擎，没有提供对数据库事务的支持，非事务安全的，锁的粒度是表级的，支持全文索引类型，相对简单性能优。
总结：
MYIASM管理非事务表，提供高速存储和检索，以及全文搜索能力，如果在应用中执行大量的select操作，应选择MYIASM引擎
Innodb用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量的insert和update操作，应选择innodb引擎。

四种隔离级别：

1，未提交读：读数据时不会检查使用任何锁。
2，已提交读：只读取提交的数据并等待其他事务释放锁。
3，可重复读：会保持共享锁到事务结束。
4，可序列化：不仅会锁定影响的数据，还会锁定这个范围

mysql的四种日志：

1.错误日志：Error Log.记录mysql运行过程ERROR，WARING等信息，系统出错或某条记录出问题可查看ERROR日志。
2.日常运行日志：General query log记录mysql中每条请求数据。
3.二进制日志：Binary log，包含一些事件，数据库的改动等。
4.慢查询日志：slow query log，用于mysql的性能调优。

mysql优化：

1.定期分析检查表：
analyze table tbl_name;
check table tbl_name;
2.定期优化表：
optimize table tbl_name;

SQL语句：group by

通过一定的规则将一个数据集划分成若干个小区域，然后针对若干个小区域进行数据处理。
例：分别计算男女性用户平均年龄：
select 性别，avg（年龄） as average from 表名 group by 性别

关系型数据库的三大范式：

第一范式：只要是关系型数据库的表，都满足第一范式。
性质：第一范式的数据表中的所有字段都是单一属性，不可分割。
第二范式：不可使用组合键，确保唯一主键。
第三范式：要求数据表中不存在非关键字段对任一候选关键字段的传递函数依赖，表分开。

mysql主从复制：

mysql主从复制的原理就是把主服务器上的bin日志复制到从服务器上执行一遍，这样从服务器上的数据就和
主服务器上的数据一致。编辑/etc/my.cnf文件确保server_id不同即可。
GTID强化了数据库的主备一致性，故障恢复，容错能力。
主库数据丢失---->使用半同步复制
mysql主从复制开启了IO线程和SQL线程。

mysql同步复制，异步复制，半同步复制的原理

在mysql5.5版本之前，复制都是异步复制。 该复制经常遇到的问题是：因为binlog日志是推送的，所有主库和从库
之间存在一定的延迟。 这样就会造成很多问题，比如主库因为磁盘损坏等故障突然崩掉，导致binlog日志不存在，
同时因为延迟binlog还没有推送到从库，从库也就会丢失很多被主库提交的事物，从而造成主从不一致。
解决如上的问题，mysql5.5版本之后引入了半同步复制机制。
异步复制：主库写入一个事务commit提交并执行完之后，，将日志记录到binlog，将结果反馈给客户端，
最后将日志传输到从库。

半同步复制：主库写入一个事务commit提交并执行完之后，并不直接将请求反馈给前端应用用户，而是等待从库也接收
到binlog日志并成功写入中继日志后，主库才返回commit操作成功给客户端。半同步复制保障了事物执行后，至少有两份
日志记录，一份在主库的binlog上 ，另一份至少在从库的中继日志Relay log上，这样就极大的保证了数据的一致性。

同步复制：指的是客户端连接到MySQL主服务器写入一段数据,MySQL主服务器同步给MySQL从服务器需要等待从服务器
发出同步完成的响应才返回客户端OK, 这其中等待同步的过程是阻塞的, 如果有N台从服务器, 效率极低。
异步复制: 指的是客户端连接到MySQL主服务器写入一段数据,MySQL主服务器将写入的数据发送给MySQL从服务器, 然后
直接返回客户端OK, 可能从服务器的数据会和主服务不一致。
半同步复制:指的是客户端连接到MySQL主服务器写入一段数据, MySQL主服务器只将数据同步复制给其中一台从服务器,
半同步复制给其他的从服务器, 来达到其中一台从服务器完全同步的效果。

MHA主从原理，选举机制：

MHA目的在于维持master库的高可用性，最大特点是可以修复多个slave之间的差异日志，最终使所有的slave
保持一致，然后从中选择一个新的充当新的master，并使其他的slave指向它。

热备份和冷备份的区别：

1.冷备份是发生在数据库正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将要害性文件拷贝
到另一个位置的一种说法。
2.热备份是在数据库运行的情况下，采用archivelog mode方式来备份数据库的方法。

---

1、触发器的作用？
触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

2、什么是存储过程？用什么来调用？
存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。 调用： 1）可以用一个命令对象来调用存储过程。 2）可以供外部程序调用，比如：java程序。

3、存储过程的优缺点？
优点： 1）存储过程是预编译过的，执行效率高。 2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。 3）安全性高，执行存储过程需要有一定权限的用户。 4）存储过程可以重复使用，可减少数据库开发人员的工作量。 缺点：移植性差

4、存储过程与函数的区别
存储过程 函数
用于在数据库中完成特定的操作或者任务（如插入、删除等） 用于特定的数据（如选择）
程序头部声明用procedure 程序头部声明用function
程序头部声明时不需描述返回类型 程序头部声明时要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句
可以使用in/out/in out 三种模式的参数 可以使用in/out/in out 三种模式的参数
可作为一个独立的PL/SQL语句来执行 不能独立执行，必须作为表达式的一部分调用
可以通过out/in out 返回零个或多个值 通过return语句返回一个值，且改值要与声明部分一致，也可以是通过out类型的参数带出的变量
SQL语句(DML 或SELECT)中不可调用存储过程 SQL语句(DML 或SELECT)中可以调用函数
5、索引的作用？和它的优点缺点是什么？
索引就一种特殊的查询表，数据库的搜索可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

6、什么样的字段适合建索引
唯一、不为空、经常被查询的字段

7、索引类型有哪些？
逻辑上：
Single column 单行索引
Concatenated 多行索引
Unique 唯一索引
NonUnique 非唯一索引
Function-based 函数索引
Domain 域索引 物理上：
Partitioned 分区索引
NonPartitioned 非分区索引
B-tree ：
Normal 正常型B树
Rever Key 反转型B树 Bitmap 位图索引

8、什么是事务？什么是锁？
事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。 锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

9、什么叫视图？游标是什么？
视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。 游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

10、视图的优缺点
优点： 1）对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。 2 ）用户通过简单的查询可以从复杂查询中得到结果。 3 ）维护数据的独立性，试图可从多个表检索数据。 4 ）对于相同的数据可产生不同的视图。 缺点： 性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据

11、列举几种表连接方式,有什么区别？
内连接、自连接、外连接（左、右、全）、交叉连接 内连接：只有两个元素表相匹配的才能在结果集中显示。 外连接： 左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 全外连接：连接的表中不匹配的数据全部会显示出来。 交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

12、主键和外键的区别？
主键在本表中是唯一的、不可为空的，外键可以重复可以为空；外键和另一张表的主键关联，不能创建对应表中不存在的外键。

13、在数据库中查询语句速度很慢，如何优化？
1.建索引 2.减少表之间的关联 3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,小表在前原则，把数据量小的表排在前面 4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据 5.尽量用PreparedStatement来查询，不要用Statement

14、数据库三范式是什么?
第一范式：列不可再分 第二范式：行可以唯一区分，主键约束 第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束 且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上

15、union和union all有什么不同?
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。 UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

16、Varchar2和varchar有什么区别？
Char的长度是固定的，而varchar2的长度是可以变化的，比如，存储字符串“abc”对于char(20)，表示你存储的字符将占20个字节，包含17个空，而同样的varchar2（20）只占了3个字节，20只是最大值，当你存储的字符小于20时，按实际长度存储。 char的效率要被varchar2的效率高。 目前varchar是varchar2的同义词，工业标准的varchar类型可以存储空字符串，但是oracle不能这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型varchar2,这个类型不是一个标准的varchar，他将在数据库中varchar列可以存储空字符串的特性改为存储null值，如果你想有向后兼容的能力，oracle建议使用varchar2而不是varchar

17、Oracle和Mysql的区别？
1）库函数不同。 2）Oracle是用表空间来管理的，Mysql不是。 3）显示当前所有的表、用户、改变连接用户、显示当前连接用户、执行外部脚本的语句的不同。 4）分页查询时候时候，mysql用limit oracle用rownum

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. //如果只给定一个参数，它表示返回最大的记录行数目： mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行 //换句话说，LIMIT n 等价于 LIMIT 0,n。 5）sql的语法的不同。

18、Oracle语句有多少类型
Oracle语句分三类：DDL、DML、DCL。 DDL（Data Definition Language）数据定义语言，包括： Create语句：可以创建数据库和数据库的一些对象。 Drop语句：可以删除数据表、索引、触发程序、条件约束以及数据表的权限等。 Alter语句：修改数据表定义及属性。 Truncate语句：删除表中的所有记录,包括所有空间分配的记录被删除。 DML（Data Manipulation Language）数据操控语言，包括： Insert语句：向数据表张插入一条记录。 Delete语句：删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是它的操作对象仍是记录。 Update语句：用于修改已存在表中的记录的内容。 DCL（Data Control Language）数据库控制语言，包括： Grant语句：允许对象的创建者给某用户或某组或所有用户（PUBLIC）某些特定的权限。 Revoke语句：可以废除某用户或某组或所有用户访问权限
19、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)
20、从数据库中随机取50条
select * from (select * from t_example order by dbms_random.random) where rownum <= 50
21、order by与group by的区别
order by 排序查询、asc升序、desc降序 group by 分组查询、having 只能用于group by子句、作用于组内，having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。
22、commit在哪里会运用
oracle的commit就是DML语句提交数据（这里是释放锁不是锁表），在未提交前你前面的操作更新的都是内存，没有更新到物理文件中。 执行commit从用户角度讲就是更新到物理文件了，事实上commit时还没有写date file，而是记录了redo log file，要从内存写到data物理文件，需要触发检查点，由DBWR这个后台进程来写，这里内容有点多的，如果不深究的话你就理解成commit即为从内存更新到物理文件。
23、行转列、列换行怎么转
1）使用decode函数 2）使用case when语句
24、什么是PL/SQL？
PL/SQL是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。
25、序列的作用
Oracle使用序列来生成唯一编号，用来处理一个表中自增字段。 Oracle序列是原子对象，并且是一致的。也就是说，一旦您访问一个序列号，Oracle将在处理下一个请求之前自动递增下一个编号，从而确保不会出现重复值。
26、表和视图的关系
视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。 表就是关系数据库中实际存储数据用的。
27、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
28、truncate与 delete区别
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 TRUNCATE,DELETE,DROP 放在一起比较：
TRUNCATE TABLE ：删除内容、释放空间但不删除定义。
DELETE TABLE: 删除内容不删除定义，不释放空间。
DROP TABLE ：删除内容和定义，释放空间。
29、oracle获取系统时间
select to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss') from dual;
30、oracle怎么去除去重
使用distinct关键字

---

1. SQL语言包括哪些类型？

数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index

数据操纵：Select ,insert,update,delete

数据控制：grant,revoke

【GRANT】是授权语句， 将对某个表的操作权限授予某个用户
举例：
将对学生表Student的插入权限授予用户U1：
GRANT insert
on table Student
to U1;

【REVOKE】就是收回权限，值得注意的是收回权限的时候注意是不是级联的收回
举例：
将U1对Student的插入权限收回
REVOKE insert
on table Student
from U1;
其中，GRANT语句和REVOKE语句是相对的

2. 内联接,外联接区别？

内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。

在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种。

等连接（内连接）、非等连接、自连接、外连接（左、右、全） Or hash join/merge join/nest loop(cluster join)/index join

数据库中的内连接、自然连接、外连接

数据中的连接join分为内连接、自然连接、外连接，外连接又分为左外连接、右外连接、全外连接。

当然，这些分类都是在连接的基础上，是从两个表中记录的笛卡尔积中选取满足连接的记录。笛卡尔积简单的说就是一个表里的记录要分别和另外一个表的记录匹配为一条记录，即如果表1有2条记录，表2也有2条记录，经过笛卡尔运算之后就应该有2*2即4条记录。如下表：

自然连接（natural join）
自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。
sql语句：Select …… from 表1 natural join 表2
结果：

内连接（inner join）
内连接基本与自然连接相同，不同之处在于自然连接奥球是同名属性列的比较，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。
sql语句：Select …… from 表1 inner join 表 2 on 表1.A=表2.E
结果：

自然连接时某些属性值不同则会导致这些元组会被舍弃，那如何保存这些会被丢失的信息呢，外连接就解决了相应的问题。外连接分为左外连接、右外连接、全外连接。外连接必须用using或on指定连接条件。

左外连接（left outer join)
左外连接是在两表进行自然连接，只把左表要舍弃的保留在结果集中，右表对应的列上填null。
sql语句：Select …… from 表1 left outer join 表2 on 表1.C=表2.C
结果：

右外连接(rignt outer join)
右外连接是在两表进行自然连接，只把右表要舍弃的保留在结果集中，左表对应的列上填null。
Select …… from 表1 rignt outer join 表2 on 表1.C=表2.C
结果：

全外连接(full join)
全外连接是在两表进行自然连接，只把左表和右表要舍弃的都保留在结果集中，相对应的列上填null。
Select …… from 表1 full join 表2 on 表1.C=表2.C
结果：

1)内连接:只连接匹配的行 select A.c1,B.c2 from A join B on A.c3 = B.c3;

2)左外连接:包含左边表的全部行（不管右边的表中是否存在与它们匹配的行）以及右边表中全部匹配的行 select A.c1,B.c2 from A left join B on A.c3 = B.c3;

3)右外连接:包含右边表的全部行（不管左边的表中是否存在与它们匹配的行）以及左边表中全部匹配的行 select A.c1,B.c2 from A right join B on A.c3 = B.c3;

4)全外连接:包含左、右两个表的全部行，不管在另一边的表中是否存在与它们匹配的行 select A.c1,B.c2 from A full join B on A.c3 = B.c3;

5)（theta）连接:使用等值以外的条件来匹配左、右两个表中的行 select A.c1,B.c2 from A join B on A.c3 != B.c3;

6)交叉连接:生成笛卡尔积——它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行一一匹配 select A.c1,B.c2 from A,B;

3. 什么是存储过程？用什么来调用？

存储过程是一个预编译的SQL语句， 简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程（CALL）。

4．触发器的作用？

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

5. 索引的作用？和它的优点缺点是什么？

索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

6. 什么是内存泄漏？

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

7. 如何维护数据库的完整性和一致性？

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

8. 什么是事务？什么是锁？

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将一组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

锁：在所有的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。共享锁（只读不写）、排他锁（可读可写）

9. 事务的隔离级别有哪些？

事务隔离级别包括： 原子性，即不可分割性，事务要么全部被执行，要么就全部不被执行； 一致性或可串性，事务的执行使得数据库从一种正确状态转换成另一种正确状态； 隔离性，在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务； 持久性,事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

10. 什么叫视图？游标是什么？

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

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。游标用于定位结果集的行，通过判断全局变量@@FETCH_STATUS可以判断是否到了最后，通常此变量不等于0表示出错或到了最后。

11. 什么是主键?什么是外键?

主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。

主键在本表中是唯一的、不可为空的，外键可以重复可以为空；外键和另一张表的主键关联，不能创建对应表中不存在的外键。

12. 对一个投入使用的在线事务处理表格有过多索引需要有什么样的性能考虑?

对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。

13、可以用什么来确保表格里的字段只接受特定范围里的值?

Check限制，它在数据库表格里被定义，用来限制输入该列的值。 触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。因此，微软建议使用Check限制而不是其他的方式来限制域的完整性。

14. 什么是相关子查询?如何使用这些查询?

经验更加丰富的开发人员将能够准确地描述这种类型的查询。 相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。

15. 使用索引查询一定能提高查询的性能吗？为什么？

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况: 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%宜采用; 基于非唯一性索引的检索 索引就是为了提高查询性能而存在的,如果在查询中索引没有提高性能,只能说是用错了索引,或者讲是场合不同

16. 查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不连续增长的列， 查询语句？

select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by ID ) T) order by ID

1

select top 10 * from A where (id not in(select top 30(id) from A as T order by id)) order by id

1

2

3

4

5

SELECT TOP 10 * FROM (

SELECT TOP 40 * FROM tableName

ORDER BY ID DESC

) T

ORDER BY ID

17. 分页查询语句?

（1）mysql的分页查询

?

1

select o.* from (sql) o limit firstIndex,pageSize

（2）sqlserver2005的分页查询

?

1

select top pageSize o.* from (select row_number() over(order by orderColumn) as rownumber,* from(sql) as o where rownumber>firstIndex;

（3）oracle分页查询

?

1

select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex

18. 如何优化数据库，如何提高数据库的性能？

1)给数据库做索引，合理的索引能立即显著地提高数据库整个系统的性能。

2)在适当的情况下，尽可能的用存储过程而不是SQL查询。因为前者已经过了预编译，运行速度更快。

3)优化查询语句，通过高性能的查询语句提高数据库的性能。

19、说出一些数据库优化方面的经验?

用PreparedStatement 一般来说比Statement性能高。

有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。

根据扫描的原理，第一条子查询语句要比第二条关联查询的效率高：

1)select e.name,e.salary where e.managerid=(select id from employee where name='zxx');

2)select e.name,e.salary,m.name,m.salary from employees e,employees m where e.managerid = m.id and m.name='zxx';

表中允许适当冗余。如，主题帖的回复数量和最后回复时间等

将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例。

sql语句全部大写，特别是列名和表名都大写。特别是sql命令的缓存功能，更加需要统一大小写，sql语句?发给oracle服务器?语法检查和编译成为内部指令?缓存和执行指令。根据缓存的特点，不要拼凑条件，而是用?和PreparedStatment

还有索引对查询性能的改进也是值得关注的。

20. 表与表之间的关联关系

分为3种：一对一、一对多、多对多。

21. 事前触发和事后触发有何区别？语句级触发和行级触发有何区别？

事前触发器运行于触发事件发生之前，而事后触发器运行于触发事件发生之后。通常事前触发器可以获取事件之前和新的字段值。语句级触发器可以在语句执行前或后执行，而行级触发在触发器所影响的每一行触发一次。

22. 数据库设计的必要性及设计步骤

好的数据库结构有利于：节省数据的存储空间，能够保证数据的完整性，方便进行数据库应用系统的开发

设计不好的数据库结构将导致：数据冗余、存储空间浪费和内存空间浪费

不管数据库的大小和复杂程度如何，可以用下列基本步骤来设计数据库：收集信息--标识对象--设计数据模型--标识每个对象--存储的信息类型–标识对象之间的关系

23. 什么是数据模型？什么是规范化？

数据模型是一种标识实体类型及其实体间联系的模型。典型的数据模型有网状模型、层次模型和关系模型。

从关系数据库的表中，除去冗余数据的过程称为规范化。包括：精简数据库的结构，从表中删除冗余的列，标识所有依赖于其它数据的数据

24. 谈谈数据库设计的三范式

第一范式的定义：如果一个表中没有重复组（即行与列的交叉点上只有一个值，而不是一组值），则这个表属于第一范式（常记成1NF）。简而言之："每一字段只存储一个值"。例如:职工号，姓名，电话号码组成一个表（一个人可能有一个办公室电话 和一个家里电话号码）

第二范式的定义：如果一个表属于1NF，任何属性只依赖于关键字，则这个表属于第二范式（常记成2NF ）。简而言之：必须先符合1NF的条件，且每一行都能被唯一的识别。将1NF转换成2NF的方法是添加主键。例如：学号，姓名，课程名，成绩

第三范式的定义：如果一个表属于2NF，且不包含传递依赖性，则这个表是第三范式（常记成 3NF）。满足3NF的表中不包含传递依赖。简而言之：没有一个非关键属性依赖于另一个非关键属性。例如：表一：学号，课程号，成绩。 表二：学号，姓名，所在系，系名称，系地址。表三：课程号，课程名，学分

25. union和union all有什么不同?

Union和Union All的区别之一在于对重复结果的处理。 UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如： select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。 而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL

26. 设计数据库应注意那些问题？

(1)原始单据与实体之间的关系

可以是一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对应且只对应一个实体。在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单证对应多个实体，或多张原始单证对应一个实体。这里的实体可以理解为基本表。明确这种对应关系后，对我们设计录入界面大有好处。

〖例1〗：一份员工履历资料，在人力资源信息系统中，就对应三个基本表：员工基本情况表、社会关系表、工作简历表。这就是“一张原始单证对应多个实体”的典型例子。

(2)主键与外键

一般而言，一个实体不能既无主键又无外键。在E—R 图中, 处于叶子部位的实体, 可以定义主键，也可以不定义主键(因为它无子孙), 但必须要有外键(因为它有父亲)。主键与外键的设计，在全局数据库的设计中，占有重要地位。当全局数据库的设计完成以后，有个美国数据库设计专家说：“键，到处都是键，除了键之外，什么也没有”，这就是他的数据库设计经验之谈，也反映了他对信息系统核心(数据模型)的高度抽象思想。因为：主键是实体的高度抽象，主键与

外键的配对，表示实体之间的连接。

(3)基本表的性质

基本表与中间表、临时表不同，因为它具有如下四个特性：

1)原子性。基本表中的字段是不可再分解的。

2)原始性。基本表中的记录是原始数据(基础数据)的记录。

3)演绎性。由基本表与代码表中的数据，可以派生出所有的输出数据。

4)稳定性。基本表的结构是相对稳定的，表中的记录是要长期保存的。

理解基本表的性质后，在设计数据库时，就能将基本表与中间表、临时表区分开来。

(4)范式标准

基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。

〖例2〗：有一张存放商品的基本表，如表1所示。“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

在Rose 2002中，规定列有两种类型：数据列和计算列。“金额”这样的列被称为“计算列”，而“单价”和“数量”这样的列被称为“数据列”。

表1 商品表的表结构

商品名称 商品型号 单价 数量 金额

电视机 29吋 2,500 40 100,000

(5)通俗地理解三个范式

通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解;

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性;

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运

行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式

，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

(6)要善于识别与正确处理多对多的关系

若两个实体之间存在多对多的关系，则应消除这种关系。消除的办法是，在两者之间增加第三个实体。这样，原来一个多对多的关系，现在变为两个一对多的关系。要将原来两个实体的属性合理地分配到三个实体中去。这里的第三个实体，实质上是一个较复杂的关系，它对应一张基本表。一般来讲，数据库设计工具不能识别多对多的关系，但能处理多对多的关系。

〖例3〗：在“图书馆信息系统”中，“图书”是一个实体，“读者”也是一个实体。这两个实体之间的关系，是一个典型的多对多关系：一本图书在不同时间可以被多个读者借阅，一个读者又可以借多本图书。为此，要在二者之间增加第三个实体，该实体取名为“借还书”，它的属性为：借还时间、借还标志(0表示借书，1表示还书)，另外，它还应该有两个外键(“图书”的主键，“读者”的主键)，使它能与“图书”和“读者”连接。

(7)主键PK的取值方法

PK是供程序员使用的表间连接工具，可以是一无物理意义的数字串, 由程序自动加1来实现。也可以是有物理意义的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时，建议字段的个数不要太多，多了不但索引占用空间大，而且速度也慢。

(8)正确认识数据冗余

主键与外键在多表中的重复出现, 不属于数据冗余，这个概念必须清楚，事实上有许多人还不清楚。非键字段的重复出现, 才是数据冗余!而且是一种低级冗余，即重复性的冗余。高级冗余不是字段的重复出现，而是字段的派生出现。

〖例4〗：商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出来的，它就是冗余，而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性，因为同一数据，可能从不同时间、地点、角色上多次录入。因此，我们提倡高级冗余(派生性冗余)，反对低级冗余(重复性冗余)。

(9)E--R图没有标准答案

信息系统的E--R图没有标准答案，因为它的设计与画法不是惟一的，只要它覆盖了系统需求的业务范围和功能内容，就是可行的。反之要修改E--R图。尽管它没有惟一的标准答案，并不意味着可以随意设计。好的E—R图的标准是：结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余。

(10) 视图技术在数据库设计中很有用

与基本表、代码表、中间表不同，视图是一种虚表，它依赖数据源的实表而存在。视图是供程序员使用数据库的一个窗口，是基表数据综合的一种形式, 是数据处理的一种方法，是用户数据保密的一种手段。为了进行复杂处理、提高运算速度和节省存储空间, 视图的定义深度一般不得超过三层。 若三层视图仍不够用, 则应在视图上定义临时表, 在临时表上再定义视图。这样反复交迭定义, 视图的深度就不受限制了。

对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统，视图的作用更加重要。这些系统的基本表完成物理设计之后，立即在基本表上建立第一层视图，这层视图的个数和结构，与基本表的个数和结构是完全相同。并且规定，所有的程序员，一律只准在视图上操作。只有数据库管理员，带着多个人员共同掌握的“安全钥匙”，才能直接在基本表上操作。请读者想想：这是为什么?

(11)中间表、报表和临时表

中间表是存放统计数据的表，它是为数据仓库、输出报表或查询结果而设计的，有时它没有主键与外键(数据仓库除外)。临时表是程序员个人设计的，存放临时记录，为个人所用。基表和中间表由DBA维护，临时表由程序员自己用程序自动维护。

(12) 完整性约束表现在三个方面

域的完整性：用Check来实现约束，在数据库设计工具中，对字段的取值范围进行定义时，有一个Check按钮，通过它定义字段的值城。

参照完整性：用PK、FK、表级触发器来实现。

用户定义完整性：它是一些业务规则，用存储过程和触发器来实现。

(13) 防止数据库设计打补丁的方法是“三少原则”

1. 一个数据库中表的个数越少越好。只有表的个数少了，才能说明系统的E--R图少而精，去掉了

重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计;

2)一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的

外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间;

3. 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一

个子表。这个方法很简单，有的人就是不习惯、不采纳、不执行。

数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念，综合观点，不能孤立某一个原则。该原则是相对的，不是绝对的。“三多”原则肯定是错误的。试想：若覆盖系统同样的功能，一百个实体(共一千个属性) 的E--R图，肯定比二百个实体(共二千个属性)

的E--R图，要好得多。

提倡“三少”原则，是叫读者学会利用数据库设计技术进行系统的数据集成。数据集成的步骤是将文件系统集成为应用数据库，将应用数据库集成为主题数据库，将主题数据库集成为全局综合数据库。集成的程度越高，数据共享性就越强，信息孤岛现象就越少，整个企业信息系统的全局E—R图中实体的

个数、主键的个数、属性的个数就会越少。

提倡“三少”原则的目的，是防止读者利用打补丁技术，不断地对数据库进行增删改，使企业数据库变成了随意设计数据库表的“垃圾堆”，或数据库表的“大杂院”，最后造成数据库中的基本表、代码表、中间表、临时表杂乱无章，不计其数，导致企事业单位的信息系统无法维护而瘫痪。

“三多”原则任何人都可以做到，该原则是“打补丁方法”设计数据库的歪理学说。“三少”原则是少而精的原则，它要求有较高的数据库设计技巧与艺术，不是任何人都能做到的，因为该原则是杜绝用“打补丁方法”设计数据库的理论依据。

(14) 提高数据库运行效率的办法

在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：

1)在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。

2. 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。

3)发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。

4)对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。

5. 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。

总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。
————————————————
版权声明：本文为CSDN博主「网络笨猪」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/lyfqyr/article/details/83351594