本篇博客来源于：
wonderful-sql/ch03:复杂一点的查询.md at main · datawhalechina/wonderful-sql · GitHub
视图&子查询
-- ---------------------------------------------------------------
-- 视图是依据SELECT语句来创建的一张虚拟表。
-- 为什么需要视图？
-- 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
-- 通过定义视图可以使用户看到的数据更加清晰。
-- 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
-- 通过定义视图可以降低数据的冗余。
-- 创建视图：
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
-- SELECT 语句中列的排列顺序和视图中列的排列顺序相同。
-- 视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。
-- 虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说， 多重视图会降低 SQL 的性能。
-- 注意：在一般的DBMS中定义视图时不能使用ORDER BY语句。因为视图和表一样，数据行都是没有顺序的。
-- 基于单表的视图：
USE shop;
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;
-- 基于多表的视图:
-- 为了学习多表视图，我们再创建一张表 shop_product，相关代码如下：
USE shop;
CREATE TABLE shop_product
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
-- 在product表和shop_product表的基础上创建视图:
USE shop;
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;
-- 可以在这个视图的基础上进行查询:
USE shop;
SELECT sale_price, shop_name
FROM view_shop_product
WHERE product_type = '衣服';
-- 修改视图结构
ALTER VIEW <视图名> AS <SELECT语句>
-- 修改上方的 productsum 视图为:
USE shop;
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 子句中包含多个表。
-- 视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。
-- 因为我们刚刚修改的 productsum 视图不包括以上的限制条件，我们来尝试更新一下视图
USE shop;
UPDATE productsum
SET sale_price = '5000'
WHERE product_type = '办公用品';
-- 此时观察原表也可以发现数据也被更新了
-- 视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。
-- 注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表
-- 删除视图:
DROP VIEW <视图名1> [ , <视图名2> …]
-- example：删除刚才创建的 productsum 视图
DROP VIEW productsum;
-- 注意：需要有相应的权限才能成功删除。
-- ---------------------------------------------------------------
-- 子查询：
-- 子查询指一个查询语句嵌套在另一个查询语句内部的查询，可以基于一个表或者多个表。
-- 子查询和视图的关系：子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。
-- 嵌套子查询：随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
-- 标量子查询：
-- 需求：查询出销售单价高于平均销售单价的商品
USE shop;
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
-- 关联子查询：
-- 需求：选取出各商品种类中高于该商品种类的平均销售单价的商品
USE shop;
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);
关联子查询的执行逻辑和正常的SELECT语句执行逻辑完全不同
-- 1.先执行主查询
-- SELECT product _type , product_name, sale_price
-- FROM Product AS P1
-- 2.从主查询的product _type先取第一个值=‘衣服’，通过WHERE P1.product_type = P2.product_type传入子查询，子查询变成：
-- (SELECT AVG(sale_price)
-- FROM Product AS P2
-- WHERE P2.product_type = ‘衣服’
-- GROUP BY product_type);
-- 从子查询得到的结果AVG(sale_price)=2500，返回主查询：
-- SELECT product_type , product_name, sale_price
-- FROM Product AS P1
-- WHERE sale_price > 2500 AND product_type = ‘衣服’
-- 3.然后，product _type取第二个值，得到整个语句的第二结果，依次类推，把product _type全取值一遍，就得到了整个语句的结果集
函数
函数大致分为如下几类：
1.算术函数 （用来进行数值计算的函数）
2.字符串函数 （用来进行字符串操作的函数）
3.日期函数 （用来进行日期操作的函数）
4.转换函数 （用来转换数据类型和值的函数）
5.聚合函数 （用来进行数据聚合的函数）
-- ---------------------------------------------------------------
-- 1.算术函数
-- + - * /四则运算
-- ABS 求绝对值：
ABS( 数值 )
-- MOD 求余数
-- MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
-- 注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
MOD( 被除数，除数 )
-- ROUND 四舍五入
-- 注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
ROUND( 对象数值，保留小数的位数 )
-- ---------------------------------------------------------------
-- 2.字符串函数
-- CONCAT 拼接
CONCAT(str1, str2, str3)
-- LENGTH 字符串长度
-- 一个中文字符占三个字节，如 length('abc哈哈')=9
LENGTH( 字符串 )
-- LOWER 小写转换
LOWER ( 字符串 )
-- UPPER 大写转换。
UPPER ( 字符串 )
-- REPLACE 字符串的替换
REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
-- SUBSTRING 字符串的截取
-- SUBSTRING 函数可以截取出字符串中的一部分字符串。索引值起始为1。
SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
-- SUBSTRING_INDEX 字符串按索引截取
-- 该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
SUBSTRING_INDEX (原始字符串,分隔符,n)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-- +------------------------------------------+
-- | SUBSTRING_INDEX('www.mysql.com', '.', 2) |
-- +------------------------------------------+
-- | www.mysql |
-- +------------------------------------------+
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-- +-------------------------------------------+
-- | SUBSTRING_INDEX('www.mysql.com', '.', -2) |
-- +-------------------------------------------+
-- | mysql.com |
-- +-------------------------------------------+
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
-- +------------------------------------------+
-- | SUBSTRING_INDEX('www.mysql.com', '.', 1) |
-- +------------------------------------------+
-- | www |
-- +------------------------------------------+
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
-- +--------------------------------------------------------------------+
-- | SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
-- +--------------------------------------------------------------------+
-- | mysql |
-- +--------------------------------------------------------------------+
-- REPEAT 字符串按需重复多次
REPEAT(string, number)
SELECT REPEAT('加油！',3);
-- +-----------------------------+
-- | REPEAT('加油！',3) |
-- +-----------------------------+
-- | 加油！加油！加油！ |
-- +-----------------------------+
-- ---------------------------------------------------------------
-- 3.日期函数
-- CURRENT_DATE 获取当前日期
SELECT CURRENT_DATE;
-- +--------------+
-- | CURRENT_DATE |
-- +--------------+
-- | 2020-08-08 |
-- +--------------+
-- CURRENT_TIME 当前时间
SELECT CURRENT_TIME;
-- +--------------+
-- | CURRENT_TIME |
-- +--------------+
-- | 17:26:09 |
-- +--------------+
-- CURRENT_TIMESTAMP 当前日期和时间
SELECT CURRENT_TIMESTAMP;
-- +---------------------+
-- | CURRENT_TIMESTAMP |
-- +---------------------+
-- | 2020-08-08 17:27:07 |
-- +---------------------+
-- 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 |
-- +---------------------+------+-------+------+------+--------+--------+
-- ---------------------------------------------------------------
-- 4.转换函数
-- 在 SQL 中主要有两层意思：
-- 一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。
-- CAST 类型转换
CAST（转换前的值 AS 想要转换的数据类型）
-- 将字符串类型转换为数值类型
-- 注意:当要转换为整型时，需要指定为 SIGNED（有符号）(可以表示正数、负数和零) 或者 UNSIGNED（无符号）(只能表示非负数,即正数和零)
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- +---------+
-- | int_col |
-- +---------+
-- | 1 |
-- +---------+
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
-- +------------+
-- | date_col |
-- +------------+
-- | 2009-12-14 |
-- +------------+
-- COALESCE 将NULL转换为其他值
-- COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
COALESCE(数据1，数据2，数据3……)
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 |
-- +-------+-------------+------------+
谓词
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：
- LIKE
- BETWEEN
- IS NULL、IS NOT NULL
- IN
- EXISTS
-- ---------------------------------------------------------------
-- the LIKE operator
-- % ：any number of characters
-- _ : single character
-- 选择姓氏以b打头的（大写B也包括）
SELECT *
FROM customers
WHERE last_name LIKE 'b%'; -- %表示b后面可以有任意字符数
-- WHERE last_name LIKE '%b%' 姓氏中含有b的都被选中
-- 选择姓氏以y结尾的，且y前面有5个字符
SELECT *
FROM customers
WHERE last_name LIKE '_____y';
-- exercise 6
-- (1)get the customers whose addresses contain TRAIL or AVENUE
USE sql_store;
SELECT *
FROM customers
WHERE address LIKE '%TRAIL%' OR address LIKE '%AVENUE%' ;
-- (2)get the customers whose phone number end with 9
USE sql_store;
SELECT *
FROM customers
WHERE phone LIKE '%9';
-- ---------------------------------------------------------------
-- the BETWEEN operator
-- BETWEEN 的特点就是结果中会包含 1000 和 3000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。
USE sql_store;
SELECT *
FROM customers
WHERE points BETWEEN 1000 and 3000;
-- exercise 5
-- return customers born between 1/1/1990 and 1/1/2000
USE sql_store;
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01';
-- ---------------------------------------------------------------
-- the IS NULL operator ； 反义：IS NOT NULL
USE sql_store;
SELECT *
FROM customers
WHERE phone IS NULL;
-- exercise 8
-- get the orders that are not shipped
USE sql_store;
SELECT *
FROM orders
WHERE shipped_date IS NULL;
-- ---------------------------------------------------------------
-- the IN operator ：查询包含在集合中的 (OR的简便用法) ；反义：NOT IN
-- 在使用IN 和 NOT IN 时是无法选取出NULL数据的
USE sql_store;
SELECT *
FROM customers
WHERE state IN ('VA','FL','GA');
-- WHERE state NOT IN ('VA','FL','GA');
-- exercise 4
-- return the products with quantity in stock equal to 49,38,72
USE sql_store;
USE sql_store;
SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72) ;
-- 使用子查询作为IN谓词的参数
-- NOT IN 同样支持子查询作为参数，用法和 in 完全一样。
-- example:
-- 创建一张新表shopproduct显示出哪些商店销售哪些商品。
-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct (
shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id) );
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT; -- 提交事务
SELECT * FROM shopproduct;
-- +---------+-----------+------------+----------+
-- | shop_id | shop_name | product_id | quantity |
-- +---------+-----------+------------+----------+
-- | 000A | 东京 | 0001 | 30 |
-- | 000A | 东京 | 0002 | 50 |
-- | 000A | 东京 | 0003 | 15 |
-- | 000B | 名古屋 | 0002 | 30 |
-- | 000B | 名古屋 | 0003 | 120 |
-- | 000B | 名古屋 | 0004 | 20 |
-- | 000B | 名古屋 | 0006 | 10 |
-- | 000B | 名古屋 | 0007 | 40 |
-- | 000C | 大阪 | 0003 | 20 |
-- | 000C | 大阪 | 0004 | 50 |
-- | 000C | 大阪 | 0006 | 90 |
-- | 000C | 大阪 | 0007 | 70 |
-- | 000D | 福冈 | 0001 | 100 |
-- +---------+-----------+------------+----------+
-- 假设我们需要取出大阪在售商品的销售单价，该如何实现呢？
USE shop;
SELECT product_name,sale_price
FROM product
WHERE product_id IN
(SELECT product_id FROM shopproduct where shop_name='大阪')
-- ---------------------------------------------------------------
-- the EXISTS operator ：
-- 谓词的作用就是 “判断是否存在满足某种条件的记录”。
-- 如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
-- EXIST（存在）谓词的主语是“记录”。
-- EXIST 通常会使用关联子查询作为参数。
-- 就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。
-- 以 IN和子查询 中的示例，使用 EXIST 选取出大阪门店在售商品的销售单价。
SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT *
FROM shopproduct AS sp
WHERE sp.shop_id = '000C'
AND sp.product_id = p.product_id);
-- +--------------+------------+
-- | product_name | sale_price |
-- +--------------+------------+
-- | 运动T恤 | 4000 |
-- | 菜刀 | 3000 |
-- | 叉子 | 500 |
-- | 擦菜板 | 880 |
-- +--------------+------------+
case表达式
- CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
- CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能。
-- ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显式地写出 ELSE 子句。
-- 此外， CASE 表达式最后的“END”是不能省略的。
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
-- 应用场景1：根据不同分支得到不同列值
-- 实现如下结果：
-- A ：衣服
-- B ：办公用品
-- C ：厨房用具
USE shop;
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 ： 办公用品 |
-- +--------------+------------------+
-- 应用场景2：实现列方向上的聚合
-- 通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）
SELECT product_type,
SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type;
-- +--------------+-----------+
-- | product_type | sum_price |
-- +--------------+-----------+
-- | 衣服 | 5000 |
-- | 办公用品 | 600 |
-- | 厨房用具 | 11180 |
-- +--------------+-----------+
-- 假如要在列的方向上展示不同种类额聚合值，该如何写呢？
-- 聚合函数 + 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：实现行转列
-- 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
-- 当待转换列为文本时，可以使用MAX MIN等聚合函数
-- CASE WHEN 实现数字列 score 行转列
SELECT name,
SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
FROM score
GROUP BY name;
-- +------+---------+------+---------+
-- | name | chinese | math | english |
-- +------+---------+------+---------+
-- | 张三 | 93 | 88 | 91 |
-- | 李四 | 87 | 90 | 77 |
-- +------+---------+------+---------+
-- 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;
-- +------+---------+------+---------+
-- | name | chinese | math | english |
-- +------+---------+------+---------+
-- | 张三 | 语文 | 数学 | 外语 |
-- | 李四 | 语文 | 数学 | 外语 |
-- +------+---------+------+---------+
练习3.1
创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
- 条件 1：销售单价大于等于 1000 日元。
- 条件 2：登记日期是 2009 年 9 月 20 日。
- 条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
SELECT * FROM ViewPractice5_1;执行结果
product_name | sale_price | regist_date
--------------+------------+------------
T恤衫 | 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20解答：
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
向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');会报错：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02')' at line 1
因为在视图中插入数据时，原表（product）也会对应插入数据，而原表中product_id和product_type设置了为not null，因此无法插入
练习3.3
请根据如下结果编写 SELECT 语句，其中 sale_price_avg 列为全部商品的平均销售单价。
解答：
SELECT product_id,product_name,product_type,sale_price,
(select AVG(sale_price) FROM product) as sale_price_avg
FROM product
练习3.4
请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
提示：其中的关键是 sale_price_avg_type 列。与习题三不同，这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
解答：
SELECT product_id,product_name,product_type,sale_price,
(SELECT avg(sale_price)
FROM product b
where a.product_type=b.product_type
) as sale_price_avg_type
FROM product a 
练习3.5 判断题
四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？
解答：是的
练习3.6
对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
①
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);②
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);解答：
执行语句1，得到
执行语句2，得到
解释：IN是谓语，谓语无法与null进行比较，所以语句1的返回结果中不会有purchase_price为null的结果；对于语句2，not in 的参数中不能包含null，所以返回结果为空。
练习3.7
按照销售单价( sale_price )对练习 3.6 中的 product（商品）表中的商品进行如下分类。
- 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
- 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
- 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
执行结果
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2解答：
SELECT COUNT(case when sale_price<=1000 THEN sale_price ELSE NULL END) AS low_price,
COUNT(case when sale_price BETWEEN 1001 AND 3000 THEN sale_price ELSE NULL END) AS mid_price,
COUNT(case when sale_price>=3001 THEN sale_price ELSE NULL END) AS high_price
FROM product
1401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
