枚举类型,字段值只能在列举范围选择
字段名 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>