MySQL 进阶4 SQL常见函数: 字符函数/数学函数/日期函数/流程控制函数(if/case)

# 进阶4 SQL常见函数

分类:
    1/单行函数: 
　　　　字符函数:
　　　　　　　　concat(),length(),ifnull(__,default) ,instr(),
　　　　　　　　trim(),upper(),lower(),lpad(),rpad(),replace()
　　　　数学函数:
　　　　　　　　round,ceil,floor,truncate,mod;
　　　　日期函数:
　　　　　　　　now,curdate,curtime, year,month,monthname,day,hour,
　　　　　　　　minute,second,str_to_date, date_format
　　　　其他函数:
　　　　　　　　

2/多行函数: 做统计用

/*
  概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外仅暴露方法名
  好处:1/隐藏了实现的细节
    2.提高了代码的重用性
  调用: select 函数名(实参列表) [from 表];
  分类:
    1/单行函数: concat(),length(),ifnull(__,default)
    2/多行函数: 做统计用
      
*/
#单行函数: 
#1.字符函数
 #length
 SELECT LENGTH('六六');  #结果: 6
 SELECT LENGTH('123');    #结果: 3
 #显示字符集    
 SHOW VARIABLES LIKE '%char%'     ;
 
#2.concat()

#3.upper()/lower()  大写函数/小写函数
SELECT UPPER('asdd');
    #实例:将姓变为大写,名变为小写,然后拼接
SELECT CONCAT(UPPER(last_name),'  ', LOWER(first_name)) 姓名
FROM employees;

#4 .substr(str,pos, len) / substring
    #注意索引从1开始
SELECT    SUBSTR('李莫愁爱上了陆展元',7) out_put;

SELECT    SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

#案例: 姓名中首字母字符大写,其他字符小写,然后用_拼接 ,显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)) ,'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;

#5.instr(s1,s2 ) 返回子串s2在母串中出现的第一次索引(从1开始),找不到返回0
SELECT INSTR('从前的那个从其那','从前') out_put;

#6. trim   #python的用法一致
SELECT TRIM('     张三  ') AS out_put;
SELECT TRIM('a' FROM 'aaaaa张aaaaaaa三aaaaaaaaaaaaaaaa') AS out_put;

#7.lpad   用指定字符实现左填充直指定长度
SELECT LPAD('殷素素',10,'*') AS out_put;  #*******殷素素
SELECT LPAD('殷素素',2,'*') AS out_put;    #殷素

#8.rpad   用指定字符实现右填充直指定长度
SELECT RPAD('殷素素',10,'*') AS out_put;  #殷素素*******
SELECT RPAD('殷素素',2,'*') AS out_put;    #殷素

#9.replace 全部替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS put;   #张无忌爱上了赵敏


########数学函数  (第二个参数表示保留小数点后位数)

#1.round 四舍五入 ,第二个参数表示保留小数点后位数
SELECT ROUND(1.65);  #2
SELECT ROUND(1.35);  #1
SELECT ROUND(1.567,2);  #1.57

#2.ceil 绝对向上取整 ,返回>=该参数的最小整数
SELECT CEIL(1.0);    #1
SELECT CEIL(1.0001); #2
SELECT CEIL(-1.02);  #1

#3.floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);  #-10
SELECT FLOOR(9.99);   #9

#4.truncate 截断取整
SELECT TRUNCATE(1.699999,1) ;  #1.6

#5.mod取余
/*
mod(a,b) : a-a/b*b即为取余结果
*/
SELECT MOD(-10,-3);  #-1

#三.日期函数:
#1.now  返回系统当前日期+时间
SELECT NOW();   #2018-10-23 17:18:08

#2.curdate   #返回当前的日期
SELECT CURDATE();  #2018-10-23

#3.curtime   返回当前的时分秒
SELECT CURTIME();  #17:17:47

#4.可以获取指定的时间部分: 年(YEAR)/月(MONTH)/日(day)/小时(hour)/分钟(minute)/秒(second)
SELECT YEAR(NOW());         #2018
SELECT YEAR('2008-10-10');  #2008

#5.str_to_date : 将字符串转换成标准格式的日期
        #标准匹配符: 年(%Y.%y)/月(%m)/日(%d)/小时(%H.%h)/分钟(%i)/秒(%s)
SELECT STR_TO_DATE('9-13-1999','%c-%d-%Y');  #1999-09-13

#6.DATE_format :将日期转换成标准格式的字符串
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');  #2018年06月06日

     #案例.查询入职日期为 1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');

     #案例:查询有奖金的员工名和入职时间(xx月/xx日 xx年)
SELECT CONCAT(last_name," ",first_name),DATE_FORMAT(hiredate,'%m月/%d日 %Y年')
FROM employees
WHERE commission_pct IS NOT NULL;

#四 .其他函数
SELECT VERSION();   #8.0.12
SELECT DATABASE();   #myemployees
SELECT USER();    #root@localhost

#五. 流程控制函数

#1.if函数 : if else 的效果
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻嘻') 备注
FROM employees;

#2.case 函数/结构 的使用1: switch case的效果
/*
   mysql 中:
 { case 要判断的字段或者表达式
   when 常量1 then 要显示的值1或语句1;
   when 常量2 then 要显示的值2或语句2;
   ...   
   else 要显示的值n或者语句n;
   end }
*/
   #举个栗子
   #部门号为30,显示工资为1.1倍;
   #部门号为40,显示工资为1.2倍;
   #部门号为50,显示工资为1.3倍;
   #其他部分不变!
SELECT salary 原工资 ,department_id,

CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 40 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

#2.case 函数/结构 的使用二: 类似于多重if 语句
/*
 mysql 中:
 { case  
   when 常量1 then 要显示的值1或语句1;
   when 常量2 then 要显示的值2或语句2;
   ...   
   else 要显示的值n或者语句n;
   end }
*/
 #举个栗子
   #工资>20000, 显示A等级
   #工资>15000, 显示A等级
   #工资>10000, 显示C等级
   #其他,显示D等级
SELECT salary 原工资 ,

CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;