SQL查询示例
数据截取自《SQL基础教程（第2版）》
数据准备
-- 创建数据表
CREATE TABLE 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');
1. 基础查询
1.1 查询所有记录
sqlite> select * from Product;
product_id product_name product_type sale_price purchase_price regist_date
---------- ------------ ------------ ---------- -------------- -----------
0001 T恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动T恤 衣服 4000 2800 NULL
0004 菜刀 厨房用具 3000 2800 2009-09-20
0005 高压锅 厨房用具 6800 5000 2009-01-15
0006 叉子 厨房用具 500 NULL 2009-09-20
0007 擦菜板 厨房用具 880 790 2008-04-28
0008 圆珠笔 办公用品 100 NULL 2009-11-11
1.2 查询某几列的记录
sqlite> select * from Product;
product_id product_name product_type sale_price purchase_price regist_date
---------- ------------ ------------ ---------- -------------- -----------
0001 T恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动T恤 衣服 4000 2800 NULL
0004 菜刀 厨房用具 3000 2800 2009-09-20
0005 高压锅 厨房用具 6800 5000 2009-01-15
0006 叉子 厨房用具 500 NULL 2009-09-20
0007 擦菜板 厨房用具 880 790 2008-04-28
0008 圆珠笔 办公用品 100 NULL 2009-11-11
1.3 设置别名
sqlite> select product_id, product_name as "产品名称", product_type as "产品类型" from Product;
product_id 产品名称 产品类型
---------- ---------- ----------
0001 T恤衫 衣服
0002 打孔器 办公用品
0003 运动T恤 衣服
0004 菜刀 厨房用具
0005 高压锅 厨房用具
0006 叉子 厨房用具
0007 擦菜板 厨房用具
0008 圆珠笔 办公用品
1.4 查询常数
sqlite> select product_name as "产品名称", "常数字段", "2022-08-18" as "查询日期" from Product;
产品名称 "常数字段" 查询日期
---------- ---------- ----------
T恤衫 常数字段 2022-08-18
打孔器 常数字段 2022-08-18
运动T恤 常数字段 2022-08-18
菜刀 常数字段 2022-08-18
高压锅 常数字段 2022-08-18
叉子 常数字段 2022-08-18
擦菜板 常数字段 2022-08-18
圆珠笔 常数字段 2022-08-18
1.5 从结果中删除重复数
sqlite> select distinct product_type from Product;
product_type
------------
衣服
办公用品
厨房用具
sqlite> select distinct product_type, sale_price from Product;
product_type sale_price
------------ ----------
衣服 1000
办公用品 500
衣服 4000
厨房用具 3000
厨房用具 6800
厨房用具 500
厨房用具 880
办公用品 100
1.6 使用 Where 筛选数据
sqlite> select product_name, product_type from Product where product_type = "衣服";
product_name product_type
------------ ------------
T恤衫 衣服
运动T恤 衣服
算术运算符和逻辑运算符
2.1 在查询结果中使用算术运算符
sqlite> SELECT product_name, sale_price, sale_price * 2 AS "sale_price_x2" FROM Product;
product_name sale_price sale_price_x2
------------ ---------- -------------
T恤衫 1000 2000
打孔器 500 1000
运动T恤 4000 8000
菜刀 3000 6000
高压锅 6800 13600
叉子 500 1000
擦菜板 880 1760
圆珠笔 100 200
2.2 在过滤条件中使用比较运算符
-- 查询价格等于500的记录
sqlite> select product_name, sale_price from Product where sale_price = 500;
product_name sale_price
------------ ----------
打孔器 500
叉子 500
-- 查询价格不等于500的记录
sqlite> select product_name, sale_price from Product where sale_price != 500;
product_name sale_price
------------ ----------
T恤衫 1000
运动T恤 4000
菜刀 3000
高压锅 6800
擦菜板 880
圆珠笔 100
-- 查询价格不等于500的记录
sqlite> select product_name, sale_price from Product where sale_price <> 500;
product_name sale_price
------------ ----------
T恤衫 1000
运动T恤 4000
菜刀 3000
高压锅 6800
擦菜板 880
圆珠笔 100
-- 查询价格大于等于500的记录
sqlite> select product_name, sale_price from Product where sale_price >= 500;
product_name sale_price
------------ ----------
T恤衫 1000
打孔器 500
运动T恤 4000
菜刀 3000
高压锅 6800
叉子 500
擦菜板 880
2.3 在查询条件中也可以使用算数运算符
sqlite> select product_name, sale_price, purchase_price from Product where (sale_price - purchase_price) = 500;
product_name sale_price purchase_price
------------ ---------- --------------
T恤衫 1000 500
2.4 逻辑运算符
-- NOT 表示否定
sqlite> select product_name, sale_price from Product where NOT sale_price >= 500;
product_name sale_price
------------ ----------
圆珠笔 100
-- AND 表示且，需要满足所有条件
sqlite> select product_name, sale_price from Product where product_type = "衣服" AND sale_price = 1000;
product_name sale_price
------------ ----------
T恤衫 1000
-- OR 表示或，满足一个条件即可
sqlite> select product_name, sale_price from Product where product_type = "衣服" OR sale_price = 1000;
product_name sale_price
------------ ----------
T恤衫 1000
运动T恤 4000
3. 聚合和排序
3.1 获取所有记录的数量
sqlite> select count(*) from Product;
count(*)
----------
8
3.2 计算合计值
-- 计算销售价总和
sqlite> select sum(sale_price) from Product;
sum(sale_price)
---------------
16780
-- 分别计算销售价总和 + 进货价格总和
sqlite> select sum(sale_price) as "销售单价", sum(purchase_price) as "进货价格" from Product;
销售单价 进货价格
---------- ----------
16780 12210
3.3 计算平均值
-- 计算销售价格的平均价
sqlite> select avg(sale_price) from Product;
avg(sale_price)
---------------
-- 分别计算销售价平均值和进货价格平均值
2097.5
3.4 计算平均值
sqlite> select avg(sale_price) from Product;
avg(sale_price)
---------------
2097.5
sqlite> select avg(sale_price), avg(purchase_price) from Product;
avg(sale_price) avg(purchase_price)
--------------- -------------------
2097.5 2035.0
3.5 计算最大值和最小值
-- 计算最高和最低的销售价格
sqlite> select max(sale_price), min(sale_price) from Product;
max(sale_price) min(sale_price)
--------------- ---------------
6800 100
3.6 使用聚合函数删除重复值
-- 计算商品种类数量
sqlite> select COUNT(DISTINCT product_type) from Product;
COUNT(DISTINCT product_type)
----------------------------
3
-- 使用DISTINCT 可能会导致结果不符合预期
sqlite> SELECT SUM(sale_price), SUM(DISTINCT sale_price) from Product;
SUM(sale_price) SUM(DISTINCT sale_price)
--------------- ------------------------
16780 16280
3.7 对查询结果进行分组
sqlite> select product_type, count(*) from Product group by product_type;
product_type count(*)
------------ ----------
办公用品 2
厨房用具 4
衣服 2
3.8 为聚合结果指定查询条件
-- 查询总数大于2的商品种类
sqlite> select product_type, count(*) from Product group by product_type having count(*) > 2;
product_type count(*)
------------ ----------
厨房用具 4
-- 查询平均价格大于1000的商品种类
sqlite> select product_type from Product group by product_type having avg(sale_price) > 1000;
product_type
------------
厨房用具
衣服
-- 有些条件可以写在where 或者 having 之后， 建议按照以下规则使用
-- where 用于过滤行
-- having 用于过滤列
sqlite> select product_type, count(*) from Product group by product_type having product_type = "衣服";
product_type count(*)
------------ ----------
衣服 2
sqlite> select product_type, count(*) from Product where product_type = "衣服" group by product_type;
product_type count(*)
------------ ----------
衣服 2
3.9 对结果进行排序
-- 按照sale_price 顺序对记录排序
sqlite> select product_id, sale_price from Product order by sale_price;
product_id sale_price
---------- ----------
0008 100
0002 500
0006 500
0007 880
0001 1000
0004 3000
0003 4000
0005 6800
-- 按照sale_price 倒序对记录排序
sqlite> select product_id, sale_price from Product order by sale_price desc ;
product_id sale_price
---------- ----------
0005 6800
0003 4000
0004 3000
0001 1000
0007 880
0002 500
0006 500
0008 100
SQL 语句执行顺序: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
子查询
4.1 查询价格大于平均价格的记录
-- WHERE 里不能用 avg 这种聚合函数
sqlite> select product_id, product_name, sale_price from Product where sale_price > avg(sale_price);
Error: misuse of aggregate function avg()
sqlite> select product_id, product_name, sale_price from Product where sale_price > (select avg(sale_price) from Product);
product_id product_name sale_price
---------- ------------ ----------
0003 运动T恤 4000
0004 菜刀 3000
0005 高压锅 6800
4.2 查询价格大于组内平均价格的商品记录
sqlite> 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);
product_type product_name sale_price
------------ ------------ ----------
办公用品 打孔器 500
衣服 运动T恤 4000
厨房用具 菜刀 3000
厨房用具 高压锅 6800
