sql的数据和表格处理方法

处理邮箱和手机号

delete FROm  all_tables WHERE (email not LIKE "%@%" and phone not REGEXP '^1[3456789][0-9]{9}$');
update all_tables set email='0' WHERE email not LIKE "%@%" ;
update all_tables set phone='0' WHERE phone not REGEXP '^1[3456789][0-9]{9}$';

执行 .sql 文件

msyql -u root -p dbname < xxx.sql

use dbname;
source xxx.sql

select into outfile

用来导出

 SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'  FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

load data local file

用来导入

msyql -u root -p
use {dbname}

load data local infile "/root/Desktop/wutuopai/from_win10/firstbig_handle.csv" into table firstbig_handle fields terminated by ',' enclosed by '"';

建表不索引

建表时一定不要（请注意是一定不要）建好索引，外键约束等等，可以再数据导入完成后添加各种约束，否则数据的导入将会出错，或特别慢。

加添字段

如果有必须要加添的字段，可以在完成数据导入后用SQL语句加添字段，实例如下：

ALTER TABLE litigationinfo ADD id INT NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST

insert into select

插入另外一张存在的表

Insert into Table2(field1,field2,...) select value1,value2,... from Table1

create as select

根据某个字段拆分成多张表

create table XXX as select * from result where city_id=12

replace

替换某些不正确的字段

update tablename set columnname = replace(cloumnname ,'xx','')

用变量重置id

SET @num = 0; 
UPDATE third_level SET id = (@num := @num + 1);

拆分字段

UPDATE city set lat=SUBSTR(loc,1,POSITION(';' in loc)-1);
update city set lon = SUBSTR(loc,POSITION(';' in loc)+1);

根据某个字段，生成另外的字段（查其他表）

UPDATE handle,city set handle.`code` = city.`code` WHERE handle.`name`=city.`name`