SQL Types

Modifiers

A column can be NOT NULL, which means it must be given a value.
A coulmn can be INDEX, which means make an index instead of a column. The coumn to be indexed must be NOT NULL
A column can be UNIQUE.
A column can be KEY, which means make an index. The column to be indexed must be NOT NULL.
A column can be PRIMARY KEY, which means KEY NOT NULL. There can only be one PRIMARY KEY. The table might be stored in PRIMARY KEY order.
A column can have a DEFAULT VALUE.
A column can be autoincrement, Inserting NULL or 0 causes the insertion of the next largest value.

Examples:
creat table fred (name char(20) not null, age smallint default 20, ssn decimal(9,0) not null, key(name), primary key(ssn));

Indexes

Indexes do not change table meaning. Instead that speed the selection of data values. They might slow the altering or inserting or data. Generally they are a net speed increase (sometimes ALOT) because there are generally many more selects than inserts.
You can index a bunch of columns together.
Columns declared KEY automatically get indexed.

Examples: create table fred (age smallint, birthday DATE key, fname varchar (10), lname varchar(10) unique, index names (fnale, lname));
create index ageindex on fred(age);
drop index ageindex;
This had two indexes. Then a third was created, and then destroyed.

Numeric Types (can be modified with UNSIGNED, ZEROFILL)

SQL Type Type

TINYINT char in C (8 bits)

SMALLINT short in C (16 bits)

INT int in C (32 bits)

BIGINT long in C (64 bit)

FLOAT float in C (32 bits)

DOUBLE double in C (64 bits)

DECIMAL(30,12) A number stored as a string, with 30 chars, including 12 after the decimal. Great for $$ amounts.

Time Types

SQL Type Type

DATE A date. Example '1967-04-31'. Uses 3 bytes

DATETIME A specific moment. Example '1967-04-31 15:22:12'. Uses 8 bytes.

TIMESTAMP A specific moment stored in Unix timestamp format. Example "19670431152212". Uses 4 bytes.
The first TIMESTAMP column, if you don't supply a value, gets marked with the current time. Cool!

TIME A time. Example '153212'. Range is -840 hours to +840 hours. Uses 3 bytes. Can store an interval as well as a time.

YEAR A year. Range from 1901 to 2055. Example '1967'. Uses 1 byte.

Feb 31 is OK with MySQL.
There's functions like DATE_SUB that work with dates.
Converting between types on the fly can cause problems. Basically it won't work if there is not enough info to do the converstion, for example YEAR to DATE. See the docs for more.

Char types (Can be modified with BINARY)

SQL Type Type

char(n) N bytes, stored zero padded on the right. Length limit 1 ... 255. Sorted case insensative unless BINARY modifier is used. Uses N bytes

varchar(n) N bytes, not without zero padding on the right. Otherwise just like char(n). Uses length_of_string+1 bytes.

BLOB or TEXT A string of up to 65535 chars.

LONGBLOB or LONGTEXT A string of up to 4 billion char (2^32-1).

ENUM('value1','value2',...) An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., or NULL. An ENUM can have a maximum of 65535 distinct values.

SET('value1','value2',...) A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members.

BLOBs are case sensative TEXTs.
Cannot use GROUP BY or ORDER BY for a BLOB or TEXT.
To check set membership, use LIKE or FIND_IN_SET

SQL Type	Type
TINYINT	char in C (8 bits)
SMALLINT	short in C (16 bits)
INT	int in C (32 bits)
BIGINT	long in C (64 bit)
FLOAT	float in C (32 bits)
DOUBLE	double in C (64 bits)
DECIMAL(30,12)	A number stored as a string, with 30 chars, including 12 after the decimal. Great for $$ amounts.

SQL Type	Type
DATE	A date. Example '1967-04-31'. Uses 3 bytes
DATETIME	A specific moment. Example '1967-04-31 15:22:12'. Uses 8 bytes.
TIMESTAMP	A specific moment stored in Unix timestamp format. Example "19670431152212". Uses 4 bytes. The first TIMESTAMP column, if you don't supply a value, gets marked with the current time. Cool!
TIME	A time. Example '153212'. Range is -840 hours to +840 hours. Uses 3 bytes. Can store an interval as well as a time.
YEAR	A year. Range from 1901 to 2055. Example '1967'. Uses 1 byte.

SQL Type	Type
char(n)	N bytes, stored zero padded on the right. Length limit 1 ... 255. Sorted case insensative unless BINARY modifier is used. Uses N bytes
varchar(n)	N bytes, not without zero padding on the right. Otherwise just like char(n). Uses length_of_string+1 bytes.
BLOB or TEXT	A string of up to 65535 chars.
LONGBLOB or LONGTEXT	A string of up to 4 billion char (2^32-1).
ENUM('value1','value2',...)	An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., or NULL. An ENUM can have a maximum of 65535 distinct values.
SET('value1','value2',...)	A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members.