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.
| EmployeeID | Name | Title |
|---|---|---|
| 1002 | Webster W. | Executive |
| 1003 | Lizzy S. | Manager |
| 1004 | Oliver T. | Software Engineer |
| EmployeeID | Department | Age |
|---|---|---|
| 1002 | HR | 36 |
| 1003 | Sales | 21 |
| 1004 | IT | 39 |
In this article...
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.
