How to create a table in MySQL or MariaDB

Tables are essential components of relational databases such as MySQL and MariaDB. These systems store data in tables, which consist of columns and rows to organize and retrieve data efficiently. Both MySQL and MariaDB are widely used for managing structured data in applications due to their reliability and open-source nature.

Creating a table requires defining the structure by specifying columns, data types, and constraints. This ensures the data stored in the table is consistent and follows the required rules, such as unique identifiers or restrictions on null values. The process of creating tables is the same in both MySQL and MariaDB because MariaDB is a fork of MySQL, meaning it shares much of the same syntax and features.

Understanding how to create and manage tables in MySQL or MariaDB is necessary for anyone working with databases. It allows for the proper setup of a database schema, making data handling and querying efficient. This guide outlines the basic steps for creating, modifying, and viewing tables in these database systems.

1. Open the MySQL or MariaDB command-line interface.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 8
   Server version: 8.0.27 MySQL Community Server - GPL

2. Connect to the desired database using the USE command.

   mysql> USE your_database_name;
   DATABASE changed

3. Use the CREATE TABLE command to define the table structure.

   mysql> CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
       FirstName VARCHAR(50) NOT NULL,
       LastName VARCHAR(50),
       BirthDate DATE
   );
   Query OK, 0 ROWS affected (0.02 sec)

4. Verify the table creation by listing the tables with the SHOW TABLES command.

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

5. Use the DESCRIBE command to view the table structure.

   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 shows details about each column, including data types, nullability, and constraints.

6. To modify the table, use the ALTER TABLE command.

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

7. Delete a table using the DROP TABLE command.

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

   Be cautious when using the DROP TABLE command, as it permanently deletes the table and all its data.