[Access]資料庫 SQL 語法範例

這篇盡量用最簡單的範例來展示,所以不可避免的,選上了成績資料為展示的目標,當然實際上運作可能包含更多的欄位,範例則是盡量精簡。一般來說資料庫考慮到各程式語言的相容性、可遷移性,盡量不用中文,範例就不太講究了,因為中文也是一種註解,就偷個懶。
 
資料表:

清單為記錄所有學生各科的成績

清單
DBID 姓名 科目 分數
1 小明 國文 95
2 小華 國文 100
3 小英 國文 87
4 小明 英文 99
5 小華 英文 94
7 小明 數學 91
8 小華 數學 96
9 小英 數學 89
 
分數級距為老師評鑑成績的名稱:

分數級距
DBID 級距 上限 下限
1 滿分 101 100
2 100 95
3 須加強 95 0
 
 

1. 要取得學生清單
SQL 語法
SELECT DISTINCT 清單.姓名
FROM 清單;

名冊
姓名
小明
小英
小華
 
2. 每個學生的平均
SQL 語法
SELECT 姓名, AVG(分數) AS 平均
FROM 清單
GROUP BY 姓名;

各人平均
姓名 平均
小明 95
小英 88
小華 96.6666666666667
 
3. 替學生各科成績加上中文評鑑標語:
SQL 語法
SELECT 清單.姓名, 清單.科目, 清單.分數, 分數級距.級距
FROM 清單 LEFT JOIN 分數級距 ON 清單.分數<分數級距.上限 AND 清單.分數>=分數級距.下限;

分數評鑑
姓名 科目 分數 級距
小明 國文 95
小華 國文 100 滿分
小英 國文 87 須加強
小明 英文 99
小華 英文 94 須加強
小明 數學 91 須加強
小華 數學 96
小英 數學 89 須加強
 
4. 各科成績表,不含平均:
SQL 語法
SELECT 名冊.姓名, 國文成績表.分數 AS 國文, 英文成績表.分數 AS 英文, 數學成績表.分數 AS 數學
FROM (((名冊
   LEFT JOIN 清單 AS 國文成績表 ON (名冊.姓名=國文成績表.姓名 AND 國文成績表.科目=’國文’))
   LEFT JOIN 清單 AS 英文成績表 ON (名冊.姓名=英文成績表.姓名 AND 英文成績表.科目=’英文’))
   LEFT JOIN 清單 AS 數學成績表 ON (名冊.姓名=數學成績表.姓名 AND 數學成績表.科目=’數學’))
;

各科成績表
姓名 國文 英文 數學
小明 95 99 91
小英 87 89
小華 100 94 96
 
5. 成績總表,各科成績加上總平均,這裡偷個懶,我將各科成績表、各人平均都建立成 View (檢視),實際上可純靠 SQL 語法兜出來,只是不想讓 SQL 語法太長:
SQL 語法
SELECT 各科成績表.*, 各人平均.平均
FROM 各科成績表 LEFT JOIN 各人平均 ON 各科成績表.姓名=各人平均.姓名
ORDER BY 各人平均.平均 DESC;

成績總表
姓名 國文 英文 數學 平均
小華 100 94 96 96.6666666666667
小明 95 99 91 95
小英 87 89 88
 
6. 最後要做成績分布表:
SQL 語法
SELECT 分數級距.級距, 國文級距表.國文, 英文級距表.英文, 數學級距表.數學
FROM (((分數級距
   LEFT JOIN (SELECT 級距, Count(級距) AS 國文 FROM 分數評鑑 WHERE 科目=’國文’ GROUP BY 級距)  AS 國文級距表 ON 分數級距.級距=國文級距表.級距)
   LEFT JOIN (SELECT 級距, Count(級距) AS 英文 FROM 分數評鑑 WHERE 科目=’英文’ GROUP BY 級距)  AS 英文級距表 ON 分數級距.級距=英文級距表.級距)
   LEFT JOIN (SELECT 級距, Count(級距) AS 數學 FROM 分數評鑑 WHERE 科目=’數學’ GROUP BY 級距)  AS 數學級距表 ON 分數級距.級距=數學級距表.級距)
ORDER BY 分數級距.DBID;

各科級距表
級距 國文 英文 數學
滿分 1
1 1 1
須加強 1 1 2
 

目前想到跟成績有關的大概是這些,如果有再想到新的,再補進本文。
廣告
Categories: 資料庫 | 4 則迴響

文章分頁導航

4 thoughts on “[Access]資料庫 SQL 語法範例

  1. Jack

    您好:
    非常感謝您的文章分享,但好像還缺少交代"國文級距表.國文, 英文級距表.英文, 數學級距表.數學"的建表。可否再加補?謝謝您!

    按讚數

    • 這篇寫完很久了,我也忘了當初怎樣做的。
      看起來應該是把上面的範例 3 分數級距表轉換成各科的級距表而已。

      按讚數

  2. 我找到之前做範例的 Access 檔案,看起來應該是當時我測試 Access 2010 用的,所以是 .accdb 檔,我不記得後來我還幹嘛過,因為我開之前,檔案日期是 2011 年,而非 2010 年的,如果有出入的話,我也不確定是怎樣了…
    下載檔案位置:https://skydrive.live.com/#cid=145419920BFD55A7&id=145419920BFD55A7%216801

    另外這篇後續的文章為:
    [SQL] SQL Server 與 Access 不同的怪地方:LEFT JOIN
    https://tlcheng.wordpress.com/2010/07/29/sql-sql-server-%e8%88%87-access-%e4%b8%8d%e5%90%8c%e7%9a%84%e6%80%aa%e5%9c%b0%e6%96%b9%ef%bc%9aleft-join/

    至於2011被變更過,我懷疑我可能是拿這個檔案測這篇用:
    [SQL] Access 在 LEFT JOIN 巢狀語法中使用 INNER JOIN
    https://tlcheng.wordpress.com/2011/08/20/sql-access-%e5%9c%a8-left-join-%e5%b7%a2%e7%8b%80%e8%aa%9e%e6%b3%95%e4%b8%ad%e4%bd%bf%e7%94%a8-inner-join/

    按讚數

  3. 引用通告: [SQL] SQL Server 與 Access 不同的怪地方:LEFT JOIN | 鄭子璉

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s

在 WordPress.com 建立免費網站或網誌.

%d 位部落客按了讚: