How to create a table in MySQL

Creating a table in MySQL involves using the Structured Query Language (SQL) to define the table's structure, including its name, columns, data types, and any constraints. This process is foundational for organizing data in a relational database. Let's explore the basics of creating tables in MySQL, advance into more complex examples, and finally look at the data types available for columns.

Basic Command Structure

To create a simple table in MySQL, you use the CREATE TABLE statement, followed by the table name and a list of columns, each with its own data type and optional constraints. Here is the basic syntax:

CREATE TABLE table_name (

    column1 datatype constraints,

    column2 datatype constraints,

    ...

);

Example: Creating a Simple Table

Suppose we want to create a table named Employees that contains three columns: EmployeeID, FirstName, and LastName. Here's how we could do it:

CREATE TABLE Employees (

    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,

    FirstName VARCHAR(100),

    LastName VARCHAR(100)

);

In this example, EmployeeID is an integer that automatically increments for each new record and acts as the primary key. FirstName and LastName are variable character fields up to 100 characters in length.

Advanced Use Cases

Adding Constraints

You can add various constraints to your table's columns to enforce data integrity. Some common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and AUTO_INCREMENT. Here's an example that uses some of these constraints:

CREATE TABLE Orders (

    OrderID INT AUTO_INCREMENT PRIMARY KEY,

    OrderNumber VARCHAR(20) NOT NULL UNIQUE,

    EmployeeID INT,

    OrderDate DATE NOT NULL,

    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

);

This Orders table includes a foreign key constraint that links EmployeeID to the EmployeeID in the Employees table, ensuring referential integrity.

Setting Default Values

You can also specify default values for columns using the DEFAULT keyword. This is useful for columns that should have a predetermined value if none is provided:

CREATE TABLE Products (

    ProductID INT AUTO_INCREMENT PRIMARY KEY,

    ProductName VARCHAR(255) NOT NULL,

    Price DECIMAL(10, 2),

    InStock BOOLEAN DEFAULT TRUE

);

In this Products table, InStock is a Boolean column that defaults to TRUE if no value is specified.

Data Types

MySQL supports a wide range of data types. Choosing the right data type for each column is crucial for optimizing performance and storage. Here's a table of common MySQL data types:

Data Type	Description	Example Value
INT	A medium-range integer	12345
BIGINT	A large-range integer	123456789012
DECIMAL(M, N)	A fixed-point number where M is the total digits and N is the digits after the decimal	123.45
VARCHAR(L)	A variable-length string (up to L characters)	'Hello, World!'
TEXT	A long text string, up to 65,535 characters	'Long text...'
DATE	A date, without time	'2024-03-21'
DATETIME	A date and time combination	'2024-03-21 15:30:00'
TIMESTAMP	A timestamp, auto-updates to the current date and time	'2024-03-21 15:30:00'
TIME	A time without date	'15:30:00'
YEAR	A year in four-digit format	2024
CHAR(L)	A fixed-length string (up to L characters)	'ABC'
TINYTEXT	A tiny text string, up to 255 characters	'Tiny text'
MEDIUMTEXT	A medium-length text string, up to 16,777,215 characters	'Medium text...'
LONGTEXT	A long text string, up to 4,294,967,295 characters	'Very long text...'
BLOB	A binary large object, up to 65,535 bytes	(binary data)
MEDIUMBLOB	A medium-sized binary large object, up to 16,777,215 bytes	(binary data)
LONGBLOB	A large binary large object, up to 4,294,967,295 bytes	(binary data)
TINYINT	A very small integer	85
SMALLINT	A small-range integer	1234
MEDIUMINT	A medium-range integer, larger than SMALLINT	123456
FLOAT	A single-precision floating point number	123.45
DOUBLE	A double-precision floating point number	123.456789
BOOLEAN	A synonym for TINYINT(1), represents a true or false value	TRUE (or 1)
ENUM	A string object that can only have one value, chosen from a list of possible values	'small', 'medium', 'large'
SET	A string object that can have 0 or more values, chosen from a list of possible values	'red', 'green', 'blue'

This overview provides the foundational knowledge needed to start creating tables in MySQL. Experimenting with different data types and constraints will help you to effectively organize and manage your data.