wonderful-sql:(2)基础查询与排序

举报
胡琦 发表于 2023/07/24 01:53:42 2023/07/24
【摘要】 这是笔者参与开源学习《wonderful-sql》的学习记录,希望通过分享自己的学习过程能够帮助到零基础的同学快速入门 SQL。

教程地址:https://linklearner.com/#/learn/detail/70

《SQL基础教程(第2版)》:https://www.ituring.com.cn/book/1880

《DataWhale SQL 基础教程》:https://fastly.jsdelivr.net/gh/datawhalechina/wonderful-sql@main/materials/SQL%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B-V2.0.pdf

wonderful-sql:https://github.com/datawhalechina/wonderful-sql

这是笔者参与开源学习《wonderful-sql》的学习记录,希望通过分享自己的学习过程能够帮助到零基础的同学快速入门 SQL。

本次学习借助开源学习社区 DataWhale,不仅有优秀的助教辅导,还有优秀的小伙伴一起学习,计划大概如下:

任务信息 截止时间 进度
07 月 20 日正式开始, 预计学习 16 天, 每天 2 小时 左右
Task01:环境搭建、初识数据库(2 天) 截止时间 07 月 22 日 03:00
Task02:基础查询与排序(2 天) 截止时间 07 月 24 日 03:00
Task03:复杂一点的查询(3 天) 截止时间 07 月 27 日 03:00 🗙
Task04:集合运算(3 天) 截止时间 07 月 30 日 03:00 🗙
Task05:SQL 高级处理(3 天) 截止时间 08 月 02 日 03:00 🗙
Task06:秋招秘籍 ABC(3 天) 截止时间 08 月 05 日 03:00 🗙

SQL(Structured Query Language)是一种用于管理和操作关系型数据库的标准语言。基础的SQL查询通常包括SELECTFROMWHEREORDER BY子句。SELECT用于选择要返回的列,FROM用于指定数据表,WHERE用于筛选行,而ORDER BY用于排序结果。

SELECT 语句

  • 基础查询

语法:


SElECT <列名>, …… FROM <表名>;

我们打开 navicat lite,连接数据库并按 F6 打开 Console 命令行终端,先创建 shop 数据库和 product 表,并插入数据:

CREATE DATABASE shop; -- 创建 shop 数据库
USE shop;
-- 注意: SQL 语句对空格敏感,字段前不能有空格
CREATE TABLE product  -- 创建 product 表
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY (product_id));
-- 插入数据
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

查询 product 所有数据:


SELECT * FROM product;

  • WHRER 加条件表达式
SElECT <列名>, …… 
FROM <表名>
WHERE <条件表达式>;

如查询所有厨房用具类型的product_nameproduct_type:

SELECT product_name, product_type
FROM product
WHERE product_type = '厨房用具';

  • AS 设定列的别名
    语法:
SELECT <列名> AS <别名>,
<列名> AS <别名>,
... -- 中文用双引号
FROM <表名>; -- 前一行不用加逗号

例如以下脚本及输出:

SELECT product_id AS id,
product_name AS name,
product_type AS "产品类别"  -- 中文用双引号
FROM product; -- 前一行不用加逗号

  • DISITNCT 过滤列中的重复数据

语法:

SELECT DISTINCT <列名>
  FROM <表名>;

例如过滤product_name重复数据:

SELECT DISTINCT product_name
FROM product;

值得注意的点:

  1. 星号(*)代表全部列的意思。
  2. SQL中可以随意使用换行符,不影响语句执行(但不可插入空行)。
  3. 设定汉语别名时需要使用双引号(")括起来。
  4. 在SELECT语句中使用DISTINCT可以过滤重复行。
  5. 注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- “和多行注释两种”/* */"。
  6. WHERE 条件表达式使用比较运算符时,<> 表示不相等.
  7. 字符串类型的数据原则上按照字典顺序进行排序,不能与数字的大小顺序混淆。
  8. 尤其注意新建表列名时不能用空格开头,不然后续查询很大的坑

聚合查询

SQL聚合查询是一种在数据库中执行聚合计算的方法,例如计算一组数据的平均值、总和、最小值、最大值等。聚合查询通常使用GROUP BY子句将数据分组,以便对每个组执行计算。

SQL聚合函数包括以下几种:

  • AVG函数:返回指定组中的平均值。
  • COUNT函数:返回组中的项目数量。
  • MAX函数:返回数据最大值。
  • MIN函数:返回数据最小值。
  • SUM函数:返回数据的和。
  • GROUPING函数:用于分组聚合计算。
  • CHECKSUM函数:计算数据的校验和。
  • STDEV函数:返回给定表达式中所有值的统计标准偏差。
  • STDEVP函数:返回给定表达式中所有值的填充的统计标准偏差。
  • VAR函数:返回给定表达式中所有值的统计方差。
  • VARP函数:返回给定表达式中所有值的填充的统计方差。
    这些聚合函数可以与SELECT语句的GROUP BY子句一起使用,以对数据进行分析、汇总和分组。

语法:

SELECT <列名1>,<聚合函数>,  ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

例如:

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

当想取多个分组时,WHERE已经不能满足了,我们可通过HAVING来实现,HAVING子句用于对分组进行过滤,可以使用常数、聚合函数和GROUP BY中指定的列名(聚合键)。

例如:
从"product"表中选择"product_type"列,并计算每个产品类型的数量。然后,使用"GROUP BY"子句按照产品类型进行分组。"HAVING"子句用于过滤只包含数量为3的产品类型的。因此,该查询将返回只有3个产品的类型的的结果集。

SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 3;

ORDER BY

ORDER BY是SQL语言中的排序子句,用于按照一个或多个列对查询结果进行排序。它的基本语法是:

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1> [ASC, DESC], <排序基准列2> [ASC, DESC], ……

例如将从"product"表中选择所有列,并按照销售价格(sale_price)降序排序

SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;

练习题

2.1 编写一条SQL语句,从 product(商品) 表中选取出“登记日期(regist_date)在2009年4月28日之后”的商品,查询结果要包含 product_name 和 regist_date 两列。

<summary>点击查看答案</summary>

答:

SELECT product_name, regist_date  
FROM product  
 WHERE regist_date > '2009-04-28';


2.2 请说出对product 表执行如下3条SELECT语句时的返回结果。

SELECT *
  FROM product
 WHERE purchase_price = NULL;

SELECT *
  FROM product
 WHERE purchase_price <> NULL;

SELECT *
  FROM product
 WHERE product_name > NULL;
<summary>点击查看答案</summary>

答: 全是 Empty set, 因为在 SQL中,NULL是一个特殊的值,表示缺失或未知的值。由于NULL值的特殊性质,不能直接与具体值进行比较。因此,使用比较运算符(如=)来比较NULL值是无效的。


2.3 SELECT语句能够从 product 表中取出“销售单价(sale_price)比进货单价(purchase_price)高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示:

product_name | sale_price | purchase_price 
-------------+------------+------------
T恤衫        |   1000    | 500
运动T|    4000    | 2800
高压锅       |    6800    | 5000
<summary>点击查看答案</summary> 答:

SELECT product_name,sale_price,purchase_price
FROM product
WHERE sale_price - purchase_price >= 500;

或者

SELECT product_name,sale_price,purchase_price
FROM product
WHERE product_type = '衣服' OR sale_price > 5000;


2.4 请写出一条SELECT语句,从 product 表中选取出满足“销售单价打九折之后利润高于 100 日元的办公用品和厨房用具”条件的记录。查询结果要包括 product_name列、product_type 列以及销售单价打九折之后的利润(别名设定为 profit)。
提示:销售单价打九折,可以通过 sale_price 列的值乘以0.9获得,利润可以通过该值减去 purchase_price 列的值获得。

<summary>点击查看答案</summary>

答:


SELECT product_name, product_type, (sale_price * 0.9 - purchase_price) AS profit  
FROM product  
WHERE product_type IN ('Office Supplies', 'Kitchen Utensils')  
AND (sale_price * 0.9 - purchase_price) > 100;
![](https://bbs-img.huaweicloud.com/blogs/img/20230724/1690135294510769396.png)

2.5 请指出下述SELECT语句中所有的语法错误。

SELECT product_id, SUM(product_name)
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';
<summary>点击查看答案</summary>

答:
WHERE子句只能指定记录(行)的条件,而不能用来指定组的条件;其次,在GROUP BY子句中,应该使用逗号分隔多个列名,这里应该加上"product_id"列,以正确地进行分组。

SELECT product_id, SUM(product_name)
  FROM product 
WHERE regist_date > '2009-09-01'
 GROUP BY product_type, product_id;


2.6 请编写一条SELECT语句,求出销售单价( sale_price 列)合计值大于进货单价( purchase_price 列)合计值1.5倍的商品种类。执行结果如下所示。


product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320
<summary>点击查看答案</summary>

答:

SELECT product_type, SUM(sale_price) AS total_sale_price, SUM(purchase_price) AS total_purchase_price  
 FROM product  
 WHERE product_type = '衣服' or product_type = '办公用品'  
 GROUP BY product_type  
 HAVING total_sale_price > total_purchase_price * 1.5;


2.7 此前我们曾经使用SELECT语句选取出了product(商品)表中的全部记录。当时我们使用了 ORDER BY 子句来指定排列顺序,但现在已经无法记起当时如何指定的了。请根据下列执行结果,思考 ORDER BY 子句的内容。


+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0003       | 运动T| 衣服         |       4000 |           2800 | NULL        |
| 0008       | 圆珠笔       | 办公用品     |        100 | NULL           | 2009-11-11  |
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 0006       | 叉子         | 厨房用具     |        500 | NULL           | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
<summary>点击查看答案</summary>

答:

SELECT * FROM product
ORDER BY regist_date IS NULL DESC, regist_date DESC

笔者水平有限,请多多指教!公众号:胡琦。

【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。