Some slightly more complex usage example code in mysql

Preface

I believe that the grammar of mysql is not difficult for everyone, but this article mainly shares some contents related to the complex usage of mysql. Through this article, I believe you will know more about mysql. Let's take a look at the detailed introduction.

One-to-many data is displayed in a row

GROUP_CONCAT (expr)

1. The table relations involved: teacher table, teacher_subject_ rel table (the list of subjects that teachers can teach), subject table

2. Business scenario: you need to pull the numbers (teacher_no) and subject names (subject_name) of all teachers. & nbsp faculty table (teacher) and discipline (teacher_subject_rel) is an one-to-many relationship, often query multiple pieces of data from the same teacher. We hope to get each teacher a data subject to be spliced into one.

1. Basic grammar

Group_concat ([DISTINCT] the field to be connected [Order BY sort field ASC/DESC] [Separator 'delimiter'])

2. Examples

SELECT t.teacher_id as' teacher id', t.teacher_no 'teacher number, (SELECT GROUP_CONCAT (s.subject_name) FROM teacher_subject_rel tsr LEFT JOIN `instruct`s ON tsr.subject_id = s.subject_id WHERE t.teacher_id = tsr.teacher_id) AS' discipline 'FROM teacher t

Subquery, query temporary table, EXISTS

Examples

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'to be submitted 'WHEN' 2' THEN'to be assigned 'WHEN' 3' THEN 'completed' WHEN'4' THEN 'processing' END statusName CASE o.emergency_degree WHEN'1' THEN 'normal' WHEN'2' THEN 'urgent' WHEN'3' THEN 'very urgent' 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_idFROM 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_idFROM jx_lesson_plan_order oLEFT JOIN student s ON s.student_intention_id = o.student_intention_idLEFT JOIN area an ON s.area_id = a.idWHERE o. STATUS 1AND s.phone = '18501665888'AND o.emergency_degree = 1AND o. STATUS = 2AND s. Exam_year = '2015'AND o.update_time > =' 2018-08-14 20:28:55'AND o.update_time = '2018-08-17 00:00:00'AND a.first_time' 2018-08-20 0000 and info.id_number is not NULL and (info.birth is NULL or t.birth ='') and t.is_train = 1

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.