MySQL表的增删查改(下)(7)

前言

  来喽，继续加油！！

一、更新

语法：

UPDATE table_name SET column = expr [, column = expr ...]
    [WHERE ...] [ORDER BY ...] [LIMIT ...]

• set 后面跟的是要重新设定的值，可以是多列。
• 一般在 update 的时候必须采用对应 where子句 进行条件筛选，如果没有的话会把这个表中指定的列全部都更新，这是不合理的。

对查询到的结果进行列值更新：

1. 将孙悟空同学的数学成绩变更为 80 分

update exam_result set math=80 where name='孙悟空';

2. 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam_result set math=60, chinese=70 where name='曹孟德';

3. 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

  不支持 math += 30 这种语法。

update exam_result set math=math+30 order by chinese+math+english limit 3;

4. 将所有同学的语文成绩更新为原来的 2 倍

update exam_result set chinese = chinese * 2;

二、删除

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

• 一般都是拿着条件删除 where
• 不加条件就是把整表的内容删除了，不过表结构还在。
• 删表结构drop

  比如说我们现在要删除 孙悟空 同学的成绩

delete from exam_result where name='孙悟空';

-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
 
 
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

  好的，我们现在删除这个表结构，但是查看创建表结构这个语句还在，并且自增长也还在。

三、插入查询结果

  现在我们来个复杂的，把我们前面学得稍微小小综合一下

INSERT INTO table_name [(column [, column ...])] SELECT ...

  我们要插就插，要删就删，要改就改，要查就查，**实际我们也可以将 select 和 insert 组合。**可以把数据从其他表里面筛选出来，然后插入到另一个表里面。

  现在我们来玩个小游戏，就是删除表中的重复记录，重复的数据只能有一份，你会怎么做？

我给出的步骤如下：

• 创建一个和原表一样结构的空表 no_duplicate_table ，
• 从原始表中把去重之后的结果筛选出来插入到 no_duplicate_table 这个表不就是不重复的吗，
• 然后对 duplicate_table 重命名，no_duplicate_table 改名字为 duplicate_table 。最终不就是完成了duplicate_table去重了吗。

create table no_duplicate_table like duplicate_table;

• 创建一个完全一样的表，使用like即可
• 全列插入就不用指定列了

insert into no_duplicate_table select distinct * from duplicate_table;

• 现在有两个表，一个duplicate_table，一个no_duplicate_table。然后对这两个表做一下重命名

rename table duplicate_table to old_duplicata_table;
rename table no_duplicate_table to duplicate_table;

  这样我们就完成了去重！

四、聚合函数

• 这里是为 select分组 查询做准备的，聚合函数是以查出来的记录为单位帮我们进行数据聚合统计的。这种聚合统计方式通常是产出一个期望的结果，如个数、和、平均值、最大值、最小值。
• mysql 中其实也是有函数的，这个函数可以被直接调用，我们可以在 mysql 直接使用聚合函数直接对一组结果进行聚合统计。
• 聚合函数（）里面可以是全列，可以是指定列。

  统计班级共有多少同学

select count(*) from exam_result;

  统计班级去重后数学成绩有多少

select count(distinct math) from exam_result;

  统计数学成绩总分

select sum(math) from exam_result;

五、分组查询

  分组查询的SQL如下：

SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];

• SQL中大写的表示关键字， [ ] 中代表的是可选项。
• 查询SQL中各语句的执行顺序为：where、group by、select、order by、limit。
• group by后面的列名，表示按照指定列进行分组查询。

我们来个示例讲解吧！

  分组查询测试表 —— 雇员信息表

  雇员信息表中包含三张表，分别是员工表（emp）、部门表（dept）和工资等级表（salgrade）。

员工表（emp）中包含如下字段：

• 雇员编号（empno）。
• 雇员姓名（ename）。
• 雇员职位（job）。
• 雇员领导编号（mgr）。
• 雇佣时间（hiredate）。
• 工资月薪（sal）。
• 奖金（comm）。
• 部门编号（deptno）。

部门表（dept）中包含如下字段：

• 部门编号（deptno）。
• 部门名称（dname）。
• 部门所在地点（loc）。

工资等级表（salgrade）中包含如下字段：

• 等级（grade）。
• 此等级最低工资（losal）。
• 此等级最高工资（hisal）。

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

  上述SQL中创建了一个名为 scott 的数据库，在该数据库中分别创建了部门表（dept）、员工表（emp）和工资等级表（salgrade），并分别向三张表中插入了一些数据用于查询。

  现在我们进入 scott 这个数据库，可以看到雇员信息表中的三张表：

  其中部门表（dept）的表结构和表中的内容如下：

  员工表（emp）的表结构和表中的内容如下：

  工资等级表（salgrade）的表结构和表中的内容如下：

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

  在 group by子句 中指明依次按照部门号和岗位进行分组，在 select语句 中使用 avg函数 和 min函数 ，分别查询每个部门的每种岗位的平均工资和最低工资。如下：

  说明一下： 上述SQL会先将表中的数据按照部门号进行分组，然后各自在组内做聚合查询得到每个组的平均工资和最高工资。

HAVING条件

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];

它与 where 的区别在于：

• where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
• where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
• where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。

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

总结

  增删查改结束！！接下来迎来的是MySQL的内置函数！！