多表查询
(1) 笛卡尔积
一个表的每一行和第二张表的每一行连接
select* from 表一名,表二名
(2) 等值连接
Select * from emp e,dept d where e.deptno=d.deptno
(3) 非等值连接
Select * from emp e,dept d where e.deptno between ‘01’ and ‘09’
(4) 内连接和外连接
内连接:将满足条件的多表连接结果显示,不满足条件的不显示
Select ename, job, e.deptno, d.DEPTNO from emp e INNER JOIN dept d where e.deptno=d.deptno
外连接:将满足条件的多表连接结果显示,不满足条件的也显示 左外连接 右外连接 mysql不支持全外连接
左外连接
Select ename, job, e.deptno, d.DEPTNO from emp e LEFT OUTER JOIN dept d on e.deptno=d.deptno
右外连接
Select ename, job, e.deptno, d.DEPTNO from emp e RIGHT OUTER JOIN dept d USING(DEPTNO) where e.deptno>'01'
(5) 自连接
SELECT * FROM emp d,emp f WHERE d.EMPNO=f.MGR
(6) 分组函数
最小数: MIN()
最大数: MAX()
求和: SUM()
平均数: AVG()
计数: COUNT()
(7) 分组查询 GROUP BY
SELECT DEPTNO,count(JOB) FROM emp GROUP BY DEPTNO;
分组函数不能放在 where 子句中可以在 having 句中
(8) 6 个关键词的执行顺序
FROM WHERE GROUPBY HAVING SELECT ORDER BY
子查询
(1) 子查询用小括号括起来
SELECT ename FROM emp WHERE (SELECT SAL FROM emp WHERE ENAME='jack')<sal
(2) WHERE子句中的子查询,子查询可以包括分组函数
子查询可以在 WHERE HAVING FROM UPDATE的SET
分组函数可以在 SELECT HAVING GROUP BY
SELECT ename FROM emp WHERE (SELECT SAL FROM emp WHERE ENAME = 'jack' ) < sal
SELECT ename, empno, sal FROM emp WHERE sal = (SELECT MIN(SAL) FROM emp )
(3) 多行子查询运算符
IN
查询每个部门工资最低的员工姓名,员工编号,员工薪资
SELECT ename, empno, sal from emp where sal in (SELECT MIN(sal) FROM emp GROUP BY deptno)
ANY 只要满足结果任意一行即可查询大于所有部门最低工资的员工姓名,员工编号,员工薪资
SELECT ename, empno, sal from emp where sal >ANY (SELECT MIN(sal) FROM emp GROUP BY deptno)
ALL
SELECT ename, empno, sal from emp where sal >ALL (SELECT MIN(sal) FROM emp GROUP BY deptno)
(4)相关子查询
SELECT dname, (SELECT count(empno) from emp WHERE emp.deptno=dept.deptno GROUP BY emp.deptno ) FROM dept