Previously, I talked about some simple use of the SELECT statement. This time, I will learn more about the SELECT statement.
Including: Where condition limit query, range query, fuzzy query, and sorting of query results.
Where condition-limited Query
Select * from EMP where EMP. deptno = 10; -- Query all employees whose apartment number is 10 select * from EMP where job = 'cler'; -- note that cler' is case sensitive.
We know that condition is composed of column name expressions, constants, and comparison operators. What comparison operators are there?
Common comparison Operators
The comparison operators above will not be tried one by one! In the future.
Range Query (between and)
In reality, we need to query a data segment, which can be implemented using between and.
-- Query the select * from EMP where Sal between 4000 and 8000 employees with a salary;
TIPS: It is important to note that values must be written after the value of between, otherwise the statement will not be wrong.
But it is impossible to query the results!
Fuzzy search (like, not like)
Like Fuzzy queries are generally used for character matching. The wildcard characters "%" and "_" can be used for character matching:
%: Represents any character, including zero; _: represents any character;
Simple Example:
Select * from EMP where ename like '_ M %'; -- query the employee whose name is the second character "M"
However, what needs to be considered here is: what if the strings we want to query contain "%", "-", and so on?
Solution:
Use escape
Simple Example:
Insert into EMP (ename, empno) values (K % iritor, 100 ); -- insert a statement manually select * from EMP where ename like 'K/% iritor 'escape '/';
Add the "/" Escape Character to escape (you can specify the escape character as needed) to add the characters following the Escape Character
Escape to the original character. The Escape Character is specified to be related to the value of the field to be queried.
As for not like, there will be no demonstration too much!
Result sorting
ASC: sort in ascending order. The default value is.
select *from emp order by sal;
Desc: sort in descending order
select *from emp order by sal;
How can I sort multiple columns in a single column?
-- First sort the wages in ascending order, and if the wages are the same, sort the names in descending order.
select *from emp order by sal asc,ename desc;
That's all. Some comparison operators in the above table will be used in the future!