SQL进阶-复杂查询方法-视图、子查询、函数等

2024-09-05 13:18
文章标签 sql 进阶 函数 查询 视图 database 复杂 查询方法

本文主要是介绍SQL进阶-复杂查询方法-视图、子查询、函数等，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

视图

SELECT stu_name FROM view_students_info;

单从表面上看起来这个语句是和正常的从数据表中查询数据是完全相同的，但其实我们操作的是一个视图。所以从SQL的角度来说操作视图与操作表看起来是完全相同的，那么为什么还会有视图的存在呢？视图到底是什么？视图与表有什么不同呢？

什么是视图

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

视图与表有什么区别

是否保存了实际的数据
视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
视图不是表，视图是虚表，视图依赖于表

为什么会存在视图

原因

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

如何创建视图

语法

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

SELECT 语句需要书写在 AS 关键字之后
视图名在数据库中需要是唯一的，不能与其他视图和表重名
图不仅可以基于真实表，也可以在视图的基础上继续创建视图,多重视图会降低 SQL 的性能
注意事项

  • 定义视图时不能使用ORDER BY语句,因为数据行都是没有顺序的**
  • 在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略
    例：
  • 基于单表的视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)FROM productGROUP BY product_type ;
  • 基于多表的视图
CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_nameFROM product,shop_productWHERE product.product_id = shop_product.product_id;

基于上面视图的查询：

SELECT sale_price, shop_nameFROM view_shop_productWHERE product_type = '衣服';

如何修改视图结构

语法：

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

修改视图:

ALTER VIEW productSumASSELECT product_type, sale_priceFROM ProductWHERE regist_date > '2009-09-11';

如何更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改
以下结构不可以被更新

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

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

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

注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

如何删除视图

语法：

DROP VIEW <视图名1> [ , <视图名2> …]

注意：需要有相应的权限才能成功删除。

DROP VIEW productSum;

子查询

SELECT stu_name
FROM (SELECT stu_name, COUNT(*) AS stu_cntFROM students_infoGROUP BY stu_age) AS studentSum;

什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询和视图的关系

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

嵌套子查询

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

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

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

标量子查询

标量就是单一的意思，所以标量子查询也就是单一的子查询。所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。例下面这样一张表
|product_id | product_name | sale_price |
|------------|-------------|----------|
|0003 | 运动T恤 | 4000 |
|0004 | 菜刀 | 3000 |
|0005 | 高压锅 | 6800|
执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果

标量子查询有什么用

具体的需求：

  1. 查询出销售单价高于平均销售单价的商品
  2. 查询出注册日期最晚的那个商品
    如何通过标量子查询语句查询出销售单价高于平均销售单价的商品:
SELECT product_id, product_name, sale_priceFROM productWHERE sale_price > (SELECT AVG(sale_price) FROM product);

关联子查询

关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的

SELECT product_type, product_name, sale_priceFROM product AS p1WHERE sale_price > (SELECT AVG(sale_price)FROM product AS p2WHERE p1.product_type = p2.product_typeGROUP BY product_type);

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

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

各种各样的函数

函数大致分为如下几类：

  • 算术函数 （用来进行数值计算的函数）
  • 字符串函数 （用来进行字符串操作的函数）
  • 日期函数 （用来进行日期操作的函数）
  • 转换函数 （用来转换数据类型和值的函数）
  • 聚合函数 （用来进行数据聚合的函数）

算数函数

  • + - * /四则运算
  • ABS – 绝对值
    语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
当 ABS 函数的参数为NULL时，返回值也是NULL。

  • MOD – 求余数
    语法：MOD( 被除数，除数 )

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
注意：SQL Server 不支持，使用%符号来计算余数

  • ROUND – 四舍五入
    语法：ROUND( 对象数值，保留小数的位数 )

ROUND 函数用来进行四舍五入操作。
注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

字符串函数

  • CONCAT – 拼接
    语法：CONCAT(str1, str2, str3)

MySQL中使用 CONCAT 函数进行拼接。

  • LENGTH – 字符串长度
    语法：LENGTH( 字符串 )
  • LOWER – 小写转换

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。
类似的， UPPER 函数用于大写转换。

  • REPLACE – 字符串的替换
    语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
  • SUBSTRING – 字符串的截取
    语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

  • SUBSTRING_INDEX – 字符串按索引截取
    语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)

该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。

日期函数

不同DBMS的日期函数语法各有不同，本课程介绍一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数

  • CURRENT_DATE – 获取当前日期
  • CURRENT_TIME – 当前时间
  • CURRENT_TIMESTAMP – 当前日期和时间
  • EXTRACT – 截取日期元素
    语法：EXTRACT(日期元素 FROM 日期)

使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

转换函数

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。

  • CAST – 类型转换
    语法：CAST（转换前的值 AS 想要转换的数据类型）
  • COALESCE – 将NULL转换为其他值
    语法：COALESCE(数据1，数据2，数据3……)

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。

谓词

什么是谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

谓词主要有以下几个：

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

LIKE谓词 – 用于字符串的部分一致查询

  • 前方一致：选取出“dddabc”
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';

%是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。

  • 中间一致：选取出“abcddd”,“dddabc”,“abdddc”
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
  • 后方一致：选取出“abcddd“
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';

综合如上三种类型的查询可以看出，查询条件最宽松，也就是能够取得最多记录的是中间一致。这是因为它同时包含前方一致和后方一致的查询结果

  • _下划线匹配任意 1 个字符

使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';

BETWEEN谓词 – 用于范围查询

使用3个参数，查询范围是个闭区间，若不包含临界值，使用<和>

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

IN谓词 – OR的简便用法

多个查询条件取并集时可以选择使用or语句

SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

虽然上述方法没有问题，但还是存在一点不足之处，那就是随着希望选取的对象越来越多， SQL 语句也会越来越长，阅读起来也会越来越困难。这时， 我们就可以使用IN 谓词
`IN(值1, 值2, 值3, …)来替换上述 SQL 语句

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

反之，希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以使用否定形式NOT IN来实现

使用IN 和 NOT IN 时无法选取出NULL数据

使用子查询作为IN谓词的参数

  • IN和子查询

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。

  • NOT IN和子查询

NOT IN 同样支持子查询作为参数，用法和 in 完全一样。

EXIST 谓词

谓词的作用就是 “判断是否存在满足某种条件的记录”. 如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）

  1. EXIST 的使用方法与之前的都不相同
  2. 语法理解起来比较困难
  3. 实际上即使不使用 EXIST，基本上也都可以使用 IN（或者 NOT IN）来代替
  • EXIST谓词的使用方法
SELECT product_name, sale_priceFROM product AS pWHERE EXISTS (SELECT *FROM shopproduct AS spWHERE sp.shop_id = '000C'AND sp.product_id = p.product_id);
  • EXIST的参数

EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询

(SELECT *FROM shopproduct AS spWHERE sp.shop_id = '000C'AND sp.product_id = p.product_id)
  • 子查询中的SELECT *

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店

商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。

因此，使用下面的查询语句，查询结果也不会发生变化。

SELECT product_name, sale_priceFROM product AS pWHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数FROM shopproduct AS spWHERE sp.shop_id = '000C'AND sp.product_id = p.product_id);

大家可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。

  • 使用NOT EXIST替换NOT IN
SELECT product_name, sale_priceFROM product AS pWHERE NOT EXISTS (SELECT *FROM shopproduct AS spWHERE sp.shop_id = '000A'AND sp.product_id = p.product_id);

CASE 表达式

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。

什么是 CASE 表达式？

语法：

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

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值

CASE表达式的使用方法

  • 应用场景1：根据不同分支得到不同列值
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 NULLEND AS abc_product_typeFROM  product;

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。防止漏读，建议显示地写出 ELSE 子句。
CASE 表达式最后的“END”是不能省略的，忘记书写 END 会发生语法错误

  • 应用场景2：实现列方向上的聚合

通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）

SELECT product_type,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type;

假如要在列的方向上展示不同种类额聚合值，该如何写呢？

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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_officeFROM product;
  • 应用场景3：实现行转列

聚合函数 + CASE WHEN 表达式即可实现该转换

  -- CASE WHEN 实现数字列 score 行转列
SELECT name,SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as englishFROM scoreGROUP BY name;

上述代码实现了数字列 score 的行转列，也可以实现文本列 subject 的行转列

-- CASE WHEN 实现文本列 subject 行转列
SELECT name,MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as englishFROM score
GROUP BY name;

https://github.com/imchaoyu/notes

这篇关于SQL进阶-复杂查询方法-视图、子查询、函数等的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1139064。

相关文章

Spring Security 从入门到进阶系列教程

Spring Security 从入门到进阶系列教程

Spring Security 入门系列 《保护 Web 应用的安全》 《Spring-Security-入门（一）：登录与退出》 《Spring-Security-入门（二）：基于数据库验证》 《Spring-Security-入门（三）：密码加密》 《Spring-Security-入门（四）：自定义-Filter》 《Spring-Security-入门（五）：在 Sprin
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
Java进阶13讲__第12讲_1/2

Java进阶13讲__第12讲_1/2

多线程、线程池 1.  线程概念 1.1  什么是线程 1.2  线程的好处 2.   创建线程的三种方式 注意事项 2.1  继承Thread类 2.1.1 认识  2.1.2  编码实现  package cn.hdc.oop10.Thread;import org.slf4j.Logger;import org.slf4j.LoggerFactory
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
hdu1171（母函数或多重背包）

hdu1171（母函数或多重背包）

题意：把物品分成两份，使得价值最接近 可以用背包，或者是母函数来解，母函数（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v） 其中指数为价值，每一项的数目为（该物品数+1）个 代码如下： #include<iostream>#include<algorithm>
阅读更多...
活用c4d官方开发文档查询代码

活用c4d官方开发文档查询代码

当你问AI助手比如豆包，如何用python禁止掉xpresso标签时候，它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2