数据处理过程中，用的sql 语句整理-昊天SEO

我们在开发过程中，总会遇到各种数据处理，今天整理了一些用到的部分sql 语句，分享一下！

数据处理过程中，用的sql 语句整理

delete from table_name where 条件
// 删除2015-01-01 00:00:00'年以后的，并且登录名以'_catering@qq.com'结尾的用户详情
delete from user_detail where userId in (select wu.id from web_user wu where wu.loginName like '%_catering@qq.com' and registerTime > '2015-01-01 00:00:00')
// 删除时，要注意表之间的依赖
delete from web_user where loginName like '%@22.com' and registerTime > '2014-01-01 14:12:30'
2. <span class='wp_keywordlink_affiliate'><a href="https://www.168seo.cn/tag/mysql" title="View all posts in mysql" target="_blank">mysql</a></span> 复制表结构与数据
// 我们要对原数据进行批量修改时，需要先备份数据
create tabel 新表名 select * from 旧表名 (要求：新表名必须不存在)
例：create table v6_sys_code_bak SELECT * from v6_sys_code

delete from table_name where 条件

// 删除2015-01-01 00:00:00'年以后的，并且登录名以'_catering@qq.com'结尾的用户详情

delete from user_detail where userId in (select wu.id from web_user wu where wu.loginName like '%_catering@qq.com' and registerTime > '2015-01-01 00:00:00')

// 删除时，要注意表之间的依赖

delete from web_user where loginName like '%@22.com' and registerTime > '2014-01-01 14:12:30'

2. mysql 复制表结构与数据

// 我们要对原数据进行批量修改时，需要先备份数据

create tabel 新表名 select * from 旧表名 (要求：新表名必须不存在)

例：create table v6_sys_code_bak SELECT * from v6_sys_code

3.数据备份与还原
#备份
drop table if exists school_bak_20160505; // 删除备份表
create table school_bak_20160505 like school; // 复制表结构
insert into school_bak_20160505 select * from school; // 复制表数据
select count(*) from school_bak_20160505; // 查询备份表的数据量
select count(*) from school; // 查询原表的数据量
#修改表
alter table `school`
add column `type` TINYINT(4) null default 1 after `status`;
##数据还原
#drop table if exists school;
#create table school like school_bak_20160505;
#inser tinto school select * from school_bak_20160505;
#select count(*) from school_bak_20160505;
#select count(*) from school;
insert into school_bak_20160505 select * from school where grade=2;
#注：还可以将指定条件的数据新插入到school_bak表中

3.数据备份与还原

#备份

drop table if exists school_bak_20160505; // 删除备份表

create table school_bak_20160505 like school; // 复制表结构

insert into school_bak_20160505 select * from school; // 复制表数据

select count(*) from school_bak_20160505; // 查询备份表的数据量

select count(*) from school; // 查询原表的数据量

#修改表

alter table `school`

add column `type` TINYINT(4) null default 1 after `status`;

##数据还原

#drop table if exists school;

#create table school like school_bak_20160505;

#inser tinto school select * from school_bak_20160505;

#select count(*) from school_bak_20160505;

#select count(*) from school;

insert into school_bak_20160505 select * from school where grade=2;

#注：还可以将指定条件的数据新插入到school_bak表中

3.查询与清理数据
#查询（爬的xx网站的数据，嘻嘻）
select distinct left(url, 35) from url_house WHERE batchId = 101;
select * from url_house where url like 'http://www.mofangge.com/app/tsj/tsj%';
select website, count(1) from grab_question group by website;
select spiderCode, count(1) from url_house u where batchId = 101 group by spiderCode;
select q.*, a.* from grab_question q, grab_answer a where q.id = a.qId and q.website = '魔哥';
select * from url_house u where batchId = 101 order by id desc limit 50;
##清理数据（清掉重来喽）
select batchId, count(1) from url_house group by batchId;
delete from url_house where batchId = 101;
delete from grab_answer where exists(select 1 from grab_question q where q.id = qId and q.website = '魔哥');
delete from grab_question where website = '魔哥';

3.查询与清理数据

#查询（爬的xx网站的数据，嘻嘻）

select distinct left(url, 35) from url_house WHERE batchId = 101;

select * from url_house where url like 'http://www.mofangge.com/app/tsj/tsj%';

select website, count(1) from grab_question group by website;

select spiderCode, count(1) from url_house u where batchId = 101 group by spiderCode;

select q.*, a.* from grab_question q, grab_answer a where q.id = a.qId and q.website = '魔哥';

select * from url_house u where batchId = 101 order by id desc limit 50;

##清理数据（清掉重来喽）

select batchId, count(1) from url_house group by batchId;

delete from url_house where batchId = 101;

delete from grab_answer where exists(select 1 from grab_question q where q.id = qId and q.website = '魔哥');

delete from grab_question where website = '魔哥';

4.多表查询 (呵呵，每次看到都很头疼)
StringBuilder sb = new StringBuilder();
sb.append("select * from("
+ "select g.id, g.avatarSmall, g.type, g.name, g.members, g.topicNum, gu.userId,gu.realname, "
+"CONVERT ((select GROUP_CONCAT(c.name) from v6_sys_code c, v6_sys_code_tags t where c.id = t.sysCodeId and t.objId = g.id and g.id = gu.groupId and gu.privilleges = @privilleges and g.type = @gType group by g.id ) USING utf8 ) AS tagName "
+ "from q_group g, q_group_user gu "
+ "where g.id = gu.groupid and gu.privilleges = @privilleges and g.type = @gType ) g "
+ "where ( g.tagName like @content or g.NAME like @content )");
CONVERT ((select GROUP_CONCAT(c.NAME) from v6_sys_code c where c.type = @gType group by c.id ) USING utf8 ) AS tagName,
convert ((select group_concat(c.name) from table where id=2) using utf8) as alias

4.多表查询 (呵呵，每次看到都很头疼)

StringBuilder sb = new StringBuilder();

sb.append("select * from("

+ "select g.id, g.avatarSmall, g.type, g.name, g.members, g.topicNum, gu.userId,gu.realname, "

+"CONVERT ((select GROUP_CONCAT(c.name) from v6_sys_code c, v6_sys_code_tags t where c.id = t.sysCodeId and t.objId = g.id and g.id = gu.groupId and gu.privilleges = @privilleges and g.type = @gType group by g.id ) USING utf8 ) AS tagName "

+ "from q_group g, q_group_user gu "

+ "where g.id = gu.groupid and gu.privilleges = @privilleges and g.type = @gType ) g "

+ "where ( g.tagName like @content or g.NAME like @content )");

CONVERT ((select GROUP_CONCAT(c.NAME) from v6_sys_code c where c.type = @gType group by c.id ) USING utf8 ) AS tagName,

convert ((select group_concat(c.name) from table where id=2) using utf8) as alias

4.复杂更新（基于myibatis的）
<!-- 更新作业的avgScore(主观题平均正答率), status=3（已判） -->

update zy_homework h
set h.avgScore = (select ifnull(SUM(sh.subjectiveRate)/COUNT(1),0) AS avgRate
from zy_student_homework sh WHERE sh.hwId = h.id), h.status=#{status}
where h.id=#{id}

4.复杂更新（基于myibatis的）

update zy_homework h