SQL-task3-学习SQL复杂一点的查询方法

学习目标：

学习SQL复杂一点的查询方法

---

学习内容：

视图

视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

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

需要视图主要有以下几点原因：

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

创建视图的基本语法如下：

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

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

• 基于单表的视图

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

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

• 基于多表的视图
  我们在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语句>

我们修改上方的productSum视图为

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 子句中包含多个表。
  删除视图
  删除视图的基本语法如下：

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

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

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。其中AS studentSum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 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;

其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
标量子查询
所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列
让我们看如何通过标量子查询语句查询出销售单价高于平均销售单价的商品。

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

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

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

视图和子查询是数据库操作中较为基础的内容，对于一些复杂的查询需要使用子查询加一些条件语句组合才能得到正确的结果。但是无论如何对于一个SQL语句来说都不应该设计的层数非常深且特别复杂，不仅可读性差而且执行效率也难以保证，所以尽量有简洁的语句来完成需要的功能。

函数

sql自带了各种各样的函数，极大提高了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(日期元素 FROM 日期)
转换函数
一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。
CAST – 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
COALESCE – 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)

谓词

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

谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS
LIKE谓词 – 用于字符串的部分一致查询
%是代表“零个或多个任意字符串”的特殊符号
前中后方一致
BETWEEN谓词 – 用于范围查询
IS NULL、 IS NOT NULL – 用于判断是否为NULL
IN谓词 – OR的简便用法
多个查询条件取并集时可以选择使用or语句。
EXIST 谓词

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

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

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

CASE 表达式

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。
语法：

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

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。 无论多么庞大的 CASE 表达式，最后也只会返回一个值。
聚合函数 + CASE WHEN 表达式即可实现该效果

---