MySQL Development skills join update and data check weight/GO heavy _mysql

Mainly involved: Join, join update, GROUP by have data check weight/GO heavy

1 INNER join, left JOIN, right join, full join (MySQL not supported), CROSS join

This is a very good post that is found on the web, illustrated by the join statement:

Coding horror-a Visual explanation of SQL joins

The following figure makes it clear that the data selection for join

[][1]
[1]: Yun_qi_img/160725-imooc-mysql-development-skills-notes-001.png

2 Update uses the filter condition to include its own table

Update col_a duplicate fields in the T1 T2 table

UPDATE T1 
SET col_a = ' Hi ' 
WHERE t1.col_a in (
 SELECT b.col_a to T1
 a INNER JOIN T2 b on
 a.col_a = b . col_a
)
;
error:1093 

can be converted to:

UPDATE T1 AA Join (
 SELECT b.col_a from
 t1 a INNER JOIN T2 b on
 a.col_a = b.col_a
) bb on aa.col_a= BB.C Ol_a
SET col_a = ' Hi ' 
;

3 Querying for duplicate data, deleting duplicate data

Using GROUP by and having queries for duplicate data

SELECT col_a, COUNT (*) from
T1
GROUP by Col_a has count (*) > 1
;

Delete duplicate data, for the same data retention ID maximum

DELETE a
from T1 a JOIN (
 SELECT col_a,count (*), MAX (ID) as ID from
 T1
 GROUP by col_a have COUNT (*) > 1
b on a.col_a = b.col_a
WHERE a.id < b.id
;

Thank you for reading this article, I hope to help you, thank you for your support for this site!