Mysql day3

**分组函数/组函数:
一组产生一个结果
1,基础:分组概念
任意一个列都可以进行分组

 1.1:效果:分组以后，表变成什么样子了
 1.2:关键字: group by 
 1.3:书写位置:紧跟着where 后面
 1.4:语法: group by a列,b列...
 1.5:意思: 对当前结果集中的数据进行分组,对a列分组,如果a列中有相同的数据再按照b列进行分组
如果按照多个列分组，会先按照第一列分组，然后按照第二列分组...
或者说：只有在列1和列2和列3的值都相同的才会被分到一个组。
 1.6: 对组的限定: having 限定条件 
     不写group by 并且使用组函数:默认是把整个表分成一个小组

2,组函数:
2.1:基础:只有在分组的结果集中才能使用组函数
或者说有group by的sql语句才能使用组函数.
2.2:解释:使用组函数操作小组,每个小组会产生一个值
具体:
avg(列):求平均值 :在某个小组中列的平均值
count(列):计算记录总数 :在某个小组中列的记录总数
Max(列):求最大值 :在某个小组中列的最大值
Min(列)：求最小值 :在某个小组中列的最小值
sum(列):求和 :在某个小组中列的和

   STDDEV(列)  ：标准差
   VARIANCE(列)：方差
2.3:理解组函数：
插入数据:
  insert into s_emp(id,last_name,dept_id) values(123,'Patel',42);
  commit;
  eg: 查询 41号部门的平均工资。
 

2.4:得到结论：
如果使用了组函数,那么在select中的列，必须是group by 列 不然查询不到。
2.5:对表的多个列进行分组:
eg:对last_name,和工资进行分组
解释:对last_name和salary进行分组，如果有某几条数据last_name和salary值相同那么这几条数据就会被分到同一个组。

3,代码执行顺序:
3.1:顺序:from—>where–>group by分组–>执行组函数–>having筛选->select–>order by
3.2:组函数出现的位置 : select字句 having字句 order by字句
4,组函数练习:
4.1:查询所有员工的平均工资?
【特例:把表中所有数据分到一个组不用写group by】

4.2:查询每个部门的平均工资?

	
4.3:查询平均工资大于1500的部门?

4.4:不能在GROUP BY子句中使用select列别名。
	
	
4.5:查询每个部门的平均工资并且显示出部门的名字和部门id？
	
4.6:查询平均工资大于1400的部门id,并且显示出部门的名字？

select dept_id,name,avg(salary)
from s_emp e,s_dept d
where dept_Id = d.id
group by dept_id,name
having avg(salary) > 1400
4.7:查询title中不包含vp字符串的每个职位的平均薪水，并对平均薪水进行降序排列，并且每个职位的总薪水大于5000？
4.8:查询s_emp表中最大工资数,并且显示出这个人的名字？

多表连接查询:
0,mysql检索规则
1，将表s_emp中的每一条数据放到结果集中。
2，如果该条数据满足where条件那么就留在结果集中，并检索下一条数据。
3，如果该条数据不满足where条件 那么就从结果集中剔除该数据，并检索下一条数据。

1,笛卡尔积:有些记录的连接是没有意义的
	
使用where条件解决这种问题（表和表的连接条件避免）
	n个表，n-1个条件

 

2,等值连接 : 一般通过外键和主键的值相同进行表关联 
	2.1: 连接条件使用  =
	2.2: 两列的值是一样的
  例一：查询所有员工的ID，名字和所在部门的名称？
  		
  例二：查询id小于30员工的姓名和部门名称所在地区的名称？

  练习一：查询部门名称包含sa的员工姓名和薪水
	
  练习二：查询部门名称是5位，
          该部门员工的薪水不等于1500，
      并按员工的薪水降序排序
	

3,不等值连接 : 连接条件使用的不是等号的连接, >=   <=   between...and
  解释 : 不等值连接就是表一的某一列 是表二中某几列的一个中间值
  前提 :	
    创建工资等级表
drop table `s_rank`;
  	create table `s_rank`(
  	id int(5) primary key,
  	minSal int(7),
  	maxSal int(7),
  	name varchar(200));
  
set names 'gbk';
  	insert into s_rank values(1,0,999,'蓝领');
  	insert into s_rank values(2,1000,1499,'白领');
  	insert into s_rank values(3,1500,2500,'金领');
  	commit;
  
  练习:
  	例1：查询所有员工的工资等级？
     
4,外连接   :外键(某一列)为空 并且需要被查询出来
	如果有部分数据不满足连接条件也要被查询出来
  插入数据:
set names 'gbk';
    insert into s_emp(id,last_name) values(999,'_briup');
    insert into s_dept(id,name) values(60,'教学部');
    commit;
   4.1:左外连接:
语法: 
from  左表 left outer join 右表 on a表和b表连接的连接条件
左表: 某一条数据外键如果为空 也要被显示出来
	
【位于左表中的一条数据不满足连接条件也要被查询出来】
	
    eg:查询员工的id,姓名,所在部门id,
       部门名,所有员工需要被查询出来
    eg:查询员工的id,姓名,所在部门id,
       部门名,所有部门需要被查询出来

    注: outer可以不写
   4.2:右外连接:
 语法:
 from 左表 right outer join 右表 on a表和b表连接的连接条件
       会把右表中外键(右表的连接条件)为空的列也查询出来
 右表: 某一条数据外键(单纯列,两个表连接的条件列)如果为空 也要被显示出来
    eg:查询员工的id,姓名,所在部门id,部门名,所有部门需要被查询出来
【位于右表中的一条数据不满足连接条件也要被查询出来】

全外连接 : mysql不支持 
 from 左表 full outer join 右表 on 连接条件

5,自连接
连接两张相同的表
解释 ：当前表和当前表连接
eg:查询每个员工的名字以及员工对应的经理的名字

select y.id,y.last_name,j.last_name
from s_emp y,s_emp j
where y.manager_id = j.id

6,集合操作符:
前提：是对结果集进行操作,俩个被操作的结果集中查询的列要完全一致
语法 ：
结果集一
集合操作符
结果集二;

差集:mysql不支持
	select 完整语句
	minus 
	select 完整语句;
union : 并集
	eg: 使用集合操作符，查询id小于5和id大于10的员工信息

select id,last_name from s_emp where id < 5
union
select id,last_name from s_emp where id > 10;
union all : 并集
	eg: 使用集合操作符，查询id小于5和id大于10的员工信息
观察和上一个区别

select id,last_name
from s_emp
where id<5
   union all
    select id,last_name
from s_emp
where id>3;
1,操作两个结果集
2,必须列相同
3,第一个结果集不需要写 ;

7,mysql 分页
语法:
limit n;
返回前n条数据
limit n offset;
从n开始，偏移offset条数据返回。
顺序:
select
from
where
order by
limit

第一页 : 每页显示5条数据
	select id,last_name from s_emp limit 5;
第二页 : 每页显示5条数据
	select id,last_name from s_emp limit 5,5;
第三页 : 每页显示5条数据
	select id,last_name from s_emp limit 10,5;
select id,last_name,salary
from s_emp
order by salary desc
limit 3;

子查询
1,定义 : 一条sql语句嵌套一个或者多个sql语句

 1,把查询一的结果集(多行多列)当做表 进行查询
查询一:
    select id,last_name,salary
    from s_emp
    where id<15

 2,把查询一的结果集(多行一列)当做当前表的限定条件 
查询一:
   select salary
   from s_emp
   where salry > 1000
 
select id,last_name,salary
from s_emp
where salary in ()
 3,把查询一的结果集(一行一列)当做当前表的限定条件
查询1:
  select max(salary)
  from s_emp
select id,last_name
from s_emp
where salary = ();

2,格式 :
select *
from (查询一) 查询一别名

select *
from 表
where id = (查询一)

3,什么情况下使用：比较符中参数不确定的情况
4,子查询出现的地方：
1）from 子句
2）where 子句
3) having

5,子查询练习:
步骤一 ： 将复杂查询分解为多个简单的查询
步骤二 ： 使用子查询将多个简单的查询融入到一个sql中

5.1:查询工资比41号部门平均工资高的员工信息

5.2:查询工资比NewMan所在部门平均工资高的员工信息

5.3:查看部门平均工资大于32号部门平均工资的部门id

5.4:查询平均工资大于41号部门平均工资的所有部门中的员工信息

5.5:查询出工资比Newman的经理的薪水高的员工

结果:
	select id,last_name,dept_id,salary
	from s_emp
	where salary > (select distinct salary
	from s_emp
	where id = (select manager_id
	from s_emp
	where last_name = 'Newman'));

exists子查询:
语法:
from 表A
where [not] exists(select语句 from 表B)
作用: select语句中是否存在合法的数据,EXISTS用于检查子查询是否至少会返回一行数据，
该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。
功能:
可以在exists()内部的select语句where条件中使用 表A 中的列

样例:
	查询员工名,和部门id,并且5倍的员工id要小于该员工所在部门的id:
	select last_name,dept_id,id
	from s_emp e
	where exists (select * from s_dept d where 5*e.id<d.id )
练习:
	查询员工信息,并且员工名中需要包含和该员工所在部门名首字母相同

有关 all、any 和 some 的子查询 : 只能用在where中
运算符 关键字
any some all
>，>= 最小值 最小值 最大值
<，<= 最大值 最大值 最小值
= 任意值 任意值 xxx
<>，!= 任意值 xxx xxx
 all(集合)：集合中的所有元素。 
查询所有工资大于 43 号部门员工工资的员工信息。 

 any(集合)：集合中的人格一个元素；some(集合)：在语法上等同于 any。 
查询所有工资小于 43 号部门最高员工工资的员工信息**