- 內容導航：
- 1、hire count
- 2、第七章 高級查詢
1、hire count
hire count發(fā)音
英： 美：
hire count中文意思翻譯
常用釋義：招聘人數：指公司或組織在一定時間內需要招聘的人數。
人數
hire count雙語使用場景
1、can count on you. Thanks to these good habits, I earned a reputation for efficiency. People who work with me and who hire me know they can count on me.───多虧有了這些好的習慣，我贏得了重效率的好名聲，和我一起工作的人或者是雇我的人都打心眼里知道我是可以值得放心信賴的。
2、Sales are back up, the head count is now up to 130, and Acme reckons it will hire 20 more people this year to handle the growing order book.───如今銷售額漲勢再起，員工數已達130人，Acme公司考慮今年再擴招20人處理不斷增多的訂單量。
3、These are Banks we count on to provide the capital that lets our small businesses hire and grow.───我們的小企業(yè)依賴這些銀行為其提供資金來雇傭人員和實現增長。
hire count相似詞語短語
1、possible hire───可能的雇傭
2、for hire───供出租
3、hire purchase───n.分期付款購買（縮寫為： HP）; 租購；n.[貿易]分期付款購買
4、hire me───雇傭我
5、charterer hire───租船人租金
6、hire───v.租用；聘用；臨時聘用；n.租借；新雇員；租金；n.(Hire)（印、美）希爾（人名）
7、new hire───新員工
8、count───n.總數；數數；量的計數；事項，罪狀；觀點；伯爵；（紗線的）支數；n.(Count)（美）康特（人名）；v.數數；計算總數；把……算入，包括；重要；正式接納；認為，看作
9、hire date───聘用日期
2、第七章 高級查詢
•請思考如下問題？
–查詢所有員工的每個月工資總和，平均工資？
–查詢工資最高和最低的工資是多少？
–查詢公司的總人數？
-查詢有獎金的總人數？
•分組函數是對數據行的集合進行操作并按組給出一個結果，這個結果可直接輸出，或者用來做判斷條件。
針對多行數據進行操作 多行函數
2）最小值min(參數)
3）平均值avg(參數)
4）求和sum(參數)
5）總條數count(參數)
分組函數可以寫到select having orderby子句中
例：#求10號部門工資最高和最低的員工的工資
SELECT max(sal),min(sal)
from emp
where deptno=10;
SELECT min(hiredate),max(hiredate)
from emp;
select avg(sal)
from emp
where deptno=20;
select sum(sal)
from emp
where deptno=20;
select count(empno)
from emp;
select count(empno)
from emp
where comm is not null;
select count(comm)
from emp;
select count(*)
from emp;
ifnull(字段名，0）
select avg(ifnull(comm,0))
from emp;
練習1：
•1.查詢部門20的員工，每個月的工資總和及平均工資。
SELECT SUM(sal),avg(sal)
from emp
where deptno=20;
•2.查詢工作在CHICAGO的員工人數，最高工資及最低工資。
SELECT count(empno),max(e.sal),min(e.sal)
from emp e,dept d
where e.deptno=d.deptno and loc="CHICAGO"
•3.查詢員工表中一共有幾種崗位類型。
select COUNT(DISTINCT job)
from emp;
•引出：求各部門平均工資，按照部門進行分組
group by（根據什么進行分組 一般與聚合函數配合使用）寫在where子句后，沒有where就寫在from子句后
select deptno,avg(sal)
from emp
group by deptno;
select deptno,dname,avg(sal)
from emp
natural join dept
group by deptno;
select emp.deptno,dname,avg(sal)
from emp,dept
where emp.deptno=dept.deptno
group by emp.deptno;
select emp.deptno,dname,avg(sal)
from emp,dept
where emp.deptno=dept.deptno and avg(sal)>2000
group by emp.deptno;
上面的寫法是錯誤的
錯在聚合函數不能用在where子句中。
如果條件里面帶有聚合函數，要用having子句，having子句寫在group by后面
總結一下：普通條件寫在where子句中，帶sum,max,min,count,avg函數的條件寫到having子句中。
把上面的語句改造下
select emp.deptno,dname,avg(sal)
from emp,dept
where emp.deptno=dept.deptno
group by emp.deptno
having avg(sal)>2000;
執(zhí)行順序： from--where-group by -having-select-order by
根據多列分組時，group by 子句中各列之間用逗號分隔
select deptno 部門編號,job 崗位,sum(sal)
from emp
group by deptno,job;
練習2
1.查詢每個部門的部門編號，部門名稱，部門人數，最高工資，最低工資，工資總和，平均工資。
select d.deptno,dname,COUNT(e.deptno) 部門人數,max(sal) 最高工資,min(sal) 最低工資,sum(sal) 工資總和,avg(sal) 平均工資
FROM emp e,dept d
where e.deptno=d.deptno
group by e.deptno
2.查詢每個部門，每個崗位的部門編號，部門名稱，崗位名稱，部門人數，最高工資，最低工資，工資總和，平均工資。
SELECT d.deptno,d.dname,e.job,COUNT(e.deptno),MAX(e.sal),MIN(e.sal),SUM(e.sal),avg(e.sal)
from dept d,emp e
where d.deptno=e.deptno
GROUP BY e.deptno,e.job;
3.查詢每個經理所管理的人數，經理編號，經理姓名。
select e.mgr,m.ename,count(e.empno) 管理的人數
from emp e,emp m
where e.mgr= m.empno
group by e.mgr;
改造成：
3.查詢每個經理所管理的人數，經理編號，經理姓名,要求包括沒有經理的人員信息。
select e.mgr,m.ename,count(e.empno) 管理的人數
from emp e
left outer join emp m
on e.mgr=m.empno
group by e.mgr,m.ename;
作業(yè)：
練習3
•1.查詢部門人數大于2的部門編號，部門名稱，部門人數。(having count )
•2.查詢部門平均工資大于2000，且人數大于2的部門編號，部門名稱，部門人數，部門平均工資，并按照部門人數升序排序。(having count ,avg order by,asc)
課后作業(yè)做能做的。
預習第2章
子查詢簡單的理解就是在where子句，having子句或者from子句中寫查詢語句（select 子句）
如果from子句中相當于利用查詢語句產生了一個臨時表
如果放在where 子句當中的，用于條件判斷的，并且聚合函數的
如果having子句當中的，也是用于條件判斷的，但是條件里包含聚合函數的。
–思考如下問題？
•1）查詢工資比Jones工資高的員工信息？
select sal
from emp
where ename='JONES'
SELECT *
FROM EMP
WHERE SAL>(select sal
from emp
where ename='JONES');
2)查詢工資最低的員工姓名
分兩步：第一步，查詢最低的工資是多少
select min(sal)
from emp;
800
第二步，查詢工資等于800的員工的信息
select *
from emp
where sal=(select min(sal)
from emp);
•子查詢可以嵌于以下SQL子句中：
–WHERE子句
–HAVING子句
–FROM子句
–子查詢要用括號括起來
–將子查詢放在比較運算符的右邊
–對于單行子查詢要使用單行運算符
–對于多行子查詢要使用多行運算符
子查詢分類
根據子查詢返回的行和列數量,分類單行，多行，多列
單行（子查詢只返回一行一列 <.> = <= >= <>）
顯示和雇員7369從事相同工作并且工資大于雇員7876的雇員的姓名和工作。(where 子句中用子查詢)
查詢工資最低的員工姓名,崗位及工資(where 子句中用子查詢)
having子句中使用子查詢：
查詢部門最低工資比20部門最低工資高的部門編號及最低工資
練習4
•1.查詢入職日期最早的員工姓名，入職日期
先查詢最早的入職日期
select min(hiredate)
from emp;
再查這個日期入職的人
select *
from emp
where hiredate=(select min(hiredate)
from emp);
•2.查詢工資比SMITH工資高并且工作地點在CHICAGO的員工姓名，工資，部門名稱
第一步先查詢smith的工資
select sal
from emp
where ename='SMITH'
第二步 查詢比該工資（800）高的并且工作地點在CHICAGO的
select ename,sal,dname
from emp,dept
where emp.deptno=dept.deptno
and sal>(select sal from emp where ename='SMITH')
and loc='CHICAGO';
•3.查詢入職日期比20部門入職日期最早的員工還要早的員工姓名，入職日期
第一步 查詢20部門入職日期最早的員工的入職日期
select min(hiredate)
from emp
where deptno=20
第二步 查詢入職日期比剛才的日期小的員工姓名，入職日期
select ename,hiredate
from emp
where hiredate<(select min(hiredate)
from emp
where deptno=20);
•4.查詢部門人數大于所有部門平均人數的的部門編號，部門名稱，部門人數
第1步：所有部門平均人數
所有部門的平均人數=總人數/部門個數=14/3
select count(empno)/COUNT(DISTINCT deptno)
from emp;
第2步：查詢部門人數大于剛才計算結果的的部門編號，部門名稱，部門人數
查詢各部門人數
select emp.deptno,count(*) ct,dname
from emp,dept
where emp.deptno=dept.deptno
group by emp.deptno,dname
having ct>(select count(empno)/COUNT(DISTINCT deptno)
from emp)
在 FROM 子句中使用子查詢
emp表里:empno deptno
需要別外一個表：deptno avgsal
第一步:分組查詢各部門及其平均工資
select deptno,avg(sal) avgsal
from emp
group by deptno
第二步：與員工表關聯，查詢結果
SELECT emp.ename,emp.sal,emp.deptno,b.avgsal
from emp,(select deptno,avg(sal) avgsal
from emp
group by deptno) b
where emp.deptno=b.deptno
and emp.sal>b.avgsal;
–多行操作符包括：
•IN
•ANY
ALL
引出：
–查詢是經理的員工姓名，工資
select empno,ename,sal
from emp
where empno in (select mgr from emp);
any
•= ANY：表示等于子查詢結果中的任意一個，即等于誰都可以，相當于IN。
查詢是經理的員工姓名，工資用另外一種方式=any 相當于in
select empno,ename,sal,mgr
from emp
where empno=any(select mgr from emp);
查詢比10號部門任意一個人的工資高的員工的信息
select *
from emp
where sal>any(select sal from emp where deptno=10)
查詢比10號部門任意一個人的工資低的員工的信息
select *
from emp
where sal
–查詢是經理的員工姓名，工資。
–查詢部門編號不為10，且工資比10部門任意一名員工工資高的員工編號，姓名，職位，工資。
all
–查詢部門編號不為20，且工資比20部門所有員工工資高的員工編號，姓名，職位，工資。
select empno,ename,job,sal
from emp
where deptno<>20
and sal>all(select sal from emp where deptno=20);
–查詢部門編號不為10，且工資和10部門所有員工工資相等的員工編號，姓名，職位，工資。
SELECT empno,ename,job,sal
from emp
where deptno<>10
and sal=all(select sal from emp where deptno=10);
練習5
•1.查詢入職日期比10部門任意一個員工晚的員工姓名、入職日期，不包括10部門員工
•2.查詢入職日期比10部門所有員工晚的員工姓名、入職日期，不包括10部門員工
•3.查詢職位和10部門任意一個員工職位相同的員工姓名，職位，不包括10部門員工
查詢不是經理的員工姓名。
SELECT ename
FROM emp
WHERE empno NOT IN
(SELECT mgr
FROM emp);
因為子查詢的結果中有一條空值，這條空值導致主查詢沒有記錄返回。這是因為所有的條件和空值比較結果都是空值。因此無論什么時候只要空值有可能成為子查詢結果集合中的一部分，就不能使用NOT IN 運算符
-- 1.查詢部門平均工資在2500元以上的部門名稱及平均工資。
select dname,avg(sal)
from emp
join dept
on emp.deptno = dept.DEPTNO
group by dname
having avg(sal)>2500
-- 2.查詢員工崗位中不是以“SA”開頭并且平均工資在2500元以上的崗位及平均工資，并按平均工資降序排序。
-- select job,avg(sal)
-- from emp
-- where job not like 'SA%'
-- group by JOB
-- having avg(sal) >2500
-- order by avg(sal) desc
-- 3.查詢部門人數在2人以上的部門名稱、最低工資、最高工資,并對求得的工資進行四舍五入到整數位。
-- select dname,min(round(sal,0)),max(round(sal,0))
-- from emp
-- join dept
-- on emp.deptno = dept.deptno
-- group by dname
-- having count(*)>2
-- 4.查詢崗位不為SALESMAN，工資和大于等于2500的崗位及每種崗位的工資和。
-- select job,sum(sal)
-- from emp
-- where job <> 'SALESMAN'
-- group by JOB
-- having sum(sal) >= 2500
-- 5.顯示經理號碼和經理姓名，這個經理所管理員工的最低工資，沒有經理的KING也要顯示，不包括最低工資小于3000的，按最低工資由高到低排序。
select m.empno,m.ename,min(e.sal)
from emp e
left join emp m
on e.mgr = m.empno
group by m.empno,m.ENAME
having min(e.sal)>=3000
order by min(e.sal) desc
-- 6.查詢工資高于編號為7782的員工工資，并且和7369號員工從事相同工作的員工的編號、姓名及工資。
-- select empno,ename,sal
-- from emp
-- where sal>(select sal
-- from emp
-- where empno = 7782)
-- and job = (select JOB
-- from emp
-- where empno = 7369)
-- 7.查詢工資最高的員工姓名和工資。
-- 8.查詢部門最低工資高于10號部門最低工資的部門的編號、名稱及部門最低工資。
-- 7.查詢工資最高的員工姓名和工資。
-- select ename,sal
-- from emp
-- where sal = (select max(sal)
-- from emp)
-- 8.查詢部門最低工資高于10號部門最低工資的部門的編號、名稱及部門最低工資。
-- select emp.deptno,dname,min(sal)
-- from emp
-- join dept
-- on emp.deptno = dept.deptno
-- group by emp.deptno,dname
-- having min(sal) > (select min(sal)
-- from emp
-- where deptno = 10)
-- 9.查詢員工工資為其部門最低工資的員工的編號和姓名及工資。
-- select empno,ename,sal
-- from emp,(select deptno,min(sal) minsal
-- from emp
-- group by deptno) t1
-- where emp.deptno = t1.deptno
-- and emp.sal = t1.minsal
-- select empno,ename,sal
-- from emp
-- where (deptno,sal) in(select deptno,min(sal)
-- from emp
-- group by deptno)
-- select empno,ename,sal
-- from emp t1
-- where sal = (select min(sal)
-- from emp
-- where deptno = t1.deptno)
-- 10.顯示經理是KING的員工姓名，工資。
-- select e.ename,e.sal
-- from emp e,emp m
-- where e.mgr = m.empno
-- and m.ename = 'KING'
-- select ename,sal
-- from emp
-- where mgr = (select EMPNO
-- from emp
-- where ename = 'KING')
-- 11.顯示比員工SMITH參加工作時間晚的員工姓名，工資，參加工作時間。
-- select ename,sal,hiredate
-- from emp
-- where hiredate > (select HIREDATE
-- from emp
-- where ename = 'SMITH')
-- 12.使用子查詢的方式查詢哪些職員在NEW YORK工作。
-- select *
-- from emp
-- where deptno in(select DEPTNO
-- from dept
-- where loc = 'NEW YORK')
-- 13.寫一個查詢顯示和員工SMITH工作在同一個部門的員工姓名，雇用日期，查詢結果中排除SMITH。
-- select ename,hiredate
-- from emp
-- where deptno = (select deptno
-- from emp
-- where ename = 'SMITH')
-- and ename <> 'SMITH'
-- 14.寫一個查詢顯示其工資比全體職員平均工資高的員工編號、姓名。
-- select empno,ename
-- from emp
-- where sal > (select avg(sal)
-- from emp)
-- 15. 顯示部門名稱和人數
-- select dname,c
-- from dept,(select deptno,count(*) c
-- from emp
-- group by deptno) t1
-- where dept.deptno = t1.deptno
select dname,count(*)
-- from emp,dept
-- where emp.deptno = dept.DEPTNO
-- group by dname
-- select dname,(select count(*)
-- from emp
-- where deptno = d.deptno)
-- from dept d
-- 16. 顯示每個部門的最高工資的員工
-- select *
-- from emp,(select deptno,max(sal) maxsal
-- from emp
-- group by deptno) t
-- where emp.deptno = t.deptno
-- and emp.sal = t.maxsal
-- 17. 顯示出和員工號7369部門相同的員工姓名，工資
-- select ename,sal
-- from emp
-- where deptno = (select deptno
-- from emp
-- where empno = 7369)
-- 18. 顯示出和姓名中包含“W”的員工相同部門的員工姓名
-- select ename
-- from emp
-- where deptno in (select DEPTNO
-- from emp
-- where ename like '%W%')
-- insert into i values('gej')
-- select length(k)
-- from i
create table dept(
deptno int(4) primary key,
dname varchar(10),
loc varchar(10)
)engine = innodb
create table emp(
empno int(4) primary key,
ename varchar(10),
deptno int(4),
constraint emp_dept_fk foreign key(deptno) references dept(deptno)
)engine = innodb
-- constraint tname1_tname_fk foreign key(cname1) references tname(cname)
create or replace view view_20
as
select empno,deptno from emp
create index emp_ename_index on emp(ename)
作業(yè)：
練習4
1.查詢入職日期最早的員工姓名，入職日期
select ename,hiredate
from emp
where hiredate = (select min(hiredate)
from emp
)
2.查詢工資比SMITH工資高并且工作地點在CHICAGO的員工姓名，工資，部門名稱
select ename,sal,dname
from emp
join dept
on emp.deptno = dept.deptno
where loc = 'CHICAGO'
and sal >(
select sal
from emp
where ename = 'SMITH')
3.查詢入職日期比20部門入職日期最早的員工還要早的員工姓名，入職日期
select ename,hiredate
from emp
where hiredate<
(select min(hiredate) from emp where deptno=20)
4.查詢部門人數大于所有部門平均人數的的部門編號，部門名稱，部門人數
select emp.deptno,dname,count(empno)
from emp
join dept
on emp.deptno=dept.deptno
group by emp.deptno,dname
having count(empno)>(select count(empno)/count(distinct emp.deptno)
from emp)
select emp.deptno,dname,count(*)
from emp
join dept
on emp.deptno = dept.deptno
group by emp.deptno,dname
having count(*)>(select avg(c)
from (select count(*) c
from emp
group by deptno) t)