SQL学习笔记——task3：MySQL复杂查询

最新推荐文章于 2023-06-08 16:44:09 发布
最新推荐文章于 2023-06-08 16:44:09 发布
阅读量415 收藏 1
点赞数 1
文章标签： mysql 数据库 sql 后端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44715733/article/details/111387758
版权

文章目录

前言

复杂的MySQL查询使用多个参数来搜索数据，并且可能包含多个表之间的多个联接以及相当多的嵌套子查询（嵌套在另一查询中 的查询）的组合。复杂查询还经常涉及大量使用AND和OR子句。

复杂查询通常用于从多个表中检索复杂数据。高级查询还可以用于报告，联接多个表，嵌套查询和事务锁定。

1. 视图

先来看一个查询语句：

SELECT stu_name FROM view_students_info;

单从表面上看起来这个语句是和正常的从数据表中查询数据是完全相同的，但其实我们操作的是一个视图。所以从SQL的角度来说操作视图与操作表看起来是完全相同的。

Q：那么为什么还会有视图的存在呢？视图到底是什么？视图与表有什么不同呢？

1.1 什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

1.2 视图与表有什么区别

有一句话非常凝练的概括了视图与表的区别——“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表。它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
在这里插入图片描述

1.3 为什么会存在视图

主要有以下几点原因：

  1. 通过定义视图可以将频繁使用的SELECT语句保存以提高效率；
  2. 通过定义视图可以使用户看到的数据更加清晰；
  3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性；
  4. 通过定义视图可以降低数据的冗余。

1.4 如何创建视图

创建视图的基本语法如下：

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

其中SELECT 语句需要书写在AS 关键字之后。SELECT 语句中列的排列顺序和视图中列的排列顺序相同，SELECT 语句中的第1 列就是视图的第 1 列，SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。

注意： 视图名称是在数据库中是唯一的，不能与其他视图和表重名。
视图不仅可以基于真实表，也可以在视图基础上继续创建视图。

在这里插入图片描述
虽然在视图上继续创建视图的语法没有错误，但是还是应该尽量避免这种操作。这是因为对多数DBMS 来说，多重视图会降低SQL 的性能。

注意事项：
需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY 语句。下面这样定义视图时错误的。

CREATE VIEW product_sum(product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
	FROM product
GROUP BY product_type
ORDER BY product_type;

Q：为什么不能使用ORDER BY子句？
A：因为视图和表是一样的，数据行都是没有顺序的。

基于单表查询的视图
在product表的基础上创建一个视图，代码如下：

CREATE VIEW productsum(product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
	FROM product
GROUP BY product_type;

创建的视图如下图所示：
在这里插入图片描述
基于多表的视图
为了学习多表视图，再创建一张表，相关代码如下：

-- 创建一张shop_product 表
create table shop_product
(shop_id char(4) NOT NULL,
 shop_name varchar(200) NOT NULL,
 product_id char(4) NOT NULL,
 quantity INTEGER NOT NULL,
 primary key(shop_id, product_id));


INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000A', '东京', '0001', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000A', '东京', '0002', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000A', '东京', '0003', 15); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000B', '名古屋', '0002', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000B', '名古屋', '0003', 120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000B', '名古屋', '0004', 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000B', '名古屋', '0006', 10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000B', '名古屋', '0007', 40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000C', '大阪', '0003', 20); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000C', '大阪', '0004', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000C', '大阪', '0006', 90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000C', '大阪', '0007', 70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) values ('000D', '福冈', '0001', 100);

在product表和shop_product表的基础上创建视图。

create view view_shop_product(product_type, sale_price, shop_name)
as 
select product_type, sale_price, shop_name
	from product,
		 shop_product
where product.product_id = shop_product.product_id;

创建的视图如下：
在这里插入图片描述
在这个视图的基础上进行查询：

select sale_price, shop_name
	from view_shop_product
where product_type = '衣服';

在这里插入图片描述

1.5 如何修改视图结构

修改视图结构的基本语法如下：

ALTER VIEW<视图名> AS <SELECT语句>

当然视图名在数据库中需要是唯一的，不能与其他视图和表重名。
当然也可以通过将当前视图删除然后重新创建的方式达到修改的效果。（对于数据库底层是不是也是这样的呢，可以自己探索一下。）

修改视图

修改上方的productsum视图为

ALTER VIEW productsum
	AS
		SELECT product_type, sale_price
			FROM product
WHERE regist_date > '2009-09-11';

此时productsum视图内容如下图所示
在这里插入图片描述

1.6 如何更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

  • 聚合函数SUM()、MIN()、MAX()、COUNT()等
  • DISTINCT关键字
  • GROUP BY 子句
  • HAVING 子句
  • UNION 或 UNION ALL 运算符
  • FROM 子句中包含多个表

视图归根结低还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

更新视图

因为我们刚刚修改的productsum视图不包括以上的限制条件，我们来尝试更新一下视图。

UPDATE productsum
	SET sale_price = '5000'
WHERE product_type = '办公用品';

未执行update语句前的productsum视图：
在这里插入图片描述
执行update语句之后，再来查看productsum视图，可以发现数据以及更新了：
在这里插入图片描述

注意： 如若mysql执行update语句会报错，报错如下：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences

这是因为MySQL运行在 safe-updates 模式下，该模式会导致非主键条件下无法执行 update 或者 delete 命令。

可以通过以下SQL进行状态查询：

show variables like 'SQL_SAFE_UPDATES';

在这里插入图片描述
这里显示sql_safe_updates 模式为打开状态。

解决办法：

  • 执行下面的SQL，关闭 safe-updates 模式：
set sql_safe_updates = 0;
-- 或者
set sql_safe_updates = false;
  • 执行下面的SQL 语句，打开 safe-updates 模式：
set sql_safe_updates = 1;
-- 或者
set sql_safe_updates = true;

2. 子查询

代码如下（示例）：

SELECT stu_name
FROM (
		SELECT stu_name, COUNT(*) AS stu_cnt
		FROM students_info
		GROUP BY stu_age) AS studentsum;

这个语句看起来很好理解，其中使用括号括起来的SQL语句首先执行，执行成功后再执行外面的SQL语句。上一节提到的视图也是根据SELECT语句创建视图然后在这个基础上在进行查询。
Q：什么是子查询呢？子查询和视图又有什么关系呢？

2.1 什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL4.1开始引入，在SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

2.2 子查询和视图的关系

子查询就是将用来定义视图的 SELECT语句直接用于 FROM子句当中。其中AS studentsum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了。

2.3 嵌套子查询

与在视图上再定义视图类似，子查询也没有具体的限制，例如：

SELECT product_type, cnt_product
FROM (SELECT *
	 	FROM (SELECT product_type,
	 					COUNT(*) AS cnt_product
	 			FROM product
	 			GROUP BY product_type) AS productsum
	 			WHERE cnt_product = 4) AS productsum2;

其中最内层的子查询我们将其命名为productsum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。

虽然嵌套子句可以查询出结果，但是随着子查询嵌套层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

2.4 标量子查询

标量就是单一的意思，那么标量子查询也就是单一的子查询。
Q：那什么叫做单一的子查询呢？
A：所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

例如，我们有下面这有一张表：

product_idproduct_namesale_price
0003运动T恤4000
0004菜刀3000
0005高压锅6800

那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。

2.5 标量子查询有什么用

那知道了标量子查询可以返回一个值了，那么它有什么作用呢？
先来看几个具体的需求：

  1. 查询出销售单价高于平均销售单价的商品
  2. 查询出注册日期最晚的那个商品

Q：看如何通过标量子查询语句查询出销售单价高于平均销售单价的商品？

代码如下：

SELECT product_id, product_name, sale_price
	FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);

运行结果如图：
在这里插入图片描述

上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑出合适的商品。

由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

还可以这样使用标量子查询：

SELECT product_id,
	   product_name,
	   sale_price,
	   (SELECT AVG(sale_price)
	   		FROM product) AS avg_price
	   	FROM product;

运行结果如下图：
在这里插入图片描述

2.6 关联子查询

什么是关联子查询

关联子查询既然包含关联两个字那么一定意味着查询与子查询之间存在这联系。这种联系是如何建立起来的呢？

先来看一个例子：

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

运行结果：
在这里插入图片描述

通过上面的例子，我们知道关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的，接下来我们就一起看一下关联子查询的具体内容吧。

关联子查询与子查询的联系

查询出销售单价高于平均销售单价的商品的SQL语句如下：

SELECT product_id, product_name, sale_price
	FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);

运行结果如图：
在这里插入图片描述

再来看一下这个需求：选取出各商品种类中高于该商品种类的平均销售单价的商品的SQL语句如下：

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

运行结果如图：
在这里插入图片描述

上面这两个语句的区别：

在第二条SQL语句也就是关联子查询我们将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE 语句连接了两个查询。

但是如果刚接触的话一定会比较疑惑关联查询的执行过程，可以通过这篇文章了解。在这里可以简要的概况为：

  1. 首先执行不带WHERE的主查询；
  2. 根据主查询的结果匹配product_type，获取子查询的结果；
  3. 将子查询结果再与主查询结合执行完整的SQL语句。
在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。

总结

视图和子查询是数据库操作中较为基础的内容，对于一些复杂的查询需要使用子查询加一些条件语句组合才能得到正确的结果。但是无论如何对于一个SQL语句来说都不应该设计的层数非常深且特别复杂，不仅可读性差而且执行效率也难以保证，所以尽量有简洁的语句来完成需要的功能。

Abro.
关注
阿里天池AI训练营SQL笔记——Task02：SQL基础查询与排序
cjb_tjy的博客
07-21 230
②无返回值，此处WHEREpurchase_priceNULL的语法不对，应该是WHEREpurchase_priceisnotNULL。这里WHERE不可行，因为，WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为2行”或者“平均值为500”等）。HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUPBY中指定的列名（聚合键）。SQL中的执行结果是随机排列的，当需要按照特定顺序排序时，可已使用ORDERBY子句。...
阿里天池AI训练营SQL笔记——Task03：复杂查询方法-视图、子查询、函数
cjb_tjy的博客
07-22 208
视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。子查询指一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。谓词就是返回值为真值的函数。包括LIKEBETWEENINEXISTSCASE表达式是函数的一种。是SQL中数一数二的重要功能，有必要好好学习一下。...
SQL server 复杂查询
11-05
SQL server 复杂查询那条原创的文档所需要的源代码，包括数据库的创建，数据表的创建。方便读者们验证查询
SQL复杂查询知识梳理
wenqi
12-19 2100
视图 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。 为什么使用视图 重用SQL语句 简化复杂的SQL操作，编写查询后，可以方便地重用它而不必知道它的基本查询细节 使用表的组成部分而不是整张表 保护数据，可以给用户授予表的特定部分的访问权限而不是整张表的访问权限 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据 性能问题: 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发
SQL 复杂查询
前端精读周刊
03-14 5332
SQL 复杂查询指的就是子查询。为什么子查询叫做复杂查询呢？因为子查询相当于查询嵌套查询，因为嵌套导致复杂度几乎可以被无限放大（无限嵌套），因此叫复杂查询。下面是一个最简单的子查询例子：S...
【SQL】复杂查询
最新发布
weixin_45183162的博客
06-08 1335
● 从 SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实 际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。● 使用视图，可以轻松完成跨多表查询数据等复杂操作。● 可以将常用的SELECT语句做成视图来使用。● 创建视图需要使用CREATE VIEW语句。● 视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。● 删除视图需要使用DROP VIEW语句。
SQL----复杂查询
laugh哥的博客
10-19 677
视图+子查询
阿里天池AI训练营SQL笔记——Task01：初识数据库与SQL
cjb_tjy的博客
07-21 229
数据库中存储的表结构类似于excel中的行和列，在数据库中，行称为记录，它相当于一条记录，列称为字段，它代表了表中存储的数据项目。行和列交汇的地方称为单元格，一个单元格中只能输入一条记录。根据对RDBMS赋予的指令种类的不同，SQL语句可以分为以下三类.DDL（DataDefinitionLanguage，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL包含以下几种指令。CREATE创建数据库和表等对象DROP删除数据库和表等对象ALTER。......
天池训练营MySQL笔记task_3自用
05-08
在【标题】"天池训练营MySQL笔记task_3自用"和【描述】中，我们可以看到视图作为学习的重点。 视图的核心作用在于： 1. 提高重用性：对于经常执行的复杂查询，可以将其创建为视图，后续只需查询视图，简化了代码...
Task03：复杂查询方法-视图、子查询、函数等
wxiaoyiwenrong的博客
07-03 296
本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；1、视图定义：视图是依据select语句并创建出的虚拟和临时表查询语句：select stu_name from view_students_info;与表的本质区别：是否保存了实际的数据，视图并不是数据库真是存储的数据表，其数据来源均建立在真实表的基础上视图与表的关系：视图不是表，视图是虚表，视图依赖于表存在意义：（1）保存频繁使用的se
sql复杂查询
Silbert Monaphia
04-13 828
前言　　前面的一篇文章写了下数据库设计和SQL语句基础，里面都是简单的增删改查，而且对最重要的查询也没有深入研究，实在是罪过，那么本文就sql复杂的查询语句进行探讨，也是当作一个记录好了复杂查询No.1假设我们现在有张这样的score表(sn表示student_name学生姓名，cn表示课程class_name名字，sc表示score成绩) sn cn sc 光头 语文 9
SQL-复杂查询
qq_42012732的博客
06-24 457
SQL 复杂查询1. 视图1.1 视图和表1.2 创建和使用视图1.3 视图的限制1.4 删除视图2. 子查询2.1 子查询视图2.2 标量子查询2.3 关联子查询3. 窗口函数4. 变量4.1 变量的定义 1. 视图 1.1 视图和表 从SQL角度看，视图就是一张表，两者的区别在于是否保存了实际的数据 a) 创建表时，会通过insert语句将数据保存到数据库中，而数据库中数据保存到存储设备 b) 使用视图时，并不会将数据保存到任何地方，实际上视图保存的时select语句，从视图读取数据时，视图会在内部执行
Sql复杂查询
qq_26586953的博客
09-23 474
sql 复杂查询 1、题目： 1、实现：
复杂SQL查询
05-26 470
表A 一对多关联 表B   将关联表B中的多个字段拼接为一个字段，与表A呈现出一对一效果，查询显示SELECT    *FROM    (        SELECT            a.id,            a.Dates,            d.PostionName,            row_number () OVER (ORDER BY a.Dates DESC)...
SQL学习-复杂查询
quanxr的博客
11-10 459
复杂查询
SQL学习|复杂查询
DXRfighting的博客
08-18 649
使用视图 视图是虚拟的表。看下面一个例子： SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2'; 可以将整个查询包装成一个productcustomers的虚拟表： SELECT cust_name,
SQL复杂的查询
LUOCHENLONG的专栏
09-19 3539
嵌套查询 定义：内查询先执行，内查询给外查询提供参数，外查询再查询。 语法：select * from 表1  where  字段=（select *from 表2）; 关联子查询 定义：外查询先执行，给内查询提供参数，内查询查询后给外查询提供参数，外查询再执行。 语法：select    字段=（select *from 表2） from 表1 ; 处理NULL数据——I
Activiti5.1 学习笔记：持久化与关键组件解析
"Activiti5.1学习笔记" Activiti 是一个流行的工作流和业务流程管理（BPM）系统，主要用于企业级应用。本学习笔记主要围绕Activiti 5.1 版本展开，涵盖其核心特性、配置以及使用方法。 在持久化方面，Activiti ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值