Create Table

This lesson explains how to create a table in MySQL.

We'll cover the following

Create Table

When creating a table, we need to specify the table name, its columns, and the backing data types for the columns. There are other constraints that we can specify when creating a table but for now we’ll demonstrate creating the table in the simplest possible manner.

Syntax

CREATE TABLE tableName (

col1 <dataType> <Restrictions>,

col2 <dataType> <Restrictions>,

col3 <dataType> <Restrictions>,

<Primary Key or Index definitions>);

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/3lesson.sh and wait for the MySQL prompt to start-up.

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 



-- Query 1

CREATE TABLE Actors (

FirstName VARCHAR(20),

SecondName VARCHAR(20),

DoB DATE,

Gender ENUM('Male','Female','Other'),

MaritalStatus ENUM('Married', 'Divorced', 'Single'),

NetWorthInMillions DECIMAL);



-- Query 2

SHOW TABLES;



-- Query 3

DESC Actors;
Terminal 1
Terminal
Loading...

  1. Execute the following command to create the Actors table. You can copy and paste the command in the terminal:

    CREATE TABLE Actors (
FirstName VARCHAR(20),
SecondName VARCHAR(20),
DoB DATE,
Gender ENUM('Male','Female','Other'),
MaritalStatus ENUM('Married', 'Divorced', 'Single'),
NetWorthInMillions DECIMAL);

    The create table statement has three pieces to itself. The create table snippet followed by the table name and opening parenthesis. The second piece lists the columns of the table and the third piece is the key definitions. To keep things simple, we don’t define any keys until the next lesson. The statement is terminated by a closing parenthesis.

    The syntax for defining a column is as follows:

    columnName columnType [NOT NULL | NULL] [DEFAULT columnValue]

    Note that column names are case-insensitive and portable across all operating systems, unlike database names.

  2. Successful execution of the previous command will create the Actors table. Execute the following command to view all the tables in the MovieIndustry database.

    SHOW TABLES;

    The output from the command should be similar to the one below

  1. We can inspect the Actors table we just created using the following command:

    DESC Actors;

    The output from the above command will be as follows:

The DESC command displays all the columns of the table and related metadata such as data type and nullability for each column.

  1. Our create statement is very basic in its current form. We can add duplicate rows or NULL as column values which is usually not what we want.

In the next lesson, we’ll see how to enforce restrictions on the table that don’t allow this behavior.