创建 MySQL 视图时如何使用逻辑运算符?
mysqlmysqli database更新于 2024/2/26 11:32:00
可以使用逻辑运算符(如 AND、OR 和 NOT)创建 MySQL 视图。可以借助以下示例 − 进行说明
带有 AND 运算符的视图
众所周知，逻辑 AND 运算符比较两个表达式，如果两个表达式都为真，则返回 true。在下面的示例中，我们创建了一个基于 ‘AND’ 运算符的视图。
示例
基表是 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) 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)
带有 OR 运算符的视图
众所周知，逻辑 OR 运算符比较两个表达式，如果至少有一个表达式为真，则返回 true。在下面的示例中，我们创建了一个具有基于"OR"运算符的条件的视图。
示例
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)
带有 NOT 运算符的视图
NOT 是唯一只接受一个操作数的运算符。如果操作数为 TRUE，则返回 0；如果操作数为 FALSE，则返回 1。在下面的示例中，我们创建了一个具有基于"NOT"运算符的条件的视图。
示例
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)
