博客

学习记录，不定时更新

复杂查询

5.1视图

视图和表


表

：用来管理数据的二维表，在RDBMS数据库中存储着实际的数据


视图

：视图本质存储的是SELECT语句，使用视图时会执行SELECT语句，创建出一个临时表；


区别

：表存储的是实际数据，视图存储的是SELECT语句

视图优点：

• 视图不用存储实际数据，节约内存空间
• 可以将经常用到的SELECT语句保存为视图，避免重复书写

创建视图的语句

CREATE VIEM 视图名字(视图列1，视图列2...)
	AS
	SELECT语句

注意：SELECT中列的顺序要和视图列的顺序保持一致

练习

在数据库中统计出每类商品的总个数,使用视图保存起来

CREATE VIEW ProductSum(product_type,cnt_product)
    AS
    SELECT product_type, COUNT(*)
    FROM Product
    GROUP BY product_type;

视图的限制

• 1、视图不能使用order by语句

  数据库的行都是无序的

• 2、对视图更新有限制

  • select语句中未使用distinct语句
  • from子句中只有一张表
  • 未使用group by子句
  • 未使用having子句

删除视图

语句：

DROP VIEW 视图名称

5.2子查询

什么是子查询

子查询就是一次性的视图，将用来定义视图的select语句直接用于from语句

也可以理解为将一个查询语句的结果用在另一个查询语句中

普通语句、视图、子查询

统计每类商品的总和

普通语句

select product_type,count(*)as cnt_product
    from Product
    group by product_type;

优点：代码量小，可以直接看到结果

缺点：只能使用一次，再次查询需要些重复语句

视图

create view ProductSum(product_type,cnt_product)
	AS
	select product_type,count(*)
	From Product
	group by product_type;

select * from ProductSum

优点：减少重复性代码，提高利用率

缺点：代码量比较大，容易出现错误

子查询

select product_type,cnt_product
	from (
    	select product_type,count(*)as cnt_product
        	from Product
        	group by product_type
    	) as ProductSum;
	

注意：as 以及后面的名字一定要有，否则会报错，原则上子查询必须设定名字

优点：可以实现复杂语句的一次性查询，适用于多个表之间的关联查询

缺点：代码量大

单一(标量)子查询

***定义：***单一子查询也叫标量子查询，就是返回单一值的子查询，必须且只能返回一行一列的子查询

实战

输出销售单价高于平均销售单价的商品

普通方法：

-- 先获取平均值
select avg(sale_price) From Product;
+-----------------+
| avg(sale_price) |
+-----------------+
|       2097.5000 |
+-----------------+
1 row in set (0.00 sec)

-- 通过获取的平均值来统计商品
select product_id,product_name,sale_prce
    from Product
    where sale_price>2097.5;

子查询：

select product_id ,product_name,sale_price
    from Product
    where sale_price > (select avg(sale_price) from Product);

单一子查询书写位置

可以应用在select、group by 、having等任何语句

select

mysql> select product_id product_name,sale_price,(select avg(sale_price) from Product)
    -> from Product;
+--------------+------------+---------------------------------------+
| product_name | sale_price | (select avg(sale_price) from Product) |
+--------------+------------+---------------------------------------+
| 001          |       1000 |                             2097.5000 |
| 002          |        500 |                             2097.5000 |
| 003          |       4000 |                             2097.5000 |
| 004          |       3000 |                             2097.5000 |
| 005          |       6800 |                             2097.5000 |
| 006          |        500 |                             2097.5000 |
| 007          |        880 |                             2097.5000 |
| 008          |        100 |                             2097.5000 |
+--------------+------------+---------------------------------------+

having

获取按照商品种类计算出的销售单价高于全部商品的平均销售单价的种类

mysql> select product_type,avg(sale_price) from Product group by product_type having avg(sale_price)>(select avg(sale_price) from Product);
+--------------+-----------------+
| product_type | avg(sale_price) |
+--------------+-----------------+
| 厨房用具     |       2795.0000 |
| 衣服         |       2500.0000 |
+--------------+-----------------+

5.3联合子查询