视图和子查询

最新推荐文章于 2024-09-15 08:03:51 发布

GaGa，

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量1k

点赞数 19

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/qq_55909023/article/details/139754547

版权

视图及子查询

假如我们有一张表，表名为 person，如下所示：

+-----------+------------+-----------+------+-------------+-----------+
| person_id | first_name | last_name | age  | city        | country   |
+-----------+------------+-----------+------+-------------+-----------+
|         1 | John       | Doe       |   30 | New York    | USA       |
|         2 | Jane       | Smith     |   25 | Los Angeles | USA       |
|         3 | Alice      | Johnson   |   35 | London      | UK        |
|         4 | Bob        | Brown     |   40 | Sydney      | Australia |
|         5 | Emma       | Garcia    |   28 | Madrid      | Spain     |
+-----------+------------+-----------+------+-------------+-----------+

视图

视图是一个虚拟的表，其内容基于对一个或多个实际表进行的查询结果。它实际上是一个 SQL 查询的结果集，但可以像表一样被查询、访问和操作。视图本身不存储数据，而是存储了对其他表的查询定义。通过创建视图，可以简化复杂的查询、隐藏数据结构、提供安全性以及实现数据抽象。

创建视图的语法通常如下：

create view 视图的名称 as
select 列名1, 列名2, ...
from 表名
where 可选的筛选条件;

可修改的视图：创建一个基于 person 表的视图。

create view editable_person_age_view as
select person_id, first_name, last_name, age
from person;

-- 这里省略查询视图的语句。
+-----------+------------+-----------+------+
| person_id | first_name | last_name | age  |
+-----------+------------+-----------+------+
|         1 | John       | Doe       |   30 |
|         2 | Jane       | Smith     |   25 |
|         3 | Alice      | Johnson   |   35 |
|         4 | Bob        | Brown     |   40 |
|         5 | Emma       | Garcia    |   28 |
+-----------+------------+-----------+------+

接下来我们对这个视图进行一个简单的修改：

update editable_person_age_view
set age = 40
where person_id = 2;

-- 这里省略查询视图的语句。
+-----------+------------+-----------+------+
| person_id | first_name | last_name | age  |
+-----------+------------+-----------+------+
|         1 | John       | Doe       |   30 |
|         2 | Jane       | Smith     |   40 |
|         3 | Alice      | Johnson   |   35 |
|         4 | Bob        | Brown     |   40 |
|         5 | Emma       | Garcia    |   28 |
+-----------+------------+-----------+------+
5 rows in set (0.00 sec)

这个更新操作将更新 editable_person_age_view 视图中 person_id 为 2 的人员的年龄信息为 40 岁。

什么样的视图可以进行修改？

视图中的每一列都来自于唯一的基表中的列。
视图中的每一列都是可以通过唯一值确定的表达式的结果。

我们上面创建的 editable_person_age_view 是可修改的，因为它们只从 person 表中查询数据，且不进行列的计算或处理。

不可修改的视图：创建一个基于 person 表的视图。

create view city_population_view as
select city, count(*) as population
from person
group by city;

-- 这里省略查询视图的语句。
+-------------+------------+
| city        | population |
+-------------+------------+
| New York    |          1 |
| Los Angeles |          1 |
| London      |          1 |
| Sydney      |          1 |
| Madrid      |          1 |
+-------------+------------+
5 rows in set (0.00 sec)

这个视图包含了聚合函数 count(*) 和 group by 子句，因此是不可修改的。

删除视图：

删除视图的语法如下：
```
drop view  view_name;
```
其中drop view 是 SQL 删除视图的关键字，view_name 是要删除的视图的名称。
还有一个关键字就是if exists，这是一个可选的子句，用于指定当视图不存在时是否给出警告。如果省略了此子句，并且视图不存在，则会出现错误。使用 if exists 可以避免因尝试删除不存在的视图而导致的错误。其语法为：
```
drop view [if exists] view_name;
```

子查询

子查询是指在 SQL 查询中嵌套另一个查询的查询结构。子查询可以用作 SQL 语句中的一个组成部分，用于过滤结果、提供计算的值或与主查询进行比较。子查询可以出现在 select、INSERT、update 或 delete 语句中的多个位置。子查询可以分为两种类型：

内部子查询：内部子查询是嵌套在其他查询中的子查询，通常出现在 where 子句中的条件中。内部子查询返回一个结果集，该结果集用于过滤主查询中的数据。

外部子查询：外部子查询是独立于主查询的子查询，通常出现在主查询的列列表或条件中。外部子查询的结果集用于与主查询中的列进行比较或用作值。

内部子查询：

select *
from person
where age > (select AVG(age) from person);

+-----------+------------+-----------+------+-------------+-----------+
| person_id | first_name | last_name | age  | city        | country   |
+-----------+------------+-----------+------+-------------+-----------+
|         2 | Jane       | Smith     |   40 | Los Angeles | USA       |
|         3 | Alice      | Johnson   |   35 | London      | UK        |
|         4 | Bob        | Brown     |   40 | Sydney      | Australia |
+-----------+------------+-----------+------+-------------+-----------+

在这个查询中，内部子查询 (select avg(age) from person) 计算了 person 表中所有人的平均年龄，然后主查询将其与 person 表中的年龄进行比较，返回年龄大于平均年龄的人员记录。

外部子查询：
```
select p.person_id, p.first_name, p.last_name, a.age
from person p
inner join  (
    select person_id, max(age) as age
    from age_records
    group by person_id
) as a on p.person_id = a.person_id;
```
在这个查询中，外部子查询 (select person_id, max(age) as age from age_records group by person_id) 返回了每个人员的最新年龄记录，然后主查询将其与 person 表进行连接，以获取每个人员的最新年龄信息。