MySQL Schema and State

MySQL is a powerful database that allows for complex logic when manipulating data. This guide is an introduction and a reference to common SQL queries for schema and state manipulation.

This guide assumes that you already have a working copy of MySQL. If you don't, read Introduction to MySQL first.

Schema

The schema of your database refers to the way that your data is structured and to how bits of data relate to each other. Every database in your MySQL server has a separate schema, and separate databases should not typically interact with one another.

Databases

To create a database on your MySQL server, simply run the query:

create database DATABASENAME;

You can delete a database using the query:

drop database DATABASENAME;

When you're at the MySQL prompt, use the following query to select a database to which to run select, insert, update, and delete queries:

use DATABASENAME;

Tables

A database contains one or more tables. Tables contain columns and rows, which you may sometimes call fields and entries.

You can see all of the tables in the database using this query:

show tables;

When you create a table, you should provide information about all of that table's columns within the create table query. Here is the general syntax for the query:

create table TABLENAME (
	FIELDNAME DATATYPE OPTIONS,
	FIELDNAME DATATYPE OPTIONS,
	FIELDNAME DATATYPE OPTIONS,
	FIELDNAME DATATYPE OPTIONS
) TABLE_OPTIONS;

You can get details about an existing table using this query:

describe TABLENAME;

Data Types

Just like a programming language, MySQL has data types. There are dozens of data types; some of the most common ones are documented below.

String Data Types

There are four main types of string data types: character, text, blob, and enumerable.

• Character types are useful for short strings, like usernames and e-mail addresses.
• Text types differ from character types in that behind the scenes, they are not actually stored with the rest of the table row; they are useful for longer blocks of text, like forum posts.
• Blobs are for binary data, like files.
• Enumerables are for something that could have one of a set number of values (not more than 65535), like what State a user is from (Alabama, Alaska, and so on). Enums are also useful for storing boolean data, like ENUM('yes','no'). An enum is actually stored as an integer in the database, making queries more efficient.

The syntax for these data types is:

• VARCHAR(#), a string at most # characters long, where 0 ≤ # ≤ 255
• CHAR(#), a string exactly # characters long, where 0 ≤ # ≤ 255
• TINYTEXT, a text block at most 255 characters long
• TEXT, a text block at most 65535 characters long
• BLOB, a binary string at most 65 KB in length
• MEDIUMBLOB, a binary string at most 16 MB in length
• LONGBLOB, a binary string at most 4 GB in length
• ENUM('a', 'b', 'c'), an enumerable with choices a, b, and c

Numeric Data Types

There are two types of numeric data types that you need to worry about: integers and decimals. The difference between the two should be pretty self-explanatory. The syntax for using these data types is:

• TINYINT, an integer n where -128 ≤ n ≤ 127
• TINYINT UNSIGNED, an integer n where 0 ≤ n ≤ 255
• SMALLINT, an integer n where -32,768 ≤ n ≤ 32,767
• SMALLINT UNSIGNED, an integer n where 0 ≤ n ≤ 65,535
• MEDIUMINT, an integer n where -8,388,608 ≤ n ≤ 8,388,607
• MEDIUMINT UNSIGNED, an integer n where 0 ≤ n ≤ 16,777,215
• INT, an integer n where -2,147,483,648 ≤ n ≤ 2,147,483,647
• INT UNSIGNED, an integer n where 0 ≤ n ≤ 4,294,967,295
• BIGINT, an integer n where -9,223,372,036,854,775,808 ≤ n ≤ 9,223,372,036,854,775,807
• BIGINT UNSIGNED, an integer n where 0 ≤ n ≤ 18,446,744,073,709,551,615
• DECIMAL(#a, #b), a decimal number at most #a digits long, #b of which are after the decimal point. For example, DECIMAL(5,2) can contain n where -999.99 ≤ n ≤ 999.99

When choosing the size of an integer, you should use the smallest integer for which you know you will never "run out of room". In most everyday circumstances, MEDIUMINT should be the biggest you'll need.

Temporal Data Types

MySQL provides a class of data types that specialize in handling dates and times. The syntax for using date types is:

• DATE, a date d where 1000-01-01 ≤ d ≤ 9999-12-31
• DATETIME, a date with time d where 1000-01-01 00:00:00 ≤ d ≤ 9999-12-31 23:59:59
• TIMESTAMP, a date with time d where 1970-01-01 00:00:01 UTC ≤ d ≤ 2038-01-19 03:14:07 UTC (note: 1970-01-01 00:00:01 UTC is the Unix epoch)
• TIME, a length of time t where -838:59:59 ≤ t ≤ 838:59:59 (note: you cannot store time to a precision greater than 1 second)
• YEAR, a year y where 1901 ≤ y ≤ 2155

Note that TIMESTAMP is a highly versatile data type that can automatically change whenever a row is updated. For more information, refer to the MySQL documentation.

Field Options

By default, any "cell" in a MySQL table can contain the NULL value. If a certain field in your database schema will never contain a null value, you should declare it as NOT NULL in order to make queries more efficient.

You can also specify a default value for a field. If you do, when you insert data into the table, it won't be necessary to specify a value for that column. To specify a default value for a field, use the syntax DEFAULT 'DEFAULT-VALUE'

Create Table Example

The following query would create a table called employees with six columns: employee ID, first name, last name, nickname, department, and the date that they joined the company. Nickname is the only optional column. The primary key for the table (see the next section) will be the id column. The department will be one of five choices, with the default being CSE. The storage engine for the table will be InnoDB.

create table employees (
	id mediumint unsigned not null auto_increment,
	first_name varchar(30) not null,
	last_name varchar(40) not null,
	nickname varchar(20),
	department enum('CSE','BME','EECE','ESE','MEMS') not null default 'CSE',
	joined timestamp not null default current_timestamp,
	primary key (id)
) engine=InnoDB;

Keys

When MySQL goes to get data out of a database, it does so using an index, like the index of a textbook. In tech talk, we call indices keys.

Primary Key

The primary key is what MySQL uses to distinguish one row from another. Every table must have exactly one primary key; no more, no less.

It is common practice to add a column to a table with data type mediumint unsigned not null auto_increment to serve as the primary key for a table. The auto_increment is a neat little feature of MySQL that basically says, "when a new row is inserted into this table, assign it the next-lowest available ID".

To specify a primary key for a table, use the syntax:

primary key (column1, column2, column3)

Unique Key

If you want to specify that all items in a certain column will be unique from each other (like usernames or e-mail addresses), you can use a unique key.

Because a table can have more than one unique key, you need to give your unique key a name. It is common practice to name the key after the columns it is indexing, like idx_unique_column1_column2.

The syntax for specifying a unique key is:

unique key INDEX_NAME (column1, column2, column3)

Foreign Key

A foreign key is the most complicated type of MySQL index. A foreign key says that the values in one column are associated with the values in a column in a different table.

For example, suppose you had two tables, one containing users and the other containing posts on a forum. There is a one-to-many relationship between users and posts. You could link a post to a user by having a column in your post table called user_id, which would be a foreign key to the id column in your users table.

Foreign keys can be a source of great confusion in developing database schemas. If you get stuck, keep this tip in mind: Foreign Keys should always reference the columns used in the Primary Key of a different table.

The syntax for creating a foreign key is as follows:

foreign key (column1, column2, column3) references FOREIGN_TABLENAME (column1, column2, column3)

For example, in the user-and-post example above, the foreign key declaration would be:

foreign key (user_id) references user (id)

Further Reading about Foreign Keys

• Wikipedia Entry on Foreign Keys
• An introductory article on Tech Republic
• MySQL Documentation for Foreign Keys

Syntax for Keys

You can define keys in your create table query, or you can define them later.

If you define them in your create table query, do so after the column definitions but still inside the parentheses:

create table TABLENAME (
	column,
	column,
	....,
	primary key (column1, column2, ...),
	unique key INDEXNAME (column1, column2, ...),
	foreign key (column1, column2, ...) references FOREIGN_TABLENAME (column1, column2, ...)
)

To define indices later, use an ALTER TABLE query:

<source lang="mysql"> alter table TABLENAME add unique key INDEXNAME (column1, column2, ...); alter table TABLENAME add foreign key (column1, column2, ...) references FOREIGN_TABLENAME (column1, column2, ...);