数据库常见面试知识

一。SQL语句优化的策略都有哪些：

1.建表的时候。应尽量建立主键,根据主键查询数据；
2.大数据表删除，用truncate table代替delete。
3.合理使用索引，在OLTP应用中一张表的索引不要太多。组合索引的列顺序尽

量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，

以减少失效的索引和碎片。
4.查询尽量用确定的列名，少用*号
5.使用where条件命中索引
6.将能过滤大量数据的where条件放到where条件集合的最前门
7.对于大量的字符匹配，要使用exist，用in拼接的话容易造成sql被无端截取

二。数据库表关联：

分了内连接和外连接。内连接也就是相等连接
外连接分为左外连接和右外连接。

1. LEFT OUTER JOIN：左外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+);

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的

员工记录
2. RIGHT OUTER JOIN：右外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id;

结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

3. FULL OUTER JOIN：全外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
结果为：所有员工及对应部门的记录，包括没有对应部门编号 department_id

的员工记录和没有任何员工的部门记录。


9. 事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。事务性质：

 原子性。即不可分割性，事务要么全部被执行，要么就全部不被执行。

 一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确

状态

 隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任

何其他事务，

 持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交

后有了其他故障，事务的处理结果也会得到保存。


存储过程：存储过程就是编译好了的一些sql语句。因为SQL语句已经预编绎过

了，因此运行的速度比较快。

GRANT (Select,delete,…) ON (对象) TO USER_NAME [WITH GRANT

OPTION];//授权

REVOKE (权限表) ON(对象) FROM USER_NAME [WITH REVOKE OPTION] //撤权
Create UNIQUE INDEX index_name ON TableName(col_name);//建索引

INSERT INTO tablename {column1,column2,…} values(exp1,exp2,…);//插

入

INSERT INTO Viewname {column1,column2,…} values(exp1,exp2,…);//插入

视图实际影响表

UPDATE tablename SET name=’zang 3’，email=‘wew’ condition;//更新

数据


数据库索引类似于书的目录，主要用于提高查询效率，也就是按条件查询的时

候，先查询索引，再通过索引找到相关的数据，索引相当于记录了对某个关键

词，指定到不同的文件，或者文件里的不同位置，当然索引自身也是通过文件

来保存的


加了索引后，也不能保证在每次检索时都会使用列索引。
如果， SQL 检索语句编写不当，就无法使用索引。
1、LIKE 运算符，进行模糊检索时，只能在前方一致的检索时，才能使用索引

。
以下写法，索引不会被使用。
[sql]
SELECT * FROM employee WHERE lname_pinyin LIKE '%w%'; 
SELECT * FROM employee WHERE lname_pinyin LIKE '%w'; 

2、使用 IS NOT NULL 、 <> 的场合，也不会使用索引。如下：
[sql]
SELECT * FROM employee WHERE lname_pinyin IS NOT NULL; 
SELECT * FROM employee WHERE lname_pinyin <> 'wang'; 

3、对列使用了运算或者函数的情况下，不会使用索引，如下：
[sql]
SELECT * FROM employee WHERE YEAR(birth) = '1980'; -- 不使用索引 
SELECT * FROM employee WHERE birth >= '1980-01-01' AND birth <=

'1980-12-31'; --使用了索引 

4、复合索引的第一列，没有包含在 WHERE 条件语句中，如下：
[sql]
CREATE INDEX idx_pinyin ON employee(lname_pinyin, fname_pinyin); 
SELECT * FROM employee WHERE lname_pinyin = 'wang' AND fname_pinyin =

'xiao'; --用了索引 
SELECT * FROM employee WHERE lname_pinyin = 'wang'; --用了索引 
 
SELECT * FROM employee WHERE fname_pinyin = 'xiao'; -- 没有使用索引 
SELECT * FROM employee WHERE lname_pinyin = 'wang' OR fname_pinyin =

'xiao'; -- 没有使用索引

复合索引

[sql]
CREATE INDEX idx_pinyin ON employee( lname_pinyin, fname_pinyin); 
 
SHOW INDEX FROM employee\G 

唯一性索引

使用 UNIQUE 关键字，来创建不可重复的索引，称为：唯一性索引。
对特定列创建唯一性索引，相当于对该列追加了唯一性制约。
创建唯一性索引的时候，如果对象列中，已经含有重复数据，则：创建失败，

报错。
创建成功后，如果，插入重复数据，则：插入失败，报错。
指定多个列，来创建唯一性索引，只要，这些列的组合数据不重复，就可以创

建成功。

索引，顾名思义，是为了检索的便捷性。因为索引需要单独去维护，所以对于

数据库的删除，插入，更新操作有影响，批量的操作，速度就会很慢，感觉就

会很明显。


对于in，rule优化器选择的内查询的结果作为驱动表来进行nest loops连接，

所以当内查询的结果集比较小的时候，这个in的效率还是比较高的。
对于exists，则是利用外查询表的全表扫描结果集过滤内查询的结果集，当外

查询的表比较大的时候，相对效率比较低