SQL学习——复杂查询方法

最新推荐文章于 2024-06-24 11:37:44 发布
最新推荐文章于 2024-06-24 11:37:44 发布
阅读量632 收藏 1
点赞数
分类专栏： SQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/luschka/article/details/114183326
版权

复杂查询方法

视图

定义

视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作

视图与表的区别—“是否保存了实际的数据”。视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

优点

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

视图的创建

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

SELECT 语句中列的排列顺序和视图中列的排列顺序相同

视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图

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

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。这是因为视图和表一样，数据行都是没有顺序的。（数据行顺序问题可以参考下文）https://blog.csdn.net/weixin_39861627/article/details/113290871?ops_request_misc=%7B%22request%5Fid%22%3A%22161439450816780255228928%22%2C%22scm%22%3A%2220140713.130102334…%22%7D&request_id=161439450816780255228928&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allbaidu_landing_v2~default-1-113290871.pc_search_result_before_js&utm_term=数据行都是没有顺序的

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 = '衣服';

修改视图

ALTER VIEW <视图名> AS <SELECT语句>
ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

更新视图

修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

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

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

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

视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。

注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表（改来改去总会乱套的，只从底层修改）

删除视图

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

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

子查询

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

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

子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了

嵌套子查询

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;

但是没事别瞎写，执行效率差

标量子查询

标量就是单一的意思，那么标量子查询也就是单一的子查询，那什么叫做单一的子查询呢？

所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

关系子查询

SELECT product_type, product_name, sale_price
  FROM product ASp1
 WHERE sale_price > (SELECT AVG(sale_price)
   FROM product ASp2
                      WHERE p1.product_type =p2.product_type
   GROUP BY product_type);
  1. 首先执行不带WHERE的主查询
  2. 根据主查询讯结果匹配product_type，获取子查询结果
  3. 将子查询结果再与主查询结合执行完整的SQL语句

各种各样的函数

函数大致分为如下几类：

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

算数函数

  • ABS – 绝对值

语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。

当 ABS 函数的参数为NULL时，返回值也是NULL。

  • MOD – 求余数

语法：MOD( 被除数，除数 )

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。

注意：主流的 DBMS 都支持 MOD 函数，只有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。

日期函数

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

EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month

转换函数

  • 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谓词 – 用于字符串的部分一致查询

部分一致大体可以分为前方一致、中间一致和后方一致三种类型

%是代表“零个或多个任意字符串”的特殊符号

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';--表示前方一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';--表示中间一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';--表示后方一致

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

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

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

BETWEEN谓词 – 用于范围查询

使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。

- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就不能使用BETWEEN语句，必须使用 < 和 >。

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

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

IN谓词 – OR的简便用法

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

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
-- 使用IN进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);
--使用NOT IN进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);

需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。

NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

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

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。

SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
                       WHERE shop_id = '000C');

使用IN语句的局限以及子查询的优势

  1. 实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
  2. 实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。

使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

NOT IN查询

- NOT IN 使用子查询作为参数，取出未在大阪门店销售的商品的销售单价
SELECT product_name, sale_price 
FROM product 
WHERE product_id NOT IN 
									(SELECT product_id 
										FROM shopproduct 
										WHERE shop_id = '000A');

EXIST 谓词

  • EXIST谓词的使用方法

谓词的作用就是 “判断是否存在满足某种条件的记录”。

如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

EXIST（存在）谓词的主语是“记录”。

SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT *
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);
  • EXIST的参数

之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“ 列 BETWEEN 值 1 AND 值 2”这样需要指定 2 个以上的参数，而 EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1 个参数的谓词。 所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

  • 子查询中的SELECT *

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件

EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。

  • 使用NOT EXIST替换NOT IN

就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。

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

CASE 表达式

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。

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

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。

无论多么庞大的 CASE 表达式，最后也只会返回一个值。

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止漏读，还是显示地写出 ELSE 子句。

此外， CASE 表达式最后的“END”是不能省略的

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 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_office
  FROM product;

实现行转列

在这里插入图片描述
在这里插入图片描述

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 english
  FROM score
 GROUP BY name;
Luschka
关注
专栏目录
MySql数据库——复杂查询
aqipa的博客
06-02 1335
MySql的复杂查询一、连接查询1.1、CROSS JOIN（交叉连接）1.2、等值连接1.2.1、NATURAL JOIN（自然连接）1.2.2、USING 连接1.2.3、ON 连接1.3、非等值连接1.4、外链接1.4.1、LEFT OUTER JOIN （左外连接）1.4.2、RIGHT OUTER JOIN（右外连接）1.4.2、FULL OUTER JOIN（全外连接）1.5、自连接二、联合查询三、子查询3.1、EXISTS 子查询3.2、集合比较子查询3.3、子查询作为计算字段 一、连接查询
Sql学习第一天——SQL 练习题(建表/sql语句)
12-15
在SQL学习的第一天，我们遇到了一个关于创建表和编写SQL语句的练习题。这个练习主要涉及了数据库的建模、数据操作以及SQL查询的高级技巧。首先，我们需要理解题目要求，即找出所有来自马德里(Madrid)并且订单数少于3...
SQL复杂的查询
LUOCHENLONG的专栏
09-19 3527
嵌套查询 定义：内查询先执行，内查询给外查询提供参数，外查询再查询。 语法：select * from 表1  where  字段=（select *from 表2）; 关联子查询 定义：外查询先执行，给内查询提供参数，内查询查询后给外查询提供参数，外查询再执行。 语法：select    字段=（select *from 表2） from 表1 ; 处理NULL数据——I
数据库第五章 复杂查询
最新发布
sediment___的博客
06-24 574
外连接：选定连接操作中涉及的某个表A为基准，即使另一个表B中没有与之匹配的记录，其结果表中也要求包括表A的所有元组。例:查询选修了所有课程的学生学号(Sno)、姓名(Sname)和所在院号(Dno)提示:查询这样的学生，没有一门课程是他不选修的。包括：等值连接查询和非等值连接查询、自然连接查询、自身连接查询、多表查询、外连接查询。(1)带谓词IN的嵌套查询(2)带有比较运算符的嵌套查询(3)带谓词ANY或ALL的嵌套查询(4)带谓词EXISTS的嵌套查询。这个操作要求参加UNION操作的各个结果表的。
MySql--复杂查询
hjf161105的博客
03-31 1083
MySql拥有着非常丰富的查询方式，除了之前总结的简单查询和子查询外，还提供复杂查询的方式，可以进行分组查询、多表查询和合并查询结果，现将之总结一下。 /*****************************分组查询**************************************/ create table studentInfo(id int PRIMARY KEY, name
MySQL基础笔记（三） 复杂查询
dbbaq24022的专栏
07-01 187
所谓复杂查询，指涉及多个表、具有嵌套等复杂结构的查询。这里简要介绍典型的几种复杂查询格式。 一、连接查询 连接是区别关系与非关系系统的最重要的标志。通过连接运算符可以实现多个表查询。连接查询主要包括内连接、外连接等。 假设有Student和Grade两个表如下： +-----+--------+-------+ +-----+---------...
SQL----复杂查询
laugh哥的博客
10-19 675
视图+子查询
SQL数据查询——嵌套查询
12-14
嵌套查询是SQL语言中的一个重要概念，它允许在一个查询语句内部嵌套另一个查询语句，以实现更复杂的查询逻辑。下面将详细讲解嵌套查询的相关知识点。 **1. 嵌套查询语法格式** 嵌套查询的基本语法是将一个查询语句...
SQL数据库查询语句基础文本——可复制的查询语句
05-23
本资源“SQL数据库查询语句基础文本——可复制的查询语句”提供了丰富的SQL语句实例，不仅方便用户直接在数据库中运行，还配以中文解释，帮助理解其工作原理。 1. **基本查询** SQL的基本查询包括`SELECT`语句，...
学习笔记——sql.zip
06-08
"学习笔记——sql.zip"这个压缩包文件很可能包含了关于SQL的学习资料，如教程、笔记、示例代码等，旨在帮助用户掌握SQL的基本概念、语法和高级特性。 首先，SQL的基础知识包括数据类型，如整型（INT）、浮点型...
SQL Server——从入门到放弃（15）– 第九章： 关系查询处理和查询优化 课后习题
12-14
在SQL Server的学习过程中，关系查询处理和查询优化是至关重要的环节，它们直接影响到数据库的性能和效率。本章我们将深入探讨这两个主题，并通过课后习题来加深理解。 首先，让我们了解一下关系查询处理。关系查询...
sql复杂查询例子大全
05-16
sql复杂查询例子大全sql复杂查询例子大全
复杂查询
qq_40208605的博客
05-13 174
public void testSearchFuzaIndex() throws Exception { SolrServer solrServer = new HttpSolrServer("http://localhost:8080/solr/"); SolrQuery query = new SolrQuery(); // q ： 设置筛选条...
复杂的查询
xiaoyugege2007的专栏
01-26 331
CREATE   PROCEDURE     [dbo].[User_Module]   (@UserName   nvarchar(20), @UserPwd   nvarchar(20)) AS select   权限表.用户权限   from   权限表 where   (权限表.Id   in   (select   用户权限表.权限表Id   from   用户权限表 where   用
SQL 复杂查询
前端精读周刊
03-14 5300
SQL 复杂查询指的就是子查询。为什么子查询叫做复杂查询呢？因为子查询相当于查询嵌套查询，因为嵌套导致复杂度几乎可以被无限放大（无限嵌套），因此叫复杂查询。下面是一个最简单的子查询例子：S...
优化查询结果：SQLServer实验教程——简单与连接查询
通过这个实验，参与者不仅可以掌握SQL的基本查询技巧，还能学习到连接不同数据表、处理复杂查询和数据分析的方法，这对于理解和运用数据库管理系统至关重要。在实践中不断分析和优化查询性能，提高数据库查询能力是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值