SQL学习笔记整理6（常见函数）

#常见函数
#单行函数：concat,length,ifnull

#一、字符函数
#看字符集
SHOW VARIABLES LIKE '%char%';
USE myemployees;
#1.concat:拼接字符
SELECT CONCAT(last_name,'_',first_name) FROM employees;
#2.upper,lower:大小写
SELECT UPPER('join');
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;

#3.substr,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)),
    '_',
    UPPER(SUBSTR(first_name, 1, 1)),
    LOWER(SUBSTR(first_name, 2))
  ) 
FROM
  employees ;
  
#4.instr：返回第二个字符串在第一个字符串第一次出现的索引
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') out_put;

#5.trim:去掉字符串前后的空格
SELECT TRIM('     张翠山     ') AS out_put;
#可以指定去掉前后的字符
SELECT TRIM('a' FROM 'aaaaa张aaaa翠山aaaaa') AS out_put;

#6.lPad
#左填充‘*’使字符串长度达到10
SELECT LPAD('殷素素',10,'*') out_put;
#rpad
SELECT RPAD('殷素素',10,'*') out_put;

#7.replace
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') OUT_PUT;

#练习1:获取邮箱@前面的字符：比如john@126.com
SELECT SUBSTR('john@126.com',1,INSTR('john@126.com','@')-1) 用户名;

#二、数学函数

#round:四舍五入
SELECT ROUND(1.65) out_put;
#四舍五入：保留小数点后两位
SELECT ROUND(2.773,2) out_put;

#ceil:向上取整
SELECT CEIL(1.002) out_put;

#floor：向下取整
SELECT FLOOR(1.98) out_put;

#truncate：截断
#截取小数点后一位
SELECT TRUNCATE(1.69,1) out_put;

#mod:取余
SELECT MOD(10,9) out_put;

#三、日期函数
#now():返回系统日期+时间
SELECT NOW();
#curdate:返回系统日期
SELECT CURDATE();
#curtime
SELECT CURTIME();

SELECT YEAR(NOW());
SELECT YEAR('1995-6-6');
SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

#str_to_date:将字符通过指定的格式转化为日期
SELECT STR_TO_DATE('1997-9-1','%Y-%c-%d') out_put;

SELECT * FROM employees a WHERE a.`hiredate` = '1992-4-3';
SELECT * FROM employees a WHERE a.`hiredate` = STR_TO_DATE('3-3 1998','%c-%d %Y');

#date_format
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') out_put;
SELECT last_name,DATE_FORMAT(hiredate,'%c月/%d日 %Y年') 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

#四、其他函数
#版本号
SELECT VERSION();
#当前库
SELECT DATABASE();
#当前用户
SELECT USER();

#流程控制函数
#if函数
SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,'有','没有') 奖金  FROM employees;

#case函数:
/*case 要判断的字段或表达式
  when 常量1 then 要显示的值1或语句1
  when 常量2 then 要显示的值2或语句2
  ......
  else 要显示的值或语句
  end
*/
#案例：查询员工的工资
/*
部门编号=30，显示的工资为1.1倍
部门编号=40，显示的工资为1.2倍
部门编号=50，显示的工资为1.3倍
其他部门，显示工资为原来的工资
*/
SELECT 
  department_id,
  salary 原始工资,
  CASE
    department_id 
    WHEN 30 
    THEN salary * 1.1 
    WHEN 40 
    THEN salary * 1.2 
    WHEN 50 
    THEN salary * 1.3 
    ELSE salary 
  END AS 新工资 
FROM
  employees ;
  
#case函数的使用二
/*
case
when 条件一 then 要显示的值1或语句1
when 条件二 then 要显示的值2或语句2
....
else 要显示的值或语句
end
*/
#案例
SELECT 
  salary,
  CASE
    WHEN salary > 20000 
    THEN 'A' 
    WHEN salary > 15000 
    THEN 'B' 
    WHEN salary > 10000 
    THEN 'C' 
    ELSE 'D' 
  END AS 工资级别 
FROM
  employees ;