数据库的多表查询

表信息查看 戳——>表信息

多表查询

之前的查询都是单表操作，如果一条sql语句想要显示多张表的数据，就要用到多表查询

• 查询出emp表和dept表的内容select * from emp,dept;显示56条数据

这个现象在数据库中叫做笛卡尔积效应，多张表的乘积,如何消除笛卡尔积，使用关联字段（只是显示上消除了笛卡尔积）

select * from emp,dept where emp.deptno = dept.deptno;

select * from emp e,dept d where e.deptno = d.deptno;(起别名)

查询出的结果虽然显示上消除了笛卡尔积，但是数据库还是会一个一个去运算，数据量大的情况下效率会慢

• 查询每一位员工的编号，姓名，职位，部门名称，部门位置

select empno,ename,job,dname,loc from dept d,emp e where e.deptno=d.deptno;

或select empno,ename,job,dname,loc from emp e left join dept d on e.deptno=d.deptno;

• 查询每位员工的姓名，职位和领导姓名

select a.ename '员工' ,a.job,b.ename '领导' from emp a left join emp b on a.mgr=b.empno;
这一题有难度，因为是自连接，所以emp一个作为员工表，一个作为领导表，通过外键自连接查出领导再左连接领导。
a b为起别名，在表后跟AS 别名，就可简化表名，AS可省略不写

• 查询每一位员工的姓名，工资，领导姓名，领导工资

select a.ename '员工',a.sal '员工工资',b.ename '领导',b.sal '领导工资' from emp a left join emp b on a.mgr=b.empno;

• 查询每一位员工的编号，姓名，工资，职位，领导姓名，部门名称，部门位置

select a.empno,a.ename,a.sal,a.job,b.ename '领导',dname,loc from emp a left join emp b on a.mgr=b.empno left join dept on dept.deptno=a.deptno;

统计函数和分组查询

• 统计表中的记录数 Count(*)

select count(*) from emp;

• 求平均工资 （Avg)

select avg(sal) from emp;

• 求总工资 （sum）

select sum(sal) from emp;

• 求工资最大值

select max(sal) from emp;

• 求工资最小值

select min(sal) from emp;

• 按照部门分组统计每个部门的人数和平均工资

select count(*),avg(sal) from emp group by dept;

• 求出每个职位的最高工资和最低工资

select max(sal),min(sal),job from emp group by job;

• 按照领取奖金和不领取奖金的员工分组，求每组的人数和平均工资

select count(deptno),avg(sal) from emp group by comm is not null and comm!=0;

•   查询各个部门的部门编号，部门名称和部门人数

select emp.deptno,dname,count(deptno) from emp left join dept on emp.deptno=dept.deptno group by emp.deptno;     

•     查询每个部门的编号，名称，位置，部门人数，平均工资

select emp.deptno,count(deptno),dname,loc,avg(sal) from emp left join dept on dept.deptno=emp.deptno group by emp.deptno;

多表查询比单表查询形式上看复杂许多，但根据题目拆分开来，查出各部分，再根据条件筛选组合起来，还是容易理解的，可以先看题目要求查询哪些信息，这些信息全部写在select......from之间后面加上条件这样理解比较容易一些，关键还是要多加练习。建议从建库、单表、多表顺序练习。