DECIMAL数据类型和NUMERIC数据类型
max()函数的替代方式
find_in_set函数
locate函数
ifnull函数
if函数
日期运算
GROUP_CONCAT函数
有时候group_concat的长度会不太够用，需要手动设置一下长度
从0开始自增的表写法
从1开始自增的表写法

DECIMAL数据类型和NUMERIC数据类型

//普通的float数据类型在存储整数的时候会将小数点后的数字去除
DECIMAL(5,1)表示最多有5个十进制数，小数点后（有且只有）有一位
表达范围：-9999.9~99999.9
//如果是正数，那么负号会转化成额外的一个位数
NUMERIC在mysql中和DECIMAL中是同一种类型

max()函数的替代方式

1.降序排序取第一个值
2.使用自连接找出有比自己更大值的参数，化归为一个结果集，随后查找不在这个结果集中的数

find_in_set函数

字符串A在字符串listB中出现的位置，若没有，则返回0

字符串b一定得是以”,”分割的数组

find_in_set(str,strlist)
例：find_ins_set(‘c’,’a,b,c,d’)将返回3

locate函数

字符串A在字符串B中出现的位置，若没有，则返回0

locate(strA,strB)
例：locate(“hello”,”hello_world”)将返回1

ifnull函数

ifnull(,)
> 如果该字段查询结果为空，则使用替换值来替换

if函数

if(expr,res1,res2)
根据表达式的结果，如果正确，则返回res1，否则返回res2

# 如果id>10返回100，否则返回1
if(id>10,100,1)

日期运算

在某日期上加减
- 加：date_add(
  , interval n <单位>)
  - 例：date_add(now(), interval 8 hour)
- 减：date_sub(同上)
计算两个日期之间的差值
- timediff(
  ,
  )
  - 将使用第一个日期减去第二个日期，结果为时:分:秒
计算两个日期之间的差值（指定返回结果）
- timestampdiff(<返回形式>,
  ,
  )
  - 例：timestampdiff(DAY,’2012-10-01’,’2013-01-13’)
  - 将返回两个日期之间所相差的天数（第二个日期减第一个日期）
    GROUP_CONCAT函数
    将组中的字符串连接成为单个字符串
    语法
DISTINCT用于连接前的去重（可选）
column为将要连接的字符串数组，可以为某个查询结果
ORDER BY允许在连接前进行排序（默认为升序）
SEPARATOR指定连接的分隔符（默认为”,”）
```
GROUP_CONCAT(DISTINCT column
  ORDER BY expression
  SEPARATOR sep);
```
示范
```
#将某列的结果使用","拼接
select group_concat(org_name) from orgnazation
```
设置长度
```sql
有时候group_concat的长度会不太够用，需要手动设置一下长度

SET GLOBAL group_concat_max_len = 10240

<a name="astf6"></a>
## 有外键约束的情况下清表
```sql
SET foreign_key_checks = 0;
TRUNCATE <table_name> ;
SET foreign_key_checks = 1;

获取插入后自增的id

参考：https://www.cnblogs.com/duanxz/p/3862356.html

# 该命令基于单次连接，不会出现并发问题
SELECT DISTINCT LAST_INSERT_ID();

将查询结果作为插入条件

复制表

insert into t1(a,b,c) select a,b,c from t2

部分插入条件为查询值，部分为预设值

可以为

insert into t1(a,b,c) select * from (select count(1) as a from t2)j1 join (select "hello" as b)j2 join (select "world" as c)j3

字符串截取

从左开始截取：
- left(str,length)
从右开始截取：
- right(str,length)
截取：
- substring(str,index)
  - 从指定位置开始截取，index为正数则往后截取，为负数从后往前数（还是往后截取）
- substring(str,index,len)
  - 额外指定长度
按关键字截取
- substring_index(str,key,count)
  - 从指定key开始截取，count为关键字出现的次数
  - substring_index(str,’-‘,2)表示从第二次出现’-‘的位置开始，往前截取
    - 可以使用负数，表示从后往前数，第n个位置往后截取
      字符串切割
      此处使用逗号作为分隔符 ```sql
      从0开始自增的表写法
      SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘7654,7698,7782,7788’, ‘,’, help_topic_id + 1), ‘,’, -1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH(‘7654,7698,7782,7788’) - LENGTH(REPLACE(‘7654,7698,7782,7788’, ‘,’, ‘’)) + 1

从1开始自增的表写法

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘7654,7698,7782,7788’, ‘,’, ee.id), ‘,’, -1) AS num FROM ehs_employee ee WHERE ee.id < LENGTH(‘7654,7698,7782,7788’) - LENGTH(REPLACE(‘7654,7698,7782,7788’, ‘,’, ‘’)) + 2

<a name="BJzNY"></a>
### 原理

- 内层的SUBSTRING_INDEX函数截取到部分字符串，外层的SUBSTRING_INDEX每次都拿最后一部分字符串
   - 这样的话，只要知道字符串需要被分为几份就可以完成切割
- mysql.help_topic表就是用于辅助执行该操作的表（其他主键自增的表也可以使用），where条件中要求该id<字符串被切割的份数，那此处的help_topic_id就是0,1,2,3
   - 第一次内层的SUBSTRING_INDEX拿到的就是7654，第一行也就是7654
   - 第二次内层的SUBSTRING_INDEX拿到的是7654,7698，第二行也就是7698
   - 以此类推
<a name="RrkyK"></a>
#### 注意
help_topic表是从0开始自增的，如果使用其他自增表，注意自增开始的情况

- 如果是1开始的话，示例中`help_topic_id + 1`就要该成`help_topic_id`，最后的`+1`要变成`+2`

<a name="wGLSS"></a>
## UNION查询

- union：用于将不同表中相同列中查询的数据展示出来（去重）
   - select <column_1> from <table_1> union select <column_2> from <table_2> order by <column>
   - 两个表中待查的列，名称不一定相同（不过既然要合并，最起码是相关的），而order所依赖的列，名称必定相同
- union all：功能同上（不去重）
<a name="PcaFX"></a>
## 编码临时转换
连接表查询时，如果两个字段的编码不一致，不能使用"="连接，此时需要进行类型转换：
```sql
convert(<column> using <code_type>)  collate <sort_by>

#示范：system_task表和system_code表的关联字段使用了不同的编码方式
SELECT st.`status`,sc.code_desc_zh FROM system_task st LEFT JOIN system_code sc on st.`status` = CONVERT(sc.`code` USING utf8) COLLATE utf8_unicode_ci;

数据类型转换

cast(
as
)
```
SELECTT cast(num as varchar(10))
```

插入时更新

导入数据时，如果不存在则添加，有修改则更新
关键字：ON DUPLICATE KEY UPDATE

# 有两个字段，a拥有unique的key
insert into table(a,b) values(1,2) on duplicate key update b = b+1

修改sql_model

# 查找
select @@global.sql_mode
# 设置
set global sql_mode = ''

case when函数

select case
           when locate('_SYS_A8_2', @org_type) > 0 then 0
           when locate('_SYS_A8_3', @org_type) > 0 then 1
           when locate('_SYS_A8_4', @org_type) > 0 then 2
           when locate('_SYS_A8_5', @org_type) > 0 then 3
           when locate('_SYS_A8_6', @org_type) > 0 then 4
           end;

大小写转换

转换为大写：upper(s)
转换为小写：lower(s)

字符串替换

把字符串中的某个字符串替换为另外一个
replace(str,str1,str2)

使用游标

如果需要在数据库中对批量的数据进行处理，就需要用到游标

delimiter $$
drop procedure if exists updateAllParentLevelNameSpace;
create procedure updateAllParentLevelNameSpace()
begin
    declare de_space_id int;
    declare de_service_id int;
    declare done int;
    # 定义游标
    declare space_cur cursor for select id          as de_space_id,
                                        service_id  as de_service_id,
                                 from ehs_space;
    # 定义游标结束时的操作
    declare continue handler for not found set done = 1;

        # 打开游标
    open space_cur;
    # 开始遍历游标（固定写法）
    posLoop:LOOP
            # 游标结束时，离开游标
        if done = 1 then
            leave posLoop;
        end if;
        # 将游标当前行的数据存入变量中
        fetch space_cur into de_space_id,de_service_id,de_building_id,de_floor_id,de_area_id;
        set @service_name_v = (select space_name from ehs_space where id = de_service_id);
        update ehs_space
        set service_name  = @service_name_v
        where id = de_space_id;
    end loop;
    # 关闭游标
    close space_cur;
end$$
delimiter ;

取有值的第一个值

有时候一次要查询多个字段，以其中第一个不是null的字段作为结果，可以用case when，也有更加便捷的处理：
coalesce（column_1,column_2,column_3）
该方法将在字段中遇到第一个不是null的值，并返回

MYSQL

日常补充