视图虚拟表创建
项目配置
查看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视图虚拟表创建成功