MySQL CREATE TABLE Guide with Examples | DbSchema

In MySQL, tables are used to store data in a structured format. This article walks through CREATE TABLE on the command line, then builds the same table visually in DbSchema. It assumes the database already exists - if it does not, start with MySQL CREATE DATABASE.

MySQL CREATE TABLE Syntax

The CREATE TABLE statement is the primary SQL command used to create a new table in a MySQL database, and it behaves much as CREATE TABLE does across SQL engines. A basic structure defines the table name, followed by a list of columns with their data types and constraints.

CREATE TABLE employees (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Prerequisites

Before we begin, you should have the following:

• A database in which to create the table
• Administrative privileges to create tables in the database - see MySQL CREATE USER and GRANT if you still need an account with them

How to Create Table in MySQL

To create a new table in MySQL, follow these steps:

1. Open the MySQL client.
2. Log in to the MySQL server using the following command:

mysql -u username -p

Replace username with your MySQL username. You will be prompted to enter your password.

1. Once you are logged in, select the database in which you want to create the table:

USE dbname;

Replace dbname with the name of the database in which you want to create the table.

1. Create a new table using the following command:

CREATE TABLE users (
    user_id INT,
    username VARCHAR(50),
    email VARCHAR(100)
);

This command creates a table named users with three columns: user_id, username, and email. Each column specifies its data type, defining what kind of data it can hold.

1. Verify that the table has been created by using the following command:

SHOW TABLES;

This will display a list of all the tables in the selected database, including the one you just created.

Column Definitions: Data Types, NULL and DEFAULT

When defining columns, you must specify a data type, such as INT, VARCHAR, or DATE. If you would rather pick types in a diagram than type them out, compare the options in the best MySQL database design tools. You can also define whether a column accepts NULL values. If a column is NOT NULL, it requires a value upon row creation. The DEFAULT keyword assigns a default value if none is provided.

CREATE TABLE products (
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Active'
);

Create Tables and Visually Manage MySql using DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Design and visualizeyour database schema

Edit referenced recordsin related tables

Query your datavisually too

Reuse the SQLgenerated

Free Download

DbSchema is a MySQL client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Create Table

Start the application and connect to the MySQL database. Right-click the table folder to create a table.

Add Table Columns

Add the columns to the table.

Conclusion

Creating a table in MySQL is a simple process. You just need to select the database in which you want to create the table and use the CREATE TABLE command to define the columns and data types for the table.