【MySQL学习】MySQL表的增删改查操作2：https://developer.aliyun.com/article/1384029
五、插入查询结果
语法：
INSERT INTO table_name [(column [, column …])] SELECT …
案例：删除表中的的重复复记录，重复的数据只能有一份
-- 创建原数据表 mysql> CREATE TABLE duplicate_table (id int, name varchar(20)); Query OK, 0 rows affected (0.01 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.00 sec) Records: 6 Duplicates: 0 Warnings: 0
-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样 mysql> CREATE TABLE no_duplicate_table LIKE duplicate_table; Query OK, 0 rows affected (0.01 sec) -- 将 duplicate_table 的去重数据插入到 no_duplicate_table 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> RENAME TABLE duplicate_table TO old_duplicate_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)
六、聚合函数
6.1 聚合函数分类
6.2 案例
- 统计班级共有多少同学
-- 使用 * 做统计，不受 NULL 影响 mysql> select count(*) from students; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) -- 使用表达式做统计 mysql> select count(1) from students; +----------+ | count(1) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
- 统计班级收集的 qq 号有多少
-- NULL 不会计入结果 mysql> select count(qq) from students; +-----------+ | count(qq) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
- 统计本次考试的数学成绩分数个数
-- COUNT(math) 统计的是全部成绩 mysql> select count(math) from exam_result; +-------------+ | count(math) | +-------------+ | 6 | +-------------+ 1 row in set (0.00 sec) -- COUNT(DISTINCT math) 统计的是去重成绩数量 mysql> select count(distinct math) from exam_result; +----------------------+ | count(distinct math) | +----------------------+ | 5 | +----------------------+ 1 row in set (0.00 sec)
- 统计数学成绩总分
mysql> select sum(math) from exam_result; +-----------+ | sum(math) | +-----------+ | 569 | +-----------+ 1 row in set (0.00 sec) -- 不及格 < 60 的总分，没有结果，返回 NULL mysql> select sum(math) from exam_result where math < 60; +-----------+ | sum(math) | +-----------+ | NULL | +-----------+ 1 row in set (0.00 sec)
- 统计平均总分
mysql> select avg(chinese + math + english) 平均总分 from exam_result; +--------------+ | 平均总分 | +--------------+ | 297.5 | +--------------+ 1 row in set (0.00 sec)
- 返回英语最高分
mysql> select max(english) from exam_result; +--------------+ | max(english) | +--------------+ | 90 | +--------------+ 1 row in set (0.00 sec)
- 返回 > 70 分以上的数学最低分
mysql> select min(math) from exam_result where math > 70; +-----------+ | min(math) | +-----------+ | 73 | +-----------+ 1 row in set (0.00 sec)
七、GROUP BY 子句的使用
在select中使用group by 子句可以对指定列进行分组查询。
语法：
select column1, column2, … from table group by column;
案例：
- 准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
- EMP员工表
- DEPT部门表
- SALGRADE工资等级表
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 '此等级最高工资' );
- 如何显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from EMP group by deptno;
- 显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job, deptno from EMP group by deptno, job;
- 显示平均工资低于2000的部门和它的平均工资
- 统计各个部门的平均工资
select avg(sal) from EMP group by deptno;
- having和group by配合使用，对group by结果进行过滤
select avg(sal) as myavg from EMP group by deptno having myavg<2000; --having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。
