烦烦的SQL学习笔记（4）---单行函数

目录

引言

1、字符函数

（1）length（2）concat（3）upper、lower（4）substr （5）instr（6）trim

（7）lpad（8）rpad（9）replace

2、数学函数

（1）round（2）ceil（3）floor（4）truncate（5）mod

3、日期函数

(1) now (2) curdate (3) curtime (4) year, month, date, hour , minnute, second

(5) str_to_date(6）date_format

 4、其他函数

5. 流程控制函数

（1）if

（2）case函数的使用一：java中switch case的效果

（3）case函数的使用二：类似于java中的多重if

6. 例题

引言：

一、功能：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
二、好处：1、隐藏了实现细节 2、提高代码的重要性
三、调用：select 函数名（实参列表） 【from 表】；
四、特点：1、叫什么（函数名）
                  2、干什么（函数功能）
五、分类：1、单行函数
         如concat、length、ifnull等
      2、分组函数
         功能：做统计、做聚合。。。

1、字符函数

（1）length

#1、length:获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('xuganlinhaha')

（2）concat

#2、concat：拼接字符串
SELECT CONCAT(last_name,'_',first_name)
FROM employees;

（3）upper、lower

#3、upper、lower: 变成大写小写
SELECT UPPER('john');
SELECT LOWER('john');
#示例：将姓变成大写、名变成小写，然后拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名
FROM employees;

（4）substr 

#4、substr：substring：
#注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('黄烦烦爱上了喻文州',6) AS output;

#截取从指定索引处指定长度的字符
SELECT SUBSTR('黄烦烦爱上了喻文州',6,2) AS output;

#案例：姓名总首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS output
FROM employees;

（5）instr

#5、instr：返回子串第一次出现的索引，如果找不到就返回0
SELECT INSTR('黄烦烦喜欢叶不羞','不') AS output;
#结果是：【7】

（6）trim

#6、trim: 去掉【前后的】空格和 选中字符，不会管中间的哦！
SELECT  (TRIM('      黄   少天     ')) AS output;
#结果是：【黄    少天】

SELECT TRIM('a' FROM 'aaaaaaa黄aaa少aaa天aaaaa') AS output;
#结果是【黄aaa少aaa天】

（7）lpad

SELECT LPAD('黄少天',6,'x') AS output;
#例如上面的结果就是【xxx黄少天】

SELECT LPAD('黄少天',2,'x') AS output;
#结果是：【黄少】

（8）rpad

#8、rpad：用指定的字符实现右填充指定长度
SELECT RPAD('黄少天',6,'x') AS output;
#结果是：【黄少天xxx】

（9）replace

#9、replace：替换
SELECT REPLACE('黄烦烦黄烦烦黄烦烦黄烦烦喜欢喻文州','黄烦烦','叶修') AS output;

2、数学函数

（1）round

#1、round：四舍五入
SELECT ROUND(1.45);
#【1】
SELECT ROUND(-1.6); 
#【-2】
SELECT ROUND(1.5695,2);
#【1.57】后面的“2”表示保留小数点后几位

（2）ceil

#2、ceil: 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.59);
#【2】
SELECT CEIL(1.00);
#【1】

（3）floor

#3、floor：向下取整，返回<=该参数的最大整数
SELECT FLOOR(2.0005);
#【2】
SELECT FLOOR(-1.59);
#【-2】

（4）truncate

#4、truncate:截断
SELECT TRUNCATE(1.65,1);
#【1.6】
SELECT TRUNCATE(1.6566666,1);
#【1.6】

（5）mod

#5、mod:取余 做除法，取余数：
SELECT MOD(10,3);
#【1】
SELECT MOD(-5,2);
#【-1】

3、日期函数

#3、curtime：返回当前时间，不包含日期；
SELECT CURTIME();

(1) now

#1、now 返回当前系统日期+时间
SELECT NOW();

(2) curdate

#2、curdate：返回当前系统日期,不包含时间
SELECT CURDATE();

(3) curtime

#3、curtime：返回当前时间，不包含日期；
SELECT CURTIME();

(4) year, month, date, hour , minnute, second

#4、可以获取指定的部分：年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS nian;
SELECT YEAR('1999-1-1') AS nian;
#【1999】

SELECT YEAR(`hiredate`) AS 入职年份
FROM `employees`；

SELECT MONTH(NOW()) AS nian;

(5) str_to_date

#5、str_to_date:将日期格式的字符转化成指定格式的日期
SELECT STR_TO_DATE('1998-2-25','%Y-%c-%d') AS output;

#案例：查询入职日期为1992-4-3的员工信息
SELECT * FROM`employees`
WHERE`hiredate`='1992-4-3';

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

(6）date_format

#6、date_format:将日期转化成字符
#date_format('2018/6/6,'%Y年%m月%d日')  结果是2018年06月06日

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');
#结果是：【22年02月26日】

#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name, DATE_FORMAT(hiredate,'%m月/%d日 %y年')
FROM`employees`
WHERE `commission_pct` IS NOT NULL;

 4、其他函数

#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

5. 流程控制函数

（1）if

#五、流程控制函数
#1、if函数：if else的效果

SELECT IF(10>5,'大','小');

SELECT last_name,IF(`commission_pct`IS NULL,'没奖金','牛') AS 奖金有无
FROM `employees`
ORDER BY `commission_pct`;

（2）case函数的使用一：java中switch case的效果

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 AS 原工资,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`;

（3）case函数的使用二：类似于java中的多重if

case 
when 条件1 then 要显示的值1或 语句1（语句后面要加；)
when 条件2 then 要显示的值2或 语句2
...
else 要显示的值n 或语句n
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`;

6. 例题

#1、显示系统时间（注：日期+时间）
SELECT NOW();

#2、查询员工号、姓名、工资，以及工资提高百分之20后的结果（new salary）
SELECT `job_id`,`last_name`,`salary`,`salary`*1.2 AS "new salary"
FROM `employees`;

#3、将员工的姓名按照首字母排序，并且写出姓名的长度
SELECT `last_name`, SUBSTR(`last_name`,1,1) AS 首字母, LENGTH(`last_name`)
FROM `employees`
ORDER BY SUBSTR(`last_name`,1,1);

#4、做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3>
#dream salary

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

#5、使用case函数，按照下面的条件：
SELECT `job_id` AS job,
CASE `job_id`
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE 'D'
END AS 等级
FROM `employees`
ORDER BY 等级;