Python基础学习（38） 存储引擎 表和数据的基础操作 数据类型 表的完整性约束 修改表 多表结构的创建与分析

Python基础学习（38） 存储引擎 表和数据的基础操作 数据类型 表的完整性约束 修改表 多表结构的创建与分析

一、今日内容大纲

• 存储引擎
• 表和数据的基础操作
• 数据类型
• 表的完整性约束
• 修改表
• 多表结构的创建与分析

二、存储引擎

存储引擎，即存储数据的方式；

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

1. 数据存储在硬盘上（数据持久化存储）

   在一张表中，表的数据和表的结构分别以不同的文件形式存储，表的数据可以使用B+树数据结构作为索引；

   不同的存储引擎会出现不同的存储方式：

   Innodb：mysql5.6及之后的默认存储引擎，数据和索引存储在一起，包含2个文件；数据结构一个文件、表结构一个文件；

   • 支持事务（transactions）：通过开启事务和提交事务将多个原子操作集合为一个原子操作（如银行转账操作）；
   • 支持行级锁（row-level locking）：修改行少的时候使用行级锁（防止多个客户端同时修改）；
   • 支持表级锁：批量修改多行的时候使用；
   • 支持外键（foreign keys）：字段的数据可以关联外表，这个字段以及字段下的列称为外键（降低增删改的出错率）；

   Myisam：mysql5.5及之前的默认的存储引擎；数据和索引不存储在一起，包含三个文件；数据、索引、表结构分别为一个文件；

   • 仅支持表级锁；

2. 数据存储在内存中（数据断电即消失）

   Memory：所有内容都存储在一起，只有一个文件，断电即小时；

mysql5.6支持的存储引擎主要包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MyISAM、ARCHIVE、PERFORMANCE_SCHEMA；其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表；

3. 存储引擎相关命令

   • 查看当前数据库支持的存储引擎：show engines;

     

   • 查看当前默认的存储引擎：show viriables like '%engine%';

     

4. 如何指定存储引擎建表

   # 在建表时指定
   create table ai(id bigint(12), name varchar(200)) engine=myisam;
   create table country(id int(4), cname varchar(50)) engine=innodb;
   
   # 也可以使用alter table语句，修改一个已经存在的表的存储引擎
   alter table ai engine = Innodb
   
   # 查看当前创建表的属性
   show create tbale engine;
   

   当我们创建一个表后，打开MySQL根目录下的data目录，会发现每个database都是一个文件夹；

   用InnoDB存储引擎建立的表，被分别存储为了一个.frm(frame)文件和一个.idb(InnoDB data)文件；

   用MyISAM存储引擎建立的表，被分别存储为了一个.frm(frame)文件和一个.MYD(MyISAM data)和一个.MYI(MyISAM index)文件；

   用Memory存储引擎建立的表，存储为了一个.frm(frame)文件；

使用Memory存储引擎的情况：使用较为频繁且在硬盘中有备份的数据；

面试题：你了解mysql存储引擎吗？你的项目用了什么存储引擎？为什么？

InnoDB，多个用户操作的过程中对同一张表的数据同时进行修改，InnoDB支持行级锁，所以我们使用了这个存储引擎；由于项目中需要扩展支付相关，InnoDB支持transactions，有助于维护数据的完整性；项目中有两张表，之间存在外键关系，为了防止增删改查出错，选择使用外键约束；

三、表和数据的基础操作

1. 创建表

   create table <TABLE_NAME>(
   	<KEY1> <DATA_TYPE>[(<LENGTH>) <CONSTRAINT>],
   	...
   );
   # 中括号的内容可以不写
   

2. 写入数据的方式

   • 增加一组数据

     insert into <TABLE_NAME> values(
     	<VALUE1>,
     	...
     );
     

   • 只增加某关键字下的数据

     insert into <TABLE_NAME> (<KEY1>,...) values (<VALUE1>,...);
     

3. 查看表中的数据

   select * from <TABLE_NAME>;
   

4. 查看表结构

   # 能够查看有多少字段、类型、长度，看不到表编码、引擎，具体的约束信息只能看到一部分
   desc <TABLE_NAME>;
   
   # 能够查看字段、类型、长度、编码、引擎、约束
   show create table <TABLE_NAME>;
   
   # 如果觉得表结构比较难以阅读可以在命令后加\G，可以以非表格的形式显示结果
   

四、数据类型

1. 数值类型

   数据类型	大小
   (unsigned) TINYINT	1byte
   (unsigned) SMALLINT	2byte
   (unsigned) MEDIUMINT	3byte
   (unsigned) INT/INTEGER	4byte
   BIGINT	8byte
   FLOAT	4byte
   DOUBLE	8byte
   DECIMAL	depend on D/M

   数值类型的长度约束：

   • int：一般情况下无需约束，最多可以表示10位数（长度11中有一位用作正负号，而unsigned约束下int默认长度只有10）；
   • float：一般需要约束，需要约束总位数和小数部分位数（float(255,30)中的30指的时小数点后30位，255指的是小数点后共255位，MySQL中的浮点数会自动四舍五入）。

2. 时间类型

   数据类型	大小	表示方法
   DATE	3byte	YYYY-MM-DD
   TIME	3byte	HH:MM:SS
   DATETIME	8byte	YYYY-MM-DD HH:MM:SS
   YEAR	1byte	YYYY
   TIMESTAMP	4byte	YYYY-MM-DD HH:MM:SS

   TIMESTAMP的时间从1970-01-01（计算机元年）开始到2038年即将结束；TIMESTAMP的时间默认为插入/修改数据的最后时间，即可记录本条数据的最后修改时间；

   可以通过将timestamp的约束后缀复制给datetime后，来实现赋予timestamp记录最后修改时间的特性（如果记不住timestamp的后缀，可以通过show create table <TABLE_NAME>;语句来读取）。

   注：timestamp的后缀为NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

   时间类型的插入方式（所有时间类型都无需长度约束）：

   • 数字表示：

     如date插入时可以用YYYYMMDD（如20180808）来表示；

     同理datetime可以用YYYYMMDDHHMMSS来表示，其他时间类型亦同理；

   • 字符串表示

     date可以用'YYYY-MM-DD'表示，datetime可以用'YYYY-MM-DD HH:MM:SS'来表示，其他同理；

   • 当前时间

     任何时间类型在插入时都可以使用now()来表示；

3. 字符串类型

   数据类型	大小	备注
   CHAR	0-255 byte	定长字符串
   VARCHAR	0-65 535 byte	变长字符串
   TINYBLOB	0-255 byte	二进制字符串
   TINYTEXT	0-255 byte	短文本字符串
   BLOB	0-65 535 byte	二进制形式的长文本数据
   TEXT	0-65 535 byte	长文本数据
   MEDIUMBLOB	0-16 777 215 byte	二进制形式的中等长度文本数据
   MEDIUMTEXT	0-16 777 215 byte	中等长度文本数据
   LONGBLOB	0-4 294 967 295 byte	二进制形式的极大文本数据
   LONGTEXT	0-4 294 967 295 byte	极大文本数据

   字符串类型的长度约束：

   char(18)表示能够容纳18个字符，char类型只能表示255个字符（char可以不写长度，默认为1）；

   varchar(18)同样表示能够容纳18个字符，varchar类型最多只能表示65535个字符（varchar必须写长度，否则无法执行）；

   char和varchar的区别：

   • char：定长存储，如存储一个‘alex’，会在后面补充剩余的空格，浪费空间，但存取速度快节省时间；
   • varchar：变长存储，如存储一个‘alex’，会在后面添加一个字符长度标记，节省空间，但存取速度慢浪费时间；

   什么情况下适合使用char/varchar：

   • char：身份证号、手机号码、QQ号、username、password、银行卡号等；
   • varchar：评论、朋友圈、微博等；

4. enum/set类型（单选、对选）

   • enum单选

     在定义类型时需要在后面加入约束条件（<OPTION1>,...），其中<OPTION>只可以为字符串形式，增加数据的时候只能从规定的约束条件选项中选择，否则会WARNING且不添加；

   • set对选

     在定义类型时需要在后面加入约束条件（<OPTION1>,...），其中<OPTION>只可以为字符串形式，增加数据的时候只能从规定的约束条件选项中选择，且重复内容会自动去重；

     注：set类型的增加数据方式较为特殊insert into <TABLE_NAME> values('<OPTION1>,...');；

五、表的完整性约束

1. 严格模式

   严格模式包含下列三项约定：不支持对not null字段插入null值；不支持对自增长字段插入''值；不支持text字段有默认值；

   设置方法：

   • 重启失效，直接在MySQL中设置：

     set sql-mode="STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION";

   • 配置文件添加（将下列代码拷贝至my.ini配置文件），永久生效：

     sql-mode="STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

2. 单个字段约束

   • 不能为空

     not null

   • 不能重复

     unique（对null无限制）

     联合唯一：对IP和PORT同时不可以两个都完全相同，这就是联合唯一；

     # 联合唯一使用方法：
     create table <TABLE_NAME>(
     	<KEY1> <TYPE1>,
     	<KEY1> <TYPE2>,
     	...
     	unique(<KEY1>, <KEY2>)
     );
     

   • 无符号的

     只和数值类型有关，如：int unsigned；

   • 默认值

     default <DEFAULT_CONTENT>

   • 自增

     auto_increment，默认从1开始，每次自增1；如ID字段可以设置自增，这样增加数据的时候就无需填写ID字段了；只能对数字有效，自带非空约束，必须在unique约束后再进行自增约束（也可以设置主键后进行自增约束）；

   • 主键

     主键必定存在非空+唯一的性质；第一个被定义为非空+为一个那一个字段会被称为这一张表的primary key即主键；另外也可以增加关键字primary key来自己指定主键；

     联合主键：不能同时为空，且不能同时相同；

     create table <TABLE_NAME>(
     	<KEY1> <TYPE1>,
     	<KEY1> <TYPE1>,
     	...
     	primary key(<KEY1>, <KEY2>)
     );
     
     # 另外也可以这么写
     create table <TABLE_NAME>(
     	<KEY1> <TYPE1> not null,
     	<KEY1> <TYPE1> not null,
     	...
     	unique(<KEY1>, <KEY2>)
     );
     

   • 外键

     外键（foreign key）就是在一张表中关联另一张表；其实现方式为foreign key(<LOCAL_KEY>) references <TABLE_NAME>(<KEY>);；

     # case
     # 班级表
     create table cls(
     	cid int primary key auto_increment,
         cname char(12) not nullm
         start date
     );
     
     # 学生表
     create table stu(
     	id int primary key auto_increment,
     	name char(12) not null,
     	gender enum('male', 'female') default 'male',
     	class_id int,
     	foreign key(class_id) references cls(cid)
     );
     

     外键的级联更新、级联删除（慎重）：更新和删除操作会影响外键连接的其他数据；on update cascade 和on delete cascade；

六、表的修改

# 修改表名
alter table <TABLE_NAME> rename <NEW_TABLE_NAME>;

# 增加字段
alter table <TABLE_NAME add <KEY> <DATA_TYPE> [(<LENGTH>) <CONSTRAINT>];
# 注:中括号中的内容可以不写（int长度不写相当于11）

# 删除字段
alter table <TABLE_NAME> drop <KEY>;

# 修改字段
alter table <TABLE_NAME> modify <KEY> [<CONSTRAINT>];
alter table <TABLE_NAME> change <OLD_KEY> <NEW_KEY> <OLD_DATA_TYPE> [<CONSTRAINT>];
alter table <TABLE_NAME> change <OLD_KEY> <NEW_KEY> <NEW_DATA_TYPE> [<CONSTRAINT>];

# 修改字段排列顺序/在增加的时候指定字段位置
alter table <TABLE_NAME> add <KEY> <DATA_TYPE> [(<LENGTH>) <CONSTRAINT>] first;
alter table <TABLE_NAME> add <KEY> <DATA_TYPE> [(<LENGTH>) <CONSTRAINT>] after <KEY_1>;
alter table <TABLE_NAME> change <OLD_KEY> <NEW_KEY> <OLD_DATA_TYPE> [<CONSTRAINT>] first;
alter table <TABLE_NAME> change <OLD_KEY> <NEW_KEY> <OLD_DATA_TYPE> [<CONSTRAINT>] after <KEY_1>;

七、多表结构的创建与分析

两张表之间的关系，主要包含多对一、多对多、一对一等；

1. 多对一

   多个学生对应一个班级：学生表有一个外键，关联班级表；

   多本书对应一个作者：书籍表有一个外键，关联作者表；

   多本书对应一个出版社：书籍表有一个外键，关联出版社表；

   ...

2. 多对多

   多个学生对应一个班级的同时，多个班级对应一个学生：连理第三张表，明确其多对多关系，并分别与两个表建立索引外键关系，如图所示：

3. 一对一

   客户-学生：一边有一个外键，两边都进行unique约束；