Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 22-Jun-2020
97 Views
Advertisements