视图和子查询

2024-06-17 22:12
文章标签 查询 视图 和子

本文主要是介绍视图和子查询,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

视图及子查询

假如我们有一张表,表名为 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 语句中的一个组成部分,用于过滤结果、提供计算的值或与主查询进行比较。子查询可以出现在 selectINSERTupdatedelete 语句中的多个位置。子查询可以分为两种类型:

内部子查询:内部子查询是嵌套在其他查询中的子查询,通常出现在 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 agefrom age_recordsgroup 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 表进行连接,以获取每个人员的最新年龄信息。

理论上,子查询可以无限嵌套,但实际操作中,我们通常不推荐这样做,因为这可能会对查询效率产生负面影响。可以将子查询视为一种临时表。这种临时表在查询过程中生成,并在查询结束后立即消失。这样的设计可以帮助我们更有效地管理和操作数据。但是,为了提高查询效率,我们应尽量避免过度使用子查询。

这篇关于视图和子查询的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1070600

相关文章

Go语言开发实现查询IP信息的MCP服务器

《Go语言开发实现查询IP信息的MCP服务器》随着MCP的快速普及和广泛应用,MCP服务器也层出不穷,本文将详细介绍如何在Go语言中使用go-mcp库来开发一个查询IP信息的MCP... 目录前言mcp-ip-geo 服务器目录结构说明查询 IP 信息功能实现工具实现工具管理查询单个 IP 信息工具的实现服

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》:本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN(连接查询)1.1 内连接(INNER JOIN)1.2 左连接(LEFT JOI

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》:本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接(cross join)三、自然连接(naturalandroid join)四

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》:本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键

Mybatis 传参与排序模糊查询功能实现

《Mybatis传参与排序模糊查询功能实现》:本文主要介绍Mybatis传参与排序模糊查询功能实现,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友跟随小编一起看看吧... 目录一、#{ }和${ }传参的区别二、排序三、like查询四、数据库连接池五、mysql 开发企业规范一、#{ }和${ }传参的

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景:问题描述原因分析:解决方案:第一种:修改后,只有当前生效,若是mysql服务重启,就会失效;

MySQL多列IN查询的实现

《MySQL多列IN查询的实现》多列IN查询是一种强大的筛选工具,它允许通过多字段组合快速过滤数据,本文主要介绍了MySQL多列IN查询的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录一、基础语法:多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析与优化1.