navicat 多表之sql语句

创建两表
dept 表： 部门表   两个字段：  dept1 部门编号   dept_name  部门名称

SELECT * from dept;
SELECT * from emp;
普通内连接
SELECT * from dept INNER JOIN emp on dept.dept1=emp.dept2;
隐藏内连接
SELECT * from dept，emp WHERE dept.dept1=emp.dept2;
左连接
SELECT * from dept LEFT JOIN emp ON dept.dept1=emp.dept2;
右链接
SELECT * from dept RIGHT JOIN emp ON dept.dept1=emp.dept2;
左独有
select * from dept left JOIN emp on dept.dept1=emp.dept2 where name is null;
右独有
select * from dept right JOIN emp on dept.dept1=emp.dept2 where dept1 is null ;
左连接+右独有
select * from dept left JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept right JOIN emp on dept.dept1=emp.dept2
where dept1 is null ;
右连接+左独有
select * from dept right JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept left JOIN emp on dept.dept1=emp.dept2
where name is null;
内连接+左独有+右独
select * from dept INNER JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept left JOIN emp on dept.dept1=emp.dept2
where name is null
UNION
select * from dept right JOIN emp on dept.dept1=emp.dept2
where dept1 is null ;

二
IT技术入职的员工号

SELECT sid FROM emp WHERE dept2=(SELECT dept1 FROM dept where dept_name='IT技术');

1.列出每个部门的平均收入及部门名称; 
结果：
avg（inconming）（emp），dept_name（dept）
条件：
group by   dept_name 
 方法1:
select   dept_name ,avg(incoming) from  dept left join  emp  on dept.dept1=emp.dept2 group  by  dept_name ;

2.财务部门的收入总和； 
结果：sum（incomig）
条件：dept_name="财务"
select  sum(incoming) from  dept inner join  emp on dept.dept1=emp.dept2
where  dept_name="财务"