Mysql数据库笔记整理（四）

回顾：
基础查询：
写法顺序：select-from-where-group by-having-order by
执行顺序：from-where-group by-having-select-order by

group_concat();处理一对多数据

一、子查询：一条sql语句中嵌套select查询语句

非关联子查询：嵌套的子查询是独立语句不依赖主查询
执行过程：
先执行子查询（独立）-返回结果（单值、多值）给主查询-再执行主查询

关联子查询：嵌套的子查询不是独立语句依赖主查询

1）查询哪些员工的薪水比本部门的平均薪水低
select ename,salary,deptno
from emp_zhang
where salary<(本部门平均薪水);

select ename,salary,deptno
from emp_zhang e
where salary<(
select avg(ifnull(salary,0))
from emp_zhang
where deptno=e.deptno
);//e.deptno表示动态数据，是由主查询传递过来的

关联子查询执行过程：
先执行主查询->将参数传递给子查询（依赖）->执行子查询->返回结果给主查询->再执行主查询

2）查询哪些人有下属
分析：
哪些人有下属-找下属
满足员工号等于别人的leader字段

//非关联
select empno,ename
from emp_zhang
where empno=any(
select leader from emp_zhang where leader is not null
);

//关联
select empno,ename
from emp_zhang e
where exists(
select 1 from emp_zhang where leader=e.empno
);

说明：exists关键字用来判断查询有没有结果返回。
满足某种条件有数据返回则true，关系不满足没有数据返回false。
exists 不关心查询返回的结果，所以子查询中select后面写什么都可以，通常直接用1来表示

3）查询哪些人没有下属
//非关联
select empno,ename
from emp_zhang
where empno not in(
select leader from emp_zhang where leader is not null
);//使用not in时列表项中空值必须去掉

//关联
select empno,ename
from emp_zhang e
where not exists(
select 1 from emp_zhang where leader=e.empno
);

4）查询哪些部门有员工（肯定）
insert into dept_zhang values(50,‘后勤部’,null);
分析：部门表中的部门号出现在员工表中
//非关联
select deptno,dname
from dept_zhang
where deptno in(
select deptno from emp_zhang where deptno is not null
);

//关联
select deptno,dname
from dept_zhang d
where exists(
select 1 from emp_zhang where deptno=d.deptno
);

5）查询哪些部门没有员工（否定）
//非关联
select deptno,dname
from dept_zhang
where deptno not in(
select deptno from emp_zhang where deptno is not null
);//not in 空值必须去掉

//关联
select deptno,dname
from dept_zhang d
where not exists(
select 1 from emp_zhang where deptno=d.deptno
);

ps：
关联子查询中嵌套的子查询执行多次

二、组合查询：
组合查询的规则：
a.组合查询是由两条或者两条以上的select语句组成的，并且以union分割

b.被union连接起来的不同查询的结果必须包含相同的列、表达式、组函数（两个结果集结果相同）

特点：union会自动去重
union all不会去重，会显示所有数据

//查询查询10号部门员工姓名和薪水
select ename,salary from emp_zhang where deptno=10;
±-------±---------+
| ename | salary |
±-------±---------+
| 张三丰 | 99999.99 |
| 张无忌 | 5000.00 |
| 杨过 | 8000.00 |
| 张张 | NULL |
±-------±---------+

//查询薪水大于6000的员工姓名和薪水
select ename,salary from emp_zhang where salary>6000;
±-------±---------+
| ename | salary |
±-------±---------+
| 张三丰 | 99999.99 |
| 杨过 | 8000.00 |
| 乔峰 | 8000.00 |
| 段誉 | 15000.00 |
| 孙悟空 | 50000.00 |
| 燕小六 | 12000.00 |
| 张无忌 | 8000.00 |
±-------±---------+

//合并
select ename,salary from emp_zhang where deptno=10
union
select ename,salary from emp_zhang where salary>6000;//union会自动去重
±-------±---------+
| ename | salary |
±-------±---------+
| 张三丰 | 99999.99 |
| 张无忌 | 5000.00 |
| 杨过 | 8000.00 |
| 张张 | NULL |
| 乔峰 | 8000.00 |
| 段誉 | 15000.00 |
| 孙悟空 | 50000.00 |
| 燕小六 | 12000.00 |
| 张无忌 | 8000.00 |
±-------±---------+

select ename,salary from emp_zhang where deptno=10
union all
select ename,salary from emp_zhang where salary>6000;
//union all不会去重，会显示所有数据

//错误合并
select ename,salary from emp_zhang where deptno=10
union
select ename,position from emp_zhang where salary>6000;

三、分页查询
Mysql中分页实现使用limit关键字，限制查询记录数的索引语句
语法：
select 字段 from 表名 limit 数量；
select 字段 from 表名 limit 开始行,数量；//常用-开始行代表从0开始

//测试
select empno,ename from emp_zhang limit 5;
select empno,ename from emp_zhang limit 5,6;

//找规律
第1页 0-4 limit 0,5
第2页 5-9 limit 5,5
第3页 10-14 limit 10,5

pageSize：每页的记录数
page：查询指定的页数

//计算公式（每页的开始行）
int begin=(page-1)*pageSize);

四、表间关联查询
内连接：
语法：
表1 [inner join] 表2 on 条件//[]可选项，可加可不加

//查询员工姓名和部门名字
分析：
结果集中包含员工名字（emp_zhang）和部门名字（dept_zhang）
select ename,dname
from emp_zhang e inner join dept_zhang d
on e.deptno=d.deptno;

select ename,dname
from emp_zhang e join dept_zhang d
on e.deptno=d.deptno;//inner 可以省略
//如果字段两个表都存在，必须指明来自哪个表，例：e.deptno或d.deptno
ps：
内连接的结果集中数据一定是在两张表中都能找到匹配记录

补充：了解
select ename,dname
from emp_zhang e,dept_zhang d
where e.deptno=d.deptno;//先笛卡尔积在过滤

ps：
如果不加where条件的连接，得到的是笛卡尔积的结果
检索结果的行数=第一张表的行数*第二章表的行数，之后where过滤

//查询员工姓名和其领导名字
分析：
查询来自两张emp_zhang表
关联条件：员工的leader等于领导的员工号
select e1.ename,e2.ename
from emp_zhang e1 inner join emp_zhang e2
where e1.leader=e2.empno;//表1和表2位置互换不影响结果

说明：
表1 join 表2 on 条件
a.表1为驱动表，表2为匹配表
b.执行过程：遍历驱动表在匹配表中找匹配
c.内连接结果集特点：匹配上的记录保留，匹配不上的记录丢掉
d.等值连接中，驱动表和匹配表可以互换，不影响结果

//查询员工姓名和部门名字，要求没有部门的员工也要被查询出来
分析：
员工=有部门的员工（内连接）+没有部门的员工（基础语句）

select ename,dname
from emp_zhang e inner join dept_zhang d
on e.deptno=d.deptno
union
select ename,‘No dept’
from emp_zhang
where deptno is null;

外连接：严格区分哪个表是驱动表
语法：[]可以省略
//左外连接（以左边的表为驱动表）
表1 left [outer] join 表2 on 条件

//右外连接（以右边的表为驱动表）
表1 right [outer] join 表2 on 条件

//查询员工姓名和部门名字，要求没有部门的员工也要被查询出来
分析：
查询全部员工，那么员工表应该做为驱动表
select ename,ifnull(dname,‘No Dept’)
from emp_zhang e left outer join dept_zhang d
on e.deptno=d.deptno;
//左外链接和右外连接可以互换。必须明确哪个表是驱动
select ename,ifnull(dname,‘No Dept’)
from dept_zhang d right outer join emp_zhang e
on e.deptno=d.deptno;

ps：
外连接：遍历驱动表在匹配表中找匹配记录，
匹配上的记录保留，匹配不上的记录匹配一行空行

//查询员工姓名和部门名字，要求没有员工的部门也要找出来
查询全部部门，部门表做驱动表
select ifnull(ename,‘No Man’),dname
from dept_zhang d left outer join emp_zhang e
on e.deptno=d.deptno;

ps：
外连接特点：
如果驱动表在匹配表中找不到匹配，则匹配一行空行
驱动表中的数据会全部出现在外连接的结果集中。

外连接结果集=内连接结果集（匹配上的记录）+匹配不上的记录（匹配空行）

//查询哪些部门没有员工
非关联子查询：
关联子查询：
外连接：
部门=有员工部门+没有员工的部门
select dname,ename
from dept_zhang d left outer join emp_zhang e
on d.deptno=e.deptno;//外联查询全部部门

select dname,ename
from dept_zhang d left outer join emp_zhang e
on d.deptno=e.deptno
where empno is null;//where行记录的过滤，过滤条件对应匹配一行空行（emp_zhang中8个字段都为空）

ps：
查询全部数据->外连接->明确哪个表是驱动表（哪个表的数据全部出现在外连接的结果集中，该表作为驱动表）

外连接的本质：驱动表中数据全部出现在外连接的结果集中

注意点：
a.不要关联一些不必要的表，处理关联非常消耗资源
b.关联的表越多，可能会导致性能下降
c.获取同样的结果，可能存在多种SQL实现方式，找最优方式