MySQL使用记录

#### 前言

在平时的工作过程中虽然经常要用到Excel表格的公式去加工处理到手的数据，但很多时候数据量一大，公式就会显得力不从心，最近还发现了几个不可忽视的问题，所以闲暇之余我都会研究一遍如何用SQL数据库提（尽）高（情）效（摸）率（鱼）。

于是我就在这里持续更新使用SQL的问题和可能相应的解决方案

#### SQL顺序

书写顺序：SELECT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY ->LIMIT

执行顺序：FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> UNION -> ORDER BY ->LIMIT

#### 技巧&用法

##### WITH AS CTE

之前有一段时间我不知道有这个用法，在使用JOIN合并匹配查询的时候都是直接在FROM位置嵌套查询

```sql
WITH
  临时表名1 AS ( SELECT * FROM 表名......),
  临时表名2 AS ( SELECT * FROM 表名......)
SELECT
  *
FROM
  临时表名1 LEFT JOIN 临时表名2 ON 临时表名1.`字段名` = 临时表名2.`字段名`
```

##### 查看当前运行任务

```sql
select *
from information_schema.processlist
where command != 'Sleep'
order by time desc ;
```

##### Navicat

* 右键数据库，选择 `命令列界面`可以进入SQL命令执行界面

| 快捷键       | 描述     |
| ------------ | -------- |
| ctrl+q       | 新建查询 |
| ctrl+/       | 添加注释 |
| ctrl+shift+/ | 删除注释 |

##### 注释符号

单行用双减号: `--`

块注释用组合符号:

```sql
/*
注释内容
*/
```

##### 聚合函数

常用: sum, avg, count, max, min

在聚合函数使用时可以组合使用，比如

```sql
sum(`语文成绩`)/count(`语文成绩`)
```

##### 自定义列名, 表名

在select中使用as可以查询字段结果自定义列名

```sql
select
  sum(`语文成绩`)/count(`语文成绩`) as '平均语文成绩' from `考试成绩`
```

在select所有子句后用as自定义表名

```sql
(
select
  sum(`语文成绩`)/count(`语文成绩`) as '平均语文成绩',
  sum(`数学成绩`)/count(`数学成绩`) as '平均数学成绩' from `考试成绩`,
  sum(`英语成绩`)/count(`英语成绩`) as '平均英语成绩' from `考试成绩`,
from
  `考试成绩`
where
  sum(`语文成绩`,`数学成绩`,`英语成绩`) >= 240
)
as tmp
```

##### JOIN的类型

LEFT JOIN

RIGHT JOIN

INNER JOIN

FULL OUTER JOIN

##### JOIN实现字段匹配筛选

在select语句中，join可以实现类似于excel的vlookup公式的效果，比如根据学号匹配三个科目的成绩

| 学生名称 | 学号   |
| -------- | ------ |
| 小明     | 130001 |
| 小红     | 210002 |
| 老王     | 320005 |
| 狗蛋     | 120003 |

| 学号   | 语文成绩 | 数学成绩 | 英语成绩 |
| ------ | -------- | -------- | -------- |
| 130001 | 90       | 68       | 87       |
| 320005 | 88       | 89       | 95       |
| 210002 | 67       | 43       | 58       |

| 学号   | 成绩 |
| ------ | ---- |
| 130001 | 72   |
| 320005 | 67   |
| 210002 | 98   |

```sql
select
  `学生名册`.`学生名称`,
  `学生名册`.`学号`,
  `考试成绩`.`语文成绩`,
  `考试成绩`.`数学成绩`,
  `考试成绩`.`英语成绩`
from
  `学生名册` left join `考试成绩` on `学生名册`.`学号` = `考试成绩`.`学号`
```

join可以顺序嵌套实现多字段依次匹配

```sql
select
  `学生名册`.`学生名称`,
  `学生名册`.`学号`,
  `考试成绩`.`语文成绩`,
  `考试成绩`.`数学成绩`,
  `考试成绩`.`英语成绩`，
  `体育成绩`.`成绩`
from
  `学生名册` left join `考试成绩` on `学生名册`.`学号` = `考试成绩`.`学号` left join `体育成绩` on `学生名册`.`学号` = `体育成绩`.`学号`
```

##### 索引的添加

使用where的时候尽量为使用到的字段添加索引，可以大大加速SELECT效率

#### 函数记录

##### 字符串替换

REPLACE(str,from_str,to_str)

##### 日期相关

| 函数名                            | 函数                                                                                    |
| --------------------------------- | --------------------------------------------------------------------------------------- |
| NOW()                             | 返回当前的日期和时间                                                                    |
| CURDATE()                         | 返回当前的日期                                                                          |
| CURTIME()                         | 返回当前的时间                                                                          |
| DATEDIFF(expr1,expr2)             | 返回两个日期之间的天数(expr1-expr2)                                                     |
| DATE_ADD(date,INTERVAL expr unit) | 在日期中添加指定的间隔[expr和unit说明](https://www.w3school.com.cn/sql/func_date_add.asp)  |
| DATE_SUB(date,INTERVAL expr unit) | 在日期中减去指定的间隔                                                                  |
| DATE_FORMAT(date,format)          | 以不同的格式显示日期数据[format说明](https://www.w3school.com.cn/sql/func_date_format.asp) |
| DAY(date)                         | 从date中提取日                                                                          |
| MONTH(date)                       | 从date中提取月份                                                                        |
| YEAR(date)                        | 从date中提取年份                                                                        |
| DATE(expr)                        | 从expr中提取年份                                                                        |
| TIME(expr)                        | 从expr中提取时间                                                                        |
| TIMEDIFF(expr1,expr2)             | 返回两个时间之间的差值(expr1-expr2)默认格式为00:00:00                                   |
| TIME_FORMAT(time,format)          | 以不同的格式显示时间数据[format说明](https://www.w3school.com.cn/sql/func_date_format.asp) |

##### 条件判断

###### IF(expr1,expr2,expr3)

##### 定位字符串位置

###### POSITION(substr IN str)

###### LOCATE(substr,str,pos)

##### 截取字符串

###### LEFT(str,len)

###### MID(str,pos,len)

###### RIGHT(str,len)

###### SUBSTRING_INDEX(str,delim,count)

count > 0 : 从左往右数，第N个分隔符的左边的全部内容 (不包含分隔符)

count < 0 : 从右往左数，第N个分隔符的右边的全部内容 (不包含分隔符)

截取中间第N和第N+1个分隔符的部分: substring_index(substring_index(str, '-', N+1), '-', -1)

#### 基本语句

##### 增

固定字段、数值

```sql
insert into
  `table_name`
  (column1, column2, column3,...columnN) 
values
  (value1, value2, value3,...valueN)
```

将select结果插入表内

```sql
insert into
  `table_name`
(
  select
    `table_name`.`column1`
  from
    `table_name`
  [where]...
)
```

##### 删

```sql
delete
from 
  `table_name` 
where 
  `column1` ...
```

##### 查

```sql
select
  `table_name`.`column`
from
  `table_name`  
[(left/inner/right/cross/full outer) join]
[where]
[group by]
[having]
[order by]
[limit]
```

##### 改

```sql
update
  `表名`
set 
  column1 = value1,
  column2 = value2
where
  condition
```