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

数据处理过程中,用的sql 语句整理
1 2 3 4 5 6 7 8 9 |
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' // 我们要对原数据进行批量修改时,需要先备份数据 create tabel 新表名 select * from 旧表名 (要求:新表名必须不存在) 例:create table v6_sys_code_bak SELECT * from v6_sys_code |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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表中 |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 = '魔哥'; |
1 2 3 4 5 6 7 8 9 10 |
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 |
1 2 3 4 5 6 7 |
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} |

