Dùng toán tử logic (AND, OR, NOT) khi tạo View MySQL

View MySQL hỗ trợ toán tử logic AND, OR, NOT trong WHERE.

View với AND

AND trả về true nếu cả hai biểu thức true.

Ví dụ

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)

Tạo view Info với AND:

mysql> Create or Replace View Info AS select ID, Name, Address, Subject FROM Student_info WHERE (Subject = 'Computers' AND ADDRESS = 'DELHI');
Query OK, 0 rows affected (0.13 sec)
mysql> Select * from Info;
+------+-------+---------+-----------+
| ID   | Name  | Address | Subject   |
+------+-------+---------+-----------+
|  133 | Mohan | Delhi   | Computers |
+------+-------+---------+-----------+
1 row in set (0.00 sec)

View với OR

OR trả về true nếu ít nhất một biểu thức true.

Ví dụ

mysql> Create or Replace View Info AS select ID, Name, Address, Subject FROM Student_info WHERE (Subject = 'Computers' OR ADDRESS = 'Amritsar');
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 |
|  133 | Mohan   | Delhi    | Computers |
+------+---------+----------+-----------+
4 rows in set (0.00 sec)

View với NOT

NOT chỉ nhận 1 toán hạng. Trả về 0 nếu operand TRUE, 1 nếu FALSE.

Ví dụ

mysql> Create or Replace View Info AS select ID, Name, Address, Subject FROM Student_info WHERE Subject != 'Computers';
Query OK, 0 rows affected (0.06 sec)
mysql> Select * from Info;
+------+---------+------------+------------+
| ID   | Name    | Address    | Subject    |
+------+---------+------------+------------+
|  101 | YashPal | Amritsar   | History    |
|  105 | Gaurav  | Chandigarh | Literature |
|  132 | Shyam   | Chandigarh | Economics  |
+------+---------+------------+------------+
3 rows in set (0.00 sec)