本文所用到的数据表的结构如下:

数据表的内容如下:

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.比较运算

常见的比较运算符

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: