MYSQL学习笔记07:高级增删改查数据(主键冲突,蠕虫复制,limit,去重,别名,多表查询,动态数据,group by,聚合函数,回溯,having,order by,limit分页,运算符)

高级数据操作

• 高级增删改查数据(主键冲突,蠕虫复制,limit,去重,别名,多表查询,动态数据,group by,聚合函数,回溯,having,order by,limit分页,运算符)

新增数据

多数据插入

只要写一次insert指令,但是可以直接插入多条记录

基本语法:insert into 表名 [(字段列表)] values(值列表),(值列表)...;

主键冲突

在有的表中,使用的是业务主键(字段有业务含义),但是往往在进行数据插入的时候,有不确定数据表中是否已经存在对应的主键.

 

如上,插入stu0004小婷的时候主键冲突.

主键冲突的解决方案.

1. 主键冲突更新

类似插入数据语法,如果插入过程中主键冲突,那么采用更新方法.

insert into 表名 [(字段列表)] values (值列表) on duplicate update 字段1=新值1,字段2=新值2...;

如上,我们插入冲突主键并更新新值这条语句执行完毕,系统提示作用于2行,为什么呢?

因为1系统找到冲突主键并重新设置主键,2更新字段.因此2行受影响.

2. 主键冲突替换

当主键冲突之后,干掉原来的数据,重新插入进去.

replace into [(字段列表)] values(值列表);  //这种方式比第1种方式较耗用资源.

蠕虫复制

蠕虫复制:一分为二,二分为四...成倍增加.从已有的数据中获取数据,并且将获取到的数据插入到数据表中.

基本语法:insert into 表名 [(字段列表)] select*/字段列表 from 表;

注意:

1. 蠕虫复制通常是重复数据,没有太大业务意义,可以在短期内快速增加表的数据量,从而可以测试表的压力.还可以通过大量数据来测试表的效率(索引)

2. 蠕虫复制虽好,但要注意主键冲突.

更新数据

1. 在更新数据的时候,特别要注意:通常一定是指定跟随条件更新(而不是无条件批量更新!此为大忌!切记!)

update 表名 set 字段名=新值 where 判断条件;

2. 如果没有条件,是全表更新!(大忌!)但是可以使用limit来限制更新的数量

update 表名 set 字段名=新值 [where 判断条件] limit 数量;

如上,有很多数据,我们只想改变一部分.比如改变4个变成e,

我们使用:update my_simple set name='e' where name='a' limit=4;

删除数据

1. 删除数据的时候尽量不要全部删除，应该使用where来进行判定；

2. 删除数据的时候可以使用limit来限制要删除的具体数量。

delete删除的时候无法重置auto_increment

如上,删除表后,重新插入数据,期望id从1开始,但它从旧步长21开始计算,得到新步长22.超出我们的期望.

mysql有一个能够重置表选项中的自增长的语法:

truncate 表名; //删表所有数据并重置初始化变量 慎用!

查询数据

完整的查询指令

select select选项 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制;

select选项:系统该如何对待查询得到的结果

1. all:默认,表示保存所有记录;

2. distinct:去重,去除重复的记录,只表示一条.(所有的字段都相同)

我们对比下两者,看下对比.

很明显,去重后只保留5个结果,而不去重返回32个结果.

字段列表

字段列表有的时候需要从多张表中获取数据,在获取数据的时候,可能存在不同表中有同名字段,需要将同名的命名为不同名的:别名.alias

基本语法:字段名[as]别名

from数据源

from是为前面的查询提供数据:数据源只要是一个符合二维表结构的数据即可.

一.单表数据

基本语法:from 表名

前面的例子都是查单表数据.此处不再举例

二.多表数据

从多张表中获取数据,

基本语法: from 表1,表2,表3...

如上是分别查询两张表数据.

如上是同时查询两张表数据,将两张表所有数据合并输出.在数学上称为笛卡尔积,但是这个结果给数据库造成压力,没有其他意义!应该尽量避免出现笛卡尔积!

动态数据

from后面跟的数据不是一个实体表,而是一个从表中查询出来得到的二维结果表.

基本语法:from (select 字段列表 from 表) as 别名; //必须要有别名

where子句

用来从数据表获取数据的时候,进行条件筛选.

数据获取原理:针对表对应的磁盘来获取所有的记录(一条一条获取),where的作用就是在拿到一条结果就开始进行判断.判断是否符合条件,符合就保存下来,不符合就舍弃(不放到内存中).

where是通过运算符进行结果比较来判断数据.

group by子句

group by表示分组:根据指定的字段,将数据进行分组.分组的目标是为了统计.

分组统计

基本语法:group by 字段名;

如上,group by是为了分组后进行数据统计的.如果只是想看数据显示,那么group by没意义.group by将数据按照指定的字段分组之后,只会保留每组的第一条记录.

利用一些统计函数,也叫聚合函数.

count():统计每组中的数量.如果统计的目标是字段,则不统计为空即null的字段，如果为*代表统计记录。

avg():求平均值

sum():求和.

max():求最大值

min():求最小值.

如下,演示聚合函数.

group_concat():为了将分组中指定的字段进行合并(字符串拼接).示例如下,看看和如上代码的区别.

多分组

将数据按照某个字段进行分组之后,对已经分组的数据进行再次分组.

基本语法:group by 字段1,字段2...;//先按照字段1排序,之后将结果按照字段2排序,依此类推

分组排序

mysql中,分组默认有排序的功能,按照分组字段进行排序,默认是升序.

基本语法:group by 字段 [asc|desc], 字段2 [asc|desc]...;

回溯统计

当分组进行多分组之后,往上统计的过程中,需要进行层层上报,将这种层层上报统计的过程称为回溯统计:每一次分组向上统计的过程都会产生一次新的统计数据,而且当前数据对应的分组字段为null

基本语法:group by 字段 [asc|desc] with rollup;

再举个例子作为对比:

以上代码演示不使用回溯和使用回溯的对比.

having子句

having的本质和where一样,是用来进行数据条件筛选.

1. having是在group by子句之后,可以针对分组数据进行统计筛选,但是where不行.

实例演示:我们想统计人数大于等于4个人的班级.

首先尝试用where子句,失败,如下:

注意:where不能使用聚合函数:聚合函数是用在group by分组的时候,where已经运行完毕.

having在group by分组之后,可以使用聚合函数或者字段别名(where是从表中取出数据,别名是在数据进入到内存之后才有的)

注意:having与where的区别

强调:having是在group by之后,group by是在where之后:where的时候表示将数据从磁盘拿到内存,where之后的所有操作都是内存操作.

• WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。   
• HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

order by子句

order by排序子句:根据校对规则对数据进行排序

基本语法:order by 字段[asc|desc];   //asc升序,默认;desc降序

order by像group by一样,可以进行多字段排序:先按照第一个字段进行排序,然后按照第二个字段进行排序.

order by 字段1规则,字段2规则;

limit子句:

主要用来限制记录数量获取

记录数限制:

纯粹的限制获取的数量:从第一条到指定的数量

基本语法:limit 数量;

limit通常在查询的时候限定为一条记录,因为获取多条记录并不能解决业务问题,但是会增加服务器的压力.

分页:

利用limit来限制获取指定区间的数据

基本语法:limit offset,length; //offset偏移量:从哪开始,length就是具体的获取多少条记录数

注意:mysql中记录从0开始.

limit 0,2:表示获取前2条记录.

实例,分页演示:

注意:limit后面的length参数表示最多获取对应数量.但是如果实际数量不够,系统不会强求.

查询中的运算符

算术运算符: + - * / %

通常不在条件中使用,而是用于结果运算(select字段中)

比较运算符: > >= < <= = <>

通常用在在条件中限定结果

=: 注意mysql中没有==符号

<=>:相等比较.

特殊应用:就是在字段结果中进行比较运算

如上 字符串'1'==数字1,因为会转化成同类型;返回的结果1代表true,0代表false.

在条件判断的时候,还有对应的运算符:计算区间

Between 条件1 and 条件2;// 请看下例

注意:between中条件1必须小于条件2.反过来不可以.效果如下:

逻辑运算符: and or not

and-逻辑与:

or-逻辑或:

in运算符

in:在...里面.判断元素是否在1个集合(多个元素)中

基本语法: in (结果1,结果2...) 只要当前条件在结果集中出现过,那么就成立

is运算符

is是专门用来判断字段是否为null的运算符

基本语法:is null或is not null

like运算符

用来进行模糊匹配(匹配字符串)

基本语法：like ‘匹配模式’

匹配模式中,有两种占位符.

_  匹配对应的单个字符

% 匹配多个字符