中石建基础设施建设有限公司网站,ghost卸载wordpress,wordpress安全博客,大连网站建设方法where、from、exists子查询、分页查询 1 where子查询1.1 where后面的标量子查询1.1.1 having后的标量子查询 1.2 where后面的列子查询1.3 where后面的行子查询#xff08;了解即可#xff09; 2 from子查询3 exists子查询#xff08;相关子查询#xff09;4 分页查询5 联合… where、from、exists子查询、分页查询 1 where子查询1.1 where后面的标量子查询1.1.1 having后的标量子查询 1.2 where后面的列子查询1.3 where后面的行子查询了解即可 2 from子查询3 exists子查询相关子查询4 分页查询5 联合查询6 练习 1 where子查询
1.1 where后面的标量子查询
1.谁的工资比Abel高
select *
from employees
where salary (select salaryfrom employeeswhere last_name Abel
);2.返回job_id与141号员工相同salary比143号员工多的员工姓名job_id 和工资
select last_name,job_id,salary
from employees
where job_id (select job_idfrom employeeswhere employee_id 141
)
and salary (select salaryfrom employeeswhere employee_id 143
);3.返回公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary
from employees
where salary (select min(salary)from employees
);1.1.1 having后的标量子查询
查询最低工资大于50号部门最低工资的部门id和其最低工资
可以拆分去考虑 1.查询50号部门最低工资
select min(salary)
from employees
where department_id 50;2.查询每个部门的最低工资
select department_id,min(salary)
from employees
group by department_id;3.合并
select department_id,min(salary)
from employees
group by department_id
having min(salary) (select min(salary)from employeeswhere department_id 50
);1.2 where后面的列子查询
单列多行 IN/NOT IN 任意一个 ANY/SOME 某一个 ALL 所有
IN 等于 ANY
1.返回location_id是1400或1700的部门中的所有员工姓名
select last_name
from employees
where department_id in (select distinct department_idfrom departmentswhere location_id in (1400,1700)
);2.返回其它工种中比job_id为’IT_PROG’工种任一工资低的员工的,工号、姓名、job_id 以及salary
select employee_id,last_name,job_id,salary
from employees
where salary any (select salaryfrom employeeswhere job_id IT_PROG
) and job_id IT_PROG;1.3 where后面的行子查询了解即可
一行多列或者多行多列
查询员工编号最小并且工资最高的员工信息
select *
from employees
where (employee_id,salary) (select min(employee_id),max(salary)from employees
);2 from子查询
查询每个部门的平均工资的工资等级
select avg_dep.department_id,avg_dep.avg,g.grade_level
from job_grades g
inner join (select department_id,avg(salary) as avgfrom employeesgroup by department_id
)as avg_dep
on avg_dep.avg between g.lowest_sal and g.highest_sal;3 exists子查询相关子查询
只关心有没有这个值
查询有员工的部门名
select department_name
from departments d
where exists(select *from employees ewhere d.department_id e.department_id
);4 分页查询
limit offset,size offset 要显示条目的索引
1.查询前五条的员工信息
select *
from employees
limit 0,5; #起始索引是0一共显示5条2.查询第11条~第25条的员工信息
select *
from employees
limit 10,15;3.有奖金的员工信息并且工资较高的前10名显示出来
select *
from employees
where commission_pct is not null
order by salary desc
limit 10;5 联合查询
查询中国用户中男性的信息以及外国用户中年男性的用户信息
select * from t_ca where csex男
union
select * from t_ua where tGender male;联合查询的特点 1.要求多条查询语句的查询列数是一致的 2.要求多条查询语句查询的每一列的类型和顺序最好一致 3.union会去重 不想去重的话可以使用union all关键字
6 练习
1.查询和zlotkey相同部门的员工姓名和工资
select last_name,salary
from employees
where department_id (select department_idfrom employeeswhere last_name Zlotkey
);2查询工资比公司平均工资高的员工的员工号姓名和工资。
select employee_id,last_name,salary
from employees
where salary (select avg(salary)from employees
);3查询各部门中工资比本部门平均工资高的员工的员工号姓名和工资
select employee_id,last_name,salary
from employees e
inner join (select department_id,avg(salary) as agfrom employeesgroup by department_id
) avg_dep
on avg_dep.department_id e.department_id
where salary avg_dep.ag;4.查询管理者是king的员工姓名和工资
select last_name,salary
from employees
where manager_id in(select employee_idfrom employeeswhere last_name K_ing
);5.查询工资最高的员工的姓名要求first_name和iast_name显示为一列列名为 姓.名
select concat(first_name,last_name) as 姓.名
from employees
where salary (select max(salary)from employees
);
