SQL operators for Oracle database SQL development
Welcome to reprint, reprint please indicate the source: blog.csdn.net/notbaron/ar…
SQL operators can restrict the rows returned by a query by pattern matching with string or value lists, value ranges, and null values.
You can use NOT to reverse the meaning of an operator.
1. the LIKE operator
You can use the LIKE operator in the WHERE clause to see if a string in a column matches the specified pattern.
Underscore _ matches a character at the specified position
The percent character % matches any character starting at the specified position
‘_o%’ represents the string with the second letter o, as follows
SQL> select * from customerswhere first_name like ‘_o%’;
CUSTOMER_IDFIRST_NAME LAST_NAME DOB PHONE
——————— ———- ——— ————
1 John Brown 01-JAN-65 800-555-1211
5 Doreen Blue 20-MAY-70
Select NOT LIKE from NOT LIKE;
SQL>select * from customers where first_name not like ‘_o%’;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
4 Gail Black 800-555-1214
If you need to text match underscores or percent characters in a string, you can use the ESCAPE option to identify those characters. For example,
‘% \ % %’ ESCAPE ‘\’
The second % is the actual character to search for. How does the high-speed database distinguish between characters to be searched and wildcards?
Such as:
SQL>select name from promotions where name like ‘%\%%’ ESCAPE ‘\’;
NAME
——————————
10% off Z Files
20% off Pop 3
30% off Modern Science
20% off Tank War
10% off Chemistry
20% off Creative Yell
15% off My Front Line
7 rows selected.
2. IN operator
You can also use the IN operator IN the WHERE clause to retrieve rows whose column values are IN a list.
Such as:
SQL> select * from customerswhere customer_id in (2,3,5);
CUSTOMER_IDFIRST_NAME LAST_NAME DOB PHONE
——————— ———- ——— ————
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
5 Doreen Blue 20-MAY-70
Use NOT IN to retrieve rows that are NOT retrieved by IN
SQL>select * from customers where customer_id not in (2,3,5);
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
1 John Brown 01-JAN-65 800-555-1211
4 Gail Black 800-555-1214
NOTIN returns FALSE if null values are included.
3. BETWEEN operator
You can use the BETWEEN operator in the WHERE clause to retrieve rows whose column values are contained within a specified range.
Such as:
SQL>select * from customers where customer_id between 1 and 3;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
Use NOT BWTWEEN to retrieve the following:
SQL>select * from customers where customer_id not between 1 and 3;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
4 Gail Black 800-555-1214
5 Doreen Blue 20-MAY-70