# 数据清洗和汇总


## 一、字段

​	字段用于将不同列的内容进行组合，以满足生产的需要。

### 拼接字段

​	MySQL使用concat()函数拼接字段。

```mysql
SELECT concat(id,'_',name) FROM customers;
#上面将会把生成id_name列
```

​	这样生成的表只适用于控制台查询，无法被客户机使用。建议使用别名创建可用于客户机的表。

## 二、别名

​	`别名`又被称为`导出列`，常用于生成汇总数据和重命名列。SQL使用`AS`创建别名：

```mysql
SELECT id AS customer_id FROM customers;
#创建id列的别名
```

​	创建别名后，别名将代替原本的列名。

```MySQL
SELECT concat(id,'_',name) as id_and_name FROM customers;
#为拼接的字段创建别名
```

## 三、函数

​	MySQL函数名不区分大小写。

### 1.去除空格

|  函数   |       用途       |
| :-----: | :--------------: |
| RTrim() | 去除列值右边空格 |
| LTrim() | 去除列值左边空格 |
| Trim()  | 去除列值两侧空格 |

​	该函数可以用来配合通配符使用，以避免因两侧空格导致的不匹配问题。

```MySQL
SELECT address FROM customers WHERE rtrim(address) LIKE "%B";
#索引以B开头的address，忽略列值右边空格
```

### 2.文本处理函数

|   函数    |       用途        |
| :-------: | :---------------: |
|  left()   | 返回串左边的字符  |
| length()  |    返回串长度     |
| locate()  | 查询串的一个字串  |
|  lower()  |   串转换为小写    |
|  upper()  |   串转换为大写    |
| soundex() | 返回串的soundex值 |

### 1.截取串

```MySQL
SELECT left("hello",3);#从左边截取字符串的前三个字符
SELECT left(name,3) FROM customers;#截取name列值的前三个字符
```

​	<a style="color:red">left()同样能够截取数字列</a>

### 2.字符串查找

​	除了通配符和正则表达式之外，locate函数也提供了字符串查找的功能。

<a style="color:blue">locate(字串,母串);</a>

<a style="color:blue">locate函数返回第一个出现字串的位置，若没有查询到字串，则返回值为0</a>

```MySQL
SELECT name,locate("z",name) FROM customers;
#查询name列中'z'第一次出现的位置
```

​	locate()同样能够查询数字。

### 3.字符串截取

​	substring(string,pos=begin(),pos=end());

### 4.模糊音查询

​	soundex用于查询读音相近的记录：

```MySQL
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()  |  返回某列值之和  |

​	<a style="color:red">所有聚集函数包含两个参数：第一个参数默认为ALL，可更改为DISTINC，第二个参数为列名。</a>

## 四、分组

​	分组主要是为了查询多行的汇总数据。主要依赖于`GROUP`和`HAVING`语句：

```MySQL
SELECT count(*) AS FROM cutomers GROUP BY id HAVING count(*)>=2;
#按id分组，并显示组行>=2的组
```



## 五、数据汇总

​	使用字段、别名、函数可以方便地建立汇总表：

```MySQL
SELECT AVG(price) AS averagePrice FROM items GROUP BY name;
#按商品名字分组并查询各组price列的平均值
```