chapter14.資料庫ii.pptVIP

Visual Basic 程式設計 講師：戴志華 hana@.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 SQL(cont’d) Aggregate function(統計用的函數) SUM:計算總和 AVG:計算平均 MAX:找出最大值 MIN:找出最小值 COUNT:計算記錄數量 Distinct Count:找出不同的值，再計算數量 SQL(cont’d) Aggregate function Example SELECT COUNT (id) as count1 from student SELECT COUNT(author) FROM authors WHERE author like ‘a%’ Examples 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 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 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 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 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,,class.classname, class.tesacher from student,class where student.class=class.class SQL(cont’d) SQL(cont’d) 若欄位名重覆，可加上表格名做為區分 emp.dep_id, dep.dep_id…… SQL(cont’d) SELECT Titles.title, P, Publishers.address FROM Titles, Publishers WHERE Titles.PubId=Publishers.PubId SQL(cont’d) INSERT:新增一筆資料 INSERT INTO Authors VALUES (2000,‘al