SQL Task3

一、视图

创建视图

创建视图不能用order by；多表合并这个功能很好用，可以把多个表中同一id集合在一起

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)

SELECT product_type, sale_price, shop_name

FROM product,

shop_product

WHERE product.product_id = shop_product.product_id;

修改视图结构

ALTER VIEW productSum

SELECT product_type, sale_price

FROM Product

WHERE regist_date > '2009-09-11';

更新视图内容（不建议通过修改视图修改表）

视图是原表的派生，所以当底层表格必须发生变化的时候才可以。

UPDATE productsum

SET sale_price = '5000'

WHERE product_type = '办公用品';

修改视图的时候原表也在跟着修改

删除视图

DROP VIEW productSum;

二、子查询

标量子查询

标量子查询返回一个值，比如下图，需要返回值为“004”

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

SELECT product_id, product_name, sale_price

FROM product

WHERE sale_price > (SELECT AVG(sale_price) FROM product);

关联子查询

三、各种各样的函数

算术函数（用来进行数值计算的函数）

字符串函数（用来进行字符串操作的函数）

日期函数（用来进行日期操作的函数）

转换函数（用来转换数据类型和值的函数）

聚合函数（用来进行数据聚合的函数）

算术函数

ABS -- 绝对值

MOD -- 求余数

ROUND -- 四舍五入

字符串函数

CONCAT -- 拼接

LENGTH -- 字符串长度

LOWER -- 小写转换

REPLACE -- 字符串的替换

SUBSTRING -- 字符串的截取

日期函数

CURRENT_DATE -- 获取当前日期

CURRENT_TIME -- 当前时间

转换函数

在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换

CAST -- 类型转换

COALESCE -- 将NULL转换为其他值

四、谓词

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

谓词主要有以下几个：

LIKE

前方一致

SELECT *FROM samplelike

WHERE strcol LIKE 'ddd%'; 比如选择出dddabce“

中间一致

SELECT *FROM samplelike

WHERE strcol LIKE '%ddd%'; 比如选择出‘12ddd3k’

后方一致

SELECT *FROM samplelike

WHERE strcol LIKE '%ddd';

使用下划线_代替%，任意一个字符

SELECT *FROM samplelike

WHERE strcol LIKE 'abc__';

BETWEEN（范围查询）

-- 选取销售单价为100～ 1000元的商品

SELECT product_name, sale_price

FROM product

WHERE sale_price BETWEEN 100 AND 1000;

开区间：

SELECT product_name, sale_price

FROM product

WHERE sale_price > 100AND sale_price < 1000;

IS NULL、IS NOT NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL

SELECT product_name, purchase_price

FROM product

WHERE purchase_price IS NULL;

SELECT product_name, purchase_price

FROM product

WHERE purchase_price IS NOT NULL;

多个查询条件取并集时可以选择使用or语句

SELECT product_name, purchase_price

FROM product

WHERE purchase_price （NOT） IN (320, 500, 5000);

分步使用，先查到shop_id是000C，然后把这些店铺的销售单价取出

SELECT product_name, sale_price

FROM product

WHERE product_id （NOT） IN (SELECT product_id

FROM shopproduct

WHERE shop_id = '000C');

EXISTS

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

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

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

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 '000C'，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。

因此，使用下面的查询语句，查询结果也不会发生变化。（1代表任意一列）

SELECT product_name, sale_price

FROM product AS p

WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数

FROM shopproduct AS sp

WHERE sp.shop_id = '000C'

AND sp.product_id = p.product_id);

NOT EXIST--不存在值的用法

SELECT product_name, sale_price

FROM product AS p

WHERE NOT EXISTS (SELECT *

FROM shopproduct AS sp

WHERE sp.shop_id = '000A'

AND sp.product_id = p.product_id);

五、case

根据不同分支得到不同列值

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;

实现列方向上的聚合

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 AVG MAX MIN的函数】

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;

文本——行与列的转换【用MAX MIN的函数】

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;

SQL Task3

推荐阅读更多精彩内容