Top Common MySQL Queries

MySQL queries are usually written once and then wrapped in class functions to minimize code repetition. This article lists the 10 queries used most often in MySQL.

A Query is essentially a request for information to be retrieved from a database table or a combination of tables. There are many query languages that can be written to perform a range of simple to complex queries. Queries will filter certain criteria based upon the specific data it is requested to find. It can also automate data management tasks and engage in calculations. This guide discusses the 10 most common MySQL queries.

Let’s use these demo databases to help us further understand how each query functions.

EmployeeIDNameTitle
1002Webster W.Executive
1003Lizzy S.Manager
1004Oliver T.Software Engineer
EmployeeIDDepartmentAge
1002HR36
1003Sales21
1004IT39

SELECT All 

A simple SELECT query is used to fetch and send back all the data that’s from a database. Its syntax can be written as such;

Code:

SELECT *
FROM Employees;

Result:

1002       Webster W.     Executive
1003       Lizzy S.       Manager
1004       Oliver T.      Software Engineer

In order to select all the columns available in the table you must use “*” to indicate that you need all possible information returned. Secondly, state the FROM keyword in order to receive data from the database table.  Lastly, always remember to end your statement with a semicolon “;” to complete the request properly.

SELECT Column

To get data from specific column(s) and not everything, you simply want to just state the field name instead of using the asterisk(*).

Code:

SELECT Title
FROM Employees;

Result:

Title
Executive
Manager
Software Engineer

When selecting multiple columns from a database they should be separated by a comma. Like so;

SELECT Title, Name
FROM Employees;

WHERE clause

When utilizing the WHERE clause in a SELECT query, data records will be filtered, then extract the records that match the condition(s) as the result.

Code: 

SELECT *
FROM Employees
WHERE EmployeeID = 1004 ;

Results: 

1004       Oliver T.          Software Engineer 

The WHERE clause can have more than one condition that can be separated by using the AND keyword.

CREATE Table 

This query can be used to generate a new table with your rpreferred choice of columns. When adding column names be sure to specify their data type. 

Code:

CREATE TABLE EmployeeInfo (

EmployeeId INT,
Department VARCHAR(25),
Age NUMBER

);

Expected Table Results:

EmployeeID
Department
Department
NULL
NULL
NULL

Once you run the CREATE TABLE command and populate it with column titles, The above query creates a “EmployeeInfo” table in the database. Essentially each column will return NULL because we have not inserted any particular value. That leads us into our next query, INSERT INTO.

INSERT INTO Query 

This is the most common query to insert values into one or more rows of new records into a table. We are able to populate the the EmployeeInfo table with some sample data; 

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

When writing an INSERT INTO query, the VALUES command must be a part of the complete statement. 

UPDATE Query

This keyword is used to change one or more existing columns in a table. Using this query will update table records with new data based on some condition.

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

Following the UPDATE command, use the keyword SET to always specify which column(s) you choose to modify, then state exactly WHERE you need the updated data applied to.

DELETE FROM Query

To remove records from a table based on one or multiple conditions you would need to use the DELETE FROM statement. When using conditions it limits the number of rows being affected by the query. 

Code: 

DELETE FROM Employees
WHERE Name = ‘Lizzy’;

Table Results:

Employees

EmployeeID
Name
Title
1002
Webster W.
Executive 
1004
Oliver T.
Software Engineer 

As previously covered in the article; using the WHERE condition determines exactly where you request data to be filtered out and executed. That query is to delete every record from the table of Employees that matches the name  “Lizzy”.

Using GROUP BY, HAVING Clause 

The GROUP BY clause is mostly always used with aggregate functions. Using this keyword will group together the result-set by one or more columns.

The HAVING keyword is used to filter that result set. Ideally, you would think that a WHERE clause would be put into place instead however, the HAVING clause introduces a condition 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 extremely common aggregate functions that allow data from a table to be interpreted or run calculations. 

  • COUNT: returns the number of rows that matches a specified column.
  • AVG:  returns the average value of a column
  • SUM: returns the sum of all the values in a selected 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

Joins are used to bring together rows from at least two tables, based on a related column between the tables. The most common practice joins are INNER, FULL and LEFT.

  • INNER Join – combines the rows from different tables if the join condition is true.
  • FULL Join –  returns all rows when there is a match in left or right table records.
  • LEFT Join – retrieves all rows from the left table and the matching records from the right table.

Employees – Table #1(Left)

EmployeeID Name Title
1002 Webster W. Executive 
1003 Lizzy S. Manager
1004 Oliver T. 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;

Results:

Will grab all rows and columns from both tables, including duplicates, and combine them into one whole table.

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

The “EmployeeID” column in the “Employee” table refers to the “EmployeeID” in the “EmployeeInfo” table, essentially stating that the EmployeeID column is the relationship between both tables. Joins can become tricky but simultaneously extremely helpful when sorting through a larger database. 

The user and the database are able to successfully exchange information by essentially speaking the same language. The queries listed above are the most commonly used by both beginners and experts. Writing MySQL queries is considered the most frequent function within the domain of database management.

This article was reviewed and updated in April 2022 by Kelsey Perkins.

 

Kelsey Perkins
Kelsey Perkins
Kelsey Perkins is a contributing writer for DatabaseJournal.com. A background in broadcast journalism led to software engineering bootcamp where she mastered JavaScript, React.js, MongoDB, and MySQL. She plans to link her two passions of telecommunications and programming as a full-stack developer for broadcast news.

Latest Articles