MYSQL之复杂查询_表链接

day2复杂查询_表链接

一、 分组

1、简单分组

• 概念：一般配合组函数一起使用，不再将一张表作为一组数据，而将某列相同的数据划分为小组，对每个小组进行处理。

• 语法：

select...分组字段|组函数 
from... 
where... 
group by 分组字段 
order by ....

• 例：按照公司部门分组：

select department_id 
from employees 
group by department_id;

• 例：查询公司每个部门的最高工资

-- step 1： 按照部门分组 
select department_id 
from employees 
group by department_id; 
-- step 2：使用组函数找到最高工资 
select department_id , max(salary) 
from employees 
group by department_id;

注意：如果select语句后有未分组的列，只返回第一行数据，不会进行分组。不要出现未分组的字段

例：

例：
select job_id , avg(salary) 
from employees; -- 100 6461 
更正：
select job_id, avg(salary) 
from employees
 group by job_id; -- OK

• 查询各个等级工资的人数

select salary,count(department_id) 人数 from employees
group by salary;
order by salary desc;

2、过滤后分组

• 作用：在分组前使用where先过滤掉一部分数据再分组

• 例：查询各个部门工资大于5000的员工数量

-- step1：按照部门进行分组 
select department_id 
from employees 
group by department_id; 
-- step2：统计员工数量 
select department_id , count(*) 
from employees 
group by department_id; 
-- step3：加入过滤条件 
select department_id , count(*)
 from employees 
 where  salary>5000
 group by department_id;

where语句的优先级高于group by ，可以在分组前过滤数据

3、分组后过滤（having）

• 作用：用于分组后的数据过滤，可以使用组函数。

• 语法：

select... 
from.. 
where... 
group by... 
having.. 
order by...

例：查询部门平均工资高于6000的部门id、人数、平均工资

-- step 1 : 按照部门进行分组
 select department_id 
 from employees 
 group by department_id; 
 -- step 2：统计每个部门的人数与平均工资
  select department_id ,count(employee_id), avg(salary) 
  from employees 
  group by department_id;
   -- step 3：having过滤 平均工资
select department_id ,count(employee_id),avg(salary) 
from employees 
group by department_id 
having avg(salary)>6000;

• where与having的区别：

1. where在group by之前执行，having在group by之后执行 
2. where以每个数据为单位进行过滤，
   having以每组数据为单位进行过滤，支持组函数

4、分页查询（limit)

• 作用：limit关键字用来限制查询结果的条目数，通常用于分页查询。

• 语法：

//sql语句中的最后一行语句 
limit offset,rows; 
-- offset：表示查询条目的起始下标， 
-- rows：表示最多显示多少条

例：获取前10行

select * from employees limit 0,10;

例：获取11行~20行

select * from employees limit 10,10;

当offset为0时，可以省略

例：

select * from employees limit 10;

二、SQL语句中关键字的执行顺序

• select … from …where…group by…having…order by…

执行顺序	关键字	作用
1	select	查询
2	form	确定数据的来源
3	where	过滤表中的原始数据
4	group by	对满足where条件的数据进行分组
5	having	过滤分组后的数据
6	order by	对最终的结果进行排序
7	limit	限制查询结果的条数

三、子查询

• 作用：当一个查询SQL的条件需要使用另外一个查询SQL的结果时，需要在一个SQL语句中嵌套另外一个SQL语句。

1、单列单值子查询

• 概念：子查询结果只有一个值时，可以利用该值进行二次查询

    例：查询比Nancy工资高的员工信息

-- step 1 ：查询Nancy的工资 
select salary
 from employees 
 where first_name='Nancy' 
 得到查询结果：12000 
 -- step 2：查询工资大于12000的员工 
 --（12000是谁？如果nancy涨工资呢？）
  select employee_id , first_name 
  from employees 
  where salary>12000; 
  -- step 3：合并替换（由sql语句替换具体的值） 
  select employee_id , first_name 
  from employees
   where salary>(
   select salary 
   from employees
    where first_name='Nancy');

例：查询与Nancy同一部门的员工信息

-- step 1：查询Nancy的部门 
select department_id 
from employees 
where first_name = 'Nancy';
 得到结果：100 
 -- step 2：查询部门编号为100的员工信息 
 select department_id , first_name ,department_id 
 from employees 
 where department_id =100; 
 -- step 3：合并替换（由sql语句替换具体的值） 
 select department_id , first_name ,department_id 
 from employees where department_id =(
 select department_id 
 from employees
  where first_name='Nancy');

2、单列多值子查询

• 概念：子查询结果出现多个值时，可以利用该值进行二次查询。

    例：查询与John同一部门的员工信息

-- step 1：查询John的部门 
select department_id 
from employees 
where first_name ='John'; 
得到结果：50、80、100 
-- step 2：查询属于50、80、100号部门的员工
 select * from employees
  where department_id 
  in(50,80,100); 
  -- step 3：合并替换（由sql语句替换具体的值） 
  select * from employees 
  where department_id in(
  select department_id 
  from employees 
  where first_name='John');

3、多列多值子查询

• 概念：子查询结果为多值并且由多列构成，一般我们用于from后面，作为数据来源虚拟表。

    例：查询工资最高的前5行

-- step 1：将数据进行排序 
select sum(salary)from employees
 order by salary; 
 limit 5; 
 -- step 2：将子查询结果一张虚表再次处理 
 select sum(salary) 
 from  (
 select salary 
 from employees 
 order by salary 
 desc limit 5 ) as e; 
 -- MYSQL会把多列多值子查询当成一张虚拟表，必须为它定义别名,as可以省略

四、关系数据

• 数据高耦合

• 通过部门编号能找到部门

• 部门的数据改名字就可以了，数据与数据之间耦合度就降低了

• 外键就是关系列，关系字段，用来表示外部关系，员工表的id是主键，唯一标识，不能为空，外键表明数据关系

• 通常就是一张表的外键指向另外一张表的主键

• 外键不能乱写，但是可以不写，表示没有部门

总结：在表中用于数据关系的字段，可以使用该字段中的数据，找到另外一张表，与之匹配，称之为外键

• 概念：有时我们不能单从一张表中看出数据的明细，它往往是一个编号，这个编号主要用于告诉我

    们到哪去寻找该数据的明细。

    

    例如：我们无法从 employees 表中看出员工的部门具体是什么，它只有一个编

    （ department_id ）,必须通过编号到 department（部门表） 表中才能找到部门的详细信息

通过编号到 department 表中寻找对应的数据

可以看出 emplyees 表中的 department_id 与 department 表中的 department_id 是有关系

的，所以我们把该列数据称之为关系数据。

而该列称为外键。

• 外键

    作用：建立（说明）表与表、数据与数据之间的关系，例如 employees 表中的 department_id

    特点：

1. 外键的值一般来源于另外一张表主键的值，并且要满足参照完整性。
2. 外键在表连接时通常作为连接条件使用。
3.用于存储数据关系，外键指向了另外一张表的主键。

五、表连接

• 作用：当查询的数据需要从多张表中获取时，需要将多张表连接起来进行查询。

1、内连接（inner join）

• 语法：

select 表1.列名,... ,表2.列名,...
 from 表1 别名 
 inner join 表2 别名 
 on 连接条件;

t1,t2是表的别名，用于在查询时进行区分，明确查询数据来源于那张表

例：查询员工和部门的信息

select e.*,d.* 
from employees e inner join departments d 
on e.department_id = d.department_id;

特点：两张表中只要满足连接条件的数据才显示，不管是哪一张表的数据，只要不满足连接条件一定不显示。

2、左外连接（left join）

• 语法：

select 左表.列名,... ，右表.列名 
from 左表 别名 
left outer join 右表 别名 
on 连接条件;

例：查询员工及所属部门信息

select e.*,d.* 
from employees e 
left outer join departments d 
on e.department_id = d.department_id;

特点：左表中数据无论是否满足条件一定显示，右表中的数据满足条件显示，不满足条件不显示。

3、右外链接（right outer join)

• 语法：

select 左表.列名，... ,右表.列名，... 
from 左表 
right outer join 右表 on 连接条件;

例：–查询员工及所在部门信息

select e.*,d.*
 from employees e 
 right outer join departments d 
 on e.department_id = d.department_id;

特点：右表中无论是否满足连接条件都显示，左表中数据满足连接条件才显示，不满足就不显示。

注意：实战开发时，左外连接使用最多。内连接的inner关键字和外连接的outer关键字可以省略

3、多表链接

• 语法：

select 表1.列名,...,表2.列名,...,表3.列名,...
 from 表1 left join 表2 
 on 连接条件 
 left join 表3 
 on 连接条件 left join...

例：查询员工所在部门以及部门地址的信息

select e.*,d.*,l.* 
from employees e 
left join departments d 
on e.department_id = d.department_id 
left join locations l 
on d.location_id = l.location_id;

注意：实战中，表连接不能超过3张表，否则会有性能问题。

4、自连接

-- 查询员工与其经理的信息
select t1.*,t2.*
from employees t1 left join employees t2
on t1.manager_id = t2.employee_id;

-- 5. *查询员工的基本信息，附加其上级的姓名 （自己链接自己）
SELECT t1.*,t2.first_name 上级姓名
from employees  t1 left join employees t2
on t1.manager_id=t2.employee_id;
select * from employees

-- 6. *求入职日期相同（年月日相同）的员工(自己链接自己) 
select t1.*,t2.*
from employees t1 left join employees t2
on t1.hiredate=t2.hiredate;

-- 7. *显示各个部门经理的基本工资（自己链接自己）
SELECT t2.first_name 上级姓名,t2.salary 工资
from employees  t1 left join employees t2
on t1.manager_id=t2.employee_id;
select * from employees