MySQL高级SQL操作（一）

MySQL高级SQL操作

高级SQL操作：利用SQL指令的变化实现一些复杂业务的数据操作 常见的SQL高级操作，主要集中在数据操作（增删改查），基于基础操作之上实现一些复杂业务的数据操作。

#数据新增 *多数据插入 *蠕虫复制 *主键冲突

#查询数据 *查询选项 *别名 *数据源 *where子句 *group by子句 *having子句 *order by子句 *limit子句

#更新数据 *限制更新

#删除数据 *限制删除 *数据清除

eg:统计不同班级的人数信息

select count(*),班级ID from 学生表 group by 班级ID;

#一、数据新增

*批量插入 *蠕虫复制 *主键冲突 冲突更新 冲突替换

##1、批量插入

批量插入：是一种优化数据逐条插入的方式，批量插入数据的语法与简单数据插入的语法差不多

批量插入分为两种：

全字段批量插入

insert into 表名 values(值列表1),(值列表2),...(值列表N);

部分字段批量插入（注意字段默认值）

insert into 表名 (字段列表) values (值列表1),(值列表2),...(值列表N);

steps: 1、用户的操作涉及到多记录同时插入（通常数据批量导入）

2、组装成批量插入SQL指令 *字段为全字段（逻辑主键不存在没问题）：全字段批量插入SQL *部分字段：组织字段列表并检查其他字段是否允许默认值

3、执行批量插入

eg:学生成绩的插入

#批量插入，全字段 insert into t_1 values(null,'Tom','Computer',90),(null,'Lily','Computer',92); #考试信息，不包括成绩 insert into t_1 (stu_name,course) values('Tony','English'),('Ray','Math');

##2、蠕虫复制

蠕虫复制：从已有表中复制数据直接插入到另外一张表（同一张表）

*蠕虫复制的目标是快速增加表中的数据 实现表中数据复制（用于数据备份或者迁移） 实现数据的指数级递增（多用于测试） *蠕虫复制语法

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

*注意！！！ 字段列表必须对应上 字段类型必须匹配上 数据冲突需要事先考虑

steps 1、确定一张需要插入数据的表（一般与要蠕虫复制数据的表结构一致）

2、确定数据的来源表 字段数量匹配 字段类型匹配 所选字段不存在冲突（数据可能是重复数据）

3、使用蠕虫复制

eg：创建一张新表，将t_1表中的数据迁移到新表中

create table t_11( id int primary key auto_increment, stu_name varchar(20) not null, course varchar(20) not null, score decimal(5,2) )charset utf8; insert into t_11 select * from t_30; #快速让t_11中数据达100条 insert into t_35 (stu_name,course,score) select stu_name,course,score from t_35;

##3、主键冲突

主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在，主键冲突的业务通常是发生在业务主键上（业务主键本身有业务意义）

主键冲突的解决方案：

#1、忽略冲突：保留原始记录 insert ignore into 表名 [(字段列表)] values(值列表); #2、冲突更新：冲突后部分字段变成更新 insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值[,字段=新值...]; # 1、尝试新增 # 2、更新 #3、冲突替换：先删除原有记录，后新增记录 replace into 表名 [(字段列表)] values(值列表); # 效率没有insert高（需要检查是否冲突）

steps 1、确定当前业务可能存在主键冲突

2、选择主键冲突的解决方案 eg： 1、用户名作为主键的用户注册（冲突不能覆盖）：username，password，regtime

create table t_2( username varchar(50) primary key, password char(32) not null, regtime int unsigned not null )charset utf8; insert into t_2 values('username','password',12345678); # 冲突忽略 insert ignore into t_2 values('username','12345678',12345678);

2、用户名作为主键的记录用户使用信息（不存在新增、存在则更新时间）：username，logintime

create table t_3( username varchar(50) primary key, logintime int unsigned )charset utf8; insert into t_3 values('username',12345678); # 当前时间戳 # 冲突更新（替换部分字段数据） insert into t_3 values('username',12345678) on duplicate key update logintime = unix_timestamp(); # 当前时间戳

如果主键不冲突：新增，如果主键冲突：更新指定字段，上述方式适用于字段较多，但是可能冲突时数据变化的字段较少 3、用户名作为主键的记录用户使用信息（不存在新增、存在则更新全部）：username，logintime、clientinfo

create table t_4( username varchar(50) primary key, logintime int unsigned, clientinfo varchar(255) not null )charset utf8; insert into t_4 values('username',unix_timestamp(),'{PC:chrome}'); # 替换插入 replace into t_4 values('username',unix_timestamp(),'{phone:uc}');

replace遇到主键重复就会先删除、后新增。如果有较多字段需要更新：建议使用替换