[关闭]
@StarSky 2016-02-23T11:15:12.000000Z 字数 2147 阅读 920

MySQL语法

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
可用order by 1或order by 1 asc/desc 表示按第一个字段进行(升序、降序)排序
字符串处理
  1. left()/ right()

数据操作

插入

  1. INSERT INTO table_name (列1, 2,...) VALUES (值1, 2,....)

更新

  1. UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

删除

  1. DELETE FROM 表名称 WHERE 列名称 =
truncate table dongfang_hk    //清除所有数据,主键从1开始
delete from dongfang_hk       //删除所有数据,主键继续增长
复制
SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

表结构操作

添加列

  1. ALTER TABLE table_name
  2. ADD column_name datatype

删除列

  1. ALTER TABLE table_name
  2. DROP COLUMN column_name

索引

一般索引

  1. create index index_name on table_name (column_list) ;
  2. alter table table_name add index index_name (column_list) ;

唯一索引

  1. create unique index index_name on table_name (column_list) ;
  2. alter table table_name add unique (column_list) ;

主键索引:

  1. alter table table_name add primary key (column_list) ;

删除索引

  1. drop index index_name on table_name ;
  2. alter table table_name drop index index_name ;
  3. alter table table_name drop primary key ;

备份

周维表维护

  1. insert into dim_week_day3
  2. 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
  3. from
  4. (select b1.dim_week_id, b1.dim_week_desc,b1.day1 as start_dim_day,d1.Dim_day_Name
  5. 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
  6. from dim_week_day as a
  7. where left(a.dim_week_id,4) = '2016' )b1
  8. LEFT JOIN dim_day as d1 on d1.dim_day_id=b1.day1
  9. where 1=1)table1
  10. LEFT JOIN
  11. (select b2.dim_week_id, b2.dim_week_desc,b2.day2,d1.Dim_day_Name
  12. 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
  13. from dim_week_day as a
  14. where left(a.dim_week_id,4) = '2016' )b2
  15. LEFT JOIN dim_day as d1 on d1.dim_day_id=b2.day2
  16. where 1=1)table2
  17. on table1.dim_week_id= table2.dim_week_id
  18. where 1=1
参考资料
[1]SQL 教程

[1] 此处四舍五入
[2] FORMAT函数用于对字段的显示进行格式化,例程提供的是数字以千分位形式显示的特殊表示。n为小数位数。
[3] group_concat([DISTINCT]要连接的字段[Order BY ASC/DESC 排序字段][Separator '分隔符'])
添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注