如何使用多个表中的数据创建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)
热门推荐
1 带马的祝福语简短
10 亲人喜得贵子祝福语简短
11 温馨开心祝福语简短英文
12 生日祝福语简短致表哥
13 虎年男孩出生祝福语简短
14 婚礼客人敬酒祝福语简短
15 生日祝福语孙悟空简短
16 传统正版婚礼祝福语简短
17 元旦祝福语同学简短创意
18 早安短句英文祝福语简短