SQL语句 外连接与内连接

数据库的表连接

本文章通过学习《慕课网实战》总结

内连接 JOIN

从多张表提取数据，必须指定关联的条件。

Select empno,ename,dname
From t_emp JOIN t_dept;
ON e.deptno = d.deptno;
//如果不定义关联条件就会出现无条件连接，两张表的数据会交叉连接，产生笛卡尔积

例子

//查询每名员工的部门信息
Select e.empno,e.ename,d.dname
FROM  t_emp e  JOIN t_dept d ON e.deptno = d.deptno;
//ON子句可以用where子句替代

连接三张表

//查询每名员工的工号、姓名、部门名称、底薪、职位、工资等级
Select e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM  t_emp e  JOIN t_dept d ON e.deptno = d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal

内连接的数据表不一定有同名字段，只要字段之间符合逻辑关系就可以

查询底薪超过平均底薪的员工信息

//错误示范
 //连接条件里不能出现聚合函数
SELECT e2.empno,e2.ename,e2.sal
FROM t_emp e1  JOIN t_emp e2 ON e2.sal > AVG(e1.sal);

修改后

SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN
(SELECT Avg(sal) avg FROM t_emp) t
on e.sal >= t.avg;

问题：查询RESEARCH部门的人数、最高底薪、最低底薪 、平均底薪、平均工龄

floor（）函数：四舍五入

// 问题：查询RESEARCH部门的人数、最高底薪、最低底薪 、平均底薪、平均工龄
SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),
FLOOR(AVG(DATEDIFF(NOW(),e.hiredate)/365))
FROM t_emp E JOIN t_dept d ON e.deptno= d.deptno
WHERE d.dname = "RESEARCH";

内连接查询练习1

//查询每种职业的最高工资、最低工资、平均工资、最高工资等级、最低工资等级
SELECT MAX(e.sal + IFNULL(e.comm,0)),MIN(e.sal + IFNULL(e.comm,0)),AVG(e.sal + IFNULL(e.comm,0)),MAX(s.grade),MIN(s.grade)
FROM  t_emp e JOIN t_salgrade s
ON (e.sal + IFNULL(e.comm,0))BETWEEN s.losal AND s.hisal
GROUP BY job;

内连接查询练习2

//查询每个底薪超过平均底薪的员工信息
SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN
(SELECT deptno AVG(sal) as avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal >= t.avg;

外连接 LEFT JOIN

外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录。

// 例子
SELECT e.empno,e.ename ,d.dname
FROM t_emp e LEFT JOIN t_dept d
on e.deptno = d.deptno

左连接与右连接

左外连接就是保留坐标所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。

外连接练习1

//查询每个部门的名称和部门的人数
SELECT d.dname,COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno;
//注意为什么用COUNT(e.deptno),而不是COUNT(*)或COUNT(d.deptno)

外连接练习2

UNION 关键字可以将多个查询语句的结果集进行合并

# 查询每个部门的名称和部门的人数？如果没有部门的员工，部门名称可以用NULL代替
(
SELECT d.dname,COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno
)
UNION
(
SELECT d.dname,COUNT(*)
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno
);
// 注意：想一想为什么第二个需要用COUNT(*)