常用SQL整理

整理了日常用到的一些sqls

1.插入表

insert into table_B select * from table_A

2.清空表

truncate table test #清空表，结构还存在
delete table test where ... #删除表中特定的数据
drop table test #删除整表，包括结构

3. 导出到文件

select * from table_A where filed1 like '8422%' into outfile '/usr/local/mysql/8422.txt' fields terminated by '||'

4.导入文件到DB

LOAD DATA INFILE "/root/user/output.txt" INTO TABLE table_A CHARACTER SET gbk fields terminated by x'09' IGNORE 1 LINES;
#字符集有时添加后会报错，可以去掉

5. 更新表

#一对一的情况
update table_A a, table_B b set a.field1 = b.field1 where a.field2 = b.field2

#一对多的情况
update table_A a inner join table_B b on a.route_id = b.valid_route_id set a.valid_route_id=b.valid_route_id

6. 字段拼接

UPDATE table_A set SIC_DESC=concat_ws('|',SICCODE1,SICCODE2,SICCODE3,SICCODE4)

7.字段截取

create table table_A ENGINE=MyISAM select locname,substring(file_name,1,3) from table_B where LOCNAME !=''

8.删除某列

alter table table_A drop column source_path

9.去重

table_A 

+----+--------+
| id | name   |
+----+--------+
| 1  | google |
| 2  | yahoo  |
| 3  | msn    |
| 4  | google |
| 5  | google |
| 6  | yahoo  |
+----+--------+

#1 通过条件选取最大或最小的记录，来达到去重的目的
select * from table_A a where id=(select min(id) from table_A where name=a.name)

#2 通过group by 和order by
select * from table_A group by name order by id desc;

#3 keep the row with the lowest id
(1)DELETE n1 FROM table_A n1, table_A n2 WHERE n1.id > n2.id AND n1.name = n2.name

(2)DELETEFROM NAMES
 WHERE id NOTIN(SELECT*FROM(SELECT MIN(n.id)FROM NAMES n
                        GROUPBY n.name) x)


#4 keep the row with the highest id
DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

10.分组取最大的N条记录

#先添加一个rowid
select *from  sh_20110401_bankup as T where 3>(select count(*) from sh_20110401_bankup 
where field1=T.field1  and rowid>T.rowid order by T.field1,T.rowid desc )

11.分组取最大值

#通过两步
(1)create table sh_20110301_0331_ptp_t select  record_date,substring(time_stamp,1,2)as tim,count(*)as cc from sh_20110301_0331_ptp
group by substring(time_stamp,1,2),record_date order by record_date,substring(time_stamp,1,2)

(2)select * from sh_20110301_0331_ptp_t where cc in (select max(cc) from sh_20110301_0331_ptp_t
group by tim) order by tim 

12.拆分分隔符

update table_A set custom11=substring_index(custom11,';',1)

13 日期转数字

update table_A set convert_date=unix_timestamp(record_date)
 tt=TIME_TO_SEC(time_stamp)

14 MD5,字段不能有null

update table_A set dd = MD5(CONCAT_ws('$',id,attribute_key,attribute_value)

15 上一个月的数据

select * from review where period_diff(date_format(now() , '%Y%m') , date_format(`Review_Date`, '%Y%m')) =1;

16 上一周的数据

select Rating,count(*) from review where week(`Review_Date`)=7  group by Rating

17 最近7天的数据

select week(`Review_Date`) from review where date_sub(curdate(), INTERVAL 7 DAY) <= date(`Review_Date`) 

18 字符变日期

update table_A set tt= str_to_date(date_created,'%m/%d/%Y')

 19 字段拆分

update table_A set custom=substring_index(custom,';',1)