5.插入查询结果
删除表中的重复记录,重复的数据只能有一份
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| for_delete |
| for_truncate |
| result |
| test |
+-------------------+
4 rows in set (0.00 sec)
mysql> create table dup_table(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
-- 插入重复数据
mysql> insert into dup_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 dup_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 100 | aaa |
| 200 | bbb |
| 200 | bbb |
| 200 | bbb |
| 300 | ccc |
+------+------+
6 rows in set (0.00 sec)
-- 查询去重,这一步只会使展现出来的结果是去重的,不会影响表中的数据
mysql> select distinct * from dup_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
-- 创建另一个表,结构与原表相同,可以用like,存储无重复的数据
mysql> create table no_dup_table like dup_table;
Query OK, 0 rows affected (0.01 sec)
mysql> desc dup_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 no_dup_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> insert into no_dup_table select distinct * from dup_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select* from no_dup_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
-- 通过重命名表,实现原子的去重操作
mysql> rename table dup_table to old_dup_table;
Query OK, 0 rows affected (0.01 sec)
mysql> rename table no_dup_table to dup_table;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| dup_table |
| for_delete |
| for_truncate |
| old_dup_table |
| result |
| test |
+-------------------+
6 rows in set (0.00 sec)
mysql> select * from dup_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
6.聚合函数
函数 | 说明 |
COUNT([DISTINCT] expr) | 返回查询到的数据的数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的最小值,不是数字没有意义 |
mysql> select* from 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 result;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from result;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select math from result;
+------+
| math |
+------+
| 98 |
| 98 |
| 90 |
| 73 |
| 95 |
+------+
5 rows in set (0.00 sec)
-- 可以小写
mysql> select count(math) from result;
+-------------+
| count(math) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
-- 也可以大写
mysql> select COUNT(math) from result;
+-------------+
| COUNT(math) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
-- 也可以重命名结果
mysql> select count(math) as res from result;
+-----+
| res |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
-- 去重操作,但是这种写法是错误的,因为执行顺序
-- 查询完然后将count结果进行了去重,无意义
-- 应该是去重完然后count
mysql> select distinct count(math) as res from result;
+-----+
| res |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
-- 先去重然后count
mysql> select count(distinct math) as res from result;
+-----+
| res |
+-----+
| 4 |
+-----+
1 row in set (0.00 sec)
mysql> select sum(math) from result;
+-----------+
| sum(math) |
+-----------+
| 454 |
+-----------+
1 row in set (0.00 sec)
-- 求数学的平均成绩
mysql> select sum(math)/count(*) from result;
+--------------------+
| sum(math)/count(*) |
+--------------------+
| 90.8 |
+--------------------+
1 row in set (0.00 sec)
-- 求英语的平均成绩
mysql> select sum(english)/count(*) from result;
+-----------------------+
| sum(english)/count(*) |
+-----------------------+
| 64.2 |
+-----------------------+
1 row in set (0.00 sec)
-- 可以加where限定条件
mysql> select count(*) from result where math<60;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from result where english<60;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select * from result where english<60;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
-- 英语成绩小于60的成绩总分
mysql> select sum(english) from result where english<60;
+--------------+
| sum(english) |
+--------------+
| 86 |
+--------------+
1 row in set (0.00 sec)
-- 英语成绩小于60的成绩均分
mysql> select sum(english)/count(*) from result where english<60;
+-----------------------+
| sum(english)/count(*) |
+-----------------------+
| 43 |
+-----------------------+
1 row in set (0.00 sec)
-- 可以直接用聚合操作的函数直接求平均值
mysql> select avg(math) from result;
+-----------+
| avg(math) |
+-----------+
| 90.8 |
+-----------+
1 row in set (0.00 sec)
-- 加上重命名
mysql> select avg(english+chinese+math) 平均分 from result;
+-----------+
| 平均分 |
+-----------+
| 303 |
+-----------+
1 row in set (0.00 sec)
mysql> select name,english+chinese+math from result;
+-----------+----------------------+
| name | english+chinese+math |
+-----------+----------------------+
| 唐三藏 | 288 |
| 猪悟能 | 364 |
| 曹孟德 | 297 |
| 孙权 | 291 |
| 宋公明 | 275 |
+-----------+----------------------+
5 rows in set (0.00 sec)
-- max函数
mysql> select max(english) from result;
+--------------+
| max(english) |
+--------------+
| 90 |
+--------------+
1 row in set (0.00 sec)
-- 大于80分的人的最低分,min函数
mysql> select min(math) from result where math>80;
+-----------+
| min(math) |
+-----------+
| 90 |
+-----------+
1 row in set (0.00 sec)
7.group by子句的使用
语法:select column1, column2, .. from table group by column;
案例:雇员信息表(oracle 9i的经典测试表)
- EMP员工表
- DEPT部门表
- SALGRADE工资等级表
导入操作:source /home/jby/scott_data.sql;
(在mysql中操作)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bitmall |
| mysql |
| performance_schema |
| scott |
| sys |
| test_db |
| user_db |
+--------------------+
8 rows in set (0.00 sec)
mysql> use scott
Database changed
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| salgrade |
+-----------------+
3 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 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 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> select avg(sal) avg,max(sal) max from emp;
+-------------+---------+
| avg | max |
+-------------+---------+
| 2073.214286 | 5000.00 |
+-------------+---------+
1 row in set (0.00 sec)
-- 每个部门分组的平均工资和最高工资
mysql> select avg(sal) avg,max(sal) max from emp group by deptno;
+-------------+---------+
| avg | max |
+-------------+---------+
| 2916.666667 | 5000.00 |
| 2175.000000 | 3000.00 |
| 1566.666667 | 2850.00 |
+-------------+---------+
3 rows in set (0.00 sec)
-- 加上deptno
mysql> select deptno,avg(sal) avg,max(sal) max from emp group by deptno;
+--------+-------------+---------+
| deptno | avg | max |
+--------+-------------+---------+
| 10 | 2916.666667 | 5000.00 |
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
+--------+-------------+---------+
3 rows in set (0.00 sec)
-- 每个部门每种岗位的平均工资和最低工资
-- 先deptno再job分组
mysql> select avg(sal) avg,min(sal) min,job,deptno from emp group by deptno,job;
+-------------+---------+-----------+--------+
| avg | min | job | deptno |
+-------------+---------+-----------+--------+
| 1300.000000 | 1300.00 | CLERK | 10 |
| 2450.000000 | 2450.00 | MANAGER | 10 |
| 5000.000000 | 5000.00 | PRESIDENT | 10 |
| 3000.000000 | 3000.00 | ANALYST | 20 |
| 950.000000 | 800.00 | CLERK | 20 |
| 2975.000000 | 2975.00 | MANAGER | 20 |
| 950.000000 | 950.00 | CLERK | 30 |
| 2850.000000 | 2850.00 | MANAGER | 30 |
| 1400.000000 | 1250.00 | SALESMAN | 30 |
+-------------+---------+-----------+--------+
9 rows in set (0.00 sec)
-- 先job再deptno分组
mysql> select avg(sal) avg,min(sal) min,job,deptno from emp group by job,deptno;
+-------------+---------+-----------+--------+
| avg | min | job | deptno |
+-------------+---------+-----------+--------+
| 3000.000000 | 3000.00 | ANALYST | 20 |
| 1300.000000 | 1300.00 | CLERK | 10 |
| 950.000000 | 800.00 | CLERK | 20 |
| 950.000000 | 950.00 | CLERK | 30 |
| 2450.000000 | 2450.00 | MANAGER | 10 |
| 2975.000000 | 2975.00 | MANAGER | 20 |
| 2850.000000 | 2850.00 | MANAGER | 30 |
| 5000.000000 | 5000.00 | PRESIDENT | 10 |
| 1400.000000 | 1250.00 | SALESMAN | 30 |
+-------------+---------+-----------+--------+
9 rows in set (0.00 sec)
-- 一般在分组group by的聚合条件中出现的列才能在select后出现
-- 加了一个ename,但是没有在group by中出现,就会报错
mysql> select ename,avg(sal) avg,min(sal) min,job,deptno from emp group by job,deptno;
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
-- 显示平均工资低于2000的部门和它的平均工资
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)
-- having是对聚合后的统计数据进行条件筛选
-- having是最后执行的,先执行group by分组,然后执行having筛选
where和having的区别理解,执行顺序,构建对结果的影响?
- 条件筛选的阶段是不同的
- 不能单纯的认为,只有磁盘上表结构导入到MySQL,真实存在的表,才叫做表,中间筛选出来的,包括最终结果,全都是逻辑上的表(“MySQL一切皆表”)
-- SMITH员工不参与统计,显示出平均工资低于2000的部门和它的平均工资
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)
下面这张图标注了语句的执行顺序: