Mysql第三天

高级查询

常用的分组函数

  • COUNT
  • MIN
  • MAX
  • SUM
  • AVG
分组函数的语法
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)行的数量

SELECT COUNT( COMM) from emp;

结果：4
使用DISTINCT 关键字

SELECT COUNT( DISTINCT DEPTNO) from emp;

结果：3
查询部门30有多少个员工领取奖金

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

MIN和MAX

MIN和MAX函数主要是返回每组的最小值和最大值。
MIN([DISTINCT | ALL] column | expression)
MAX([DISTINCT | ALL] column | expression)
MIN和MAX可以用于任何数据类型

  • 查询入职日期最早和最晚的日期
SELECT
    MIN(hirdate),
    MAX(HIREDATE)
FROM
    emp

SUM函数和AVG函数'

SUM和AVG函数分别返回每组的总和及平均值。
SUM([DISTINCT | ALL] column | expression)
AVG([DISTINCT | ALL] column | expression)
SUM和AVG函数都是只能够对数值类型的列或表达式操作。
查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和。

SELECT
    AVG(SAL) 平均工资,
    MIN(SAL) 最低工资,
    MAX(SAL) 最高工资,
    SUM(SAL) 工资和
FROM
    emp
WHERE
    JOB LIKE 'SALES%'
  • IFNULL 函数可以使分组函数强制包含含有空值的记录
SELECT
    COUNT(IFNULL(COMM,0))
FROM
    emp;

所有员工的平均奖金

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

有奖金的平均奖金

SELECT
    AVG(COMM)
FROM
    emp;

练习：

  • 查询部门20的员工，每个月的工资总和及平均工资。
SELECT
    AVG(sal) 平均工资,
    SUM(sal) 工资和
FROM
    emp
WHERE
    deptno = 20;
  • 查询工作在CHICAGO的员工人数，最高工资及最低工资。
SELECT
    COUNT(m.empno) 员工人数,
    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 语句

通过 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;

使用规则:

  • 在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中。
select ename, deptno, avg(sal) from emp group by deptno, ename;
  • GROUP BY 所指定的列并不是必须出现在SELECT 列表中
select ename, avg(sal) from emp group by deptno, ename;

按多个列分组

查询每个部门每个岗位的工资总和

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

练习：
查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT
    d.DEPTNO 部门编号,
    d.DNAME 部门名称,
    COUNT(*) 部门人数,
    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;

查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT
    d.DEPTNO 部门编号,
    d.DNAME 部门名称,
    e.JOB 岗位名称,
    COUNT(*) 部门人数,
    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;

查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT
    COUNT(*) 管理人数,
    m.EMPNO 经理编号,
    m.ENAME 经理姓名
FROM
    emp w
LEFT OUTER JOIN emp m ON w.MGR = m.EMPNO
GROUP BY
    m.EMPNO,
    m.ENAME

分组后的筛选

  • 使用WHERE子句中限制分组后失败， 应该使用having
    每个组内最高薪水大于3000时候输出
SELECT
    e.DEPTNO,
    MAX(e.sal)
FROM
    emp e
WHERE
    MAX(e.sal) > 3000
GROUP BY
    e.DEPTNO

Having

使用步骤：

  • 记录已经分组.
  • 使用过组函数.
  • 与 HAVING 子句匹配的结果才输出
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时输出，并按照结果的工资总数进行升序排序

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

SELECT语句执行过程

1、通过FROM子句中找到需要查询的表；
2、通过WHERE子句进行非分组函数筛选判断；
3、通过GROUP BY子句完成分组操作；
4、通过HAVING子句完成组函数筛选判断；
5、通过SELECT子句选择显示的列或表达式及组函数；
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
  • 查询部门人数大于2的部门编号，部门名称，部门人数。
SELECT
    e.DEPTNO 部门编号,
    d.DNAME 部门名称,
    COUNT(*) 部门人数
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    e.DEPTNO
HAVING
    COUNT(*) > 2
  • 查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
SELECT
    e.DEPTNO 部门编号,
    d.DNAME 部门名称,
    COUNT(*) 部门人数,
    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);
  • 括号内的查询叫做子查询，也叫内部查询，先于主查询执行。
  • 子查询的结果被主查询（外部查询）使用
  • expr operator包括比较运算符

单行运算符：>、=、>=、<、<>、<=

多行运算符： IN、ANY、ALL

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

  • WHERE子句
  • HAVING子句
  • FROM子句

子查询类型

  • 子查询要用括号括起来
  • 将子查询放在比较运算符的右边
  • 对于单行子查询要使用单行运算符
  • 对于多行子查询要使用多行运算符

单行子查询（>、=、>=、<、<>、<=）

  • 显示和工号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)

练习：
查询入职日期最早的员工姓名，入职日期

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

查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

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
    有一个满足就可以
  • 查询是经理的员工姓名和工资
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;

子查询中的空值

查询不是经理的员工姓名

SELECT ename FROM emp WHERE EMPNO not in (SELECT mgr  from emp);
  • 子查询返回的结果中含有空值
    上面的SQL语句试图查找出没有下属的雇员，逻辑上，这个SQL语句应该会返回8条记录，但是却一条也没返回，why?
    因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符
    正确的查询不是经理的员工姓名
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;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,445评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,889评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,047评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,760评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,745评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,638评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,011评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,669评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,923评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,655评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,740评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,406评论 4赞 320
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,995评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,961评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,197评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,023评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,483评论 2赞 342