Mysql-select语法笔记

选择，表记为 T，列名记为C，某个具体取值记为X

单表查询

*顺序
SELECT *
FROM T
WHERE C1 = X
ORDER BY C2

*列运算与重命名
SELECT
C3,
C4+10 AS ‘newname’
FROM T
WHERE C1 = X
ORDER BY C2

*去重
SELECT DISTINCT C1
FROM T

*时间
SELECT *
FROM T
WHERE C > ‘1990-01-01’

*多条件（AND > OR，NOT）
SELECT *
FROM T
WHERE NOT (C1 > ‘1990-01-01’ AND C2 >1000)

*多条件（IN）
SELECT *
FROM T
WHERE C IN (‘VA’,‘FL’,‘GA’)
– WHERE C=‘VA’ OR C=‘GL’ OR C = ‘GA’

*多条件（BETWEEN）
SELECT *
FROM T
WHERE C BETWEEN 1000 AND 3000

*通配符（LIKE）
SELECT *
FROM T
WHERE C LIKE ‘b%’
– WHERE C LIKE ‘%b%’
– WHERE C LIKE ‘%b’
– WHERE C LIKE ‘_b’（一个空格表示一个字符，如ab符合，但aab不符合）

*通配符（REGEXP）
SELECT *
FROM T
– WHERE C LIKE ‘%field%’
– WHERE C REGEXP ‘field’
– WHERE C REGEXP ‘^field’（必须以field开头）
– WHERE C REGEXP ‘field$’（必须以field结尾）
– WHERE C REGEXP ‘^field | mac | rose’
– WHERE C REGEXP ‘[gim]e’ （包含ge、ie、me的都会被选上。另一种’e[a-h]’，ea、eb…eh都会被选上）

*NULL操作
SELECT *
FROM T
WHERE C IS NULL

*ORDER BY
SELECT *
FROM T
ORDER BY C2 DESC
– ORDER BY C1 DESC, C2 DESC

*LIMIT
SELECT *
FROM T
LIMIT 3
– LIMIT 6,3（从第6行开始，查询第7，8，9这三行数据）

多表查询

*内连接（INNER JOIN，默认JOIN就是内连接）
SELECT A.C1,T2.C3
FROM T1 AS A
JOIN T2
ON A.C1 = T2.C2

*跨数据库连接，数据库1，2分别记为DB1，DB2
SELECT *
FROM DB1.T1 AS A
JOIN DB2.T2 AS B
ON A.C1 = B.C2

*自连接
SELECT E.C1,M.C1
FROM T E
JPIN T M
ON E.C1 = M.C2

*多表连接
SELECT *
FROM T1
JOIN T2
ON T1.C1 = T2.C1
JOIN T3
ON T1.C2 = T3.C2

*复合连接（复合主键情形）
SELECT *
FROM T1
JOIN T2
ON T1.C1 = T2.C1
AND T1.C2 = T2.C2

*隐式连接
SELECT *
FROM T1,T2 （交叉链接）
WHERE T1.C1 = T2.C1

*外连接（LEFT/RIGHT JOIN）
SELECT *
FROM T1
LEFT JOIN T2
ON T1.C1 = T2.C1
ORDER BY T1.C1

*多表外连接
SELECT *
FROM T1
LEFT JOIN T2
ON T1.C1 = T2.C1
LEFT JOIN T3
ON T3.C2 = T2.C2
ORDER BY T1.C1

*自外连接
SELECT *
FROM T E
LEFT JOIN T M
ON E.C1 = M.C2

*USING子句（名字必须一样）
SELECT *
FROM T1
JOIN T2
– ON T1.C1 = T2.C1
USING (C1)
– USING (C1,C3)（复合主键）
LEFT JOIN T3
USING (C2)

*交叉连接（主键笛卡尔积）
SELECT *
FROM T1
CROSS JOIN T2
– SELECT * FROM T1,T2

*Unions（合并多条查询记录）
SELECT C1 AS newname
FROM T1
UNION
SELECT C2
FROM T2（列的数目必须一致）