数据库_04_常见函数-单行函数

#进阶4：常见函数-单行函数

语法：select 函数名（实参列表）【from 表】

特点：
1.叫什么（函数名）
2.做什么（功能）

分类：
1.单行函数
如concat,length,ifnull…
2.分组函数

功能：做统计使用，又称统计函数，聚合函数，组函数

#-------------------------------

#一，字符函数

#1.length 获取参数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

#2.concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper,lower

SELECT UPPER('young');

#案例：将姓变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名 FROM employees;

#4.substr,substring 索引从1开始
#截取从指定索引处后面所有字符

SELECT SUBSTR('港港爱霜霜',3) 结果

#截取从指定索引处指定字符长度的字符

SELECT SUBSTR('港港爱霜霜',1,2) 结果

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

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

#5.instr 返回字串第一次出现的索引，如果找不到返回0

SELECT INSTR('港港今年想上岸上海理工','上海理工') AS out_put;

#6.trim

SELECT LENGTH(TRIM('    上海理工  ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaa上海aaaa理工aaaaa') AS out_put;

#7.lpad 用指定的字符实现左填充指定长度
#同理，rpad为右填充

SELECT LPAD('浙江大学',6,'*') AS out_put;

#8.replace 替换

SELECT REPLACE('港港今年北语考北语','北语','上海理工') AS out_put;

#二，数学函数
#round 四舍五入

SELECT ROUND(-1.55);
SELECT ROUND(1.239,2);

#ceil 向上取整,返回大于等于该参数的最小整数
#同理 floor 向下取整

SELECT CEIL(1.002);

#truncate 截断

SELECT TRUNCATE(1.6999,1);

#mod取余

SELECT MOD(10,3);
SELECT 10%3;

#三，日期函数
#now 当前日期+时间

SELECT NOW();

#curdate 返回当前日期，不包括时间

SELECT CURDATE();

#同理，CURTIME 返回当前时间，不包括日期

#可以获取指定的部分，年，月，日，小时，分钟，秒

SELECT YEAR(hiredate) FROM employees;

#同理：month,day…

#str_to_date 将字符通过指定格式转换成日期

SELECT STR_TO_DATE('7-12-2018','%c-%d-%Y');

#案例：查询入职日期为1992-4-3的员工信息

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

#date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');

#案例：查询有奖金的员工名和入职日期（xx月/xx日 xx年）

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 
FROM employees 
WHERE commission_pct IS NOT NULL;

#四，其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

#五，流程控制函数
#1.if

SELECT last_name,commission_pct,IF(commission_pct,'有奖金','无奖金') 
FROM employees;

#2.case
#使用一： switch case的效果
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
…
else 要显示的值或语句
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 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees；

#使用二：类似于 多重if
语法：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值或语句
end

案例：查询员工的工资情况
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>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;

#测试

SELECT employee_id,last_name,salary,salary*1.2 'now salary' FROM employees;

SELECT last_name,LENGTH(last_name) 姓名长度,SUBSTR(last_name,1,1) 首字符 
FROM employees 
ORDER BY 首字符;

SELECT 
CONCAT(last_name,' ','earns',' ',salary,' ','but',' ','wants',' ',salary*3) 
AS 'dream salary'
FROM employees;

SELECT job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE 'D'
END AS GRADE
FROM employees;