数据库整理一：Mysql基础知识

Python开发【第十七篇】：MySQL（一）

Python开发【第十八篇】：MySQL（二）

Python开发【第十九篇】：Python操作MySQL

1、MySQL的架构：

MySQL是关系型数据库，主要用于存放持久化数据，将数据存储在硬盘中，读取速度较慢。

数据库的三范式：

• 强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项

• 要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

• 任何非主属性不依赖于其它非主属性。

分为Server和存储引擎两部分。

1.Server 层

1. Server 层包括：
   连接器、查询缓存、分析器、优化器、执行器等，涵盖了 MySQL的大多数核心服务功能，以及所有的内置函数（如：日期、时间、数学和加密函数等）

2. 所有跨存储引擎的功能都在这一层实现，比如：存储过程、触发器、视图等。

2.存储引擎层

1. 存储引擎层负责：数据的存储和提取。

2. 其架构是插件式的，支持 InnoDB、MyISAM 等多个存储引擎。

3. 从 MySQL5.5.5 版本开始默认的是InnoDB，但是在建表时可以通过 engine = MyISAM来指定存储引擎。不同存储引擎的表数据存取方式不同，支持的功能也不同。

4. 不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。

InnoDB和MyISAM比较

InnoDB：

• 最为通用 / 推荐的一种引擎，

• 支持事务、行级锁、表锁甚至间隙锁（避免幻读）

• 支持热备份，支持MVCC，支持外键，

• 支持B+树索引和自适应哈希索引

• InnoDB5.6之前不支持全文索引

• 在并发上占优势，原始数据存储，系统资源占用多。

• 关于count()，InnoDB不会直接存储总行数，需要按行扫描。

MyISAM：

• 默认的存储引擎，

• 支持全文索引，支持表锁

• 只支持B+树索引

• 不支持事务和行级锁，不支持MVCC，不支持外键

• 实现了前缀压缩技术，系统资源占用少（但会影响查找），查询速度上完胜（大概是InnoDB的3倍）。

• count()：MyISAM会直接存储总行数

PS：大部分情况下，InnoDB都是正确的选择。

3、锁及粒度

1、共享锁/读锁：

• 互不阻塞，优先级低

2、排他锁/写锁：

• 阻塞其他锁，优先级高，即确保在一个事务写入时不受其他事务的影响。

3、锁粒度：

• 锁定的数据量越少（粒度越小），并发程度越高，但相应的加锁、检测锁、释放锁用的系统开销也随之增大。

4、锁策略：锁开销与数据安全性之间的平衡

1）表锁：

• 锁住整张表，读锁互不阻塞，写锁阻塞其他所有读写锁（同一张表）

• 开销最小，加锁快，不会出现死锁。

• 锁定粒度大，发生锁冲突的概率最高，并发量最低。

2）行级锁：

• 对每一行数据（记录）加锁。

• 开销大，加锁慢，会出现死锁。

• 锁力度小，发生锁冲突的概率小，并发度最高。

5）InnoDB存储引擎的锁的算法

• Record lock：单个行记录上的锁；

• Gap lock：间隙锁，锁定一个范围，不包括记录本身；

• Next-key lock：record + gap 锁定一个范围，包含记录本身。

4、InnoDB MVCC

多版本并发控制，是为了避免加锁而实现的。

一般的实现方法是存储快照来实现的。

InnoDB实现方式：

• 在记录后添加两个隐藏列（表项），分别是事务创建时间、过期时间，存储的实际上是系统版本号（系统版本号随着事务的创建而递增）。

• 即 insert 时加上开始版本号，update / delete时加上过期版本号，在 select 时，只访问开始系统版本号小于当前的事务的版本号、过期时间要么未定义要么在当前版本号之后的记

• 这样就可以保证：访问的记录是在本事务开始前就存在而且在本事务期间没有过期（被删除或被修改过的）。

5、关系型数据库和非关系型数据库

1）关系型数据库：

• 指采用了关系模型来组织数据的数据库。

• 关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织

关系模型中常用的概念：

• 关系：一张二维表，每个关系都具有一个关系名，也就是表名

• 元组：二维表中的一行，在数据库中被称为记录

• 属性：二维表中的一列，在数据库中被称为字段

• 域：属性的取值范围，也就是数据库中某一列的取值限制

• 关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成

• 关系模式：指对关系的描述。其格式为：关系名(属性1，属性2， … … ，属性N)，在数据库中成为表结构

当今十大主流的关系型数据库：

• Oracle，Microsoft SQL Server，MySQL，PostgreSQL，DB2， Microsoft Access，SQLite，Teradata，MariaDB(MySQL的一个分支)，SAP

2）非关系型数据库

• 指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。

• 非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

2、基础语句

1、创建与删除数据库 / 表

数据库：

create database 数据库名称 charset=utf8
drop database 数据库名称 

数据表：

 create table table_name(
        id int not null auto_increment primary key   
        name char（10）    --10代表10个字符
        department_id int
        constraint 外键名称 foreign key(department_id) references 另外一张表的表名(列名)
        unique 唯一索引的名称(列名，列名)   --不能重复，可以为空                
        )engine=innodb   default charset=utf8;

2、键

1）主键

• 主键不可以为空，一张表只能有一个主键，一个主键不一定只有一列

• 当使用两列做主键时：primary key(列名，列名)即可，注意：这两列都要not null

• 不建议使用过长的字段作为主键
  因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

• 在InnoDB中不要用非单调的字段作为主键。
  因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

• 主键是能确定一条记录的唯一标识，比如，一条记录包括身份证号，姓名，年龄，身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。

InnoDB为什么要用自增id作为主键：

• 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

• 如果使用非自增主键（比如身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过优化表（optimize table）来重建表并优化填充页面。

• 因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

2）外键

• 外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

• 外键的名字不可以重复，外键可以节省空间，可以起到约束的作用

• 外键：一对一，一对多，多对多

• 当外键关联的表中使用两列做主键时，外键sql语句中references后面可以使用这两个列名

constraint 外键名称 foreign key(department_id) 
references 另外一张表的表名(列名，列名)

对于自增：

desc tb1;  --查看表tb1中数据的字段类型、是否为空等
show create table tb1;  --查看表tb1是怎么创建的，横着显示结果
show create table tb1 \G;  --查看表tb1是怎么创建的， 竖着显示结果
alter table tb1  AUTO_INCREMENT=2  --用于修改自增列的起始值为2
                
show session variable like 'auto_inc'   --查看变量
set session auto_increment_increment=2  --用于改变会话中的自增的步长，不影响其他会话中的步长
set session auto_increment_offset=2  --用于改变会话中的自增的起始值
                
show global variable like 'auto_inc'   --查看变量
set global auto_increment_increment=2  --用于改变全局的自增的步长，任意会话中的步长都被变化
set global auto_increment_offset=2  --用于改变全局的自增的起始值 

3、基本数据类型

1）数字：

• 整数： tinyint（范围小）、int（范围稍大）、bigint（范围更大）

• 小数：double（不精准）、float（不精准）、decimal（精准）

• decimal(10,5) # 总共保留10位，其中小数点后保留5位

2）字符：

• char：定长、查找速度更快

• varchar：不定长，最多255

• text：不定长，大于255时用

非常大时写成文件保存在硬盘上，把路径写入数据库（照片也是）

创建数据表时，把定长的往前放置，则查询速度会相对快一些

char和varchar的区别：

1.char(n)

• char类型是定长的类型

• 当定义的是char(10)，输入的是"abc"这三个字符时，它们占的空间一样是10个字节，包括7个空字节。效率高，但占用空间。

• 当输入的字符长度超过指定的数时，char会截取超出的字符。

• 当存储char值时，MySQL自动删除输入字符串末尾的空格。

• 取数据的时候，char类型的要用trim()去掉多余的空格，varchar不需要。

• char适合存储很短的、一般固定长度的字符串。
  对于非常短的列，char比varchar在存储空间上更有效率。

2.varchar(n)类型

• varchar(n)类型用于存储长度为 n 个字节的、可变长度的、非Unicode的字符数据。
  n必须是介于1和8000之间的数值，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

• varchar需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节（是定义的最长长度，不是实际长度），则使用1个字节表示长度，否则使用2个字节来表示。

• varchar(10)中10 的含义是最多存放 10 个字符，当输入abc三个字符时，那么实际存储大小为3个字节。

• varchar(10) 和 varchar(20) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 order by col 采用 fixed_length 计算 col 长度。

• 取数据的时候，varchar不需要去掉多余的空格。

3）时间类型：datetime（最常用，年月日时分秒）

4）二进制类型

• 枚举：enum()，选项固定且不常修改时候用，只能选其中一个选项

• 集合：set()，几个选项组合起来用

size enum('1','2','3')  --选择size，1,2,3中选一个
col set('a','b','c')  # 选择col，a,b,c中选，可以选多个

4、数据行的增删改查操作

增：

insert into table_name1(name, age) values('alex',18),('alex1',19);


--把tb2中的数据插入到tb1中
insert into table_name1(name, age)  select name,age from table_name2;  

删：

delete from table_name 
 --删除了之后自增列会在原来的基础上继续增加，不会从0重新开始

truncate table table_name  --删除了之后自增列从0开始
delete from table_name where id > 10  --删除指定列
delete