3.1 Comparing values: <, >, =, <>
One of the most common keywords that are used when writing SQL queries is the WHERE. It is with its help that we tell MySQL which table rows we want to filter (select).
After the keyword, WHEREyou can write very complex and tricky conditions. So what operations can this condition contain?
| Operator | Description | Example |
|---|---|---|
| < | Less | salary < 40000 |
| > | More | salary > 100000 |
| <= | Less or equal | age <= 25 |
| >= | More or equal | age >= 18 |
| = | Equals | occupation = 'Programmer' |
| <>, != | Not equal | Year(join_date) != 2015 |
Unlike the Java language, for strict equality, one equals character is used here, and not two .
If you want to check that the values are not equal, then this can be done in two ways: <>either !=The first approach is used in languages such as Pascal, the second is typical for languages such as C ++ and Java.
For assignment in SQL, it is used :=as in Pascal, but the assignment itself is used very rarely.
Let's write a query where we display all the employees who were not hired in 2015.
SELECT * FROM employee WHERE YEAR(join_date) != 2015
And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
| 2 | Petrov Petr | Programmer | 80,000 | 23 | 2013-08-12 |
| 3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
| 6 | Vaska | cat | 1,000 | 3 | 2018-01-01 |
3.2 Logical operations AND, OR, NOT
You can also WHEREuse the logical operations AND, ORand NOT. And they are written exactly as I described them here. None &&, ||or!
Let's write a SQL query where we select employees who are over 20 years old and receive a salary of less than 50K.
SELECT * FROM employee WHERE age > 20 AND salary < 50000
And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
| 5 | Kirienko Anastasia | Office Manager | 40,000 | 25 | 2015-10-10 |
Too easy. Let's write a query where we select employees who receive more than 100K and do not work as a director:
SELECT * FROM employee WHERE salary >= 100000 AND NOT occupation = 'Director'
Here we specifically !=used the operator instead of the symbol NOT.
And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
3.3 BETWEEN: value range
Also in SQL there is a special operator BETWEENwith which you can check whether a variable lies between two values. This operator is used inside boolean expressions. The general form of such an operator is:
variable BETWEEN bottom line AND top_bound
In this case, unlike the Java language, both the lower and upper boundaries are included in the allowed interval.
Let's write an example where we select all employees with a salary from 40K to 100K. The request will look like:
SELECT * FROM employee WHERE salary BETWEEN 40000 AND 100000And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
| 2 | Petrov Petr | Programmer | 80,000 | 23 | 2013-08-12 |
| 3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
| 5 | Kirienko Anastasia | Office Manager | 40,000 | 25 | 2015-10-10 |
The operator BETWEENsupports not only numeric types, but also dates and even string types . In the case of working with strings, the lexicographic order is used: aaba comes before aabb
3.4 IN: list of values
In addition, SQL has a special operator INwith which you can check whether a variable is contained in the specified list. This operator is used inside boolean expressions. The general form of such an operator is:
variable IN (value 1 , value 2 , ... value N)Let's write a query in which we select employees who are 20, 25 or 30 years old. The request will look like:
SELECT * FROM employee WHERE age IN (20, 25, 30)And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
| 3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
| 5 | Kirienko Anastasia | Office Manager | 40,000 | 25 | 2015-10-10 |
And now let's write a query, where, on the contrary, we select employees whose age is not 20, 25 or 30. The query will look like:
SELECT * FROM employee WHERE age NOT IN (20, 25, 30)And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 2 | Petrov Petr | Programmer | 80,000 | 23 | 2013-08-12 |
| 4 | Rabinovich Moisha | Director | 200,000 | 35 | 2015-05-12 |
| 6 | Vaska | cat | 1,000 | 3 | 2018-01-01 |
Everything worked as expected. Beauty.
3.5 LIKE: string pattern
And finally, another important and very often used operator - LIKE. It is used when comparing strings. It can be used to set a string pattern. This operator is used inside boolean expressions. The general form of such an operator is:
variable LIKE 'sample'Special characters can be used in the pattern: %and _.
- Underscore means any character, always 1 pc.
- Percent means any character and any number of times (including 0 times).
Let's write a query that will select all people whose last name begins with the letter "I". The request will look like:
SELECT * FROM employee WHERE name LIKE 'I%'And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
| 3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
Now let's complicate the task - the name should contain "o" and the name should end with "a". The request will look like:
SELECT * FROM employee WHERE name LIKE '%o%a'And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 4 | Rabinovich Moisha | Director | 200,000 | 35 | 2015-05-12 |
Okay, let's make it a little more difficult. Let's filter out all employees whose age starts with the number "3". The request will look like:
SELECT * FROM employee WHERE age LIKE '3%'And we get the following query result:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
| 4 | Rabinovich Moisha | Director | 200,000 | 35 | 2015-05-12 |
| 6 | Vaska | cat | 1,000 | 3 | 2018-01-01 |
By the way, our age column is of type int, so LIKEit works not only with strings. What can not but rejoice.
GO TO FULL VERSION