简单学SQL——select 查(二)

最新推荐文章于 2024-04-28 20:30:00 发布

Kolo_Tong

最新推荐文章于 2024-04-28 20:30:00 发布

阅读量118

点赞数 1

文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/qq_32531505/article/details/120392902

版权

1. 视图

视图是什么，在我看来视图相当于给文件创建了一个快捷方式，但是对打开的内容进行了限制。视图就是对表创建的一个或者多个快捷方式。
视图，从词本意理解，就是创建一个窗口去看表的内容，内容可以是局部的，也可以是全局的，视实际使用而定。如图所示，透过（通过）视窗改动数据，就是改动表的数据。

在这里插入图片描述

1.1 视图的优势

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

1.2 视图相关语法

创建视图

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

视图对效率的提高体现在对于经常查询的内容通过视窗的方式查询，代码量大大减少，举例如下：

create view ViewPractice5_1(product_name,sale_price,regist_date) 
as 
select product_name,sale_price,regist_date
from product
where regist_date = '2009-09-20' and sale_price>=1000;

select * from ViewPractice5_1;-- 对于经常查询的内容通过视窗的方式查询，代码量大大减少
-- 等同于
select product_name,sale_price,regist_date
from product
where regist_date = '2009-09-20' and sale_price>=1000;

修改视图结构

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

更新视图中的数据

UPDATE 视窗名
  SET <列名> = <表达式> [, <列名2>=<表达式2>...];  
  WHERE <条件>;  -- 可选，非常重要。

删除视窗

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

2.子查询与关联查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。
举例，查询全部商品的平均销售单价

select 
product_id,product_name, product_type ,sale_price,
(select avg(sale_price) from product) as sale_price_all
from product;

子句可出现在不同地方，举例，在where中是子句查询

select product_id, product_name, sale_price
  from product
 where sale_price > (select avg(sale_price) from product);

2.1 关联查询

关联查询中的关键是怎么在查询与子查询中建立联系———— where 条件是关键
举例，查询高于均价的商品类型和商品名称

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 p1.product_type = p2.product_type，这句将查询与子查询联系在一起了

还有一个需要牢记的是，查询的执行顺序：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

例如，查询商品信息中包含同种商品类型中的平均销售价，如下内容
在这里插入图片描述
查询语句：

select product_id,product_name,product_type,sale_price,
 (select avg(sale_price) 
  from product as p2 
  where p1.product_type=p2.product_type) as avg_sale_price  -- 子查询
from product as p1 ;

从书写角度看，会误以为先执行的是子查询select，而且p2比p1先出现，实际执行是 from p1 select … 再去执行from p2 select

2.2 如何写好关联查询

思想：先拆分后合并。先分开写查询语句，再找到共有的属性，以此为桥梁连接合并不同查询。

第一步，分别查
查商品信息

select 
product_id,
product_name,
product_type,
sale_price
from product;

在这里插入图片描述
查同种类商品均价

select product_type,avg(sale_price)  from product group by product_type;

在这里插入图片描述
合并，使用where 条件句将主查询p1和子查询p2连接

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 ;

由于主表数据顺序从0001中的product_type='衣服’开始往下执行，先判断 p1.product_type(‘衣服’)= p2.product_type(‘衣服’)，成功则将avg_sale_price对应的值附上。也正是因为这样，两个表product_type的对比，可以确定product_type，即可删除子查询中的 group by product_type。

完整语句为

select product_id,product_name,product_type,sale_price,
 (select avg(sale_price) 
  from product as p2 
  where p1.product_type=p2.product_type) as avg_sale_price  -- 子查询
from product as p1 ;

4. 谓词

like
between
is null、is not null
in
exists

4.1 like

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
1 row in set (0.00 sec)

4.2 between

between 所去范围是闭区间。

-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;-- 代替 WHERE sale_price >= 100 AND sale_price <= 100


+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| T恤          |       1000 |
| 打孔器       |        500 |
| 叉子         |        500 |
| 擦菜板       |        880 |
| 圆珠笔       |        100 |
+--------------+------------+
5 rows in set (0.00 sec)

4.3 in

谓词in 的好处是简化 where 条件中逻辑词 or 的使用

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);-- 代替 WHERE purchase_price = 320
											 -- OR purchase_price = 500
					 						 -- OR purchase_price = 5000;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤          |            500 |
| 打孔器       |            320 |
| 高压锅       |           5000 |
+--------------+----------------+
3 rows in set (0.00 sec)

4.4 exists

谓词 exists 在where条件中只作是否存在的判断，不作内容的查询。

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);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+

5. case 表达式

书写语法

case when <求值表达式> then <表达式>
     when <求值表达式> then <表达式>
     .
     .
     .
else <表达式>
end

case 表达式有一个好处是将内容转成列项表达

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;  
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服         |      5000 |
| 办公用品      |       600 |
| 厨房用具      |     11180 |
+--------------+-----------+
3 rows in set (0.00 sec)

转成列项表达

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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 |
+-------------------+-------------------+------------------+
1 row in set (0.00 sec)

case表达式还可以与聚合函数搭配一起使用。
举例，将价位分成三挡，第一档0-1000，第二档1001-3000，第三档 3001以上。

先来一种错误做法，先找到子查询，在组合成新的查询，结果便是记录重复

select (select count(product_name) from product where sale_price <= 1000) as low_price,
(select count(product_name) from product where sale_price between 1001 and 3000) as mid_price,
(select count(product_name) from product where sale_price > 3000) as h_price
from product;

在这里插入图片描述
正确的做法，如下

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 h_price 
from product;

在这里插入图片描述

资料：
1.Datawhale——team-learning-sql

Kolo_Tong

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
简单学SQL——select 查(二)

1. 视图视图是什么，在我看来视图相当于给文件创建了一个快捷方式，但是对打开的内容进行了限制。视图就是对表创建的一个或者多个快捷方式。视图，从词本意理解，就是创建一个窗口去看表的内容，内容可以是局部的，也可以是全局的，视实际使用而定。如图所示，透过（通过）视窗改动数据，就是改动表的数据。1.1 视图的优势通过定义视图可以将频繁使用的SELECT语句保存以提高效率通过定义视图可以使用户看到的数据更加清晰通过定义视图可以不对外公开数据表全部字段，增强数据的保密性通过定义视图可以降低数据的冗
复制链接

扫一扫