mogotemplate 多表联查 多表联查group by

多行函数

--作用域多行记录，返回一个值

No	名称	类型	描述
1	EMPNO	NUMBER(4)	编号：四位数
2	ENAME	VARCHAR2(10)	姓名：10位数字符
3	JOB	VARCHAR2(9)	职位
4	MGR	NUMBER(4)	领导编号：领导也是公司员工
5	HIREDATE	DATE	入职日期
6	SAL	NUMBER(7,2)	基本工资，两位小数，五位整数，共七位
7	COMM	NUMBER(7,2)	年终奖
8	DEPTNO	NUMBER(2)	员工所在部门编号

图1-1（emp员工表）

分组函数：作用于多行，返回一个值。

l 统计记录数count(*);注：该函数忽略null值;

l 最小值查询min();

l 最大查询max();

l 查询平均值avg();

l 求和函数sum();

Group by 可以按指定的列将数据分成若个组，然后对组内数据进行多行函数统计。

Eg:查询每个部门的人数：select deptno,count(*) from emp group by deptno;

Eg:查询每个部门的平均工资：select deptno,avg(sal) from emp group by deptno;

• 如使用分组函数，sql只能将group by 分组条件字段和分组函数查询出来，不能有其他字段。

Eg:select deptno,job,avg(sal) from emp group by deptno;--错误

Eg:select deptno,job,avg(sal) from emp group by deptno,job;--正确

• 如使用分组函数，不使用group by 的字段，只可以查询出分组函数的值；

Eg:select avg(sal) from emp;

过滤分组数据

Eg:查询出部门平均工资大于2000的部门，用having和where都可以实现：

Select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

Select * from (Select deptno,avg(sal) sal from emp group by deptno )where sal>2000;

Eg:查询部门员工工资大于1500的部门平均工资：

Select deptno,avg(sal) from emp where sal>1500 group by deptno;

多表查询

--作用域多行记录，返回一个值

NO	名称	类型	描述
1	DEPTNO	NUMBER(2)	表示部门编号，由两位数字组成
2	DNAME	VARCHAR2(14)	部门名称，最多由14个字符组成
3	LOC	VARCHAR2(13)	部门所在位置

图1-1部门表（dept）

No	名称	类型	描述
1	EMPNO	NUMBER(4)	编号：四位数
2	ENAME	VARCHAR2(10)	姓名：10位数字符
3	JOB	VARCHAR2(9)	职位
4	MGR	NUMBER(4)	领导编号：领导也是公司员工
5	HIREDATE	DATE	入职日期
6	SAL	NUMBER(7,2)	基本工资，两位小数，五位整数，共七位
7	COMM	NUMBER(7,2)	年终奖
8	DEPTNO	NUMBER(2)	员工所在部门编号

图1-2（emp员工表）

什么是多表查询

l 一个语句需要显示多张表的数据，则必须应用到多表查询的操作。

 

什么是笛卡尔积？

l 数学概念中：集合的关系运算（集合所有关系组合）

用sql实现笛卡尔积：select * from emp e,dept d order by e.empno,e.deptno;
解决笛卡尔积无效数据问题----(内连接：

• 隐式内连接：from A,b where A.关联字段=B.关联字段
• 显式内连接：from A inner join B on a.关联字段=b.关联字段)：

　　　　select * from emp e,dept d where e.deptno = d.deptno;--隐式
　　　　select * from emp e inner join dept d on e.deptno = d.deptno;--显式

总结

l 内连接要求两张表的数据都满足要求才可以显示这条数据。

l 问题：有部门没员工。关联查询要显示部门信息？----外连接

外连接(在Oracle中有一种特殊写法：)

• 左外连接：以左表为基准，左表有数据则全部显示，右表无数据则显示Null

           From B表 left join A on a.关联字段=b.关联字段

• 右外连接：以右表为基准，右表有数据则全部显示，左表无数据则显示Null

           From a表 right join b on a.关联字段=b.关联字段

• 外连接(在Oracle中有一种特殊写法)，已左连接为例

           FROM A,B WHERE A.关联字段(+)=b.关联字段--建议不要用这种写法，因为不通用

自查询

• 左外连接：以左表为基准，左表有数据则全部显示，右表无数据则显示Null

 Eg:查出员工姓名、职位、领导姓名：select * from emp e,emp m where e.mgr = m.empno;

问题：没有领导的员工信息就没有了。

解决：加上左连接

Select * from emp e left join emp m on e.mgr = m.empno;

为什么建议使用left join 、right join、inner join on？

• 首先对于外连接而言OARACLE特有的（+）写法不通用，只针对oracle。
•  Left  join on 、right join on 、 inner join on 结构更加清晰，调试sql时比较容易。
• （+）部分主次表，而Left  join on 、right join on 、 inner join on有明显的主次之分。