A brief introduction of conditions
Understand the general form of conditions
The most common condition is the following form:
Gyj@ocm> Select * from t1 where name= ' gyj2 ';
ID NAME SALARY
---------- ---------- ----------
2 Gyj2 8000
Criteria can be used to restrict the output of rows only after a where. The usual condition is in the form of a column name comparison operator value. In the example above, the comparison operator is "=", which is looking for a row with name equal to ' gyj2 '.
Second, comparison operators
1, the operator of the form of comparison symbol (PPT)
operator meaning
= equals
> Greater than
>= is greater than or equal to
< less than
<= less than or equal
<>,!=, ^= not equal to
To understand the meaning of the various comparison operators, this one is simpler, I will not say more.
2. Comparison symbol in non-operator form
Learn about the use of between, in, like, are NULL, exist, and understand the use of wildcards with like
(1) Column name [NOT] BETWEEN ... And ... : Between two numbers (and includes these two numbers)
Gyj@ocm> SELECT * from t1 where ID between 4 and 6;
ID NAME SALARY
---------- ---------- ----------
4 Gyj4 15000
5 Gyj5 12000
6 Gyj6 12000
Displays the employee number between 4 and 6. To add not before the between, the opposite is the meaning of two:
Gyj@ocm> SELECT * from t1 where ID is not between 4 and 6;
ID NAME SALARY
---------- ---------- ----------
2 Gyj2 8000
3 GYJ3 10000
7 Gyj7 12000
1 gyj1 12000
1 gyj1 20000
(2) Column name [NOT] in (value 1, value 2, ...). ): The column value is equal to the value 1 or equal to the value 2 or value 3, and so on.
Gyj@ocm> SELECT * from t1 where ID in (5,6,7);
ID NAME SALARY
---------- ---------- ----------
5 Gyj5 12000
7 Gyj7 12000
6 Gyj6 12000
Displaying the employee number equals 5,6,7, if we add not before in, the result is a row with an ID that is not equal to 5 or 6 or 7.
Gyj@ocm> SELECT * from t1 where ID is not in (5,6,7);
Gyj@ocm> SELECT * from t1 where ID is not in (5,6,7);
ID NAME SALARY
---------- ---------- ----------
2 Gyj2 8000
3 GYJ3 10000
4 Gyj4 15000
1 gyj1 12000
1 gyj1 20000
(3) Character column like ' string ': string-matching operator.
Gyj@ocm> SELECT * from t1 where name like ' Gyj2 ';
ID NAME SALARY
---------- ---------- ----------
2 Gyj2 8000
Look at its effect, it's kind of like "=". But its function is stronger than the equal sign. Because it can use the following wildcard characters:
_: Corresponds to any one character.
%: Any character corresponding to any length.
The% of any length can be from 0 to any length. And _ corresponding to the character, must be one.
Gyj@ocm> SELECT * from t1 where name like ' _o% ';
ID NAME SALARY
---------- ---------- ----------
9 Joe 22000
Ten Tom 28000