去重表数据
表的准备工作
去除表中重复的数据，重复的数据只留一份。
mysql> create table duplicate_table (
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into duplicate_table values
-> (100,'aaa'),
-> (100,'aaa'),
-> (200,'bbb'),
-> (200,'bbb'),
-> (200,'bbb'),
-> (300,'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select *from duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 100 | aaa |
| 200 | bbb |
| 200 | bbb |
| 200 | bbb |
| 300 | ccc |
+------+------+
6 rows in set (0.00 sec)
mysql>
去除表中重复的数据，重复的数据只留一份
mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.03 sec)
mysql> desc no_duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select distinct *from no_duplicate_table ;
Empty set (0.00 sec)
mysql> select distinct *from duplicate_table ;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
mysql> insert into no_duplicate_table select distinct *from duplicate_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from no_duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
mysql> rename table duplicate_table to old_duplicate_table ;
Query OK, 0 rows affected (0.04 sec)
mysql> rename table no_duplicate_table to duplicate_table ;
Query OK, 0 rows affected (0.01 sec)
mysql> select *from duplicate_table ;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
mysql>
聚合统计
| 函数 | 说明 |
| COUNT（DISTINCT） | 返回查询到的数据的 数量 |
| SUM（DISTINCT） | 返回查询到的数据的 总和，不是数字没有意义 |
| AVG（DISTINCT） | 返回查询到的数据的 平均值，不是数字没有意义 |
| MAX（DISTINCT） | 返回查询到的数据的 最大值，不是数字没有意义 |
| MIN（DISTINCT） | 返回查询到的数据的 最小值，不是数字没有意义 |
count简单使用
mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) 总数 from exam_result;
+--------+
| 总数 |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
mysql> select count(2) 总数 from exam_result;
+--------+
| 总数 |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
mysql> select math from exam_result;
+------+
| math |
+------+
| 98 |
| 98 |
| 90 |
| 73 |
| 95 |
+------+
5 rows in set (0.00 sec)
mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(math) as res from exam_result;
+-----+
| res |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
mysql>
统计本次考试的数学成绩分数个数
mysql> select math from exam_result;
+------+
| math |
+------+
| 98 |
| 98 |
| 90 |
| 73 |
| 95 |
+------+
5 rows in set (0.00 sec)
mysql> select distinct count(math) as res from exam_result;
+-----+
| res |
+-----+
| 5 |
+-----+
1 row in set (0.01 sec)
mysql> select count(distinct math) as res from exam_result;
+-----+
| res |
+-----+
| 4 |
+-----+
1 row in set (0.00 sec)
mysql>
数学英语平均分，不及格个数，不及格总分，不及格平均分
mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
| 454 |
+-----------+
1 row in set (0.00 sec)
mysql> select sum(math)/count(*) from exam_result;
+--------------------+
| sum(math)/count(*) |
+--------------------+
| 90.8 |
+--------------------+
1 row in set (0.00 sec)
mysql> select sum(english)/count(*) from exam_result;
+-----------------------+
| sum(english)/count(*) |
+-----------------------+
| 64.2 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select count(*) from exam_result where math<60;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from exam_result where english<60;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select *from exam_result where english<60;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
mysql> select sum(english) from exam_result where english<60;
+--------------+
| sum(english) |
+--------------+
| 86 |
+--------------+
1 row in set (0.00 sec)
mysql> select sum(english)/count(english) from exam_result where english<60;
+-----------------------------+
| sum(english)/count(english) |
+-----------------------------+
| 43 |
+-----------------------------+
1 row in set (0.00 sec)
mysql>
统计平均总分
mysql> select sum(math)/count(*) from exam_result;
+--------------------+
| sum(math)/count(*) |
+--------------------+
| 90.8 |
+--------------------+
1 row in set (0.00 sec)
mysql> select avg(math) from exam_result;
+-----------+
| avg(math) |
+-----------+
| 90.8 |
+-----------+
1 row in set (0.00 sec)
mysql> select avg(math+chinese+english) from exam_result;
+---------------------------+
| avg(math+chinese+english) |
+---------------------------+
| 303 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select name,math+english+chinese from exam_result;
+-----------+----------------------+
| name | math+english+chinese |
+-----------+----------------------+
| 唐三藏 | 288 |
| 猪悟能 | 364 |
| 曹孟德 | 297 |
| 孙权 | 291 |
| 宋公明 | 275 |
+-----------+----------------------+
5 rows in set (0.00 sec)
mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
| 90 |
+--------------+
1 row in set (0.00 sec)
mysql> select name ,max(english) from exam_result;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list containode=only_full_group_by
mysql>
返回>70分以上的数学最低分
mysql> select math from exam_result;
+------+
| math |
+------+
| 98 |
| 98 |
| 90 |
| 73 |
| 95 |
+------+
5 rows in set (0.00 sec)
mysql> select math from exam_result where math>70;
+------+
| math |
+------+
| 98 |
| 98 |
| 90 |
| 73 |
| 95 |
+------+
5 rows in set (0.00 sec)
mysql> select min(math) from exam_result where math>70;
+-----------+
| min(math) |
+-----------+
| 73 |
+-----------+
1 row in set (0.00 sec)
mysql>
分组聚合查询
导入数据库样例sql文件
scott_data.sql（文件名）（下面是文件内容）
DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `scott`;
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL COMMENT '等级',
`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
rz选择scott_data.sql文件导入数据
[root@VM-8-12-centos d1]# cd /var/lib/mysql
[root@VM-8-12-centos mysql]# ls
auto.cnf d1 ibtmp1 private_key.pem
ca-key.pem ib_buffer_pool mysql public_key.pem
ca.pem ibdata1 mysql.sock server-cert.pem
client-cert.pem ib_logfile0 mysql.sock.lock server-key.pem
client-key.pem ib_logfile1 performance_schema sys
[root@VM-8-12-centos mysql]# rz
[root@VM-8-12-centos mysql]# ls
auto.cnf d1 ibtmp1 private_key.pem sys
ca-key.pem ib_buffer_pool mysql public_key.pem
ca.pem ibdata1 mysql.sock scott_data.sql
client-cert.pem ib_logfile0 mysql.sock.lock server-cert.pem
client-key.pem ib_logfile1 performance_schema server-key.pem
mysql> source /var/lib/mysql/scott_data.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| salgrade |
+-----------------+
3 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| d1 |
| mysql |
| performance_schema |
| scott |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql>
展示样例sql数据表
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| salgrade |
+-----------------+
3 rows in set (0.00 sec)
mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO | | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select* from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno | int(6) unsigned zerofill | NO | | NULL | |
| ename | varchar(10) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int(4) unsigned zerofill | YES | | NULL | |
| hiredate | datetime | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| comm | decimal(7,2) | YES | | NULL | |
| deptno | int(2) unsigned zerofill | YES | | NULL | |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> select*from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES | | NULL | |
| losal | int(11) | YES | | NULL | |
| hisal | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select *from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql>
如何显示每个部门的平均工资和最高工资
mysql> select max(sal) 最高,avg(sal) 平均 from emp;
+---------+-------------+
| 最高 | 平均 |
+---------+-------------+
| 5000.00 | 2073.214286 |
+---------+-------------+
1 row in set (0.00 sec)
mysql> select max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+---------+-------------+
| 最高 | 平均 |
+---------+-------------+
| 5000.00 | 2916.666667 |
| 3000.00 | 2175.000000 |
| 2850.00 | 1566.666667 |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> select deptno,max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+--------+---------+-------------+
| deptno | 最高 | 平均 |
+--------+---------+-------------+
| 10 | 5000.00 | 2916.666667 |
| 20 | 3000.00 | 2175.000000 |
| 30 | 2850.00 | 1566.666667 |
+--------+---------+-------------+
3 rows in set (0.00 sec)
mysql>
显示每个部门的每种岗位的平均工资和最低工资
mysql> select *from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select deptno,avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
+--------+-------------+---------+
| deptno | 平均 | 最低 |
+--------+-------------+---------+
| 10 | 1300.000000 | 1300.00 |
| 10 | 2450.000000 | 2450.00 |
| 10 | 5000.000000 | 5000.00 |
| 20 | 3000.000000 | 3000.00 |
| 20 | 950.000000 | 800.00 |
| 20 | 2975.000000 | 2975.00 |
| 30 | 950.000000 | 950.00 |
| 30 | 2850.000000 | 2850.00 |
| 30 | 1400.000000 | 1250.00 |
+--------+-------------+---------+
9 rows in set (0.00 sec)
mysql> select deptno,job, avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
+--------+-----------+-------------+---------+
| deptno | job | 平均 | 最低 |
+--------+-----------+-------------+---------+
| 10 | CLERK | 1300.000000 | 1300.00 |
| 10 | MANAGER | 2450.000000 | 2450.00 |
| 10 | PRESIDENT | 5000.000000 | 5000.00 |
| 20 | ANALYST | 3000.000000 | 3000.00 |
| 20 | CLERK | 950.000000 | 800.00 |
| 20 | MANAGER | 2975.000000 | 2975.00 |
| 30 | CLERK | 950.000000 | 950.00 |
| 30 | MANAGER | 2850.000000 | 2850.00 |
| 30 | SALESMAN | 1400.000000 | 1250.00 |
+--------+-----------+-------------+---------+
9 rows in set (0.01 sec)
mysql> select ename,deptno,job, avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
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
mysql>
显示平均工资低于2000的部门和它的平均工资
mysql> select avg(sal) deptavg from emp group by deptno;
+-------------+
| deptavg |
+-------------+
| 2916.666667 |
| 2175.000000 |
| 1566.666667 |
+-------------+
3 rows in set (0.00 sec)
mysql> select deptno,avg(sal) deptavg from emp group by deptno;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select deptno,avg(sal) deptavg from emp group by deptno having deptavg<2000;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
mysql> select deptno,avg(sal) deptavg from emp group by deptno where deptavg<2000;
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
mysql> select *from emp having ename='SMITH';
+--------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-------+------+---------------------+--------+------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
+--------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)
mysql>
（SMITH不参加统计）显示平均工资低于2000的部门和它的平均工资
mysql> select deptno, job from emp where ename!='SMITH' group by deptno ,job;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 10 | CLERK |
| 10 | MANAGER |
| 10 | PRESIDENT |
| 20 | ANALYST |
| 20 | CLERK |
| 20 | MANAGER |
| 30 | CLERK |
| 30 | MANAGER |
| 30 | SALESMAN |
+--------+-----------+
9 rows in set (0.00 sec)
mysql> select deptno, job ,max(sal) 最高,min(sal) 最低 from emp where ename!='SMITH' group by deptno ,job;
+--------+-----------+---------+---------+
| deptno | job | 最高 | 最低 |
+--------+-----------+---------+---------+
| 10 | CLERK | 1300.00 | 1300.00 |
| 10 | MANAGER | 2450.00 | 2450.00 |
| 10 | PRESIDENT | 5000.00 | 5000.00 |
| 20 | ANALYST | 3000.00 | 3000.00 |
| 20 | CLERK | 1100.00 | 1100.00 |
| 20 | MANAGER | 2975.00 | 2975.00 |
| 30 | CLERK | 950.00 | 950.00 |
| 30 | MANAGER | 2850.00 | 2850.00 |
| 30 | SALESMAN | 1600.00 | 1250.00 |
+--------+-----------+---------+---------+
9 rows in set (0.00 sec)
mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job;
+--------+-----------+-------------+
| deptno | job | myavg |
+--------+-----------+-------------+
| 10 | CLERK | 1300.000000 |
| 10 | MANAGER | 2450.000000 |
| 10 | PRESIDENT | 5000.000000 |
| 20 | ANALYST | 3000.000000 |
| 20 | CLERK | 1100.000000 |
| 20 | MANAGER | 2975.000000 |
| 30 | CLERK | 950.000000 |
| 30 | MANAGER | 2850.000000 |
| 30 | SALESMAN | 1400.000000 |
+--------+-----------+-------------+
9 rows in set (0.00 sec)
mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job having mysal<2000;
ERROR 1054 (42S22): Unknown column 'mysal' in 'having clause'
mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job having myavg<2000;
+--------+----------+-------------+
| deptno | job | myavg |
+--------+----------+-------------+
| 10 | CLERK | 1300.000000 |
| 20 | CLERK | 1100.000000 |
| 30 | CLERK | 950.000000 |
| 30 | SALESMAN | 1400.000000 |
+--------+----------+-------------+
4 rows in set (0.00 sec)
mysql>
结尾
最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
谢谢您的支持，期待与您在下一篇文章中再次相遇！
