wonderful-sql:(2)基础查询与排序
《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查询通常包括SELECT
、FROM
、WHERE
和ORDER 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_name
和product_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;
值得注意的点:
- 星号(*)代表全部列的意思。
- SQL中可以随意使用换行符,不影响语句执行(但不可插入空行)。
- 设定汉语别名时需要使用双引号(")括起来。
- 在SELECT语句中使用DISTINCT可以过滤重复行。
- 注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- “和多行注释两种”/* */"。
- WHERE 条件表达式使用比较运算符时,
<>
表示不相等. - 字符串类型的数据原则上按照字典顺序进行排序,不能与数字的大小顺序混淆。
- 尤其注意新建表列名时不能用空格开头,不然后续查询很大的坑
聚合查询
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 两列。
答:
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;
答: 全是 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
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 列的值获得。
答:
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;
2.5 请指出下述SELECT语句中所有的语法错误。
SELECT product_id, SUM(product_name)
--本SELECT语句中存在错误。
FROM product
GROUP BY product_type
WHERE regist_date > '2009-09-01';
答:
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
答:
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 |
答:
SELECT * FROM product
ORDER BY regist_date IS NULL DESC, regist_date DESC
笔者水平有限,请多多指教!公众号:胡琦。
- 点赞
- 收藏
- 关注作者
评论(0)