第四章sql查询

/*第四章sql查询**/

/*1.基本查询*/

select t.teamname,sum(score) "totalscore" from team t,users u

where t.leader = u.id

GROUP BY t.teamname;

/*select DISTINCT t.*,u.* from team t,users u where u.id=t.leader;*/

/*分组-group by 子句*/

/*

【示例】表employees 与表salary中分别存储员工和工资信息，现需获得每位员工的工资总额

select e.emp_id,e.emp_name,sum(s.salary) total_salary from emp e,salary s

where e.emp_id=s.emp_id

group by e.emp_id,e.emp_name;

详解：

1.获取from子句数据源

2.where子句筛选

3.group by分组,组合工资合并

*/

select dept "部门",AVG(salary) as "部门工资" from staff 

GROUP BY dept

HAVING AVG(salary)>3000 ORDER BY dept DESC;

/*记录【笛卡尔积】：两个数据表的乘积-第一表的每条记录与第二标的每条记录组合所获得结果集*/

/*2.子查询

3.联合语句

4.关联语句:多表/视图之间的关联关系

5.层次化查询：树状结构查询

**/