数据库小记

[薄舟]

查询的执行顺序：
1）from : 表名
2）where：条件过滤
3）group by : 分组
4）having ： 分组之后进行过滤。
5）select ：执行完毕之后，显示内容。
6）order by ： 根据查询的内容进行排序输出.

数据库范式
1NF 原子性：表中每列不可再拆分。
2NF 不产生局部依赖，一张表只描述一件事情。表中的每一列是完全依赖于主键的。
3NF 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。


事务的特性（ACID）
隔离性：每个事务都是独立的互相隔离互不影响的
持久性：指一个事务一旦被提交，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。
原子性：表中每列不可再拆分。原子性是指事务包装的一组sql(一组业务逻辑)是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性：一个事务在执行之前和执行之后 数据库都必须处于一致性状态。
如果事务成功的完成，那么数据库的所有变化将生效。
如果事务执行出现错误，那么数据库的所有变化将会被回滚（撤销），返回到原始状态。
事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性。
事务问题：
事务的隔离级别
脏读：指一个事务读取了另外一个事务未提交的数据。(非常危险) 注意脏读的前提是没有事务的隔离性
不可重复读：在一个事务内多次读取表中的数据，多次读取的结果不同。（update）
虚读：在一个事务内多次读取表中的数据，多次读取的结果不同。（insert/delete）
隔离级别
安全和性能对比
安全性：serializable > repeatable read > read committed > read uncommitted
性能 ： serializable < repeatable read < read committed < read uncommitted
常见数据库的默认隔离级别：
MySql：repeatable read。
Oracle：read committed。

[薄舟]

数据库删除小面试题
delete删除表中的数据时没有加where 条件，会删除表中的所有数据，它与truncate 有什么区别？

1.DELETE
　DML语言
数据还可以找回来，恢复回来
　可以有条件的删除。DELETE FROM 表名 WHERE 条件

2.TRUNCATE TABLE
　DDL语言
数据不可以恢复
　先将整个表删除，再重新创建
　删除速度比delete快。
delete from person; 和 drop table person; 有什么区别：
delete from person; 把person表中的所有数据全部删除，但是person的表还存在。还可以向表中继续添加数据。
drop table person; 把person数据表从数据库中删除。表已经不存在，不能再向表中添加数据。

[薄舟]

数据库索引：

主键默认有索引；对于经常查询排序分组的id增加索引
语法：create index 索引名 on 表名 (字段名,字段名)
create index i_staff on staff (empno);
create index i_agent on agent (empno, start_date);
CREATE INDEX mytable_categoryid ON mytable (category_id);
CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);
删除索引的语句：drop index 索引名
drop index I_staff;
索引的一些特点：
1.不同值较多的列上可建立检索，不同值少的列上则不要建 比如在雇员表的“性别”列上只有“男”与“女”两个不同值
2.: 如果在索引列上加表达式，则索引不能正常使用
例如：b1,c1分别是表b,c的索引列
select * from b where b1/30< 1000 ;
select * from c where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
以上都是不正确的写法
3: where子句中如果使用in、or、like、!=，均会导致索引不能正常使用
例如：select * from b where b1=30 or b1=40;
4: 使用复合索引进行查询时必须使用前置列
例如表a上有一个复合索引（c1,c2,c3）,则c1为其前置列
如果用c1或c1+c2或c1+c2+c3为条件进行查询，则该复合索引可以发挥作用，反之，用c2或c3或c2+c3进行查询，则该索引不能起作用。


引用了 https://www.cnblogs.com/wanggd/p/3240998.html

[薄舟]

数据库优化：
1.字段的属性大小要符合具体的业务情况，数据库中的表性能越高，合理应用char跟varChar，比如邮编用char类型足够了，尽量把字段设置为notNull，这样以后查询
的时候，数据库就不用去比较null值，不要滥用bigint。
2.使用多表查询时使用内连接或者外连接代替子查询；使用子查询时需要把内层查询结果当作外层查询的比较条件，执行子查询时需要创建临时表，查询完毕再删除这些表
这样已经影响了效率；在关联很多张表并且数据量很大的情况下，需要将这几张变得数据全部查询一遍这需要耗费大量时间，而连接查询并不用创建临时表
3.对一些值有限的列用0123来表示不过要写好注释
4.将一些查询比较频繁的字段加上索引，varchar的类型在建立索引的时候最好指定好长度，查询有多个条件时，优先使用具有索引的条件，像LIKE条件这样的模糊搜索对于字段索引是无效的，
需要另外建立关键词索引来解决；请尽量不要在数据库层面约束表和表之间的关系，这些表之间的依赖应该在代码层面去解决
5.一张表的某些字段值比较大的话应该拆分成多张表（垂直拆分，水平拆分）一张表的某些字段访问查询量比较高也应该分表
6.优化查询语句：请尽量指定需要查询的列，不要使用select *如果不指定，一方面会返回多余的数据，占用宽带等；当只要一行数据时，请使用LIMIT 1，如果数据过多，
请适当设定LIMIT，分页查询；多条件查询时，请把简单查询条件或则索引列查询置于前面
7.添加缓存：使用redis等缓存，还有本地文件缓存等，可以极大地减少数据库查询次数。缓存这个东西，一定要分析自己系统的数据特点，适当选择。
对于一些常用的数据，比如配置信息等，可以放在缓存中
可以在本地缓存数据库的表结构
缓存的数据一定要注意及时更新，还有设置有效期
增加缓存务必会增加系统复杂性，一定要注意权衡
8.数据库引擎：mysql比较常用的数据库引擎有两种，一种是innodb、一种是myisam 之所以mysiam快，是因为他的数据存储结构、索引存储结构和innodb不一样的，
mysiam的索引结构是在内存中存的 ；mysiam也有弱点，那就是他是表级锁，而innodb是行级锁，所以，mysiam适用于一次插入，多次查询的表，或者是读写分离中的读
库中的表，而对于修改插入删除操作比较频繁的表，就很不合适了