查詢資料表 (SELECT)

選用資料庫

USE practice

多個資料表查詢

SELECT ac_basic.id, ac_basic.email, ac_basic.nick, district_tw.district, status_affect.affect
FROM ac_basic, district_tw, status_affect
WHERE ac_basic.district=district_tw.id AND ac_basic.affect=status_affect.id;

SELECT ac.id, ac.email, ac.nick, dt.district, af.affect
FROM ac_basic ac, district_tw dt, status_affect af
WHERE ac.district=dt.id AND ac.affect=af.id;

關聯資料表查詢

參照欄位連接關聯資料表查詢 (JOIN…ON)

SELECT ac.email, ac.nick, dt.district AS residence, af.affect FROM ac_basic ac
JOIN district_tw dt ON dt.id=ac.district
JOIN status_affect af ON af.id=ac.affect
ORDER BY residence;

增加條件查詢 (WHERE)

SELECT ac.email, ac.nick, dt.district AS residence, af.affect FROM ac_basic ac
JOIN district_tw dt ON dt.id=ac.district
JOIN status_affect af ON af.id=ac.affect
WHERE dt.district LIKE '%北%'
ORDER BY residence;

保留關聯表連接參照表查詢 (LEFT JOIN…ON)

SELECT ac.email, ac.nick, dt.district AS residence, af.affect FROM ac_basic ac
LEFT JOIN district_tw dt ON dt.id=ac.district
LEFT JOIN status_affect af ON af.id=ac.affect
ORDER BY residence;

SELECT ac.email, ac.nick, dt.district AS residence, af.affect FROM ac_basic ac
JOIN district_tw dt ON dt.id=ac.district
LEFT JOIN status_affect af ON af.id=ac.affect
ORDER BY residence,ac.email;

SELECT ac.email, ac.nick, dt.district AS residence, af.affect FROM ac_basic ac
JOIN status_affect af ON af.id=ac.affect
LEFT JOIN district_tw dt ON dt.id=ac.district
ORDER BY residence,ac.email;

使用函數統計 (aggregate) 查詢

COUNT(*) 列出非 NULL 筆數

SELECT COUNT(*) AS rows FROM ac_basic;
SELECT COUNT(district) AS rows FROM ac_basic;
SELECT email,nick,COUNT(*) AS matched FROM ac_basic
WHERE nick LIKE '%惠%';
SELECT COUNT(DISTINCT affect) AS rows FROM ac_basic;

參考資源

• SELECT

更新日期： 2014-8-24

• 目錄