mysql dba系統學習（23）必須明白的sql知識

必須明白的sql知識

一，兩表外串連查詢

現有兩個表A,B內容如下

mysql> select * from A;+------+------+| id   | Col1 |+------+------+|    1 | AA   ||    2 | BB   ||    3 | CC   |+------+------+3 rows in set (0.00 sec)mysql> select * from B;+------+------+| id   | Col2 |+------+------+|    2 | DD   ||    3 | EE   ||    4 | FF   |+------+------+3 rows in set (0.01 sec)

1，A表和B表左串連

先將左表A）資料查出，然後根據on後面的條件，將右表中凡是id與左表id相等的記錄都查出來，與匹配的左表記錄依次排成一行或多行，若無匹配的記錄，則顯示null。

mysql> select * from A left join B on A.id=B.id;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    1 | AA   | NULL | NULL ||    2 | BB   |    2 | DD   ||    3 | CC   |    3 | EE   |+------+------+------+------+3 rows in set (0.00 sec)mysql> select A.id ID ,A.Col1 C1 ,B.Col2 C2  from A left join B on A.id=B.id;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    1 | AA   | NULL ||    2 | BB   | DD   ||    3 | CC   | EE   |+------+------+------+3 rows in set (0.00 sec)

下面的結果也是一樣的

2，A表和B表右串連

先將右表B）資料查出，然後根據on後面的條件，將左表中凡是id與右表id相等的記錄都查出來，與匹配的左表記錄依次排成一行或多行，若無匹配的記錄，則顯示null

mysql> select * from A right join B on A.id=B.id;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    2 | BB   |    2 | DD   ||    3 | CC   |    3 | EE   || NULL | NULL |    4 | FF   |+------+------+------+------+3 rows in set (0.07 sec)mysql> select A.id ID ,A.Col1 C1 ,B.Col2 C2  from A right  join B on A.id=B.id;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    2 | BB   | DD   ||    3 | CC   | EE   || NULL | NULL | FF   |+------+------+------+3 rows in set (0.00 sec)

3，A表和B表交叉串連

沒有用where子句的交叉串連將產生串連所涉及的笛卡爾積第一個表的行數乘以第二個表的行數等於笛卡爾積和結果集的大小。 Cross join 後面不能跟on 只能用where 。但是如果帶返回或顯示的是匹配的行數。不帶條件where。

mysql> select *   from A cross join B  ;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    1 | AA   |    2 | DD   ||    2 | BB   |    2 | DD   ||    3 | CC   |    2 | DD   ||    1 | AA   |    3 | EE   ||    2 | BB   |    3 | EE   ||    3 | CC   |    3 | EE   ||    1 | AA   |    4 | FF   ||    2 | BB   |    4 | FF   ||    3 | CC   |    4 | FF   |+------+------+------+------+9 rows in set (0.00 sec)

mysql> select A.id ID,A.Col1 C1,B.Col2 C2  from A cross join B  where A.id=B.id ;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    2 | BB   | DD   ||    3 | CC   | EE   |+------+------+------+2 rows in set (0.01 sec)

這裡還有一種交叉串連表示方法

mysql> select * from A,B ;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    1 | AA   |    2 | DD   ||    2 | BB   |    2 | DD   ||    3 | CC   |    2 | DD   ||    1 | AA   |    3 | EE   ||    2 | BB   |    3 | EE   ||    3 | CC   |    3 | EE   ||    1 | AA   |    4 | FF   ||    2 | BB   |    4 | FF   ||    3 | CC   |    4 | FF   |+------+------+------+------+9 rows in set (0.02 sec)mysql> select A.id ID,A.Col1 C1,B.Col2 C2  from A , B  where A.id=B.id ;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    2 | BB   | DD   ||    3 | CC   | EE   |+------+------+------+2 rows in set (0.00 sec)

4，A表和B表完整外部串連查詢mysql 目前不支援）

select * from A full join B on  A.id=B.id+------+------+------+| ID   | C1   | C2   |+------+------+------+|    1 | AA   |      ||    2 | BB   | DD   ||    3 | CC   | EE   ||    4 |      | FF   |+------+------+------+

二，group by if count sum

group by 將查詢的結果按列或者多列的值分組，值相等的為一組

if if運算式，value1，value2） 運算式成立時值是value1 否則是value2

count 計數

sum 求和

某表ps內容如下

mysql> select * from ps;+------------+------+| RQ         | SF   |+------------+------+| 2005-05-09 | s    || 2005-05-09 | s    || 2005-05-09 | f    || 2005-05-09 | f    || 2005-05-10 | s    || 2005-05-10 | f    || 2005-05-10 | f    |+------------+------+7 rows in set (0.00 sec)

希望求到如下結果

    RQ          s        f| 2005-05-09 |  2    |   2    || 2005-05-10 |  1    |   2    |

mysql>  select  RQ,sum(if(SF="s",1,0)) s ,sum(if(SF="f",1,0)) f from ps group by RQ;+------------+------+------+| RQ         | s    | f    |+------------+------+------+| 2005-05-09 |    2 |    2 || 2005-05-10 |    1 |    2 |+------------+------+------+2 rows in set (0.00 sec)

sum(if(SF="s",1,0)) 可以求得s的出現次數

本文出自 “好好活著” 部落格，請務必保留此出處http://wolfword.blog.51cto.com/4892126/1298442