小SQL大作用

从DBA那问来的，备份现有数据库表：

create table B select * from A ;

删除，重建数据库主键

alter table book_order drop primary key; 
alter table book_order add primary key(bookid,platform,stat_date);

 根据小时分组数据

select date_format(inserttime,'%Y-%m-%d %H'),count(userid), count(distinct userid) from pppay_order_alipay where status=8888 and moneyType=1 and inserttime > '2013-10-16' and inserttime < '2013-10-23' group by date_format(inserttime,'%Y-%m-%d %H');

select date_format(from_unixtime(dateline),'%Y-%m-%d %H'),count(userid),count(distinct userid) from pppay_order where status=4 and moneyType=1 and from_unixtime(dateline) > '2013-10-16' and from_unixtime(dateline) < '2013-10-23' group by date_format(from_unixtime(dateline),'%Y-%m-%d %H');

date_format  函数生成这样的字符串2013-10-12 00 ~ 2013-10-12 23 数据集也根据这个进行分组

 

concat 字符串链接函数, format控制小数位数

select area,count(distinct userid) as uv, concat(format(count(distinct userid)/119089*100,2),'%') as uv_rate,sum(pv) as pv,  concat(format(sum(pv)/1454985*100,2),'%') as pv_rate from foreigners group by area order by uv desc; 

 

创建视图

create view byb_booklist_view as select distinct bid,price,date_format(stat_date,'%Y-%m') as month from byb_read_list where book_type=2;

 修改字段名

alter table pay_stat_foruc change price price int;