| MySQL 如何使用多张表格创建MySQL视图？ | 您所在的位置：网站首页 › sql多张表建立视图 › MySQL 如何使用多张表格创建MySQL视图？ |
|
MySQL 如何使用多张表格创建MySQL视图？
MySQL的UNION操作符可以合并两个或多个结果集，因此我们可以使用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 教程 示例以下查询将使用来自上述两个表的数据创建一个视图。 mysql> Create or Replace View Info AS Select StudentName from Student_detail UNION Select Name From Student_info; Query OK, 0 rows affected (0.10 sec) mysql> select * from info; +-------------+ | StudentName | +-------------+ | Gaurav | | Raman | | Rahul | | Ram | | Mohan | | YashPal | | Shyam | +-------------+ 7 rows in set (0.00 sec)以上结果集来自两列的组合。如果一个值重复，则会消除重复值。 我们还可以使用UNION ALL存储所有值，包括重复的值，如以下查询所示： mysql> Create or Replace View Info AS Select student name from Student_detail UNION ALL Select Name From Student_info; Query OK, 0 rows affected (0.16 sec) mysql> select * from info; +-------------+ | StudentName | +-------------+ | Gaurav | | Raman | | Rahul | | Ram | | Mohan | | YashPal | | Gaurav | | Raman | | Ram | | Shyam | | Mohan | +-------------+ 11 rows in set (0.00 sec) |
| CopyRight 2018-2019 实验室设备网 版权所有 |