講師：戴志華 hana@arbor.ee.ntu.edu.tw 國立台灣大學電機工程研究所 Visual Basic 程式設計 講師：戴志華 hana@arbor.ee.ntu.edu.tw 國立台灣大學電機工程研究所

第十四章 資料庫II

SQL ORDER BY: 排序 SELECT au_id, author FROM authors ORDER BY au_id [ASC|DESC]

SQL(cont’d) Order by 之後的欄位可用數字代替 Ex: Select id, name, score from student order by 3 Order by 1, 3, 2

SQL(cont’d) Adodc1.visible=false Adodc1的屬性對話盒RecordSource CommandType=1-adCmdText CommandText= select * from authors Private Sub Command1_Click() Adodc1.RecordSource = Text1.Text Adodc1.Refresh End Sub

SQL(cont’d) Aggregate function(統計用的函數) SUM:計算總和 AVG:計算平均 MAX:找出最大值 MIN:找出最小值 COUNT:計算記錄數量 Distinct Count:找出不同的值，再計算數量

FROM authors WHERE author like ‘a%’ SQL(cont’d) Aggregate function Example SELECT COUNT (id) as count1 from student SELECT COUNT(author) FROM authors WHERE author like ‘a%’ FROM authors WHERE author like ‘a%’ SELECT COUNT(author)

Examples Employee_TBL Id Last_name First_name pager 1 Stephens Tina 2 Plew Linda 3 Glass Brandon 1234567 4 Jacob 7654321 5 Wallace Mariah 6 Spurgeon Tiffany

Examples (cont’d) Select count(id) from Employee_TBL 6 2 Select count(pager) from Employee_TBL 2 Select count(*) from Employee_TBL Select count(distinct (Last_name)) from Employee_TBL 5

Examples Select count(id) as count1 from student where name like ‘小%’ select max ( chinese ) as maxchinese, min(chinese) as minchinese from student select max(chinese) as maxchiniese, min(chinese) as minchinese from student group by class select max(chinese) as maxchiniese, min(chinese) as minchinese, class from student group by class

計算每個出版社(PubID)有出版幾本書 SQL(cont’d) GROUP BY 分組 SELECT SEX, AVG(SCORE) From student GROUP BY SEX ORDER BY 2 SELECT PubID, COUNT(PubID) From Titles GROUP BY PubID ORDER BY PubID 計算每個出版社(PubID)有出版幾本書

SQL(cont’d) Having 子句 Select city, AVG(salary) from emp_tbl Group by city Having AVG(salary) >20000 Where group byHaving order by

SQL(cont’d) 必需同時使用兩個tableJOIN id name dep_id salary 00001 李小輝 A001 計算本書的出版社名字 **先看看這個小例子** 計算每個人部門所在地 SQL(cont’d) 必需同時使用兩個tableJOIN id name dep_id salary 00001 李小輝 A001 10000 00002 連阿戰 8000 00003 陳阿扁 A002 11000 emp dep_id position A001 台北 A002 高雄 dep

SQL(cont’d) SELECT emp.id, dep.position FROM emp, dep WHERE emp.dep_id=dep.dep_id JOIN: (inner join) Join後會產生3*2=6筆記錄 經由where emp.dep_id=dep.dep_id過濾後，剩3筆 Example Select stduent.id,student.name,class.classname, class.tesacher from student,class where student.class=class.class

SQL(cont’d) 00001 李小輝 10000 A001 台北 A002 高雄 00002 連阿戰 8000 00003 陳阿扁 emp.id emp.name emp.salary emp.dep_id dep.dep_id dep.position 00001 李小輝 10000 A001 台北 A002 高雄 00002 連阿戰 8000 00003 陳阿扁 11000

SQL(cont’d) 若欄位名重覆，可加上表格名做為區分 emp.dep_id, dep.dep_id…… 00001 李小輝 10000 emp.id emp.name emp.salary emp.dep_id dep.dep_id dep.position 00001 李小輝 10000 A001 台北 00002 連阿戰 8000 00003 陳阿扁 11000 A002 高雄

SQL(cont’d) SELECT Titles.title, Publishers.name, Publishers.address FROM Titles, Publishers WHERE Titles.PubId=Publishers.PubId biblio.mdb

SQL(cont’d) INSERT:新增一筆資料 INSERT INTO Authors VALUES (2000,‘alex’,0) INSERT INTO Authors(year,name,count) VALUES (2000,‘alex’,0) INSERT INTO Authors select * from Authors_BK where…. (插入另一個表格的資料)

SQL(cont’d) INSERT INTO Student (id,name,phone) VALUES (2006,‘alex’,NULL) VALUES (2006,‘alex’,’’)

SQL(cont’d) DELETE:刪除一些資料 UPDATE:更新資料 DELETE FROM Authors WHERE author like ‘A%’ UPDATE:更新資料 UPDATE Authors SET Author=‘Alex’ , count=7 WHERE au_id=20000

SQL(cont’d) http://www.1keydata.com/tw/sql/sql.html http://www.geocities.com/SiliconValley/Vista/2207/sql1.html http://www.w3schools.com/sql/default.asp

Data Environment 新增Data Environment Project/Add Data Environment 滑鼠移至在專案視窗上的project1，按右鍵，選add，選 data environmemt 新增Data Environment Project/Add Data Environment

Data Environment(2) ConnectionADODC CommandADODC.RecordSource 與資料庫的連線 CommandADODC.RecordSource SQL Table ……

Data Environment(3) Data Grid與Data Environment連結 將DataSource設為DataEnvironment 將DataMember設為想要的Command

Data Report 新增Data Report Project Data Report 若找不到可至”設定使用元件”視窗, click”設計師“, 點選” data report” 即可 新增Data Report Project Data Report

Data Report(2) 功能簡單，但能與Data Environment整合 其它協力廠商的產品 Crystal Report http://www.crystaldecisions.net/products/crystalreports/ VB 6.0 專業版/企業版內附Crystal Report 4.6.1

Data Report(3) 資料來源相關屬性： 設定報表設計時的精確度(twips)： DataSourceData Environment DataMemberCommand 設定報表設計時的精確度(twips)： GridX GridY

Data Report(4) LeftMargin、RightMargin、TopMargin與BottomMargin報表的四邊寬度 ReportWidth報表的寬度

Data Report(5) 方法： Show：報表預覽 PrintReport：報表列印 ExportReport：輸出報表成檔案 Refresh：資料更新

Data Report(6) ExportReport(輸出格式,檔名,覆蓋模式,showDialog,範圍,PageFrom,PageTo) 輸出格式 rptKeyHtml rptKeyUnicodeHTML rptKeyText rptKeyUnicodeText Example DataReport1.ExportReport rptKeyHTML, "test1.htm", True, True 是否出現Save As Dialog

Data Report(7) PrintReport(showDialog,範圍,PageFrom,PageTo) 範圍: rptRangeAllPages rptRangeFromTo

Data Report控制項 Report Header報表封面 Page Header頁首 Detail Header報表內容 Page Footer頁尾 Report Footer 報表封底

RptLable的Caption屬性接受的特殊字元 %d 目前日期（短格式） %p 目前頁碼 %D 目前日期（長格式） %P 總頁數 %t 目前時間（短格式） %i 報表標題 %T 目前時間（長格式） %% 百分比符號

報表製作 台大出版社 作者清單 2000/3/8

報表製作(2) 台大出版社作者清單 1 Bill Gates 2 Michael Jordan …… 第1頁 共10頁

報表製作(3) 報表預覽 Private Sub Form_Click() DataReport1.Show vbModal End Sub

報表製作II OO出版社 ISBN1 書名1 ISBN2 書名2 …… 共oo本 XX出版社 共xx本 共yy本

報表製作II(2) 使用Data Environment Command1 select * from publishers Command2 ( child command of command1 ) select * from titles 設定Command1與Command2的關聯

設定Command1與Command2的關聯