Common MySQL Queries


To save code redundancy, MySQL queries are often written just once before being wrapped in class functions. The top 10 MySQL queries are listed in this article.

In essence, a query is a request for data to be retrieved from a database table or set of databases. It is possible to write a variety of query languages to handle simple to sophisticated questions. Depending on the precise data that is needed to be found, queries will filter particular parameters. It can also do computations and automate data management operations. The 10 most used MySQL queries are covered in this article.

Let's utilize these test databases to learn more about how each query works.

EmployeeID

Name

Title

001

Hardik

CEO

002

Naman

CTO

003

Aadish

CFO

EmployeeID

Department

Age

001

Finance

19

002

Accounts

18

003

ERP

29

SELECT All

To retrieve and send back all the data from a database, perform a straightforward SELECT query. Its syntax may be expressed as follows;

SELECT *
FROM Employees;

Result

001

Hardik

CEO

002

Naman

CTO

003

Aadish

CFO

Use "*" to specify that you want all information provided in order to choose every column that is present in the table. To obtain data from the database table, second, use the FROM keyword. Finally, to correctly complete the request, always remember to terminate your sentence with a semicolon, ";".

SELECT Column

The field name should be used in place of the asterisk (*) if you just wish to get data from a particular column or columns and not all of them.

Code

SELECT Title
FROM Employees;

Result

Title

CEO

CTO

CFO

Multiple columns should be separated by commas when being selected from a database. the same;

SELECT Title, Name
FROM Employees;

WHERE clause

Data records will be filtered when the WHERE clause is used in a SELECT query, and the records that match the condition(s) will then be extracted as a result.

Code

SELECT *
FROM Employees
WHERE EmployeeID = 001;

Result

001

Hardik

CEO

The AND keyword can be used to separate several conditions in the WHERE clause.

CREATE TABLE

This query may be used to create a brand-new table with the columns of your choosing. Make careful to include the data type for each column when adding names.

Code

CREATE TABLE EmployeeInfo (

   EmployeeId INT,
   Department VARCHAR(25),
   Age NUMBER
);

Expected Table Results

EmployeeID

Department

Department

NULL

NULL

NULL

The preceding query generates an "EmployeeInfo" table in the database when you execute the CREATE TABLE command and fill it with column titles. Since we haven't entered any specific values, each column will essentially return NULL. This brings us to our following query, INSERT INTO.

INSERT INTO Query

The most typical query to add values to one or more rows of brand-new entries in a database is this one. We are able to add some example data to the EmployeeInfo database;

Code

INSERT INTO EmployeeInfo (EmployeeID, Department,Age)
VALUES 
(1002, HR, 46),
(1003, Finance, 28),
(1004, IT, 39);

Table Results

EmployeeInfo

EmployeeID

Department

Age

1002

HR

46

1003

Finance

28

1004

IT

39

The VALUES command must be a part of the whole statement when constructing an INSERT INTO query.

UPDATE QUERY

The current columns in a table can be changed with this term. This query will add new data to table records based on a criterion.

EmployeeInfo

EmployeeID

Department

Age

1002

HR

36

1003

Sales

21

1004

IT

39

Code

UPDATE EmployeeInfo
SET Age = 22
WHERE EmployeeID = 1003

Table Results

EmployeeInfo

EmployeeID

Department

Age

1002

HR

36

1003

Sales

22

1004

IT

39

Use the term SET after the UPDATE command to always indicate the column(s) you want to change, and then specify exactly WHERE you want the modified data applied.

DELETE FROM Query

Use the DELETE FROM command to delete records from a database depending on one or more criteria. The amount of rows that the query can influence is constrained when conditions are used.

Code

DELETE FROM Employees
WHERE Name =”Naman”;

Table Results

Employees

EmployeeID

Name

Title

1002

HR

Executive

1004

Aadish

Software Engineer

Using the WHERE condition allows you to specify exactly where you want data to be filtered out and performed, as was previously discussed in the article. The goal of this query is to remove all Employee table records that include the name "Naman."

Using GROUP BY, HAVING Clause

Aggregate functions almost typically employ the GROUP BY clause. By using this term, the result set will be grouped by one or more columns.

The result set is filtered using the keyword HAVING. In a perfect world, a WHERE clause would be used instead, however the HAVING clause places a restriction on aggregations.

EmployeeInfo

EmployeeID

Department

Age

1002

HR

36

1003

Sales

21

1004

IT

39

Code

SELECT COUNT(Age), EmployeeID
FROM EmployeeInfo
GROUP BY EmployeeID
HAVING COUNT(Age) > 21;

Results

COUNT(Age)

EmployeeID

39

1004

36

1002

Aggregate Functions (SUM, AVG & COUNT)

There are three widely popular aggregate functions that make it possible to analyse or do computations using data from a table.

COUNT − gives the number of rows that correspond to the given column.

AVG − returns a column's median value.

SUM − provides the total of all values in the chosen column.

EmployeeInfo

EmployeeID

Department

Age

1002

HR

36

1003

Sales

21

1004

IT

39

Code for COUNT

SELECT 
COUNT(Department)
FROM EmployeeInfo;

Results

COUNT(Department)

3

Code for AVG

SELECT AVG(Age)
FROM EmployeeInfo;
Results:

AVG(Age)

32

Code for SUM

SELECT SUM(Age)
FROM EmployeeInfo;
Results:

SUM(Age)

96

Joins

Rows from at least two tables are combined using joins when there is a linked column between the tables. The INNER, FULL, and LEFT joins are the most often used joins.

If the join condition is true, an INNER Join is used to integrate the rows from several tables.

When there is a match in the records of the left or right table, the FULL Join option returns all rows.

When using a LEFT Join, all rows from the left table are retrieved together with any matching records from the right table.

Employees – Table #1(Left)

EmployeeID

Name

Title

1002

Hardik

Executive

1003

Naman

Manager

1004

Aadish

Software Engineer

EmployeeInfo – Table #2(Right)

EmployeeID

Department

Age

1002

HR

36

1003

Sales

21

1004

IT

39

INNER JOIN Code

SELECT Employees.Title, EmployeeInfo.Department
FROM Employees 
INNER JOIN EmployeeInfo 
ON Employees.EmployeeID = Employees.EmployeeID;

Results

Title          Department
Executive      HR     
Manager        Sales     
Software       Engineer IT

FULL JOIN Code

SELECT * 
FROM Employees  
FULL JOIN EmployeeInfo  
ON Employees.EmployeeID = Employees.EmployeeID;

LEFT JOIN Code

SELECT Employees.Name, EmployeeInfo.Age
FROM Employees  
LEFT JOIN EmployeeInfo  
ON Employees.EmployeeID = Employees.EmployeeID;

Results

Name

Age

Webster W.

36

Lizzy S.

21

Oliver T.

39

Given that the "EmployeeID" column in the "Employee" database relates to the "EmployeeID" in the "EmployeeInfo" table, it can be said that the EmployeeID column serves as the link between the two tables. When sifting over a bigger database, joins can become challenging but are also quite beneficial.

By effectively speaking the same language, the user and the database are able to successfully share information. The questions mentioned above are the ones that both novices and experts utilize the most frequently. Writing MySQL queries is said to be the database administration task that is performed the most frequently.

Conclusion

In this segment, we came to know about different queries used in SQL to reduce code redundancy and make the experience user-friendly. A few of the commonly used queries are summarized in the table below

Query

Syntax

Function

SELECT ALL

SELECT *

To retrieve and send back all the data from a database, perform a straightforward SELECT query

SELECT COLUMN

SELECT Title

The field name should be used if you just wish to get data from a particular column or columns and not all of them.

WHERE CLAUSE

WHERE EmployeeID = 001;

Data records will be filtered when the WHERE clause is used in a SELECT query, and the records that match the condition(s) will then be extracted as a result.

CREATE TABLE

CREATE TABLE

EmployeeInfo (XYZ);

This query may be used to create a brand-new table with the columns of your choosing.

INSERT INTO

INSERT INTO EmployeeInfo

(EmployeeID, Department,Age)

VALUES

(XYZ),

(ABC);

The most typical query to add values to one or more rows of brand-new entries in a database is this one.

UPDATE

UPDATE EmployeeInfo

SET M=XX

WHERE EmployeeID=XXXX;

This query will add new data to table records based on a criterion.

Updated on: 06-Apr-2023

198 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements