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…
三、子查询
作用：当一个查询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
