Use operators to specify a range of values. : ENUM « Data Type « SQL / MySQL






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

Use operators to specify a range of values.

   
mysql>
mysql> CREATE TABLE CDs
    -> (
    ->     CDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     CDName VARCHAR(50) NOT NULL,
    ->     InStock SMALLINT UNSIGNED NOT NULL,
    ->     OnOrder SMALLINT UNSIGNED NOT NULL,
    ->     Reserved SMALLINT UNSIGNED NOT NULL,
    ->     Department ENUM('Classical', 'Popular') NOT NULL,
    ->     Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO CDs (CDName, InStock, OnOrder, Reserved, Department, Category) VALUES
    -> ('Xml', 10, 5, 3, 'Popular', 'Rock'),
    -> ('Java', 10, 5, 3, 'Classical', 'Opera'),
    -> ('SQL', 17, 4, 1, 'Popular', 'Jazz'),
    -> ('MySQL', 9, 4, 2, 'Classical', 'Dance'),
    -> ('CSS', 24, 2, 5, 'Classical', NULL),
    -> ('HTML', 16, 6, 8, 'Classical', NULL),
    -> ('Oracle', 2, 25, 6, 'Popular', 'Blues'),
    -> ('Javascript', 32, 3, 10, 'Popular', NULL),
    -> ('Data type', 12, 15, 13, 'Popular', 'Country'),
    -> ('Flash', 5, 20, 10, 'Popular', 'New Age'),
    -> ('Ajax', 24, 11, 14, 'Popular', 'New Age'),
    -> ('Photoshop', 42, 17, 17, 'Classical', NULL),
    -> ('Word', 25, 44, 28, 'Classical', 'Dance'),
    -> ('iPhone', 32, 15, 12, 'Classical', 'General'),
    -> ('MacBook', 20, 10, 5, 'Classical', 'Opera'),
    -> ('Linux', 23, 12, 8, 'Classical', 'General'),
    -> ('Shell', 23, 10, 17, 'Popular', 'Country'),
    -> ('Pascal', 18, 20, 10, 'Popular', 'Jazz'),
    -> ('Ruby', 22, 5, 7, 'Popular', 'Blues'),
    -> ('Sql Server', 28, 17, 16, 'Classical', 'General'),
    -> ('Opera', 10, 35, 12, 'Classical', 'Opera'),
    -> ('Safari', 15, 30, 14, 'Popular', NULL),
    -> ('C', 42, 0, 8, 'Popular', 'Blues'),
    -> ('C++', 16, 8, 8, 'Classical', 'General');
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql>
mysql> SELECT CDName, InStock, OnOrder, Reserved
    -> FROM CDs
    -> WHERE (InStock+OnOrder);
+------------+---------+---------+----------+
| CDName     | InStock | OnOrder | Reserved |
+------------+---------+---------+----------+
| Xml        |      10 |       5 |        3 |
| Java       |      10 |       5 |        3 |
| SQL        |      17 |       4 |        1 |
| MySQL      |       9 |       4 |        2 |
| CSS        |      24 |       2 |        5 |
| HTML       |      16 |       6 |        8 |
| Oracle     |       2 |      25 |        6 |
| Javascript |      32 |       3 |       10 |
| Data type  |      12 |      15 |       13 |
| Flash      |       5 |      20 |       10 |
| Ajax       |      24 |      11 |       14 |
| Photoshop  |      42 |      17 |       17 |
| Word       |      25 |      44 |       28 |
| iPhone     |      32 |      15 |       12 |
| MacBook    |      20 |      10 |        5 |
| Linux      |      23 |      12 |        8 |
| Shell      |      23 |      10 |       17 |
| Pascal     |      18 |      20 |       10 |
| Ruby       |      22 |       5 |        7 |
| Sql Server |      28 |      17 |       16 |
| Opera      |      10 |      35 |       12 |
| Safari     |      15 |      30 |       14 |
| C          |      42 |       0 |        8 |
| C++        |      16 |       8 |        8 |
+------------+---------+---------+----------+
24 rows in set (0.00 sec)

mysql>
mysql> drop table CDs;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>








Related examples in the same category

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