Creating tables

Specifying field data types

MySQL supports various data types including numbers, strings, and date/time types:

Type Range Size
BOOL -128..127   0=false 1 byte
SMALLINT -32768..32767 2 bytes
INT -2147483648..2147483647 4 bytes
BIGINT -9223372036854775808..9223372036854775807 8 bytes
FLOAT -3.402823466E+38..-1.175494351E-38, 0,
1.175494351E-38..3.402823466E+38		 4 bytes
DOUBLE -1.7976931348623157E+308..-2.2250738585072014E-308, 0
2.2250738585072014E-308..1.7976931348623157E+308		 8 bytes
DATE '1000-01-01' .. '9999-12-31' varies
TIME '-838:59:59' .. '838:59:59' varies
CHAR(m) Fixed length 0..255 chars m bytes
VARCHAR(m) Variable length 1..65535 chars 1..m bytes

Dates must be entered in the format YYYY-MM-DD and the time in the format HH:MM:SS.

You should always choose the minimum storage appropriate for your application as the size of the database grows very fast.

Creating tables

Use the CREATE TABLE statement to create a table:

CREATE TABLE table_name (col_name TYPE, ...);
Example:
mysql> CREATE TABLE products (make VARCHAR(20), model VARCHAR(30),
    -> price FLOAT, quantity INT);

Use the following syntax to double check the created table structure:

mysql> DESCRIBE products;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| make     | varchar(20) | YES  |     | NULL    |       |
| model    | varchar(30) | YES  |     | NULL    |       |
| price    | float       | YES  |     | NULL    |       |
| quantity | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Deleting tables

Use the following syntax to delete a table:

mysql> DROP TABLE table_name;