MySQL的联表更新、联表插入和联表删除

工作常用的MySQL语句

• • Bases
  • • INSERT INTO SELECT(将查找的结果插入目标表)
    • INSERT ON DUPLICATE KEY UPDATE statement（插入更新数据）
    • UPDATE JOIN syntax（联表更新数据）
    • DELETE JOIN with JOIN（联表删除数据）
  • Tips
  • • 1. 比较两个表的数据是不是能够完全关联上
    • 2. 查找单个表里可能存在的重复数据
    • 3. 注册一个值，在下面使用
    • 4. 在mysql里使用**正则**
    • 5. 特殊的排序方式
    • 6. 使用sql进行表备份

文中的大部分SQL摘自MySQL Tutorial

Bases

INSERT INTO SELECT(将查找的结果插入目标表)

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;

INSERT ON DUPLICATE KEY UPDATE statement（插入更新数据）

如果目标表里已经存在相同的主键，则执行下面的更新字段的SQL

INSERT INTO table (column_list)
VALUES (value_list)
[SELECT ...FROM ... WHERE]
ON DUPLICATE KEY UPDATE
   c1 = v1, 
   c2 = v2,
   ...;

UPDATE JOIN syntax（联表更新数据）

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

DELETE JOIN with JOIN（联表删除数据）

DELETE T1[, T2]
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

Tips

1. 比较两个表的数据是不是能够完全关联上

SELECT pk,c1 FROM (  
SELECT t1.pk, t1.c1
   FROM t1
   UNION ALL
   SELECT t2.pk, t2.c1
   FROM t2
)  t
GROUP BY pk, c1
HAVING COUNT(0) = 1
ORDER BY pk

2. 查找单个表里可能存在的重复数据

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;

3. 注册一个值，在下面使用

(可以在同一个会话中直接用@Variable_name调用)

SET @variable_name := value;

SELECT @variable_name := value;

4. 在mysql里使用正则

SELECT 
    productname
FROM
    products
WHERE
    productname REGEXP '^(A|B|C)'
ORDER BY productname;

Metacharacter	Behavior
^	matches the position at the beginning of the searched string
$	matches the position at the end of the searched string
.	matches any single character
[…]	matches any character specified inside the square brackets
[^…]	matches any character not specified inside the square brackets
p1|p2	matches any of the patterns p1 or p2
*	matches the preceding character zero or more times
+	matches preceding character one or more times
{n}	matches n number of instances of the preceding character
{m,n}	matches from m to n number of instances of the preceding character

查找数据的时候 REGEXP，LIKE，IN，=都可以查找
但是需要考虑：效率哪个更高

5. 特殊的排序方式

ORDER BY 
    FIELD(key,'value','value2','value3');

需要特殊注意：如果排序字段的值不在上述列出，则会排在最前面

6. 使用sql进行表备份

CREATE TABLE new_table 
SELECT col, col2, col3 
FROM
    existing_table;

直接用sql导出数据