高級查詢

常用的分組函數(shù)

COUNT
MIN
MAX
SUM
AVG

分組函數(shù)的語法
SELECT    [column,] group_function(column)
FROM        table
[WHERE    condition]
[GROUP BY    column]
[HAVING       group_function(column)expression 
[ORDER BY    column |  group_function(column)expression];

COUNT

COUNT( * | {[DISTINCT | ALL] column | expression})

COUNT( [DISTINCT | ALL] column | expression)：返回滿足條件的非空(NULL)行的數(shù)量

SELECT COUNT( COMM) from emp;

結(jié)果：4
使用DISTINCT 關(guān)鍵字

SELECT COUNT( DISTINCT DEPTNO) from emp;

結(jié)果：3
查詢部門30有多少個員工領(lǐng)取獎金

SELECT COUNT(comm) from emp where deptno=30;

MIN和MAX

MIN和MAX函數(shù)主要是返回每組的最小值和最大值。
MIN([DISTINCT | ALL] column | expression)
MAX([DISTINCT | ALL] column | expression)
MIN和MAX可以用于任何數(shù)據(jù)類型

查詢?nèi)肼毴掌谧钤绾妥钔淼娜掌?/li>

SELECT
    MIN(hirdate),
    MAX(HIREDATE)
FROM
    emp

SUM函數(shù)和AVG函數(shù)'

SUM和AVG函數(shù)分別返回每組的總和及平均值办悟。
SUM([DISTINCT | ALL] column | expression)
AVG([DISTINCT | ALL] column | expression)
SUM和AVG函數(shù)都是只能夠?qū)?shù)值類型的列或表達式操作甲脏。
查詢職位以SALES開頭的所有員工平均工資妻往、最低工資、最高工資震檩、工資和。

SELECT
    AVG(SAL) 平均工資,
    MIN(SAL) 最低工資,
    MAX(SAL) 最高工資,
    SUM(SAL) 工資和
FROM
    emp
WHERE
    JOB LIKE 'SALES%'

IFNULL 函數(shù)可以使分組函數(shù)強制包含含有空值的記錄

SELECT
    COUNT(IFNULL(COMM,0))
FROM
    emp;

所有員工的平均獎金

SELECT
    AVG(IFNULL(COMM,0))
FROM
    emp;

有獎金的平均獎金

SELECT
    AVG(COMM)
FROM
    emp;

練習(xí)：

查詢部門20的員工，每個月的工資總和及平均工資赢乓。

SELECT
    AVG(sal) 平均工資,
    SUM(sal) 工資和
FROM
    emp
WHERE
    deptno = 20;

查詢工作在CHICAGO的員工人數(shù)忧侧，最高工資及最低工資。

SELECT
    COUNT(m.empno) 員工人數(shù),
    MAX(sal) 最高工資,
    MIN(sal) 最低工資
FROM
    emp m,
    dept d
WHERE
    m.deptno = d.DEPTNO
AND d.loc = 'CHICAGO';

查詢員工表中一共有幾種崗位類型

SELECT
    COUNT(DISTINCT JOB)
FROM
    emp;

group by 語句

image

通過 GROUP BY 子句可將表中滿足WHERE條件的記錄按照指定的列劃分成若干個小組牌芋，其中GROUP BY子句指定要分組的
查詢每個部門的部門編號蚓炬，平均工資

select deptno, avg(sal)  from emp group by deptno;

查詢每個部門的部門編號，每個部門的最低工資
查詢每個部門的部門編號躺屁，每個部門的最高工資

select deptno, min(sal) from emp group by deptno;
select deptno, max(sal) from emp group by deptno;

使用規(guī)則:

在SELECT列表中除了分組函數(shù)那些項肯夏，所有列都必須包含在GROUP BY 子句中。

select ename, deptno, avg(sal) from emp group by deptno, ename;

GROUP BY 所指定的列并不是必須出現(xiàn)在SELECT 列表中

select ename, avg(sal) from emp group by deptno, ename;

按多個列分組

查詢每個部門每個崗位的工資總和

select deptno, job, sum(sal) from emp group by deptno, job;

練習(xí)：
查詢每個部門的部門編號犀暑，部門名稱驯击，部門人數(shù)，最高工資耐亏，最低工資徊都，工資總和，平均工資广辰。

SELECT
    d.DEPTNO 部門編號,
    d.DNAME 部門名稱,
    COUNT(*) 部門人數(shù),
    MAX(e.SAL) 最高工資,
    MIN(e.SAL) 最低工資,
    SUM(e.SAL) 工資總和,
    AVG(e.SAL) 平均工資
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    e.DEPTNO, d.DNAME;

查詢每個部門暇矫，每個崗位的部門編號，部門名稱轨域，崗位名稱袱耽，部門人數(shù)，最高工資干发，最低工資朱巨，工資總和，平均工資枉长。

SELECT
    d.DEPTNO 部門編號,
    d.DNAME 部門名稱,
    e.JOB 崗位名稱,
    COUNT(*) 部門人數(shù),
    MAX(e.SAL) 最高工資,
    MIN(e.SAL) 最低工資,
    SUM(e.SAL) 工資總和,
    AVG(e.SAL) 平均工資
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    d.DEPTNO,d.DNAME, e.JOB;

查詢每個經(jīng)理所管理的人數(shù)冀续，經(jīng)理編號，經(jīng)理姓名必峰，要求包括沒有經(jīng)理的人員信息洪唐。

SELECT
    COUNT(*) 管理人數(shù),
    m.EMPNO 經(jīng)理編號,
    m.ENAME 經(jīng)理姓名
FROM
    emp w
LEFT OUTER JOIN emp m ON w.MGR = m.EMPNO
GROUP BY
    m.EMPNO,
    m.ENAME

分組后的篩選

使用WHERE子句中限制分組后失敗，應(yīng)該使用having
每個組內(nèi)最高薪水大于3000時候輸出

SELECT
    e.DEPTNO,
    MAX(e.sal)
FROM
    emp e
WHERE
    MAX(e.sal) > 3000
GROUP BY
    e.DEPTNO

image

Having

使用步驟：

記錄已經(jīng)分組.
使用過組函數(shù).
與 HAVING 子句匹配的結(jié)果才輸出

SELECT    column, group_function
FROM        table
[WHERE    condition]
[GROUP BY    group_by_expression]
[HAVING    group_condition]
[ORDER BY    column];

舉個栗子
查詢每個部門最高薪水大于2900的部門編號吼蚁，最高工資

SELECT
    e.DEPTNO,
    MAX(e.sal)
FROM
    emp e
GROUP BY
    e.DEPTNO
HAVING
    MAX(e.SAL) > 2900

查詢每個工種凭需，工資總和，并且工作不是以 SALE開頭的且每個工種的工資和大于5000時輸出肝匆，并按照結(jié)果的工資總數(shù)進行升序排序

SELECT
    job 工種, 
    SUM(sal) 工資總和 
FROM
    emp
WHERE
    job NOT LIKE 'SALES%'
GROUP BY
    job
HAVING
    SUM(sal) > 5000
ORDER BY
    SUM(sal);

SELECT語句執(zhí)行過程

1粒蜈、通過FROM子句中找到需要查詢的表；
2旗国、通過WHERE子句進行非分組函數(shù)篩選判斷枯怖；
3、通過GROUP BY子句完成分組操作能曾；
4度硝、通過HAVING子句完成組函數(shù)篩選判斷肿轨；
5、通過SELECT子句選擇顯示的列或表達式及組函數(shù)蕊程；
6椒袍、通過ORDER BY子句進行排序操作。
舉個栗子

SELECT
    DEPTNO,
    JOB,
    AVG(SAL)
FROM
    emp
WHERE
    JOB IN (
        'CLERK',
        'SALESMAN',
        'MANAGER'
    )
GROUP BY
    DEPTNO,
    JOB
HAVING
    AVG(SAL)> 1200
ORDER BY 3 DESC

查詢部門人數(shù)大于2的部門編號存捺，部門名稱槐沼，部門人數(shù)。

SELECT
    e.DEPTNO 部門編號,
    d.DNAME 部門名稱,
    COUNT(*) 部門人數(shù)
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    e.DEPTNO
HAVING
    COUNT(*) > 2

查詢部門平均工資大于2000捌治，且人數(shù)大于2的部門編號岗钩，部門名稱，部門人數(shù)肖油，部門平均工資兼吓，并按照部門人數(shù)升序排序。

SELECT
    e.DEPTNO 部門編號,
    d.DNAME 部門名稱,
    COUNT(*) 部門人數(shù),
    AVG(e.SAL) 部門平均工資
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    e.DEPTNO
HAVING
    (COUNT(*) > 2)
AND (AVG(e.SAL) > 2000)
ORDER BY
    3

子查詢

查詢里還有查詢

查詢工資比Jones工資高的員工信息

SELECT
    *
FROM
    emp e
WHERE
    e.SAL > ( SELECT e.SAL from emp e WHERE e.ENAME = 'JONES');

查詢工資最低的員工姓名

SELECT
    e.ENAME
FROM
    emp e
WHERE
    e.SAL = (SELECT MIN(e.sal) from emp e);

語法：

SELECT    select_list
FROM    table
WHERE    expr operator
         (SELECT    select_list
           FROM        table);

括號內(nèi)的查詢叫做子查詢森枪，也叫內(nèi)部查詢视搏，先于主查詢執(zhí)行。
子查詢的結(jié)果被主查詢（外部查詢）使用
expr operator包括比較運算符

單行運算符：>县袱、=浑娜、>=、<式散、<>筋遭、<=

多行運算符： IN、ANY暴拄、ALL

子查詢可以嵌于以下SQL子句中：

WHERE子句
HAVING子句
FROM子句

子查詢類型

image

子查詢要用括號括起來
將子查詢放在比較運算符的右邊
對于單行子查詢要使用單行運算符
對于多行子查詢要使用多行運算符

單行子查詢（>漓滔、=、>=乖篷、<响驴、<>、<=）

顯示和工號7369從事相同工作并且工資大于7876的員工姓名和工作

SELECT e.ENAME, e.JOB
from emp e
WHERE
    e.JOB = (SELECT JOB FROM emp WHERE EMPNO = 7369) and 
    e.sal > (SELECT SAL FROM emp WHERE EMPNO = 7876);
```sql
- 查詢部門最低工資比20部門最低工資高的部門編號及最低工資
```sql
SELECT e.DEPTNO 部門編號 ,MIN(e.SAL)
from emp e
GROUP BY e.DEPTNO
HAVING
MIN(e.SAL) > (SELECT MIN(sal) from emp WHERE DEPTNO = 20)

練習(xí)：
查詢?nèi)肼毴掌谧钤绲膯T工姓名撕蔼，入職日期

SELECT
  e.ename,
  e.hiredate
FROM
  emp e
WHERE
  HIREDATE = (SELECT min(hiredate) FROM emp);

查詢工資比SMITH工資高并且工作地點在CHICAGO的員工姓名豁鲤，工資，部門名稱

SELECT
  e.ename,
  e.sal,
  d.dname
FROM
  emp e,
  dept d
WHERE
  e.deptno = d.deptno
AND e.sal > (
  SELECT
    sal
  FROM
    emp
  WHERE
    ename = 'SMITH'
)
AND d.loc = 'CHICAGO';

查詢?nèi)肼毴掌诒?0部門入職日期最早的員工還要早的員工姓名鲸沮，入職日期

SELECT
  e.ename,
  e.hiredate
FROM
  emp e
WHERE
  e.hiredate < (
    SELECT
      min(hiredate)
    FROM
      emp
    WHERE
      deptno = 20
  )

多行子查詢

多行子查詢進行比較時畅形，需要使用多行操作符，多行操作符包括:

IN 判斷是否與子查詢的任意一個返回值相同诉探。

SELECT ENAME, SAL
FROM emp 
WHERE EMPNO in (SELECT m.mgr FROM emp m);

ANY
有一個滿足就可以
查詢是經(jīng)理的員工姓名和工資

SELECT ENAME, SAL
FROM emp 
WHERE EMPNO  = ANY(SELECT m.mgr FROM emp m);

查詢部門編號不為10，且工資比10部門任意一名員工工資高的員工編號棍厌，姓名肾胯，職位竖席，工資

SELECT EMPNO, ENAME,JOB, SAL
FROM emp 
WHERE SAL 
>ANY(SELECT sal from emp WHERE DEPTNO = 10) AND DEPTNO <> 10;

ALL
每一個都要滿足
查詢部門編號不為20，且工資比20部門所有員工工資高的員工編號敬肚，姓名毕荐，職位，工資艳馒。

SELECT EMPNO, ENAME,JOB, SAL
FROM emp 
WHERE SAL 
>ALL(SELECT sal from emp WHERE DEPTNO = 20) AND DEPTNO <> 20;

查詢部門編號不為10憎亚，且工資比10部門所有員工工資低的員工編號，姓名弄慰，職位第美，工資。

SELECT EMPNO, ENAME,JOB, SAL
FROM emp 
WHERE SAL 
< ALL(SELECT sal from emp WHERE DEPTNO = 10) AND DEPTNO <> 10;

子查詢中的空值

查詢不是經(jīng)理的員工姓名

SELECT ename FROM emp WHERE EMPNO not in (SELECT mgr  from emp);

子查詢返回的結(jié)果中含有空值
上面的SQL語句試圖查找出沒有下屬的雇員陆爽，邏輯上什往，這個SQL語句應(yīng)該會返回8條記錄，但是卻一條也沒返回慌闭，why?
因為子查詢的結(jié)果中有一條空值别威，這條空值導(dǎo)致主查詢沒有記錄返回。這是因為所有的條件和空值比較結(jié)果都是空值驴剔。因此無論什么時候只要空值有可能成為子查詢結(jié)果集合中的一部分省古，就不能使用NOT IN 運算符
正確的查詢不是經(jīng)理的員工姓名

SELECT
    ename
FROM
    emp
WHERE
    empno NOT IN (SELECT mgr FROM emp WHERE MGR is NOT NULL);

FROM 子句中使用子查詢

查詢比自己部門平均工資高的員工姓名，工資丧失，部門編號豺妓，部門平均工資

SELECT e.ename 員工姓名, e.SAL 工資, e.DEPTNO 部門編號, a.avgsal 部門平均工資
FROM emp e, (SELECT DEPTNO, AVG(SAL) avgsal  from emp GROUP BY DEPTNO) a
WHERE
e.DEPTNO = a.DEPTNO AND e.SAL > a.avgsal;

MySQL-高級查詢

MySQL-高級查詢

高級查詢

常用的分組函數(shù)

COUNT

MIN和MAX

SUM函數(shù)和AVG函數(shù)'

group by 語句

按多個列分組

分組后的篩選

Having

SELECT語句執(zhí)行過程

子查詢

單行運算符：>县袱、=浑娜、>=、<式散、<>筋遭、<=

多行運算符： IN、ANY暴拄、ALL

子查詢類型

單行子查詢（>漓滔、=、>=乖篷、<响驴、<>、<=）

多行子查詢

子查詢中的空值

FROM 子句中使用子查詢