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;