1. 笛卡尔积
一个表的每一行和第二张表的每一行发生连接
Select * from emp,dept
Select * from emp,dept,salgrade//多余两个表
Select * from emp e,dept d//别名
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
LEFT OUTER JOIN dept d USING(DEPTNO) WHERE e.deptno>’01’
5. 自连接
SELECT * FROM emp d,emp f WHERE d.EMPNO=f.MGR
6. 分组函数
MIN(expr) MAX( ) SUM( ) AVG([DISTINCT] expr) COUNT(expr)
SELECT COUNT(DISTINCT(job)) FROM emp;
**7. 分组查询**group by
SELECT * FROM emp
GROUP BY deptno;
SELECT DEPTNO,count(JOB)
FROM emp
GROUP BY DEPTNO;
分组函数不能放在where子句中 可以在having子句中
SELECT DEPTNO,MAX(sal)
FROM emp
GROUP BY DEPTNO
HAVING MAX(sal)>2000
分组查询的6个关键词
SELECT FROM WHERE GROUP BY HAVING ORDER BY
8. 6个关键词的执行顺序
FROM WHERE GROUP BY HAVING SELECT ORDER BY
SELECT e.deptno,count(empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno
SELECT deptno,count(*)
FROM emp
GROUP BY deptno
HAVING count(*)>2