MySQL创建视图虚拟表

视图虚拟表创建

项目配置

查看chengji表

mysql> use aaa;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from chengji;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201002 |       18 | lisi     |      70 |
| 201003 |       18 | wangwu   |      80 |
| 201004 |       17 | zhaoliu  |      95 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      80 |
| 201007 |       19 | lisi     |      70 |
| 201008 |       20 | lii      |      66 |
+--------+----------+----------+---------+
8 rows in set (0.00 sec)

创建新表并添加数据

mysql> create table sushe(id int(3),num int(3));
Query OK, 0 rows affected (0.01 sec)

mysql> create table sushe(id int(10),num int(3));
ERROR 1050 (42S01): Table 'sushe' already exists
mysql> drop table sushe;\
Query OK, 0 rows affected (0.00 sec)

mysql> create table sushe(id int(10),num int(3));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sushe values(201001,305),(201002,306),(201003,307),(201008,308),(201009,309),(201010,310);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

创建视图虚拟表stu，并查询虚拟表与实际表内容是否一致

mysql> create view stu as select chengji.xingming,sushe.num from chengji inner join sushe where chengji.xuehao=sushe.id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;                                                                     
+----------+------+
| xingming | num  |
+----------+------+
| zhangsan |  305 |
| lisi     |  306 |
| wangwu   |  307 |
| lii      |  308 |
+----------+------+
4 rows in set (0.00 sec)

mysql> select chengji.xingming,sushe.num from chengji inner join sushe where chengji.xuehao=sushe.id;    //视图为映射，查看原表信息
+----------+------+
| xingming | num  |
+----------+------+
| zhangsan |  305 |
| lisi     |  306 |
| wangwu   |  307 |
| lii      |  308 |
+----------+------+
4 rows in set (0.00 sec)

修改sushe实际表内容，增加数据

mysql> select * from sushe;
+--------+------+
| id     | num  |
+--------+------+
| 201001 |  305 |
| 201002 |  306 |
| 201003 |  307 |
| 201008 |  308 |
| 201009 |  309 |
| 201010 |  310 |
+--------+------+
6 rows in set (0.00 sec)

mysql> insert into sushe values(201004,304);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sushe;
+--------+------+
| id     | num  |
+--------+------+
| 201001 |  305 |
| 201002 |  306 |
| 201003 |  307 |
| 201008 |  308 |
| 201009 |  309 |
| 201010 |  310 |
| 201004 |  304 |
+--------+------+
7 rows in set (0.00 sec)

再次查看实际表及虚拟表信息

mysql> select chengji.xingming,sushe.num from chengji inner join sushe where chengji.xuehao=sushe.id;
+----------+------+
| xingming | num  |
+----------+------+
| zhangsan |  305 |
| lisi     |  306 |
| wangwu   |  307 |
| zhaoliu  |  304 |
| lii      |  308 |
+----------+------+
5 rows in set (0.00 sec)

mysql> select * from stu;
+----------+------+
| xingming | num  |
+----------+------+
| zhangsan |  305 |
| lisi     |  306 |
| wangwu   |  307 |
| zhaoliu  |  304 |
| lii      |  308 |
+----------+------+
5 rows in set (0.00 sec)

信息发生改变，MySQL视图虚拟表创建成功。