整理QQ数据库sql语句

设置数据库的时候 qq 号如果用整型,设置成UNSIGNED,不然超过一定数值就错误

UPDATE `sao_qq` SET qq_num = REPLACE ( qq_num, '@qq.com', '' );

delete from sao_qq where qq_num not like '%qq.com%'

select * from sao_qq where LENGTH(0+qq_pass)=LENGTH(qq_pass) 判断是否纯数字

delete from sao_qq where length(qq_pass)<9 and LENGTH(0+qq_pass)=LENGTH(qq_pass)

select * from sao_qq where not LENGTH(0+qq_num)=LENGTH(qq_num) // 取不是纯数字

@QQ.COM

UPDATE `sao_qq` SET qq_num = REPLACE ( qq_num, '@QQ.COM', '' );

UPDATE `sao_qq` SET qq_num = REPLACE ( qq_num, '@qzone.qq.com', '' );

UPDATE `sao_qq` SET qq_num = REPLACE ( qq_num, '@vip.qq.com', '' );

UPDATE `sao_qq` SET qq_num = REPLACE ( qq_num, '@QQ.com', '' );

UPDATE `sao_qq` SET qq_num = REPLACE ( qq_num, '@qq.COM', '' );

select * from sao_qq where find_in_set('@qq.com',qq_num)>0

SELECT * FROM `za1` WHERE `qq_num` IS NULL

SELECT * FROM sao_qq WHERE qq_pass REGEXP '^[a-zA-Z]*$' 纯数字

SELECT * FROM sao_qq WHERE qq_pass REGEXP '^[0-9]*$'

SELECT * FROM sao_qq WHERE qq_pass REGEXP '^[a-zA-Z]{0,9}[0-9]{0,9}$'

delete FROM sao_qq WHERE qq_pass REGEXP '^[0-9]*$' // 删除纯数字

delete from sao_qq where qq_pass REGEXP '^[a-zA-Z]*$' // 删除纯字母

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '0', ''); //把0替换空

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '1', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '2', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '3', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '4', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '5', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '6', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '7', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '8', '');

UPDATE `sao_qq` SET `qq_pass` = REPLACE(`qq_pass`, '9', '');

delete from sao_qq where length(qq_pass)<6 // 删除密码少于6个字符

SELECT * FROM sao_qq WHERE qq_pass REGEXP '^[a-zA-Z]{1,9}[0-9]{1,9}$'

delete from sao_qq where id not in(SELECT id FROM sao_qq WHERE qq_pass REGEXP '^[a-zA-Z]{1,9}[0-9]{1,9}$')

insert into sao_qq where

Insert into sao_qq2 select * from sao_qq WHERE qq_pass REGEXP '^[a-zA-Z]{1,9}[0-9]{1,9}$'

INSERT INTO sao_qq (id,qq_num,qq_pass,zt,hy) SELECT id,qq_num,qq_pass,zt,hy FROM db2_name

RENAME TABLE old_table TO backup_table,

delete from qq_midui where LENGTH(0+qq)<>LENGTH(qq) 判断QQ字段纯数字,删除QQ号里面带字母的

update qq_namepass2 LEFT JOIN qq_namepass ON qq_namepass2.qq = qq_namepass.qq set num=(select count(qq) as n from qq_namepass where qq=qq_namepass2.qq) // update 的left join

// 去空白

update `qq_midui` set `qq`=replace(`qq`,' ','');

update `qq_midui` set `qq`=replace(`qq`,'　','');

update `qq_midui` set `qq`=replace(`qq`,' ','');

update `qq_midui` set `qq`=replace(`qq`,'\r\n','');

update `qq_midui` set `qq`=replace(`qq`,'\n','');

update `qq_midui` set `qq`=trim(`qq`);

转载于:https://www.cnblogs.com/microtiger/p/6208071.html