一、 實驗目的:
1 熟悉Access軟件操作環境。
2 掌握在Access中新建數據庫、新建表及表結構設計,新建窗體及利用窗體錄入數據,新建報表及新建頁等基本操作。
3 掌握在Access中通過SQL進行查詢、插入、更新和刪除。
二、實驗設備:Access2003軟件
三、實驗步驟和結果:
首先建立要用到的訂單管理系統數據庫,其中主要用到的兩張主要表有“產品”表和“訂單”表,如下圖所示:
1、利用SQL創建產品信息數據表:
(1)打開“訂單管理系統”數據庫。
(2)選擇“查詢”對象,單擊“新建”按鈕,在出現的“新建查詢”對話框中選擇“設計視圖”選項,單擊“確定”按鈕。
(3)在出現的“顯示錶”對話框中直接單擊“關閉”按鈕;
(4)右擊,從彈出的菜單中選擇“SQL特定查詢”子菜單中的“數據定義”命令。
(5)在SQL視圖中輸入如下圖所示的語句:
(6)單擊工具欄中的“運行”按鈕,執行該SQL語句;
(7)保存該查詢或直接退出,打開表結構
2、添加和刪除一個“供貨商”字段:在出現的“數據定義查詢”對話框中直接輸入數據定義SQL語句,如下圖所示,執行該SQL語句後,“產品_SQL”表就添加上了“供貨商”字段,如下圖所示。
對於“產品_SQL”表,刪除“供貨商”字段,其操作步驟同上,在出現的“數據定義查詢”對話框中直接輸入數據定義SQL語句,如下圖所示。執行該SQL語句後,“產品_SQL”表就刪除了“供貨商”字段。刪除“產品_SQL”數據表的操作步驟同上,輸入的SQL語句如下圖所示。運行後,“產品_SQL”表不再存在。
3、在“訂單1”表中將“謝先生”的公司改爲“嘉元實業”,對應的SQL命令如下:
UPDATE 訂單1 SET 公司名稱="嘉元實業" WHERE 姓名="謝先生"
“訂單1”表如下圖所示,該SQL語句運行後,“訂單1”表中的記錄如下所示,從中看到已修改了第一條記錄。
4、實現向“訂單1”中刪除姓林的訂單記錄功能,對應的SQL命令如下:
DELETE FROM 訂單1 WHERE 姓名 like"林*"
該SQL語句運行後,“訂單1”表中的記錄如下圖所示,從中看到已刪除了一條記錄。
5、實現向“訂單1”表中插入一條訂單記錄功能,對應的SQL命令如下:
INSERT INTO student1 VALUES("16","靜心軒","林女士","4800","18")
該SQL語句運行後,“訂單1”表中的記錄如下圖所示,從中看到已添加了一條記錄。
6、投影查詢:實現輸出“產品”表中所有記錄的“產品名稱”、“產品單價”功能,對應的SQL命令如下:
SELECT 產品名稱,產品單價 FROM 產品
查詢運行如果如下圖所示:
如果要去掉重複的顯示行,可以在字段名前加上DISTINCT關鍵字來說明。
實現輸出“訂單1”表所有數量不重複的“數量”列功能,對應的SQL命令如下:
SELECT DISTINCT 產品單價
FROM 產品
查詢運行如果如下圖所示:
7、實現輸出“產品”表中單價在10~20之間的所有記錄功能,對應的SQL命令如下:
SELECT * FROM 產品 WHERE 產品單價 BETWEEN 10 AND 20
查詢運行如果如下圖所示:
8、實現輸出“產品”表中單價爲10、15或20的記錄功能,對應的SQL命令如下:
SELECT * FROM 產品 WHERE 產品單價 IN (10,15,20)
查詢運行如果如下圖所示:
9、實現輸出“產品”表中單價爲10或產品名稱爲“蘋果汁”的記錄功能,對應的SQL命令如下:
SELECT * FROM 產品 WHERE 產品單價 LIKE"1*"
查詢運行如果如下圖所示:
10、排序查詢:實現以產品單價降序輸出“產品”表的所有記錄功能,對應的SQL命令如下:
SELECT * FROM 產品 ORDER BY 產品單價 DESC
查詢運行如果如下圖所示:
實現輸出產品名稱升序、產品單價降序顯示所有記錄功能,對應的SQL命令如下:
SELECT * FROM 產品 ORDER BY 產品名稱,產品單價 DESC
查詢運行如果如下圖所示:
11、使用聚合函數實現數據統計:
(1)實現輸出“產品”表產品單價大於10的產品個數功能,對應的SQL命令如下:
SELECT COUNT(*) AS 產品單價大於10
FROM 產品
WHERE 產品單價>10
查詢運行如果如下圖所示:
(2)實現輸出“產品”表產品單價最高的功能,對應的SQL命令如下:
SELECT MAX(產品單價) AS 最高產品單價
FROM 產品
查詢運行如果如下圖所示:
(3)實現輸出“產品”表產品單價平均價格記錄的功能,對應的SQL命令如下:
SELECT AVG(產品單價) AS 產品平均價格
FROM 產品
查詢運行如果如下圖所示:
(4)實現輸出“產品”表中產品單價大於10的平均價格記錄功能,對應的SQL命令如下:
SELECT 產品名稱,產品單價
FROM 產品
WHERE(((產品單價)>( select AVG (產品單價) FROM 產品)))
ORDER BY 產品單價 DESC
查詢運行如果如下圖所示:
(5)實現輸出“產品”表中產品單價大於等於12且小於等於15的記錄功能,對應的SQL命令如下:
SELECT 產品.產品單價
FROM 產品
GROUP BY 產品.產品單價
HAVING MIN(產品.產品單價)>=12 AND MAX(產品.產品單價) <=15
查詢運行如果如下圖所示:
12、表的連接查詢:
(1)實現輸出所有“訂單”表中與“產品ID”相對應的“產品名稱”記錄的功能,對應的SQL命令如下:
SELECT 訂單.[訂單 ID],產品.產品名稱
FROM 產品,訂單
WHERE 產品.產品ID=訂單.[產品 ID]
查詢運行如果如下圖所示:
(2)實現輸出所有產品的產品ID、數量和產品單價列的功能,對應的SQL命令如下:
SELECT x.產品ID,y.數量, x.產品單價
FROM 產品 x,訂單 y
WHERE x.產品ID=y.[產品 ID]
查詢運行如果如下圖所示:
(3)實現輸出所有產品的產品ID、數量和產品單價列的功能,對應的SQL命令如下:
SELECT y.[客戶 ID],avg(y.數量) AS 平均數量
FROM 產品 x,訂單 y
WHERE x.產品ID=y.[產品 ID] AND x.產品單價=10
GROUP BY y.[客戶 ID]
查詢運行如果如下圖所示:
(4)實現連接“產品”和“訂單”表,輸出相同產品ID記錄的功能,對應的SQL命令如下:
SELECT *
FROM 產品,訂單
WHERE