MySQL is a popular open-source relational database management system that provides efficient storage and retrieval of data. One of the fundamental aspects of working with databases is performing CRUD operations, which stands for Create, Read, Update, and Delete.
Let's explore how to execute these operations in MySQL.
To store data, start by creating a table using the CREATE TABLE statement. Define the table’s columns, their data types, and any additional constraints. For instance, let’s create a table named users with three columns: id, name, and email.
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),email VARCHAR(50));
To add records to the table, use the INSERT INTO statement. Specify the table name, followed by the column names and their corresponding values. Here’s an example of inserting a new user into the users table.
INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');INSERT INTO users (name, email) VALUES ('Olive Yew', 'oliveyew@example.com');
To fetch data from a table, utilize the SELECT statement. Specify the columns to retrieve or use the wildcard (*) to fetch all columns. You can also apply conditions using the WHERE clause. Here’s an example that retrieves all users from the users table.
SELECT * FROM users;
To retrieve specific columns or apply conditions, modify the query accordingly. For instance, to fetch users with a specific email address:
SELECT name FROM users WHERE email = 'johndoe@example.com';
To modify existing records, employ the UPDATE statement. Specify the table name, followed by the column to update and the new value. Apply conditions using the WHERE clause to determine which rows to update. Here’s an example that updates the email address of a user.
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;SELECT * from users
To remove records from a table, use the DELETE FROM statement. Apply conditions using the WHERE clause to determine which rows to delete. Here’s an example that deletes a user from the users table:
DELETE FROM users WHERE id = 1;SELECT * from users
CRUD operations (Create, Read, Update, Delete) are essential in working with databases. By following the examples, you can learn to perform these actions in MySQL tables.
Free Resources