mysql学习笔记14 多表查询初步

1. 对数据分组的总结

举例：统计各个部门的平均工资，并且是大于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 |
+-------------+---------+

2. 笛卡尔积的原理
   
   多表查询练习：
3. 显示员工名，工资，及所在部门的名字

select staff.stname , staff.stsal, department.dname from staff, department where department.deptno = staff.stdepno;

4. 显示部门号为10的部门名，员工名，工资，

select staff.stname , staff.stsal, department.dname from staff, department where department.deptno = staff.stdepno and department.deptno = 10;

5. 显示每个员工的姓名，工资， 及其工资的级别

select staff.stname, staff.stsal, salalevel.slevel from staff, salalevel where staff.stsal >= slower and staff.stsal <= shig;

6. 显示每个员工姓名，工资，以及部门名，以及工资级别
   这样就设计到三张表的关联查询，三张表为了避免笛卡尔积， 就需要至少两个查询条件

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;