蠕虫复制
蠕虫复制:从已有的数据中去获取数据,然后将数据又进行新增操作;数据成倍的增加。
表创建高级操作:从已有表创建新表(复制表结构)
create table 表名 like.数据库.表名;
蠕虫复制:先查出数据,然后将查出的数据新增一遍
insert into 表名[(字段表名)] select 字段列表 * from 数据表名;
系统报错,原因:前面my_copy已经复制了表结构,与现在被复制的表结构不符合,且不能再复制subscribe的表结构。需要创建一个新的my_copy1复制subscribe的表结构再进行蠕虫复制。
系统报错:每个表最多只有一个主键,即主键具有唯一性;主键不能为空,即逐渐具有非空性。主键的内容字段必须唯一,其他字段可以不唯一。蠕虫复制会导致一张表出现多个相同内容,因此报错。
图片来源于https://blog.csdn.net/sphinx1122/article/details/84402854
对特定字段进行蠕虫复制,让主键进行自增长,便可解决此问题。(蠕虫复制可以复制数据表本身,也可以复制其他的数据表)
蠕虫复制的意义:
1.从已有表拷贝数据到新表中
2.可以迅速的让表中的数据膨胀到一定的数量级,测试表的压力以及效率
解决主键冲突
新增数据
基本语法:
insert into 表名 [(字段列表)] values(值列表);
在数据插入的时候,假设主键对应的值已经存在,插入一定会失败!
主键冲突
当主键存在冲突的时候(Duplicate key),可以选择性的进行处理:更新和替换。(先给数据表新增数据,如果发生冲突即主键重复,便进行更新或替换)
查看表结构,如果没有主键需给表新添一条主键。
主键冲突:更新操作(太麻烦)
insert into 表名 [(字段列表 包含主键)] values(值列表) on duplicate key update 字段 = 新值;
注:包含主键:包含主键才能造成主键冲突; on:当xx事情发生;update:更新
主键冲突:替换(把重复的旧主键数据删除,再新增新的主键数据)
insert into 表名 [(字段列表 包含主键)] values(值列表);
清空数据
删除:当表中存在主键自增长,即使把表数据删除,自增长也不会还原。
思路:数据的删除不会影响到表的结构(自增长上升到表的选项),只能删除表后重建新表。
truncate 表名; – 先删除该表,再新增该表。
去除重复记录
查询数据
基本语法:
select 字段列表/* from 表名 [where 条件];
完整语法:
select[select 选项] 字段列表[字段别名]/* from 数据源 [where 条件子句] [group by 子句] [having 子句] [order by 子句] [limit 子句] ;
select 选项
select 对查出来的结果的处理方式
- all:保留所有的查询结果
- distinct:去重,将查出的结果中重复的给去除(所有字段都相同)
注意:distinct去重查询必须要求数据每个字段一一相同才能进行去重查询。
如上图所示,需要数据id,email,status,code完全相同才能去重查询。
排序【order by】
定义:order by即通过对某个字段,使用表对应的校对集实现升序或降序的排序。
1.order by语法:order by 字段名 [ASC|DESC] ;其中ASC是升序,DESC是降序。
2.中文排序:绝大部分字符集的校对集默认是ci即不区分大小写,如果要对中文进行排序,那么必须指定排序方式(可以事先设定表的校对集),order by的中文方式:order by convert(字段 using 字段集)[collate 校对集];
注意:中文的排序是按拼音进行排序的(gbk),utf8不支持中文比较,一般情况下不会对中文进行排序。
3.多字段排序:即对某个字段排序后,再将排序字段内的其他字段进行排序,排序语法为:order by 字段1 [ASC|DESC],字段2 [ASC|DESC]…
注意:多字段排序是按照排序顺序先在字段1的基础上进行排序,然后再按照已经排好的顺序进行字段2的排序。
总结&需要注意的事项:
- 可以使用order by 字段名 [ASC|DESC] 来进行升序|降序排序(整个记录跟着排序)对含有null值的列进行排序时,升序排列null值将出现在最前面,降序排序null值出现在最后。
- order by可以对多个字段进行排序,排序原则是在第一个字段排序好后(有类似分组效果),然后对应另外字段进行排序。
- 3.如果碰到需要使用中文进行排序,可以利用gbk排序,使用order by conver(中文字段using gbk)。
限量【limit】
定义: limit限制的意思,即在获取记录的时候,可以按照设定的记录数来获取对应的数据。
1.简易版limit:限制获取的记录数,语法规则为: limit 具体数量;
2. limit在设定的数量大于记录数量时,就是获取全部数据(上图所示)。
3.综合版limit:限制获取记录数的同时,还可以设定获取数据的起始位置,语法规则为: limit 起始数量获取数。
4. limit分页功能实现:分页就是按照起始根据页码不断的计算起始位置,去获取不同数据。
●页码:外部传
●数据长度:明确规范数据
●起始位置: (页码-1)*数据长度
●分页SQL: limit起始位置,数据长度;
|总结:
1.简易limit可以用来限制数据获取的数量,通常是从第1条记录到指定数量。
2.综合limit可以实现分页,通过计算起始位置和数据长度控制。
分组【group by】
定义:group by即分组,可以将查询结果按属性列或属性列组合在行的方向上进行分组,每组在属性列或属性列组合上具有相同的聚合值。
以这张sh_goods表为例。
错误案例:没加group by与加了group by的区别。
1.group by语法:select 数据列表 from 数据表名 group by 字段名;
2.group by分组原理:
●按照分组字段,将狱取到的记录分成几块
●保留每块的第一 条记录
3. group by目的: group by分组的目的是为了实现分组统计,分组统计主要用到以下聚合函数
●count(*/字段名): 统计分组字段对应的记录数量
●max(字段名):统计分组后某个字段的最大值
●min(字段名):统计分组后某个字段的最小值
●avg(字段名):统计分组后某个字段的平均值
●sum(字段名):统计分组后某个字段的和
●group concat(字段名): 统计分组后,某个分组字段的字符串拼接
分别查询sh_goods表中文具、服装、电子产品里最大值、最小值、总和、平均值、对应商品名、数量。
4 group by注意事项
●MySQL5.7.5版本后,凡是使用到group by分组统计后, select后的字段信息必须只能是分组字段本身或者使用的聚合函数统计; 5.7.5以前允许查看其它非分组字段(每组第一条记录对应字段值)。
●count函数在进行字段统计时,不会统计字段值为NULL的字段。
●group by子句必须在where子句之后使用(可以没有where子句) : where是从磁盘读取数据,group by是针对已经进入内存的数据进行分组。
●如果想访问其他非分组统计字段,那么可以使用系统函数any._value(想要查看的字段)来实现(也可以修改MySQL配置文件中的严格模式,网上很多方案,输入错误即可查到)。
5.多分组:group by可以针对多个字段进行分组,即根据某个字段分组后,再对分组进行其他字段分组。多分组语法为:group by 字段1,字段2,…,字段N;
多分组,先按关键词分组,再按评分分组,最后显示对应的数量。
6.6.分组排序: group by在进行字段分组的同时,也会分组字段进行排序,默认排序为升序ASC,可以使用DESC降序排序,排序语法为group by段[ASC|DESC];
分别查询文具、服装、电子产品所包含的产品名字,分别以升序和降序显示出来。
7.回溯统计:回溯统计是指当数据被分组后,系统会在最后额外按照分组字段进行-次统计, 此时分组字段会被置空。回溯统计语法为: group by字段1,字段2…字段N with rollup;
对所有统计的结果进行回溯再一次统计。
|总结
1.可以使用group by字段名来根据字段进行分组。
2.分组的目的是为了进行数据统计,因此配合使用聚合函数: sum(), max(), min(), avg(), count)和
group. _concat()。
3.分组统计在不同版本中,对于非分组字段(没有使用聚合函数)有查询区别: 5.7.5以前允许查询,5.7.5以后不允许查询。
4. group by子句与where子句同时存在的时候,必须在where子句之后。
5. group by可以实现多字段分组。
6. group by分组后会自动根据分组字段进行升序排序,可以控制排序方式为降序DESC。
7. group by分组统计可以进行回溯統计,即查询对应字段末分组之前效果。
判定【having】
定义: where是从磁盘读取数据时进行判定,而在数据进入到内存之后where就不能生效,having是完全针对进入内存后的数据进行判定。
having子句通常与group by子句结合使用,尽管指定该子句时也可以不带group by。having子句指定在应用where子句的筛选后要进一步应用的筛选。
1.having语法: having几乎能做where所能做的所有事情: having 条件判定
having与where的不同之处:where是在数据表里先查询,查询出对应的数据后再放进内存里;having把所有数据先放进内存里,再进行筛选。效率方面having比where要低。
2.having主要针对group by后的统计结果进行判定(having可以用字段别名进行操作)
判断所有评论数量大于2的keyword与所对应的name并对keyword进行分组。
注意: having子句中用到的字段。必须在select后出现过, 即字段从磁盘读入到内存中。
3.having条件判定中可以直接使用聚合函数(建议使用别名,效率更好)
|总结
1.having是一种和where类似的条件判定子句
2. having是针对内存中的数据进行条件判定的,因此可以使用字段别名和聚合函数
3.如果是字段本身条件筛选,不是进行分组统计后的话,一定使用where
别名
定义:别名,即对某些较长名字或者冲突名字(重复)指定一个新的名字, 在最后查看数据的时候就可以通
过新的名字来进行查看。
1.别名分为两种:字段别名和表别名,使用方式基本一致: 字段名/表名[as]别名
错误情况:中文没有用反引号
|总结
1.可以通过字段名/表名[as] 新名字;来实现别名操作
2.别名的目的是为了让名字变得简洁或者更加方便使用(不重复字段)
3.别名只能在查询数据的SQL指令中使用(方便操作者使用)
4.别名-般是在比较复杂的SQL指令中用到,如果SQL指令本身很简单就不用别名
聚合函数
●为了快速得到统计数据,经常会用到如下5个聚合函数.
●count()表示计算总行数,括号中写星与列名,结果是相同的
●聚合函数不能在where中使用
1.查询商品总数
count(*)是指把数据表中每列元素结合起来当成一列元素,每一行只要有值便统计进去。
2.查询电子产品最高价钱
3.查询价格大于500的商品中评分最少的商品
4.查询文具价格的总和
5.查询服装的平均评分