MySQL IN BETWEEN LIKE Operators
Retrieving all the records
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | DB | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> USE DB; Database changed mysql> SHOW TABLES; +--------------+ | Tables_in_DB | +--------------+ | holders | +--------------+ 1 row in set (0.01 sec) mysql> SELECT * FROM holders; +-------------+---------------+---------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+---------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348990 | Donald Taylor | Irvine | 1990-08-20 | Citi | 7000 | | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348992 | Ryan Bakshi | Mumbai | 1982-01-14 | Citi | 50000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | | 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 | | 25622348995 | James Moore | NULL | 1978-06-26 | Citi | 97800 | +-------------+---------------+---------+------------+----------+--------+ 7 rows in set (0.00 sec)
Using IN operator
The IN operator is used to specify multiple OR conditions.
mysql> SELECT * FROM holders WHERE city IN ('Mumbai','Irvine');
+-------------+---------------+--------+------------+-------+--------+
| account_no | name | city | dob | bank | amount |
+-------------+---------------+--------+------------+-------+--------+
| 25622348990 | Donald Taylor | Irvine | 1990-08-20 | Citi | 7000 |
| 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 |
| 25622348992 | Ryan Bakshi | Mumbai | 1982-01-14 | Citi | 50000 |
+-------------+---------------+--------+------------+-------+--------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM holders WHERE city NOT IN ('Mumbai','Irvine');
+-------------+--------------+---------+------------+----------+--------+
| account_no | name | city | dob | bank | amount |
+-------------+--------------+---------+------------+----------+--------+
| 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 |
| 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 |
| 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 |
+-------------+--------------+---------+------------+----------+--------+
3 rows in set (0.00 sec)
Using BETWEEN operator
The BETWEEN operator used to select values in a specified range and often used in the WHERE clause of SELECT and UPDATE statements. The following syntax and queries will help you to understand practically.
mysql> SELECT * FROM holders WHERE amount BETWEEN 70000 AND 150000; +-------------+---------------+--------+------------+-------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+--------+------------+-------+--------+ | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 | | 25622348995 | James Moore | NULL | 1978-06-26 | Citi | 97800 | +-------------+---------------+--------+------------+-------+--------+ 3 rows in set (0.00 sec) mysql> SELECT account_no, name FROM holders WHERE amount BETWEEN 70000 AND 150000; +-------------+---------------+ | account_no | name | +-------------+---------------+ | 25622348991 | Edward Parkar | | 25622348994 | Aanya | | 25622348995 | James Moore | +-------------+---------------+ 3 rows in set (0.00 sec)
Using LIKE operator (percentage wildcard)
The LIKE operator with percentage wildcard often used with the WHERE clause to retrieve specific records by specifying the pattern. The percentage wildcard represents zero, one, or multiple characters, whereas the underscore represents a single character. If you observe the following queries, we have used LIKE j%, which means the name must start with j character, and LIKE %r, which means the name must end with r character. Lastly, we used LIKE %ar%, which means the name must contain ar characters in between.
mysql> SELECT * FROM holders; +-------------+---------------+---------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+---------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348990 | Donald Taylor | Irvine | 1990-08-20 | Citi | 7000 | | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348992 | Ryan Bakshi | Mumbai | 1982-01-14 | Citi | 50000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | | 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 | | 25622348995 | James Moore | NULL | 1978-06-26 | Citi | 97800 | +-------------+---------------+---------+------------+----------+--------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM holders WHERE name LIKE 'j%'; +-------------+-------------+---------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+-------------+---------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348995 | James Moore | NULL | 1978-06-26 | Citi | 97800 | +-------------+-------------+---------+------------+----------+--------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM holders WHERE name LIKE '%r'; +-------------+---------------+--------+------------+-------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+--------+------------+-------+--------+ | 25622348990 | Donald Taylor | Irvine | 1990-08-20 | Citi | 7000 | | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | +-------------+---------------+--------+------------+-------+--------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM holders WHERE name LIKE '%ar%'; +-------------+---------------+--------+------------+--------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+--------+------------+--------+--------+ | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | +-------------+---------------+--------+------------+--------+--------+ 2 rows in set (0.00 sec)
Using LIKE operator (underscore wildcard)
In the following queries, we have used LIKE operator with underscore wildcard LIKE _a%, which means the name's second character must be a. In the second query, we have given five times underscore, which means we want a bank name with exactly five characters in it. Lastly, we have used %_____ which means the bank name should at least contain five characters.
mysql> SELECT * FROM holders WHERE name LIKE '_a%'; +-------------+--------------+---------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+--------------+---------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | | 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 | | 25622348995 | James Moore | NULL | 1978-06-26 | Citi | 97800 | +-------------+--------------+---------+------------+----------+--------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM holders WHERE bank LIKE '_____'; +-------------+---------------+--------+------------+-------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+--------+------------+-------+--------+ | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | +-------------+---------------+--------+------------+-------+--------+ 1 row in set (0.00 sec) mysql> SELECT * FROM holders WHERE bank LIKE '%_____'; +-------------+---------------+---------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+---------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | +-------------+---------------+---------+------------+----------+--------+ 3 rows in set (0.01 sec)
Advertisement