1、字段间的算术运算
销量 = 备货量 - 库存量，那么，让备货量对应的字段
total减去库存量对应的surplus，结果是实际销量。SQL 会根据我们给出的算式，按行执行字段与字段之间的数学运算，最终的计算结果也被保存到了一个新的字段中，新字段的名称就是原算式total - surplus。SELECT event_name, total, surplus, total - surplus FROM stocks;
2、round()函数--保留指定位数的小数
- round(待处理的算式/数据, 保留小数的位数)
- eg：round(surplus/total, 2)
SELECT event_name, total, surplus, round(surplus/total, 2) FROM stocks;
3、AS关键字用
AS关键字给字段取别名，这里的字段可以是数据表中的单独一列，也可以是算术运算后得到的新字段。
--比如下面的语句，可以给 total - surplus 取别名为 sales，意为销量： SELECT event_name, price, total - surplus AS sales FROM stocks; --用中文作别名 SELECT event_name AS 演出名称, price AS 价格, total - surplus AS 销量 FROM stocks;取别名时的注意事项：在三种情况下，要在别名两侧加反引号
- 别名与SQL中的关键字相同。例： `where `
- 别名以数字开头。例： `2020年销售额 `
- 别名中包含特殊字符。例： `互联网+ `
4、ORDER BY 子句--按指定字段对查询结果进行排序。默认情况下，
ORDER BY子句的结果为升序（从低到高）排列--按多个字段排序 --对 stocks 表按 演出类型 - 价格 排序的语句，写成： SELECT * FROM stocks ORDER BY event_type, price;这里
event_type作为外层排序依据，price作为内层排序依据，SQL 会首先按event_type，将同类型的数据组织到一起，再在每个类型内部按price字段排序。SQL 对于中文会按照拼音排序，对于英文则是按照字母顺序排序的。
-- 按字段别名排序 -- 按英文别名排序 SELECT event_name, price, total - surplus AS sales FROM stocks ORDER BY sales; -- 按中文别名排序 SELECT event_name AS 演出名称, price AS 价格, total - surplus AS 销量 FROM stocks ORDER BY 销量;指定排序方向
- 从低到高 ORDER BY price ASC (升序排序关键字可省略)
- 从高到低 ORDER BY price DESC (降序排序关键字)
5、LIMIT关键字--限制查询结果的数量
LIMIT关键字用于限制查询结果的数量，接收的两个参数分别为获取的起始位置（也即起始行号 - 1），和一共获取的行数，从起始位置开始，一直向后数指定个数字，也就得到了待获取的行号。
LIMIT 3, 4的含义就是从 3 开始往后数 4 个数字，得到 4,5,6,7，以此作为待获取的行号。以
LIMIT 0, 5为例，起始位置是 0，返回结果的行数是 5，那么，从 0 开始往后数 5 个数，就得到了待获取的行号：1, 2, 3, 4, 5，最终返回了第 1～5 行数据。
LIMIT 5相当于LIMIT 0, 5。-- 找到 话剧 类型的演出中 售卖比最高的 5 场 SELECT event_name AS 演出名称, price AS 价格, event_type AS 演出类型, (total - surplus) * price AS 销售额, round((total - surplus)/total, 2) AS 售卖比 FROM stocks WHERE 演出类型 = '话剧' ORDER BY 售卖比 DESC LIMIT 5;
6、datetime()函数
datetime(需要处理的日期字段, 原日期字段的格式)
eg：datetime(pay_time,' unixepoch')
把 pay_time 列从 Unix 时间戳格式日期，转换成标准格式的日期
7、substring()函数
substring (需要处理的字段, 开始提取位置, 提取字符个数)
eg：substring (tracking_num, 1, 2);--tracking_num 中前两个字母是我们需要提取出的快递公司编号信息，所以写成 SQL 语句就是这样： SELECT substring(tracking_num, 1, 2) AS 快递公司编码 FROM orders; --让计算机对于 tracking_num 字段中的每一行，都从第 1 个字符开始，提取出长度为 2 的字符串，也就是提取出前两个字母 --判断提取出来的信息是否为 HX SELECT tracking_num AS 快递单号, substring(tracking_num, 1, 2) = 'HX' AS 是否为自营 FROM orders; --是否为自营 一列中的数据不是 0 就是 1 --SQLite 默认使用 0 来表示 假、不成立 的概念，用 1 来表示 真、成立 的概念
8、iif()函数
iif( 需要判断的条件, 满足条件时在结果对应行填入的值, 不满足条件时在结果对应行填入的值)
eg：iif( substring (tracking_num, 1, 2) = ‘HX', '是', '否')
SELECT tracking_num AS 快递单号, iif( substring(tracking_num, 1, 2) = 'HX', -- 需要判断的条件 '是', -- 满足条件时填入的值 '否' -- 不满足条件时填入的值 ) AS 是否为自营 FROM orders;
跟统计相关的函数：
count()：统计行数；
- 参数为
*：统计选择范围内所有行（包括存在空值的行）的个数；- 参数为 字段名：统计该字段中，所有 非空值 行的个数；
- 参数为 DISTINCT 字段名：统计该字段中，唯一 非空值 行的个数。
min()：统计最小值；max()：统计最大值；avg()：统计平均值；sum()：统计所有数值之和。
-- 在 SQLite 中，整数与整数作除法时，会舍去小数点后的数值 -- 所以两个计算字段，都需要在前面乘以 1.0，写成形如 1.0 * 计算公式 的形式，避免损失精度 SELECT round(1.0*(sum(price*amount))/count(DISTINCT user_id), 2) AS 平均消费金额, round(1.0*(sum(amount))/count(DISTINCT user_id), 2) AS 平均购买门票张数 FROM orders;
9、GROUP BY子句--将原数据进行 分组GROUP BY 分组依据；
eg：GROUP BY user_id;
SQL 会先执行
GROUP BY子句，再执行SELECT子句。
10、HAVING子句--筛选分组结果HAVING 筛选依据；
-- 筛选出所有新用户啦 -- 同时满足 R 高（R > 32）、F 不高于2（F <= 2）、M 低（M < 1180）这三个条件的用户才是新用户 -- 并按照 M 降序、F 降序、R 升序 的顺序，对结果进行排序 SELECT user_id AS 用户ID, user_name AS 用户昵称, (1641052799 - max(pay_time)) / (24 * 60 * 60) AS R, count(user_id) AS F, sum(price * amount) AS M FROM orders GROUP BY user_id HAVING R > 32 AND F <= 2 AND M < 1180 ORDER BY M DESC, F DESC, R;执行顺序是：
FROM子句：从orders数据表中读取数据，确定后续查询的数据来源；GROUP BY子句：根据user_id列的值进行分组；HAVING子句：从分组结果中筛选出符合R > 32 AND F <= 2 AND M < 1180条件的分组；SELECT子句：从最终结果中选择出若干列、计算字段进行展示。
子查询：用一对圆括号()包裹，作为一个元素参与到外层查询中的查询。
-- 从此处开始写外层查询
SELECT item_id, item_name
FROM items
WHERE item_id IN
-- 用圆括号将子查询内容包裹起来
(
SELECT item_id
FROM user_behavior
WHERE type = 'buy' -- 筛选出类型为 buy 的数据
GROUP BY item_id -- 按 item_id 进行分组
ORDER BY count(*) DESC -- 按每组数据的数量进行降序排序
LIMIT 1 -- 选取出第一条数据，也就是购买人数最多的商品
);
-- 外层查询编写完毕，别忘了加上分号哦把子查询作为外层查询的计算字段:
-- 根据 items 数据表中记录的 brand_id 信息，检索出 brands 数据表中对应的 brand_name
-- 并作为一个计算字段，展示在最终的结果表里
SELECT (
SELECT brand_name
FROM brands
WHERE brands.brand_id = items.brand_id
) AS 品牌名,
item_name AS 商品名
FROM items;SELECT count(iif(type = 'pv', 1, NULL)) AS A,
count(iif(type IN ('fav', 'cart'), 1, NULL)) AS I,
count(iif(type = 'buy', 1, NULL)) AS P,
(
SELECT count(*)
FROM (
SELECT count(*) AS 购物次数
FROM user_behavior
WHERE type = 'buy'
GROUP BY user_id
HAVING 购物次数 > 1
)
) AS L
FROM user_behavior;AS关键字给表取别名
SELECT datetime(ub.time, 'unixepoch') AS 购买时间,
ub.user_name AS 用户名,
i.item_name AS 商品名,
b.brand_name AS 品牌名
FROM user_behavior AS ub,
items AS i,
brands AS b
WHERE ub.item_id = i.item_id
AND i.brand_id = b.brand_id
AND ub.type = 'buy';JOIN --把两张数据表 联结 起来
SELECT datetime(time, 'unixepoch') 购买时间,
user_name 用户名,
item_name 商品名,
brand_name 品牌名
FROM user_behavior ub
JOIN items i ON ub.item_id = i.item_id
JOIN brands b ON i.brand_id = b.brand_id
WHERE type = 'buy';LEFT JOIN
把 LEFT JOIN 关键字之前的数据表作为主表，把后面的作为次表。在创建联结时，先把主表中的所有数据誊抄一遍，再把次表中符合联结条件的数据拼接在后面。可以完全保留主表中的数据。
SELECT *
FROM items i
LEFT JOIN item_data id ON i.item_id = id.item_id
JOIN brands b ON i.brand_id = b.brand_id;
577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
