如何使用多个表中的数据创建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)
热门推荐
10 诗词送行祝福语大全简短
11 新房开工吉日祝福语简短
12 50多岁生日简短祝福语
13 安徽疫情祝福语简短英语
14 农民朋友发财祝福语简短
15 对生活祝福语简短精辟
16 搬家词简短祝福语朋友
17 女神结婚快乐祝福语简短
18 文学短句祝福语大全简短