MySQL Tutorial

Creating Tables

The CREATE statement is used to create a table in MySQL. The general syntax to create a table in MySQL is:

CREATE TABLE tableName
(
    fieldName1 dataType(size) [NULL | NOT NULL]
    fieldName2 dataType(size) [NULL | NOT NULL]
);

If NULL is specified, the field is allowed to be left empty. If NOT NULL is specified, the field must be given a value. In the absence of either a NULL or NOT NULL, NULL is assumed.

Data Types of Fields

The following lists the data types avaiable in MySQL.

Data Type Example Description
CHAR(size fieldName CHAR(10)

Stores up to 255 characters. If the content is smaller than the field size, the content will have trailing spaces appended.

VARCHAR(size fieldName VARCHAR(100)

Stores up to 255 characters, and a minimum of 4 characters. No trailing spaces are appended to the end of this datatype. MySQL keeps track of a delimiter to keep track of the end of the field.

TINYTEXT fieldName TINYTEXT

Stores up to 255 characters. Equivalent to VARCHAR(255).

TEXT fieldName TEXT

Stores up to 65,535 characters. An Index can be created on the first 255 characters of a field with this data type.

MEDIUMTEXT fieldName MEDIUMTEXT

Stores up to 16,777,215 characters. An Index can be created on the first 255 characters of a field with this data type.

LONGTEXT fieldName LONGTEXT

Stores up to 4,294,967,295 characters. An Index can be created on the first 255 characters of a field with this data type.

Note: The maximum size of a string in MySQL is currently 16 million bytes, so this data types is not useful at the moment.

ENUM fieldName ENUM('Yes', 'No')

Stores up to 65,535 enumerated types. The DEFAULT modifier may be used to specify the default value for this field.

INT fieldName INT

Stores a signed or unsigned integer number. Unsigned integers have a range of 0 to 4,294,967,295, and signed integers have a range of -2,147,438,648 to 2,147,438,647. By default, the INT data type is signed. To create an unsigned integer, use the UNSIGNED attribute.

fieldName INT UNSIGNED

The ZEROFILL attribute may be used to left-pad any of the integer with zero's.

fieldName INT ZEROFILL

The AUTO_INCREMENT attribute may be used with any of the Integer data types. The following example could be used to create a primary key using the AUTO_INCREMEMNT attribute.

fieldName INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
TINYINT fieldName TINYINT

Stores a signed or unsigned byte. Unsigned bytes have a range of 0 to 255, and signed bytes have a range of -128 to 127. By default, the TINYINT data type is signed.

MEDIUMINT fieldName MEDIUMINT

Stores a signed or unsigned medium sized integer. Unsigned fields of this type have a range of 0 to 1,677,215, and signed fields of this type have a range of -8,388,608 to 8,388,607. By default, the MEDIUMINT data type is signed.

BIGINT fieldName BIGINT

Stores a signed or unsigned big integer. Unsigned fields of this type have a range of 0 to 18,446,744,073,709,551,615, and signed fields of this type have a range of -9,223,372,036,854,775,808 to 9,223,327,036,854,775,807. By default, the BIGINT data type is signed.

FLOAT fieldName FLOAT

Used for single precision floating point numbers.

DOUBLE fieldName DOUBLE

Used for double precision floating point numbers.

DATE fieldName DATE

Stores dates in the format YYYY-MM-DD.

TIMESTAMP(size) fieldName DATETIME

Stores dates and times in the format YYYY-MM-DD HH:MM:SS.

DATETIME fieldName TIMESTAMP(14)

Automatically keeps track of the time the record was last ammended. The following table shows the formats depending on the size of TIMESTAMP

Size Format
2 YY
4 YYMM
6 YYMMDD
8 YYYYMMDD
10 YYYYMMDDHH
12 YYYYMMDDHHMM
14 YYYYMMDDHHMMSS
TIME fieldName TIME

Stores times in the format HH:MM:SS.

YEAR(size) fieldName YEAR(4)

Stores the year as either a 2 digit number, or a 4 digit number, depending on the size provided.

Primary Keys

A primary key is a field in a table that uniquely identifies a record. The PRIMARY KEY attribute may be used when defining the field name to create a primary key, as in the following example.

fieldName INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

Alternatively, the PRIMARY KEY attribute may be used to specify the primary key after the defining the fields, as in the following example:

CREATE TABLE product
(
    prodID INT UNSIGNED AUTO_INCREMENT,
    description VARCHAR(100),
    PRIMARY KEY(prodID)
);

The second method is ideal should you want to create a compound primary key. A compound primary key is where more than one field is used to uniquely identify a unique record. The field names are separated with commas. Consider a table used with a search engine for a site. The primary key could comprise the Directory name, and the page name. The following example illustrates creating a table with a compound primary key.

createSearch.sql
CREATE TABLE search
(
    Category VARCHAR(100) NOT NULL,
    Page VARCHAR(20) NOT NULL,
    Directory VARCHAR(255) NOT NULL,
    LinkName VARCHAR(255) NOT NULL,
    Keywords MEDIUMTEXT NOT NULL,
    Desription VARCHAR(255) NOT NULL,
    PRIMARY KEY(Page, Directory)
);

The file may then be used with MySQL as follows:

mysql> \. createSearch.sql

Providing Default Values

Default values may be provided for fields using the DEFAULT attribute. The following example uses a default value of index.html, should a Page name not be provided for the search table.

createSearch.sql

CREATE TABLE search
(
    Category VARCHAR(100) NOT NULL,
    Page VARCHAR(20) DEFAULT 'index.html',
    Directory VARCHAR(255) NOT NULL,
    LinkName VARCHAR(255) NOT NULL,
    Keywords MEDIUMTEXT NOT NULL,
    Desription VARCHAR(255) NOT NULL,
    PRIMARY KEY(Page, Directory)
);

Examining Tables

You can examine the structure of a table using the DESCRIBE statement. The following displays the structure of the search table.

mysql> DESCRIBE search;

The DESCRIBE statement may be shorted to DESC. The following achieves the same thing.

mysql> DESC search;

You may also use the SHOW FIELDS FROM statement to achieve the same thing.

mysql> SHOW FIELDS FROM search;

Listing All Tables

You can list all tables in the database using the SHOW TABLES statement. The following example lists all tables in the current database.

mysql> SHOW TABLES;

You might also like...

Comments

About the author

Gez Lemon United Kingdom

I'm available for contract work. Please visit Juicify for details.

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Memory is like an orgasm. It's a lot better if you don't have to fake it.” - Seymour Cray