本文所用到的数据表的结构如下:
数据表的内容如下:
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:
近期评论