学好MySQL的常用知识总结

1.MySQL的数据类型和约束

1.数据类型

• 整数：int，bit
• 小数：decimal
• 字符串：varchar,char
• 日期时间: date, time, datetime
• 枚举类型(enum)
  数据类型说明
• decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
• char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab '，3表示字符数
• varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’，3表示字符数
• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.
• 字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

2.数据约束

• 主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
• 非空 not null: 此字段不允许填写空值.
• 惟一 unique: 此字段的值不允许重复.
• 默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
• 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

类型	说明	使用场景
VARCHAR	可变长度，小型数据	姓名、地址、品牌、型号
TEXT	可变长度，字符个数大于 4000	存储小型文章或者新闻
CHAR	固定长度，小型数据	身份证号、手机号、电话、密码
LONGTEXT	可变长度， 极大型文本数据	存储极大型文本数据

类型	字节大小	示例
DATE	4	‘2020-01-01’
TIME	3	‘12:29:59’
DATETIME	8	‘2020-01-01 12:29:59’
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

2.基础SQL语句

# 查看所有数据库
show databases;
# 创建数据库
create databases 数据库名 charset=utf8;
# 选定使用数据库
use 数据库名
# 查看当前使用的数据库
select database();
# 删除数据库
drop database 数据库名；

# 创建表
create table 数据库名(
字段名称 数据类型 可选约束条件,
字段名称 数据类型 可选约束条件...
)
# 删除表
drop table 表名;

# 添加字段
alter table 表名 add 字段名称 数据类型 约束条件;
# 修改字段类型
alter table 表名 modify 字段名称 数据类型 约束条件;
# 修改字段名和字段类型
alter table 表名 change 原名称 新名称 数据类型 约束条件;
# 删除字段
alter table 表名 drop 字段名称;

# 查看创表SQL语句
show create table 表名;
# 查看创库SQL语句
show create database 库名;

# 添加数据--一条完整的数据
insert into 表名 values (...);
# 部分插入
insert into 表名 (字段1,字段2...) values (值1,值2...);
# 多列插入
insert into 表名 values (...),(...),(...)...;
# 多列部分插入
insert into 表名 (字段1,字段2...) values (值1,值2...),(值1,值2...),(值1,值2...)...;

# 修改数据
update 表名 set 字段1=值1,字段2=值2,... where 条件;

# 删除一条数据
delete from 表名 where 条件;

3.数据库设计

三范式

1. 第一范式（1NF）: 强调的是列的原子性，即列不能够再分成其他几列。
2. 第二范式（2NF）: 满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段 必须完全依赖于主键，而不能只依赖于主键的一部分。
3. 第三范式（3NF）: 满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

---

设计顺序和引擎选择
表结构 >> 字段设计 >> 索引设计 >> 数据库引擎选择

---

表结构设计

1. 垂直分表：将一张大表一分为二，将常用字段和不常用字段分离，提高效率，解决索引膨胀。
2. 垂直分库：将一个库一分为二，提高单个数据库处理能力；缓解单库磁盘空间不足；突破单库操作的IO瓶颈。
3. 水平分表：数据量极大时，才会考虑水平分表。分表依据：id范围；HASH；地理区域；时间。不建议采用
4. 水平分库分表：将单张表切分到多个服务器上，每个服务器有对应的库表。有效缓解单机单库的性能瓶颈，突破IO、连接数、硬件资源等瓶颈.
   缺点：无法实现关联查询，得多次查询。事务变成了分布式，消耗更多得性能，编程效率降低

# 查看mysql数据库支持的表的存储引擎
show engines;

# 修改表的存储引擎使用
alter table 表名 engine = "引擎类型"

• InnoDB: 事务型数据库首选引擎，支持事务安全表，支持行锁定和外键
• MyISAM：在web/数据仓库最常使用的存储引擎之一，有较高的插入和查询速度，不支持事务。
• MEMORY：类似redis，存储在运存中，没redis好用
• Archive：归档数据引擎，主要用作备份，记录日志信息

---

索引设计

• 优缺点：
  加快数据的查询速度，但是创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加
• 使用原则：
  1.通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
  2.对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引
  3.数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
  4.在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

# 查看表中已有索引
show index from 表名;

# 创建索引 -- 如果不指定索引名，默认使用字段名
alter table 表名 add index 索引名[可选] (字段名,...)

# 删除索引
alter table 表名 drop index 索引名;

验证索引性能

# 向表中添加10w条数据
from pymysql import connect

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()

if __name__ == "__main__":
    main()

# 1.开启运行时间检测
set profiling=1;
# 2.查找最末端的数据
select * from test_index where title='ha-99999';
# 3.查看执行时间
show profiles;
# 4.给title字段创建索引：
alter table test_index add index (title);
# 5.再次执行查询语句
select * from test_index where title='ha-99999';
# 6.再次查看执行的时间
show profiles;

联合索引

• 联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。

# 创建teacher表
create table teacher
(
    id int not null primary key auto_increment,
    name varchar(10),
    age int
);

# 创建联合索引
alter table teacher add index (name,age);

• 联合索引的最左原则:在使用联合索引的时候，我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询，因为没有用到创建的联合索引。

-- 下面的查询使用到了联合索引
select * from stu where name='张三' -- 这里使用了联合索引的name部分
select * from stu where name='李四' and age=10 -- 这里完整的使用联合索引，包括 name 和 age 部分 
-- 下面的查询没有使用到联合索引
select * from stu where age=10 -- 因为联合索引里面没有这个组合，只有 name | name age 这两种组合

4.事务

• 定义：事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。

• 作用：保证数据的完整性和一致性

• 四个特性
  A(Atomicity)原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
  C(Consistency)一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
  I(Isolation)隔离性：通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
  D(Durability)持久性：一旦事务提交，则会永久保存到数据库
  使用：确保存储引擎是InnoDB类型，只有这个类型才可以使用事务

• 事务的隔离级别

• 详细介绍可见这篇博客
  Read uncommitted(未授权读取、读未提交)：
  如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
  Read committed（授权读取、读提交）：
  读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
  Repeatable read（可重复读取）：
  可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现。
  Serializable（序列化）：
  提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。