工作常用的MySQL語句

文中的大部分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');

需要特殊注意：如果排序欄位的值不在上述列出，則會排在最前面

直接用sql導出數據