一相关概念
视图:本质上是一种虚拟表。并不在数据库中以存储的数据值形式存在,行与列数据在具体引用视图时生成。可以实现查询的简化
功能:封装了复杂的查询语句
二创建视图
1 Use view; #选择数据库
2 Create view view_selectproduct
As
Select id name from t_product;
Select * from view_selectproduct;
#封装使用聚合函数
3 Create view view_test1
As
Select count(name) from t_product;
4 Create view view_test2
As
Select name from t_product
Order by id desc;
#封装实现表内连接
5 Create view view_test3
As
Select s.name from t_student as s, t_group as g
Where s.group_id=g.id and g.id=2;
#封装实现表外连接
6 Create view view_test4
As
Select s.name from t_student as s left join t_group as g on s.group_id=g.id
Where g.id=2;
#封装实现子查询
7Create view view_test5
As
Select s.name from t_student as s
Where s.group_id in (select in from t_group) ;
#封装实现记录联合(union / union all)
8Create view view_test6
As
Select id,name from t_student
Union all
Select id,name from t_group ;
三查看视图
1 show table status from view \G
2 show table status from view LIKE “view_selectproduct” \G
3select * from views where table_name= “view_selectproduct” \G
四删除视图
1drop view view_selectproduct1,view_selectproduct2;
五修改视图
4 alter view view_selectproduct
As
Select name from t_product
Order by id desc;
六利用视图操作表
1添加数据
insert into view_product(id,name,price,order_id)
values(11, 'pear4', 12.3, 2);
2删除数据
DELECT from vie_product where name='apple';
3更新数据操作
update view_product set price=3.5 where name='pear';