1 where子查詢

1.1 where后面的標(biāo)量子查詢

1.誰的工資比Abel高？

select *
from employees
where salary > (select salaryfrom employeeswhere last_name = 'Abel'
);

2.返回job_id與141號(hào)員工相同，salary比143號(hào)員工多的員工姓名，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后的標(biāo)量子查詢

查詢最低工資大于50號(hào)部門最低工資的部門id和其最低工資

可以拆分去考慮
1.查詢50號(hào)部門最低工資

select min(salary)
from employees
where department_id = 50;

2.查詢每個(gè)部門的最低工資

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 任意一個(gè)
ANY/SOME 某一個(gè)
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’工種任一工資低的員工的,工號(hào)、姓名、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后面的行子查詢（了解即可）

一行多列或者多行多列

查詢員工編號(hào)最小并且工資最高的員工信息

select *
from employees
where (employee_id,salary) = (select min(employee_id),max(salary)from employees
);

2 from子查詢

查詢每個(gè)部門的平均工資的工資等級(jí)

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子查詢（相關(guān)子查詢）

只關(guān)心有沒有這個(gè)值

查詢有員工的部門名

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.有獎(jiǎng)金的員工信息，并且工資較高的前10名顯示出來

select *
from employees
where commission_pct is not null
order by salary desc
limit 10;

5 聯(lián)合查詢

查詢中國(guó)用戶中男性的信息以及外國(guó)用戶中年男性的用戶信息

select * from t_ca where csex='男'
union
select * from t_ua where tGender = 'male';

聯(lián)合查詢的特點(diǎn)：
1.要求多條查詢語句的查詢列數(shù)是一致的
2.要求多條查詢語句查詢的每一列的類型和順序最好一致
3.union會(huì)去重
不想去重的話，可以使用union all關(guān)鍵字

6 練習(xí)

1.查詢和zlotkey相同部門的員工姓名和工資

select last_name,salary
from employees
where department_id = (select department_idfrom employeeswhere last_name = 'Zlotkey'
);

2查詢工資比公司平均工資高的員工的員工號(hào)，姓名和工資。

select employee_id,last_name,salary
from employees
where salary > (select avg(salary)from employees
);

3查詢各部門中工資比本部門平均工資高的員工的員工號(hào)，姓名和工資

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
);