select fieldName
from tbName
where condition_
groupby 分组要求;-- group by是一个分组关键字-- 查询各部门人数是多少-- 1. 需要按照department_id进行分组-- 2. 计数需要使用count, 根据用户的employee_id进行计数操作select department_id,count(employee_id)from t_employees
groupby department_id;-- 查询各部门的平均工资-- 1. 需要按照department_id进行分组-- 2. 平均工资使用avg方法计算select department_id,avg(salary)from t_employees
groupby department_id;-- 查询各部门,各岗位的人数-- 1. 需要按照department_id进行分组-- 2. 需要按照岗位job_id进行分组-- 3. 记录人数,count(employee_id)select department_id, job_id,count(employee_id)from t_employees
groupby department_id, job_id;-- [42000][1055] Expression #1 of SELECT list is not in GROUP BY-- clause and contains nonaggregated column 'company.t_employees.department_id'-- which is not functionally dependent on columns in GROUP BY clause;-- this is incompatible with sql_mode=only_full_group_by-- 如果使用group by要求分组字段一定是查询要求字段,这里需要根据查询结果进行分组select department_id
from t_employees
groupby job_id;
1.2 分组过滤查询
select fieldName
from tbName
where condition_
groupby 分组要求
having 过滤规则;-- having是在 group by 之后的条件过滤-- 查询指定100,50,30,80最高工资-- 1. 需要按照department_id进行分组-- 2. 最高工资-- 3. 限制manager_id = 100-- 4. 限制department_id号为100,50,30,80select department_id,max(salary)from t_employees
where manager_id =100groupby department_id
having department_id in(100,50,30,80);
1.3 限定查询
select fieldName
from tbName
limit 限制;-- limit [offset_start], row_count-- 查询员工表中前10个数据,员工first_name, employee_idselect employee_id, first_name
from t_employees
limit10;-- 查询员工表中10个数据,要求offset为3,员工first_name, employee_id-- 起始行从0开始select employee_id, first_name
from t_employees
limit3,10;-- 【重点】-- limit核心用法,分页查询-- pageCount 当前是第几页-- itemCount 一页展示多少个元素-- select * from tbName limit (pageCount - 1) * itemCount, itemCount;-- 展示第一页10条数据select employee_id, first_name
from t_employees
limit0,10;-- 展示第二页10条数据select employee_id, first_name
from t_employees
limit10,10;-- 展示第三页10条数据select employee_id, first_name
from t_employees
limit20,10;
1.4 子查询
1.4.1 基本格式
select fieldName
from tbName
where(子查询结果);
1.4.2 子查询结果作为条件判断约束
-- 查询工资高于Jack的员工id和姓名-- 1. 找出Jack的工资-- 2. 得到Jack工资,作为条件查询对应的员工信息select salary
from t_employees
where first_name ='Jack';select employee_id, first_name
from t_employees
where salary >8400;-- 整合为子查询-- 条件判断select employee_id, first_name
from t_employees
where salary >(select salary
from t_employees
where first_name ='Jack');
1.4.3 子查询结果作为枚举限制
-- 查询和Jack同部门的员工信息-- 1. 找出Jack的部门编号select department_id
from t_employees
where first_name ='Jack';-- 2. 根据Jack的部门编号,使用in枚举查询,限制条件select employee_id, first_name
from t_employees
where department_id in(80);-- 整合为子查询select employee_id, first_name
from t_employees
where department_id in(select department_id
from t_employees
where first_name ='Jack');
-- 查询所有员工对应的ID号,名字,部门名称,和国家对应名字select te.employee_id, te.first_name, td.department_name, tc.country_name
from t_employees te
innerjoin t_departments td on te.department_id = td.department_id
innerjoin t_locations tl on td.location_id = tl.location_id
innerjoin t_countries tc on tl.country_id = tc.country_id;
-- 查询所有员工对应的ID号,名字,工作职称,部门名称,和国家对应名字select te.employee_id, te.first_name, tj.job_title, td.department_name, tc.country_name
from t_employees te
innerjoin t_jobs tj on te.job_id = tj.job_id
innerjoin t_departments td on te.department_id = td.department_id
innerjoin t_locations tl on td.location_id = tl.location_id
innerjoin t_countries tc on tl.country_id = tc.country_id;
1.5.3 左外连接
-- 左外连接 左表是主表,要求左表完整显示,右表匹配左表数据,如果右表没有数据匹配,显示null-- 查询所有的员工信息ID,first_ame,已经对应的部门名字select te.employee_id, te.first_name, td.department_name
from t_employees te
leftjoin t_departments td on te.department_id = td.department_id;
1.5.4 右外连接
-- 右外连接查询,右表是主表,要求右表完整展示,左表匹配右表数据,如果左表没有数据匹配,显示null-- 查询所有部门对应员工信息,员工信息没有显示nullselect td.department_name,te.employee_id, te.first_name
from t_employees te
rightjoin t_departments td on te.department_id = td.department_id;
1. 查询1.1 分组查询select fieldNamefrom tbName where condition_ group by 分组要求;-- group by是一个分组关键字-- 查询各部门人数是多少-- 1. 需要按照department_id进行分组-- 2. 计数需要使用count, 根据用户的employee_id进行计数操作select department...