# 复杂查询

## 视图

视图不会保存数据，保存的是一段SQL语句，执行时产生一个临时表。

优点：无须保存数据、节省容量

```
# 视图中的列和SELECT中的一一对应
CREATE VIEW 视图名 (列1, 列2, ...)
AS
<SELECT􏱇􏱈 语句>
```

例子，创建ProductSum视图：

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

PS：视图可以使用任何SELECT的语句，WHERE，GROUP BY，HAVING，`（ORDER BY除外，查询的数据是没有顺序的）`；

PS：可以多重视图，但是建议不要，太乱，降低性能；

```
# 在上面的视图基础上，再创建视图
CREATE VIEW ProductSumJim (product_type,cnt_product)
AS
SELECT product_type,cnt_product
 FROM ProductSum
WHERE product_type = '办公用品'
```

### 向视图插入数据

满足一些性质时，可以直接向视图进行更新（会更新底层数据库）

* SELECT未使用DISTINCT ------否则此时无法区分具体数据行
* FROM只有一张表 ------否则此时无法区分哪个表
* 未使用GROUP BY ------否则此时无法区分具体数据行
* 未使用HAVING ------否则此时无法区分具体数据行

### 删除视图

```
DROP VIEW ProductSum;
```

## 子查询

以视图为基础的查询、一张一次性的视图，直接拿定义视图的SELECT语句，用于FROM查询中。

1.视图

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

# 使用
SELECT product_type,cnt_product
 FROM ProductSum;
```

2.子查询形式

执行顺序：先里面的，后外面的

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

PS：子查询没有限制，理论上可以套很多层

## 标量子查询

单一返回结果，只返回一行、一列数据。可以用<>=之类的比较

```
# 想查询大于平均值的数据行，但是这样不行，执行AVG时，数据还没SELECT
SELECT product_id,product_ame,sale_price
 FROM Product
WHERE sale_price >AVG(sale_price);

# 设置标量
SELECT AVG(sale_price)
 FROM Product;

# 使用标量子查询
SELECT product_id,product_ame,sale_price
 FROM Product
WHERE 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 P1.product_type = P2.product_type`，起到在同类商品中进行价格比较，

P1、P2因为都是同一个表，所以必须要起别名

```
SELECT product_id,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;
)
```

深入理解：

要解决的问题：检索工资大于同职位的平均工资的员工信息

![img](https://img2018.cnblogs.com/blog/1010633/201903/1010633-20190321203040321-1257239144.png)

```
select * from emp e where sal > 
(select avg(sal) from emp e2 where e2.job = e.job);
```

`第一步`先执行外层查询，即先执行:

```
select * from emp e;
```

结果是：也就是该表的所有内容

![img](https://img2018.cnblogs.com/blog/1010633/201903/1010633-20190321204032824-1789881042.png)

根据（where e2.job = e.job ），所以将第一条记录转到子查询

![img](https://img2018.cnblogs.com/blog/1010633/201903/1010633-20190321205313508-73776773.png)

`第二步`，这条进入子查询后，子查询job是CLERK，所以先筛选出所有Job=‘CLERK’的，再对他们取平均。

相当于执行了：

```
select avg(sal) from emp where job='CLERK';
```

结果是

![img](https://img2018.cnblogs.com/blog/1010633/201903/1010633-20190321205603759-588157348.png)

`第三步`，这个结果进入外层查询where和SMITH这个人的sal进行对比，相当于执行了

```
select * from emp where sal>1037.5 and job='CLERK';
```

结果是为：

![img](https://img2018.cnblogs.com/blog/1010633/201903/1010633-20190321210702959-1174558014.png)

循环，然后就抽出第一次外层查询的第二条（ALLEN）：

![img](https://img2018.cnblogs.com/blog/1010633/201903/1010633-20190321210851946-999482892.png)

如SALESMAN职位的查询顺序为：

```
select avg(sal) from emp where job='SALESMAN';
select * from emp where sal>xxx and job='SALESMAN';
```

最终就能得到所有岗位，大于本岗位平均工资的人的信息


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://im-qianuxn.gitbook.io/pytorch/ji-suan-ji/shu-ju-ku/mysql/fu-zha-cha-xun.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.