1,案例
/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+--------------------------+------------+------+----------+-----------------+----------+
| id | address | phone | email | birthday | sex | m_status | s_name | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| 1 | 200, Regina Street | 7176666 | net@ | 1971-04-26 | M | Y | Ane Regina | NULL |
| 2 | 1232 Alberta Road | 5553312 | jo@ | 1968-03-02 | M | Y | Jane Van | 3 |
| 3 | 90 Potter A | 3331211 | gp@ | 1967-09-22 | M | N | Sandhya Pil | 2 |
| 4 | 202, Donna Street | 7176167 | twink@ | 1976-08-09 | F | Y | Manish Sha | NULL |
| 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ | 1974-10-14 | F | N | NULL | NULL |
| 6 | 46 SFU Street | 6451234 | kill@ | 1978-12-31 | M | N | NULL | NULL |
| 7 | 432 Mercury Ave | 7932232 | mac@ | 1966-08-21 | M | Y | Mary Shelly | 3 |
| 8 | 8 Little YaleTown | 5442994 | edd@ | 1975-01-14 | M | N | NULL | NULL |
| 9 | 64 Temp Road | 4327652 | nan@ | 1969-05-19 | M | Y | Man Nanda | 1 |
| 10 | 132 Metro House, Henry Street | 5552376 | ra@ | 1968-07-06 | M | N | NULL | NULL |
| 11 | 1 Grace Town, Van Avenue | 5433879 | soundofsilence@ | 1957-11-04 | M | Y | Muriel Lovelace | 4 |
| 12 | 97 Oakland Road | 5423311 | kingarthur@ | 1968-02-15 | M | Y | Rina Brighton | 3 |
| 13 | 543 Applegate Lane | 3434343 | levy@ | 1968-09-03 | F | Y | Matt Shi | 2 |
| 14 | 76 Fish Street | 7432433 | tink@ | 1965-04-28 | M | N | NULL | NULL |
| 15 | 98 Gun Street | 6500787 | danny@ | 1966-06-23 | M | Y | Betty Cudly | 3 |
| 16 | #5 Winnepag Homes | 5433243 | mike@ | 1964-03-06 | M | Y | Stella Stevens | 2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@ | 1970-04-18 | F | Y | Edgar Alan | 1 |
| 18 | Apartment #9, Together Towers | 5476565 | odessey@ | 1973-10-09 | M | N | NULL | NULL |
| 19 | Apartment #9, West Towers | 5476565 | jire@ | 1973-01-20 | M | N | NULL | NULL |
| 20 | 90 Yale Town | 7528326 | help@ | 1968-01-25 | F | N | NULL | NULL |
| 21 | 4329 Eucalyptus Avenue | 4254863 | money@ | 1964-06-13 | M | Y | Ruby Richer | 2 |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
21 rows in set (0.01 sec)
mysql> select id, s_name
-> from employee_person
-> where m_status = 'Y';
+----+-----------------+
| id | s_name |
+----+-----------------+
| 1 | Ane Regina |
| 2 | Jane Van |
| 4 | Manish Sha |
| 7 | Mary Shelly |
| 9 | Man Nanda |
| 11 | Muriel Lovelace |
| 12 | Rina Brighton |
| 13 | Matt Shi |
| 15 | Betty Cudly |
| 16 | Stella Stevens |
| 17 | Edgar Alan |
| 21 | Ruby Richer |
+----+-----------------+
12 rows in set (0.00 sec)
*/
Drop table employee_person;
CREATE TABLE employee_person (
id int unsigned not null primary key,
address varchar(60),
phone int,
email varchar(60),
birthday DATE,
sex ENUM('M', 'F'),
m_status ENUM('Y','N'),
s_name varchar(40),
children int
);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, '200, Regina Street', 7176666, 'net@', '1971-04-26', 'M', 'Y', 'Ane Regina');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, '1232 Alberta Road', 5553312, 'jo@', '1968-03-02', 'M', 'Y', 'Jane Van', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, '90 Potter A', 3331211, 'gp@', '1967-09-22', 'M', 'N', 'Sandhya Pil', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, '202, Donna Street', 7176167, 'twink@', '1976-08-09', 'F', 'Y', 'Manish Sha');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, 'Apartment #8, UBC, Van Street', 8973242, 'holy@', '1974-10-14', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, '46 SFU Street', '6451234', 'kill@', '1978-12-31', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, '432 Mercury Ave', 7932232, 'mac@', '1966-8-21', 'M', 'Y', 'Mary Shelly', '3');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, '8 Little YaleTown', 5442994, 'edd@', '1975-01-14', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, '64 Temp Road', 4327652, 'nan@', '1969-05-19', 'M', 'Y', 'Man Nanda', '1');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, '132 Metro House, Henry Street', 5552376, 'ra@', '1968-07-06', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, '1 Grace Town, Van Avenue', 5433879, 'soundofsilence@', '1957-11-04', 'M', 'Y', 'Muriel Lovelace', '4');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, '97 Oakland Road', 5423311, 'kingarthur@', '1968-02-15', 'M', 'Y', 'Rina Brighton', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, '543 Applegate Lane', 3434343, 'levy@', '1968-09-03', 'F', 'Y', 'Matt Shi', '2');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, '76 Fish Street', 7432433, 'tink@', '1965-04-28', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, '98 Gun Street', 6500787, 'danny@', '1966-06-23', 'M', 'Y', 'Betty Cudly', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, '#5 Winnepag Homes', 5433243, 'mike@', '1964-03-06', 'M', 'Y', 'Stella Stevens', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, '652 Devon Building, 6th Jade Avenue', 5537885, 'mona@', '1970-04-18', 'F', 'Y', 'Edgar Alan', 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, 'Apartment #9, Together Towers', 5476565, 'odessey@', '1973-10-09', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, 'Apartment #9, West Towers', 5476565, 'jire@', '1973-1-20', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, '90 Yale Town', 7528326, 'help@', '1968-01-25', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, '4329 Eucalyptus Avenue', 4254863, 'money@', '1964-06-13', 'M', 'Y', 'Ruby Richer', 2);
select * from employee_person;
select id, s_name
from employee_person
where m_status = 'Y';
二：字段解析：
ENUM 是一个字符串对象，其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举。
每个枚举值均有一个索引值：
例如，指定为 ENUM("one", "two", "three") 的一个列，可以有下面所显示的任一值。每个值的索引值也如下所示：
值
索引值
NULL
NULL
""
0
"one"
1
"two"
2
"three"
3
换个枚举最大可以有 65535 个成员值。
