Use two ENUM data type in a table : ENUM « Data Type « SQL / MySQL






  1. SQL / MySQL
  2. Data Type
  3. ENUM

Use two ENUM data type in a table

  
/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+--------------------------+------------+------+----------+-----------------+----------+
| id | address                             | phone   | email | birthday   | sex  | m_status | s_name          | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
|  1 | 200, Regina Street                  | 7176666 | net@hotmail.com | 1971-04-26 | M    | Y        | Ane Regina      |     NULL |
|  2 | 1232 Alberta Road                   | 5553312 | jo@hotmail.com | 1968-03-02 | M    | Y        | Jane Van        |        3 |
|  3 | 90 Potter A                         | 3331211 | gp@ymail.com | 1967-09-22 | M    | N        | Sandhya Pil     |        2 |
|  4 | 202, Donna Street                   | 7176167 | twink@hotmail.com | 1976-08-09 | F    | Y        | Manish Sha      |     NULL |
|  5 | Apartment #8, UBC, Van Street       | 8973242 | holy@ymail.com | 1974-10-14 | F    | N        | NULL            |     NULL |
|  6 | 46 SFU Street                       | 6451234 | kill@el.com | 1978-12-31 | M    | N        | NULL            |     NULL |
|  7 | 432 Mercury Ave                     | 7932232 | mac@hotmail.com | 1966-08-21 | M    | Y        | Mary Shelly     |        3 |
|  8 | 8 Little YaleTown                   | 5442994 | edd@gmail.com | 1975-01-14 | M    | N        | NULL            |     NULL |
|  9 | 64 Temp Road                        | 4327652 | nan@pmail.com | 1969-05-19 | M    | Y        | Man Nanda       |        1 |
| 10 | 132 Metro House, Henry Street       | 5552376 | ra@hotmail.com | 1968-07-06 | M    | N        | NULL            |     NULL |
| 11 | 1 Grace Town, Van Avenue            | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M    | Y        | Muriel Lovelace |        4 |
| 12 | 97 Oakland Road                     | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M    | Y        | Rina Brighton   |        3 |
| 13 | 543 Applegate Lane                  | 3434343 | levy@cmail.com | 1968-09-03 | F    | Y        | Matt Shi        |        2 |
| 14 | 76 Fish Street                      | 7432433 | tink@email.com | 1965-04-28 | M    | N        | NULL            |     NULL |
| 15 | 98 Gun Street                       | 6500787 | danny@fhardy.com | 1966-06-23 | M    | Y        | Betty Cudly     |        3 |
| 16 | #5 Winnepag Homes                   | 5433243 | mike@cmail.com | 1964-03-06 | M    | Y        | Stella Stevens  |        2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.com | 1970-04-18 | F    | Y        | Edgar Alan      |        1 |
| 18 | Apartment #9, Together Towers       | 5476565 | odessey@hotmail.com | 1973-10-09 | M    | N        | NULL            |     NULL |
| 19 | Apartment #9, West Towers           | 5476565 | jire@hotmail.com | 1973-01-20 | M    | N        | NULL            |     NULL |
| 20 | 90 Yale Town                        | 7528326 | help@more.org | 1968-01-25 | F    | N        | NULL            |     NULL |
| 21 | 4329 Eucalyptus Avenue              | 4254863 | money@cold.com | 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@hotmail.com', '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@hotmail.com', '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@ymail.com', '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@hotmail.com', '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@ymail.com', '1974-10-14', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, '46 SFU Street', '6451234', 'kill@el.com', '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@hotmail.com', '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@gmail.com', '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@pmail.com', '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@hotmail.com', '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@boxer.net', '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@roundtable.org', '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@cmail.com', '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@email.com', '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@fhardy.com', '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@cmail.com', '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@darling.com', '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@hotmail.com', '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@hotmail.com', '1973-1-20', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, '90 Yale Town', 7528326, 'help@more.org', '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@cold.com', '1964-06-13', 'M', 'Y', 'Ruby Richer', 2);


select * from employee_person;

select id, s_name
       from employee_person
       where m_status = 'Y';








Related examples in the same category

1.How to use enum
2. Storage Comparisons of ENUM and SET
3.Insert String value as ENUM
4.Define and use ENUM data type
5.Define 'Male' and 'Female' as Enum
6.ENUM: Enumeration of up to 65,535 strings
7.With ENUM you can manage a list of up to 65,535 character strings.
8.Sorting ENUM Values
9.MySQL uses the internal numeric values for sorting for enum value
10.Sort ENUM values in lexical order
11.Consider changing the column type to ENUM, if you always sort a non-enumeration column in a specific non-lexic
12.Use ALTER TABLE to convert the name column to an ENUM that lists the colors in the desired sort order:
13.Add to mytbl an ENUM column e that has several members
14.Enumerating a Many-to-Many Relationship
15.Configure an ENUM column and a SET column
16.If you add an invalid value to ENUM columns, an empty string ("") is inserted instead
17.Perform queries on enumerated fields based on their indexes
18.Sets work in a similar way to enumerated fields
19.The ENUM and SET Column Types
20.Enumeration values aren't limited to being single letters or uppercase.
21.An ENUM column definition may list up to 65,535 members.
22.Insert value to enum type column
23.The SET datatype, like ENUM, is declared using a comma-separated list of quoted strings that define its valid
24.enum value index
25.Enum for month names
26.Enum type column
27.Order by enum value
28.Using foreign key as an enum type
29.Use operators to specify a range of values.