How can we create MySQL view by selecting data based on pattern matching from base table?

MySQL LIKE operator is used to select data based on pattern matching. Similarly, we can use LIKE operator with views to select particular data based on pattern matching from the base table. To understand this concept we are using the base table ‘student_info’ having the following data −

mysql> Select * from Student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| 130  | Ram     | Jhansi     | Computers  |
| 132  | Shyam   | Chandigarh | Economics  |
| 133  | Mohan   | Delhi      | Computers  |
+------+---------+------------+------------+
6 rows in set (0.00 sec)

Example

Following query will create a view named ‘Info’, to select some particular values based on pattern matching by using ‘LIKE’ operator −

mysql> Create or Replace view Info AS SELECT * from student_info WHERE Name LIKE '%Ra%';
Query OK, 0 rows affected (0.11 sec)

mysql> Select * from Info;
+------+--------+------------+------------+
| id   | Name   | Address    | Subject    |
+------+--------+------------+------------+
| 105  | Gaurav | Chandigarh | Literature |
| 125  | Raman  | Shimla     | Computers  |
| 130  | Ram    | Jhansi     |  Computers |
+------+--------+------------+------------+
3 rows in set (0.00 sec)

We can use NOT with LIKE operator also as follows −

mysql> Create or Replace view Info AS SELECT * from student_info WHERE Name NOT LIKE'%Ra%';
Query OK, 0 rows affected (0.14 sec)

mysql> Select * from info;
+------+---------+------------+-----------+
| id   | Name    | Address    | Subject   |
+------+---------+------------+-----------+
| 101  | YashPal | Amritsar   | History   |
| 132  | Shyam   | Chandigarh | Economics |
| 133  | Mohan   | Delhi      | Computers |
+------+---------+------------+-----------+
3 rows in set (0.00 sec)