【读书笔记】数据库

这里是整理了数据库的一些常见问题，以及基本命令

---

1、数据库中的事务 

数据库事务是数据库运行中的逻辑工作单位，单个逻辑工作单元所执行的一系列操作，要么都执行，要么都不执行。

2、数据库事务的四大特性（ACID)

• 原子性(Atomicity)：一件事务要么提交成功，要么全部失败回滚
• 一致性(Consistency)：事务执行前后都保持一致性原则
• 隔离性(Isolation)：事务提交之前它对其他所有事务不可见。通过乐观锁和悲观锁实现
• 持久性(Durability)：一旦事务提交，所做修改永久保存到数据库中
  
  

3、数据库的锁

• 乐观锁：假设不会发生冲突，直到事务提交时才检查是否违反了完整性(通过数据 版本或者时间戳实现；添加version字段，每做一次操作就让version+1，提交时进行对比)
• 悲观锁：假设一定会发生冲突，屏蔽一切可能违反完整性的操作(synchronized就是悲观锁)
• 共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁。获准共享锁的事务只能读数据，不能修改数据。
• 排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

4、数据库优化

(1)sql语句的优化

• 子查询变成left join
• or条件优化，多个or条件可以用union all对结果进行合并
• where代替having,having 检索完所有记录，才进行过滤
• 对多个字段进行等值查询时，联合索引

(2)索引优化

(3)数据库结构优化

• 范式优化：消除冗余
• 反范式优化：适当加冗余（减少join）
• 拆分表：分区将数据在物理上分隔开
• 垂直拆分和水平拆分

(4)服务器硬件优化

5、关系型数据库的三大范式

第一范式：确保每一列的原子性。如果每一列都是不可再分的最小数据单元，则满足第一范式。

第二范式:  非键字段必须依赖于键字段：如果一个关系满足1NF，并且除了主键以外的其它列，都依赖与该主键，则满足2NF，第二范式要求每个表只描述一件事。

第三范式：在1NF基础上，除了主键以外的其它列都不传递依赖于主键列，或者说： 任何非主属性不依赖于其它非主属性

6、聚集索引和非聚集索引

聚集索引 ：数据按索引顺序存储，中子结点存储真实的物理数据

非聚集索引：存储指向真正数据行的指针

7、索引类型

普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它是一种特殊的唯一索引，不允许有空值。
全文索引：针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

8、索引工作原理

索引的实现通常使用B树和B+树。

• B树：每个节点存储key和data。所有节点组成这棵树，叶子结点不包含任何关键信息
• B+树：所有叶子节点包含了全部关键字的信息，及指向含有这些关键字记录的指针

B+树的磁盘读写代价更低，查询效率更稳定。

9、mysql基本语句

基本操作

create database db_name;    创建数据库
drop database db_name;        删除数据库

创建新表
create table tb_name(col1 type1[not null][primary key],col2 type2,...);
drop table tb_name;            删除表

alter table tb_name add column col type;    增加一个列
alter table tb_name add primary key(col);    增加主键
alter table tb_name drop primary key(col);    删除主键


create [unique] index idxname on tabname(col….);   创建索引
drop index idxname;            　　　　　　　　　　　　删除索引

create view viewname as select statement;    　　创建视图
drop view viewname;                           　 删除视图


常规操作
select * from table1 where 范围                            选择
insert into table1(field1,field2) values(value1,value2)   插入
delete from table1 where 范围                        　　　 删除
update table1 set field1=value1 where 范围                 更新
select * from table1 where field1 like ’%value1%’         查找
select * from table1 order by field1,field2 [desc]        排序
select count as totalcount from table1                    总数
select sum(field1) as sumvalue from table1                求和
select avg(field1) as avgvalue from table1                平均
select max(field1) as maxvalue from table1                最大
select min(field1) as minvalue from table1                最小

10、join

inner join
基于连接谓词将两张表的列组合在一起，产生新的结果表
select * from user1 inner join user2 on user1.user_name=user2.user_name;


left join
从左表产生一套完整的记录，还有右边匹配的记录，如果没有匹配就包含null
select * from user1 left join user2 on user1.user_name=user2.user_name;

只查询左表的数据，不包含右表的，使用where 限制右表key为null
select * from user1 left outer join user2 on user1.user_name=user2.user_name where user2.user_name is null;


right join
select * from user1 right join user2 on user1.user_name=user2.user_name;


cross join
交叉连接，得到的结果是两个表的乘积，即笛卡尔积 
select * from user1 cross join user2;