本文所用到的数据表的结构如下:
数据表的内容如下:
1.算术运算
1.算术运算
1.1 加减乘除运算
SELECT
id,
(sales_a + sales_b)as all_sales,
(sales_a - sales_b)as sales_a_b,
(sales_a * price_a + sales_b * price_b)as gmv,
(price_a / price_b)as price_a_b,
sales_a * 2 as s_sales_a
FROM
chapter7
result:
1.2 整除和取余运算
SELECT 7 DIV 2
result:
SELECT 7 % 2
result:
SELECT 7 MOD 2
result:
1.3 null相关的运算
null与任何数进行运算，结果都是null
SELECT
1 + NULL,
1-NULL,
1 * NULL,
1 / NULL
result:
2.比较运算
常见的比较运算符
| 运算符 | 含义 |
|---|---|
| 大于 | |
| < | 小于 |
| = | 等于 |
| >= | 大于等于 |
| <= | 小于等于 |
| != | 不等于 |
| <> | 不等于 |
| between A and B | [A, B] |
| is null | 空值 |
| is not null | 非空值 |
2.1 列之间的比较
SELECT
id,
sales_a,
sales_b,
sales_a > sales_b as "大于",
sales_a < sales_b as "小于",
sales_a = sales_b as "等于",
sales_a != sales_b as "不等于",
sales_a is null as "空值",
sales_a is not null as "非空值"
FROM
chapter7
result:
这里对比较运算做一个说明。比较结果为真则返回1，否则返回0
2.2 条件筛选
SELECT
id,
sales_a
FROM
chapter7
WHERE sales_a BETWEEN 14 AND 18
result:
