How to create tables in a MySQL database?

The following table’s image is a visual representation of a MySQL database table that we are going to create in this tutorial.

• SQL code for creating a table.
• Create tables using MySQL Workbench.
• Create tables using phpMyAdmin.
• Creating tables using CLI.
• Delete a MySQL table.

---

✒️ Database and Table info

• Database Name: my_test_db
• Table Name: users
• users table columns: users table has three columns name, age, and email.

Note: one database can have multiple tables, which means you can create multiple tables in a single database.

So first, open your MySQL and create a Database called my_test_db.

After creating the my_test_db database, we will create a table called users inside this database.

SQL Syntax for creating a table

Syntax of creating a table in a MySQL database.

CREATE TABLE table_name(
	column_name1 DATA_TYPE(SIZE),
    column_name2 DATA_TYPE(SIZE),
    column_name1 DATA_TYPE(SIZE)
);

• DATA_TYPE – Which type of data you want to store in a Column such as – Number, String, Date.
• SIZE – You can define the size of a column like – how many characters a column can store or how many bytes a column can store.

SQL Data Types

There are three main data types in SQL – String, Number, and Date & Time.

These three data types are divided into several types according to size. For Example –

• String
  • CHAR(size) – 0 to 255 characters.
  • VARCHAR(size) – 0 to 65535 characters.
  • TEXT(size) – Holds a string with a maximum length of 65,535 bytes.
  • more…
• Number
  • INT(size)
  • BOOLEAN(size)
  • DECIMAL(size, d)
  • more…
• Date & Time
  • DATE – Format: YYYY-MM-DD
  • TIME(fsp)
  • DATETIME(fsp)
  • more…

Creating the `users` table and its columns

CREATE TABLE IF NOT EXISTS users(
	name VARCHAR(50),
    age TINYINT,
    email VARCHAR(50)
);

1. Create table using MySQL Workbench

Switch to the database where you want to create the table, then run the above SQL code to create the users table.

Now, refresh the list of Schemas.

If your SQL code has run successfully, you will see the users table and its columns.

---

2. Create MySQL table using phpMyAdmin

Click on the database to switch to the database.

Go the SQL tab and write the SQL code for creating the users table, and then click on the go button to run the SQL code.

After running the SQL code successfully, you can see the users table link under the my_test_db, click on that.

Now, click on the structure tab to see the structure of the users table.

---

3. Create MySQL table using CLI

First login to your MySQL

sudo mysql -u root -p

Switch to the database where you want to create the table

MariaDB [(none)]> use my_test_db;
Database changed
MariaDB [my_test_db]>

Now Run the SQL code of creating the table

---

How to delete a MySQL table

First select the database

use my_test_db;

Run the following SQL code to delete a table

DROP TABLE table_name;

DROP TABLE users;

OR

DROP TABLE IF EXISTS users;

---

What Next?

🔗 Now we will see – How to insert new data into the users table?