How to use enum : ENUM « Data Type « SQL / MySQL

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Data Type » ENUM 




How to use enum
  
/*
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.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_namevalues (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, childrenvalues (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, childrenvalues (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_namevalues (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_statusvalues (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_statusvalues (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, childrenvalues (7'43Mercury Ave', 7932232'mac@hotmail.com', '1966-8-21', 'M''Y''Mary Shelly', '3');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_statusvalues (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, childrenvalues (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_statusvalues (10'13Metro 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, childrenvalues (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, childrenvalues (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, childrenvalues (13'54Applegate Lane', 3434343'levy@cmail.com', '1968-09-03', 'F''Y''Matt Shi', '2');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_statusvalues (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, childrenvalues (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, childrenvalues (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, childrenvalues (17'65Devon 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_statusvalues (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_statusvalues (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_statusvalues (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, childrenvalues (21'4329 Eucalyptus Avenue', 4254863'money@cold.com', '1964-06-13', 'M''Y''Ruby Richer', 2);

select from employee_person;
           
         
    
  














Related examples in the same category
1.Use two ENUM data type in a table
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.
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.