Learn 7 MySQL Database Operations in Easy Way

By phptutorialpoints
April 27th, 2023

In this article, we’ll see MySQL Database Operations. MySQL is a relational database management system that can be used to store and manage data. Here is a link for mysql download in case you didn’t install it on your device. Some common operations you can perform with MySQL databases include:

Table of Contents

List of MySQL Database Operations:

1). Creating a database:

To create a new database in MySQL, you use the CREATE DATABASE statement for MySQL Database Operations. You specify the name of the database you want to create, and any additional options you want to set, such as the character set to use.

To create a database in MySQL, you can follow these steps:

Open the MySQL command-line tool. This can be done by typing the following command in your terminal or command prompt:

mysql -u username -p

Replace the username with your MySQL username. You will be prompted to enter your MySQL password.

Once you’re logged in, enter the following command to create a new database:

CREATE DATABASE dbname;

Replace the dbname with the name you want to give your new database.

You can verify that the database was created successfully by running the following command:

SHOW DATABASES;

This will display a list of all the databases on the MySQL server, including the one you just created.

To switch to the new database and start using it, run the following command:

USE dbname;

Again, replace the dbname with the name of your new database.

That’s it! You have now successfully created a new database in MySQL

2). Creating a table:

To create a new table in a database, you use the CREATE TABLE statement for MySQL Database Operations. You specify the name of the table, the columns and their data types, and any constraints you want to set, such as a primary key or foreign key. here is an example SQL code to create a basic table in MySQL:

CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);

This code will create a table named “users” with five columns: “id”, “username”, “email”, “password”, and “created_at”. The “id” column is set as the primary key, and it will auto-increment for each new record added to the table. The other columns are set as NOT NULL, which means they must have a value assigned to them when a new record is inserted. The “created_at” column has a default value of the current timestamp, which means it will automatically be set to the current time when a new record is inserted.

You can customize the column names, data types, and constraints based on your specific requirements.

3). Inserting data:

To insert data into a table, you use the INSERT INTO statement for MySQL Database Operations. You specify the name of the table, the columns you want to insert data into, and the data you want to insert. Here’s the basic syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Let’s break it down:

INSERT INTO specifies that you want to insert data into a table.
table_name is the name of the table where you want to insert data.
(column1, column2, column3, …) lists the columns where you want to insert data. You can omit this part if you want to insert values into all columns.
VALUES (value1, value2, value3, …) lists the values you want to insert into the specified columns. The values must be in the same order as the columns listed above.

Here’s an example:

INSERT INTO users (name, email, phone)
VALUES ('John Doe', 'johndoe@example.com', '555-1234');

This would insert a new row into the “users” table with the values ‘John Doe’, ‘johndoe@example.com’, and ‘555-1234’ in the name, email, and phone columns, respectively.

4). Retrieving data:

To retrieve data from a table, you use the SELECT statement for MySQL Database Operations. You specify the columns you want to retrieve and any conditions you want to use to filter the data. You can also use SQL functions and aggregate functions to manipulate the data before you retrieve it. you can use the SELECT statement. Here’s the basic syntax:

SELECT column1, column2, ...
FROM table_name;

Let’s break it down:

SELECT specifies that you want to retrieve data from a table.
column1, column2, … lists the columns you want to retrieve data from. You can use * to retrieve data from all columns.
FROM table_name specifies the name of the table you want to retrieve data from.

Here’s an example:

SELECT name, email, phone
FROM users;

This would retrieve data from the name, email, and phone columns in the “users” table.

5). Updating data:

To update data in a table, you use the UPDATE statement for MySQL Database Operations. You specify the name of the table, the columns you want to update, the new data you want to use, and any conditions you want to use to filter the data. Here’s the basic syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Let’s break it down:

UPDATE specifies that you want to update data in a table.
table_name is the name of the table where you want to update data.
SET column1 = value1, column2 = value2, … lists the columns you want to update and the new values you want to set.
WHERE the condition specifies which rows to update. If you omit the WHERE clause, all rows in the table will be updated.

Here’s an example:

UPDATE users
SET phone = '555-5678'
WHERE name = 'John Doe';

This would update the phone column in the “users” table to ‘555-5678’ for all rows where the name column is ‘John Doe’.

You can also use operators such as <, >, <=, >=, =, !=, LIKE, IN, and BETWEEN in your WHERE clause to specify more complex conditions.

6). Deleting data:

To delete data from a table, you use the DELETE statement for MySQL Database Operations. You specify the name of the table and any conditions you want to use to filter the data. Here’s the basic syntax:

DELETE FROM table_name
WHERE condition;

Let’s break it down:

DELETE FROM specifies that you want to delete data from a table.
table_name is the name of the table which you want to delete data from.
WHERE the condition specifies which rows to delete. If you omit the WHERE clause, all rows in the table will be deleted.

Here’s an example:

DELETE FROM users
WHERE name = 'John Doe';

This would delete all rows from the “users” table where the name column is ‘John Doe’.

You can also use operators such as <, >, <=, >=, =, !=, LIKE, IN, and BETWEEN in your WHERE clause to specify more complex conditions.

7). Joining tables:

To combine data from multiple tables, you use the JOIN statement for MySQL Database Operations. You specify the tables you want to join, and the conditions you want to use to match the data. There are different types of joins in MySQL, but the most commonly used ones are INNER JOIN, LEFT JOIN, and RIGHT JOIN. Here’s a brief overview of each:

INNER JOIN: Returns only the rows with a match in both tables based on the specified join condition.
LEFT JOIN: Returns all the rows from the left table and the matching rows from the right table based on the specified join condition. If there is no match in the right table, NULL values are returned for the right table columns.
RIGHT JOIN: Returns all the rows from the right table and the matching rows from the left table based on the specified join condition. If there is no match in the left table, NULL values are returned for the left table columns.

Here’s the basic syntax for using JOIN in MySQL:

SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

Let’s break it down:

SELECT columns specify the columns you want to retrieve from the joined tables.
FROM table1 specifies the first table you want to join.
JOIN table2 specifies the second table you want to join.
ON table1.column = table2.column specifies the join condition. This specifies which columns in the two tables to match up.

Here’s an example:

SELECT orders.order_id, customers.name, orders.order_date
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;

This would retrieve data from the order_id, name, and order_date columns in the orders and customers tables, joining them based on the customer_id column. The result set would contain only the rows with a match in both tables based on the customer_id column.

These are some of the common database operations you can perform with MySQL. Understanding these operations will allow you to effectively store, manage, and retrieve data from your databases.

You can follow the below video to learn basic MySQL Database Operations using the command line.

You can learn about MySQL commands here

I hope this article helps you learn MySQL Database Operations!

Last updated on July 8th, 2023.

List of MySQL Database Operations:

1). Creating a database:

2). Creating a table:

3). Inserting data:

4). Retrieving data:

5). Updating data:

6). Deleting data:

7). Joining tables:

Share this: