SQL学习|复杂查询

最新推荐文章于 2024-07-25 10:28:23 发布
最新推荐文章于 2024-07-25 10:28:23 发布
阅读量640 收藏 2
点赞数
分类专栏： SQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/DXRfighting/article/details/119782809
版权
本文介绍了SQL视图的概念和使用，包括如何创建、查看、删除和更新视图，以及如何通过视图过滤和重新格式化数据。同时，详细探讨了子查询的运用，如作为计算字段、在联结表中使用，以及如何用子查询进行数据过滤。此外，还讨论了内部联结、日期和转换函数，以及谓词如LIKE、BETWEEN、ISNULL等在查询中的应用。案例展示了如何在实际操作中运用这些概念。
摘要由CSDN通过智能技术生成

使用视图

视图是虚拟的表。看下面一个例子：

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, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

视图不包含任何列或数据，它包含的是一个SQL查询。

  • 使用视图
  1. 视图使用CREATE VIEW语句来创建
  2. 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
  3. 用DROP删除视图，语法为DROP VIEW viewname;
  4. 更新视图时，可以先用DROP在用CREATE，也可以直接用CREATE OR REPLACE VIEW。

创建一个名为productcustomers的视图

CREATE VIEW productcustomers AS 
SELECT cust_name, cust_contact, prod_id 
FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id    
    AND orderitems.order_num = orders.order_num;

这个视图联结了三个表，以返回以订购了任意产品的所有客户列表。

用视图重新格式化检索出的数据

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
    AS vend_title
FROM vendors 
ORDER BY vend_name;

转化为视图：

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
    AS vend_title
FROM vendors 
ORDER BY vend_name;

执行SELECT * FROM vendorlocations;

用视图过滤不想要的数据

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

这里排除了没有电子邮件的那些行，使用SELECT * FROM customeremaillist;查看。

使用视图与计算字段
检索特定订单的物品并计算总价格

SELECT prod_id,
          quantity,
          item_price, 
          quantity * item_price AS expanded_price
FROM orderitems 
WHERE order_num = 20005;

转化为视图：

CREATE VIEW orderitemsexpanded AS
SELECT order_num, 
          prod_id,
          quantity,
          item_price, 
          quantity * item_price AS expanded_price
FROM orderitems;

然后可以使用SELECT * FROM vendorlocations;查询：

修改视图结构

基本语法：

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

更新视图内容

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

更新视图内容对应的表中的内容也会更新，但是需要注意的是，只有表中映射到视图中的记录才会被更新，也就是只有从视图中可以看到的记录才会更新。基于这个原因，一般不使用视图来对表中的数据更新。

使用子查询

  • 利用子查询过滤
    使用SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');，在查询语句中嵌套了子查询，表示查询具有TNT2订单的客户ID。

检索到这些客户的id后就可以检索出客户的具体信息了， SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

当嵌套语句比较多时可以适当采用缩进

  • 作为计算字段使用子查询
    对每个客户的订单数做计算， SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

orders.cust_id = customers.cust_id完全限定列名。

标量子查询

标量子查询就是查询结果只有一行一列的查询。
看下面这个例子：

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 vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;用来将供应商表和产品表的内容联结。

没有联结条件将会返回笛卡尔积结果，如SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;

  • 内部联结
    SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;这句返回与上面相同的结果，这里使用了INNER JOIN指定另一个表，使用ON传递条件。

  • 联结多个表
    SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

需要注意的是，联结多张表对于性能消耗非常大。

关联子查询

关联子查询是在子查询中使用标志的方法将内层查询与外层查询关联起来过滤一些数据。
选取出各商品种类中高于该商品种类的平均销售单价的商品可以这样写：

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 );

关联子查询的执行过程：

  • 首先执行不带WHERE的主查询
  • 根据主查询结果匹配product_type，获取子查询结果
  • 将子查询结果再与主查询结合执行完整的SQL语句

作业

3.1
CREATE VIEW ViewPractice5_1 ( product_name, sale_price, regist_date ) AS SELECT
product_name,
sale_price,
regist_date 
FROM
	product 
WHERE
	sale_price >= 1000 
	AND regist_date = '2009-09-20';
3.2

无法插入数据（原因？）

3.3
SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	( SELECT avg( sale_price ) FROM product ) AS sale_price_all 
FROM
	product;

这里如果直接使用avg(sale_price)的话只能查出一条数据，因此需要标量子查询。

3.4
CREATE VIEW AvgPriceByType ( product_id, product_name, product_type, sale_price, avg_sale_price ) AS SELECT
product_id,
product_name,
product_type,
sale_price,
( SELECT AVG( sale_price ) AS avg_sale_price FROM product p2 WHERE p2.product_type = p1.product_type ) AS avg_sale_price 
FROM
	product p1;

各种函数

算术函数

  • ABS–绝对值
  • MOD–求余数
  • ROUND–四舍五入

字符串函数

常用的文本处理函数。

函数说明
Left()返回串左边的字符
Length()返回串的长度
Locate()找出串的一个子串
Lower()将串转换为小写
LTrim()去掉串左边的空格
Right()返回串右边的字符
RTrim()去掉串右边的空格
Soundex()返回串的SOUNDEX值
SubString()返回子串的字符
Upper()将转换为大写

其中Soundex返回所有发音类似的结果。如SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');返回发音与Y Lie相似的结果。

日期函数

  • CURRENT_DATE – 获取当前日期
  • CURRENT_TIMESTAMP – 当前日期和时间
  • EXTRACT – 截取日期元素

转换函数

  • CAST – 类型转换
  • COALESCE – 将NULL转换为其他值

谓词

谓词有以下几个：

  • LIKE
  • BETWEEN
  • IS NULL、IS NOT NULL
  • IN
  • EXISTS

LIKE-- 用于字符串的部分一致查询

%可以匹配任意个字符，因此我们要搜索出最前面三个字母为ddd的记录可以这样写：

SELECT
	* 
FROM
	samplelike 
WHERE
	strcol LIKE 'ddd%';

%的位置比较随意，可以在字符串中任意位置。
另外，_可以匹配任意一个字符，比如查询前三个为abc的字符：

SELECT
	* 
FROM
	samplelike 
WHERE
	strcol LIKE 'abc__';

BETWEEN谓词 – 用于范围查询

选取销售单价为100～ 1000元的商品：

SELECT
	product_name,
	sale_price 
FROM
	product 
WHERE
	sale_price BETWEEN 100 
	AND 1000;

between包含的是闭区间

IS NULL、 IS NOT NULL – 用于判断是否为NULL

IN谓词 – OR的简便用法

查询purchase_price 为320， 500和5000的记录。

SELECT
	product_name,
	purchase_price 
FROM
	product 
WHERE
	purchase_price IN ( 320, 500, 5000 );

反之，可以使用not in

另外，in也可以用在子查询中。

EXIST 谓词

使用 EXIST 选取出000c门店在售商品的销售单价：

SELECT
	product_name,
	sale_price 
FROM
	product AS p 
WHERE
	EXISTS ( SELECT * FROM shop_product AS sp WHERE sp.shop_id = '000C' AND sp.product_id = p.product_id );

CASE表达式

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END

case语句判断每一个when后面的表达式是否为真，真的话就执行then后面的表达式。

  • 根据不同分支得到不同列值
SELECT
	product_name,
CASE
		
		WHEN product_type = '衣服' THEN
		CONCAT( 'A ： ', product_type ) 
		WHEN product_type = '办公用品' THEN
		CONCAT( 'B ： ', product_type ) 
		WHEN product_type = '厨房用具' THEN
		CONCAT( 'C ： ', product_type ) ELSE NULL 
	END AS abc_product_type 
FROM
	product;

得到如下结果：
在这里插入图片描述

  • 实现列方向上的聚合
SELECT
	SUM( CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END ) AS sum_price_clothes,
	SUM( CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END ) AS sum_price_kitchen,
	SUM( CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END ) AS sum_price_office 
FROM
	product;

在这里插入图片描述

作业

3.5

运算或者函数中含有 NULL 时，结果全都会变为NULL ？
不会。

3.6

1、会查询出除了purchase_price为null以外的所有purchase_price不是500, 2800, 5000的记录
2、null不能用于比较，所以查询出结果都是空。
sql查询in包含null的情况下，查询结果为空

3.7
SELECT
	count( CASE WHEN sale_price <= 1000 THEN product_id ELSE NULL END ) AS low_price,
	count( CASE WHEN sale_price >= 1001 AND sale_price <= 3000 THEN product_id ELSE NULL END ) AS mid_price,
	count( CASE WHEN sale_price >= 3001 THEN product_id ELSE NULL END ) AS high_price 
FROM
	product;

在这里插入图片描述

参考资料：
[1] https://github.com/datawhalechina/team-learning-sql/blob/main/Task03%EF%BC%9A%E5%A4%8D%E6%9D%82%E4%B8%80%E7%82%B9%E7%9A%84%E6%9F%A5%E8%AF%A2.md
[2] 《MySQL必知必会》

杜小瑞
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL学习之“复杂一点的查询”
KDmanbu的博客
03-21 1435
本节将学习一些SQL查询语句的复杂用法， 在SQL中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表，但它实际不是真实的表。视图与表的区别在于“是否保存了实际的数据”，其数据来源均建立在真实表的基础上。 1、视图介绍 创建视图的语法如下： CREATE VIEW 视图名 AS SELECT 列名
「MySQL」- 复杂的SQL查询语句
热门推荐
「zero」的博客
11-30 1万+
前言 MySQL 除了基础的 CRUD 操作之外 , 还涉及了更多更复杂的查询操作 , 不仅支持一张表进行查询 , 也支持多张表进行查询 ,本章是针对 MySQL 中的 select 查询操作更复杂的一些用法 , 在本章你将会学习进阶的 select 操作 , 随着作者一起往下看看吧(本章书写风格会在单行与多行之间切换) ! ! ! 聚合查询 聚合查询是指对一个数据表中某个字段的数据进行部分或者全部统计查询的一种方式，比如所有全部书的平均价格或者是书籍的总数量等等 ，在这些时候就会使用到聚合查询这种方
sql复杂查询例子大全
05-16
sql复杂查询例子大全sql复杂查询例子大全
SQL 复杂查询
前端精读周刊
03-14 5284
SQL 复杂查询指的就是子查询。为什么子查询叫做复杂查询呢？因为子查询相当于查询嵌套查询，因为嵌套导致复杂度几乎可以被无限放大（无限嵌套），因此叫复杂查询。下面是一个最简单的子查询例子：S...
一些复杂的sql处理
最新发布
2301_76787626的博客
07-25 1101
select 'select '''||partition_name||''' partition_name,min(主键列) flag from '||table_owner||'.'||table_name||' partition ('||partition_name||') union all ' from dba_tab_partitions where table_name='XXX';update 表名 set 字段=null where 字段=某值 --即将表中字段为某值的替换为null。
sql复杂查询
Silbert Monaphia
04-13 821
前言　　前面的一篇文章写了下数据库设计和SQL语句基础，里面都是简单的增删改查，而且对最重要的查询也没有深入研究，实在是罪过，那么本文就sql复杂的查询语句进行探讨，也是当作一个记录好了复杂查询No.1假设我们现在有张这样的score表(sn表示student_name学生姓名，cn表示课程class_name名字，sc表示score成绩) sn cn sc 光头 语文 9
SQL复杂的查询
LUOCHENLONG的专栏
09-19 3519
嵌套查询 定义：内查询先执行，内查询给外查询提供参数，外查询再查询。 语法：select * from 表1  where  字段=（select *from 表2）; 关联子查询 定义：外查询先执行，给内查询提供参数，内查询查询后给外查询提供参数，外查询再执行。 语法：select    字段=（select *from 表2） from 表1 ; 处理NULL数据——I
SQL----复杂查询
laugh哥的博客
10-19 674
视图+子查询
SQLServer中复杂sql语句查询
06-19
在SQL Server中，处理复杂SQL语句查询是数据库管理员和开发者的必备技能。这涉及到多个方面的知识，包括但不限于多表联接、索引优化、触发器应用和存储过程的编写。下面将对这些主题进行详细阐述。 首先，**多表...
SQL学习笔记(pdf)
03-17
这份"SQL学习笔记"涵盖了SQL的基础概念、语法以及高级特性，是学习数据库管理和数据分析的宝贵资料。 1. **SQL基础** - 数据库概念：了解什么是数据库，它的作用以及数据库管理系统（DBMS）如何工作。 - SQL简介...
SQL学习资料（包含所有常用的语句例子）
09-14
这份名为"SQL学习资料（包含所有常用的语句例子）"的压缩包，显然是为那些想要深入理解和掌握SQL的人准备的。其中包含了SQL常用语句的实例，以图文并茂的方式，帮助学习者直观理解SQL语句的执行过程和结果。 首先，...
SQL server 复杂查询
11-05
SQL server 复杂查询那条原创的文档所需要的源代码，包括数据库的创建，数据表的创建。方便读者们验证查询
【SQL】复杂查询
weixin_45183162的博客
06-08 1281
● 从 SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实 际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。● 使用视图，可以轻松完成跨多表查询数据等复杂操作。● 可以将常用的SELECT语句做成视图来使用。● 创建视图需要使用CREATE VIEW语句。● 视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。● 删除视图需要使用DROP VIEW语句。
SQL学习-复杂查询
quanxr的博客
11-10 456
复杂查询
复杂SQL查询
05-26 464
表A 一对多关联 表B   将关联表B中的多个字段拼接为一个字段，与表A呈现出一对一效果，查询显示SELECT    *FROM    (        SELECT            a.id,            a.Dates,            d.PostionName,            row_number () OVER (ORDER BY a.Dates DESC)...
MySql--复杂查询
hjf161105的博客
03-31 1083
MySql拥有着非常丰富的查询方式，除了之前总结的简单查询和子查询外，还提供复杂查询的方式，可以进行分组查询、多表查询和合并查询结果，现将之总结一下。 /*****************************分组查询**************************************/ create table studentInfo(id int PRIMARY KEY, name
sql复杂实例
basycai的博客
08-06 9088
from：http://www.wzsky.net/html/Program/DataBase/35200.html 1.查询语句的使用 　　 　　使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据. 　　 　　1.1相关子查询 　　 　　可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询
SQL 的复杂查询语句，例子及解析
默默不代表沉默
03-09 7337
select *from emp where deptno= (select deptno from emp where ename="smith") 这是查询出与smith同一部门的员工信息。 ----------------------------------------------------------------------------------------------------- ...
SQL复杂查询详解：联接与嵌套教程
本教程旨在帮助学习者掌握如何有效地使用SQL进行复杂的查询，包括理解联接查询的基础概念，掌握联接语法，以及灵活运用嵌套查询来满足更深层次的数据分析需求。这对于在IT行业中进行数据管理和分析至关重要，能够...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值