面试提高及日常学习积累——数据库篇

这一篇主要讲mysql的一些积累和学习体会。

一：数据库基础知识

Innodb引擎

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这篇文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

MyIASM引擎

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

唯一索引(聚集索引)

单个表只能有一个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。数据存储结构和索引存储结构顺序一致。（一般主键为唯一索引）

创建单列索引：CREATE UNIQUE  INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR类型，length可以小于字段实际长度;如果是BLOB 和 TEXT 类型，必须指定length。

创建组合索引：CREATE UNIQUE  INDEX indexName ON tableName( `column1`, `column2`, `column3` );

删除索引：DROP INDEX indexName;

非聚集索引

单个表可以有多个非聚集索引，可以是单列也可以是组合索引，数据存储结构和索引存储结构顺序无关。

创建单列索引：CREATE INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR类型，length可以小于字段实际长度;如果是BLOB 和 TEXT 类型，必须指定length。

创建组合索引：CREATE INDEX indexName ON tableName( `column1`, `column2`, `column3` );

删除索引：DROP INDEX indexName;

组合索引

创建组合索引：CREATE INDEX indexName ON tableName( `column1`, `column2`, `column3` );

对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b|c|b,c进行查找 ,当最左侧字段是常量引用时，索引就十分有效。(组合索引最多2-3列)

视图与存储过程

这两块的内容可以更具具体的业务需要进行建立，一般对于查询较多的相似组合的表，或者需要隐藏原表信息的可以合并提供查询视图，对于复杂业务和需要特殊效率的业务可以提供存储过程。

常用函数

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

以下内容来自请去这里查看更详细  http://www.jb51.net/article/40179.htm 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

2、字符串函数
CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
REVERSE(str) 返回颠倒字符串str的结果

3、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt)  依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
DAYOFYEAR(date)   返回date是一年的第几天(1~366)
DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time)   返回time的小时值(0~23)
MINUTE(time)   返回time的分钟值(0~59)
MONTH(date)   返回date的月份值(1~12)
MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW()    返回当前的日期和时间
QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date)   返回日期date为一年中第几周(0~53)
YEAR(date)   返回日期date的年份(1000~9999)

4、控制流函数
MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。

MySQL控制流函数：
CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f)   如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。

代码如下:

SELECT CASE 'green'
     WHEN 'red' THEN 'stop'
     WHEN 'green' THEN 'go' END;
SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;
SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS;
SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROMUserLoginInfo;
SELECT fname,lname,(math+sci+lit) AS total,
CASE WHEN (math+sci+lit) < 50 THEN 'D'
WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'
WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'
ELSE 'A' END
AS grade FROM marks;
SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#一个登陆验证

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

二：数据库设计

在官方学习的时候这部分是要符合三范式规则的，但是这种规则下对于大多数的数据业务来说都是在理论上太完整，不太实用，所以可以学习这种设计的思想，但是适不适用可以按照自己的业务规划。

在设计数据库的过程主要可以参考以下几个方向（个人总结）：

1.首先分析自己的业务，抽象好数据对象以及对象之间的关联关系。

2.对于通用的一些数据字段可以抽象总结出来，比如创建时间，更新时间，操作者。。。这些通用的字段。

3.针对自己的业务主要分析查询的部分，对于重复查询较多，修改机会较少的字段可以适当的在多个表中进行冗余，以提高查询和简化sql代码的复杂度。

4.在建表时可以考虑后期业务的扩展性，但是不必强制流出字段等，因为业务变化比较平凡，过多的预留会让表看起来非常混乱。

5.当业务扩展性和项目需求有冲突时可以优先考虑项目需求，至于扩展性可看业务发展再定，甚至修改。（个人观点，可不理会）

6.做好字段说明，备注。

三：sql优化（只是优化的方向和原则）

1.级联查询优化，多个单标查询的优化，可以考虑用多表级联查询优化。

2.考虑索引使用情况优化，对于破坏索引，未使用索引的sql，可以考虑建立索引或者使现有索引生效方面优化。

3.对于大数据量分页的优化。

4.通过分析业务，用更多的条件确定更少的列。

 四：数据库架构

1.对于大数据和有读写压力的业务可以考虑从几个方向去设计   

表分区：http://www.cnblogs.com/freeton/p/4265228.html  这个网址写的不错，值得浏览下。

分表存储：按业务需求可以有水平分表和垂直分表两种不同的技术。

　　　　    水平分表是按照一定的规则将一个大的表格水平分割到不同的子表中，所以每个表格的列都是相同的。

　　　　　垂直分表是将一个大的表格按照不同的业务需求把不同的列分割到不同的子表中，以某一个相同的属性（例ID）关联起来。

　　　　　对于分表后的查询可以按照业务先进行逻辑判断，然后定位到某个子表。对于一些统计功能，则循环所有子表，合并。

分库技术：就是采用不同的数据库部署数据库集群，可以搜搜MyCat中间件。

读写分离：http://blog.csdn.net/zoomyj/article/details/50610349  这片文章写了好多 有兴趣的可以多了解下，作为一个了解和积累。

总结：零零散散写了一些，这里写的主要是一些方向性的东西，可能没有具体的一些实现或者是代码。所以只是一个指引，希望能帮助大家在这些方面去学习，去积累。也有一些东西是网络摘抄的，只要是好的就学习吧。

以上内适用一到三年java开发面试，个人见解，值得参考,觉得适用的顺手推荐下，让更多的人看到

最后分享一个不错的鸡汤给想要干事的朋友们，祝大家成功！http://www.iheima.com/zixun/2017/0628/163820.shtml