使用视图
视图是虚拟的表。看下面一个例子:
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
可以将整个查询包装成一个productcustomers的虚拟表:
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
视图不包含任何列或数据,它包含的是一个SQL查询。
- 使用视图
- 视图使用CREATE VIEW语句来创建
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
- 用DROP删除视图,语法为DROP VIEW viewname;
- 更新视图时,可以先用DROP在用CREATE,也可以直接用CREATE OR REPLACE VIEW。
创建一个名为productcustomers的视图
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
这个视图联结了三个表,以返回以订购了任意产品的所有客户列表。
用视图重新格式化检索出的数据
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
转化为视图:
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
执行SELECT * FROM vendorlocations;
用视图过滤不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
这里排除了没有电子邮件的那些行,使用SELECT * FROM customeremaillist;
查看。
使用视图与计算字段
检索特定订单的物品并计算总价格
SELECT prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
转化为视图:
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM orderitems;
然后可以使用SELECT * FROM vendorlocations;
查询:
修改视图结构
基本语法:
ALTER VIEW <视图名> AS <SELECT语句>
更新视图内容
UPDATE productsum
SET sale_price = '5000'
WHERE product_type = '办公用品';
更新视图内容对应的表中的内容也会更新,但是需要注意的是,只有表中映射到视图中的记录才会被更新,也就是只有从视图中可以看到的记录才会更新。基于这个原因,一般不使用视图来对表中的数据更新。
使用子查询
- 利用子查询过滤
使用SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
,在查询语句中嵌套了子查询,表示查询具有TNT2订单的客户ID。
检索到这些客户的id后就可以检索出客户的具体信息了, SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
当嵌套语句比较多时可以适当采用缩进
- 作为计算字段使用子查询
对每个客户的订单数做计算,SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
orders.cust_id = customers.cust_id
完全限定列名。
标量子查询
标量子查询就是查询结果只有一行一列的查询。
看下面这个例子:
SELECT
product_id,
product_name,
sale_price
FROM
product
WHERE
sale_price > ( SELECT AVG( sale_price ) FROM product );
这条语句首先后半部分查询出product表中的平均售价,前面的sql语句在根据WHERE条件挑选出合适的商品。 由于标量子查询的特性,导致标量子查询不仅仅局限于 WHERE 子句中,通常任何可以使用单一值的位置都可以使用。也就是说, 能够使用常数或者列名的地方,无论是 SELECT 子句、GROUP BY 子句、HAVING 子句,还是 ORDER BY 子句,几乎所有的地方都可以使用。
联结表
- 创建联结
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
用来将供应商表和产品表的内容联结。
没有联结条件将会返回笛卡尔积结果,如SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;
-
内部联结
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
这句返回与上面相同的结果,这里使用了INNER JOIN指定另一个表,使用ON传递条件。 -
联结多个表
SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;
需要注意的是,联结多张表对于性能消耗非常大。
关联子查询
关联子查询是在子查询中使用标志的方法将内层查询与外层查询关联起来过滤一些数据。
选取出各商品种类中高于该商品种类的平均销售单价的商品可以这样写:
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 );
关联子查询的执行过程:
- 首先执行不带WHERE的主查询
- 根据主查询结果匹配product_type,获取子查询结果
- 将子查询结果再与主查询结合执行完整的SQL语句
作业
3.1
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
无法插入数据(原因?)
3.3
SELECT
product_id,
product_name,
product_type,
sale_price,
( SELECT avg( sale_price ) FROM product ) AS sale_price_all
FROM
product;
这里如果直接使用avg(sale_price)的话只能查出一条数据,因此需要标量子查询。
3.4
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 ) AS avg_sale_price FROM product p2 WHERE p2.product_type = p1.product_type ) AS avg_sale_price
FROM
product p1;
各种函数
算术函数
- ABS–绝对值
- MOD–求余数
- ROUND–四舍五入
字符串函数
常用的文本处理函数。
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换为小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX值 |
SubString() | 返回子串的字符 |
Upper() | 将转换为大写 |
其中Soundex返回所有发音类似的结果。如SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');
返回发音与Y Lie相似的结果。
日期函数
- CURRENT_DATE – 获取当前日期
- CURRENT_TIMESTAMP – 当前日期和时间
- EXTRACT – 截取日期元素
转换函数
- CAST – 类型转换
- COALESCE – 将NULL转换为其他值
谓词
谓词有以下几个:
- LIKE
- BETWEEN
- IS NULL、IS NOT NULL
- IN
- EXISTS
LIKE-- 用于字符串的部分一致查询
%可以匹配任意个字符,因此我们要搜索出最前面三个字母为ddd的记录可以这样写:
SELECT
*
FROM
samplelike
WHERE
strcol LIKE 'ddd%';
%的位置比较随意,可以在字符串中任意位置。
另外,_可以匹配任意一个字符,比如查询前三个为abc的字符:
SELECT
*
FROM
samplelike
WHERE
strcol LIKE 'abc__';
BETWEEN谓词 – 用于范围查询
选取销售单价为100~ 1000元的商品:
SELECT
product_name,
sale_price
FROM
product
WHERE
sale_price BETWEEN 100
AND 1000;
between包含的是闭区间
IS NULL、 IS NOT NULL – 用于判断是否为NULL
IN谓词 – OR的简便用法
查询purchase_price 为320, 500和5000的记录。
SELECT
product_name,
purchase_price
FROM
product
WHERE
purchase_price IN ( 320, 500, 5000 );
反之,可以使用not in
另外,in也可以用在子查询中。
EXIST 谓词
使用 EXIST 选取出000c门店在售商品的销售单价:
SELECT
product_name,
sale_price
FROM
product AS p
WHERE
EXISTS ( SELECT * FROM shop_product AS sp WHERE sp.shop_id = '000C' AND sp.product_id = p.product_id );
CASE表达式
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
case语句判断每一个when后面的表达式是否为真,真的话就执行then后面的表达式。
- 根据不同分支得到不同列值
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;
作业
3.5
运算或者函数中含有 NULL 时,结果全都会变为NULL ?
不会。
3.6
1、会查询出除了purchase_price为null以外的所有purchase_price不是500, 2800, 5000的记录
2、null不能用于比较,所以查询出结果都是空。
sql查询in包含null的情况下,查询结果为空
3.7
SELECT
count( CASE WHEN sale_price <= 1000 THEN product_id ELSE NULL END ) AS low_price,
count( CASE WHEN sale_price >= 1001 AND sale_price <= 3000 THEN product_id ELSE NULL END ) AS mid_price,
count( CASE WHEN sale_price >= 3001 THEN product_id ELSE NULL END ) AS high_price
FROM
product;
参考资料:
[1] https://github.com/datawhalechina/team-learning-sql/blob/main/Task03%EF%BC%9A%E5%A4%8D%E6%9D%82%E4%B8%80%E7%82%B9%E7%9A%84%E6%9F%A5%E8%AF%A2.md
[2] 《MySQL必知必会》