3. 复杂一点的查询

最新推荐文章于 2024-10-05 00:24:42 发布
最新推荐文章于 2024-10-05 00:24:42 发布
阅读量989 收藏 18
点赞数 19
分类专栏： SQL 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/AlexZChoy/article/details/138519465
版权

1. 视图

  • 视图与表的区别：“是否保存了实际的数据”
  • 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时，会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
  • 视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说， 多重视图会降低 SQL 的性能。

创建视图

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
  • 在一般的DBMS中，定义视图时不能使用ORDER BY语句，因为视图和表一样，数据行都是没有顺序的。
  • 在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。
-- 下面这样定义视图在一般的DBMS中是错误的
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
 ORDER BY product_type;
  • 基于单表的视图
-- 在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 = '衣服';

修改视图结构

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

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

更新视图内容

  • 视图是一个虚拟表，对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
  • 对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
    1. 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
    2. DISTINCT 关键字。
    3. GROUP BY 子句。
    4. HAVING 子句。
    5. UNION 或 UNION ALL 运算符。
    6. FROM 子句中包含多个表。
  • 如果原表可以更新，那么视图中的数据也可以更新。反之亦然
-- 尝试更新一下productSum视图
UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';
  • 此时我们发现productSum视图和原表数据都被更新了
    更新后
  • 但原表的数据只有一条做了修改，因为视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。
  • 这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

删除视图

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

DROP VIEW productSum;

在这里插入代码片

2. 子查询

  • 子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
-- 示例
-- 使用括号括起来的sql语句首先执行，执行成功后再执行外面的sql语句
SELECT stu_name
FROM (
         SELECT stu_name, COUNT(*) AS stu_cnt
          FROM students_info
          GROUP BY stu_age) AS studentSum;
  • 子查询和视图的关系
    子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。其中AS studentSum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。
  • 嵌套子查询
    虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
/*其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，
第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。 */

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_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

SELECT product_id, product_name, sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;
  • 关联子查询
    通过一些标志将内外两层的查询连接起来起到过滤数据的目的
-- 查询出销售单价高于平均销售单价的商品
SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

-- 选取出各商品种类中高于该商品种类的平均销售单价的商品
-- 将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询
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);
  • 关联查询的执行过程
    1. 首先执行不带WHERE的主查询
    2. 根据主查询讯结果匹配product_type，获取子查询结果
    3. 将子查询结果再与主查询结合执行完整的SQL语句

3. 函数

算数函数

-- 为了演示算数函数，在此构造samplemath表
-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m NUMERIC(10,3),
 n INT,
 p INT);

-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;
+----------+------+------+
| m        | n    | p    |
+----------+------+------+
|  500.000 |    0 | NULL |
| -180.000 |    0 | NULL |
|     NULL | NULL | NULL |
|     NULL |    7 |    3 |
|     NULL |    5 |    2 |
|     NULL |    4 | NULL |
|    8.000 | NULL |    3 |
|    2.270 |    1 | NULL |
|    5.555 |    2 | NULL |
|     NULL |    1 | NULL |
|    8.760 | NULL | NULL |
+----------+------+------+
11 rows in set (0.00 sec)
  • ABS – 绝对值
    语法：ABS( 数值 )
    当 ABS 函数的参数为NULL时，返回值也是NULL。
  • MOD – 求余数
    语法：MOD( 被除数，除数 )
    小数没有余数的概念，只能对整数列求余数。
    注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
  • ROUND – 四舍五入
    语法：ROUND( 对象数值，保留小数的位数 )
    注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
SELECT m,
    ABS(m) AS abs_col ,
    n, p,
    MOD(n, p) AS mod_col,
    ROUND(m,1) AS round_col
FROM samplemath;
+----------+---------+------+------+---------+-----------+
| m        | abs_col | n    | p    | mod_col | round_col |
+----------+---------+------+------+---------+-----------+
|  500.000 | 500.000 |    0 | NULL |    NULL |     500.0 |
| -180.000 | 180.000 |    0 | NULL |    NULL |    -180.0 |
|     NULL |    NULL | NULL | NULL |    NULL |      NULL |
|     NULL |    NULL |    7 |    3 |       1 |      NULL |
|     NULL |    NULL |    5 |    2 |       1 |      NULL |
|     NULL |    NULL |    4 | NULL |    NULL |      NULL |
|    8.000 |   8.000 | NULL |    3 |    NULL |       8.0 |
|    2.270 |   2.270 |    1 | NULL |    NULL |       2.3 |
|    5.555 |   5.555 |    2 | NULL |    NULL |       5.6 |
|     NULL |    NULL |    1 | NULL |    NULL |      NULL |
|    8.760 |   8.760 | NULL | NULL |    NULL |       8.8 |
+----------+---------+------+------+---------+-----------+
11 rows in set (0.08 sec)

字符串函数

-- 为了学习字符串函数，构造samplestr表。
-- DDL ：创建表
USE  shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML：插入数据
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',	'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',	'月亮', '火星');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',	NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- 确认表中的内容
SELECT * FROM samplestr;
+-----------+------+------+
| str1      | str2 | str3 |
+-----------+------+------+
| opx       | rt   | NULL |
| abc       | def  | NULL |
| 太阳      | 月亮 | 火星 |
| aaa       | NULL | NULL |
| NULL      | xyz  | NULL |
| @!#$%     | NULL | NULL |
| ABC       | NULL | NULL |
| aBC       | NULL | NULL |
| abc哈哈   | abc  | ABC  |
| abcdefabc | abc  | ABC  |
| micmic    | i    | I    |
+-----------+------+------+
11 rows in set (0.00 sec)
  • 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)
    该函数返回一个字符串，该字符串是在给定分隔符之前或之后的子字符串。如果 count 为正数，则返回从字符串开头到第 count 个分隔符之间的子字符串；如果 count 为负数，则返回从字符串末尾到第 count 个分隔符之间的子字符串。
SELECT SUBSTRING_INDEX('one|two|three|four|five', '|', 2);
-- 结果为 'one|two'

SELECT SUBSTRING_INDEX('one|two|three|four|five', '|', -2);
-- 结果为 'four|five'
  • REPEAT – 字符串按需重复多次
SELECT REPEAT('Hello', 3);
-- 结果为 'HelloHelloHello'

SELECT REPEAT('abc', 0);
-- 结果为空字符串 ''

SELECT REPEAT('xyz', -2);
-- 结果为 NULL

日期函数

  • CURRENT_DATE – 获取当前日期
  • CURRENT_TIME – 当前时间
    • CURRENT_TIMESTAMP – 当前日期和时间
SELECT CURRENT_DATE;
-- 返回当前日期，例如 '2024-05-22'

SELECT CURRENT_TIME;
-- 返回当前日期，例如'17:26:09'

SELECT CURRENT_TIMESTAMP;
-- 返回当前日期和时间，例如'2020-08-08 17:27:07'
  • EXTRACT – 截取日期元素
    语法：EXTRACT(日期元素 FROM 日期)
    使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”，“月”，或者“小时”，“秒”等。该函数的返回值并不是日期类型而是数值类型
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
+---------------------+------+-------+------+------+--------+--------+
| now                 | year | month | day  | hour | MINute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2020-08-08 17:34:38 | 2020 |     8 |    8 |   17 |     34 |     38 |
+---------------------+------+-------+------+------+--------+--------+

转换函数

  • CAST – 类型转换
    语法：CAST（转换前的值 AS 想要转换的数据类型）
    当要转换为整型时，需要指定为 SIGNED（有符号） 或者 UNSIGNED（无符号）
-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
+---------+
| int_col |
+---------+
|       1 |
+---------+

-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col   |
+------------+
| 2009-12-14 |
+------------+
  • COALESCE – 将NULL转换为其他值
    语法：COALESCE(数据1，数据2，数据3……)
    返回可变参数 A 中左侧开始第 1个不是NULL的值
SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
+-------+-------------+------------+
| col_1 | col_2       | col_3      |
+-------+-------------+------------+
|    11 | hello world | 2020-11-01 |
+-------+-------------+------------+

4. 谓词

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

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

  • 部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
-- 首先我们来创建一张表
-- DDL ：创建表
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
  PRIMARY KEY (strcol)
);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');
COMMIT; -- 提交事务
SELECT * FROM samplelike;
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
| abddc  |
| abdddc |
| ddabc  |
| dddabc |
+--------+
  • 前方一致
    前方一致即作为查询条件的字符串与查询对象字符串起始部分相同。
    • %是代表“零个或多个任意字符串”的特殊符号
    • 下划线_代表任意 1 个字符
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
  • 中间一致
    中间一致即查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字符串的最后还是中间还是开头都没有关系。
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
+--------+
| strcol |
+--------+
| abcddd |
| abdddc |
| dddabc |
+--------+
  • 后方一致
    后方一致即作为查询条件的字符串与查询对象字符串的末尾部分相同。
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
+--------+
| strcol |
+--------+
| abcddd |
+--------+

BETWEEN谓词 – 用于范围查询

-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| T恤          |       1000 |
| 打孔器       |        500 |
| 叉子         |        500 |
| 擦菜板       |        880 |
| 圆珠笔       |        100 |
+--------------+------------+
  • BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >
SELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 打孔器       |        500 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+

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

  • 为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 叉子         |           NULL |
| 圆珠笔       |           NULL |
+--------------+----------------+
  • 与此相反，想要选取 NULL 以外的数据时，需要使用IS NOT NULL
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤          |            500 |
| 打孔器       |            320 |
| 运动T恤      |           2800 |
| 菜刀         |           2800 |
| 高压锅       |           5000 |
| 擦菜板       |            790 |
+--------------+----------------+

IN谓词 – OR的简便用法

  • 多个查询条件取并集时可以选择使用or语句
-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤          |            500 |
| 打孔器       |            320 |
| 高压锅       |           5000 |
+--------------+----------------+
  • 虽然上述方法没有问题，但还是存在一点不足之处，那就是随着希望选取的对象越来越多， SQL 语句也会越来越长，阅读起来也会越来越困难。这时， 我们就可以使用IN 谓词 `IN(值1, 值2, 值3, …)来替换上述 SQL 语句
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤          |            500 |
| 打孔器       |            320 |
| 高压锅       |           5000 |
+--------------+----------------+
  • 上述语句简洁了很多，可读性大幅提高。 反之，希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以使用否定形式NOT IN来实现。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 运动T恤      |           2800 |
| 菜刀         |           2800 |
| 擦菜板       |            790 |
+--------------+----------------+
  • 需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。 实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉子和圆珠笔。 NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断

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

  • IN和子查询
    IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。
-- 在此，我们创建一张新表shopproduct显示出哪些商店销售哪些商品。
/*
由于单独使用商店编号（shop_id）或者商品编号（product_id）不能区分表中每一行数据 ，
因此指定了 2 列作为主键（primary key）对商店和商品进行组合，用来唯一确定每一行数据。
*/

-- DDL ：创建表
DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct
(  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) -- 指定主键
);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT; -- 提交事务
SELECT * FROM shopproduct;
+---------+-----------+------------+----------+
| shop_id | shop_name | product_id | quantity |
+---------+-----------+------------+----------+
| 000A    | 东京      | 0001       |       30 |
| 000A    | 东京      | 0002       |       50 |
| 000A    | 东京      | 0003       |       15 |
| 000B    | 名古屋      | 0002       |       30 |
| 000B    | 名古屋      | 0003       |      120 |
| 000B    | 名古屋      | 0004       |       20 |
| 000B    | 名古屋      | 0006       |       10 |
| 000B    | 名古屋      | 0007       |       40 |
| 000C    | 大阪      | 0003       |       20 |
| 000C    | 大阪      | 0004       |       50 |
| 000C    | 大阪      | 0006       |       90 |
| 000C    | 大阪      | 0007       |       70 |
| 000D    | 福冈      | 0001       |      100 |
+---------+-----------+------------+----------+
  • 取出大阪在售商品的销售单价
-- step1：取出大阪门店的在售商品 `product_id`
SELECT product_id
FROM shopproduct
WHERE shop_id = '000C';
+------------+
| product_id |
+------------+
| 0003       |
| 0004       |
| 0006       |
| 0007       |
+------------+

-- step2：取出大阪门店在售商品的销售单价 `sale_price`
SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
                       WHERE shop_id = '000C');
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+

-- 上述语句的子查询执行之后，sql 展开成下面的语句
SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+

  • 为什么使用子查询而不是 in

    1. 实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
    2. 实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。
    • 使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

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

-- NOT IN 使用子查询作为参数，取出未在东京门店销售的商品的销售单价
SELECT product_name, sale_price
  FROM product
 WHERE product_id NOT IN (SELECT product_id
                            FROM shopproduct
                           WHERE shop_id = '000A');
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 菜刀         |       3000 |
| 高压锅       |       6800 |
| 叉子         |        500 |
| 擦菜板       |        880 |
| 圆珠笔       |        100 |
+--------------+------------+

EXIST 谓词

  • 谓词的作用就是 “判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
-- 继续以 IN和子查询 中的示例，使用 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);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
  • EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
  • 由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件
SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
  • 使用NOT EXIST替换NOT IN
    NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）
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);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 菜刀         |       3000 |
| 高压锅       |       6800 |
| 叉子         |        500 |
| 擦菜板       |        880 |
| 圆珠笔       |        100 |
+--------------+------------+

5. CASE 表达式

  • CASE 表达式是函数的一种，是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END  
/*
上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，
如果所有的 when 表达式均为假，则执行 ELSE 后的语句。 
无论多么庞大的 CASE 表达式，最后也只会返回一个值。
*/
  • 应用场景1：根据不同分支得到不同列值
    • ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL
    • 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;
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤          | A ： 衣服        |
| 打孔器       | B ： 办公用品    |
| 运动T恤      | A ： 衣服        |
| 菜刀         | C ： 厨房用具    |
| 高压锅       | C ： 厨房用具    |
| 叉子         | C ： 厨房用具    |
| 擦菜板       | C ： 厨房用具    |
| 圆珠笔       | B ： 办公用品    |
+--------------+------------------+
  • 应用场景2：实现列方向上的聚合
SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;  
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服         |      5000 |
| 办公用品      |       600 |
| 厨房用具      |     11180 |
+--------------+-----------+

-- 在列的方向上展示不同种类额聚合值
-- 对按照商品种类计算出的销售单价合计值进行行列转换
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;
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
|              5000 |             11180 |              600 |
+-------------------+-------------------+------------------+
  • 应用场景3：实现行转列
    • 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数
    • 当待转换列为文本时，可以使用MAX MIN等聚合函数
      转换效果
-- 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 english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 |      93 |   88 |      91 |
| 李四 |      87 |   90 |      77 |
+------+---------+------+---------+

-- 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 english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文    | 数学 | 外语    |
| 李四 | 语文    | 数学 | 外语    |
+------+---------+------+---------+
AlexChoyZX
关注
专栏目录
sql-复杂查询语句
weixin_45720616的博客
07-12 2453
复杂的查询语句1 视图1.1 创建视图1.2 修改视图1.3 更新视图1.4 删除视图2 子查询2.1 关联子查询练习题 数据来源 1 视图 定义：视图与表的功能是差不多的，区别在于，视图是虚拟的，并不是真实存在的，他相当于一个窗口，通过视图，我们可以观察到真实的表，从而对其进行操作。 1.1 创建视图 创建视图的语法： CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句> 比如:创建一个名为productsum
用SQL语句进行数据库查询(复杂查询)
@初阶牛的博客
01-03 7265
数据查询的习题,以及答案
Elasticsearch的复杂查询案例（捎带一点Elasticsearch的集群搭建）
QAQnullPoint的博客
08-29 1521
过滤的时候，不进行任何的匹配分数计算，相对于 query 来说，filter 相对效率较高。​ yellow：索引库的每个 primary shard 都是 active 的，但部分的 replica shard 不是 active 的，如单节点创建。match Query即全文检索，它的搜索方式是先将搜索字符串分词，再使用各各词条从索引中搜索。高亮显示：高亮不是搜索条件，是显示逻辑，在搜索的时候，经常需要对搜索关键字实现高亮显示。must：表示必须，多个查询条件必须都满足。...
Spring Data JPA复杂动态查询
liuyunyihao的博客
07-28 9568
Spring Data JPA其诸多优点给我们的工作带来了很多便利，但对于接触不久的同学来说，有些情况让我们头疼。一些复杂的查询，比如涉及到聚合函数、动态多条件等，着实有些棘手。在不够了解的情况下，觉得Spring Data JPA在这方面不太人性化，有时候我们干脆使用原生sql粗暴的来解决这类查询问题。但这与Spring Data JPA的初衷是相悖的，在不断的学习中，慢慢发现Spring Da...
springboot3.x集成SpringDoc Swagger3
记录点滴
03-06 1万+
近期将springboox2.x升级到了3.x，索性将swagger2也同步升级到swagger3。本人提供的解决方案就是通过过滤器的方式对请求进行验证，请求的时候需要在链接后面加上我们自定义的token参数，通过验证token判断是否是合法的访问，注意，添加过滤器后需要在启动类上加上
spring data jpa 实现多条件复杂查询及多表联查
maybe的博客
06-19 3万+
最近发现JPA在处理单表时，很方便，但多表就比较复杂了。今天写一下端午这两天琢磨的多条件查询，还有多表联查。maven依赖啊，配置，继承写法等知识点不展开说了，之前写过一篇文章： spring boot 配置及使用 spring data jpa这里说一下更新的地方：JPA的配置###################################################### ###sp...
深入学习ElasticSearch（五）——ElasticSearch较复杂的查询
Xiaowei's Blog
07-08 3144
上一篇博客讲解了elasticsearch的mapping的相关知识，那么这一篇我就来讲一下如果elasticsearch的索引的mapping特别复杂的话怎么样去查询我们想要的数据。 1、建立索引，插入数据 先利用kibana工具建立一个复杂的索引，建立索引如下： PUT user { "mappings": { "student": { "properties": { ...
Mybatis 常用及复杂查询,mybatis-generator自动生成mapper接口
jieerlianshan的博客
02-02 1万+
mybatis-generator   自动生成Mapper和Mapper.xml 没有mybatis-generator的朋友 请自行百度下载 重要的事情说三遍 每一次用mybatis-generator之前都要先检查里面的src目录有没有之前生成过的文件,假如有就全部都删掉,不删掉又生成，会变成大坑,小心!! 每一次用mybatis-generator之前都要先检查里面的src目录有没
2022年最新前端面试题（大前端时代来临卷起来吧小伙子们..持续维护走到哪记到哪）
热门推荐
qq_54753561的博客
12-25 26万+
2022年最新前端面试题 热门题目 （面经最全版）（蛋糕）...持续维护中，HTML和Css部分vue部分，Null，undefined，Boolean，Number，String，BigInt 、BigInt 、Object、1. **解构赋值、扩展运算符、class类、模块化 import export、箭头函数、generator函数、promise let cosnt ......
sqlite3-递归查询
aflyeaglenku的博客(QQ1010316426）
03-25 1万+
本文是关于sqlite3 的递归查询语法的讲解说明。是一篇译文，原文出自http://www.sqlite.org/lang_with.html 文章的作者也是是sqlite的创造者。 初次接触sqlite的递归查询可能不会使用它的语法，也写不出来sql语句。不要紧。你可以联系笔者（QQ1010316426），我会在能力范围内帮你解答。
使用GDAL查询tiff影像任意一点的高程
11-07
本篇文章将详细讲解如何使用GDAL在C++环境中查询TIFF影像（一种常见的遥感图像格式）上任意一点的高程信息。 首先，我们需要了解GDAL的基本用法。GDAL库提供了一系列的API函数，用于与各类地理空间数据进行交互。在...
活动活动大脑的一个稍微复杂一点的sql
04-12
标题中的“活动活动大脑的一个稍微复杂一点的sql”暗示了我们即将探讨的SQL语句可能具有一定的难度，涉及到了较为复杂的数据库操作。SQL（Structured Query Language）是用于管理和处理关系数据库的标准语言，它用于...
SQL学习之“复杂一点的查询”
KDmanbu的博客
03-21 1456
本节将学习一些SQL查询语句的复杂用法， 在SQL中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表，但它实际不是真实的表。视图与表的区别在于“是否保存了实际的数据”，其数据来源均建立在真实表的基础上。 1、视图介绍 创建视图的语法如下： CREATE VIEW 视图名 AS SELECT 列名
SQL Server—约束和主键外键详解
qq_3517289697的博客
10-03 1764
关系型数据库中一条记录有若干个属性，若其中某一个属性能够位置标识这条记录，这个属性就可以设置为表的主键，主键是确定一条记录的唯一标识，有可能作为主键的属性 学生ID、身份证号。-- 语法 alter table 表名 add constraint 约束名 check(列名 between 范围1 and 范围2)-- 语法: alter table 表名 add constraint 约束名 primary key(列名)-- 1. 可控性: 外键的字段值允许为空，表示没有找到关联的表的数据。
mysql学习教程，从入门到精通，SQL 表的创建（33）
qq_45746668的博客
10-03 1209
如何在数据库服务器上创建数据库。现在是时候在数据库中创建一些表，这些表实际上将保存数据。数据库表只是将信息组织成行和列。
SQL Inject-基于报错的信息获取
guanrongl的专栏
10-03 600
在MySQL中使用一些指定的函数来制造报错，从而从报错信息中获取设定的信息。updatexml() : 函数是MYSQL对XML文档数据进行查询和修改的XPATH函数。extractvalue(): 函数也是MYSQL对XML文档数据进行查询的XPATH函数。Updatexm()函数作用: 改变(查找并替换) XML文档中符合条件的节点的值。extractvalue()函数作用:从目标XML中返回包含所查询值的字符串。第二个参数: XPathstring (Xpath格式的字符串)。
掌握嵌套子查询：复杂 SQL 中 * 列的准确表列关系
最新发布
学习之余随便记记
10-05 850
本文重点讨论在具有 * 列的嵌套子查询中建立表和列之间正确关系的挑战。使用 Teradata SQL 代码示例来说明该过程。本文聚焦于一个别名为 SUBSCRIBER_ 的子查询及其派生的列，这些列在外层查询中被使用。有些列在子查询的选择列表中被明确列出，而其他一些列则来自一个列（SUBSCR.），这个*列引用了 PRD2_ODW.SUBSCRIBER_ 表。接着，分析检查了外层查询的选择列表，特别是来自 SUBSCRIBER_ 子查询的列。
PostgreSQL常用数值处理函数简介
lunan的博客
10-04 595
这些数值处理函数提供了丰富的数值计算功能，从简单的数学操作到更高级的科学计算，满足了大多数数据库操作中的数值处理需求。
postgresql的TOAST表
文牧之的博客
10-04 739
在 PostgreSQL 中，TOAST（The Oversized-Attribute Storage Technique）是一种处理超大数据（如大型文本字段、大型字节数组等）存储的技术。当表中的某些字段非常大，无法直接存储在一个标准 PostgreSQL 数据页中（通常是 8KB），TOAST 就会被用于管理这些超大数据。
图书管理系统 代码 复杂一点
06-28
1. **数据库设计**：系统需要与数据库交互，包括图书信息（作者、标题、分类、库存等）、用户信息（借阅历史、权限等），这涉及到数据结构的设计和SQL查询优化。 2. **用户界面**：前端设计可能使用HTML、CSS和...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值