Working with Create Table in MySQL

Intro

The CREATE TABLE statement is a fundamental statement in SQL that allows you to create databases in SQL. When using this statement, you will be able to detail columns, constraints and other details about your table. These can be changed later as well. We wont be able to cover everything you can do with CREATE TABLE, but we will cover the basics.

The Syntax

The basic syntax of CREATE TABLE is as follows:

CREATE TABLE [table_name] (
  [columns]
  ...
  [constraints]
)

If you want the answer, here is the quick solution.

CREATE TABLE employees (
  emp_no INT AUTO_INCREMENT PRIMARY KEY,
  birth_date DATE NOT NULL
);

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: mysql:latest
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: root_pass
      MYSQL_DATABASE: app_db
      MYSQL_USER: db_user
      MYSQL_PASSWORD: db_user_pass
    ports:
      - "6033:3306"
    volumes:
      - dbdata:/var/lib/mysql
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: pma
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  dbdata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

An Example

When create tables there are many things to consider. A few items are the following:

• the table name
• the column names
• the columns types
• the column "nullability"

Let's start by creating an employees table. Here is the structure.

CREATE TABLE employees (
);

Now, we want to add a emp_no which will be an ID. And birth_date which is Date.

When choosing types, you can use the official doc here: https://dev.mysql.com/doc/refman/5.7/en/data-types.html.

We also want to determine if the columns are null or not null, i.e. required or optional. Let's fill in the results for these columns.

CREATE TABLE employees (
  emp_no INT NOT NULL,
  birth_date DATE NOT NULL
);

MySQL allows us to defined a Primary Key and we can also Auto Increment this. This feature will tell MySQL to assign an ID and index that ID so you can easily search. This is common so that each row in our database will have a unique identifier.

To continue with with our basic table design, let's convert emp_no into a primary key.

CREATE TABLE employees (
  emp_no INT AUTO_INCREMENT PRIMARY KEY,
  birth_date DATE NOT NULL
);

There is much more to uncover about table design, but that is all we will cover here.