左外连接(LEFT JOIN) 返回左边表的所有行
- 以左边表为准,左边表的所有字段+右边表
SELECT
e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
d.dname
FROM
scott.emp e
LEFT JOIN scott.DEPT d ON e.DEPTNO = d.DEPTNO;
去重
select distinct(ENAME) from emp;
GROUP BY
注意事项
- 需要查询的字段,必须与group by 相同
select d.dname , max(e.sar),min(e.sar),avg(e.sar)
from dept d,emp e
where d.did=e.did
group by d.dname;
HAVING子句
查询每个部门的最高工资、最低工资和平均工资,只保留平均工资小于5000的部门
select d.dname,max(e.sar),min(e.sar),avg(e.sar)
from dept d,emp e
where d.did=e.did
group by d.dname
having avg(e.sar)<5000;
UNION 合并,去除重复(加all表示不去除重复)
SELECT EMPNO, ename, sal FROM emp WHERE sal>2000
UNION
SELECT EMPNO, ename, sal FROM emp WHERE DEPTNO=10
INTERSECT 取交集
SELECT EMPNO, ename, sal FROM emp WHERE sal>2000
INTERSECT
SELECT EMPNO, ename, sal FROM emp WHERE DEPTNO=10
MINUS 从第一个结果集中减去相同的行
SELECT EMPNO, ename, sal FROM emp WHERE sal>2000
MINUS
SELECT EMPNO, ename, sal FROM emp WHERE DEPTNO=10 ;
连接操作符
连接操作符用于将多个字符串或数据值合并成一个字符串