Some slightly more complex usage example codes in mysql

Preface

I believe that the syntax of MySQL is not difficult for everyone, but this article mainly shares some related content about the complex usage of MySQL. Through this article, I believe that everyone will have a deeper understanding of MySQL. Let's take a look at the detailed introduction together.

One-to-many data is displayed in one row

GROUP_CONCAT(expr)

1. Table relationships involved: teacher table, teacher_subject_rel table (table of subjects that teachers can teach), subject table
2. Business scenario: You need to pull all teacher numbers (teacher_no) and subject names (subject_name). &nbsp The teacher table (teacher) and subject (teacher_subject_rel) are a one-to-many relationship, and often multiple records of the same teacher appear in a query. We hope to get one data point for each teacher and stitch it into one

1. Basic grammar

group_concat( [DISTINCT] Fields to be connected [Order BY Sorting fields ASC/DESC] [Separator 'Separator'] )

2. Example

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 `subject` s ON tsr.subject_id = s.subject_id
 WHERE
  t.teacher_id = tsr.teacher_id
) AS 'Subject'
FROM
 teacher

Subquery, query temporary table, EXISTS

example

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
   'Pending submission'
  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
  '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_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
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 Associated variable conditional modification

1. Table relationships involved: id_number (ID number) in the user_info table, birth field in the teacher table, and the association relationship user_id = teacher_id
2. Business scenario: Get the date of birth on the user's ID card and update the date of birth in the birth field

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in: