T1 { [INNER]| { LEFT|RIGHT|FULL } [OUTER] } JOIN T2 ON boolean_expression
USING(joincolumn list )// USING 是 on 的简写 USING (a, b) 等于 ON T1.a = T2.a AND T1.b = T2.b。NATURALJOIN T2 // NATURAL是 USING 的简写,两个表里都出现了的列名组成
T1表
num | name
-----+------1| a
2| b
3| c
T2表
num |value-----+-------1| xxx
3| yyy
5| zzz
=>SELECT*FROM t1 CROSSJOIN t2;
num | name | num |value-----+------+-----+-------1| a |1| xxx
1| a |3| yyy
1| a |5| zzz
2| b |1| xxx
2| b |3| yyy
2| b |5| zzz
3| c |1| xxx
3| c |3| yyy
3| c |5| zzz
(9rows)=>SELECT*FROM t1 INNERJOIN t2 ON t1.num = t2.num;
num | name | num |value-----+------+-----+-------1| a |1| xxx
3| c |3| yyy
(2rows)=>SELECT*FROM t1 INNERJOIN t2 USING(num);
num | name |value-----+------+-------1| a | xxx
3| c | yyy
(2rows)=>SELECT*FROM t1 NATURALINNERJOIN t2;
num | name |value-----+------+-------1| a | xxx
3| c | yyy
(2rows)=>SELECT*FROM t1 LEFTJOIN t2 ON t1.num = t2.num;
num | name | num |value-----+------+-----+-------1| a |1| xxx
2| b ||3| c |3| yyy
(3rows)=>SELECT*FROM t1 LEFTJOIN t2 USING(num);
num | name |value-----+------+-------1| a | xxx
2| b |3| c | yyy
(3rows)=>SELECT*FROM t1 RIGHTJOIN t2 ON t1.num = t2.num;
num | name | num |value-----+------+-----+-------1| a |1| xxx
3| c |3| yyy
||5| zzz
(3rows)=>SELECT*FROM t1 FULLJOIN t2 ON t1.num = t2.num;
num | name | num |value-----+------+-----+-------1| a |1| xxx
2| b ||3| c |3| yyy
||5| zzz
(4rows)
一些常用的WHERE子句例子
SELECT...FROM fdt WHERE c1 >5SELECT...FROM fdt WHERE c1 IN(1,2,3)SELECT...FROM fdt WHERE c1 IN(SELECT c1 FROM t2)SELECT...FROM fdt WHERE c1 IN(SELECT c3 FROM t2 WHERE c2 = fdt.c1 +10)SELECT...FROM fdt WHERE c1 BETWEEN(SELECT c3 FROM t2 WHERE c2 = fdt.c1 +10)AND100SELECT...FROM fdt WHEREEXISTS(SELECT c1 FROM t2 WHERE c2 > fdt.c1)
GROUP BY例子
=>SELECT*FROM test1;
x | y
---+---
a |3
c |2
b |5
a |1(4rows)=>SELECT x FROM test1 GROUPBY x;
x
---
a
b
c
(3rows)=>SELECT x,sum(y)FROM test1 GROUPBY x;
x | sum
---+-----
a |4
b |5
c |2(3rows)=>SELECT x,sum(y)FROM test1 GROUPBY x HAVINGsum(y)>3;
x | sum
---+-----
a |4
b |5(2rows)=>SELECT x,sum(y)FROM test1 GROUPBY x HAVING x <'c';
x | sum
---+-----
a |4
b |5(2rows)