Mysql基础学习(二)-常见函数之单行函数

Mysql基础学习(二)-常见函数之单行函数

本章用到sql

链接: https://pan.baidu.com/s/1U6ntr8fTdOO0SYRxJWQqVA 提取码: 9yji 复制这段内容后打开百度网盘手机App，操作更方便哦

常见函数介绍

单行函数

字符函数

length 获取参数值的字节个数

SELECT LENGTH('john');

concat 拼接字符串

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

upper、lower 转换大小写

SELECT UPPER('john');
SELECT LOWER('joHn');
#将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

substr 截取字符串

SELECT SUBSTR('李莫愁爱上了陆展元',7)#输出陆展元,索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',1,3)#输出李莫愁,索引从1开始，截取1到3的字符串
#姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) FROM employees;

instr 返回字符串2在字符串1中第一次出现的起始索引

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS OUTPUT

trim 去前后空格

SELECT TRIM('    张翠山    ') AS OUT_PUT
#去除前后的a，指定字符
SELECT TRIM('a' FROM 'aaaaa张aaa翠山aaaa') AS OUT_PUT

lpad 用指定的字符实现左填充指定长度，长度不够从右边删除

SELECT LPAD('殷素素',10,'*') AS OUT_PUT

rpad 同上，变成右填充，长度不够从右边删除

replace 替换指定字符为指定字符，全部替换

SELECT REPLACE('张无忌爱上了周芷若周芷若周芷若周芷若','周芷若','张敏') AS out_put

数学函数

round 绝对值四舍五入

SELECT ROUND(1.65);
#保留两位小数
SELECT ROUND(1.635,2);

ceil 向上取整，返回>=该参数的最小整数

SELECT CEIL(1.01);

**floor **向下取整，返回<=该参数的最大整数

SELECT FLOOR(9.99);

truncate 截断 小数点后保留N位数

SELECT TRUNCATE(1.69,1);#输出1.6

mod 取余

SELECT MOD(10,3);#a-a/b*b

日期函数

now 返回当前系统日期+时间

SELECT NOW()

curdate 返回当前系统日期，不包含时间

SELECT CURDATE()

curtime 返回当前时间，不包含日期

SELECT CURTTIME()

获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW());#获取当前年份
SELECT MONTH(NOW());#获取当前月份，数字
SELECT MONTHNAME(NOW());获取当前月份英文
#...依此类推

str_to_date 将日期格式的字符转换为指定的格式的日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS OUT_PUT

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

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

date_format 将日期转换成字符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BsATIETv-1606474276959)(C:\Users\Skaura\AppData\Roaming\Typora\typora-user-images\image-20201124213106595.png)]

符号如上

SELECT DATE_FORMAT(NOW(),‘%y年%m月%d日') AS OUT_PUT

查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT 
	last_name,DATEFORMAT(hiredate,'%m月/%d日 %Y年')
FROM
	employees
WHERE
	commission_pct iS NOT NULL

其他函数

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

流程控制函数

if if else的效果，类似三目

SELECT IF(10>5,'大','小')
SELECT last_name,commission_pct,IF(commission_pct iS NULL,'没奖金','有奖金')
FROM employees;

case 控制结构

• 使用一：switch case的效果

语法

#case 要判断的字段或表达式
#when 常量1 then 要显示的值1或语句1;
#...
#else （默认值）要显示的值n或语句n
#end

案例：查询员工的工资，要求

​ 部门号=30，显示的工资为1.1倍

​ 部门号=40，显示的工资为1.2倍

​ 其他部门，显示的工资为原工资

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 要显示的值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;

案例

显示系统时间（注：日期+时间）

SELECT NOW();

查询员工号，姓名，工资，以及工资提高百分之20后的结果

SELECT employee_id,last_name,salary,salary*1.2 AS 提高后
FROM employees;

将员工的姓名按首字母排序，并写出姓名的长度（LENGTH）

SELECT last_name,SUBSTR(last_name,1,1) AS 首字符,LENGTH(last_name) AS LENGTH
FROM employees
ORDER BY 首字符

做一个查询，产生下面的结果

<last_name> earns monthly but wants <salary*3>

Dream Salary

King earns 24000 monthly but wangts 72000

SELECT CONCAT(last_name,' earns',salary,' monthly but wants ',salary*3) AS "Dream Salary"
FROM employees
LIMIT 1;

使用case-when，按照下面的条件：

job grade

AD_PRES A

ST_MAN B

IT_PROG C

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 grade
FROM employees;
#TIP：字符串都需要添加单引号