如何使用多个表中的数据创建MySQL视图？
MySQLUNION运算符可以合并两个或多个结果集，因此我们可以使用UNION运算符创建一个包含来自多个表的数据的视图。为了理解这个概念，我们使用具有以下数据的基本表“Student_info”和“Student_detail”-
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> Select * from Student_detail; +-----------+-------------+------------+ | Studentid | StudentName | address | +-----------+-------------+------------+ | 100 | Gaurav | Delhi | | 101 | Raman | Shimla | | 103 | Rahul | Jaipur | | 104 | Ram | Chandigarh | | 105 | Mohan | Chandigarh | +-----------+-------------+------------+ 5 rows in set (0.00 sec)
示例
下面的查询将通过使用上述两个表中的数据来创建视图-
mysql> Create or Replace View Info AS Select StudentName from Student_detail UNION Select Name From Student_info; mysql> select * from info; +-------------+ | StudentName | +-------------+ | Gaurav | | Raman | | Rahul | | Ram | | Mohan | | YashPal | | Shyam | +-------------+ 7 rows in set (0.00 sec)
上面的结果集包含两列的值的组合。如果值重复，则它将消除重复的值。
我们还可以存储所有值，也可以使用UNIONALL来复制一个值，如以下查询所示：
mysql> Create or Replace View Info AS Select student name from Student_detail UNION ALL Select Name From Student_info; mysql> select * from info; +-------------+ | StudentName | +-------------+ | Gaurav | | Raman | | Rahul | | Ram | | Mohan | | YashPal | | Gaurav | | Raman | | Ram | | Shyam | | Mohan | +-------------+ 11 rows in set (0.00 sec)