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:
- Table Name:
- users table columns:
userstable has three columns
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
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 –
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 –
- 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.
- DECIMAL(size, d)
- Date & Time
- DATE – Format: YYYY-MM-DD
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
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
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
Run the following SQL code to delete a table
DROP TABLE table_name;
DROP TABLE users;
DROP TABLE IF EXISTS users;
🔗 Now we will see – How to insert new data into the users table?