mysql基本函数总结

左手的ㄟ右手 2022-06-05 23:40 225阅读 0赞

**详细的列举一下**

USE test11;
    
    
    
    select *,DATE_FORMAT(t,'%Y%m') from test01;
    select DATE_FORMAT(t,'%Y%U') weeks,name ,sum(x) as 销售额 from test01 group by weeks;  
    select DATE_FORMAT(t,'%Y%m%d') days ,name ,sum(x) as 销售额 from test01 group by days;  
    select DATE_FORMAT(t,'%Y%m') months,name ,sum(x) as 销售额 from test01 group by months ; 
    
    
    -- 按人按月查询销售额 
    SELECT NAME, SUM(x), DATE_FORMAT(t, '%Y-%M') FROM test01 GROUP BY DATE_FORMAT(t, '%Y-%M');
    
    -- 按人按日查询销售额 
    SELECT NAME, SUM(x), DATE_FORMAT(t, '%Y-%M-%d') FROM test01 GROUP BY DATE_FORMAT(t, '%Y-%M-%d');
    
    SELECT * FROM test01;
    
    -- weelday (date) 返回0 - 6对应星期天到星期一
    SELECT *, WEEKDAY(t) FROM test01 WHERE WEEKDAY(t) >= 5;
    
    -- MONTH (date) 返回月中的日期 1 - 12 
    SELECT *, MONTH (t) FROM test01 WHERE MONTH (t) = 9;
    
    
    -- all查询所有 默认为all;
    SELECT ALL NAME FROM test01;
    
    -- DISTINCT表示除去重复的 
    SELECT DISTINCT NAME FROM test01; ditinct
    
    
    #声明存储过程
    DELIMITER //
      CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM test11.test01;
        END // DELIMITER ;
    
    #调用proc
    CALL myproc(@p_out);
    SELECT @p_out;
    
    
    
    #查看存储过程
    
    #全部数据库
     show procedure status;
    #指定数据库 
     select `name` from mysql.proc where db = 'test11' and `type` = 'PROCEDURE' select DATE_FORMAT('2017-10-19','%y');
    #日期格式查询
    select *,DATE_FORMAT(t,'%Y%m') from test01;
    select DATE_FORMAT(t,'%Y%U') weeks,name ,sum(x) as 销售额 from test01 group by weeks;  
    select DATE_FORMAT(t,'%Y%m%d') days ,name ,sum(x) as 销售额 from test01 group by days;  
    select DATE_FORMAT(t,'%Y%m'); months,name ,sum(x)  as 销售额 from test01 group by months ; 
    #时间差
     SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
    #提取日期
    SELECT DATE('2003-12-31 01:02:03');
    #返回date 对应的工作日名称。
    SELECT DAYNAME('2017-10-19');
    #返回date 对应的该月日期，范围是从 1到31。
    SELECT DAYOFMONTH('1998-02-08');
    #返回date 对应的一年中的天数，范围是从 1到366。
    SELECT DAYOFYEAR('1998-02-03');
    #返回date 对应的月份，范围时从 1 到 12
    SELECT MONTH('1998-02-03');
     #返回date 对应月份的全名
    SELECT MONTHNAME('1998-02-05');
    
    #返回date 对应的一年中的季度值，范围是从 1到 4。 daarter
    SELECT QUARTER('2017-10-19');
    #查询星期
    select week('2017-10-19');
    #查询月份
    select MONTH('2017-10-19');
    #返回一个星期的天数0-6 礼拜天是0--礼拜六6 正常星期加1
    select WEEKDAY('2017-10-19');
    #返回一个星期的天数1-7 礼拜天是1--礼拜六7 正常星期减1
    select DAYOFWEEK('2017-10-21');

**数学函数**

1).求绝对值
    
            例子:select Weight-50,abs(Weight-50),abs(-5.38) from person;
    
        2).求指数
    
            例子:select Weight,power(Weight,-0.5),power(Weight,2),power(Weight,3),power(Weight,4) from person;
    
        3).求平方根
    
            例子:select Weight,sqrt(Weight) from person;
    
        4).求随机数
    
            例子:select rand();
    
        5).舍入到最大整数
    
            例子:select Name,Weight,ceiling(Weight),ceiling(Weight*-1) from person;
    
        6).舍入到最小整数
    
            例子:select Name,Weight,floor(Weight),floor(Weight*-1) from person;
    
        7).四舍五入
    
            round()函数的用法:两个参数的和一个参数的
    
            两个参数:round(m,d),m为待进行四舍五入的数值,而d则为计算精度.d还可以取负值.
            例子:select Name,Weight,round(Weight,1),round(Weight*-1,0),round(Weight,-1) from person;
    
            一个参数:round(m),相当于d=0
            例子:select Name,Weight,round(Weight),round(Weight*-1) from person;
    
        8).求正弦值
    
            例子:select Name,Weight,sin(Weight) from person;
    
        9).求余弦值
    
            例子:select Name,Weight,cos(Weight) from person;
    
        10).求反正弦值
    
            例子:select Name,Weight,asin(1/Weight) from person;
    
        11).求反余弦值
    
            例子:select Name,Weight,acos(1/Weight) from person;
    
        12).求正切值
    
            例子:select Name,Weight,tan(Weight) from person;
    
        13).求反正切值
    
            例子:select Name,Weight,atan(Weight) from person;
    
        14).求X/Y的反正切值
    
            例子:select Name,Weight,atan2(Weight,2) from person;
    
        15).求余切
    
            例子:select Name,Weight,cot(Weight) from person;
    
        16).求圆周率pi
    
            例子:select Name,Weight,Weight*pi(),pi() from person;  
    
        17).弧度制转换为角度制
    
            例子:select Name,Weight,degrees(Weight) from person; 
    
        18).角度制转换为弧度制
    
            例子:select Name,Weight,radians(Weight) from person; 
    
        19).求符号
    
            数值大于0返回1，等于0返回0，小于0返回-1
            例子:select Name,Weight-48.68,sign(Weight-48.68) from person; 
    
        20).求整除余数
    
            例子:select Name,Weight,mod(Weight,5) from person;
    
        21).求自然对数
    
            例子:select Name,Weight,log(Weight) from person;
    
        22).求以10为底的对数
    
            例子:select Name,Weight,log10(Weight) from person;

**字符串函数**

1).计算字符串长度
    
            例子:select Name,length(Name) from person;
    
        2).转换为小写
    
            例子:select Name,lower(Name) from person;
    
        3).转换为大写
    
            例子:select Name,upper(Name) from person;
    
        4).截去字符串左侧空格
    
            例子:select Name,ltrim(Name),ltrim(' abc ') from person;
    
        5).截去字符串左侧空格
    
            例子:select Name,rtrim(Name),rtrim(' abc ') from person;
    
        6).截去字符串两侧空格
    
            例子:select Name,trim(Name),trim(' abc ') from person;
    
        7).取子字符串
    
            第二个参数是截取的起始位置，第三个参数是长度
            例子:select substring('abcdef111',2,3);
                 select Name,substring(Name,2,3) from person;
    
        8).计算子串的位置
    
            例子:select Name,instr(Name,'m'),instr(Name,'ly') from person;
    
        9).从左侧开始取字串
    
            例子:select Name,left(Name,3),left(Name,2) from person;
    
        10).从右侧开始取字串
    
            例子:select Name,right(Name,3),right(Name,2) from person; 
    
        11).字符串替换
    
            第一个参数是字符串，第二个是要被替换的子串，第三个是想要替换的子串.
            这个函数第三个参数如果为空串的话相当于删除操作了.
    
            例子:select Name,replace(Name,'i','e'),IDNumber, replace(IDNumber,'2345','abcd') from person; 
    
        12).得到字符的ASCII码
    
            ASCII()函数用来得到一个字符的ASCII码，有且只有一个参数，如果参数为一个字符串则函数返回第一个字符 的ASCII码
            例子:select ASCII('a'),ASCII('abc');
    
        13).得到ASCII码对应的字符
    
            例子:select char(56),char(90),'a',char(ASCII('a'));
    
        14).发音匹配
    
            soundex函数用于计算一个字符串的发音特征值，这个特征值为一个四个字符的字符串，特征值的第一个字符总是初始字符串中的第一个字符，而其后则是一个三位数字的数值。
            例子:select soundex('jack'),soundex('jeck'),soundex('joke'),soundex('juke'),soundex('look'),soundex('jobe');
    
                 select name,soundex(name) from person;

**日期时间函数**

1).获取当前日期时间
    
            例子:select now(),sysdate(),current_timestamp;
    
        2).获取当前日期
    
            例子:select curdate(),current_date;  
    
        3).获取当前时间
    
            例子:select curtime(),current_time;  
    
        4).日期增减
    
            函数:date_add(date,interval expr type);它的别名是adddate()
    
            例子:select BirthDay,date_add(BirthDay,interval 1 week) as w1,date_add(BirthDay,interval 2 month) as m2,date_add(BirthDay,interval 5 quarter) as q5 from person;
    
                 select BirthDay,date_add(BirthDay,interval '3 2:10' DAY_MINUTE) as dm,date_add(BirthDay,interval 1-6 YEAR_MONTH) as ym from person;
    
        5).计算日期差
    
            例子:select RegDay,BirthDay,datediff(RegDay,BirthDay),datediff(BirthDay,RegDay) from person;
    
        6).计算一个日期是星期几
    
            例子:select BirthDay,dayname(BirthDay),RegDay,dayname(RegDay) from person;
    
        7).取得日期的指定部分
    
            函数:date_format(date,format);
    
            例子:select BirthDay,date_format(BirthDay,'%y-%M %D %W') as bd,RegDay,date_format(RegDay,'%Y年%m月%e日') as rd from person;
    
        8).类型转换
    
            函数:cast(expression as type)
                 convert(expression,type)
    
            例子:select cast('-30' as signed) as sig,convert('36',unsigned integer) as usig, cast('2008-08-08' as date) as d,convert('08:09:10',time) as t;
    
        9).空值处理
    
            例子:select name,birthday,regday,coalesce(birthday,regday,'2008-08-08') as ImoortDay from person;