MySQL之数据运算-阿里云开发者社区

开发工具：

mysql-8.0
DataGrip

数据源：chapter7.csv

id,name,sales_A,sales_B,price_A,price_B
E001,张通,18,15,10,5
E002,李谷,12,17,10,5
E003,孙凤,19,20,10,5
E004,赵恒,12,14,10,5
E005,王娜,13,11,10,5
E006,李伟,16,16,10,5
E007,刘杰,11,13,10,5
E008,薛李,14,18,10,5
E009,裴军,11,18,10,5

（1）算术运算

算术运算就是我们所熟悉的加减乘除运算，是比较常见的、简单的运算。

在SQL中，我们要对某两列或多列进行算术运算时，直接将相应的列名与相应的运算符连接即可。现在需要获取每个销售产品的所有销量，即a产品销量+b产品销量；a产品与b产品的销量差；每个销售产品的总销售额，即a产品销量×a产品价格+b产品销量×b产品价格；a产品与b产品的价格倍数；a产品销量的2倍。具体实现代码如下：

-- 算术运算
-- 现在需要获取每个销售产品的所有销量，
-- 即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 chapter7;

（2）比较运算

比较运算主要用于两列或者某一列同一个具体的值之间的比较，主要有如下表所示的几种运算。

在SQL中，如果要实现比较运算，与在Excel中类似，需要先指明待比较的具体列，然后用比较运算符将不同的列连接起来，具体实现代码如下：

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

（3）逻辑运算

逻辑运算符主要用来连接多个条件，有and、or、not三种，如下表所示。

比如，我们要给每个id加两个标签：双优和单优。双优的标准是sales_a列和sales_b列均大于15，单优的标准是只要sales_a列和sales_b列中有一列大于15即可。具体实现代码如下：

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;

（4）数学运算

数学运算就是与数学相关的一些运算，比如，三角函数、对数运算等。

（4.1）求绝对值

比如，我们想要求每个id对应的sales_a列和sales_b列的绝对差值，如果直接将这两列做差，得到的结果肯定有正有负，但我们想要求的是绝对差值，所以我们需要对直接做差后的结果求绝对值。具体实现代码如下：

-- 1求绝对值
select id,sales_A,sales_B,(sales_A - sales_B) as '差值' ,
       ABS(sales_A - sales_B) as '绝对值' from chapter7;

（4.2）求最小整数值

比如，生成不小于x的最小整数值。在SQL中，我们使用的是ceil()函数，具体实现代码如下：

select ceil(2.9);

最后得到的结果为3，是不小于2.9的最小整数值。

（4.3）求最大整数值

比如，生成不大于x的最大整数值。在SQL中，我们使用的是floor()函数，具体实现代码如下：

select floor(2.1);

运行上面的代码，最后得到的结果为2，是不大于2.1的最大整数值。

（4.4）随机数生成

在SQL中，我们使用rand()函数来生成随机数，rand()函数返回0～1范围内的一个随机浮点数。

-- 随机数生成
select id ,rand() as '随机数' from demo.chapter7;
select id ,rand() as '随机数' from demo.chapter7 order by rand() desc limit 3 ;

（4.5）小数点位数调整

在SQL中，我们使用round()函数来对小数点位数进行调整。具体实现代码如下：

select round(1.1111,2)

运行上面的代码，最后得到的结果为1.11。

（4.6）正负判断

在SQL中，我们使用sign()函数来进行正负判断。比如，我们要判断每个id对应的sales_a列和sales_b列的差值的正负，可以通过如下代码实现：

select id,sales_A , sales_B,
       (sales_A - sales_B) as sales_a_b,
       sign(sales_a - sales_b) as "正负" from chapter7;

（5）字符串运算

字符串运算也是比较常见的一种运算，字符串运算函数如下表所示。

（5.1）字符串替换

在SQL中，我们使用的是replace()函数，具体实现代码如下：

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

最后得到的结果为aaaaaa。

比如，把chapter7表中id列的字符E替换成e，具体实现代码如下：

select id,replace(id,'E','e') as  replace_id from chapter7;

（5.2）字符串合并

字符串合并就是将多个字符串合并成一个字符串，在SQL中，我们使用的是concat()函数。

有的表中姓和名是分为两列存储的，所以我们需要将姓和名合并起来组成姓名，具体实现代码如下：

-- 字符串合并
select concat('cai','zhengjie');

如果将一张表中的两列或多列合并，则直接在concat()函数的括号中指明要合并的列名即可，比如，将chapter7表中的id列和name列合并，具体实现代码如下：

-- 将chapter7表中的id列和name列合并，具体实现代码如下：
select id,name,concat(id,name) as  concat_id from chapter7;

有时候，我们想用固定的符号合并不同的字符串或列，这个时候就需要用到另一个函数concat_ws()：

-- 我们想用固定的符号合并不同的字符串或列，这个时候就需要用到另一个函数concat_ws()：
select id,name,concat_ws('-',id,name) as  concat_id from chapter7;

（5.3）字符串截取

字符串截取就是从一个字符串中截取我们需要的部分字符，主要有左、中、右三种截取方式。

-- 字符串截取
select left('2019-10-01 12:30:21',10);
select right('2019-10-01 12:30:21',8);
select substring('2019-10-01 12:30:21',6,2);

（5.4）字符串匹配

在SQL中用于字符串匹配的是like，like有两种匹配符号：%和_。%用于匹配任意长度的字符，可以是0个，而_用于匹配单个长度的字符。

-- 字符串匹配
-- 比如，我们要把姓张的同学全部提取出来，假设名字列为name
select * from chapter7 where name like '张%';
-- 再如，我们要把name列中包含通的名字全部提取出来，只需要保证中间字符是凯，
-- 而前面和后面可以是若干个字符，具体实现代码如下：
select * from chapter7 where name like '%通%';
-- 又如，我们要获取name列中姓张的，且姓名为两个字的同学，可以通过如下代码实现：
select * from chapter7 where name like '张_';
-- 比如，我们要获取非张姓同学的信息，可以通过如下代码实现：
select * from chapter7 where name not like '张%';

（5.5）字符串计数

字符串计数就是统计一个字符串中包含多少个字符。在SQL中，我们使用的是char_length()函数，与char_length()函数类似的一个函数是length()

-- 字符串计数
select char_length('sql'); -- 3
 select length('我爱学习'); -- 12

（5.6）去除字符串空格

有去除字符串左边的空格、去除字符串右边的空格、去除字符串两边的空格三种方式。具体实现代码如下：

-- 去除字符串空格
-- 有去除字符串左边的空格、去除字符串右边的空格、去除字符串两边的空格三种方式。
select length(' abcdefg ') as srt_length,
       length(ltrim(' abcdefg ')) as ltrim_length,
       length(rtrim(' abcdefg ')) as rtrim_length,
       length(trim(' abcdefg ')) as trim_length;

（5.7）字符串重复

字符串重复是将同一个字符串重复若干次后合并成一个字符串，在SQL中使用的是repeat()函数。具体实现形式如下：

-- 字符串重复
-- repeat(str,x) -- 返回字符串str重复x次的结果
select repeat('Sql',3);

（6）聚合运算

聚合运算是指将多个值聚合在一起进行某种运算，比如，求和、求平均值等。

（6.1）count()计数

count()函数是用来对多个非缺失值进行计数的，常用于查看表中某列有多少非空值

-- 聚合运算
-- count()计数,我们要查看chapter7表中的id列一共有多少非空值
select count(id) from demo.chapter7;
-- 如果我们想要查看chapter7表中一共有多少行，那么只需要把括号中的id换成*即可。
select count(*) from demo.chapter7;
select count(' ');
-- 比如，我们想查看chapter7表中产品a一共有几种销量水平，即对sales_a列删除重复值后的计数，具体实现代码如下：
select count(distinct sales_A) from demo.chapter7;

（6.2）sum()求和

sum()函数主要用于对表中某列的所有值进行求和汇总

-- sum()求和
-- 比如，我们要分别获取chapter7表中产品a和产品b的总销量
select sum(sales_A),sum(sales_B) from demo.chapter7;

（6.3）avg()求平均值

avg()函数主要用于对表中某列的所有值进行求平均值运算

-- avg()求平均值
-- 比如，我们要分别获取chapter7表中产品a和产品b的平均销量，就可以使用avg()函数，具体实现代码如下：
select avg(sales_A),avg(sales_B) from demo.chapter7;

（6.4）max()求最大值

max()函数主要用于获取表中某列的最大值

-- max()求最大值
-- 比如，我们要分别获取chapter7表中产品a和产品b的最高销量，就可以使用max()函数，具体实现代码如下：
select max(sales_A),max(sales_B) from demo.chapter7;

（6.5）min()求最小值

min()函数与max()函数相对应，用于获取表中某列的最小值

-- min()求最小值
-- 比如，我们要分别获取chapter7表中产品a和产品b的最低销量，就可以使用min()函数，具体实现代码如下：
select min(sales_A),min(sales_B) from demo.chapter7;

（6.6）求方差

方差用于反映一组数据的离散程度，即波动程度，方差越大，说明数据波动越厉害，方差的计算公式如下：

式中，X为一组数据中的每个值，μ为总体平均值，N为总体数值个数。这个式子表示的就是一组数据中每个值与整组数据平均值之差的平方和，然后除以整组数据中的数值个数。

在SQL中，求总体方差，使用的是var_pop()函数；求样本方差，使用的是var_samp()函数。具体实现代码如下：

-- 求方差
-- 在SQL中，求总体方差，使用的是var_pop()函数；求样本方差，使用的是var_samp()函数。具体实现代码如下：
select var_pop(sales_A),var_samp(sales_A) from demo.chapter7;

（6.7）求标准差

标准差是方差的开方，也是用于反映数据的离散程度的，读者可能会想，不是已经用方差来反映数据的离散程度了吗，为什么还要用标准差呢？那是因为方差虽然可以反映数据的离散程度，但是不具有实际业务意义。因为标准差与实际数据的单位是一致的，比如，中学生身高的标准差的单位是厘米，而方差是厘米的平方就比较难理解。

因为标准差是方差的开方，方差有总体方差和样本方差，所以标准差也有总体标准差和样本标准差。

在SQL中，求总体标准差，使用的是std()函数；求样本标准差，使用的是stddev_ samp()函数。具体实现代码如下：

-- 求标准差
select  std(sales_A),stddev_samp(sales_A) from demo.chapter7;

（6.8）聚合函数之间的运算

比如，我们要获取产品a和产品b的总销量，就需要先对sales_a列进行求和聚合运算，然后对sales_b列进行求和聚合运算，最后把聚合运算后的两个值进行求和聚合运算，就是产品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;

MySQL之数据运算