查詢資料表 (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;
參考資源

更新日期:

google 論壇

App javascript (groups.google.com/group/app-javascript/)