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.