How to create a table in MySQL or MariaDB

Creating a table in MySQL or MariaDB defines the schema that keeps structured data consistent, searchable, and enforceable over time. A well-designed table makes inserts predictable, queries efficient, and integrity rules explicit instead of implied by application code.

Tables are created with the CREATE TABLE statement, which declares column names, data types, and constraints such as PRIMARY KEY, NOT NULL, and UNIQUE. The server stores this definition in its data dictionary and uses it to validate incoming data, build indexes, and choose execution plans for queries.

Schema decisions tend to outlive the first use case, so data types and constraints deserve deliberate choices. Explicitly setting the storage engine and character set avoids surprises across environments, and DDL statements can implicitly commit or rebuild tables depending on the change. The DROP TABLE operation is permanent data removal unless backups exist.

1. Start the MySQL or MariaDB command-line client.

   $ mysql --user=root --password
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Server version: 8.0.x MySQL Community Server - GPL

   Some systems provide the client as mariadb instead of mysql.

2. Select the target database with the USE statement.

   mysql> USE your_database_name;
   DATABASE changed

3. Create the table with an explicit schema using CREATE TABLE.

   mysql> CREATE TABLE IF NOT EXISTS Employees (
       EmployeeID INT NOT NULL AUTO_INCREMENT,
       FirstName VARCHAR(50) NOT NULL,
       LastName VARCHAR(50) DEFAULT NULL,
       BirthDate DATE DEFAULT NULL,
       PRIMARY KEY (EmployeeID)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   Query OK, 0 ROWS affected (0.02 sec)

   utf8mb4 supports full Unicode, and InnoDB is the standard transactional engine for MySQL and MariaDB.

4. Confirm the new table appears in the database with SHOW TABLES.

   mysql> SHOW TABLES;
   +----------------------------+
   | Tables_in_your_database_name|
   +----------------------------+
   | Employees                   |
   +----------------------------+
   1 ROW IN SET (0.00 sec)

5. Inspect the exact DDL stored by the server with SHOW CREATE TABLE.

   mysql> SHOW CREATE TABLE Employees\G
   *************************** 1. ROW ***************************
          TABLE: Employees
   CREATE TABLE: CREATE TABLE `Employees` (
     `EmployeeID` INT NOT NULL AUTO_INCREMENT,
     `FirstName` VARCHAR(50) NOT NULL,
     `LastName` VARCHAR(50) DEFAULT NULL,
     `BirthDate` DATE DEFAULT NULL,
     PRIMARY KEY (`EmployeeID`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
   1 ROW IN SET (0.00 sec)

   SHOW CREATE TABLE is the quickest way to verify engine, charset, indexes, and constraint names exactly as created.

6. Review column-level metadata with DESCRIBE.

   mysql> DESCRIBE Employees;
   +------------+-------------+------+-----+---------+----------------+
   | FIELD      | TYPE        | NULL | KEY | DEFAULT | Extra          |
   +------------+-------------+------+-----+---------+----------------+
   | EmployeeID | INT         | NO   | PRI | NULL    | AUTO_INCREMENT |
   | FirstName  | VARCHAR(50) | NO   |     | NULL    |                |
   | LastName   | VARCHAR(50) | YES  |     | NULL    |                |
   | BirthDate  | DATE        | YES  |     | NULL    |                |
   +------------+-------------+------+-----+---------+----------------+
   4 ROWS IN SET (0.01 sec)

   This output summarizes data types, nullability, index participation, defaults, and auto-increment behavior.

7. Insert a test row to confirm the table accepts data.

   mysql> INSERT INTO Employees (FirstName, LastName, BirthDate)
       -> VALUES ('Ada', 'Lovelace', '1815-12-10');
   Query OK, 1 ROW affected (0.01 sec)

8. Query the table to confirm the inserted row returns as expected.

   mysql> SELECT EmployeeID, FirstName, LastName, BirthDate FROM Employees;
   +------------+----------+----------+------------+
   | EmployeeID | FirstName | LastName | BirthDate  |
   +------------+----------+----------+------------+
   |          1 | Ada      | Lovelace | 1815-12-10 |
   +------------+----------+----------+------------+
   1 ROW IN SET (0.00 sec)

9. Add a column to an existing table with ALTER TABLE when the schema needs to evolve.

   mysql> ALTER TABLE Employees ADD COLUMN Email VARCHAR(100) DEFAULT NULL;
   Query OK, 0 ROWS affected (0.03 sec)
   Records: 0  Duplicates: 0  Warnings: 0

   Some ALTER TABLE operations may rebuild large tables or hold locks depending on version, engine, and the specific change.

10. Remove a table with DROP TABLE only when permanent deletion is acceptable.

    mysql> DROP TABLE Employees;
    Query OK, 0 ROWS affected (0.02 sec)

    DROP TABLE permanently deletes the table definition and all rows, and recovery depends on backups or point-in-time restore.