如何使用多个表中的数据创建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 孩子16岁祝福语简短
12 初八祝福语简短语
13 月子小孩祝福语大全简短
14 周年庆典祝福语致辞简短
15 女朋友考试祝福语简短
16 红颜生日祝福语简短独特
17 祝美术老师祝福语简短
18 送灯祝福语简短独特