Dùng toán tử BETWEEN trong view để lọc khoảng giá trị từ bảng gốc. Bảng student_info:
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)
Ví dụ
Tạo view Info lọc tên từ 'C' đến 'P':
mysql> Create or Replace view Info AS SELECT * from student_info WHERE Name BETWEEN 'C' AND 'P';
Query OK, 0 rows affected (0.14 sec)
mysql> Select * from info;
+------+--------+------------+------------+
| id | Name | Address | Subject |
+------+--------+------------+------------+
| 105 | Gaurav | Chandigarh | Literature |
| 133 | Mohan | Delhi | Computers |
+------+--------+------------+------------+
2 rows in set (0.00 sec)
Dùng NOT BETWEEN lấy phần còn lại:
mysql> Create or Replace view Info AS SELECT * from student_info WHERE Name NOT BETWEEN 'C' AND 'P';
Query OK, 0 rows affected (0.06 sec)
mysql> Select * from Info;
+------+---------+------------+-----------+
| id | Name | Address | Subject |
+------+---------+------------+-----------+
| 101 | YashPal | Amritsar | History |
| 125 | Raman | Shimla | Computers |
| 130 | Ram | Jhansi | Computers |
| 132 | Shyam | Chandigarh | Economics |
+------+---------+------------+-----------+
4 rows in set (0.00 sec)