暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL数据类型 - （8）

MySQL数据类型 - （8）

武金夆 2021-06-11
321

不积跬步无以至千里，不积小流无以成江海。


枚举类型

枚举类型，字段值只能在列举范围选择


格式：

字段名 enum(值1，值2，值N) 单选

字段名 set(值1，值2，值N) 多选


    mysql> create table t25(
        -> name char(10),
        -> sex enum("boy","girl"),
        -> likes set ("book","game","music","file")
        -> );
    Query OK, 0 rows affected (0.50 sec)
    mysql>


    mysql> desc t25;
    +-------+-----------------------------------+------+-----+---------+-------+
    | Field | Type                              | Null | Key | Default | Extra |
    +-------+-----------------------------------+------+-----+---------+-------+
    | name  | char(10)                          | YES  |     | NULL    |       |
    | sex   | enum('boy','girl')                | YES  |     | NULL    |       |
    | likes | set('book','game','music','file') | YES  |     | NULL    |       |
    +-------+-----------------------------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)


    mysql>


    mysql> insert into t25 values ("lucy","boy","book,file");
    Query OK, 1 row affected (0.08 sec)


    mysql> select * from t25;
    +------+------+-----------+
    | name | sex  | likes     |
    +------+------+-----------+
    | lucy | boy  | book,file |
    +------+------+-----------+
    1 row in set (0.00 sec)
    mysql> insert into t25 values ("revon","1","book,file");    
                                  //1代表枚举类型里面的第一个数值
    Query OK, 1 row affected (0.08 sec)


    mysql> select * from t25;
    +-------+------+-----------+
    | name  | sex  | likes     |
    +-------+------+-----------+
    | lucy  | boy  | book,file |
    | revon | boy  | book,file |
    +-------+------+-----------+
    2 rows in set (0.00 sec)


    mysql> insert into t25 values ("jason","2","book,file");
    Query OK, 1 row affected (0.09 sec)


    mysql> select * from t25;
    +-----------+------+-----------+ 
    | name      | sex  | likes     |
    +-----------+------+-----------+
    | lucy      | boy  | book,file |
    | revon     | boy  | book,file |
    | jason     | girl | book,file |
    +-----------+------+-----------+
    3 rows in set (0.00 sec)


    mysql>



    约束条件

    下面的Null，Key，Default，Extra统称约束条件

      mysql> desc t24;
      +-------+---------------------------+------+-----+---------+-------+
      | Field | Type                      | Null | Key | Default | Extra |
      +-------+---------------------------+------+-----+---------+-------+
      | age   | int(10) unsigned zerofill | YES  |     | NULL    |       |
      +-------+---------------------------+------+-----+---------+-------+
      1 row in set (0.00 sec)


      mysql>


      作用：限制如何给字段赋值

       

      创建表t26，name不允许空，年龄无符号数值型，性别不允许空，默认值boy，爱好默认值book，film

        mysql> create table t26( name char(10) not null, age tinyint(2) unsigned default 25,sex enum("boy","girl") not null default "boy", likes set ("book","game","music","film") default "book,film" );
        Query OK, 0 rows affected (0.41 sec)


        mysql> desc t26;
        +-------+-----------------------------------+------+-----+-----------+-------+
        | Field | Type                              | Null | Key | Default   | Extra |
        +-------+-----------------------------------+------+-----+-----------+-------+
        | name  | char(10)                          | NO   |     | NULL      |       |
        | age   | tinyint(2) unsigned               | YES  |     | 25        |       |
        | sex   | enum('boy','girl')                | NO   |     | boy       |       |
        | likes | set('book','game','music','film') | YES  |     | book,film |       |
        +-------+-----------------------------------+------+-----+-----------+-------+
        4 rows in set (0.00 sec)


        mysql> insert into t26(name) values("bob");
        Query OK, 1 row affected (0.05 sec)


        mysql>
        mysql> select * from t26;
        +------+------+-----+-----------+
        | name | age  | sex | likes     |
        +------+------+-----+-----------+
        | bob  |   25 | boy | book,film |
        +------+------+-----+-----------+
        1 row in set (0.00 sec)
        mysql>


        name字段不允许位空NULL，所有insert数据的时候，如果不给name字段赋值会报错


          mysql> insert into t26 (age,sex,likes) values(30,"girl","game");
          ERROR 1364 (HY000): Field 'name' doesn't have a default value
          mysql>


          给name字段赋值=null也不允许

            mysql> insert into t26 (name,age,sex,likes) values(null,30,"girl","game");
            ERROR 1048 (23000): Column 'name' cannot be null
            mysql>


            但是给字段name赋值"null"或者""是可以的

              mysql> insert into t26 (name,age,sex,likes) values("null",30,"girl","game");
              Query OK, 1 row affected (0.05 sec)


              mysql>
              mysql> insert into t26 (name,age,sex,likes) values("",30,"girl","game");
              Query OK, 1 row affected (0.16 sec)


              mysql> select * from t26;
              +------+------+------+-------+
              | name | age  | sex  | likes |
              +------+------+------+-------+
              | null |   30 | girl | game  |
              |      |   30 | girl | game  |
              +------+------+------+-------+
              2 rows in set (0.00 sec)
              mysql>


              Practice makes perfect

              MySQL安装/基本配置 - （1）

              MySQL基本使用 - （2）

              MySQL基本使用 - （3）

              MySQL基本使用 - （4）
              MySQL基本使用 - （5）
              MySQL基本使用 - （6）
              MySQL基本使用 - （7）


              mysql
              文章转载自武金夆，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

              评论