数据库系列之算术运算和比较运算
本文所用到的数据表的结构如下:
数据表的内容如下:
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:
作者:不思量自难忘
链接:https://juejin.cn/post/7022524569369968670