SQL学习打卡3—复杂查询

视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

视图与表有什么区别

视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。视图不是表，视图是虚表，视图依赖于表

为什么会存在视图

那既然已经有数据表了，为什么还需要视图呢？主要有以下几点原因：

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

创建视图

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

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

其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。 需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句，这是因为视图和表一样，数据行都是没有顺序的。

如何修改视图结构

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

其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。

如何更新视图内容（避免更新视图操作以免造成原始表的不同步）

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

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

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等；
• DISTINCT 关键字；
• GROUP BY 子句；
• HAVING子句；
• UNION 或 UNION ALL 运算符；
• FROM 子句中包含多个表。
  视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了

注意：在创建视图时也尽量使用限制不允许通过视图来修改表

如何删除视图

删除视图的基本语法如下：

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

子查询

子查询，写在某个SELECT 语句后() 中的SELECT 语句，把内层查询结果当作外层查询参照的数据表
来用的查询，类似于Excel的函数嵌套

标量子查询

标量就是单一的意思，那么标量子查询也就是单一的子查询，那什么叫做单一的子查询呢？
所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表

product_id | product_name | sale_price 
------------+-------------+----------
0003       | 运动T恤       | 4000 
0004       | 菜刀          | 3000 
0005       | 高压锅        | 6800

那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。

标量子查询作用

几个具体的需求：
1.查询出销售单价高于平均销售单价的商品
2.查询出注册日期最晚的那个商品

思考：
1.可以先查询出平均销售单价，把他作为标量子查询，在用所有的出售单价进行对比

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

2.把当前的日期作为标量子查询，用所有的数据日期进行日期差值，升序排列，只取一条数据

关联子查询作用

子查询：1.查询出销售单价高于平均销售单价的商品

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

关联子查询：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);

总结：

1、关联子查询的执行逻辑完全不同于正常的SELECT语句。

2、关联子查询执行逻辑如下：

（1）先从主查询的Product表中product _type列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。

（2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集，就是最后的答案。

练习题

# 3.3
select product_id,product_name,product_type,sale_price,
         (SELECT AVG(sale_price)
          FROM product) as sale_price_avg
from product
group by product_name;

# 3.4
create view AvgPriceByType (product_id,product_name,product_type,sale_price,sale_price_avg_type)
as
select product_id,
       product_name,
       a.product_type,
       sale_price,
       b.sale_price_avg_type
from product a,
    (select product_type, avg(sale_price) as sale_price_avg_type
     from product
    group by product_type ) as b
where a.product_type=b.product_type;

函数

数学函数

字符串函数

LOWER – 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。

类似的， UPPER 函数用于大写转换。

REPLACE – 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

日期函数

CURRENT_DATE – 获取当前日期

SELECT CURRENT_DATE;

CURRENT_TIME – 当前时间

SELECT CURRENT_TIME;

NOW() 获取当前日期和时间
NOW() 函数括号内不需要任何参数，返回的日期型数据是DATETIME，YYYY-MM-DD HH:MM:SS

SELECT NOW();

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)

特殊操作符

使用 BETWEEN 可以进行范围查询

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

_代表一个任意字符串

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

---

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

使用IN 来代替多层OR

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

还可以将子查询作为IN查询的条件

-- step1：取出大阪门店的在售商品 `product_id`
SELECT product_id
FROM shopproduct
WHERE shop_id = '000C';
+------------+
| product_id |
+------------+
| 0003       |
| 0004       |
| 0006       |
| 0007       |
+------------+
4 rows in set (0.00 sec)

-- 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 |
+--------------+------------+
4 rows in set (0.00 sec)

EXIST的使用方法

表示存在满足特定条件，使用Exists 关键字时，内层查询语句不返回查询的记录，而是返回一个真
假逻辑值，如果内层查询语句查询到满足条件的记录，就返回真值，否则返回假值。当返回真值是，外
层查询语句将进行查询，当返回假值时，外层查询语句不进行查询或者查询不出任何记录

试题
3.5 当值为NULL时：

1）算术运算：对其进行任何算术运算（加减乘除）——结果都为NULL
2）进行任何比较运算——均无法判定是真还是假，是除真假之外的第三种unknown，所以对应记录不会输出。
3）判定只能是 IS NULL 或 IS NOT NULL
4）聚合函数：只有count(*)会将NULL计入到内
5）使用distinct时，NULL也被视为一行，而且只会保留一行；
6）group by()：会将NULL视为一组特定数据
7)ABS(Null)：结果为Null

3.6

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

NOT IN参数中不能含NULL，否则结果为空。

3.7

select
sum(case when sale_price<=1000 then 1  else 0 end) as low_price,
       sum(case when sale_price between 1001 and 3000 then 1 else 0 end ) as mid_price,
       sum(case when sale_price>=3001 then 1 else 0 end) as high_price
from product;