您好,欢迎访问代理记账网站
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

天池龙珠计划SQL_Task03

Task03:复杂查询方法

本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql;

3.1 视图

3.1.1 视图与表

视图是一个虚拟的表,不同于直接操作数据表,视图是依据SELECT语句来创建的,所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表,然后在这张虚拟表上做SQL操作。
可以看作是一个窗口,通过这个窗口我们可以看到数据库表中真实存在的数据
在这里插入图片描述

3.1.2 为什么会存在视图

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

3.1.3 如何创建视图

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
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;
注意事项

是在一般的DBMS中定义视图时不能使用ORDER BY语句。

3.1.4 修改视图结构:

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

3.1.5 更新视图内容

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

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

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';
  • 原表也可以发现数据也被更新
  • 视图只是原表的一个窗口,所以它修改也只能修改透过窗口能看到的内容
  • 并不推荐这种使用方式, 而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

3.1.6 删除视图:

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

3.2 子查询

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

3.2.1 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询
(子查询就是 SQL内部生成的表)

3.2.2 子查询和视图的关系

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

3.2.3 标量子查询

标量就是单一的意思,那么标量子查询也就是单一的子查询:就是要求我们执行的SQL语句只能返回一个值,也就是要返回表中具体的某一行的某一列。

3.2.4 标量子查询的作用

必须而且只能返回1行1列的结果,因此标量子查询的返回值可以用在=或者<>这样需要单一值的比较运算符之中。

eg. 查询出销售单价高于平均销售单价的商品

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

标量子查询:SELECT AVG(sale_price) FROM product

3.2.5 关联子查询

就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的
eg. 选取出各商品种类中高于该商品种类的平均销售单价的商品

SELECT product_type, product_name, sale_price
  FROM product ASp1
 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.3 函数

函数大致分为如下几类:
算术函数 (用来进行数值计算的函数)
字符串函数 (用来进行字符串操作的函数)
日期函数 (用来进行日期操作的函数)
转换函数 (用来转换数据类型和值的函数)
聚合函数 (用来进行数据聚合的函数)

3.3.1 算数函数

  • ABS – 绝对值 语法:ABS( 数值 )
  • MOD – 求余数 语法:MOD( 被除数,除数 )
  • ROUND – 四舍五入 语法:ROUND( 对象数值,保留小数的位数 )

3.3.2 字符串函数

  • CONCAT – 拼接 语法:CONCAT(str1, str2, str3)
  • LENGTH – 字符串长度 语法:LENGTH( 字符串 )
  • LOWER – 小写转换 LOWER 函数只能针对英文字母使用,它会将参数中的字符串全都转换为小写
  • REPLACE – 字符串的替换 语法:REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )
  • SUBSTRING – 字符串的截取 语法:SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
    使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算,索引值起始为1。

3.3.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 |
+---------------------+------+-------+------+------+--------+--------+

3.3.4 转换函数

在 SQL 中主要有两层意思:一是数据类型的转换,简称为类型转换,在英语中称为cast;另一层意思是值的转换。

CAST – 类型转换

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

-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col; 
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;       

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 |
+-------+-------------+------------+      

3.4 谓词

谓词就是返回值为真值的函数。
包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个:
LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

= 只能判断普通数值。
is 只能判断null值
<=> 安全等于都可以

3.4.1(not)like:通配搜索

一般与通配符搭配使用,对字符型数据进行部分匹配查询
常见的通配符:_任意单个字符 %任意多个字符

Eg. 特殊情况使用转移字符
即设置$符号为转移字符(其他符号也可以)

-查询员工姓名中第二个字符为_的员工信息
SELECT*
FROM `employees`
WHERE `last_name` LIKE '_$_%' ESCAPE '$'

3.4.2 (not) in

功能:查询某字段的值是否属于指定范围内

-查询部门编号为30/50/90的员工的员工名,部门编号
SELECT`last_name`,`department_id`
WHERE `department_id`IN(30,50,90)
FROM `employees`

3.4.3 between …and…

如果不想让结果中包含临界值,那就必须使用 < 和 >

-查询年薪不是100000-20000之间的员工姓名,工资,年薪
SELECT`last_name`,`salary`,`salary`*12*(1+IFNULL(`commission_pct`,0))  '年 薪'
FROM `employees`
WHERE `salary`*12*(1+IFNULL(`commission_pct`,0)) NOT BETWEEN 100000 AND 200000;

3.4.4 is (not) null

-查询奖金率为0的员工信息
SELECT *
FROM `employees`
WHERE `commission_pct` IS NULL

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

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

展开后的结果

SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');

你会疑惑既然 in 谓词也能实现,那为什么还要使用子查询呢,因为使用子查询即可保持 sql 语句不变,极大提高了程序的可维护性,这是系统开发中需要重点考虑的内容。

3.4.6 EXIST (*)

  • 谓词的作用就是 “判断是否存在满足某种条件的记录”。
  • 如果存在这样的记录就返回真(TRUE),如果不存在就返回假(FALSE)。
  • EXIST的主语是“记录”。
  • EXIST的参数:只需要在右侧书写 1 个参数,通常都会是一个子查询。
  • 子查询中的SELECT *:由于 EXIST 只关心记录是否存在,因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件
  • 使用NOT EXIST替换NOT IN

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

语法:

case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end 新名称--新名称可以省略

ELSE 子句也可以省略不写,这时会被默认为 ELSE NULL(最好还是写)。
此外,最后的“END”是不能省略的

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

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;  
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服         |      5000 |
| 办公用品      |       600 |
| 厨房用具      |     11180 |
+--------------+-----------+

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

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 |
+-------------------+-------------------+------------------+

应用场景3:实现行转列


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进