Enum类型
create database EnumTypeStudy;
use EnumTypeStudy;
create table EnumTypes(etype enum('男','女','保密'));
insert into Enumtypes values('男');
select *from enumtypes;
insert into Enumtypes values(1);
insert into Enumtypes values(2);
insert into Enumtypes values(3);
select *from enumtypes;
select etype + 0 from EnumTypes;
create table EnumTypes1(etype enum('男','女','保密'), name varchar(32));
insert into EnumTypes1(name) values("张三");
select *from EnumTypes1;
create table EnumTypes2(etype enum('男','女','保密')not null, name varchar(32));
insert into EnumTypes2(name) values("张三");
select *from EnumTypes2;
insert into EnumTypes2 values('女',"李四");
select *from EnumTypes2;
set类型
create database setTypeStudy;
use setTypeStudy;
create table setTypes(stypes set('唱','跳','rap','篮球'));
insert into settypes values("唱,跳");
select* from settypes;
insert into settypes values("rap,跳");
select* from settypes;
insert into settypes values("篮球,rap");
select* from settypes;
select stypes+0 from settypes;
select bin(stypes+1) from settypes;
insert into settypes values("唱,跳,rap");
select bin(stypes+1) from settypes;
insert into settypes values("唱,跳,rap,篮球");
select bin(stypes+1) from settypes;
mysql> insert into settypes values("唱,跳");
Query OK, 1 row affected (0.01 sec)
mysql> select* from settypes;
+--------+
| stypes |
+--------+
| 唱,跳 |
+--------+
1 row in set (0.00 sec)
mysql> insert into settypes values("rap,跳");
Query OK, 1 row affected (0.01 sec)
mysql> select* from settypes;
+--------+
| stypes |
+--------+
| 唱,跳 |
| 跳,rap |
+--------+
2 rows in set (0.00 sec)
mysql>
mysql> select stypes+0 from settypes;
+----------+
| stypes+0 |
+----------+
| 3 |
| 6 |
| 12 |
+----------+
3 rows in set (0.00 sec)
mysql> select bin(stypes+1) from settypes;
+---------------+
| bin(stypes+1) |
+---------------+
| 100 |
| 111 |
| 1101 |
+---------------+
3 rows in set (0.00 sec)
mysql> select bin(stypes+1) from settypes;
+---------------+
| bin(stypes+1) |
+---------------+
| 100 |
| 111 |
| 1101 |
| 1000 |
+---------------+
4 rows in set (0.00 sec)
mysql> select stypes+0 from settypes;
+----------+
| stypes+0 |
+----------+
| 3 |
| 6 |
| 12 |
| 7 |
+----------+
4 rows in set (0.00 sec)
mysql> insert into settypes values("唱,跳,rap,篮球");
Query OK, 1 row affected (0.01 sec)
mysql> select bin(stypes+1) from settypes;
+---------------+
| bin(stypes+1) |
+---------------+
| 100 |
| 111 |
| 1101 |
| 1000 |
| 10000 |
+---------------+
5 rows in set (0.00 sec)
mysql> select stypes+0 from settypes;
+----------+
| stypes+0 |
+----------+
| 3 |
| 6 |
| 12 |
| 7 |
| 15 |
+----------+
5 rows in set (0.00 sec)
mysql> delete from settypes;
Query OK, 5 rows affected (0.01 sec)
mysql> insert into settypes values("唱,跳");
Query OK, 1 row affected (0.01 sec)
mysql> select* from settypes;
+--------+
| stypes |
+--------+
| 唱,跳 |
+--------+
1 row in set (0.00 sec)
mysql> insert into settypes values("rap,跳");
Query OK, 1 row affected (0.01 sec)
mysql> select* from settypes;
+--------+
| stypes |
+--------+
| 唱,跳 |
| 跳,rap |
+--------+
2 rows in set (0.00 sec)
mysql>
mysql> insert into settypes values("篮球,rap");
Query OK, 1 row affected (0.01 sec)
mysql> select* from settypes;
+----------+
| stypes |
+----------+
| 唱,跳 |
| 跳,rap |
| rap,篮球 |
+----------+
3 rows in set (0.00 sec)
mysql>
mysql> select stypes+0 from settypes;
+----------+
| stypes+0 |
+----------+
| 3 |
| 6 |
| 12 |
+----------+
3 rows in set (0.00 sec)
mysql> select bin(stypes+1) from settypes;
+---------------+
| bin(stypes+1) |
+---------------+
| 100 |
| 111 |
| 1101 |
+---------------+
3 rows in set (0.00 sec)
mysql> insert into settypes values("唱,跳,rap");
Query OK, 1 row affected (0.01 sec)
mysql> select bin(stypes+1) from settypes;
+---------------+
| bin(stypes+1) |
+---------------+
| 100 |
| 111 |
| 1101 |
| 1000 |
+---------------+
4 rows in set (0.00 sec)
mysql> insert into settypes values("唱,跳,rap,篮球");
Query OK, 1 row affected (0.01 sec)
mysql> select bin(stypes+1) from settypes;
+---------------+
| bin(stypes+1) |
+---------------+
| 100 |
| 111 |
| 1101 |
| 1000 |
| 10000 |
+---------------+
5 rows in set (0.00 sec)