MySQL基础篇：07_数据高级操作

---

主键冲突

• 在数据插入的时候，假设主键对应的值已经存在：插入一定会失败：主键冲突
• 当主键存在冲突时（duplicate key）可以选择性的进行处理：更新和替换

（1） 主键冲突：更新操作

insert into 表名称[(字段列表：包含主键)] values(值列表) 
on duplicate key update 字段 = 新值;

• 现在的业务需求是：当主键冲突的时候：更新相关字段的值：主键字段的值当然不变
  

（2） 主键冲突：替换操作

replace into 表名称[(字段列表: 包含主键)] values(值列表);

蠕虫复制

• 蠕虫复制：从已有的数据中去获取数据，然后将数据又进行新增操作：数据成倍的增加
• 表创建高级操作：从已有表创建新表：复制的是已有表的结构

create table 表名称 like 数据库名称.表名称;

• 蠕虫复制语法：先查询出数据，然后将查询出来的数据再新增一遍

insert into 表名称[(字段列表)] select */字段列表 from 表名称;

• 蠕虫复制：insert into my_table(name, age) select name, age from my_table;
  
• 蠕虫赋值的意义：
  （1） 从已有表拷贝数据到新表
  （2） 可以迅速让表中的记录膨胀到一定数量级：测试表的压力以及效率

---

更新数据

update 表名称 set 字段1=值,字段2=值,... [where条件];
update 表名称 set 字段1=值,字段2=值,... [where条件] [limit更新数量];

---

删除数据

delete from 表名称 [where条件] [limit条件];
truncate 表名称; -- 

• 如果表中存在主键自增长，那么删除记录之后，自增长不会还原
  

---

查询数据

• 这里的查询数据指的是单表查询

select [select选项] */字段列表
from 数据源
[where条件字句]
[group by字句]
[having字句]
[order by字句]
[limit字句]

select选项

• select选项有两个取值：all与distinct
  （1）all：默认值，保留查询出来的所有记录
  （2）distinct：去重：查询出来的结果，将重复的记录去除：重复的记录指的是所有字段的值都相等的记录就是重复的记录
  

字段别名

• 字段别名：当数据进行查询出来的时候，有时候字段的名字并一定就满足需求：多表查询的时候会有同名的字段：需要对字段名称进行重新命名：字段别名

字段名  [as] 字段别名

数据源

• 数据源：数据的来源，关系型数据库的来源都是数据表：本质上只要保证数据类似二维表，最终都可以作为数据源
• 数据源分类：
  （1） 单表数据源
  （2） 多表数据源
  （3） 查询语句
• 数据源：单表数据源
  
• 数据源：多表数据源

select */字段列表 from 表1,表2,......; 

• 从一张表中依次取出记录去匹配另一张表的所有记录，而且全部保留结果：记录数与字段数：这种结果称为：笛卡尔积（交叉连接）：笛卡尔积没有什么卵用，所以应该尽量避免
• 数据源：查询语句：子查询

select */字段列表 from (select语句) [as] 表名称;

where字句

• where字句：用来判断数据，筛选数据
• where字句返回结果：0或1：0表示false，1表示true
• 判断条件

比较运算符：>、>=、<、<=、=、!=、like、between...and...、in/not in
逻辑运算符：&&(and)、||(or)、!(not)

• where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断条件是否成立：从磁盘取出一条记录，开始进行where判断：判断的结果如果成立就将当前记录保存到内存中；如果失败就直接放弃
• where字句中是不能使用字段别名的：因为字段别名是字段进入到内存之后才有的：但是where是从磁盘中获取数据进行判断的
• where字句中当然可以使用表别名：因为表别名与内存无关
• 创建一张表

create table student(
	id int primary key auto_increment,
	number varchar(20) not null,
	name varchar(20) not null,
	gender varchar(10) default 'male',
	age tinyint unsigned default 16,
	height tinyint unsigned
)charset utf8;

insert into student(id,number,name,gender) values
(null,'itcast0001','张三','male'),
(null,'itcast0002','李四','male'),
(null,'itcast0003','王五','female'),
(null,'itcast0004','赵六','male'),
(null,'itcast0005','小明','male');

update student set age=floor(rand() * 20 + 20),
height = floor(rand()*20 + 170);

• 要求找出学生id为1或者3或者5的学生

• 查出区间落在180,190身高之间的学生
  
• between是闭区间：左侧的值必须不能大于左侧的值

group by字句

• group by：分组的意思，根据某个字段进行分组：相同的放一组，不同的就会分到不同的组中去

group by 字段1, 字段2,...

• 分组的意思：SQL中的分组的目的是为了统计数据：按组统计：按分组字段进行数据统计
• SQL提供了一些列统计函数

count()：统计分组后的记录数：每一组有多少记录
max()：统计每组中的最大值
min()：统计每组中的最小值
avg()：统计每组中的平均值
sum()：统计每组中的和

• count()函数里面可以使用两种参数：*与字段名：其中*代表统计记录数；字段名代表统计对应的字段：字段值为NULL的不统计
• 分组默认会自动排序：根据分组字段：默认是升序

group by 字段名 [asc|desc]; 

• 多字段分组：先根据第一个字段分组，然后对分组后的结果再次按照其他字段进行分组
  
• 函数group_concat(字段)：可以对分组结果中的某个字段进行字符串连接：保留改组所有的字段值
  
• 回溯统计：group by 字段名 with rollup：任何一个分组后都有一个小组，最后都需要向上级进行汇报统计：根据当前分组的字段！这就是回溯统计：回溯统计的时候会将分组字段置NULL
  
• 多字段回溯统计：考虑第一层分组会有此回溯: 第二次分组要看第一次分组的组数, 组数是多少,回溯就是多少,然后加上第一层回溯即可

having字句

• having字句与where字句一样：进行条件判断的：但是两者判断的时机不同
• where是针对磁盘数据进行判断：进入到内存之后，where就不能进行判断了：如果SQL语句进行分组操作，如果需要给分组结果进行判断处理，那么就需要使用having来处理：分组结果就使用having来处理
• having能做where能做的几乎所有的事情，但是where却不能做having能做的很多事情：where是针对磁盘数据

where	having
where是针对磁盘数据	having是对结果集进行过滤
where中不能使用聚合函数	having能够使用聚合函数
where中不能使用字段别名	having能够使用字段别名

• 字段别名是字段进入到内存之后
• where字句 早于 group by字句 早于 having字句
• 分组统计的结果或者说统计函数都只有having能够使用
  
• having能够使用字段别名，但是where不能使用字段别名：where是从磁盘取数据，而名字只能是字段名；字段别名是在字段进入内存后才产生的
  

order by字句

• order by是对结果集（内存中的二维表数据）进行排序
• 通过select出来的结果集是按表中默认的顺序来排序的，order by允许我们对查询结果针对某个字段进行排序
• 实际场景：根据数据的创建时间、更新书剑、文件大小、商品价格等字段来排序
• order by排序有两个值：asc与desc：其中asc是默认的，即升序

order by 字段名 [asc|desc], 字段名 [asc|desc]...

• 多字段排序：排序可以进行多字段排序: 先根据某个字段进行排序, 然后排序好的内部,再按照某个数据进行再次排序:
  
• 要想height字段降序看出效果来：前提是age字段值相等

limit字句

• limit字句是一种限制结果的语句：限制数量
• limit有两种用法

方案一：limit 数量
方案二：limit 起始位置, 数量

• limit方案1
  
• limit方案2：主要用来实现数据的分页：为用户节省时间，提交服务器的响应效率，减少资源的浪费
  （1） 对于用户来说：可以点击分页按钮【1】【2】【3】…
  （2） 对于服务器来说：根据用户选择的页码来获取不同的数据：limit offset,length（length：每页显示的数据量：基本不会变；offset = (页码 - 1) * length）