SQL command to select from a database

MySQL Dec 28, 2022

The SELECT command allows you to get data from the database. It is possible to set various filters and limits on the selection. Let's try to give some examples of SQL queries with a selection constraint in the USERS table, which contains user data.

WHERE clause in SQL query

Getting all the records in a table in one query is a very rare case in real projects. Often, either one record is needed, or a range, for example, of 10 or 100 records, or that meets a certain condition. Such a selection can be made using the WHERE condition command in an SQL query.

Comparison (=, !=, <, >, <=, >=)

Let's demonstrate this condition on the restriction of the sample by user ID. Here are a few examples of queries:

SELECT * FROM `USERS` WHERE `ID` = 2;
SELECT * FROM `USERS` WHERE `ID` != 2;
SELECT * FROM `USERS` WHERE `ID` < 2;
SELECT * FROM `USERS` WHERE `ID` <= 2;
SELECT * FROM `USERS` WHERE `ID` > 2;
SELECT * FROM `USERS` WHERE `ID` >= 2;

As you can guess from the mathematical symbols in these queries, the selection is limited by user ID (integer).

Substring search (LIKE) and full match (=)

The equal sign "=" can be used in SQL queries not only for numbers, but also for strings. Imagine that we need to get a selection from the database, which will contain data about a user named "Deep". The request will look like this:

SELECT * FROM `USERS` WHERE `NAME` = 'Deep';

As a result, we will get all the rows in which the username column "NAME" contains the string "Deep". Please note that the match must be complete. That is, users whose names are "Gray Deep", "White Deep", "Black Deep" will not be included in the sample. To select these users as well, you need to do a text search on the value of the column. For this, the "LIKE" command is used.

The "LIKE" command can be used to search for a substring in a column. To do this, put a percent sign "%" on the side of the substring that can contain other characters. For instance:

SELECT * FROM `USERS` WHERE `NAME` LIKE 'Deep%';

The selection results will include not only the user with the name "Deep", but also "Gray Deep", "White Deep", "Black Deep". If you put the percent sign before the substring:

SELECT * FROM `USERS` WHERE `NAME` LIKE '%Deep%';

then not only all previous results will be included in the selection, but also the user with the name "Big deep".LIKE makes the search case-insensitive. That is, the results from '%deep%' and '%DEEP%' will be the same.

AND and OR logic

There are times when you need to set multiple constraints related to logic. For example, if you need to select users with IDs from 2 to 5, then you can use the condition with "AND":

SELECT * FROM `USERS` WHERE `ID` >= 2 AND `ID` < 5;

The number of conditions and "AND" is unlimited:

SELECT * FROM `USERS` WHERE `ID` >= 2 AND `ID` < 5 AND `NAME` LIKE 'Deep%';

It is possible to use "OR" logic thanks to the "OR" condition. Let's demonstrate this:

SELECT * FROM `USERS` WHERE `ID` < 2 OR `ID` > 5;

Parentheses ( ) can be used to group OR and AND conditions:

SELECT * FROM `USERS` WHERE (`ID` >= 2 AND `ID` < 5) OR (`ID` > 10 AND `NAME` LIKE 'Deep%');

Selecting specific columns in SELECT

In this article, all fields were requested in all SELECT queries to the database. Because after the word SELECT there was an asterisk * . But the larger the amount of data you sample, the slower the database returns the answer. Therefore, try to request from the database only what you will use. For example, if you only want to get the user ID and the name 'NAME', then list these fields separated by a comma after the word SELECT:

SELECT `ID`, `NAME` FROM `USERS` WHERE `ID` <= 5;

Site databases are not designed to receive large samples. They work fastest on volumes up to 100 lines. If you try to query 100,000 rows from the database and specify * instead of specific fields , you can see a significant performance drop. And the slower your site loads, the fewer visitors will come to it. Therefore, always try to optimize your database queries.

Sorting (ORDER BY)

Using the ORDER BY command, you can sort the result that will be issued from the database. The result can be sorted by field value. Either forward "ASC" or reverse "DESC". Here is an example of usage:

SELECT * FROM `USERS` WHERE `ID` > 2 ORDER BY `ID` DESC;

At the end of this query, you can see the construction "ORDER BY `ID` DESC", which says that the selection will be sorted in descending order of the value of the ID column. If you need to do in ascending order, then there will be such a request:

SELECT * FROM `USERS` WHERE `ID` > 2 ORDER BY `ID` ASC;

You can do two sorts. To do this, after "ORDER BY" you need to write the column name and sort direction separated by commas. For example, let's sort the selection by ascending ID but descending by date (column DATE):

SELECT * FROM `USERS` ORDER BY `ID` ASC, `DATE` DESC;

Limitation (LIMIT)

When working with a database, you should always remember that the database is a rather slow tool. Therefore, it is necessary to minimize not only the number of requests to it, but also the amount of data selected from it. This can be done in three ways. And it's better to use them all at the same time:

Make a selection of certain fields, not all fields of the table. To do this, after the SELECT command, you must put not * , but the name of the selected fields separated by commas.
Use WHERE conditions that limit the sample size.
Use LIMIT (limit) on the number of selected rows.

We will talk about the last method separately. The limit is given as a number. For example, if you write "LIMIT 1;", then no more than 10 rows from the table will be returned as a result of the SQL query. To set a limit, write it at the very end of the request:

SELECT * FROM `USERS` LIMIT 10;

Setting a fetch limit can significantly speed up some database queries. Do not forget that the slower the pages of your site load, the less visitors there will be. Because people don't like to wait for a page to load. Loads longer than 2 seconds push away more than 60% of the site's audience.