In operators and LIKE clauses: various queries

The judgment of a specified range: in operator

The in operator represents the scope of a specified query, for example, now has the following query requirement:

Example: Query employee information for employee number 7369, 7566, 7799

If it is done with the first practice, using the or operation;

SELECT * from EMP

WHERE empno=7369 or empno=7566 or empno=7799;

If you now use the new operator in, the code is simple;

SELECT * from EMP

WHERE empno in (7369,7566,7799);

And what if you're using not in? Indicates that it is not in the specified range.

SELECT * from EMP

WHERE empno not in (7369,7566,7799);

Note points: questions about not in

If you use the in operator now, NULL exists in the scope of the query and does not affect the query;

SELECT * from EMP WHERE empno in (7369,7566,null);

If you are using the not in operator now, if you have null in the scope of the query, you are querying all the data.

SELECT * from EMP WHERE empno not in (7369,7566,null);

For this limitation, remember now as a feature and later explain why NULL does not appear in the.

Two, fuzzy query: LIKE clause

The function of the LIKE clause is to provide the operation of the Fuzzy Lookup, for example: Some programs appear on the search operation, all belong to the implementation of such clauses, but must be reminded that search engine queries are not like.

But to use the LIKE clause you must know two matching symbols:

Match a single character: _; -> A

Match any number of characters:%; -> 0, one, multiple

Example: Ask to query all employee information that starts with the letter A in the employee's name

SELECT * from emp WHERE ename like ' a% ';

Example: Ask to query for all employee information for the second letter in employee's name

SELECT * from emp WHERE ename like ' _a% ';

Example: Ask for an employee with the letter A in the employee's name

SELECT * from emp WHERE ename like '%a% ';

You can now also use the not operation to reverse the operation:

SELECT * from emp WHERE ename isn't like '%a% ';

However, for a LIKE clause, it may not necessarily be represented on string data, but can be represented on arbitrary data:

SELECT * from emp WHERE ename like '%1% ' or hiredatelike '%1% ' or sal like '%1% ';

Description: About the use of like clauses

In development, the database's fuzzy query certainly uses the LIKE clause, but when using the LIKE clause, there is one of the biggest points of attention: if you do not set any query keywords on the fuzzy query ('%% '), it means that all records are queried:

SELECT * from emp WHERE ename like ' percent ' or hiredatelike '% ' or sal like ' percent% ';

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/