04 表相关操作

一. 存储引擎介绍

1. 什么是存储引擎

存储引擎就是表的类型, 争对不同类型的表, mysql使用不同的代码处理

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方
法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和
操作此表的类型）

拓展:

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql
数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据
自己的需要编写自己的存储引擎

SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询。 

2. MySQL四种主要存储引擎介绍

# InnoDB
'''
使用版本: MySQL5.5版本及之后默认的存储引擎
优点: 支持事务, 行级锁定和外键约束对数据的安全性较	
半个缺点: 访问速度没有MyISAM快, 但是我们更加注重的是数据的安全.

每个InnoDB在磁盘上存储成2个文件，其中文件名和表名都相同，但是扩展名分别为：
    .frm(存储表结构)
    .idb(i -> index. 索引+数据组织表)
'''

# MyISAM
'''
使用版本: MySQL5.5版本之前默认的存储引擎
优点: 访问速度要比Innodb更快
缺点: 不支持事务，也不支持外键, 对事务完整性没有要求, 数据的安全性没有Innodb高.

每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：
    .frm(存储表结构)
    .MYD(MyData，存储数据)
    .MYI(MyIndex，存储索引)
'''

# MEMORY
'''
优点: 采用内存存储, 访问数据可以得到最快的响应.
缺点: 断电数据丢失. 或当mysqld守护进程崩溃时，所有的Memory数据也会丢失.

每个MEMORY在磁盘上存储成1个文件，其中文件名和表名都相同，但是扩展名分别为：
    .frm(存储表结构)
'''

# BLACKHOLE
'''
特点: 无论存什么，都立刻消失(黑洞). 类似于回收站

每个BLACKHOLE在磁盘上存储成1个文件，其中文件名和表名都相同，但是扩展名分别为：
    .frm(存储表结构)
'''
 
# MyISAM与InnoDB两个引擎的区别:           
'''
<1> InnoDB支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要 哪个出错还可以回滚还原，而MyISAM就不可以了.
<2> MyISAM适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用.
<3> InnoDB支持外键，MyISAM 不支持
<4> InnoDB中不保存表的行数, InnoDB需要扫描一遍整个表来计算有多少行. MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表;
<5> 对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引;
<6> 清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表;
<7> InnoDB支持行锁(注意: 某些情况下还是锁整表)
'''

3. 验证: 指定不同存储引擎进行插入数据测试

# 查看所有支持的存储引擎
show engines;

# 查看正在使用的存储引擎
# storage  /ˈstɔːrɪdʒ/ 存储 储存 保管
show variables like 'storage_engine%';

drop table innodb_t1;
drop table myisam_t1;
drop table memory_t1;
drop table blackhole;

# 创建不同的表指定不同的存储引擎
create table innodb_t1(id int) engine=innodb;
create table myisam_t1(id int) engine=myisam;
create table memory_t1(id int) engine=memory;
create table blackhole_t1(id int) engine=blackhole;

# 插入数据
insert into innodb_t1 values(1);
insert into myisam_t1 values(1);
insert into memory_t1 values(1);
insert into blackhole_t1 values(1);

# 查看
select * from innodb_t1;
select * from myisam_t1;
select * from memory_t1;
select * from blackhole_t1;

# 结果
'''
mysql> select * from innodb_t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.07 sec)

mysql> select * from myisam_t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.07 sec)

mysql> select * from memory_t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.06 sec)

mysql> select * from blackhole_t1;
Empty set (0.07 sec)
'''

# 重启mysql或者重启机器后，表memory_t1内数据清空
[root@yangyang day45]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
mysql> select * from memory_t1;
Empty set (0.08 sec)

二. 表介绍

表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段

id，name，qq，age称为字段，其余的，一行内容称为一条记录

三. 创建表的完整语法

# 语法
create table 表名(
    字段名1 类型(宽度) 约束条件,
    字段名2 类型(宽度) 约束条件,
    字段名3 类型(宽度) 约束条件
);

# 注意
<1> 同一张表中字段名不能重复
<2> 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的. 
    约束条件写的话 也支持写多个: 字段名1 类型(宽度) 约束条件1 约束条件2...,
    reate table t5(id);  报错
    
<3> 最后一行指定不能有逗号
	create table t6(
        id int,
        name char, ---> 报错
    );   

"""补充1: 宽度"""
# 注意: 一般情况下指的是对存储数据的限制
# 1. char不指定宽度. 默认宽度是1
create table t1(name char);
insert into t1 values('jason');

# 2. 关键字NULL没有宽度的限制.
insert into t1 values(null);  

针对不同的版本会出现不同的效果
    5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符，那么会自动帮你截取.
    5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超，一旦超出范围立刻报错(Data too long for column 'name' at row 1.)
            
"""补充2: 严格模式到底开不开呢？"""
MySQL5.7之后的版本默认都是开启严格模式的
使用数据库的准则:
	能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力. 
	因为没有开启严格模式, 当遇到宽度超出时, 会进行额外的截取操作这种操作就会给数据库增加额外的压力. 换个角度来说, 本来数据的存储就不因该超出限制范围.

# 总结: 宽度和约束条件到底是什么关系?
"""
宽度是用来限制数据的存储. 
约束条件是在宽度的基础之上增加的额外的约束
"""    

四. 数据库的三种模式的指定

'''
variables  /'vɛrɪəbl/  变量
session  /ˈseʃn/ 会 话
trans  /trænz 反式
pad /pæd/  填补
substitution /ˌsʌbstɪˈtuːʃn/ 替代 换人 换船
strict trans tables  严格的反式表
no engine substitution 没有引擎提交
pad char to full length 填补字符到全长度
'''

"""查看当前模式的2种方式"""
# 方式一: 大范围查找. variables + 模糊匹配
show variables like "%mode";
    模糊匹配/查询: 关键字 like
        `%`:匹配任意多个字符
        `_`: 匹配任意单个字符
        
# 方式二: 定位查找
select @@sql_mode;

"""修改模式的2种方式"""
注意!!: 这种修改是替换操作, 如果想保留原来得模式并增添新得模式需要使用逗号隔开. 示例: 'strict_trans_tables,no_engine_substitution'

set global sql_mode = 'STRICT_TRANS_TABLES';
    set session  只在当前窗口有效
    set global   全局有效
	注意: 修改完之后 需要重新登录服务端.

五. 数据类型

六. 表完整性约束

七. 修改表: alter table(了解)

# 提示: 以下所有的约束条件可以指定多个.

# 修改表名
alter table 表名 rename 新表名;

# 增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;

# 删除字段
alter table 表名 drop 字段名;


# 修改字段
alter table 表明 modify 字段名 字段类型(宽度) 约束条件;
alter table 表明 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

八. 复制表: create table(了解)

"""
注意1: 复制不包含的key. 主键key,  外键key, 索引key
注意2: 如果通过where筛选的旧表中没有数据, 会生成一张空的表.
"""
create table 新表名 select * from 旧表名  
create table 新表名 select * from 旧表名 where id > 9999;

九. 删除表

DROP TABLE 表名;

十. 总结

# mysql四种存储引擎: InnoDB, MyISAM, MEMORY, BLACKHOLE
"""
InnoDB: 支持事务, 外键与约束, 行锁, 数据安全, 访问速度没有MyISAM快. 5.5版本后
    创建一个该模式的表生成2个文件. 后缀名: .frm, .idb
    
MyISAM: 不支持事务, 外键与约束, 行锁, 数据不太安全, 只是访问问数据快于InnoDB. 5.5版本前
    创建一个该模式的表生成3个文件. 后缀名: .frm, .MYD, .MYI
    
MEMORY: 内存
    创建一个该模式的表生成1个文件. 后缀名: .frm
    
BLACKHOLE: 垃圾回收站
    创建一个该模式的表生成1个文件. 后缀名: .frm
"""

# 了解3种模式
"""
普通模式: no_engine_substitution
严格模式: strict_trans_tables
取消剔除char空格模式: pad_char_to_full_length

注意: 模式的的修该方式是替换.

查看模式2种方式:
show variable like '%mode';
select @@sql_mode;

修改模式的2种方式:
set session sql_mode=模式;
set global sql_mode=模式;
"""