• 【二】【SQL】去重表数据及分组聚合查询

    去重表数据

    表的准备工作

    去除表中重复的数据，重复的数据只留一份。

     

    1. mysql> create table duplicate_table (
    2.     -> id int,
    3.     -> name varchar(20)
    4.     -> );
    5. Query OK, 0 rows affected (0.03 sec)
    6.  
    7. mysql> insert into duplicate_table values
    8.     -> (100,'aaa'),
    9.     -> (100,'aaa'),
    10.     -> (200,'bbb'),
    11.     -> (200,'bbb'),
    12.     -> (200,'bbb'),
    13.     -> (300,'ccc');
    14. Query OK, 6 rows affected (0.01 sec)
    15. Records: 6  Duplicates: 0  Warnings: 0
    16.  
    17. mysql> select *from duplicate_table;
    18. +------+------+
    19. | id   | name |
    20. +------+------+
    21. |  100 | aaa  |
    22. |  100 | aaa  |
    23. |  200 | bbb  |
    24. |  200 | bbb  |
    25. |  200 | bbb  |
    26. |  300 | ccc  |
    27. +------+------+
    28. 6 rows in set (0.00 sec)
    29.  
    30. mysql> 
     

    去除表中重复的数据，重复的数据只留一份
     

     

    1. mysql> create table no_duplicate_table like duplicate_table;
    2. Query OK, 0 rows affected (0.03 sec)
    3.  
    4. mysql> desc no_duplicate_table;
    5. +-------+-------------+------+-----+---------+-------+
    6. | Field | Type        | Null | Key | Default | Extra |
    7. +-------+-------------+------+-----+---------+-------+
    8. | id    | int(11)     | YES  |     | NULL    |       |
    9. | name  | varchar(20) | YES  |     | NULL    |       |
    10. +-------+-------------+------+-----+---------+-------+
    11. 2 rows in set (0.00 sec)
    12.  
    13. mysql> desc duplicate_table;
    14. +-------+-------------+------+-----+---------+-------+
    15. | Field | Type        | Null | Key | Default | Extra |
    16. +-------+-------------+------+-----+---------+-------+
    17. | id    | int(11)     | YES  |     | NULL    |       |
    18. | name  | varchar(20) | YES  |     | NULL    |       |
    19. +-------+-------------+------+-----+---------+-------+
    20. 2 rows in set (0.00 sec)
    21.  
    22. mysql> select distinct *from no_duplicate_table ;
    23. Empty set (0.00 sec)
    24.  
    25. mysql> select distinct *from duplicate_table ;
    26. +------+------+
    27. | id   | name |
    28. +------+------+
    29. |  100 | aaa  |
    30. |  200 | bbb  |
    31. |  300 | ccc  |
    32. +------+------+
    33. 3 rows in set (0.00 sec)
    34.  
    35. mysql> insert into no_duplicate_table select distinct *from duplicate_table;
    36. Query OK, 3 rows affected (0.01 sec)
    37. Records: 3  Duplicates: 0  Warnings: 0
    38.  
    39. mysql> select *from no_duplicate_table;
    40. +------+------+
    41. | id   | name |
    42. +------+------+
    43. |  100 | aaa  |
    44. |  200 | bbb  |
    45. |  300 | ccc  |
    46. +------+------+
    47. 3 rows in set (0.00 sec)
    48.  
    49. mysql> rename table duplicate_table to old_duplicate_table ;
    50. Query OK, 0 rows affected (0.04 sec)
    51.  
    52. mysql> rename table no_duplicate_table to duplicate_table ;
    53. Query OK, 0 rows affected (0.01 sec)
    54.  
    55. mysql> select *from duplicate_table ;
    56. +------+------+
    57. | id   | name |
    58. +------+------+
    59. |  100 | aaa  |
    60. |  200 | bbb  |
    61. |  300 | ccc  |
    62. +------+------+
    63. 3 rows in set (0.00 sec)
    64.  
    65. mysql> 
     

     

    聚合统计
     

     
    函数
         		 
    说明
     
     
    COUNT（DISTINCT）
         		 
    返回查询到的数据的 数量
     
     
    SUM（DISTINCT）
         		 
    返回查询到的数据的 总和，不是数字没有意义
     
     
    AVG（DISTINCT）
         		 
    返回查询到的数据的 平均值，不是数字没有意义
     
     
    MAX（DISTINCT）
         		 
    返回查询到的数据的 最大值，不是数字没有意义
     
     
    MIN（DISTINCT）
         		 
    返回查询到的数据的 最小值，不是数字没有意义
     
     

    count简单使用
     

     

    1. mysql> select *from exam_result;
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  1 | 唐三藏    |     134 |   98 |      56 |
    6. |  3 | 猪悟能    |     176 |   98 |      90 |
    7. |  4 | 曹孟德    |     140 |   90 |      67 |
    8. |  6 | 孙权      |     140 |   73 |      78 |
    9. |  7 | 宋公明    |     150 |   95 |      30 |
    10. +----+-----------+---------+------+---------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select count(*) from exam_result;
    14. +----------+
    15. | count(*) |
    16. +----------+
    17. |        5 |
    18. +----------+
    19. 1 row in set (0.00 sec)
    20.  
    21. mysql> select count(1) 总数 from exam_result;
    22. +--------+
    23. | 总数   |
    24. +--------+
    25. |      5 |
    26. +--------+
    27. 1 row in set (0.00 sec)
    28.  
    29. mysql> select count(2) 总数 from exam_result;
    30. +--------+
    31. | 总数   |
    32. +--------+
    33. |      5 |
    34. +--------+
    35. 1 row in set (0.00 sec)
    36.  
    37. mysql> select math from exam_result;
    38. +------+
    39. | math |
    40. +------+
    41. |   98 |
    42. |   98 |
    43. |   90 |
    44. |   73 |
    45. |   95 |
    46. +------+
    47. 5 rows in set (0.00 sec)
    48.  
    49. mysql> select count(math) from exam_result;
    50. +-------------+
    51. | count(math) |
    52. +-------------+
    53. |           5 |
    54. +-------------+
    55. 1 row in set (0.00 sec)
    56.  
    57. mysql> select count(math) as res from exam_result;
    58. +-----+
    59. | res |
    60. +-----+
    61. |   5 |
    62. +-----+
    63. 1 row in set (0.00 sec)
    64.  
    65. mysql> 
     

    统计本次考试的数学成绩分数个数
     

     

    1. mysql> select math from exam_result;
    2. +------+
    3. | math |
    4. +------+
    5. |   98 |
    6. |   98 |
    7. |   90 |
    8. |   73 |
    9. |   95 |
    10. +------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select distinct count(math) as res from exam_result;
    14. +-----+
    15. | res |
    16. +-----+
    17. |   5 |
    18. +-----+
    19. 1 row in set (0.01 sec)
    20.  
    21. mysql> select count(distinct math) as res from exam_result;
    22. +-----+
    23. | res |
    24. +-----+
    25. |   4 |
    26. +-----+
    27. 1 row in set (0.00 sec)
    28.  
    29. mysql> 
     

    数学英语平均分，不及格个数，不及格总分，不及格平均分
     

     

    1. mysql> select sum(math) from exam_result;
    2. +-----------+
    3. | sum(math) |
    4. +-----------+
    5. |       454 |
    6. +-----------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> select sum(math)/count(*) from exam_result;
    10. +--------------------+
    11. | sum(math)/count(*) |
    12. +--------------------+
    13. |               90.8 |
    14. +--------------------+
    15. 1 row in set (0.00 sec)
    16.  
    17. mysql> select sum(english)/count(*) from exam_result;
    18. +-----------------------+
    19. | sum(english)/count(*) |
    20. +-----------------------+
    21. |                  64.2 |
    22. +-----------------------+
    23. 1 row in set (0.00 sec)
    24.  
    25. mysql> select count(*) from exam_result where math<60;
    26. +----------+
    27. | count(*) |
    28. +----------+
    29. |        0 |
    30. +----------+
    31. 1 row in set (0.00 sec)
    32.  
    33. mysql> select count(*) from exam_result where english<60;
    34. +----------+
    35. | count(*) |
    36. +----------+
    37. |        2 |
    38. +----------+
    39. 1 row in set (0.00 sec)
    40.  
    41. mysql> select *from exam_result where english<60;
    42. +----+-----------+---------+------+---------+
    43. | id | name      | chinese | math | english |
    44. +----+-----------+---------+------+---------+
    45. |  1 | 唐三藏    |     134 |   98 |      56 |
    46. |  7 | 宋公明    |     150 |   95 |      30 |
    47. +----+-----------+---------+------+---------+
    48. 2 rows in set (0.00 sec)
    49.  
    50. mysql> select sum(english) from exam_result where english<60;
    51. +--------------+
    52. | sum(english) |
    53. +--------------+
    54. |           86 |
    55. +--------------+
    56. 1 row in set (0.00 sec)
    57.  
    58. mysql> select sum(english)/count(english) from exam_result where english<60;
    59. +-----------------------------+
    60. | sum(english)/count(english) |
    61. +-----------------------------+
    62. |                          43 |
    63. +-----------------------------+
    64. 1 row in set (0.00 sec)
    65.  
    66. mysql> 
     

    统计平均总分
     

     

    1. mysql> select sum(math)/count(*) from exam_result;
    2. +--------------------+
    3. | sum(math)/count(*) |
    4. +--------------------+
    5. |               90.8 |
    6. +--------------------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> select avg(math) from exam_result;
    10. +-----------+
    11. | avg(math) |
    12. +-----------+
    13. |      90.8 |
    14. +-----------+
    15. 1 row in set (0.00 sec)
    16.  
    17. mysql> select avg(math+chinese+english) from exam_result;
    18. +---------------------------+
    19. | avg(math+chinese+english) |
    20. +---------------------------+
    21. |                       303 |
    22. +---------------------------+
    23. 1 row in set (0.00 sec)
    24.  
    25. mysql> select name,math+english+chinese from exam_result;
    26. +-----------+----------------------+
    27. | name      | math+english+chinese |
    28. +-----------+----------------------+
    29. | 唐三藏    |                  288 |
    30. | 猪悟能    |                  364 |
    31. | 曹孟德    |                  297 |
    32. | 孙权      |                  291 |
    33. | 宋公明    |                  275 |
    34. +-----------+----------------------+
    35. 5 rows in set (0.00 sec)
    36.  
    37. mysql> select max(english) from exam_result;
    38. +--------------+
    39. | max(english) |
    40. +--------------+
    41. |           90 |
    42. +--------------+
    43. 1 row in set (0.00 sec)
    44.  
    45. mysql> select name ,max(english) from exam_result;
    46. ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list containode=only_full_group_by
    47. mysql> 
     

    返回>70分以上的数学最低分
     

     

    1. mysql> select math from exam_result;
    2. +------+
    3. | math |
    4. +------+
    5. |   98 |
    6. |   98 |
    7. |   90 |
    8. |   73 |
    9. |   95 |
    10. +------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select math from exam_result where math>70;
    14. +------+
    15. | math |
    16. +------+
    17. |   98 |
    18. |   98 |
    19. |   90 |
    20. |   73 |
    21. |   95 |
    22. +------+
    23. 5 rows in set (0.00 sec)
    24.  
    25. mysql> select min(math) from exam_result where math>70;
    26. +-----------+
    27. | min(math) |
    28. +-----------+
    29. |        73 |
    30. +-----------+
    31. 1 row in set (0.00 sec)
    32.  
    33. mysql> 
     

    分组聚合查询
     

    导入数据库样例sql文件
     

    scott_data.sql（文件名）（下面是文件内容）
     

    1.       
    2. DROP database IF EXISTS `scott`;
    3. CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    4.  
    5. USE `scott`;
    6.  
    7. DROP TABLE IF EXISTS `dept`;
    8. CREATE TABLE `dept` (
    9.   `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
    10.   `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
    11.   `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
    12. );
    13.  
    14.  
    15. DROP TABLE IF EXISTS `emp`;
    16. CREATE TABLE `emp` (
    17.   `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
    18.   `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
    19.   `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
    20.   `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
    21.   `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
    22.   `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
    23.   `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
    24.   `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
    25. );
    26.  
    27.  
    28. DROP TABLE IF EXISTS `salgrade`;
    29. CREATE TABLE `salgrade` (
    30.   `grade` int(11) DEFAULT NULL COMMENT '等级',
    31.   `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
    32.   `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
    33. );
    34.  
    35.  
    36. insert into dept (deptno, dname, loc)
    37. values (10, 'ACCOUNTING', 'NEW YORK');
    38. insert into dept (deptno, dname, loc)
    39. values (20, 'RESEARCH', 'DALLAS');
    40. insert into dept (deptno, dname, loc)
    41. values (30, 'SALES', 'CHICAGO');
    42. insert into dept (deptno, dname, loc)
    43. values (40, 'OPERATIONS', 'BOSTON');
    44.  
    45. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    46. values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
    47.  
    48. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    49. values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
    50.  
    51. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    52. values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
    53.  
    54. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    55. values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
    56.  
    57. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    58. values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
    59.  
    60. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    61. values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
    62.  
    63. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    64. values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
    65.  
    66. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    67. values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
    68.  
    69. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    70. values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
    71.  
    72. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    73. values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
    74.  
    75. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    76. values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
    77.  
    78. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    79. values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
    80.  
    81. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    82. values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
    83.  
    84. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    85. values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
    86.  
    87. insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
    88. insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
    89. insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
    90. insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
    91. insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
    92.  
     

     rz选择scott_data.sql文件导入数据
     

    1. [root@VM-8-12-centos d1]# cd /var/lib/mysql
    2. [root@VM-8-12-centos mysql]# ls
    3. auto.cnf         d1              ibtmp1              private_key.pem
    4. ca-key.pem       ib_buffer_pool  mysql               public_key.pem
    5. ca.pem           ibdata1         mysql.sock          server-cert.pem
    6. client-cert.pem  ib_logfile0     mysql.sock.lock     server-key.pem
    7. client-key.pem   ib_logfile1     performance_schema  sys
    8. [root@VM-8-12-centos mysql]# rz     
    9.  
    10. [root@VM-8-12-centos mysql]# ls
    11. auto.cnf         d1              ibtmp1              private_key.pem  sys
    12. ca-key.pem       ib_buffer_pool  mysql               public_key.pem
    13. ca.pem           ibdata1         mysql.sock          scott_data.sql
    14. client-cert.pem  ib_logfile0     mysql.sock.lock     server-cert.pem
    15. client-key.pem   ib_logfile1     performance_schema  server-key.pem
     

     

    1. mysql> source /var/lib/mysql/scott_data.sql;
    2. Query OK, 0 rows affected, 1 warning (0.00 sec)
    3.  
    4. Query OK, 1 row affected (0.00 sec)
    5.  
    6. Database changed
    7. Query OK, 0 rows affected, 1 warning (0.00 sec)
    8.  
    9. Query OK, 0 rows affected (0.03 sec)
    10.  
    11. Query OK, 0 rows affected, 1 warning (0.00 sec)
    12.  
    13. Query OK, 0 rows affected (0.02 sec)
    14.  
    15. Query OK, 0 rows affected, 1 warning (0.00 sec)
    16.  
    17. Query OK, 0 rows affected (0.03 sec)
    18.  
    19. Query OK, 1 row affected (0.00 sec)
    20.  
    21. Query OK, 1 row affected (0.00 sec)
    22.  
    23. Query OK, 1 row affected (0.01 sec)
    24.  
    25. Query OK, 1 row affected (0.00 sec)
    26.  
    27. Query OK, 1 row affected (0.01 sec)
    28.  
    29. Query OK, 1 row affected (0.00 sec)
    30.  
    31. Query OK, 1 row affected (0.00 sec)
    32.  
    33. Query OK, 1 row affected (0.00 sec)
    34.  
    35. Query OK, 1 row affected (0.01 sec)
    36.  
    37. Query OK, 1 row affected (0.00 sec)
    38.  
    39. Query OK, 1 row affected (0.00 sec)
    40.  
    41. Query OK, 1 row affected (0.01 sec)
    42.  
    43. Query OK, 1 row affected (0.00 sec)
    44.  
    45. Query OK, 1 row affected (0.00 sec)
    46.  
    47. Query OK, 1 row affected (0.00 sec)
    48.  
    49. Query OK, 1 row affected (0.01 sec)
    50.  
    51. Query OK, 1 row affected (0.00 sec)
    52.  
    53. Query OK, 1 row affected (0.00 sec)
    54.  
    55. Query OK, 1 row affected (0.01 sec)
    56.  
    57. Query OK, 1 row affected (0.00 sec)
    58.  
    59. Query OK, 1 row affected (0.02 sec)
    60.  
    61. Query OK, 1 row affected (0.00 sec)
    62.  
    63. Query OK, 1 row affected (0.00 sec)
    64.  
    65. mysql> show tables;
    66. +-----------------+
    67. | Tables_in_scott |
    68. +-----------------+
    69. | dept            |
    70. | emp             |
    71. | salgrade        |
    72. +-----------------+
    73. 3 rows in set (0.01 sec)
    74.  
    75. mysql> show databases;
    76. +--------------------+
    77. | Database           |
    78. +--------------------+
    79. | information_schema |
    80. | d1                 |
    81. | mysql              |
    82. | performance_schema |
    83. | scott              |
    84. | sys                |
    85. +--------------------+
    86. 6 rows in set (0.00 sec)
    87.  
    88. mysql> 
     

    展示样例sql数据表
     

     

    1. mysql> show tables;
    2. +-----------------+
    3. | Tables_in_scott |
    4. +-----------------+
    5. | dept            |
    6. | emp             |
    7. | salgrade        |
    8. +-----------------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> desc dept;
    12. +--------+--------------------------+------+-----+---------+-------+
    13. | Field  | Type                     | Null | Key | Default | Extra |
    14. +--------+--------------------------+------+-----+---------+-------+
    15. | deptno | int(2) unsigned zerofill | NO   |     | NULL    |       |
    16. | dname  | varchar(14)              | YES  |     | NULL    |       |
    17. | loc    | varchar(13)              | YES  |     | NULL    |       |
    18. +--------+--------------------------+------+-----+---------+-------+
    19. 3 rows in set (0.00 sec)
    20.  
    21. mysql> select* from dept;
    22. +--------+------------+----------+
    23. | deptno | dname      | loc      |
    24. +--------+------------+----------+
    25. |     10 | ACCOUNTING | NEW YORK |
    26. |     20 | RESEARCH   | DALLAS   |
    27. |     30 | SALES      | CHICAGO  |
    28. |     40 | OPERATIONS | BOSTON   |
    29. +--------+------------+----------+
    30. 4 rows in set (0.00 sec)
    31.  
    32. mysql> desc emp;
    33. +----------+--------------------------+------+-----+---------+-------+
    34. | Field    | Type                     | Null | Key | Default | Extra |
    35. +----------+--------------------------+------+-----+---------+-------+
    36. | empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
    37. | ename    | varchar(10)              | YES  |     | NULL    |       |
    38. | job      | varchar(9)               | YES  |     | NULL    |       |
    39. | mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
    40. | hiredate | datetime                 | YES  |     | NULL    |       |
    41. | sal      | decimal(7,2)             | YES  |     | NULL    |       |
    42. | comm     | decimal(7,2)             | YES  |     | NULL    |       |
    43. | deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
    44. +----------+--------------------------+------+-----+---------+-------+
    45. 8 rows in set (0.00 sec)
    46.  
    47. mysql> select*from emp;
    48. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    49. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
    50. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    51. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
    52. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
    53. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
    54. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
    55. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
    56. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
    57. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
    58. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
    59. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
    60. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
    61. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
    62. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
    63. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
    64. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
    65. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    66. 14 rows in set (0.00 sec)
    67.  
    68. mysql> desc salgrade;
    69. +-------+---------+------+-----+---------+-------+
    70. | Field | Type    | Null | Key | Default | Extra |
    71. +-------+---------+------+-----+---------+-------+
    72. | grade | int(11) | YES  |     | NULL    |       |
    73. | losal | int(11) | YES  |     | NULL    |       |
    74. | hisal | int(11) | YES  |     | NULL    |       |
    75. +-------+---------+------+-----+---------+-------+
    76. 3 rows in set (0.00 sec)
    77.  
    78. mysql> select *from salgrade;
    79. +-------+-------+-------+
    80. | grade | losal | hisal |
    81. +-------+-------+-------+
    82. |     1 |   700 |  1200 |
    83. |     2 |  1201 |  1400 |
    84. |     3 |  1401 |  2000 |
    85. |     4 |  2001 |  3000 |
    86. |     5 |  3001 |  9999 |
    87. +-------+-------+-------+
    88. 5 rows in set (0.00 sec)
    89.  
    90. mysql> 
     

    如何显示每个部门的平均工资和最高工资
     

     

    1. mysql> select max(sal) 最高,avg(sal) 平均 from emp;
    2. +---------+-------------+
    3. | 最高    | 平均        |
    4. +---------+-------------+
    5. | 5000.00 | 2073.214286 |
    6. +---------+-------------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> select max(sal) 最高,avg(sal) 平均 from emp group by deptno;
    10. +---------+-------------+
    11. | 最高    | 平均        |
    12. +---------+-------------+
    13. | 5000.00 | 2916.666667 |
    14. | 3000.00 | 2175.000000 |
    15. | 2850.00 | 1566.666667 |
    16. +---------+-------------+
    17. 3 rows in set (0.00 sec)
    18.  
    19. mysql> select deptno,max(sal) 最高,avg(sal) 平均 from emp group by deptno;
    20. +--------+---------+-------------+
    21. | deptno | 最高    | 平均        |
    22. +--------+---------+-------------+
    23. |     10 | 5000.00 | 2916.666667 |
    24. |     20 | 3000.00 | 2175.000000 |
    25. |     30 | 2850.00 | 1566.666667 |
    26. +--------+---------+-------------+
    27. 3 rows in set (0.00 sec)
    28.  
    29. mysql> 
     

    显示每个部门的每种岗位的平均工资和最低工资
     

     

    1. mysql> select *from emp;
    2. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    3. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
    4. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    5. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
    6. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
    7. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
    8. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
    9. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
    10. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
    11. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
    12. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
    13. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
    14. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
    15. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
    16. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
    17. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
    18. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
    19. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    20. 14 rows in set (0.00 sec)
    21.  
    22. mysql> select deptno,avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
    23. +--------+-------------+---------+
    24. | deptno | 平均        | 最低    |
    25. +--------+-------------+---------+
    26. |     10 | 1300.000000 | 1300.00 |
    27. |     10 | 2450.000000 | 2450.00 |
    28. |     10 | 5000.000000 | 5000.00 |
    29. |     20 | 3000.000000 | 3000.00 |
    30. |     20 |  950.000000 |  800.00 |
    31. |     20 | 2975.000000 | 2975.00 |
    32. |     30 |  950.000000 |  950.00 |
    33. |     30 | 2850.000000 | 2850.00 |
    34. |     30 | 1400.000000 | 1250.00 |
    35. +--------+-------------+---------+
    36. 9 rows in set (0.00 sec)
    37.  
    38. mysql> select deptno,job, avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
    39. +--------+-----------+-------------+---------+
    40. | deptno | job       | 平均        | 最低    |
    41. +--------+-----------+-------------+---------+
    42. |     10 | CLERK     | 1300.000000 | 1300.00 |
    43. |     10 | MANAGER   | 2450.000000 | 2450.00 |
    44. |     10 | PRESIDENT | 5000.000000 | 5000.00 |
    45. |     20 | ANALYST   | 3000.000000 | 3000.00 |
    46. |     20 | CLERK     |  950.000000 |  800.00 |
    47. |     20 | MANAGER   | 2975.000000 | 2975.00 |
    48. |     30 | CLERK     |  950.000000 |  950.00 |
    49. |     30 | MANAGER   | 2850.000000 | 2850.00 |
    50. |     30 | SALESMAN  | 1400.000000 | 1250.00 |
    51. +--------+-----------+-------------+---------+
    52. 9 rows in set (0.01 sec)
    53.  
    54. mysql> select ename,deptno,job, avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
    55. ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.emp.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    56. mysql> 
     

    显示平均工资低于2000的部门和它的平均工资
     

     

    1. mysql> select avg(sal) deptavg from emp group by deptno;
    2. +-------------+
    3. | deptavg     |
    4. +-------------+
    5. | 2916.666667 |
    6. | 2175.000000 |
    7. | 1566.666667 |
    8. +-------------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> select deptno,avg(sal) deptavg from emp group by deptno;
    12. +--------+-------------+
    13. | deptno | deptavg     |
    14. +--------+-------------+
    15. |     10 | 2916.666667 |
    16. |     20 | 2175.000000 |
    17. |     30 | 1566.666667 |
    18. +--------+-------------+
    19. 3 rows in set (0.00 sec)
    20.  
    21. mysql> select deptno,avg(sal) deptavg from emp group by deptno having deptavg<2000;
    22. +--------+-------------+
    23. | deptno | deptavg     |
    24. +--------+-------------+
    25. |     30 | 1566.666667 |
    26. +--------+-------------+
    27. 1 row in set (0.00 sec)
    28.  
    29. mysql> select deptno,avg(sal) deptavg from emp group by deptno where deptavg<2000;
    30. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where deptavg<2000' at line 1
    31. mysql> select *from emp having ename='SMITH';
    32. +--------+-------+-------+------+---------------------+--------+------+--------+
    33. | empno  | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
    34. +--------+-------+-------+------+---------------------+--------+------+--------+
    35. | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
    36. +--------+-------+-------+------+---------------------+--------+------+--------+
    37. 1 row in set (0.00 sec)
    38.  
    39. mysql> 
     

    （SMITH不参加统计）显示平均工资低于2000的部门和它的平均工资
     

     

    1. mysql> select deptno, job from emp where ename!='SMITH' group by deptno ,job;
    2. +--------+-----------+
    3. | deptno | job       |
    4. +--------+-----------+
    5. |     10 | CLERK     |
    6. |     10 | MANAGER   |
    7. |     10 | PRESIDENT |
    8. |     20 | ANALYST   |
    9. |     20 | CLERK     |
    10. |     20 | MANAGER   |
    11. |     30 | CLERK     |
    12. |     30 | MANAGER   |
    13. |     30 | SALESMAN  |
    14. +--------+-----------+
    15. 9 rows in set (0.00 sec)
    16.  
    17. mysql> select deptno, job ,max(sal) 最高,min(sal) 最低 from emp where ename!='SMITH' group by deptno ,job;
    18. +--------+-----------+---------+---------+
    19. | deptno | job       | 最高    | 最低    |
    20. +--------+-----------+---------+---------+
    21. |     10 | CLERK     | 1300.00 | 1300.00 |
    22. |     10 | MANAGER   | 2450.00 | 2450.00 |
    23. |     10 | PRESIDENT | 5000.00 | 5000.00 |
    24. |     20 | ANALYST   | 3000.00 | 3000.00 |
    25. |     20 | CLERK     | 1100.00 | 1100.00 |
    26. |     20 | MANAGER   | 2975.00 | 2975.00 |
    27. |     30 | CLERK     |  950.00 |  950.00 |
    28. |     30 | MANAGER   | 2850.00 | 2850.00 |
    29. |     30 | SALESMAN  | 1600.00 | 1250.00 |
    30. +--------+-----------+---------+---------+
    31. 9 rows in set (0.00 sec)
    32.  
    33. mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job;
    34. +--------+-----------+-------------+
    35. | deptno | job       | myavg       |
    36. +--------+-----------+-------------+
    37. |     10 | CLERK     | 1300.000000 |
    38. |     10 | MANAGER   | 2450.000000 |
    39. |     10 | PRESIDENT | 5000.000000 |
    40. |     20 | ANALYST   | 3000.000000 |
    41. |     20 | CLERK     | 1100.000000 |
    42. |     20 | MANAGER   | 2975.000000 |
    43. |     30 | CLERK     |  950.000000 |
    44. |     30 | MANAGER   | 2850.000000 |
    45. |     30 | SALESMAN  | 1400.000000 |
    46. +--------+-----------+-------------+
    47. 9 rows in set (0.00 sec)
    48.  
    49. mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job having mysal<2000;
    50. ERROR 1054 (42S22): Unknown column 'mysal' in 'having clause'
    51. mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job having myavg<2000;
    52. +--------+----------+-------------+
    53. | deptno | job      | myavg       |
    54. +--------+----------+-------------+
    55. |     10 | CLERK    | 1300.000000 |
    56. |     20 | CLERK    | 1100.000000 |
    57. |     30 | CLERK    |  950.000000 |
    58. |     30 | SALESMAN | 1400.000000 |
    59. +--------+----------+-------------+
    60. 4 rows in set (0.00 sec)
    61.  
    62. mysql> 
     

    结尾
     

    最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
     

     

    同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
     

     

    谢谢您的支持，期待与您在下一篇文章中再次相遇！
    
                
    
                    
    
                
    • 

                
  • 

                    相关阅读:
    
                    
jsp教师教学信息管理系统Myeclipse开发sqlserver数据库web结构java编程计算机网页项目                            
    
Seata四大模式之XA模式详解及代码实现                            
    
DNS域名解析                            
    
C++ —— 二叉搜索树                            
    
gprof 分析程序执行时间和函数调用次数                            
    
在线问题反馈模块实战(十五)​:实现在线更新反馈状态功能                            
    
【Spring Boot】单元测试                            
    
GBase 8s典型安装                            
    
传统机器学习笔记6——回归树模型                            
    
leetcode347 前 K 个高频元素                            
    
                        
                
    • 
                
  • 
                    原文地址：https://blog.csdn.net/m0_74163972/article/details/136349843
                
    •