MySQL数据类型和约束条件

　一.数据库操作数据的存储引擎

INNODB:支持事务 行锁  外键 查询速度比MYSiam慢  但是保证了数据的安全性 5.1 版本之后
MYSIAM:老版本用 5.1版本之前 搜索速度快 不支持事务 没有外键

MEMERY:不需要存储数据表 只在内存缓存 redis 用作大批量数据的内存缓存

应用Redis实现数据的读写，同时利用队列处理器定时将数据写入mysql。同时要注意避免冲突，
在redis启动时去mysql读取所有表键值存入redis中，往redis写数据时，对redis主键自增并进行读取，
若mysql更新失败，则需要及时清除缓存及同步redis主键。这样处理，主要是实时读写redis，
而mysql数据则通过队列异步处理，
缓解mysql压力，不过这种方法应用场景主要基于高并发，
而且redis的高可用集群架构相对更复杂，对于数据的保存不是永久的,
一些数据要求不严格的大公司会用,一般不是很推荐。

blackhole:任何写入到此引擎的数据均会被丢弃掉，
　　  不做实际存储；
Select语句的内容永远是空 过滤数据 记录日志

　 数据结构区别:

Innodb
            目前5.1之后MySQL版本默认的存储引擎
            支持事务,行锁,外键
            由于上面的支持 数据更安全
            
            建表的时候innodb会产生两个文件
                一个是表结构文件
                一个是存储数据文件
        MyIsam
            5.1版本之前的MySQL的默认存储引擎
            查询速度较于Innodb要快
            建表的时候会产生三个文件
                一个是表结构文件
                一个是索引文件
                    索引你先把它理解成是书的目录,能够帮助你更快的查询数据
                一个是存储数据文件
        memory
            将数据存放于内存中 
            建表的时候都仅仅只有一个表结构文件
        blackhole
            任何写入的数据都会消失
            建表的时候都仅仅只有一个表结构文件

　　

 查询的内部的执行图

 

　二.数据类型

　　SQL语法:

　　　

一.创建表的完整语法
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件] 
);

　

　注意:
　　　　1.在同一张表中字段名不能相同
　　　　2.字段名和类型都是必须要的而宽度和约束条件都是可以选择的
　　　　3.最后一个字段不能加逗号
　补充:
　　　　宽度指的是对存储数据的限制

mysql> create  table user(age int not null);
        ERROR 1050 (42S01): Table 'user' already exists
        # 如果字段已经创建了 在创建其他字段是是不可以的
        # 创建的sql 语句是:alter table 表名(user) add(age int not null);

　2.约束调条件初识>>null与 not null

 往表中插入数据的时候  可以指定字段进行插入 不需要全部都插
         insert into t17(name,id) values('egon',2);
          
         如:
            mysql> insert into user values(1,'jasom',null);
            ERROR 1048 (23000): Column 'age' cannot be null
            之前在表设计的时候我们设计的是不能为空 
                alter table user(age int not null);

　3.总结:
　　　　1.类型与约束条件的区别
　　　　2.类型:限制字段必须以什么样的数据类型进行存储
　　　　3.约束条件:约束条件是在类型之外添加一种额外的限制

　基本数据类型

　　  - 整型

　　1.分类 TINYINT SMALLINT MEDIUMINT INT BIGINT

 　   2.作用:存储年龄,等级,id,各种号码

　　

类型存储范围：参考图片 

　

　(1)整型:

验证整形字段有无符号及范围
        mysql> insert into t2 values(127);
        mysql> insert into t2 values(-128);
        # 有符号整形范围:(-128,127) 
           +------+
           | x    |
           +------+
           |  127 |
           | -128 |
           +------+

　　# 无符号整形范围 

 mysql> create table t3 (x tinyint unsigned);


 mysql> insert into t3 values(0),(255);

　　# 无符号int 类型的最大位数

mysql> insert into t4 values(12345678901); 报错
        mysql> insert into t4 values(1234567890);
        范围(0, 4294967295)

　　3.疑问:类型后面的宽度能否改变字段存储的大小限制

mysql> create table t5(x int(8));


mysql> insert into t5 values (1234567890);OK 

　　4.# 显示时，不够8位用0填充，如果超出8位则正常显示

mysql> create table t6(x int(8) unsigned zero fill);
        mysql> insert into t6 values(1234567890);
        # 超出 只要在10个字范围内 都是正常显示
        # 不够的话用零填充
        mysql> insert into t6 values(1234);
        mysql> select*from t6;
        +------------+
        | x          |
        +------------+
        |  429496729 |
        | 1234567890 |
        |   00001234 |
        +------------+

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

　- 浮点型

　　分类:float double decimal  精准度

　　应用场景: 身高.体重.薪资

　　 字段限制特点(255,30)前一位表示所有的位数，后一位表示小数个数

　　三者最大整数位和小数位对比

　　

#存储限制
        float(255,30)
        double(255,30)
        decimal(65,30)

 　　精准度的验证

reate table t7(x float(255,30));

create table t8(x double(255,30));

create table t9(x decimal(65,30));

　insert into t7 values(1.111111111111111111111111111111);

　insert into t8 values(1.111111111111111111111111111111);

  insert into t9 values(1.111111111111111111111111111111);

　　结果:

mysql> select * from t7;
        +----------------------------------+
        | x                                |
        +----------------------------------+
        | 1.111111164093017600000000000000 |
        +----------------------------------+
        
       mysql> select * from t8;
        +----------------------------------+
        | x                                |
        +----------------------------------+
        | 1.111111111111111200000000000000 |
        +----------------------------------+
        
        mysql> select  * from t9;
        +----------------------------------+
        | x                                |
        +----------------------------------+
        | 1.111111111111111111111111111111 |
        +----------------------------------+

　　

　

总结:float <double< declmal 精准度越来越准确 
　　一般比较精确应用于航天 decimal 还有些直接用字符串存取
　　存多少就取多少

　　补充:严格模式

　　

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

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

        set global sql_mode ='STRICT_TRANS_TABLES'
        # 修改完之后退出当前客户端重新登陆即可
        ```

    - 字符类型(char与varchar)

　　　　类型:
　　　　char(定长)
　　　　varchar(变长)

create table t10(name char(4))  # 超出四个字符报错，不够四个字符空格补全

create table t11(name varchar(4))  # 超出四个字符报错，不够四个有几个就存几个

            # 验证存储限制
            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会自动将末尾的空格取掉"""
            # 如果不想让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

char
                # 优点:存取速度快
                直接按照固定的长度读取,比较方便
                # 缺点:浪费空间
                name char(4) 
                Alice egon tank jaso n

 varchar

 varchar  
                name varchar(4)
                varchar变长
                
                1.节省空间
                2.存取速度慢(较于char比较慢)
                    1bytes+egon 1bytes+alex 1bytes+lxx  1bytes+jxx  1bytes+txx
                    存的时候 需要给数据讲一个记录长度的报头
                    取的时候 需要先读取报头才能读取真实数据

     - 日期类型

　  五.时间类型:

 date:年 月 日 2019-8-19
           time:时 分 秒
           Datetime:年 月 日 时 分 秒 2019-8-19 11:30:10
           Year; 2019

实列:

　

　 insert into student values(1,'egon','2019','2019-05-    
    09','11:11:00','2019-         11-11 11:11:11');

   - 枚举与集合

　六.枚举与集合类型

　　  1 .枚举(enum)  限制某个字段能够存储的数据内容

　　　  enum 多选一 

　　　

列子:
        mysql> create table techer(id int,
        -> name char(32),
        -> gender enum('male','female','other'));
        Query OK, 0 rows affected (0.03 sec)

　　mysql> insert into techer values(1,'koko','male');

 mysql> select * from techer;
        +------+------+--------+
        | id   | name | gender |
        +------+------+--------+
        |    1 | koko | male   |
        +------+------+--------+

　　mysql> insert into techer values(1,'yyy',female');

mysql> select *from techer;
        +------+------+--------+
        | id   | name | gender |
        +------+------+--------+
        |    1 | koko | male   |
        |    2 | yyy  | female |
        +------+------+--------+
        

　　

　　2.集合(set) 限制某个字段能够存储的数据内容
　　set 可以单选 可以多选

1.mysql> create table student1(id int primary key auto_increment,
            ->  name char(32),
            ->  gender enum('male','female','other'),
            ->  hobby set('sing','dancing','read','play'));
                Query OK, 0 rows affected (0.03 sec)

　　插入:insert into student1(name,gender,hobby) values('koko','male','read,dancing,play');
　　Query OK, 1 row affected (0.00 sec)

　　

 mysql> select * from student1;;
                +----+------+--------+-------------------+
                | id | name | gender | hobby             |
                +----+------+--------+-------------------+
                |  1 | koko | male   | dancing,read,play |
                +----+------+--------+-------------------+
                1 row in set (0.00 sec)

　三.约束条件

  七.约束条件

1.PRIMARY KEY (PK)  

1.PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
            FOREIGN KEY (FK)    标识该字段为该表的外键
            NOT NULL    标识该字段不能为空
            UNIQUE KEY (UK)    标识该字段的值是唯一的
            AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
            DEFAULT    为该字段设置默认值

            UNSIGNED 无符号
            ZEROFILL 使用0填充

2.not null+default

 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')  # 成功

   3.unique

 ```mysql
            # 单列唯一
            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') # 成功

 联合唯一
            create table server(
                id int,
              ip char(16),
              port int,
              unique(ip,port)
            )

insert 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+auto_increment

 ```mysql
            # 单从约束角度来说primary key就等价于not null unique
            create table t11(id int primary key);
            desc t11;
            insert into t11 values(1),(1);  # 报错
            insert into t11 values(1),(2);

            # 除了约束之外，它还是innodb引擎组织数据的依据，提升查询效率

"""
强调:
1.一张表中必须有且只有一个主键，如果你没有设置主键，那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键
"""

create table t12(
              id int,
              name char(16),
              age int not null unique,
              addr char(16) not null unique
              )engine=innodb;
              desc t12;
            

"""
2.如果表里面没有指定任何的可以设置为主键的字段，那么innodb会采用自己默认的一个隐藏字段作为主键，隐藏意味着你在查询的时候无法根据这个主键字段加速查询了
索引：类似于书的目录，没有主键就相当于一页一页翻着查
3.一张表中通常都应该有一个id字段，并且通常将改id字段作成主键

"""
            # 联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键
            create table t18(
                ip char(16),
              port int,
              primary key(ip,port)
            );
            desc t18;

# 主键id作为数据的编号，每次最好能自动递增

create table t13(
                id int primary key auto_increment,
              name char(16)
            );

insert into t13('jason'),('jason'),('jason'); # id字段自动从1开始递增
# 注意：auto_increment通常都是加在主键上，并且只能给设置为key的字段加

```
补充:
delete from tb1;
强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0，
所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录
delete from tb1 where id > 10;

如果要清空表，使用truncate tb1；
作用：将整张表重置,id重新从0开始记录