资料库实习作业四Oracle.doc

2008/11資料庫實習作業四Oracle SQL*Plus 作業繳交期限：2008年11月28日 (五)，下午5：00前 作業主題：靜宜運送系統 繳交內容：A. 書面報告封面：註明 第四次實習作業；組別、組員、修課班級、學號及姓名。B. 書面報告內容：(請依以下順序裝訂，5個查詢亦依序排列，自行設計部份請放在最後)（一） 印出建立6個資料表的指令(即檔案內容)，其建立方式如下: 其中3個資料表是以SQL command file的檔名(.sql)來產生，例如：Customer_create.sql、Store_create.sql、Order_create.sql等等。 另外3個資料表需在SQL PLUS視窗中建立，並使用SPOOL指令將過程記錄下來，檔名存成(.lst)，例如：Car_create.lst、Class_create.lst與Dest_create.lst。（二） 印出6個資料表的內容 在SQL PLUS視窗中以指令將所有資料表的內容顯示出來，並以SPOOL指令將所執行的過程與結果以檔案儲存(.lst)。 例如：customer資料表內容：customer_content.lst（三） 印出5個查詢的內容及查詢結果 包括3個基本查詢，2個自行建立查詢，並附上自行設計的說明。 所有查詢需使用SPOOL指令將查詢的內容及結果記錄下來，並將檔案存成(.lst)。l 基本查詢：(參閱 作業二 )查詢一：車種查詢查詢二：2008年某月份送達訂單查詢查詢三：一年內所有訂單運送時間查詢例如: 查詢一：車種查詢：query1.lstB. 檔案：繳交.sql及.lst檔 作業內容說明：1. 建立六個資料表n 每一資料表中必須輸入至少20筆資料，除車種資料表可最少3筆資料。n 資料表內容要與作業一相同 (Hint：將Access資料表匯出成文字檔) n Customer、Store、Order資料表的建立及insert資料將SQL command寫在檔案中，以執行SQL command file的方式來產生。n Car、Class、Dest資料表的建立及insert資料直接下SQL command的方式來產生，並且利用SPOOL指令將操作過程儲存起來。2. 資料欄位說明：資料表一：顧客資料表（Customer）欄位名稱資料型態條件資料輸入Cust_idchar(6)not null, primary keyCust_namechar(16)not nullCust_telchar(13)not nullCust_phonechar(12)not nullCust_addchar(50)not nullCust_mailchar(26)not nullcheck(email like %)電子郵件位址需要有符號補充說明：Cust_id設為主鍵primary keyprimary key(Cust_id)補充說明：like的用法底線_在SQL中代表一個字元、百分比%在SQL中代表多個字元資料表二：營業點資料表（Store）欄位名稱資料型態條件資料輸入Sto_idchar(6)not null, primary keySto_namechar(16)not nullSto_telchar(13)not nullSto_addchar(50)not nullSales_namechar(26)not null 補充說明：Sto_id設為主鍵primary keyprimary key(Sto_id)資料表三：車輛資料表（Car）欄位名稱資料型態條件資料輸入Car_idchar(6)not null, primary keyCar_nochar(7)not nullDriver_namechar(10)not nullSto_idchar(6)not null, foreign keyClass_idchar(6)not null, foreign key補充說明：Car_id設為主鍵primary keyprimary key(Car_id)補充說明：由於Sto_id會參考到營業點資料表的Sto_id欄位，因此必須設為foreign key Ex: foreign key(Sto_id) references Store (Sto_id)【Store表格必須先建立完成！】補充說明：由於Class_id會參考到車種資料表的Class_id欄位，因此必須設為foreign key Ex: foreign key(Class_id) references Class (Class_id)【Class表格必須先建立完成！】資料表四：車種資料表（Class）欄位名稱資料型態條件資料輸入Class_idchar(6)not null, primary keyClass_namechar(50)not null資料表五：送達地資料表（Destination）欄位名稱資料型態條件資料輸入Dest_idChar(6)not null, primary keyDest_namechar(20)not null資料表六：運送單資料表（Order）欄位名稱資料型態條件資料輸入Ord_idChar(6)not null, primary keyCar_idChar(6)not null, foreign keyDest_idChar(6)not null, foreign keyProductChar(50)not nullSend_idChar(6)not null, foreign keySend_dateDatenot nullRece_idChar(6)not null, foreign keyRece_dateDatenot null補充說明：由於Send_date、Rece_date 資料型態為date，因此在insert資料時，必須用TO_DATE函數，此函數是將字元資料型態轉成時間型態；函數第一項參數是字元型態的日期，第二個參數是日期資料儲存的格式。例如：TO_DATE(1976/1/1, yyyy/mm/dd)補充說明：由於Send_id、Rece_id會參考到顧客資料表的Cust_id欄位，因此必須設為foreign key由於Car_id會參考到車輛資料表的Car_id欄位，因此必須設為foreign key由於Dest_id會參考到送達地資料表的Dest_id欄位，因此必須設為foreign key3. 五個查詢：u 完成指定的3個基本查詢，並且另行設計2個查詢,請將所有的查詢SQL command及結果利用SPOOL指令存成檔案。(.lst)u 自行建立的查詢必須有一個使用到outer join。u 自行建立的查詢必須有一個使用到排序(order by )。4. 基本查詢(重點提示)u 查詢所需的欄位是來自哪些資料表？這些資料表出現在保留字FROM之後。u 兩個資料表間的關聯，需透過相同的欄位來完成。例如：其中，在where中的寫法如下： Where class.class_id=car.class_id 即代表class資料表與car資料表的關聯。class_name like &請輸入車種即代表可以讓使用者自行輸入數值，取代原來的值。u 時間部份：Send_date between add_months(sysdate,-12) and sysdatesysdate代表取得系統目前時間add_months的語法為add_months(初始時間, 數量)to_char(Rece_date,yyyy/mm/dd) like 2008/&請輸入月份/%to_char(日期, 格式) 可將日期轉換成所需要的格式，並輸出成字串。yyyy/mm/dd代表格式為年/月/日。月份要求輸入，所以使用&符號。u outer-join(外部合併)例如：select t1.id,t2.idfrom test t1,test2 t2where t1.id=t2.id(+)；where t1.id=t2.id(+)； =注意”(+)”的位置u 排序的語法： ORDER BY field_name1, file_name2, ASC|DESC ASC：由小到大（系統預設值） DESC：由大到小 start filename（儲存檔案之完整路徑名稱）例如：step1. 建立test.sql檔，內容如下：step2. 於SQL*Plus提示符號下，以START指令來執行：u SPOOL指令：將使用 SQL*Plus時所下的命令與結果儲存成紀錄檔。(.lst) 使用方法：SQLspool filename（欲儲存命令與結果檔案之完整路徑名稱） SQL撰寫SQL*Plus命令SQL*Plus指令結果顯示SQLspool off例如：SQLspool D:teacher_creatSQL create table teacher 2 (teacher_id char(9) not null, 3 name char(16) not null, 4 dept_id char(4) not null, 5 office char(16) not null,.SQL spool off如上所示，會產生一個teacher_create.lst的檔案，在d:目錄下，其內容如下圖：u 將Access資料表匯入SQL*Plus1. 開啟Access之資料表。2. 選擇【檔案】【匯出】【存檔】，選擇存檔類型為文字檔案（*.txt）選擇【分隔符號字元如逗號或是Tab所分隔的欄位】【下一步】選擇【欄位分隔字元：逗點】【字串辨識符號：單引號】設定匯出檔案之路徑。3. 於notepad中，加入正確之SQL指令。7 SQL重點整理：1.大小寫字母均可2.符號：逗號【，】：分開命令行中的項目。括號【（）】：分開個別的元件，如欄位規格，子查詢命令等。分號【；】：SQL命令結束表示法3.表格命名限制：l 必須以英文字母開始l 長度不可超過30個字元l 不可有空白字元l 不可與其他已存在的表格名稱重複l 不可使用Oracle的保留字 補充說明: 以下關於語法部份，為了方便說明，故以大寫字母來代表指定字， 代表可寫可不寫l 資料表n 建立資料表： CREATE TABLE table_name ( column_name type(size) NULL/NOT NULL， column_name type(size) NULL/NOT NULL， ，column_name type(size) NULL/NOT NULL )；n 刪除資料表DROP TABLE table_name；例如：n 更改資料表名稱：RENAME oldname TO newname；例如：n 查詢資料表結構DESC table_name；n 顯示所有已建立的資料表SELECT * FROM TAB；l 欄位n 刪除資料表的某一個欄位ALTER TABLE table_name DROP (column_name)；例如：n 新增資料表的某一個欄位ALTER TABLE table_name ADD(column type) table constraint)；例如：n 修改資料表內某一個欄位定義ALTER TAB