• mysql数据类型


    一、引擎

     

    前提: 引擎是建表是规定, 它是提供给表使用的, 而不是数据库。

    mysql> show engines; # 展示所有引擎
    》》》》结果:
    ----------------+--------------+------+------------+
    | Engine             | Support | Comment
                    | Transactions | XA   | Savepoints |
    +--------------------+---------+-----------------------------------------------
    ----------------+--------------+------+------------+
    | FEDERATED          | NO      | Federated MySQL storage engine
                    | NULL         | NULL | NULL       |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables
                    | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine
                    | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)
                    | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine
                    | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for tempo ary tables
                    | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine
                    | NO           | NO   | NO         |
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and oreign keys
                    | YES          | YES  | YES        |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema
                    | NO           | NO   | NO         |
    +--------------------+---------+-----------------------------------------------
    ​
    # 重点: 
    # innodb(默认): 支持事务, 行级锁, 外键
    # myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库
    ​
    mysql> use db1;
    ##创建各种类型引擎的表:
    mysql> create table t1(id int)engine=innodb;
    mysql> create table t2(id int)engine=myisam;
    mysql> create table t3(id int)engine=blackhole; #写入的数据马上消失
    mysql> create table t4(id int)engine=memory;#数据只存在内存
    ​
    insert into t1 values(1);
    insert into t2 values(1);
    insert into t3 values(1);
    insert into t4 values(1);
    ​
    select * from t1; ...
    View Code
     

    二、创建表的完整的语法

    '''
    create table 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
    )engine=innodb charset=utf8; #默认引擎为innodb,设置了配置文件后的默认编码为utf8。
    '''# []可选参数
    # create table db1.t1(name char(3) not null); [(3)宽度 not null约束条件]
    # 数据插入时,name不能为空(null), 且最长只能存放三个字符
    # 总结: 宽度和约束条件为可选参数, 用来限制存放数据的规则;字段名和类型是必须的;在同一张表中,字段名是不能相同。
    ###创建表
    mysql> create table t1(
        -> id int,
        -> name varchar(50),
        -> sex enum('male','female'),
        -> age int(3));
    Query OK, 0 rows affected (0.60 sec)
    ​
    mysql> show tables;
    +---------------+
    | Tables_in_db1 |
    +---------------+
    | t1            |
    +---------------+
    1 row in set (0.00 sec)
    ​
    mysql> desc t1;
    +-------+-----------------------+------+-----+---------+-------+
    | Field | Type                  | Null | Key | Default | Extra |
    +-------+-----------------------+------+-----+---------+-------+
    | id    | int(11)               | YES  |     | NULL    |       |
    | name  | varchar(50)           | YES  |     | NULL    |       |
    | sex   | enum('male','female') | YES  |     | NULL    |       |
    | age   | int(3)                | YES  |     | NULL    |       |
    +-------+-----------------------+------+-----+---------+-------+
    4 rows in set (0.03 sec)

    三、数据库的模式

    # sql_mode: 反映数据库的全局变量
    # 数据库模式限制的是客户端对服务器操作数据的方式(是否严格)
    # 两种模式
    no_engine_substitution:非安全性,默认 #非数据安全,即在限定字段长度的情况下后,存入该字段的数据超出长度后,仍能截断存入不发生报错,造成数据不全。
    strict_trans_tables:安全模式 #数据安全,即在限定字段长度的情况下后,存入该字段的数据超出长度后,就会报错。
    # 查看当前数据库模式:
    show variables like "%sql_mode%"; # %匹配0~n个任意字符 => 模糊查询
    # 设置为安全模式
    set global sql_mode="strict_trans_tables";
    ​
    # 重启连接(客户端)
    quit
    ​
    # 应用
    create table t1(name char(2)); #表示name字段下的字符长度为2个字符。
    insert into t1 values ("ab") # 正常
    insert into t1 values ("zero") # 错误 Data too long for column 'name' at row 1

    四、数据类型

    mysql数据库支持存放的数据:整型、浮点型、字符型、时间类型、枚举型、集合类型

    1、整型

    '''类型
    任意类型下都分为有符号型和无符号型,类型本身不带符号,能够区分是用字节首位进行标识。
    tinyint:1字节(8位二进制) signed -128~127 *  unsigned 0~255
    smallint:2字节
    mediumint:3字节
    int:4字节 signed -2147483648~2147483647 *   unsigned 0~4294967295
    bigint:8字节
    '''
    '''约束 *
    unsigned:无符号
    zerofill:0填充
    '''
    # 不同类型所占字节数不一样, 决定所占空间及存放数据的大小限制
    # eg:
    create table t8(x tinyint);  # 默认为signed
    insert into t8 values(200);  # 非安全模式存入,值只能到最大值127
    select (x) from t8;
    ​
    ​
    '''宽度
    1.不能决定整型存放数据的宽度, 超过宽度可以存放, 最终由数据类型所占字节决定
    2.如果没有超过宽度,且有zerofill限制, 会用0填充前置位的不足位
    3.没有必要规定整型的宽度, 默认设置的宽度就为该整型能存放数据的最大宽度 *
    '''
    ​
    ​
    # eg:1
    create table t9(x int(5)); #定义的宽度5为显示宽度,一般使用系统默认的
    insert into t9 values(123456); 
    select (x) from t9; # 结果: 123456
    insert into t9 values(2147483648); 
    select (x) from t9; # 结果: 2147483647
    insert into t9 values(10); 
    select (x) from t9; # 结果: 10
    # eg:2
    create table t10(x int(5) unsigned zerofill); # 区域0~4294967295
    insert into t10 values(10); 
    select x from t10; # 结果: 00010
    insert into t10 values(12345678900); 
    select x from t10; # 结果: 4294967295

    注意:为该类型指定宽度时,仅仅只是指定查询结果的显示宽度,与存储范围无关,存储范围如下 其实我们完全没必要为整数类型指定显示宽度,使用默认的就可以了 默认的显示宽度,都是在最大值的基础上加1

    2、浮点型

    '''类型
    float:4字节,3.4E–38~3.4E+38 *
    double:8字节,1.7E–308~1.7E+308
    decimal:M,D大值基础上+2
    '''
    '''宽度:
    限制存储宽度
    (M, D) => M为位数,D为小数位
    float(255, 30):精度最低,最常用
    double(255, 30):精度高,占位多
    decimal(65, 30):字符串存,全精度
    '''
    # eg:1
    create table t11 (age float(256, 30)); # Display width out of range for column 'age' (max = 255)
    create table t11 (age float(255, 31)); # Too big scale 31 specified for column 'age'. Maximum is 30.
    # eg:2
    create table t12 (x float(255, 30));
    create table t13 (x double(255, 30));
    create table t14 (x decimal(65, 30));
    ​
    insert into t12 values(1.11111111111111111111);
    insert into t13 values(1.11111111111111111111);
    insert into t14 values(1.11111111111111111111);
    ​
    select * from t12; # 1.111111164093017600000000000000 => 小数据,精度要求不高, 均采用float来存储 *
    select * from t13; # 1.111111111111111200000000000000
    select * from t14; # 1.111111111111111111110000000000
    ​
    alter table t14 modify x decimal(10, 5); # 1.11111 => 限制了数据的存储宽度

    3、字符型

    '''类型
    char:定长
    varchar:不定长
    '''
    '''宽度
    限制存储宽度
    char(4):以4个字符存储定长存储数据
    字符长度范围:0-255(一个中文是一个字符,是utf8编码的3个字节)
        存储:
            存储char类型的值时,会往右填充空格来满足长度
            例如:指定长度为10,存>10个字符则报错,存<10个字符则用空格填充直到凑够10个字符存储
        检索:
            在检索或者说查询时,查出的结果会自动删除尾部的空格,除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';)
            
    varchar(4):数据长度决定字符长度,为可变长度存储数据
    字符长度范围:0-65535(如果大于21845会提示用其他类型 。mysql行最大限制为65535字节,字符编码为utf-8)
        存储:
            varchar类型存储数据的真实内容,不会用空格填充,如果'ab  ',尾部的空格也会被存起来
            强调:varchar类型会在真实数据前加1-2Bytes的前缀,该前缀用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字,正好符合mysql对row的最大字节限制,即已经足够使用)
            如果真实的数据<255bytes则需要1Bytes的前缀(1Bytes=8bit 2**8最大表示的数字为255)
            如果真实的数据>255bytes则需要2Bytes的前缀(2Bytes=16bit 2**16最大表示的数字为65535)
        
        检索:
            尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容
    '''
    # eg:
    create table t15 (x char(4), y varchar(4));
    insert into t15 values("zero", 'owen'); # '' | "" 均可以表示字符
    select x,y from t15; # 正常
    insert into t15 values("yanghuhu", 'lxxVSegon'); # 非安全模式数据丢失,可以存放, 安全模式报错
    select x,y from t15; # 可以正常显示丢失后(不完整)的数据
    insert into t15 values('a', 'b');
    ​
    # 验证数据所在字符长度
    # 前提: 安全模式下以空白填充字符
    set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
    # 重启连接
    select char_length(x), char_length(y) from t15; # a占4 b占1
    '''重点: 存储数据的方式 **  => 数据库优化
    char: 一定按规定的宽度存放数据, 以规定宽度读取数据, 通常更占空间
    varchar: 首先根据数据长度计算所需宽度, 并在数据开始以数据头方式将宽度信息保存起来, 是一个计算耗时过程, 取先读取宽度信息,以宽度信息为依准读取数据, 通常节省空间
    '''
    8: zero    egon    lxx     yanghuhu
    8: 4zero4egon3lxx8yanghuhu
    注: varchar的数据头占1~2字节
        规定char|varchar宽度均为4,用来存放4个字符的数据, char存取更高效,char占4字符,varchar占5字符,char更省空间
    ​
    总结: 数据长度相近的数据提倡用char来存放数据, 数据需要高速存取,以空间换时间, 采用char

    4、时间类型

    '''类型
    year:yyyy(1901/2155)
    date:yyyy-MM-dd(1000-01-01/9999-12-31)
    time:HH:mm:ss
    datetime:yyyy-MM-dd HH:mm:ss(1000-01-01 00:00:00/9999-12-31 23:59:59)
    timestamp:yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038-01-19 ??)
    '''
    # eg: 1
    create table t16(my_year year, my_date date, my_time time);
    insert into t16 values(); # 三个时间类型的默认值均是null
    insert into t16 values(2156, null, null); # 在时间范围外,不允许插入该数据
    insert into t16 values(1, '2000-01-01 12:00:00', null); # 2001 2000-01-01 null
    insert into t16 values(2019, '2019-01-08', "15-19-30"); # time报格式错误 => 按照时间规定格式存放数据
    ​
    alter table t16 change my_year myYear year(2); # 时间的宽度修改后还是采用默认宽度 => 不需要关系宽度
    ​
    ​
    # eg:2
    create table t17(my_datetime datetime, my_timestamp timestamp);
    insert into t17 values(null, null); # 可以为空, 不能为null,赋值null采用默认值current_timestamp
    insert into t17 values('4000-01-01 12:00:00', '2000-01-01 12:00:00'); # 在各自范围内可以插入对应格式的时间数据
    # datetime VS timestamp
    datetime:时间范围,不依赖当前时区,8字节,可以为null
    timestamp:时间范围,依赖当前时区,4字节,有默认值CURRENT_TIMESTAMP

    5、枚举与集合

    '''类型
    enum:单选
    set:多选
    '''
    ​
    create table t19(
        sex enum('male','female','wasai') not null default 'wasai', # 枚举
        hobbies set('play','read','music') # 集合
    );
    ​
    insert into t19 values (null, null); # sex不能设置null
    insert into t19 values (); # wasai null
    insert into t19 (hobbies) values ('play,read'), ('music,play'); # sex采用默认值, 对hobbies字段添加两条记录
    insert into t19 (sex,hobbies) values ('male,female', 'play'); # sex字段只能单选

    五、约束条件

    """
    primary key:主键,唯一标识,表都会拥有,不设置为默认找第一个 不空,唯一 字段,未标识则创建隐藏字段
    foreing key:外键
    unique key:唯一性数据, 该条字段的值需要保证唯一,不能重复
    ​
    auto_increment:自增,只能加给key字段辅助修饰
    ​
    not null:不为空
    default:默认值
    ​
    unsigned:无符号
    zerofill:0填充
    """
    ​
    注:
    1.键是用来讲的io提供存取效率
    2.联合唯一
    create table web (
        ip char(16),
        port int,
        unique(ip,port)
    );
    3.联合主键
    create table web (
        ip char(16),
        port int,
        primary key(ip,port)
    );
    ​
    # eg:1
    # 单列唯一
    create table t20 (
        id int unique
    );
    # 联合唯一
    create table web (
        ip char(16),
        port int,
        unique(ip,port)
    );
    # 如果联合两个字段,两个字段全相同才相同,否则为不同
    insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);
    ​
    # 注: 
    # 1.表默认都有主键, 且只能拥有一个主键字段(单列主键 | 联合主键)
    # 2.没有设置主键的表, 数据库系统会自上而下将第一个规定为unique not null字段自动提升为primary key主键
    mysql> create table t6(age int not null unique);
    Query OK, 0 rows affected (0.23 sec)
    ​
    mysql> desc t6;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | age   | int(11) | NO   | PRI | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    1 row in set (0.01 sec)
    ​
    mysql> create table t7(age int unique);
    Query OK, 0 rows affected (0.36 sec)
    ​
    mysql> desc t7;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | age   | int(11) | YES  | UNI | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    1 row in set (0.01 sec)
    ​
    # 3.如果整个表都没有unique not null字段且没有primary key字段, 系统会默认创建一个隐藏字段作为主键
    # 4.通常必须手动指定表的主键, 一般用id字段, 且id字段一般类型为int, 因为int类型可以auto_increment
    # eg:2
    create table t21(id int auto_increment); # 自增约束必须添加给key的字段
    # eg:3
    create table t21(id int primary key auto_increment); # 自增要结合key,不赋值插入,数据会自动自增, 且自增的结果一直被记录保留
    # eg:4
    # 联合主键
    create table t22(
        ip char(16),
        port int,
        primary key(ip,port)
    );
    # 如果联合两个字段,两个字段全相同才相同,否则为不同
    insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);
    单列做主键
    ==================多列做主键================
    create table service(
    ip varchar(15),
    port char(5),
    service_name varchar(10) not null,
    primary key(ip,port)
    );
    ​
    mysql> desc service;
    +--------------+-------------+------+-----+---------+-------+
    | Field        | Type        | Null | Key | Default | Extra |
    +--------------+-------------+------+-----+---------+-------+
    | ip           | varchar(15) | NO   | PRI | NULL    |       |
    | port         | char(5)     | NO   | PRI | NULL    |       |
    | service_name | varchar(10) | NO   |     | NULL    |       |
    +--------------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    ​
    mysql> insert into service values
        -> ('172.16.45.10','3306','mysqld'),
        -> ('172.16.45.11','3306','mariadb')
        -> ;
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    ​
    mysql> insert into service values ('172.16.45.10','3306','nginx');
    ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
    多列做主键

     

  • 相关阅读:
    Java面向对象之内部类(匿名内部类)
    Java面向对象之内部类(访问格式)
    Java面向对象之USB接口实例
    Java面向对象之多态(成员访问特点) 入门实例
    Java面向对象之多态(向上、向下转型) 入门实例
    Java面向对象之接口interface 入门实例
    Ansible 的安装
    Windows server 2016安装Docker EE
    Docker-py 的使用
    flask 上传文件
  • 原文地址:https://www.cnblogs.com/peng-zhao/p/10241351.html
Copyright © 2020-2023  润新知