暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

mysql学习笔记-复杂查询方法-视图、子查询、函数等

Skill数据分析 2021-04-13
738

一、视图

1、什么是视图

视图是一个虚拟的表,不同于直接操作数据表,视图是依据SELECT语句来创建的。

视图不是表,视图是虚表,视图依赖于表

2、为什么会存在视图

主要有以下几点原因:

通过定义视图可以将频繁使用的SELECT语句保存以提高效率。

通过定义视图可以使用户看到的数据更加清晰。

通过定义视图可以不对外公开数据表全部字段,增强数据的保密性。

通过定义视图可以降低数据的冗余。

3、如何创建视图

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

    4、如何修改视图结构

      ALTER VIEW <视图名> AS

      5、如何更新视图内容

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

      • 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

      • DISTINCT 关键字。

      • GROUP BY 子句。

      • HAVING 子句。

      • UNION 或 UNION ALL 运算符。

      • FROM 子句中包含多个表。

      # 创建视图时尽量使用限制不允许通过视图来修改表

      6、如何删除视图

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

        二、子查询


        练习题-第一部分

        3.1

        创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 product(商品)表作为参照表,假设表中包含初始状态的 8 行数据。

        • 条件 1:销售单价大于等于 1000 日元。

        • 条件 2:登记日期是 2009 年 9 月 20 日。

        • 条件 3:包含商品名称、销售单价和登记日期三列。

        对该视图执行 SELECT 语句的结果如下所示。

          SELECT * FROM ViewPractice5_1;
          执行结果
          product_name | sale_price | regist_date
          --------------+------------+------------
          T恤衫 |   1000 | 2009-09-20
          菜刀 | 3000 | 2009-09-20

          答:


            CREATE VIEW ViewPractice5_1(product_name,sale_price,regist_date)
            AS
            SELECT product_name,sale_price,regist_date
            FROM product
            WHERE regist_date = "2009-9-20"
            AND sale_price >= 1000;

            3.2

            向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?

              INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

              答:报错

              3.3

              请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。

                product_id | product_name | product_type | sale_price | sale_price_all
                ------------+-------------+--------------+------------+---------------------
                0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
                0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
                0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
                0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
                0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
                0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
                0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
                0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000

                答:


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



                  3.4

                  请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)。

                    product_id | product_name | product_type | sale_price | avg_sale_price
                    ------------+-------------+--------------+------------+---------------------
                    0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
                    0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
                    0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
                    0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
                    0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
                    0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
                    0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
                    0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000

                    提示:其中的关键是 avg_sale_price 列。与习题三不同,这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说,该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

                    答:


                      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) FROM product p2
                      WHERE p1.product_type = p2.product_type
                      GROUP BY p1.product_type) AS avg_sale_price FROM product p1;

                      三、各种各样的函数


                      函数大致分为如下几类:

                      • 算术函数 (用来进行数值计算的函数)

                      • 字符串函数 (用来进行字符串操作的函数)

                      • 日期函数 (用来进行日期操作的函数)

                      • 转换函数 (用来转换数据类型和值的函数)

                      • 聚合函数 (用来进行数据聚合的函数)

                      1、算数函数

                      • ABS – 绝对值

                      语法:ABS( 数值 )

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

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

                      • MOD – 求余数

                      语法:MOD( 被除数,除数 )

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

                      注意:主流的 DBMS 都支持 MOD 函数,只有SQL Server 不支持该函数,其使用%符号来计算余数。

                      • ROUND – 四舍五入

                      语法:ROUND( 对象数值,保留小数的位数 )

                      ROUND 函数用来进行四舍五入操作。

                      注意:当参数 保留小数的位数 为变量时,可能会遇到错误,请谨慎使用变量。

                      2、字符串函数

                      • 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。

                        SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
                        +------------------------------------------+
                        | SUBSTRING_INDEX('www.mysql.com', '.', 2) |
                        +------------------------------------------+
                        | www.mysql |
                        +------------------------------------------+
                        1 row in set (0.00 sec)
                        SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
                        +-------------------------------------------+
                        | SUBSTRING_INDEX('www.mysql.com', '.', -2) |
                        +-------------------------------------------+
                        | mysql.com |
                        +-------------------------------------------+
                        1 row in set (0.00 sec)
                        获取第1个元素比较容易,获取第2个元素/第n个元素可以采用二次拆分的写法。
                        SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
                        +------------------------------------------+
                        | SUBSTRING_INDEX('www.mysql.com', '.', 1) |
                        +------------------------------------------+
                        | www |
                        +------------------------------------------+
                        1 row in set (0.00 sec)
                        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
                        +--------------------------------------------------------------------+
                        | SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
                        +--------------------------------------------------------------------+
                        | mysql |
                        +--------------------------------------------------------------------+
                        1 row in set (0.00 sec)

                        3、日期函数


                        CURRENT_DATE – 获取当前日期

                        CURRENT_TIME – 当前时间

                        CURRENT_TIMESTAMP – 当前日期和时间


                        • 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 |
                          +---------------------+------+-------+------+------+--------+--------+
                          1 row in set (0.00 sec)

                          4、转换函数


                          • CAST – 类型转换

                          语法:CAST(转换前的值 AS 想要转换的数据类型)


                          • COALESCE – 将NULL转换为其他值

                          语法:COALESCE(数据1,数据2,数据3……)

                          COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的,因此可以根据需要无限增加。

                          在 SQL 语句中将 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 |
                            +-------+-------------+------------+
                            1 row in set (0.00 sec)

                            5、谓词

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

                            谓词主要有以下几个:

                            • LIKE

                            • BETWEEN

                            • IS NULL、IS NOT NULL

                            • IN

                            • EXISTS

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

                             _(下划线)来代替 %,与 % 不同的是,它代表了“任意 1 个字符”


                            BETWEEN谓词 – 用于范围查询

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

                              -- 选取销售单价为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 |
                              +--------------+------------+
                              5 rows in set (0.00 sec)

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

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


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


                               IN谓词 – OR的简便用法


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

                              虽然上述方法没有问题,但还是存在一点不足之处,那就是随着希望选取的对象越来越多, SQL 语句也会越来越长,阅读起来也会越来越困难。

                              这时, 我们就可以使用IN 谓词`IN(值1, 值2, 值3, …)来替换上述 SQL 语句。

                              反之,可以使用否定形式NOT IN来实现。

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

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


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


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


                              EXIST 谓词


                              • EXIST谓词的使用方法

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

                              如果存在这样的记录就返回真(TRUE),如果不存在就返回假(FALSE)。

                              • 使用NOT EXIST替换NOT IN

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

                              CASE 表达式


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



                                上述语句执行时,依次判断 when 表达式是否为真值,是则执行 THEN 后的语句,

                                如果所有的 when 表达式均为假,则执行 ELSE 后的语句。无论多么庞大的 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 NULL
                                  END AS abc_product_type
                                  FROM product;
                                  +--------------+------------------+
                                  | product_name | abc_product_type |
                                  +--------------+------------------+
                                  | T恤 | A :衣服 |
                                  | 打孔器 | B :办公用品 |
                                  | 运动T恤 | A :衣服 |
                                  | 菜刀 | C :厨房用具 |
                                  | 高压锅 | C :厨房用具 |
                                  | 叉子 | C :厨房用具 |
                                  | 擦菜板 | C :厨房用具 |
                                  | 圆珠笔 | B :办公用品 |
                                  +--------------+------------------+
                                  8 rows in set (0.00 sec)

                                  ELSE 子句也可以省略不写,这时会被默认为 ELSE NULL。但为了防止有人漏读,还是希望大家能够显示地写出 ELSE 子句。此外, CASE 表达式最后的“END”是不能省略的,请大家特别注意不要遗漏

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

                                    sum_price_clothes | sum_price_kitchen | sum_price_office
                                    ------------------+-------------------+-----------------
                                    5000 | 11180 | 600

                                    聚合函数 + CASE WHEN 表达式即可实现该效果

                                      -- 对按照商品种类计算出的销售单价合计值进行行列转换
                                      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 |
                                      +-------------------+-------------------+------------------+
                                      1 row in set (0.00 sec)
                                      • (扩展内容)应用场景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 english
                                        FROM score
                                        GROUP BY name;
                                        +------+---------+------+---------+
                                        | name | chinese | math | english |
                                        +------+---------+------+---------+
                                        | 张三 | 93 | 88 | 91 |
                                        | 李四 | 87 | 90 | 77 |
                                        +------+---------+------+---------+
                                        2 rows in set (0.00 sec)

                                        上述代码实现了数字列 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 english
                                          FROM score
                                          GROUP BY name;
                                          +------+---------+------+---------+
                                          | name | chinese | math | english |
                                          +------+---------+------+---------+
                                          | 张三 | 语文 | 数学 | 外语 |
                                          | 李四 | 语文 | 数学 | 外语 |
                                          +------+---------+------+---------+
                                          2 rows in set (0.00 sec

                                          总结:

                                          • 当待转换列为数字时,可以使用SUM AVG MAX MIN等聚合函数;

                                          • 当待转换列为文本时,可以使用MAX MIN等聚合函数

                                          练习题-第二部分

                                          3.5

                                          运算或者函数中含有 NULL 时,结果全都会变为NULL ?(判断题)

                                          答:

                                          正确

                                          3.6

                                          对本章中使用的 product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?

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


                                            答:

                                            会得到商品进价不为500,2800,500的商品,但不包含null值商品

                                              SELECT product_name, purchase_price
                                              FROM product
                                              WHERE purchase_price NOT IN (500, 2800, 5000, NULL);


                                              答:

                                              空值

                                              3.7

                                              按照销售单价( sale_price)对练习 6.1 中的 product(商品)表中的商品进行如下分类。

                                              • 低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)

                                              • 中档商品:销售单价在1001日元以上3000日元以下(菜刀)

                                              • 高档商品:销售单价在3001日元以上(运动T恤、高压锅)

                                              请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。

                                              执行结果

                                                low_price | mid_price | high_price
                                                ----------+-----------+------------
                                                5 | 1 | 2


                                                  SELECT  SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS '低价格',
                                                  SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS '中价格',
                                                  SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS '高价格'
                                                  FROM product;





                                                  文章转载自Skill数据分析,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

                                                  评论