mysql中视图
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql中视图，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4273字，纯文字阅读大概需要7分钟。
内容图文
#以表中的"name,age"创建视图. mysql> select * from student; +-----+--------+------+------+ | SNO | SNAME | AGE | SEX | +-----+--------+------+------+ | 1 | 换换 | 23 | 男 | | 2 | 刘丽 | 22 | 女 | | 4 | NULL | 10 | NULL | | 5 | 张友 | 22 | 男 | | 6 | 刘力 | 22 | 男 | +-----+--------+------+------+ 5 rows in set (0.00 sec) #创建视图stage. mysql> create view stage as select sname,age from student; Query OK, 0 rows affected (0.02 sec) #查看视图(如果直接看,很难看出是视图还是表). mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | boy | | boy2 | | boy3 | | course | | sc | | stage | | student | +----------------+ 7 rows in set (0.00 sec) #查看视图中的数据. mysql> select * from stage; +--------+------+ | sname | age | +--------+------+ | 换换 | 23 | | 刘丽 | 22 | | NULL | 10 | | 张友 | 22 | | 刘力 | 22 | +--------+------+ 5 rows in set (0.00 sec) #表中的数据如下: mysql> select * from sc; +-----+-----+-------+ | SNO | CNO | SCORE | +-----+-----+-------+ | 1 | K1 | 83 | | 2 | K1 | 85 | | 2 | K5 | 90 | | 5 | K1 | 92 | | 5 | K5 | 84 | | 5 | K8 | 80 | +-----+-----+-------+ 6 rows in set (0.00 sec) #分组算表中的平均值,取最大的值. mysql> select cno,avg(score) from sc group by cno; +-----+------------+ | cno | avg(score) | +-----+------------+ | K1 | 86.6667 | | K5 | 87.0000 | | K8 | 80.0000 | +-----+------------+ 3 rows in set (0.00 sec) #求平均值. mysql> select cno,avg(score) as pj from sc group by cno; +-----+---------+ | cno | pj | +-----+---------+ | K1 | 86.6667 | | K5 | 87.0000 | | K8 | 80.0000 | +-----+---------+ 3 rows in set (0.00 sec) #查询平均值中最大的值. mysql> select cno,avg(score) as pj from sc group by cno order by pj desc limit 1; +-----+---------+ | cno | pj | +-----+---------+ | K5 | 87.0000 | +-----+---------+ 1 row in set (0.00 sec) #直接创建视图"pj",然后队视图进行排序,就比较简单. mysql> create view pj as select cno,avg(score) as pj from sc group by cno; Query OK, 0 rows affected (0.01 sec) mysql> select * from pj; +-----+---------+ | cno | pj | +-----+---------+ | K1 | 86.6667 | | K5 | 87.0000 | | K8 | 80.0000 | +-----+---------+ 3 rows in set (0.00 sec) mysql> select * from pj order by pj desc limit 1; +-----+---------+ | cno | pj | +-----+---------+ | K5 | 87.0000 | +-----+---------+ 1 row in set (0.00 sec) 修改视图虚拟表，物理表会变？ #表中的数据如下: mysql> select * from sc; +-----+-----+-------+ | SNO | CNO | SCORE | +-----+-----+-------+ | 1 | K1 | 83 | | 2 | K1 | 85 | | 2 | K5 | 90 | | 5 | K1 | 92 | | 5 | K5 | 84 | | 5 | K8 | 80 | +-----+-----+-------+ 6 rows in set (0.00 sec) #创建视图"sc2"存放表sc中sno,score的值. mysql> create view sc2 as select sno,score from sc; Query OK, 0 rows affected (0.01 sec) #查看视图中的数据. mysql> select * from sc2; +-----+-------+ | sno | score | +-----+-------+ | 1 | 83 | | 2 | 85 | | 2 | 90 | | 5 | 92 | | 5 | 84 | | 5 | 80 | +-----+-------+ 6 rows in set (0.00 sec) #更新视图sc2中的数据. mysql> update sc2 set score=99 where sno=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 #查看视图中的数据已经改变. mysql> select * from sc2; +-----+-------+ | sno | score | +-----+-------+ | 1 | 99 | | 2 | 85 | | 2 | 90 | | 5 | 92 | | 5 | 84 | | 5 | 80 | +-----+-------+ 6 rows in set (0.00 sec) #查看物理表中的数据也已经改变. mysql> select * from sc; +-----+-----+-------+ | SNO | CNO | SCORE | +-----+-----+-------+ | 1 | K1 | 99 | | 2 | K1 | 85 | | 2 | K5 | 90 | | 5 | K1 | 92 | | 5 | K5 | 84 | | 5 | K8 | 80 | +-----+-----+-------+ 6 rows in set (0.00 sec) 如果修改虚拟表中的平均值,物理表中的数据会怎么变？ #查看之前虚拟视图中的平均值. mysql> select * from pj; +-----+---------+ | cno | pj | +-----+---------+ | K1 | 92.0000 | | K5 | 87.0000 | | K8 | 80.0000 | +-----+---------+ 3 rows in set (0.00 sec) #修改视图中的平均值,查看结果如何. mysql> update pj set pj=90 where cno="k8"; ERROR 1288 (HY000): The target table pj of the UPDATE is not updatable mysql> 注意:报错提示不能更新修改平均值.
内容总结
以上是互联网集市为您收集整理的mysql中视图全部内容，希望文章能够帮你解决mysql中视图所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】