mysql中的一些稍微复杂用法实例代码

前言

mysql的语法相信对大家来说都不是难事，但是本文主要给分享了一些mysql复杂用法的相关内容，通过这篇文章相信大家会对mysql更深的了解一些，下面话不多说了，来一起看看详细的介绍吧

一对多数据显示成一行

GROUP_CONCAT(expr)

1、涉及的表关系:teacher表、teacher_subject_rel表(教师所能教的学科表)、subject表
2、业务场景: 需要拉取所有教师的编号(teacher_no)、学科名(subject_name)。   教师表(teacher)和学科(teacher_subject_rel)是一对多关系, 往往查询出现的是同一教师多条 数据。我们希望得到每个教师一条数据 学科拼接成一条

1、基本语法

group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )

2、例子

  SELECT   t.teacher_id as '教师id',   t.teacher_no '教师编号',   (   SELECT    GROUP_CONCAT(s.subject_name)   FROM    teacher_subject_rel tsr   LEFT JOIN `subject` s ON tsr.subject_id = s.subject_id   WHERE    t.teacher_id = tsr.teacher_id  ) AS '学科'  FROM   teacher t

子查询、查询临时表、EXISTS

例子

  SELECT   *  FROM   (    SELECT     o.id,     o.student_intention_id,     s. NAME,     s.area_id,     a.area_name,     s.exam_year,     o. STATUS,     CASE o. STATUS    WHEN '1' THEN     '待提交'    WHEN '2' THEN     '待指派'    WHEN '3' THEN     '已完成'    WHEN '4' THEN     '处理中'    END statusName,    CASE o.emergency_degree   WHEN '1' THEN    '正常'   WHEN '2' THEN    '紧急'   WHEN '3' THEN    '非常紧急'   END emergencyDegreeName,   o.emergency_degree,   o.update_time,   (    SELECT     first_lesson_time    FROM     jx_strategy    WHERE     jx_lesson_plan_order_id = o.id    AND STATUS IN (2, 7)    AND first_lesson_time > now()    ORDER BY     first_lesson_time ASC    LIMIT 1   ) AS first_time,   (    SELECT     deal_user_id    FROM     jx_strategy    WHERE     jx_lesson_plan_order_id = o.id    AND STATUS <> 7    AND deal_user_id <> 0    ORDER BY     id DESC    LIMIT 1   ) AS deal_user_id  FROM   jx_lesson_plan_order o  LEFT JOIN student s ON s.student_intention_id = o.student_intention_id  LEFT JOIN area a ON s.area_id = a.id  WHERE   o. STATUS <> 1  AND s.phone = '18501665888'  AND o.emergency_degree = 1  AND o. STATUS = 2  AND s.exam_year = '2015'  AND o.update_time >= '2018-08-14 20:28:55'  AND o.update_time <= '2018-08-14 20:28:55'   ) AS a  WHERE   1 = 1  AND a.deal_user_id = 145316  AND a.first_time >= '2018-08-17 00:00:00'  AND a.first_time <= '2018-08-30 00:00:00'  AND EXISTS (   SELECT    *   FROM    jx_strategy js   WHERE    js.jx_lesson_plan_order_id = a.id   AND js. STATUS IN (2, 7)   AND js.subject_id IN (2, 3)  )  ORDER BY   a.update_time DESC  LIMIT 0,   10

update 关联变量条件修改

1、涉及的表关系: user_info表中的 id_number(身份证号) teacher表中的birth字段、 关联关系usrer_id = teacher_id
2、业务场景：获取用户身份证上的出生日期将出生日期更新在birth字段

  UPDATE teacher t INNER JOIN (    SELECT t.teacher_id, t.birth, u.id_number, CONCAT(SUBSTRING(u.id_number, 7, 4), '-', SUBSTRING(u.id_number, 11, 2), '-', SUBSTRING(u.id_number, 13, 2)) as birth1, u.reg_date, t.exit_time from teacher t  INNER JOIN user_info u ON u.user_id = t.teacher_id    ) info on info.teacher_id = t.teacher_id  SET t.birth = info.birth1  WHERE info.reg_date > '2018-08-20 00:00:00' and info.id_number is not NULL and (info.birth is NULL or t.birth = '') and t.is_train = 1