MySQL - select

 

全部顯示

    SELECT * FROM `tb_name`;

全部列出，但只顯示 `欄位1` 和 `欄位2`

    SELECT `欄位1`,`欄位2` FROM `tb_name`;

顯示 `欄位1` 不重覆的全部資料 (DISTINCT)（換成 DISTINCTROW 代表整列都不重覆才顯示）

    SELECT DISTINCT `欄位1` FROM `tb_name`;

限制顯示筆數

    SELECT * FROM `tb_name` limit 1;

僅列出特定條件的資料 (WHERE)

    SELECT * FROM `tb_name` WHERE `欄位1`=數值1 AND `欄位2`='字串1';

除了使用 AND 也可使用 OR、LIKE、>、<、=、IS NULL、NOT、IN('字串a','字串b')

例如：顯示 `欄位2` 包含 '字串1' 的資料（百分比"%"為萬用字串，底線"_"為萬用字元）

    SELECT * FROM `tb_name` WHERE `欄位2` LIKE '%字串1%';

如果真的要找 "%" 和 "_"，要在前面加 "!" (如下是找包含"%字串1"的資料)

    SELECT * FROM `tb_name` WHERE `欄位2` LIKE '%!%字串1%';

搜尋結果再設定特定條件顯示 (HAVING)

    SELECT `欄位2`,SUM(`num1`*`num2`) AS a FROM `tb_name1` WHERE `欄位1`=1
        GROUP BY `欄位2` HAVING `欄位2`='字串1' AND a>30;

HAVING 是建議加在 GROUP BY 的後面，但其實沒有 GROUP BY 也不會有錯

多個table同時搜尋

在 FROM 後面設定別名

    SELECT *,a.`欄位2` FROM `tb_name1` AS a,`tb_name2` AS b WHERE a.`欄位1`=b.`欄位1`;

使用 UNION（若改用 UNION ALL 可列出所有重覆的值）

    SELECT `欄位2` FROM `tb_name1` WHERE `欄位1`=1
        UNION SELECT `欄位2` FROM `tb_name2` WHERE `欄位1`=1;

使用 JOIN

    SELECT * FROM `tb_name1` AS a JOIN `tb_name2` AS b ON a.`欄位1`=b.`欄位1`;

也可以是這樣

    SELECT * FROM (SELECT * FROM `tb_name1` WHERE `欄位1`=1) AS a
    JOIN (SELECT * FROM `tb_name2` WHERE `欄位1`=1) AS b ON a.`欄位1`=b.`欄位1`;

JOIN 可改用
"INNER JOIN" (預設)：會列出左右兩邊同時都有的資料
 "LEFT JOIN"：以左邊table資料為主
 "RIGHT JOIN"：以右邊table資料為主

不列出重覆的 (GROUP BY)

    SELECT * FROM `tb_name1` AS a WHERE `欄位1`=1 GROUP BY `欄位2`;
    SELECT * FROM `tb_name1` AS a WHERE `欄位1`=1 GROUP BY `欄位2`,`欄位3`;

排序 (ORDER BY)

    SELECT * FROM `tb_name1` AS a WHERE `欄位1`=1 ORDER BY `欄位2` ASC;
    SELECT * FROM `tb_name1` AS a WHERE `欄位1`=1 ORDER BY `欄位2` ASC,`欄位3` DESC;

ASC (預設值可省略, 小到大)，DESC(大到小)