数据库整理笔记(三)：

in(列表项)：等于任意一项
not in(列表项)：不等于所有项

常用函数：
单行函数：round\now()\length\data_format
组函数：count\sum\avg\max\min(列名)

排序：
先有结果集再排序

基础查询_分组

使用：	group by 列名	
		group_concat(字段)：处理一对多数据
		having:对分组后的数据再进行过滤
-- 查询每个部门的最高薪水和最低薪水，要求没有部门的不算最内
select deptno,max(salary),min(salary) 
from emp_xu 
where deptno is not null 
group by deptno;

-- 查询每个部门的薪水总和和平均薪水，要求没有部门的不算最内
select deptno,sum(salary),avg(salary) 
from emp_xu 
where deptno is not null 
group by deptno;

-- 按照职位分组，每个职位的最高薪水、最低薪水、人数总和，要求没有职位的不算在内
select	position,max(salary),min(salary),count(*)
from emp_xu
where position is not null 
group by position;

ps:
	分组语句中，select 后面的内容，要么被组函数包围，要么出现在 group by 之后				

-- 查询平均薪水大于5000的部门和平均薪水，没有部门的不算在内
select deptno,avg(ifnull(salary,0))
from emp_xu
where deptno is not null 
group by deptno 
having avg(ifnull(salary,0))>5000;

-- 查询薪水总和大于20000的部门和薪水总和，要求没有部门的不算在内
select deptno,sum(ifnull(salary,0))
from emp_xu
where deptno is not null 
group by deptno
having sum(ifnull(salary,0))>20000;

-- 哪些职位的人数超过两个人，没有职位的不算在内，计算每种职位的平均薪水并且按照平均薪水升序排序
select position,count(*),avg(ifnull(salary,0)) avg
from emp_xu
where position is not null
group by position
having count(*)>2
order by avg asc;

总结：
	基础查询(6个子句)
	写法书序：	select -> from -> where -> group by -> having -> order by
	执行顺序(分析过程):
		from(查询指定表)	-> where(表中记录过滤) -> 
		group by(分组)	-> having(分组后过滤)  -> 
		select(结果集)	->order by（排序）

高级查询_子查询
子查询：一条SQL语句中嵌套 select 查询语句

非关联子查询：嵌套的子查询是独立语句，不依赖主查询
	执行过程：先执行嵌套子查询(独立)->返回查询结果作为主查询的条件->再执行主查询
	ps:
		非关联子查询中嵌套子查询的一条独立SQL语句，不依赖主查询单独执行，并且只执行一次
	1)查询最高薪水是谁（查询最高薪水，根据最高薪水找人）	
	select max(salary) from emp_xu    //查询最高薪水
	select ename,salary 
	from emp_xu 
	where salary=(select max(salary) from emp_xu);

	2）查询最低薪水是谁
	select ename，salary
	from emp_xu
	where salary=(select min(salary) from emp_xu);

	3)查询谁的薪水'张无忌'高
	select ename，salary
	from emp_xu
	where salary>(select salary from emp_xu where ename='张无忌');

	4)查询'研发部'有哪些职位
	select position,deptno
	from emp_xu
	where deptno=(select deptno from dept_xu where dname='人事部');

	5)查询谁的薪水'张无忌'高(有多个'张无忌')
	insert into emp_xu values(1014,'张无忌','Clerk',8000.00,600,now(),1013,null);
	-- 查询'张无忌'薪水
	select salary from emp_xu where ename='张无忌') ;
	-- 大于所有(子查询返回多个值，大于号不能用)
	select ename,salary
	from emp_xu
	where salary>all(select salary from emp_xu where ename='张无忌');

	-- 大于最大
	select ename,salary
	from emp_xu
	where salary>(select max(salary) from emp_xu where ename='张无忌');

	6)查询哪些人的薪水比'张无忌'高(有多个'张无忌')
	-- 满足大于任意一个
	select ename,salary
	from emp_xu
	where salary>any(select salary from emp_xu where ename='张无忌');

	-- 满足大于最小的
	select ename,salary
	from emp_xu
	where salary>(select min(salary) from emp_xu where ename='张无忌');

	7)查询谁和'郭靖'同部门，列出除了'郭靖'之外的员工（单值）
	select ename,deptno
	from emp_xu
	where deptno=(select deptno from emp_xu where ename='郭靖') and ename!='郭靖';

	8)查询谁和'郭靖'同部门，列出除了'郭靖'之外的员工（多个'郭靖'值）
	ps:
		in等价于=any  两者可以互换
	select ename,deptno
	from emp_xu
	where deptno=any(select deptno from emp_xu where ename='郭靖') and ename!='郭靖';

	select ename,deptno
	from emp_xu
	where deptno in(select deptno from emp_xu where ename='郭靖') and ename!='郭靖';

	9)查询谁是'张三丰'的下属
	select ename,empno,leader
	from emp_xu
	where leader=(select empno from emp_xu where ename='张三丰'); //单值

	select ename,empno,leader
	from emp_xu
	where leader in(select empno from emp_xu where ename='张三丰'); //多值
	ps:
		非关联子查询中比较预算符选择，根据子查询返回的值决定
		单值：= > < !=
		多值：>any >all =any in 

	10)查询每个部门拿最高薪水是谁  
	--  查询每个部门最高薪水
	select deptno,max(salary)
	from emp_xu
	where deptno is not null
	group by deptno;    //多值多列
	-- 根据部门、最高薪水找人
		-- 多值多列情况，多值注意比较符选择，多列比较规则相同
	select ename,deptno,salary
	from emp_xu
	where (deptno,salary) in(
		select deptno,max(salary) 
		from emp_xu 
		where deptno is not null 
		group by deptno
	);
	
	11)查询哪些部门的人数比30号部门人数多
	select deptno,count(*)
	from emp_xu
	where deptno is not null
	group by deptno 
	having count(*)>(select count(*) from emp_xu where deptno=30);

	12)查询哪些部门的平均薪水比20号部门平均薪水高
	select deptno,avg(ifnull(salary,0))
	from emp_xu
	where deptno is not null 
	group by deptno
	having avg(ifnull(salary,0))>(select avg(ifnull(salary,0)) from emp_xu where deptno=20);

	13)查询员工所在部门的平均薪水大于5000的员工姓名和职位
	select ename,position
	from emp_xu
	where deptno in(	
		select deptno
		from emp_xu
		where deptno is not null
		group by deptno
		having avg(ifnull(salary,0))>5000
		);

	14)查询那些员工的薪水是本部门的平均薪水值
	-- 找每个部门的平均薪水
	select deptno,avg(ifnull(salary,0)) as avg_salary
	from emp_xu
	where deptno is not null
	group by deptno; 
	-- 根据部门、平均薪水找人
	select ename,salary,deptno
	from emp_xu
	where (deptno,salary)=any(
		select deptno,avg(ifnull(salary,0))
		from emp_xu
		where deptno is not null
		group by deptno
		);
总结：非关联子查询
	a.理解非关联子查询执行过程
	b.比较
	c.多值多列情况（比较规则相同）	

分析题目：
	-- 查询哪些员工的薪水比本部门的平均薪水低
	-- 满足部门号相等（=），薪水小于平均薪水（<）
	-- 两种比较规则不一致，非关联子查询不能使用
关联子查询：嵌套的子查询不是独立语句，依赖主查询
	1）查询哪些员工的薪水比本部门的平均薪水低
	select ename,salary
	from emp_xu
	where salary<(本部门的平均薪水)

	select ename,salary,deptno
	from emp_xu e
	where salary<(
		select avg(ifnull(salary,0))
		from emp_xu 
		where deptno=e.deptno
		);	//e.deptno 动态数据 依赖主查询

	关联子查询执行过程：
		先执行主查询(传递数据)->执行嵌套子查询(依赖数据 执行多次)->返回查询结果->在执行主查询

	2)查询那些人有下属
		找领导 ->
	非关联： 
	select ename,empno
	from emp_xu
	where empno in(
		select leader
		from emp_xu 
		);

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

	说明：
		exists 用于判断子查询有没有数据返回，如果满足关系则有数据返回，不满足没有数据返回
		exists 不关心子查询返回结果，子查询中 select 后面写什么都可以，通常用1表示

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

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

	4)查询哪些部门有员工(肯定)
		-- 满足部门表中的部门号出现在员工表中
		非关联：
		select deptno,dname
		from dept_xu
		where deptno in(
			select deptno from emp_xu
			);

		关联：
		select deptno,dname
		from dept_xu d
		where exists(
			select 1 
			from emp_xu e
			where  e.deptno=d.deptno
			);

	5)查询哪些部门没有员工(否定)
		非关联：
		select deptno,dname
		from dept_xu
		where deptno not in(
			select deptno 
			from emp_xu
			where deptno is not null
			);

		关联：
		select deptno,dname
		from dept_xu d
		where  not exists(
			select 1 
			from emp_xu e
			where  e.deptno=d.deptno
			);