MySql待整理

#ELT(n,str1,str2,str3,...) ：如果n=1，则返回str1,如果n=2，则返回str2,依次类推。如果n小于1或大于参数个数，返回NULL。ELT()是FIELD()的功能补充函数。
select name,ELT(2,"地球","太阳","我") from `user` 
# result 张飞，太阳
----------------------------------------
#FIELD(str,str1,str2,str,str3,str4...):返回str 在后面的参数列（str1,str2,str,str3,str4...）中的索引，起始索引为1。如果未在参数列中发现str 则返回0
select FIELD(`name`,id,`name`,age,phone) from `user` WHERE name="张飞"
#result 1
----------------------------------------
#IFNULL(expr1,expr2)：如果expr1是NULL,则返回expr2,如果expr1不是NULL,则返回expr1。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。
select IFNULL(reserve,"空的") from user  WHERE name="张飞"
#result 空的
----------------------------------------
#INTERVAL(N,N1,N2,N3,...) 小于后面的某个参数，就返回这个参数的前一个位置数字
SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
#result 3 (23小于30)
-----------------------------------------
#IF(expr1,expr2,expr3) 如果 expr1 为真(expr1 <> 0 以及 expr1 <> NULL)，那么 IF() 返回 expr2，否则返回expr3。IF()返回一个数字或字符
SELECT if(sex,'man','woman') sex from user
-----------------------------------------
#订单记录中存储了该订单的来源类型编码，现需将其来源名称在页面列表中展示，来源信息并未单独建表存储，现需将查询语句做出修改以达到目的
SELECT *,IFNULL(ELT(FIELD(type,'1','2','3','4'),'来源A','来源B','来源C','来源D'),'未知') name from order;

-----------------------------------------------------------------------
#流程控制函数case
CASE expression
WHEN value1 THEN returnvalue1
WHEN value2 THEN returnvalue2
WHEN value3 THEN returnvalue3
……
ELSE defaultreturnvalue
END

    SELECT name
        ,
        ( CASE sex WHEN 0 THEN '女' ELSE '男' END ) sex 
    FROM
        `user`        
------------------------------
CASE
WHEN condition1 THEN returnvalue1
WHEN condition 2 THEN returnvalue2
WHEN condition 3 THEN returnvalue3
……
ELSE defaultreturnvalue
END

    SELECT NAME
        ,
        ( CASE WHEN sex = 0 THEN '女' ELSE '男' END ) sex 
    FROM
        `user`
------------------------------
#mysql时间数据查询
-- 今天
select * from product where to_days(date)=to_days(now())
-- 昨天到今天
select*from product where to_days(now())-to_days(date)<=1
-- 7天
SELECT * FROM product where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(date)
-- 近30天
SELECT * FROM product where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(date)
-- 本月
SELECT * FROM product WHERE DATE_FORMAT( date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
-- 上一月
SELECT * FROM product WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( date, '%Y%m' ) ) =1
-- 本季度
select * from product where QUARTER(date)=QUARTER(now());
-- 上季度
select * from product where QUARTER(date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
-- 本年
select * from product where YEAR(date)=YEAR(NOW());
-- 上年
select * from product where year(date)=year(date_sub(now(),interval 1 year));

//----------------------------------------------
#mysql 函数
replace(str,from_str,to_str)   
用字符串to_str替换字符串str中的子串from_str并返回  
select REPLACE('duaxaijun','x','i');
-- duanaijun

repeat(str,count)   
返回由count个字符串str连成的一个字符串(任何参数为null时
SELECT REPEAT('love',3);
-- lovelovelove

reverse(str)   
颠倒字符串str的字符顺序并返回 
SELECT REVERSE('过来倒')

lcase(str)  
lower(str)   
返回小写的字符串str 
select lcase('FUCK'); -- fuck 
select lower('YOU');  -- you

ucase(str)   
upper(str)   
返回大写的字符串str  

load_file(file_name)   
读入文件并且作为一个字符串返回文件内容

 update table_name set blob_column=load_file
("/tmp/picture") where id=1;  
//----------------------------------------------
#数学函数
abs(n)  
返回n的绝对值

sign(n)  
返回参数的符号(为-1、0或1)  

mod(n,m)    
取模运算,返回n被m除的余数(同%操作符)   
floor(n)  
返回不大于n的最大整数值

ceiling(n)  
返回不小于n的最小整数值 

round(n,d)  
返回n的四舍五入值,保留d位小数(d的默认值为0)  

exp(n)  
返回值e的n次方(自然对数的底)  
select exp(1)

pow(x,y)    
power(x,y)    
返回值x的y次幂  
SELECT pow(10,2);
-- 100

sqrt(n)  
　返回非负数n的平方根 

truncate(n,d)    
保留数字n的d位小数并返回 

least(x,y,...) 
返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)

greatest(x,y,...)    
返回最大值(其余同least()) 

//-------------------------------------------
# 日期时间函数
dayofweek(date)
返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)  
SELECT DAYOFWEEK(date) from product WHERE id = 1 

weekday(date)    
返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。

dayofmonth(date)    
返回date是一月中的第几日(在1到31范围内)

dayofyear(date)    
返回date是一年中的第几日(在1到366范围内) 
dayname(date)    
返回date是星期几(按英文名返回) 

monthname(date)    
返回date是几月(按英文名返回)  
mysql> select monthname("1998-02-05");    
　　-> 'february'    
 
quarter(date)    
返回date是一年的第几个季度    
mysql> select quarter('98-04-01');    
　　-> 2    
 
week(date,first)   
返回date是一年的第几周(first默认值0,first取值1表示周一是
周的开始,0从周日开始)  
mysql> select week('1998-02-20');    
　　-> 7    
mysql> select week('1998-02-20',0);    
　　-> 7    
mysql> select week('1998-02-20',1);    
　　-> 8    
 
year(date)    
返回date的年份(范围在1000到9999)    
mysql> select year('98-02-03');    
　　-> 1998    
 
hour(time)    
返回time的小时数(范围是0到23)   
mysql> select hour('10:05:03');    
　　-> 10    
 
minute(time)    
返回time的分钟数(范围是0到59)    
mysql> select minute('98-02-03 10:05:03');    
　　-> 5    
 
second(time)    
返回time的秒数(范围是0到59)   
mysql> select second('10:05:03');    
　　-> 3    
 
period_add(p,n)    
增加n个月到时期p并返回(p的格式yymm或yyyymm)  

period_diff(p1,p2)    
返回在时期p1和p2之间月数(p1和p2的格式yymm或yyyymm)  

to_days(date)    
返回日期date是西元0年至今多少天(不计算1582年以前)  
mysql> select to_days(950501);    
　　-> 728779    
mysql> select to_days('1997-10-07');    
　　-> 729669    
 
from_days(n)    
　给出西元0年至今多少天返回date值(不计算1582年以前)   

date_format(date,format) 
mysql> select date_format('1997-10-04 22:23:00','%h:%i:%
s');  

time_format(time,format)  
　和date_format()类似,但time_format只处理小时、分钟和秒(其
余符号产生一个null值或0) 
curdate()     
current_date()  
　以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值(根据返回值所
处上下文是字符串或数字)    
mysql> select curdate();    
　　-> '1997-12-15'    
mysql> select curdate() + 0;    
　　-> 19971215    
 
curtime()    
current_time()  
　以'hh:mm:ss'或hhmmss格式返回当前时间值(根据返回值所处上
下文是字符串或数字)      
mysql> select curtime();    
　　-> '23:50:26'    
mysql> select curtime() + 0;    
　　-> 235026

now()    
sysdate()    
current_timestamp()  
　以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期
时间(根据返回值所处上下文是字符串或数字)     
mysql> select now();    
　　-> '1997-12-15 23:50:26'    
mysql> select now() + 0;    
　　-> 19971215235026    
 
unix_timestamp()    
unix_timestamp(date)    
返回一个unix时间戳(从'1970-01-01 00:00:00'gmt开始的秒
数,date默认值为当前时间)  
mysql> select unix_timestamp();    
　　-> 882226357    
mysql> select unix_timestamp('1997-10-04 22:23:00');    
　　-> 875996580    
 
from_unixtime(unix_timestamp)    
以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的
值(根据返回值所处上下文是字符串或数字)     
mysql> select from_unixtime(875996580);    
　　-> '1997-10-04 22:23:00'    
mysql> select from_unixtime(875996580) + 0;    
　　-> 19971004222300 

sec_to_time(seconds)    
以'hh:mm:ss'或hhmmss格式返回秒数转成的time值(根据返回值所处上下文是字符串或数字)     
mysql> select sec_to_time(2378);    
　　-> '00:39:38'    
mysql> select sec_to_time(2378) + 0;    
　　-> 3938    
 
time_to_sec(time)    
返回time值有多少秒    
mysql> select time_to_sec('22:23:00');    
　　-> 80580    
mysql> select time_to_sec('00:39:38');    
　　-> 2378 

//--------------------------------------
转换函数
cast
用法：cast(字段 as 数据类型) [当然是否可以成功转换，还要看数据类型强制转化时注意的问题]
实例：select cast(a as unsigned) as b from cardserver where order by b desc;
convert：
用法：convert(字段,数据类型)
实例：select convert(a ,unsigned) as b from cardserver where order by b desc;

#增加字段

ALTER TABLE product add COLUMN price BIGINT not NULL DEFAULT 1.00 COMMENT '价格' 
AFTER title
#删除字段
ALTER TABLE product DROP price 
#更改表名
ALTER TABLE product rename to products
#修改字段
ALTER TABLE product MODIFY COLUMN price BIGINT COMMENT 'jiage'
#删除表
DROP TABLE IF product1 EXISTS
#修改字段
ALTER TABLE product CHANGE price money BIGINT NOT NULL;