视图
表里面保存的是实际数据,视图里面保存的是select语句(视图本身不存储数据)。
从视图中读取数据,此时视图在内部执行select语句,创建一张临时表。
使用视图的好处:其一,视图不保存数据,节省存储设备容量。其二,将频繁使用的select语句保存成视图,每次使用这些语句时候,不用重复书写,只需调用视图。其三,数据保存到表中,要显式的执行sql更新语句才能更新数据,而视图中的数据会随着原表的变化自动更新。
创建视图
格式:
create view 视图名称(<视图列名1>,<视图列名2>,...) as <select语句>
例子:
create view productsum (product_type, cnt_product) as select product_type, count(*) from product group by product_type;
使用视图
可见,如果使用视图,不用每次都写group by等一些语句从product表中取数据。
并且,如果product表中数据更新,视图也自动更新。
这是因为,视图就是保存好的select语句。
select product_type, cnt_product from productsum;
多重视图:以视图为基础创建视图。但是这样会降低sql性能。
create view productsuma (product_type, cnt_product) as select product_type, cnt_product from productsum where product_type = '办公用品';
定义视图时,不能用order by子句。因为视图和表,数据行都没有顺序。
(postgresql里面,定义视图时候可以用order by子句,有些dbms不行)
视图更新:
如果定义视图的select语句满足一些条件,视图可以被更新。
select子句没用distinct、from子句只有一张表、没用group by、没用having。
通过汇总得到的数据无法更新,这是因为视图和表要同时更新。
如果给上面的productsum中添加(‘食物’,3)的数据,原表就需要增加三行种类为食物的数据,但是这些数据我们都不知道,因此没法更新表中的数据。
可以更新下面这样,不通过汇总得到的视图。
create view producta (product_id, product_name, product_type, sale_price, purchase_price, regist_date) as select * from product where product_type = '办公用品';
向视图插入数据。
insert into producta values ('0009', '铅笔', '办公用品', 95, 10, '2222-10-1');
此时可看到,视图和表都更新了。
删除视图:
格式
drop view 视图名称(<视图列名1>,<视图列名2>,...)
例子
drop view productsum;
然后报错
error: cannot drop view productsum because other objects depend on it 描述: view productsuma depends on view productsum 提示: use drop ... cascade to drop the dependent objects too.
这是因为前面以productsum为基础,创建了一个productsuma视图。
可以像下面这样删除productsum和与之关联的视图。
drop view productsum cascade;
子查询
子查询,相当于一次性视图。
定义视图productsum
create view productsum (product_type, cnt_product) as select product_type, count(*) from product group by product_type;
子查询:将定义视图的select语句直接用到from子句里面。
as productsum,productsum是子查询的名称。执行完外边的select语句,子查询就消失了。
下面代码,执行顺序,先是from子句里面的select语句,然后是外边的select语句。
select product_type, cnt_product from (select product_type, count(*) as cnt_product from product group by product_type) as productsum;
下面再次查看productsum发现,productsum已经不存在了。由此看出,子查询是一次性的,并不像视图一样保存到硬盘里面。
在子查询的from子句里面,可以继续使用子查询。
下面就是把productsum里面cnt_product = 4的数据选出来了。
select product_type, cnt_product from (select * from (select product_type, count(*) as cnt_product from product group by product_type) as productsum where cnt_product = 4) as productsum2;
标量子查询scalar subquery,返回表中某一行某一列的值(单一值)的子查询。
可以在where子句中使用标量子查询。
由于where子句中无法使用聚合函数,像下面的语句就是错误的。
select product_id, product_name, sale_price from product where sale_price > avg(sale_price);
可以通过下面这样去实现。
select product_id, product_name, sale_price from product where sale_price > (select avg(sale_price) from product);select product_id, product_name, sale_price from product where sale_price > (select avg(sale_price) from product);
在任何使用单一值的地方,都可以使用标量子查询。
在select子句中使用标量子查询:
select product_id, product_name, sale_price, (select avg(sale_price) from product) as avg_price from product;select product_id, product_name, sale_price, (select avg(sale_price) from product) as avg_price from product;
在having子句中使用标量子查询:
不同商品种类的平均销售单价与全部商品的销售单价相比。
select product_type, avg(sale_price) from product group by product_type having avg(sale_price) > (select avg(sale_price) from product);
标量子查询不能返回多行结果,如果返回多行结果,那就是一个普通的子查询,不能用到需要单一输入值的地方了。
关联子查询
现在要选取各个商品种类里面,高于该商品种类平均销售价的商品。
按照商品种类计算平均价格:
select avg(sale_price) from product group by product_type;
因为有三种商品,上面这个查询返回三个结果。
那么就不能用下面这种方法了。因为子查询不是标量子查询,不能在where子句里面用。
select product_id, product_name, sale_price from product where sale_price > (select avg(sale_price) from product group by product_type);
在细分的组内进行比较的时候,用到关联子查询。
在子查询里面添加了一个where子句。目的是在同一商品种类中对各商品销售单价和平均单价比较。
由于比较对象是同一个product表,所以用了p1、p2两个别名。
使用关联子查询,用<表名>.<列名>形式,限定product_type,对平均单价比较。
select product_type, product_name, sale_price from product as p1 where sale_price > (select avg(sale_price) from product as p2 where p1.product_type = p2.product_type group by product_type);
而且,不加group by,也能得到相同结果:
select product_type, product_name, sale_price from product as p1 where sale_price > (select avg(sale_price) from product as p2 where p1.product_type = p2.product_type);
以上就是postgresql数据库视图及子查询使用操作的详细内容,更多关于postgresql数据库的视图子查询的资料请关注<计算机技术网(www.ctvol.com)!!>其它相关文章!
需要了解更多数据库技术:PostgreSQL数据库视图及子查询使用操作,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/1086955.html