sql 4 复杂查询

2019-03-31

sql 4 复杂查询

视图

从sql角度说，视图和表相同。两者区别在于表中保存的是真实的数据，视图中保存的是select语句（视图本身不存数据）。视图类似于临时存储数据的表。

优点：

1）节省空间视图中只保存select语句的代码，通过select来查询真实数据显示出来。
2）不用重新书写select语句

一般把经常使用的select语句做成视图。原表数据更新，视图也会更新。

创建视图

格式：create view 视图名(视图列名1, ... ) as <select语句>;
注意：select语句列的排序顺序要和视图中列的排序顺序要一致

1
2
3

create view t2_view (shobin_bunrui, count_shobin) 
as 
select shobin_bunrui, count(*) from t2 group by shobin_bunrui;

查看视图

格式：select <视图列名,...> from <视图名>;

select * from  t2_view;

shobin_bunrui | count_shobin
---------------+--------------
衣服          |            1
办公用品      |            1
厨房用具      |            3

视图限制

1）定义视图时不能使用order by子句
2）对视图进行更新规范 1.select语句未使用distinct 2.from子句只有一张表 3.未使用group by子句 4.未使用having子句

删除视图

格式：drop view 视图名;

1	drop view t2_view;

子查询

子查询就是一次性的视图，它在select语句执行完后就会消失。

子查询是把定义视图的select语句放在其from子句中。as后是子查询名。先执行内层select语句再执行外层select语句。

1	select * from (select shobin_bunrui, count(hanbai_tabka) from t2 group by shobin_bunrui) as v2_view;

也可以多层子查询

1	select * from ( select * from (select shobin_bunrui, count(hanbai_tabka) from t2 group by shobin_bunrui) as v2_view1 where count = 3) as v2_view2;

标量子查询

标量是单一的意思。标量子查询就是必须而且只能返回1行1列的结果。
注意：标量子查询不能返回多行数据，返回多汗数据只是普通子查询，不能用在 = 或者 <>当中。

如果想“查询出销售单价高于平均销售单价的商品”

1
2
3

select shohin_mei, shobin_bunrui, hanbai_tabka 
from t2 
where hanbai_tabka > avg (hanbai_tabk);

但是聚合函数是不能写再where子句中的，而单独查询平均销售单价的sql语句是

1	select avg(hanbai_tabka) from t2;

此时单独查询平均销售单价的sql语句就是标量子查询了，此时用标量子查询来代替avg (hanbai_tabk)的位置

select shohin_mei, shobin_bunrui, hanbai_tabka 
from t2 
where hanbai_tabka > (select avg(hanbai_tabka) from t2);

shohin_mei | shobin_bunrui | hanbai_tabka
------------+---------------+--------------
T恤        | 衣服          |         2000
菜刀       | 厨房用具      |         2990

能够使用常数或者列名的地方，无论是select子句，where子句，group by子句，having子句和order by子句，都是可以使用标量子查询的。

在select子句中使用标量子查询

select shohin_mei, shobin_bunrui, hanbai_tabka, ( select avg(hanbai_tabka) from t2 ) as ava_product 
from t2 ;

shohin_mei | shobin_bunrui | hanbai_tabka |      ava_product
------------+---------------+--------------+-----------------------
打孔器     | 办公用品      |          500 | 1198.0000000000000000
T恤        | 衣服          |         2000 | 1198.0000000000000000
叉子       | 厨房用具      |          500 | 1198.0000000000000000
擦菜板     | 厨房用具      |            0 | 1198.0000000000000000
菜刀       | 厨房用具      |         2990 | 1198.0000000000000000

在having子句中使用标量子查询

select shobin_bunrui,avg(hanbai_tabka) 
from t2 
group by shobin_bunrui 
having avg(hanbai_tabka) > (select avg(hanbai_tabka) from t2);

shobin_bunrui |          avg
---------------+-----------------------
衣服          | 2000.0000000000000000

关联子查询

使用关联子查询，需要在表对应的列名之前加上表的别名，以“<表名>.<列名>”形式。

如果想“查询出各商品种类中高于该商品种类的平均销售单价的商品”

在同为厨房用具的商品,厨房用具类的平均销售单价是(500+0+2990)/3=1163 ，符合要求的是菜刀。

每种商品种类的平均值语句

select avg(hanbai_tabka) from t2 group by shobin_bunrui;

          avg
-----------------------
2000.0000000000000000
  500.0000000000000000
1163.3333333333333333

由于该查询得三个值，在where语句中只能使用标量子查询，因此下面语句会报错

1	select shohin_mei, shobin_bunrui, hanbai_tabka from t2 where hanbai_tabka > (select avg(hanbai_tabka) from t2 group by shobin_bunrui);

再此语句中使用关联子查询便得到结果

select shohin_mei, shobin_bunrui, hanbai_tabka 
from t2 as P1
where hanbai_tabka > (select avg(hanbai_tabka) from t2 as P2 where P1.shobin_bunrui = P2.shobin_bunrui group by shobin_bunrui);

shohin_mei | shobin_bunrui | hanbai_tabka
------------+---------------+--------------
菜刀       | 厨房用具      |         2990

在这里起到关键作用的语句是where P1.shobin_bunrui = P2.shobin_bunrui 意思是在同类商品中对各类商品的销售单价和平均单价进行比较，由于是使用同一个表t2,为了区分使用关联名称P1 P2 ,而关联子查询使用形式为<表名>.<列名>