DataWhale SQL组队Day3-复杂一点的查询

最新推荐文章于 2022-10-11 19:34:44 发布
最新推荐文章于 2022-10-11 19:34:44 发布
阅读量327 收藏
点赞数
分类专栏： DA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Shirley_L77/article/details/111384739
版权

文章目录

1 视图

1.1 知识点总结

在这里插入图片描述

1.2 语句

--创建视图
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
--修改视图
ALTER VIEW <视图名> AS <SELECT语句>
--更新视图
UPDATE 视图名
   SET 更新后的值
 WHERE 选择更新的列;
 --只能修改透过窗口能看到的内容，创建视图时尽量使用限制不允许通过视图来修改表。
 
 --删除视图
 DROP VIEW 视图名;

2 子查询

2.1 知识点总结

在这里插入图片描述

2 语句

--嵌套子查询 （product例子）
SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;
--标量子查询
SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);
 --或者
 SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);
 --关联子查询
 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);

PS:对于一个SQL语句来说都不应该设计的层数非常深且特别复杂，不仅可读性差而且执行效率也难以保证，所以尽量有简洁的语句来完成需要的功能。

关联查询那块还是有点晕，先存下教程关联查询执行过程

3 练习题-第一部分

3.1

创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
在这里插入图片描述

--创建视图
CREATE VIEW ViewPractice5_1
AS
SELECT product_name, sale_price,regist_date
  FROM product
WHERE regist_date = '2009-9-20'AND sale_price >= 1000;
--执行题中的语句
SELECT *
  FROM ViewPractice5_1

3.2

向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？在这里插入图片描述
会报错。因为原表中的product_id 不允许为空。
在这里插入图片描述

3.3

请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。在这里插入图片描述

SELECT product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;

3.4

请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。
在这里插入图片描述

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 sale_price_all
FROM product AS P1

4 各种各样的函数

4.1 知识点总结

在这里插入图片描述

4.2 语句

4.2.1 数值函数

--ABS绝对值（参数为NULL时，返回值也是NULL）
ABS（数值）
--MOD余数，只有整数列求余
--主流 DBMS 都支持 MOD 函数，只有SQL Server 不支持，其使用%符号求余。
MOD(被除数，除数）
--ROUND四舍五入
ROUND（对象数值）

4.2.2 字符串函数

--CONCAT 拼接
CONCAT(str1, str2, str3)
--LENGTH字符串长度
LENGTH( 字符串 )
--LOWER 小写转换，只能针对英文字母使用
LOWER（字符串）
--REPLACE字符串替换
REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
--SUBSTRING 字符串的截取
SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
--截取的起始位置从字符串最左侧开始计算，索引值起始为1。

4.2.3 日期函数

--CURRENT_DATE获取当前日期
SELECT CURRENT_DATE;
--CURRENT_TIME – 当前时间
SELECT CURRENT_TIME;
--CURRENT_TIMESTAMP – 当前日期和时间
SELECT CURRENT_TIMESTAMP;

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

4.2.4 转换函数

--CAST 类型转换
CAST（转换前的值 AS 想要转换的数据类型）
--COALESCE将NULL转换为其他值
COALESCE(数据1，数据2，数据3……)

5 谓词

5.1知识点总结

在这里插入图片描述

5.2 语句

--like
--前方一致:作为查询条件的字符串与查询对象字符串起始部分相同
SELECT *
FROM 表名
WHERE strcol LIKE '一致的字符串部分%';
--中间一致：查询对象字符串中含有作为查询条件的字符串
--字符串出现在对象字符串的最后或中间都可以
SELECT *
FROM 表名
WHERE strcol LIKE '%一致的字符串部分%';
--后方一致：作为查询条件的字符串与查询对象字符串的末尾部分相同。
SELECT *
FROM 表名
WHERE strcol LIKE '%一致的字符串部分';
--_下划线匹配任意 1 个字符
--使用 _（下划线）代替 %，它代表“任意 1 个字符”。
SELECT *
FROM 表名
WHERE strcol LIKE 'abc__';

--BETWEEN（闭区间），开区间只能用大于/小于
SELECT 列1, 列2
FROM product
WHERE 列 BETWEEN 条件1 AND 条件2;
--NULL/NOT NULL
SELECT 列1, 列2
FROM 表名
WHERE 列 IS NULL/NOT NULL;

--IN（OR的简便用法）
SELECT 列1, 列2
FROM 表名
WHERE列 IN (条件1, 条件2, 条件3);（满足/不满足其一即可）
--不满足改成NOT IN即可

--使用子查询作为IN谓词的参数
-- DDL ：创建一张新表shopproduct显示出哪些商店销售的商品
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;
--假设我么需要取出大阪在售商品的销售单价
--第一步，取出大阪门店的在售商品 `product_id ;
SELECT product_id
FROM shopproduct
WHERE shop_id = '000C';
--第二步，取出大阪门店在售商品的销售单价 `sale_price
SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
                       WHERE shop_id = '000C');
--NOT IN和子查询
-- NOT IN 使用子查询作为参数，取出未在大阪门店销售的商品的销售单价
SELECT product_name, sale_price
  FROM product
 WHERE product_id NOT IN (SELECT product_id
                            FROM shopproduct
                           WHERE shop_id = '000A');
                       

--EXISTS:存在这样的记录就返回真（TRUE），不存在就返回假（FALSE）
--上面的表，用 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 是只有 1 个参数的谓词，左侧没有参数，右侧有一个，通常是子查询
--EXIST 是只有 1 个参数的谓词
--EXIST 只关心记录是否存在，返回哪些列都没有关系
 EXIST 的子查询中用 SELECT * 就行
--NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）

6 CASE表达式

6.1知识点总结

在这里插入图片描述

6.2 语句

ASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END  
--ELSE 子句可以省略，此时默认为 ELSE NULL
-- CASE 表达式最后的“END”不能省略

--应用1:根据不同分支得到不同结果
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 product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;  
 --列方向不同种类聚合值
 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等聚合函数；
--当待转换列为文本时，可以使用MAX MIN等聚合函数

7 练习题-第二部分

7.1
运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

是的

7.2
对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
在这里插入图片描述
运行结果1：返回purchase_price结果不为500，2800，5000，且不为NULL 的行。
在这里插入图片描述
运行结果2：返回为空。就是上题提到的运算或函数中含有 NULL 时，结果全都会变为NULL 。筛选含/不含NULL的项，用IS NULL或IS NOT NULL.

在这里插入图片描述

7.3
按照销售单价（ sale_price）对 product（商品）表中的商品进行如下分类。

  • 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）

  • 中档商品：销售单价在1001日元以上3000日元以下（菜刀）

  • 高档商品：销售单价在3001日元以上（运动T恤、高压锅）

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
执行结果
在这里插入图片描述

SELECT
SUM(CASE WHEN sale_price BETWEEN 0 AND 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001  AND 3000 THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price>3000 THEN 1 ELSE 0 END) AS high_price
  FROM product;
Smile_L77
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
阿里云天池龙珠计划SQL练习答案
ken_131的博客
05-17 432
sql练习 本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql； 1.1 建表 create table Addressbook ( regist_no int not null, name varchar(128) not null, address varchar(256) not null, tel_no char(10), mail_address char(20), primary
MySQL-learning03 复杂一点的查询
m0_50918678的博客
12-20 163
视图 练习题 3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。 条件 1：销售单价大于等于 1000 日元。 条件 2：登记日期是 2009 年 9 月 20 日。 条件 3：包含商品名称、销售单价和登记日期三列。 CREATE VIEW ViewPractice5_1 AS SELECT product_name,sale_price,regist_data FROM prod
datawhale-sql打卡之Task 3:复杂一点的查询
fff2zrx的博客
12-20 304
视图 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。 图片来源：《sql基础教程第2版》 视图的优点 可以将频繁使用的SELECT语句保存以提高效率。 可以使用户看到的数据更加清晰。 可以不对外公开数据表全部字段，增强数据的保密性。 可以降低数据的冗余 创建视图 CREATE VIEW productsum (product_type, cnt_prod
SQL学习：第三天
Monsters2019的博客
12-20 266
文章目录一、视图1.什么是视图2.视图与表的区别3.为什么会存在视图4 如何创建视图5.如何修改视图结构6.如何更新视图内容7 如何删除视图二、子查询1.什么是子查询2.子查询和视图的关系3.嵌套子查询4.标量子查询5.标量子查询有什么用6 关联子查询总结 一、视图 1.什么是视图 我认为视图是一张虚拟的表，不同于数据表。创建视图时，我们是调用数据表中的数据进行一个新的组合排列，进行一些分析。同时对视图进行操作时并不会影响数据表。相当时生成了一张虚拟表 2.视图与表的区别 《sql基础教程**第2版》用一句
DataWhale SQL组队Day2-基础查询与排序
Shirley_L77的博客
12-16 278
1 SELECT语句基础 1.1 SELECT语句 从表中只选择必要的数据。通过该语句查询并选取出必要数据的过程称为匹配查询或查询（query）。 基本语句： SELECT <列名>， FROM <表名>； 1.2 WHERE 从表中选出符合条件的数据。 基本语句： SELECT <列名>，…… FROM <表名> WHERE <条件表达式>； 1.3 相关法则 星号（*）代表全部列； SQL中可以随意使用换行符，但不可插入
DataWhale SQL组队 Day1-数据库初识
Shirley_L77的博客
12-14 1118
1 数据库基础 相关概念 数据库（Database,DB):将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。 数据库管理系统（Database Management System，DBMS):用来管理数据库的计算机系统。 DBMS 2 SQL 2.1 SQL语句 数据库中存储的表结构类似于excel中的行和列。 在数据库中，行称为记录，相当于一条记录；列称为字段，代表了表中存储的数据项目。行和列交汇的地方称为单元格。 DDL：创建/删除数据库及数据库中的对象 2.2 SQL
Datawhale SQL组队Day0-Win10 MySQL8.0.21安装
Shirley_L77的博客
12-14 449
版本8.0.21 遇到的问题：路径选择mysql the selected path already exist 卸载不干净：重装MYSQL的时候发现要输入current root password 参考：https://blog.csdn.net/baidu_38290612/article/details/79782350
DataWhale SQL 组队Day5—窗口函数
Shirley_L77的博客
12-23 109
1 知识点梳理 1.1 窗口函数 窗口函数的基本语法： --[ ] 中的内容可以省略 <窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>) 1.2 窗口函数的的应用 1.2.1 基本语法 --PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行 <窗口函数> OVER (ORDER BY &l
Datawhale组队学习之MySQL-task4
一步一个脚印，安静和谐地存在
08-15 227
Datawhale组队学习之MySQL-task4 4.1 MySQL 实战 - 复杂项目**#作业#** 项目十六 分数排名 （难度：中等） 依然是昨天的分数表，实现排名功能，但是排名需要是非连续的，如下： ±------±-----+ | Score | Rank | ±------±-----+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 3 ...
DataWhale组队学习-SQL秋招秘籍ABC.zip
09-27
6.7 练习七：查询回答率最高的问题 （难度：中等） 6.8 练习八：各部门前3高工资的员工（难度：中等） 6.9 练习九：平面上最近距离 (难度: 困难） 6.10 练习十：行程和用户（难度：困难） ch07：秋招秘籍 B 7.1...
实验四 SQL进行复杂查询-学生.docx
最新发布
05-24
实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂...
team-learning-sql:主要存储Datawhale组队学习中“SQL”方向的资料
05-16
SQL基础教程学习笔记（team-learning-sql） SQL 在诸多工作中应用广泛，数据分析、开发、测试、维护、产品经理都有可能会用到SQL，而在大学里曾经系统学习SQL的人比较少， 这就要求我们在工作中来学习SQL。然而，...
SQL-Interview-questions.rar_sql 联合查询
09-19
一些经典的SQL复杂查询、联合查询、交叉查询等操作
OracleSQL实例-复杂查询语句的使用实例.pdf
07-11
OracleSQL实例-复杂查询语句的使用实例.pdf 学习资料 复习资料 教学资源
EXCEL 学习笔记
Shirley_L77的博客
10-11 1201
EXCEL知识点笔记
Python与Excel
Shirley_L77的博客
11-19 628
xlsx格式通过 openpyxl 模块打开； xls格式通过 xlwt 模块写， xlrd 模块读取。 #xls #多行内容显现 from IPython.core.interactiveshell import InteractiveShell InteractiveShell.ast_node_interactivity = "all" import * 和 from…import… 的区别 import 导入一个模块，相当于导入的是一个文件夹，相对路径。 from…import… 导入了一个.
DataWhale组队SQL组队学习连接汇总
Shirley_L77的博客
12-17 154
DAY2:https://blog.csdn.net/Shirley_L77/article/details/111240425 DAY1:https://blog.csdn.net/Shirley_L77/article/details/111178026 DAY0:https://blog.csdn.net/Shirley_L77/article/details/111148054
DataWhale组队 SQL—Day4集合运算
Shirley_L77的博客
12-22 150
1 表的加减法 1.1 知识点梳理 ** UNION *UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。 UNION用法和OR很像，但有些情况必须用到它： 将两个不同的表中的结果合并在一起； -对同一张表，有时会出于查询效率方面的因素使用 UNION. UNION ALL：包含重复行的集合运算 bag 模型与 set 模型 隐式类型转换 1.2 练习题 1.1找出 product 和 product2 中售价高于
SQL数据库语句学习.doc
06-03
SQL数据库语句学习

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值