MySQL – 表的增删查改

---

表的增删查改一共有四种操作：Create(创建), Retrieve(读取), Update(更新), Delete(删除)

一、Create创建

语法：

1.插入数据

先创建一个表：

• 全列插入：
  如果不显式指定插入哪些字段的数据，就默认全列插入，需要输入全部字段的数据；
  
  
• 指定列插入
  可以显式指定插入哪些列数据：
  
• 多行数据插入：
  

2.插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败，可以算则行的进行同步更新操作：

insert into 表名 () values ()on duplicate keyupdate column = value;

主键冲突，则执行更新语句；

3.替换

replace into 表名 () values ();

• 主键或者唯一键没有冲突，则直接插入；
• 主键或者唯一键如果冲突，则删除后再插入；

二、Retrieve查找

创建成绩单表格：

插入数据：

1.select列

1.1.全列查询

select * from 表名;

1.2.指定列查询

select 列名1, 列名2,... from 表名; 

指定列的顺序不需要定义表格的顺序来；

1.3.查询字段为表达式

select 列名1（表达式）, 列名2（表达式）,... from 表名; 

1.4.为查询结果指定别名

select 列名1（表达式）[as] 别名, 列名2（表达式）[as] 别名,... from 表名; 

1.5.结果去重

select distinct 表达式1, 表达式2,... from 表名; 

未去重：

去重：

2.where条件

select distinct 表达式1, 表达式2,... from 表名 where 条件表达式; 

比较运算符：

• 注：MySQL中的等于是用单等号表示；

逻辑运算符：

2.1.英语不及格的同学（英语<60）

2.2.语文成绩在 [80, 90] 分的同学及语文成绩

使用and进行连接：

使用between…and…条件：

2.3.数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

使用or进行连接：

使用in条件：

2.4.姓孙的同学 及 孙某同学

使用like进行模糊匹配；
% 匹配任意多个（包括 0 个）任意字符

– _ 匹配严格的一个任意字符

2.5.语文成绩好于英语成绩的同学

2.4.总分在 200 分以下的同学

where条件中不能使用别名

2.5.语文成绩 > 80 并且不姓孙的同学

not的使用：

2.6.孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

综合性查询，使用括号分开各条件：

2.7.NULL 的查询

• null不可以使用=查询；
• null可以使用is null、is not null或者<=>来查询；
• 空串与null的区别还有：空串可以使用=查询，而null不行；
  

3.结果排序

• asc为升序（默认）；
• desc为降序；

select 表达式 from 表名 where 条件表达式 order by 表达式[asc|desc];

3.1.同学及数学成绩，按数学成绩升序显示

3.2.同学及 qq 号，按 qq 号排序显示

• null视为比任何值都小，因此在最上面；

3.3.查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

多字段排序，排序优先级随书写顺序

3.4.查询同学及总分，由高到低

• ORDER BY 中可以使用表达式；
  

• ORDER BY 中可以使用别名；
  

3.5.查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

结合 WHERE 子句 和 ORDER BY 子句

4.筛选分页结果

语法：

• 从 0 开始，筛选 n 条结果：

select 表达式 from 表名 [where...] [order by ...] limit n;

• 从s开始，筛选 n 条结果：

select 表达式 from 表名 [where...] [order by ...] limit s, n;

• 从s开始，筛选 n 条结果：

select 表达式 from 表名 [where...] [order by ...] limit n offset s;

• 建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死;

三、Update更新

update 表名 set 列名 = 值, ... [where ...][order by ...][limit ...];

• 对查询到的结果进行更新，如果没有查询条件，会对该列所有数据更新；

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

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

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

• 不支持math += 30这种表达式；
  
  

4.将所有同学的语文成绩都加10分

• 更新全表的语句慎用；

四、Delete删除

delete from 表名 [where ...][order by ...][limit ...];

MySQL三大日志包括：undolog, redo log，binlog，它们分别有以下作用：
undolog：是Innodb存储引擎生成的日志，用于事务的回滚和MVCC，保证了事务的原子性；
redo log：是Innodb存储引擎生成的日志；用于崩溃后修复数据，保证了事务的持久性；
binlog：是Server层生成的白志，用于备份数据，集群等；

1.删除数据

1.1.删除孙悟空的成绩

2.2.删除整张表的数据

再插入一条数据，发现自增量是从4开始的，从之前的数据延续：

查看表结构，会有AUTO_INCREMENT=n 项：

2.截断表

truncate [table] 表名; 

• 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
• 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚；
• 会重置 AUTO_INCREMENT 项；

截断表操作：

再插入一条数据，查看自增量，发现AUTO_INCREMENT 项已经被重置 ；

五、插入查询结果（数据备份）

insert into 表名 [列名, ...] select [列名, ...] from 表名 [where ...][order ...][limit ...];

删除表中的重复记录，重复的记录只能有一份：

• 创建原数据表：
  
• 插入测试数据：
  
• 创建一张空表（结构与原表一致）：
  
• 将原表的去重数据插入到新表中：
  
• 通过重命名表，实现原子的去重操作：
  
• 查看最终结果：
  

六、聚合函数

聚合函数需要在所有的数据都筛选完成后，再做统计；

1.统计班级共有多少同学

• 使用 * 做统计，不受 NULL 影响
  
• 用表达式做统计
  

2.统计班级收集的qq有多少

• NULL 不会计入结果
  

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

• COUNT(math) 统计的是全部成绩
  
• COUNT(DISTINCT math)统计的是去重成绩
  

4.统计数学成绩总分

• 不及格 < 60 的总分，没有结果，返回 NULL
  

5.统计平均总分

6.返回英语最高分

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

七.group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询；

select column1, column2, .. from table group by column;

1.准备数据

准备oracle 9i的经典测试表

• EMP员工表：
  
• DEPT部门表：
  
• SALGRADE工资等级表：
  

2.显示每个部门的平均工资和最高工资

使用group by语句按照部门进行分组统计：

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

• 先按照部门分组，再在部门中按照岗位分组；
• group by后面的列是分组依据，只有分组依据才能在前面显示出来，其他的列一般不可以；

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

• 统计各个部门的平均工资：
  

• 使用where语句进行条件筛选：
  
  where和聚合函数的执行顺序是有冲突的；
  因为聚合函数是要在有数据之后才能统计，而此时where中要用到聚合统计的结果来做筛选就已经冲突了；

• having和group by配合使用，对group by结果进行过滤：
  
  having是对聚合统计之后的数据进行筛选；

5.总结

1. group by是通过分组这样的手段，为未来进行聚合统计提供基本的功能支持(group by一定是配合聚合统计使用的)；
2. group by后面跟的都是分组的字段依据，只有在group by后面出现的字段，未来在聚合统计的时候，在select中才能出现；
3. where VS having：他们两个不是冲突的，是互相补充的；
   having通常是在完成整个分组聚合统计，然后再进行筛选；
   where通常是在表中数据初步被筛选的时候，要起效果的；

八、MySQL语句执行顺序

1.查询语句的顺序

1. from…：从表中取数据；
2. where …：遍历数据，找出满足条件的数据行；
3. select …：从满足条件的数据中选择用户指定的列或表达式显示；
4. 在拿到了用户需要的数据之后，再进行排序，调整数据顺序；

• where语句中不能使用别名的原因：where语句在别名定义之前就已经执行了，因此并不知道别名；
• order排序中可以使用别名：因为order语句在别名定义之后才执行；

2.group by语句的顺序

1. from…：从表中取数据；
2. group by：根据…分组；
3. select：对分组之后的数据进行聚合统计；
4. having：对聚合统计之后的结果进行筛选；

1. from…：从表中取数据；
2. where：对表中的数据进行初步筛选；
3. group by：对初筛后的结果根据…分组；
4. select：对分组之后的数据进行聚合统计；
5. having：对聚合统计之后的结果进行进一步的筛选；