mysql-6多表查询（连表查询、子查询、limit分页查询）
6.多表查询
a.连接查询
1.连接查询
-
1.1多表连接，跨表查询
-
1.2分类：sql92,sql99
-
1.3 表连接分类：
- 内连接：等值连接、非等值连接、自连接，
- 外连接：左外连接（左连接）、右外连接（右连接），
- 全连接
2.笛卡尔积
当两张表进行连接查询，没有任何条件限制的，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积
select ename,dname from emp,dept;
-
1.5如何避免笛卡尔积现象 （sql92语法）
-
虽然使用where 进行条件筛选，但是并没有减少匹配此次
-
表的连接次数越多，效率越低，尽量避免表的连接
select ename,dname from emp,dept where emp.deptno=dept.deptno; 通过设定条件（sql92语法）
-- 优化
select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
-- 使用别名进行进一步优化，提供效率
select e.ename,d.dname from emp e,dept d where e.deptno= d.deptno;
3.内连接- 等值连接（条件是等量关系故称等值连接） inner
案例：查询每个员工所在部门名称，显示员工名和部门名
emp e 和dep d 表进行连接。条件是e.deptno = d.deptno
-- sql 92语法
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno
-- sql 99语法 join 前inner 可省略
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno= d.deptno;
- sql92缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放在where后面
- sql99优点：表连接的条件是独立的，连接之后，如果还需进一步筛选，再往后继续添加where
4.内连接- 非等值连接
条件不是一个等量关系，称为非等值连接
范围、等级、取值区间关联，使用between.. and..
-- between .. and
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
5.内连接 -自连接
技巧：把一张表，看成两张表
对emp 领导表内进行自连接，需要把emp 表既看成员工表，也看成领导表
| empno | ename | mgr |
|---|---|---|
| 7369 | smith | 7902 |
| 7499 | allen | 7698 |
| 7698 | blark | 7839 |
| 7902 | ford | 7566 |
--
select
a.ename as '员工名', b.ename as'领导名'
from
emp a
join
emp b
on
a.mgr=b.empno;
-- 员工的领导编号 = 领导的员工编号
6.外连接
-
右外连接 right join
右表为主表，将右表全部查询出来，捎带关联左边的表
在外链接当中，两张表连接，产生了主次关系。
-
左外连接亦然 left join
-- 右外连接
-- 可带outer 带上可读性更强
select
e.ename,d.dname
from
emp e
right outer join
dept d -- 右边全部匹配
on
e.deptno= d.deptno;
总结 外连接的查询结果条数一定是>=内连接的查询结果条数
7.三张表，四张表连接
-- 语法：
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的条件
join
d
on
a和d的条件
-- 一条sql中内连接和外连接可以混合，都可以出现
案例：
找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级
如果再加上查询员工的上级领导（使用外连接，避免删除）
分析：从emp 取出ename, 关联dept,取dname, 关联salgrade 取grade
| emp | |||
|---|---|---|---|
| empno | ename | sal | depton |
| 7398 | smith | 800.00 | 20 |
| 7499 | allen | 1600.00 | 30 |
| 7521 | ward | 1250.00 | 30 |
| 7654 | martin | 1250.00 | 30 |
| dept | ||
|---|---|---|
| deptno | dname | loc |
| 10 | accounting | new york |
| 20 | research | dallas |
| 30 | sales | chicago |
| 40 | operations | boston |
| salgrde | ||
|---|---|---|
| grade | losal | hisal |
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
select e.ename,d.dname,s.grade
from emp e
join dept d
on e.depton = d.depton.deptno
join salgrade s
on e.sal between s.losal and s.hisal;
b.子查询
1.子查询
select 语句中嵌套select 语句，被嵌套的select 的语句称为子查询
2.where 条件后的子查询
-- 子查询1,where条件中
select 表头a from 表a where 表头 .. (select .. from table_b)
3.from 子语句中的子查询 (可以将子查询的结果当做一张临时表)
-- from 子句中的子查询
from 后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）
__注意__子查询注意分组函数的使用时易被错认为函数，导致报错，需要对该分组函数命名
4.select 后面出现的子查询
select 按表行搜索，类似循环嵌套,(使用联表进行查询)
-
select 后面出现的子查询只能返回一条结果，多于一条报错
-- 实例 理解： 查询emp表，分别查询e对应的ename列，deptno列，以及e第一行中对应的deptno 对应d表中的dname, select e.ename,e,deptno (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e; -
实例 查询各科成绩前三名的记录:(不考虑成绩并列情况)
select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(select
sid,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_num
from
score as s1
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num;
c.union 合并查询结果集
- union 进行结果集合并要求结果集的列数相同，合并时列与列的数据类型也相同
- 同时union合并时会自动去重，可以使用union all 进行保留
- 减少匹配次数，实现数据集的拼接
- union的效率要高一些（减少匹配次数），对于表连接而言，每连接一次新表，则匹配的次数满足笛卡尔积
- 实例：
查询工作岗位是manager 和salesman的员工
-- 第一种
select ename,job from emp where job='manager' or 'salesman';
-- 第二种
select ename,job from emp where job in ('manager','salesman');
- 使用union 进行合并
select ename,job from emp where job ='manager'
union
select ename,job from emp where job = 'salesman';
D、limit 分页查询
1.将数据集的一部分取出，通常使用在分页查询当中
-
语法
完整用法:limit startindex,length startindex 起始下标 length 长度 起始下标从0开始 缺省用法：limit num; 取前num个 limit 8 表示取前八个 -
limit 与oder by的使用
select ... from... order by .. desc limit 5
2.通用分页
- 每页显示3条记录
- 第1页 ： limit 0,3
- 第2页： limit 3,3
- 第3页： limit 6,3
- 第4页： limit 9,3
- 第n页： limit (num-1)*3,3
