“SQL编程语言”小组学习的Task03

---

前言

本文章为天池“SQL编程语言”小组学习的Task03-学习日志，需要了解视图并对视图实现创立修改等操作，学习并实现子查询，学习了解函数计算，谓词以及CASE 表达式。

---

一、视图

1.基础知识

2.视图SQL操作

2.1.创建视图

基本语法如下：

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

注：（1）视图名在数据库中需要是唯一的，不能与其他视图和表重名。视图不仅可以基于真实表，也可以在视图的基础上继续创建视图。如下图：

（2）视图和表一样，数据行都是没有顺序的，不建议使用 ORDER BY

在product表的基础上创建一个视图，如下：

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

2.2.修改视图结构

基本语法如下：

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

productSum视图上修改，如下：

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

（整个表修改，结构一致）

2.3.更新视图内容

注：
（1）视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，
在修改时只有满足底层基本表的定义才能成功修改。
（2）如果包含以下结构的任意一种的视图都是不可以被更新的：

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

修改的productSum视图：

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

（原表数据也被更新了，并不推荐这种使用方式，在创建视图时也尽量使用限制不允许通过视图来修改表）

2.4.删除视图

基本语法如下：

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

二、子查询

1.嵌套子查询

如下：

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语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

2.标量子查询

标量子查询语句查询出销售单价高于平均销售单价的商品

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

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

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

3.关联子查询

查询与子查询之间存在着联系
语法如下：

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语句

–

三、函数计算

函数大致分为如下几类：

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

四、谓词

五、CASE 表达式

• CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
• 语法分为简单CASE表达式和搜索CASE表达式两种
  语法如下：

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
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 ： 办公用品    |
+--------------+------------------+
8 rows in set (0.00 sec)