Task03_复杂一点的查询

知识点总结

一.视图

1.视图并不是数据库真实存储的数据表

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

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

SELECT 语句中列的排列顺序和视图中列的排列顺序相同

视图的列名是在视图名称之后的列表中定义的

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

二.子查询

子查询是从最内层开始执行的（由内而外）

1.标量子查询：不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

2.关联子查询SQL语句执行顺序不一样：

首先执行不带WHERE的主查询

根据主查询讯结果匹配product_type，获取子查询结果

将子查询结果再与主查询结合执行完整的SQL语句

三.函数

3.1 算数函数

ABS MOD ROUND

1.主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数

2.float（10，3） 表示总位数为10，小数位占3

3.2 字符串函数

1. UTF-8:一个汉字=3个字节
2. SUBSTRING – 字符串的截取

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

截取的起始位置从字符串最左侧开始计算，索引值起始为1。截取的起始位置：按字符计算

例：SELECT SUBSTRING(str1 from 2 for 2) AS len1 FROM samplestr; 表示从第二个字符开始截取两个字符

3.SUBSTRING_INDEX – 字符串按索引截取

语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)

SELECT SUBSTRING_INDEX('www,458,88,aa,mysql.com', ',', 2);
--结果：www,458
SELECT SUBSTRING_INDEX('www,458,88,aa,mysql.com', ',', -2);
--结果：aa,mysql.com

4.字符串按需重复多次

语法：REPEAT(string, number)

SELECT REPEAT('中国',4);
--中国中国中国中国

3.3 日期函数

3.4 转换函数

1.数据类型转换

语法：CAST（转换前的值 AS 想要转换的数据类型）

2.值转换 COALESCE – 将NULL转换为其他值，返回可变参数左侧开始第 1个不是NULL的值

语法：COALESCE(数据1，数据2，数据3……)

四.谓词

• LIKE：部分一致性查询，搭配%或者_
• BETWEEN：取闭区间范围内的值
• IS NULL、IS NOT NULL
• IN、NOT IN：取值，在使用IN 和 NOT IN 时是无法选取出NULL数据的

​ 注：可以使用子查询、视图作为IN、NOT IN的参数

• EXISTS：判断记录是否存在

  EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询（常为关联子查询）

  可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯

五.case表达式

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

聚合函数+ case when 实现行列转换

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

注意：如果没有group by也会报sql_mode=only_full_group_by的问题。

• 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
• 当待转换列为文本时，可以使用MAX MIN等聚合函数

练习题

3.1

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

• 条件 1：销售单价大于等于 1000 日元。
• 条件 2：登记日期是 2009 年 9 月 20 日。
• 条件 3：包含商品名称、销售单价和登记日期三列。

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

3.2

无法插入，对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。product表中product_id product_type都是非NULL值，没有指定默认值。

3.3

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）

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

3.5

运算或者函数中含有 NULL 时，结果是否都会变为NULL ？

算数函数运算结果是NULL，如MOD ABS ROUND；

字符串函数运算结果是NULL，如CONTACT LENGTH LOWER REPLACE；

转换函数COALESCE将NULL转换为其他值。

3.6

1：选出价格purchase_price不是500，2800，5000 的记录。

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

2：没有选出任何结果。在使用IN 和 NOT IN 时是无法选取出NULL数据的

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

3.7
按照销售单价( sale_price )对 product（商品）表中的商品进行分类。

• 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
• 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
• 高档商品：销售单价在3001日元以上（运动T恤、高压锅）

SELECT 	COUNT(CASE WHEN sale_price<1000 THEN product_name ELSE NULL END ) 
AS low_price,
				COUNT(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN product_name 	ELSE NULL END )
AS mid_price,
				COUNT(CASE WHEN sale_price>3000 THEN product_name	ELSE NULL END ) 
AS high_price
FROM product;

DW-SQL课程