- 對數據分組的總結
舉例：統計各個部門的平均工資，并且是大于1000的，并且按照平均工資從高到底排序
mysql> select avg(stsal) as myavgsal, stdepno from staff group by stdepno having myavgsal > 1000 order by myavgsal desc;
+-------------+---------+
| myavgsal | stdepno |
+-------------+---------+
| 7000.200000 | 10 |
| 5350.200000 | 30 |
| 3094.766667 | 20 |
+-------------+---------+
- 笛卡爾積的原理
多表查詢練習： - 顯示員工名，工資，及所在部門的名字
select staff.stname , staff.stsal, department.dname from staff, department where department.deptno = staff.stdepno;
- 顯示部門號為10的部門名，員工名，工資，
select staff.stname , staff.stsal, department.dname from staff, department where department.deptno = staff.stdepno and department.deptno = 10;
- 顯示每個員工的姓名，工資， 及其工資的級別
select staff.stname, staff.stsal, salalevel.slevel from staff, salalevel where staff.stsal >= slower and staff.stsal <= shig;
- 顯示每個員工姓名，工資，以及部門名，以及工資級別
這樣就設計到三張表的關聯查詢，三張表為了避免笛卡爾積， 就需要至少兩個查詢條件
select staff.stname, staff.stsal , department.dname, salalevel.slevel from staff,department,salalevel where staff.stdepno = department.deptno and staff.stsal >= salalevel.slower and staff.stsal <= salalevel.shig;
