35.创建表的各类知识点

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

1.存储引擎
      不同的数据应该有不同的处理机制

      mysql存储引擎
         Innodb:默认的存储引擎  查询速度较myisam慢  但是更安全
         myisam:mysql老版本用的存储引擎
         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:memory服务端重启之后,存在内存中数据丢失,查询无结果
             insert into t4 values(1),(2);
             ps:blankhole黑洞:存进去数据就消失了


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

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

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

                 2).InnoDB存储引擎的特点是：行级锁、事务安全（ACID兼容）、支持外键、不支持FULLTEXT(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(load table from master)操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

         9. 其他区别：

             1). 对于AUTO_INCREMENT(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参数，这一参数用来指定数据引擎。

2.创建表的完整语法,即约束认知 和 数据类型
    1.创建表的完整语法
        create table 表名(
            列名1 类型[(宽度) 约束条件],
            列名2 类型[(宽度) 约束条件],
            ...
            列名n 类型[(宽度) 约束条件]
        );

        ps:
             1.列名和列类型是必须的,中括号内的参数都是可选参数
             2.同一张表中,列名不能重复
             3.最后一列后面不能加逗号

        最简:
            create table t6(
               id int,
               name char
            );
        宽度:
          使用数据库的准则:能尽量让它少干活就尽量少干活

      对存储数据的限制
          char(1)  只能存一个字符
          如果超了 mysql会自动帮你截取
                1.插入的时候 mysql自动截取
                2.会直接报错(mysql严格模式)


          alter table t5 modify name char not null;
          not null该字段不能插空



   类型和中括号内的约束
      类型约束的是数据的存储类型
      而约束是基于类型之上的额外限制

语法
    create table t8(
        id int auto_increment primary key,
        name varchar(32) not null default ""
    )engine=Innodb charset='utf-8';


3.字段类型:
    1.整型: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填充

         强调:
             **对于整型来说,数据类型后的宽度并不是存储限制,
             而是显示限制,所以在创建表时,如果列类型采用的是整型类型,完全无需指定显示宽度,
             默认的显示宽度,足够显示完整当初存放的数据

             只要是整型 都不需要指定宽度 因为有默认的宽度 足够显示对应的数据

4.模糊匹配

      like :
          %匹配任意多个字符
          _匹配任意一个字符

      set session 临时有效 只在你当前操作的窗口有效

     set global 全局有效 终生有效

     set global sql_mode = 'STRICT_TABLES';
     设置完成后,你只需要重新退出客户端再次进入即可


5.浮点型
"""
  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

6.字符类型
 """
      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比较慢)
                 存的时候
             需要给数据讲一个记录长度的报头
                 取的时候
             需要先读取报头才能的读取真实数据
         取的时候时候比较繁琐,无法知道具体数据到底有多长

"""
7.严格模式:
""""""
 """严格模式:即安全模式"""
  """
  问题:
      设置char,tinyint,
     存储数据时超过它们的最大存储长度，
     发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。
      但在实际情况下，我们应该尽量减少数据库的操作，缓解数据库的压力，
     让它仅仅只管理数据即可，这样的情况下就需要设置"安全模式"

     # 查看数据库变量名中包含的 mode 配置参数
     show variables like "%mode";
     # 修改安全模式
     set session  # 只在当前界面操作有效
     set global # 全局有效

     set global sql_mode = 'STRIC_TRANS_TABLES',
            stric_trans_tables()
     修改完之后退出 当前客户端重新登录即可

8.约束条件:
    1.约束条件
        PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
        (foreign key)FOREIGN KEY (FK)    标识该字段为该表的外键

        not null 不能为空
        unique 表示字段是唯一的
        auto_increment 自增
        unsigned 无符号
        zerofill zerofill 使用0填充
        default 给某个字段设置默认值(当用户写了的时候用"用户的",当用户没有写就用默认值)
        create table t11(id int ,name char(16) default 'jason');
        往表中插入数据的时候 可以指定字段进行插入 不需要全部都插入;
        insert into t11(name ,id) values('llx','2');
        alter table userinfo modify name char(16) not null;
    2.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')  # 成功

              """
15 联合唯一:
         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);

    3.primary key 主键 not null unique
         限制效果 跟 not null + unique 组合效果一致  非空且唯一
         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_increment 自动递增
             create table t14(
                 id int auto_crement primary key,
                 name varchar(32)
                 );
             delete from 仅仅是删除数据 不会重置主键
             新创会接着原先ID继续

             truncate 初始化表 会重置主键 id从0开始记录

    4.时间类型

        * 分类

          * date：2019-05-01
          * time：11:11:11
          * Datetime: 2019-01-02 11:11:11
          * Year:2019

        * 测试

          ```mysql
          create table student(
            id int,
            name char(16),
            born_year year,
            birth date,
            study_time time,
            reg_time datetime
          );
          insert into student values(1,'egon','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');
          ```
    5.枚举enum与集合set类型

        * 分类
          * 枚举enum 多选一
          * 集合set 多选多

        * 测试

          ```mysql
          create table user(
            id int,
            name char(16),
            gender enum('male','female','others')
          );
          insert into user values(1,'jason','xxx')  # 报错
          insert into user values(2,'egon','female')  # 正确！


          create table teacher(
            id int,
            name char(16),
            gender enum('male','female','others'),
            hobby set('read','sleep','sanna','dbj')
          );
          insert into teacher values(1,'egon','male','read,sleep,dbj')  # 集合也可以只存一个
          ```