1 / 26

Visual Basic 程式設計

Visual Basic 程式設計. 講師：戴志華 hana@arbor.ee.ntu.edu.tw 國立台灣大學電機工程研究所. 第十五章 資料庫 III. SQL. 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.

tyanne
Download Presentation

Visual Basic 程式設計

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 第十五章 資料庫III SQL

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

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

  5. 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

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

  7. 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)

  8. Examples Employee_TBL

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

  10. 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

  11. 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)有出版幾本書

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

  13. 計算本書的出版社名字 **先看看這個小例子** 計算每個人部門所在地 SQL(cont’d) • 必需同時使用兩個tableJOIN emp dep

  14. SQL(cont’d) • SELECT emp.id, dep.position FROM emp, depWHERE 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

  15. SQL(cont’d)

  16. SQL(cont’d) • 若欄位名重覆，可加上表格名做為區分 • emp.dep_id, dep.dep_id……

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

  18. 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…. (插入另一個表格的資料)

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

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

  21. 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

  22. 第十五章 資料庫III Recordset

  23. Recordset.AddNew • Recordset.Delete • Recordset.Edit • Recordset.Update

  24. Recordset.MoveFirst • Recordset.MoveLast • Recordset.MoveNext • Recordset.MovePrevious

  25. Recordset.Filter • Recordset.Find • Find (條件設定式, 略過資料筆數, 搜尋方向, 搜尋起始處) • adSearchForward, adSearchBackward • Recordset.Bookmark

  26. Dim bm as Variant xxx.Recordset.MoveFirst xxx.Recordset.Find “Sex = ‘True’” While Not xxx.Recordset.EOF xxx.Recordset.Find “Sex = ‘True’”, 1,_ adSearchForward, xxx.Recordset.Bookmark Wend

More Related