Java面试题-day24 数据库之基础

数据库，又称为数据管理系统，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的资料执行新增、截取、更新、删除等操作[1]。

所谓“数据库”是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。一个数据库由多个表空间（Tablespace）构成。

前言

数据库一般分为关系型数据库和非关系型数据库，而我们日常用的就是关系型数据库，基本上接触到的最多的就是MySQL。

提示：以下是本篇文章正文内容，下面案例可供参考

一、 SQL概念

SQL（Structured Query Language 结构查询语言）是一个功能强大的数据库语言。SQL是关系数据库管理系统的标准语言。
DML（数据操作语言）: 用于检索或修改数据
DDL（数据定义语言）:用于定义数据的结构，创建、修改、删除数据库对象
DCL（数据控制语言）: 用于定义数据库用户的权限

二、常用函数

字符函数
UPPER: 将输入的字符串变为大写返回
Select upper(‘hello’) from dual;  //dual为数据库提供的一张虚拟表
LOWER: 将输入的字符串变为小写返回
Select lower(ename) from emp;
INITCAP: 开头首字母大写
Select initcap(ename) from emp;
LENGTH: 求出字符串的长度
Select ename, length(ename) from emp;
查询出姓名长度为5的雇员信息
Select ename,length(ename) from emp where length(ename)=5;
REPLACE: 字符串进行替换
使用”_”替换姓名中所有字母”A”
Select replace(ename,’A’,’_’) from emp;
SUBSTR: 字符串截取
Select ename,substr(ename,0,3) from emp ;
截取每个雇员姓名的后三个字母
Select ename,substr(ename,length(ename)-2) from emp;
Select ename,substr(ename,-3) from emp;
数字函数
ROUND: 四舍五入的操作
TRUNC: 截取指定位置的内容
MOD: 取模或取余
通用函数
NVL() 处理null
查询出每个雇员的全部年薪
Select ename,sal,comm,(sal+comm)*12 from emp; //此时comm字段有null值
//此时要将null变为0
Select ename,sal,comm,(sal+nvl(comm,0))*12,nvl(comm,0) from emp; 
DECODE() 多数值判断
类似于if…else语，不同的是decode()函数判断的是数值，而不是逻辑条件。
显示全部雇员的职位，但是这些职位要求替换为中文显示
Select empno,ename,job,decode(job,’CLERK’,’办事员’,’SALESMAN’,’销售人员’,’MANAGER’,’经理’ ,’ANALYST’,’分析员’ ,’PRESIDENT’,’总裁’) from emp

三、常用的数据字段(oracle)

每一张数据表实际是由若干字段组成，而每一个字段会有其对应的数据类型。

四、集合操作

在数学的操作中存在交、差、并、补的概念，而在数据的查询中也存在此概念。
UNION 连接两个，相同部分不显示。
UNION ALL连接两个，相同部分显示。
INTERSECT 返回两个查询中相同部分
MINUS 返回两个查询中不同部分

五、视图和游标

视图时一种虚拟表，具有与物理表相同的功能，可对视图增删改查。视图通常是一个或多个表的行或列的子集。对视图的修改不影响基本表。使获取数据更容易，相比多表查询。
游标: 对查询出来的结果集做一个单元来有效处理。游标可定在该单元中的特定行，从结果集的当前行检索一行或多行。可对结果集当前行做修改。
一般不使用游标,但当需逐条处理数据时,游标显得很重要。

六、数据库视图和表的区别，什么是视图？怎么使用视图？物化视图？

视图是种虚表，不是真正的物理表，只是为业务查询方便，将几张物理表虚拟组成一个视图。

第一点：
使用视图，可以定制用户数据，聚焦特定的数据。
解释：
在实际过程中，公司有不同角色的工作人员，我们以销售公司为例的话，
采购人员，可以需要一些与其有关的数据而与他无关的数据，对他没
有任何意义，我们可以根据这一实际情况，专门为采购人员创建一个视
图，以后他在查询数据时，只需select * from view_caigou 就可以啦。

第二点：使用视图，可以简化数据操作。
解释：我们在使用查询时，在很多时候我们要使用聚合函数，同时还要
显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能
会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我
们只需要select * from view1就可以

第三点：使用视图，基表中的数据就有了一定的安全性
因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以
将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集
合，数据是随着基表的更新而更新。同时，用户对视图，不可以随意的更改
和删除，可以保证数据的安全性。

第四点：可以合并分离的数据，创建分区视图
随着社会的发展，公司的业务量的不断的扩大，一个大公司，下属都设有很
多的分公司，为了管理方便，我们需要统一表的结构，定期查看各公司业务
情况，而分别看各个公司的数据很不方便，没有很好的可比性，如果将这些
数据合并为一个表格里，就方便多啦，这时我们就可以使用union关键字，
将各分公司的数据合并为一个视图。

七、数据库的索引有什么用，带来的问题是什么?

1、索引定义

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引是对数据库表中一个或多个列（例如，employee 表的姓氏 (lname) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

2、建立索引的优缺点

优点：
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
　　1.索引需要占用数据表以外的物理存储空间
　　2.创建索引和维护索引要花费一定的时间
　　3.当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。

3、索引类型

根据数据库的功能，可以在数据库设计器中创建索引：唯一索引、主键索引和聚集索引。尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。
唯一索引： UNIQUE
例如：create unique index stusno on student（sno）；
表明此索引的每一个索引值只对应唯一的数据记录，对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

主键索引： primary key
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引（也叫聚簇索引）：cluster
在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

4、索引的实现方式

1、B+树

经常听到B+树就是这个概念，用这个树的目的和红黑树差不多，也是为了尽量保持树的平衡，当然红黑树是二叉树，但B+树就不是二叉树了，节点下面可以有多个子节点，数据库开发商会设置子节点数的一个最大值，这个值不会太小，所以B+树一般来说比较矮胖，而红黑树就比较瘦高了。
关于B+树的插入，删除，会涉及到一些算法以保持树的平衡，这里就不详述了。ORACLE的默认索引就是这种结构的。
如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

2、散列索引

第二种索引叫做散列索引，就是通过散列函数来定位的一种索引，不过很少有单独使用散列索引的，反而是散列文件组织用的比较多。
散列文件组织就是根据一个键通过散列计算把对应的记录都放到同一个槽中，这样的话相同的键值对应的记录就一定是放在同一个文件里了，也就减少了文件读取的次数，提高了效率。
散列索引呢就是根据对应键的散列码来找到最终的索引项的技术，其实和B树就差不多了，也就是一种索引之上的二级辅助索引，我理解散列索引都是二级或更高级的稀疏索引，否则桶就太多了，效率也不会很高。

3、位图索引

位图索引是一种针对多个字段的简单查询设计一种特殊的索引，适用范围比较小，只适用于字段值固定并且值的种类很少的情况，比如性别，只能有男和女，或者级别，状态等等，并且只有在同时对多个这样的字段查询时才能体现出位图的优势。
位图的基本思想就是对每一个条件都用0或者1来表示，如有5条记录，性别分别是男，女，男，男，女，那么如果使用位图索引就会建立两个位图，对应男的10110和对应女的01001,这样做有什么好处呢，就是如果同时对多个这种类型的字段进行and或or查询时，可以使用按位与和按位或来直接得到结果了。
B+树最常用，性能也不差，用于范围查询和单值查询都可以。特别是范围查询，非得用B+树这种顺序的才可以了。
HASH的如果只是对单值查询的话速度会比B+树快一点，但是ORACLE好像不支持HASH索引，只支持HASH表空间。
位图的使用情况很局限，只有很少的情况才能用，一定要确定真正适合使用这种索引才用（值的类型很少并且需要复合查询），否则建立一大堆位图就一点意义都没有了。

八、索引失效

	1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 
	注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
	2.对于多列索引，不是使用的第一部分，则不会使用索引
	3.like查询是以%开头
	4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
	5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
	此外，查看索引的使用情况
	show status like ‘Handler_read%’; 
	大家可以注意：
	handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

九、怎么用索引?建立索引的思想？

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。

十、如何防止sql注入?

1）简单又有效的方法是使用PreparedStatement

采用预编译语句集，它内置了处理SQL注入的能力，只要使用它的setXXX方法传值即可。
使用好处：
(1).代码的可读性和可维护性.
(2).PreparedStatement尽最大可能提高性能.
(3).最重要的一点是极大地提高了安全性.
原理：
sql注入只对sql语句的准备(编译)过程有破坏作用而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,而不再对sql语句进行解析,准备,因此也就避免了sql注入问题.