set:用于多选项表;
enum:多由于性别,及部分选项较少的表。
创建表:
dreate table votes(
username varchar(30),
hobby set('登山','游泳','编程','唱歌'),
gender enum('男','女')
);
插入内容:
insert into votes(username,hobbby,gender) values('Joy','编程,唱歌',1);
insert into votes(username,hobby,gender) values('fay','编程',2);
insert into votes(username,hobby,gender) values('janny','登山,游泳',2);
insert into votes(username,hobby,gender) values('lisa','唱歌',2);
显示表内容:
select * from votes;
+----------+-----------+--------+
| username | hobby | gender |
+----------+-----------+--------+
| Joy | 编程,唱歌 | 男 |
| fay | 编程 | 女 |
| lisa | 唱歌 | 女 |
| janny | 登山,游泳 | 男 |
+----------+-----------+--------+
选项查找多选项中的某一选项,仅仅使用 where 是不够的:
select * from votes where hobby='编程';
+----------+-------+--------+
| username | hobby | gender |
+----------+-------+--------+
| fay | 编程 | 女 |
+----------+-------+--------+
select * from votes where gender='女';
+----------+-------+--------+
| username | hobby | gender |
+----------+-------+--------+
| fay | 编程 | 女 |
| lisa | 唱歌 | 女 |
+----------+-------+--------+
需要调用 find_in_set(选项,列名):
select * from votes where find_in_set('编程',hobby);
+----------+-----------+--------+
| username | hobby | gender |
+----------+-----------+--------+
| Joy | 编程,唱歌 | 男 |
| fay | 编程 | 女 |
+----------+-----------+--------+
实例容易掌握,但还是需要不断地练习。