@StarSky
2016-02-23T11:15:12.000000Z
字数 2147
阅读 920
MySQL
函数 | 例程 |
---|---|
ROUND()[1] | IFNULL(CONCAT(ROUND(a.amount_rate*100,2),'%'),'--') AS amount_rate |
CAST() | CAST ( expression AS data_type [ ( length ) ] ) |
CONVERT() | CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) |
FORMAT()[1] | format(x,n) |
DATE_FORMAT() | DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y%m%d') |
函数 | 例程 |
---|---|
order by | order by asc(ascend)/ desc(descend) |
group by | select id,group_concat[1](name separator ';') from aa group by id |
left()/ right()
插入
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
更新
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
删除
DELETE FROM 表名称 WHERE 列名称 = 值
truncate table dongfang_hk //清除所有数据,主键从1开始
delete from dongfang_hk //删除所有数据,主键继续增长
添加列
ALTER TABLE table_name
ADD column_name datatype
删除列
ALTER TABLE table_name
DROP COLUMN column_name
一般索引
create index index_name on table_name (column_list) ;
alter table table_name add index index_name (column_list) ;
唯一索引
create unique index index_name on table_name (column_list) ;
alter table table_name add unique (column_list) ;
主键索引:
alter table table_name add primary key (column_list) ;
删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
周维表维护
insert into dim_week_day3
select table1.dim_week_id,table1.dim_week_desc,table1.start_dim_day,table1.Dim_day_Name as start_day_Desc,table2.day2 as end_dim_day,table2.Dim_day_Name as end_day_Desc
from
(select b1.dim_week_id, b1.dim_week_desc,b1.day1 as start_dim_day,d1.Dim_day_Name
from (select a.dim_week_id,a.dim_week_desc,DATE_FORMAT(DATE_SUB(a.begin_day_id,INTERVAL 3 DAY),'%Y%m%d') as day1
from dim_week_day as a
where left(a.dim_week_id,4) = '2016' )b1
LEFT JOIN dim_day as d1 on d1.dim_day_id=b1.day1
where 1=1)table1
LEFT JOIN
(select b2.dim_week_id, b2.dim_week_desc,b2.day2,d1.Dim_day_Name
from (select a.dim_week_id,a.dim_week_desc,DATE_FORMAT(DATE_SUB(a.end_day_id,INTERVAL 3 DAY),'%Y%m%d') as day2
from dim_week_day as a
where left(a.dim_week_id,4) = '2016' )b2
LEFT JOIN dim_day as d1 on d1.dim_day_id=b2.day2
where 1=1)table2
on table1.dim_week_id= table2.dim_week_id
where 1=1