MySQL --- 表的增删查改

一、增

insert into 表名 (列名) values (数据);

1、全列插入/指定列插入

注意：指定列插入时，数据要和指定列的顺序对应，全列插入时数据要和创建表字段时的先后顺序一致

2、多行插入

数据之间用逗号隔开即可

3、插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败

insert into 表名 (列名) values (数据) on duplicate key update 列名 = 数据;

• 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
• 1 row affected: 表中没有冲突数据，数据被插入
• 2 row affected: 表中有冲突数据，并且数据已经被更新

4、替换

replace into 表名 (列名) values (数据);

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

二、查

select (列名) from 表名 [where (条件)] [order by 列明 [asc | desc] ];

1、按列查询

2、where条件查询

>,>=,<.<=	大于，大于等于，小于，小于等于
=	等于，null不安全，如 null = null 的结果为 null
<=>	等于，null安全，如 null <=> null 的结果为 true(1)
!=,<>	不等于
between a and b	范围匹配，[a，b]，如果 a <= value <= b，返回 true(1)
in (option,...)	如果是option中的任何一个，返回 true(1)
is null	是null
is not null	不是null
like	模糊匹配，%表示任意多个(包括0个)任意字符，_表示任意一个字符
and	多个条件必须都为 true(1)，结果才为true(1)
or	任意一个条件为true(1)，结果为true(1)
not	条件为true(1)，结果为false(0)

下面演示一下没咋见过的运算符

3、结果排序

• 注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
• 先筛选出表数据，然后才开始排序 （查询语句的执行顺序）

4、筛选分页结果

起始下标为 0

• 从 s 开始，筛选 n 条结果
  select ... from 表名 [where ...] [order by ...] limit s, n;

• 从 0 开始，筛选 n 条结果
  select ... from 表名 [where ...] [order by ...] limit n;

• 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
  select ... from 表名 [where ...] [order by ...] limit n offset s;

limit子句一般都是最后执行，因为它本质是一个显示性质的语句，只有数据被处理好了，才能被显示（limit子句在order by后面执行）

三、改

update 表名 set 列名 = 数据  [where ...] [order by ...] [limit ...];

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

注意：在更新时一定要注意加筛选条件，否则就是全表修改，请谨慎使用！！！

四、删

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

• 删除局部数据 

• 删除整表数据 

当我们删除整表数据时，自增值并没有被重置。

但还有一条语句也能删除整表数据，但是会重置自增值

truncate table 表名;

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

五、插入查询结果

insert into 表名 [(column [, column ...])] select ...

六、聚合函数

函数	说明
count([distinct] expr)	返回查询到的数据数量，null 不会计入
sum([distinct] expr)	返回查询到的数据的总和，不是数字没有意义
avg([distinct] expr)	返回查询到的数据的平均值，不是数字没有意义
max([distinct] expr)	返回查询到的数据的最大值，不是数字没有意义
min([distinct] expr)	返回查询到的数据的最小值，不是数字没有意义

使用如下： 

•  计算有多少条记录

• 计算数学成绩总和

• 计算英语成绩平均值

• 计算语文成绩最大值和最小值

当然我们也可以先筛选数据，然后再进行聚合，如显示语文成绩>60分的最大值和最小值

七、group by

• group by 子句可以对指定列进行分组查询
  select column1, column2, .. from table group by column;

假设我们有一张员工表，内容如下(id - 编号，name - 姓名，sal - 薪资，deptno - 部门号)

• 找出每个部门的最高工资和最低工资

• 找出平均工资低于2000的部门和它们的平均工资

说明：having的作用和where类似，having一般和group by搭配，where一般和select搭配，两者再一些情况下可以混用，但是不推荐，容易混淆。

简单说明一下这种查询语句的执行顺序

在进行数据库查询时，如果查询的条件比较复杂，我们可以分步来思考，即先找到姓名不为'a'的表，在将该表按部门进行分组聚合成新的表，在将该表数据进行筛选，得到最终的表。我们会发现我们分出来的每一步都是将一张表变成另一张表，从逻辑上来说就是表和表的变换，所以我们不用关心它内部是怎样的结构，在我们看来，它就是对(不同)表的操作。