存储引擎 索引 数据类型 约束条件 # 35

day35
0.补充 存储引擎

""""""
"""
存储引擎
    不同的数据应有不同的处理机制
    
    mysql 存储引擎
        Innodb:默认的存储引擎 查询速度较myisam慢 但是更安全(存储安全性比mysam高)
            特点:支持事务,支持行锁,外键
            目前更加看重数据安全
        myisam:mysql 老版本用的是存储引擎(存储速度比Innodb快)
        memory:内存引擎(数据全部存在内存中)
            特点:临时存储
        blackhole:无论存什么都立马消失(黑洞)
        
    研究一下每个存储引擎存取数据的特点
        show engines;
        
        为了不影响其他存储:
            create database db13;
            
            create table t1(id int)engine=innodb;
            create table t2(id int)engine=myisam;
            create table t3(id int)engine=memory;
            create table t4(id int)engine=blankhole;
            ps:
                myisam 真实数据和索引是分开的,Innodb合在一起存储在真实数据之中
            
            insert into t1 values(1),(2);
            insert into t2 values(1),(2);
            insert into t3 values(1),(2);
            ps:服务端重启之后,存在内存中数据丢失,查询无结果
            insert into t4 values(1),(2);
            ps:黑洞:存进去数据就消失了

"""

1. MySQL默认存储引擎的变迁

    在MySQL 5.5之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。

2. MyISAM与InnoDB存储引擎的主要特点

    1).MyISAM存储引擎的特点是：表级锁、不支持事务和支持全文索引，适合一些CMS内容管理系统作为后台数据库使用，但是使用大并发、重负荷生产系统上，表锁结构的特性就显得力不从心；

    2).InnoDB存储引擎的特点是：行级锁、事务安全（ACID兼容）、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、

                                                     回滚和崩溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

    注意：

    InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表(锁完以后，判断不符合条件的会逐步解锁)，

    例如update table set num=1 where name like “a%”。

     两种类型最主要的差别就是InnoDB支持事务处理与外键和行级锁。而MyISAM不支持。所以MyISAM往往就容易被人认为只适合在小项目中使用。

3. MyISAM与InnoDB性能测试

    随着CPU核数的增加，InnoDB的吞吐量反而越好，而MyISAM，其吞吐量几乎没有什么变化，显然，MyISAM的表锁定机制降低了读和写的吞吐量。

4. 事务支持与否

    MyISAM是一种非事务性的引擎，使得MyISAM引擎的MySQL可以提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用；

    InnoDB是事务安全的；事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

5. MyISAM与InnoDB构成上的区别

    1).每个MyISAM在磁盘上存储成三个文件：

        第一个文件的名字以表的名字开始，扩展名指出文件类型，.frm文件存储表定义。

       第二个文件是数据文件，其扩展名为.MYD (MYData)。

       第三个文件是索引文件，其扩展名是.MYI (MYIndex)。

   2).基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的 大小只受限于操作系统文件的大小，一般为 2GB。

6. MyISAM与InnoDB表锁和行锁的解释

    MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；

    而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

    InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的

     时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。

7. 是否保存数据库表中表的具体行数

     InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。

     注意的是，当count(*)语句包含where条件时，两种表的操作是一样的。也就是 上述“6”中介绍到的InnoDB使用表锁的一种情况。

8. 如何选择：

    MyISAM适合：

    1). 做很多count 的计算；

    2). 插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；

    3). 没有事务。

    InnoDB适合：

    1). 可靠性要求比较高，或者要求事务；

    2). 表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；

    3). 如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；

    4).DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；

   5).LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

9. 其他区别：

    1). 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

    2). DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

    3). LOAD TABLE FROMMASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

    4). 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

    5). 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

    6). InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

         要注意，创建每个表格的代码是相同的，除了最后的 TYPE参数，这一参数用来指定数据引擎。

区别.png

四大存储引擎.png

存储引擎第二知识点.存储位置及特点.png

1.创建表的完整语法,即约束认知 和 数据类型

""""""
"""
创建表的完整语法:
    create table 表名(
        列名1 类型[(宽度) 约束条件,
        列名2 类型[(宽度) 约束条件,
        ...
        列名n 类型[(宽度) 约束条件,
    );
    
ps:
    1.列名和列类型是必须的,中括号内的参数都是可选参数
    2.同一张表中,列名不能重复
    3.最后一列后面不能加逗号
        最简:
        create table t5(
            id int,
            name char
        );
 


"""

"""
宽度:
    使用数据库的准则:能尽量让它少干活就尽量少干活
    
    对存储数据的限制
    char(1) 只能存一个字符
    如果超了 mysql 会自动帮你截取
        1.插入的时候 mysql自动截取
        2.会直接报错(mysql严格模式)
        最新数据库版本直接报错提示无法存储:Data too long for column 'name' at row 1
    例:   
    alter table t5 modify name char not null
    not null 该字段不能插空
    
"""
"""
类型和中括号的约束
    类型约束的是数据的存储类型 
    而中括号内的约束 是基于 类型之上的额外限制
"""
"""
语法:
create table 表名(
        列名1 类型[(宽度) 约束条件,
        列名2 类型[(宽度) 约束条件,
        ...
        列名n 类型[(宽度) 约束条件,
    )engine=Inodb charset='utf-8';
    
    
    create table t15(
        id int auto_crement primary key,
        name varchar(32) not null default ""
    )engine=Innodb charset='utf-8';
"""

""""""
"""
字段类型
"""
"""
整型:
    smallint tinyint int bigint
    
    tinyint:
        默认是否有符号
    默认是带有符号的(-128,127)
    
    超出限制会如何?
        超出后只会存最大值或者最小值
        create table t6(id tinyint);
        
        not null 不能为空
        unsighed 无正负符号
        zerofill 0填充多余的位数
        create table t17(id int(8) unsighed zerofill);
        
       ps: char后面的数字是用来限制存储数据的长度
        特例:
            只有整型后面的数字不是用来限制存储数据的长度
            而是用来控制展示的数据位数
            int(8) 够/超8位有几位存几位,不够8位空格填充
            
        修改约束条件: 不够8位的情况下,用0填充
        
        强调:
            **对于整型来说,数据类型后的宽度并不是存储限制,
            而是显示限制,所以在创建表时,如果列类型采用的是整型类型,完全无需指定显示宽度,
            默认的显示宽度,足够显示完整当初存放的数据
            
            只要是整型 都不需要指定宽度 因为有默认的宽度 足够显示对应的数据
        
    
"""

""""""
"""模糊匹配"""
"""
    like :
        %匹配任意多个字符
        _匹配任意一个字符
        
    set session 临时有效 只在你当前操作的窗口有效
    
    set global 全局有效 终生有效
    
    set global sql_mode = 'STRICT_TABLES';
    设置完成后,你只需要重新退出客户端再次进入即可
    
"""

""""""
"""浮点型"""
"""
float(255,30)  总共255位 小数部分占30位
double(255,30) 总共255位 小数部分占30位
decimal(65,30) 总共65位 小数部分占30位

例:
create table t6(id float(255,30)); 
create table t7(id double(255,30));
create table t8(id decimal(65,30));

insert into t6 values(1.11111111111111111);
insert into t7 values(1.11111111111111111);
insert into t8 values(1.11111111111111111);
"""
"""
精确度对比:
    float < double < decimal
"""

""""""
"""字符类型"""
"""
    char(4) # 最大只能存四个字符 超出来会直接报错 如果少了 会自动用空格填充
    varchar(4) # 最大只能存四个字符 超出来会直接报错 如果少了 有几个存几个
    
    例:
    # 超出四个字符报错，不够四个字符空格补全
    create table t9(name char(4));
    # 超出四个字符报错，不够四个有几个就存几个
    create table t10(name varchar(4));
    
    char_length()
    
    # 验证存储限制
    insert into t12 values('hello');
    insert into t13 values('hello');
    # 验证存储长度
    insert into t12 values('a'); #'a    '
    insert into t13 values('a'); #'a'
    select * from t12
    select * from t13  # 无法查看真正的结果
    
    select char_length(name) from t12
    select char_length(name) from t13  # 仍然无法查看到真正的结果
    
    结论:
        mysql在存储char类型字段的时候 硬盘上确确实实存的是固定长度的数据
        
        但是再取出来的那一瞬间 mysql会自动将填充的空格去除
        
        可以通过严格模式 来修改机制 让其不做自动去除处理
    
    例:
    set global
    sql_mode='strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';
    退出客户端重新登陆:
        select char_length(x) from t12 #4
        select char_length(y) from t13; #1
    
    # 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格
    
    
    
"""
"""
    char 与 varchar 的区别
        char 定长
            1.浪费空间
            2.存取速度快
            
            取的时候方便:直接按固定的长度取即可
        varchar 变长
            1.节省空间
            2.存取速度慢(较于char比较慢)
                存的时候
            需要给数据讲一个记录长度的报头
                取的时候
            需要先读取报头才能的读取真实数据
            
        取的时候时候比较繁琐,无法知道具体数据到底有多长
        
"""

""""""
"""日期类型"""
"""
date
datetime
year
time
"""
"""枚举与集合类型"""
"""
    枚举(enum) 多选一
限制 某个字段能够存储的数据内容
    集合(set) 多选多
限制某个字段能够存储的数据内容
"""
"""
例:
create table userinfo(
    id int,
    name char(16),
    gender enum('male','female','other')
    );
    
insert into userinfo values(1,'llx','llx') # 报错,只能选'male','female','other'
insert into userinfo values(2,'llx','male')

create table teacher(
    id int,
    name char(16),
    gender enum('male','female','other'),
    hobby set('lll','xxx','kkkk')
)
insert into teacher values(1,'llx','male','lll,xxx,kkk')
insert into teacher values(1,'llx','male','lll')
"""

整型和浮点型.png

""""""
"""严格模式:即安全模式"""
"""
问题:
    设置char,tinyint,
    存储数据时超过它们的最大存储长度，
    发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。
    但在实际情况下，我们应该尽量减少数据库的操作，缓解数据库的压力，
    让它仅仅只管理数据即可，这样的情况下就需要设置"安全模式"
    
    # 查看数据库变量名中包含的 mode 配置参数
    show variables like "%mode";
    # 修改安全模式
    set session  # 只在当前界面操作有效
    set global # 全局有效
    
    set global sql_mode = 'STRIC_TRANS_TABLES',
    修改完之后退出 当前客户端重新登录即可
"""

2.约束

""""""
"""约束条件"""
"""
    PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
    FOREIGN KEY (FK)    标识该字段为该表的外键
    not null 不能为空
    unique 标识字段是唯一的
    auto_crement 自增
    unsigned 无符号
    zerofill 使用0填充
    default 给某个字段设置默认值(当用户写了的时候用"用户的",当用户没有写就用默认值)
    
    create table t11(id int ,name char(16) default 'jason');
    
    往表中插入数据的时候 可以指定字段进行插入 不需要全部都插入;
    
    insert into t11(name ,id) values('llx','2');
    


"""
"""
例:
create table user(
    id int,
  name char(16)
);
insert into user values(1,null)  # 可以修改

alter table user modify name char(16) not null;
insert into user(name,id) values(null,2);  # 报错 插入数据可以在表名后面指定插入数据对应的字段

create table student(
    id int,
  name char(16) not null,
  gender enum('male','female','others') default 'male'
)
insert into student(id,name) values(1,'jason')  # 成功
"""
"""
# alter table userinfo modify name char(16) not null;
"""

""""""
"""unique 唯一"""
"""
    单列唯一 限制某一个字段是唯一的联合唯一(在语句的最后用括号的形式 表示哪几个字段组合的结果是唯一的)
    
    create table user1(
    id int unique, 
  name char(16)
    );
    insert into user1 values(1,'jason'),(1,'egon')  # 报错
    insert into user1 values(1,'jason'),(2,'egon')  # 成功
    
"""
"""
联合唯一:
    ip
    port
    
    create table sever(
        id int,
        ip char,
        port int,
        unique(ip,port)
        );
    nsert into server values(1,'127.0.0.1',8080);
    insert into server values(2,'127.0.0.1',8080);  # 报错
    insert into server values(1,'127.0.0.1',8081);
"""

""""""
"""primary key 主键 = not null unique"""
"""
    限制效果 跟 not null + unique 组合效果一致  非空且唯一
    
    create table t18(id int primary key);
    desc t11;
    insert into t11 values(1),(1);  # 报错
    insert into t11 values(1),(2);
    
    
    primary key 也是innodb 引擎 查询必备的索引
        索引你就把当成书的目录
        
    innodb 引擎 在创建表的时候 必须要有一个主键
    当你没有指定主键的时候
        1.会将非空切唯一的字段自动升级主键
        2.当你的表中没有任何的约束条件 innodb会采用自己的内部默认的一个主键字段
            该主键字段你在查询时候是无法使用的
            查询数据的速度就会很慢
            类似于一页一页的翻书
            create table t12(
                id int,
                name char(16),
                age int not null unique,
                addr char(16) not null unique
                );
            
        主键字段到底设置给谁呢???
            通常每张表里面都应该有一个id字段
            并且应该将id设置为表的主键字段
        
        
    
"""
"""
联合主键:
    多个字段联合起来作为表的一个主键,本质还是主键!!!
    ps:innodb 引擎中一张表有且只有一个主键
    
    create table t13(
        ip char(16),
        port int,
        primary key(ip,port)
        );
        
        desc t13;
        
    主键字段 应该具备自动递增的特点
        每次添加数据,不需要用户手动输入
        auto_crement 自动递增
    create table t14(
        id int auto_crement primary key,
        name varchar(32)
        );
    delete from 仅仅是删除数据 不会重置主键
    新创会接着原先ID继续
    
    truncate 初始化表 会重置主键 id从0开始记录
"""

01 上周内容回顾.mp4
02 存储引擎.mp4
03 创建表的完整性约束.mp4
04 整型类型.mp4
05 严格模式.mp4
06 浮点型.mp4
07 字符类型.mp4
08 日期类型.mp4
09 枚举与集合类型.mp4
10 约束条件.mp4