数据清洗和汇总
一、字段
字段用于将不同列的内容进行组合，以满足生产的需要。
拼接字段
MySQL使用concat()函数拼接字段。
SELECT concat(id,'_',name) FROM customers;
#上面将会把生成id_name列
这样生成的表只适用于控制台查询，无法被客户机使用。建议使用别名创建可用于客户机的表。
二、别名
别名又被称为导出列，常用于生成汇总数据和重命名列。SQL使用AS创建别名：
SELECT id AS customer_id FROM customers;
#创建id列的别名
创建别名后，别名将代替原本的列名。
SELECT concat(id,'_',name) as id_and_name FROM customers;
#为拼接的字段创建别名
三、函数
MySQL函数名不区分大小写。
1.去除空格
| 函数 | 用途 | | :—–: | :————–: | | RTrim() | 去除列值右边空格 | | LTrim() | 去除列值左边空格 | | Trim() | 去除列值两侧空格 |
该函数可以用来配合通配符使用，以避免因两侧空格导致的不匹配问题。
SELECT address FROM customers WHERE rtrim(address) LIKE "%B";
#索引以B开头的address，忽略列值右边空格
2.文本处理函数
| 函数 | 用途 | | :——-: | :—————: | | left() | 返回串左边的字符 | | length() | 返回串长度 | | locate() | 查询串的一个字串 | | lower() | 串转换为小写 | | upper() | 串转换为大写 | | soundex() | 返回串的soundex值 |
1.截取串
SELECT left("hello",3);#从左边截取字符串的前三个字符
SELECT left(name,3) FROM customers;#截取name列值的前三个字符
2.字符串查找
除了通配符和正则表达式之外，locate函数也提供了字符串查找的功能。
locate函数返回第一个出现字串的位置，若没有查询到字串，则返回值为0
SELECT name,locate("z",name) FROM customers;
#查询name列中'z'第一次出现的位置
locate()同样能够查询数字。
3.字符串截取
substring(string,pos=begin(),pos=end());
4.模糊音查询
soundex用于查询读音相近的记录：
SELECT name FROM customer WHERE soundex(name)=soundex("Y Lie");
#查询name中读音和"Y Lie"相近的记录
5.时间函数
| 函数名 | 用途 | | :———–: | :—————————-: | | AddDate() | 增加一个日期（天、周等） | | AddTime() | 增加一个时间（时、分等） | | CurDate() | 返回当前日期 | | CurTime() | 返回当前时间 | | Date() | 返回日期时间的日期部分 | | DateDiff() | 计算两个日期之差 | | Date_Add() | 高度灵活的日期运算函数 | | Date_Format() | 返回一个格式化的日期或时间串 | | Day() | 返回一个日期的天数部分 | | DayOfWeek() | 对于一个日期，返回对应的星期几 | | Hour() | 返回一个时间的小时部分 | | Minute() | 返回一个时间的分钟部分 | | Month() | 返回一个日期的月份部分 | | Now() | 返回当前日期和时间 | | Second() | 返回一个时间的秒部分 | | Time() | 返回一个日期时间的时间部分 | | Year() | 返回一个日期的年份部分 |
6.数学函数
| 函数 | 用途 | | :—-: | :—————-: | | Abs() | 返回一个数的绝对值 | | Cos() | 返回一个角度的余弦 | | Exp() | 返回一个数的指数值 | | Mod() | 返回除操作的余数 | | Pi() | 返回圆周率 | | Rand() | 返回一个随机数 | | Sin() | 返回一个角度的正弦 | | Sqrt() | 返回一个数的平方根 | | Tan() | 返回一个角度的正切 |
7.聚集函数
| 函数 | 用途 | | :—–: | :————–: | | AVG() | 返回某列的平均值 | | COUNT() | 返回某列的行数 | | MAX() | 返回某列的最大值 | | MIN() | 返回某列的最小值 | | SUM() | 返回某列值之和 |
四、分组
分组主要是为了查询多行的汇总数据。主要依赖于GROUP和HAVING语句：
SELECT count(*) AS FROM cutomers GROUP BY id HAVING count(*)>=2;
#按id分组，并显示组行>=2的组
五、数据汇总
使用字段、别名、函数可以方便地建立汇总表：
SELECT AVG(price) AS averagePrice FROM items GROUP BY name;
#按商品名字分组并查询各组price列的平均值