数据运算

*数据源：储存在demo数据库的chapter7表中

一、算术运算：

需要获取每个销售产品的所有销量，即A产品销量+B产品销量；A产品与B产品的销售差；每个销售产品的总销售额，即A产品销售量呈A产品价格+B产品销量B产品价格；A产品与B产品的价格倍数；A产品销量的2倍。

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 2_sales_a
from demo.chapter7

以下为整除（div）和取余（%和mod）:

select 7 div 2

结果：3

select 7 % 2

结果：1

select 7 mod 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 demo.chapter7

以下为获取A产品的销量为15~20范围内的id列和sales_a列：

select
      id
      ,sales_a
from demo.chapter7
where sales_a between 15 and 20

以下为获取A产品的销量大于15的id列和sales_a列：

select
      id
      ,sales_a
from demo.chapter7
where sales_a > 15

三、逻辑运算：

需要给每个id加两个标签：双优和单优。双优的标准是sales_a列和sales_b列均大于15，单优的标准是只要sales_a列和sales_b列中有一列大于15即可。满足双优标准的id会被加上1标签，不满足的被加上0标签。

select
      id
      ,sales_a
      ,sales_b
      ,((sales_a > 15) and (sales_b >15)) as "双优"
      ,((sales_a > 15) or (sales_b >15)) as "单优"
from demo.chapter7

四、数学运算：

1.求绝对值：需要每个id对应的sales_a列和sales_b列的绝对差值

select 
    id
    ,sales_a
    ,sales_b
    ,(sales_a-sales_b) as "差值"
    ,abs(sales_a-sales_b) as "绝对差值"
from demo.chapter7

*abs(x)——返回x的绝对值

2.求最小整数值：生成不小于x的最小整数值

select ceil(2.9)

*结果：3（不小于2.9的最小整数值）

3.求最大整数值：生成不大于x的最大整数值

select floor(2.1)

*结果：2（不大于2.1的最大整数值）

4.随机数生成：
1)0——1范围内的随机浮点数:

select rand()

2)给每个id对应生成一个随机数：

select 
    id
    ,rand() as "随机数"
from demo.chapter7

3）从id里随机抽取出3个，将id按照随机数大小进行排序

select 
    id
    ,rand() as "随机数"
from demo.chapter7
order by raand()
limit 3

5.小数点位数调整：保留2位小数

select round(1.1111,2)

*结果：1.11

6.正负判断：判断每个id对应的sales_a列和sales_b列的差值的正负

select 
    id
    ,sales_a
    ,saels_b
    ,(sales_a-sales_b) as sales_a-b
    ,sign(sales_a-sales_b) as "正负"
from demo.chapter7

五、字符串运算：

1.字符串替换：
1）A被替换成a：

select replace("AaAaAa","A","a")

*结果：aaaaaa
2）id列的字符E替换成e：

select 
    id
    ,replace(id,"E","e") as replace_id
from demo.chapter7

2.字符串合并：
1）将姓和名组成姓名：

select concat("多啦","a梦")

2）将id列和name列合并：

select 
    id
    ,name
    ,concat(id,name) as id_name
from demo.chapter7

3)用固定的符号合并不同的字符串或列：

select 
    id
    ,concat_ws("-",id,name) as id_name
from demo.chapter7

3.字符串截取：
1）截取字符串的左边部分：

select left("2019-10-01 12:30:20",10)

*结果：2019-10-01
2）截取字符串的右边部分：

select right("2019-10-01 12:30:20",8)

*结果：12:30:20
3)截取字符串的中间部分：

select substring("2019-10-01 12:30:20",6,2)

*结果：10

4.字符串匹配：
1）like匹配符号%：
（1）提取所有姓张的人：

select 
     *
from demo.chapter7
where 
    name like "张%"

*%用于匹配任意长度的字符
（2）包含凯的名字全部提取出来：

select 
     *
from demo.chapter7
where 
    name like "%凯%"

2）like匹配符号_：提取所有姓张，并且姓名为2个字的人

select 
     *
from demo.chapter7
where 
    name like "张_"

3）not like匹配不到的数据：

select 
     *
from demo.chapter7
where 
    name not like "张%"

5.字符串计数：
1）char_length:

select char_length("sql")
select char_length("我爱学习")

*结果：3；4
2）length：

select length("sql")
select length("我爱学习")

*结果：3；12

*注意：char_length()是基于字符计数的；length()是基于字节计数的

6.去除字符串空格：
1）使用ltrim()去掉左边空格
2）使用rtrim()去掉右边的空格
3）使用trim()去掉两边的空格

select
     length(" abcdef ") as str_length
     ,length(ltrim(" abcdef")) as lstr_length
     ,length(rtrim(" abcdef")) as rstr_length
     ,length(trim(" abcdef")) as tstr_length

*结果：8(总长度)；7(去左)；7(去右)；6(去两边)

7.字符串重复：将同一个字符串重复若干次后合并成一个字符串

select repeat("sql",3)

*结果：sqlsqlsql

六、聚合运算：

1.count()计数：
1）查看id列一共有多少非空值：

select 
    count(id)
from demo.chapter7

2)查看表中一共有多少行：

select 
    count(*)
from demo.chapter7

3）删除重复值后的计数：

select 
    count(distinct sales_a)
from demo.chapter7

2.sum()求和：获取产品a和产品b的总销售

select 
    sum(sales_a)
    ,sum(sales_b)
from demo.chapter7

3.avg()求平均值：获取产品a和产品b的平均销量

select 
    avg(sales_a)
    ,avg(sales_b)
from demo.chapter7

4.max()求最大值：获取产品a和产品b的最高销量

select 
    max(sales_a)
    ,max(sales_b)
from demo.chapter7

5.min()求最小值：获取产品a和产品b的最低销量

select 
    min(sales_a)
    ,min(sales_b)
from demo.chapter7

6.求方差：获取产品a的总体方差与样本方差

select 
    var_pop(sales_a)
    ,var_samp(sales_a)
from demo.chapter7

7.求标准差：获取产品a的总体标准差和样本标准差

select 
    std(sales_a)
    ,stddev_samp(sales_a)
from demo.chapter7

8.聚合函数之间的运算：获取产品a和产品b的总销量

select 
    sum(sales_a) as a group
    ,sum(sales_b) as b_group
    ,sum(sales_a) + sum(sales_b) as a_b_group
from demo.chapter7