Understanding Table Data
Data in tables can be various types, and must be designated as to which type applies to each field. The tables below describe all the possibilities. Don't try to memorize it all, come back to this table as you need.
NUMERICAL and DATE DATA | |||
---|---|---|---|
Integers | |||
Type | Range | Bytes | Description |
TINYINT | -127/128 | 1 | Very small integers |
SMALLINT | -32768/ 32767 |
2 | Small integers |
MEDIUMINT | -8388608/ 8388607 |
3 | Medium-sized integers |
INTEGER | -231/231 | 4 | Regular integers |
BIGINT | -263/263 | 8 | Big integers |
Floating Point | |||
Type | Range | Bytes | Description |
FLOAT | 1.17E-38/ 3.40E+38 |
4 | Single precision |
DOUBLE | 1.79E-308/ 2.22E+308 |
8 | Double precision |
DECIMAL | varies | varies | FLOAT stored as CHAR |
Date and Time | |||
Type | Range | Description | |
YEAR | 1901/2155 | 4-digit year | |
DATE | 1000-01-01 9999-12-31 |
Displayed as YYYY-MM-DD |
|
DATETIME | 1000-01-01 9999-12-31 |
Displayed as YYYY-MM-DDHH:MM:SS |
|
TIME | -838:59:59/ 838:59:59 |
Displayed as HH:MM:SS |
|
TIMESTAMP | 1970-01-01 2037-??-?? |
Displayed as YYYYMMDDHHMMSS |
STRING DATA | ||
---|---|---|
Regular | ||
Type | Range | Description |
CHAR | 1/255 characters |
Fixed length string |
VARCHAR | 1/255 characters |
Variable length string |
Long Text | ||
Type | Max length | Description |
TINYTEXT | 255 | Tiny text field |
TEXT | 65,535 | Normal text field |
MEDIUMTEXT | 224 - 1 | Medium text field |
LONGTEXT | 232 - 1 | Long text field |
Binary Large Object | ||
Type | Max length | Description |
TINYBLOB | 255 | Tiny BLOB field |
BLOB | 65,535 | Normal BLOB field |
MEDIUMBLOB | 224 - 1 | Medium BLOB field |
LONGBLOB | 232 - 1 | Long BLOB field |
CREATE Tables
Now you can fill your database with tables. Table names cannot contain spaces, slashes or periods. The syntax is: CREATE TABLE <table_name> (<field_name_1> <field_type_1> <modifiers>, <field_name_2> <field_type_2> <modifiers>, ... , <field_name_n> <field_type_n> <modifiers>) Here is the code to create the People Table used in previous examples. The DESCRIBE command shows us the fields in a table.
+------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | ID | char(8) | | PRI | | | | NAME_LAST | char(20) | | | | | | NAME_FIRST | char(20) | | | | | | MI | char(1) | | | | | | POSITION | char(6) | | | | | +------------+----------+------+-----+---------+-------+5 rows in set (0.00 sec) mysql>
As you can see, the table name is people. The fields are ID, NAME_LAST, NAME_FIRST, MI and POSITION. I chose the char type from the tables above for all fields. The numbers in parentheses are the maximum number of characters I allowed for each field (the larger the maximum number, the more storage space used in the table). Lastly, I set the Primary Key to ID
When records are entered and fields are populated, some of them may be empty. For instance, when we first input the record for Barbara Smith, we might not know her middle initial, so we leave it blank. An empty string "" is something; the number 0 is something; NULL is nothing. When you use null, it reflects no value. To provide some value, even if it is a blank space, use the NOT NULL modifier. Now, if we don't know Barbara Smith's middle initial, a blank space is entered.
Other modifiers you can employ include
- UNIQUE - which specifies that values entered into a field must be unique.
- AUTO_INCREMENT - which is only available for numeric fields, specifies that MySQL should automatically generate a number for that field (by incrementing the previous value by 1).
Copy the CREATE TABLE command (shown in blue above) and paste it after your mysql prompt so you can create the same table. To test the creation, type describe people; .