How to create, modify, and drop a MySQL table using SQL scripts?
Tutorial to learn how to create, modify, empty, and drop a table with MySQL scripts. MySQL is a relational database management system (RDBMS) that allows you to store and manage data in an organized way. In this tutorial, we will see how to create a table, insert data into it, delete data from it, modify the table to add or delete columns, clear all data from the table, and delete the table.
Table of Contents
1. Create a MySQL table
To create a table in a MySQL database, we use the CREATE TABLE command. Here is an example code that creates a table named users with two columns, id and username, respectively of type INT and VARCHAR(255).
The following code create a MySQL table, to execute it, connect to PHPMyAdmin and run the code. This will create a
users table with 5 different columns.
- id : an integer that will be automatically incremented and used as the primary key for the table
- username: a string with a maximum length of 255 characters, and is not allowed to be NULL
- email: a string with a maximum length of 255 characters, and is not allowed to be NULL
- password: a string with a maximum length of 255 characters, and is not allowed to be NULL
- created_at: a datetime value that will be set to the current timestamp by default
CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
We can also specify constraints for each column. For example, we have defined the id column as the primary key of the table and as an auto-incremented integer.
To verify that the table was successfully created, we can use the SHOW TABLES command, as follows.
Check out this detailed tutorial to create a Pivot Table with Excel to analyse data easily.
2. Insert data into a MySQL table
To insert data into a MySQL table, we use the INSERT INTO command. Here is an example code that inserts a record into the users table:
INSERT INTO users (username, email, password) VALUES ('johndoe', 'firstname.lastname@example.org', 'mypassword');
We can also insert multiple records at once using the following syntax:
INSERT INTO users (username, email, password) VALUES ('johndoe', 'email@example.com', 'mypassword'), ('janedoe', 'firstname.lastname@example.org', 'anotherpassword');
To insert a row and specifying the data type for each value, especially the DATE. Please note that the following statement will duplicate the line with the same name, because the primary key is the auto incremented ID and not the user name.
To allow one unique name only, change the table structure to add a constraint on the username column.
INSERT INTO users (username, email, password, created_at) VALUES ('johndoe', 'email@example.com', 'mypassword', CAST('2022-01-01' AS DATE));
3. Delete data with MySQL
To delete data from a MySQL table, we use the standard SQL DELETE FROM command.
3.1 Deleting all data from a MySQL table
Here is an example code that deletes all records from the users table.
DELETE FROM users;
3.2 Deleting specific data from a MySQL table
We can also use a WHERE clause to specify deletion conditions. For example, to delete a record from the users table where the username is ‘johndoe’, we can use the following query.
DELETE FROM users WHERE username = 'johndoe';
4. Modify columns with MySQL
It is possible to modify a MySQL table by adding or deleting columns.
4.1 Adding a MySQL Column
To add a column to a MySQL table, we use the ALTER TABLE and ADD COLUMN commands. Here is an example code that adds a column named email to the users table.
ALTER TABLE users ADD COLUMN email VARCHAR(255);
4.2 Deleting a Column from a MySQL Table
To delete a column from a MySQL table, we use the ALTER TABLE and DROP COLUMN commands. Here is an example code that deletes the email column from the users table.
ALTER TABLE users DROP COLUMN email;
5. Clear all data from a MySQL table
To clear all data from a MySQL table, we use the TRUNCATE TABLE command. Here is an example code that clears all data from the users table.
TRUNCATE TABLE users;
6. Drop a MySQL table from a database
To delete a MySQL table, we use the DROP TABLE command. Here is an example code that deletes the users table.
DROP TABLE users;
7. Conclusion on how to manage MySQL tables
In this tutorial, we have learned how to create, modify, clear, and delete a MySQL table using various SQL commands. It is important to note that these actions are irreversible, so it is always a good idea to make a backup of your data before making any changes. It is also recommended to use these commands with caution, as they can have a significant impact on your database.
Another tutorial on how to connect to PHPMyAdmin and execute SQL queries to create a table with a primary key.