结合实际应用总结mySql语句关键场景用法

      前言：根据应用场景，不定时更新，欢迎大家把自己遇到的问题留言，稍后完善。

1 应用场景

       根据研发过程中，遇到很频繁的数据库操作，总结常用sql语句，应用于业务代码中，运维过程中等。

1.1 编写业务sql语句

（1）条件语句，给字段重新赋值

　　适用场景：每个枚举，返回对应的字符串。

　　关键字：case when x=y then e when x=z then f end

　　sql语句：(case when a.status=1 then '申请中' when a.status=2 then '受理中' when a.status=3 then '已转账' when a.status=4 then '拒绝' end) as status

　　解释：当status为1时状态为申请中，当status为2时状态为受理中，当status为3时状态为已转账，当status为4时状态为拒绝。

（2）条件语句，给字段拼接字符

　　适用场景：给字段拼接字符。

　　关键字：concat

　　sql语句：(case when a.type=0 then concat('-',a.score) else concat('+',a.score) end) as showscore

　　解释：当type为0时score增加“-”前缀，当type为其他时score增加“+”前缀。

（3）条件语句，截取字段中字符串前几位，使用left

　　适用场景：截取字符串。

　　关键字：left

　　sql语句：select * from table_a where left(create_time,4) in (2019, 2020);

　　解释：取创建时间的前4位，即年，查询在创建时间未2019和2020的记录。

（4）遍历ids（ids为入参List<String> ids）

<select id="queryNamesByIds" resultType="java.lang.String">
SELECT role_name

FROM sys_depart_role WHERE id in <foreach collection="ids" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>

</select>

（5）mybatis中嵌入单个对象association 

<resultMap id="BaseResultMap" type="org.modules.gbm.entity.CcReco">
<id column="id" property="id" />
<result column="dept_id" property="deptId" />
<result column="project_id" property="projectId" />
<result column="bank_id" property="bankId" />
<result column="remark" property="remark" />
<result column="create_by" property="createBy" />
<result column="create_time" property="createTime" />
<result column="update_by" property="updateBy" />
<result column="update_time" property="updateTime" />
<association property="reconciliationSupplier" javaType="org.modules.gbm.entity.CcSup">
    <id column="id" property="id" />
    <result column="rec_id" property="recId" />
    <result column="sup_id" property="supId" />
</association>
</resultMap>

（6）性能提升，判断存在exist

 

（7）统计另外一张表中匹配字段的数量，如果另外一张表无数据，则数量为0

select a.*,if(ISNULL(b)=1,0,b)count
from app_user a
LEFT JOIN(SELECT appd,COUNT(appd) b from care GROUP BY appd) c on a.id=c.appd

 

（8）常用sql操作：

模糊查询：name like CONCAT('%',#{data.name,jdbcType=VARCHAR},'%')

日期格式化：DATE_FORMAT(create_time, '%Y-%m-%d') &gt;= DATE_FORMAT(#{data.startTime}, '%Y-%m-%d')

精确匹配：FIND_IN_SET(#{data.supplyType}, a.supply_type)           #FIND_IN_SET(str,strlist) ：  str 要查询的字符串，strlist  需查询的字段，参数以”,”分隔，形式如 (1,2,6,8,10,22)；该函数的作用是查询字段(strlist)中是否包含(str)的结果，返回结果为null或记录。

条件键值对（替换where）：choose

<choose>
　　<when>
　　　　...
　　</when>
   <otherwise>
      ...
　　</otherwise>

</choose>

1.2 运维

（1） 批量更新表数据

　　适用场景：将表table_a数据中字段a，批量更新到表table_b中。适用于表迁移。

　　关键字：inner join

　　sql语句：update table_a a   inner join table_b b on b.org_id=a.department_id   set a.department_short_name = b.short_name

　　解释：table_a的department_id和table_b的org_id中值一样，需要将table_b 中idorg_id一样的short_name批量更新到table_a中。

（2）迁移数据，如果存在外键导致导入数据不成功

　　导入前：mysql> set @@global.foreign_key_checks = 0;

　　导入后：mysql> set @@global.foreign_key_checks = 1;

（3）mysql 替换函数replace()

　　UPDATE `table_name` SET `field_name` = replace (`field_name`,’from_str’,’to_str’) WHERE `field_name` LIKE ‘%from_str%’

　　eg. update pro_flow set title = replace(title, '主材', '材料') where type_id=36;

　　说明：table_name —— 表的名字

           　　field_name —— 字段名

          　　 from_str —— 需要替换的字符串

         　　  to_str —— 替换成的字符串

（4）计算数据类

　　格式化日期：DATE_FORMAT(alarm_time, '%Y-%m-%d')

　　保留2位小数：ROUND(100.2345, 2) as count

　　eg：select DATE_FORMAT(alarm_time, '%Y-%m-%d') as time, ROUND(COUNT(x.id)/(select COUNT(1) from alarm_info), 2) as count from alarm_info x group by DATE_FORMAT(x.alarm_time, '%Y-%m-%d'))

（5）将一张表同步到另外一张表中

　　delete from `role_permission` where role_id=15;  //删除表中的条件为role_id=15数据
　　insert into `role_permission` (id, role_id, permission_id) (select id, 15, permission_id from permission)  //将permission中数据同步到role_permission，且role_id=15

  （6）将表中字段增加字符串

 　　UPDATE tb_material_dic SET material_name = CONCAT('gj-', material_name);

（7）在MySQL中，当使用ORDER BY number ASC出现1,10,11,2,21的字符串排序结果时，通常是因为字段number被存储为字符串类型（如VARCHAR），而非数字类型（如INT）。数据库默认按字符的ASCII码排序，导致数字的字典序排列

 　　SELECT * FROM your_table 2ORDER BY CAST(number AS UNSIGNED) ASC;

1.3 设计数据库

（1）按照首字符升序排列：mysql数据库中，若需要按照汉字的拼音排序，用的比较多是在人名的排序中，按照姓氏的拼音字母，从A到Z排序；

        修改表的sql语句：ALTER TABLE `model_basic_info` MODIFY COLUMN manu_name VARCHAR(100) CHARACTER SET gbk COLLATE gbk_chinese_ci;

　　mysql对其排序分两种情况：
　　场景一、存储姓名的字段采用：GBK字符集
　　GBK内码编码时，其本身就采用了拼音排序的方法，
　　“查询语句+ order by name asc”--------- 查询结果按照姓氏的升序排序

　　场景二、存储姓名的字段采用： utf8字符集
　　　　排序的时候需要对字段进行转码。
　　　　“查询语句+order by convert(name using gbk) asc”------查询的结果也是按照姓氏的升序排序

 （2）创建索引：create index filed_id_index on filed_info(filed_id)

1.4（Navicat Premium）操作my sql

 （1）查询mysql中正在执行的sql语句

 　　#show processlist ;

 （2）当数据库链接中有多个数据库时，过滤需要的数据库

 　　# select * from information_schema.processlist where db='gbm_tm';

 （3）最大连接数

　　# show variables like '%max%connec%';  

（4）explain分析执行计划

type 对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

　　查询数据库版本：select @@version; 

　　查询事务级别：select @@tx_isolation;  默认为：REPEATABLE-READ

 

2 mySql三范式

2.1 第一范式(1NF)

　　(必须有主键，列不可分)数据库表中的任何字段都是单一属性的，不可再分

2.2 第二范式(2NF)

　　(当一个表是复合主键时，非主键的字段不依赖于部分主键(即必须依赖于全部的主键字段))

　　数据库表中非关键字段对任一候选关键字段的 都 不存在部分函数依赖

2.3 第三范式(3NF)

　　关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递依赖