mysql第4天之常见函数

目录

一、字符函数

二、数学函数

三、日期函数

四、其他函数

五、流程控制函数

---

一、字符函数

（一）、length：获取参数的字节个数

        注意：utf8编码中一个英文字符占一个字符，一个汉语占3个字符

        select length（'john'）；输出结果为4

（二）、concat：拼接函数（将函数体中多个元素拼接一起输出）

        select concat(last_name,'_',first_name) 姓名 from  employees；

案例一、姓中首字母大写，其他字母小写然后用‘_’符号拼接起来

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

（三）、upper（全大写）、lower（全小写）

        select upper('joHN')    ;   输出结果  JOHN

        select  lower('joHN')    ;   输出结果john

（四）、substr：截取字符串函数

        注意：索引从1开始

        1.截取从指定索引往后的字符（不包括索引）

        select  substr('天不生我李淳罡，剑道万古如长夜！',8);输出结果：剑道万古如长夜！

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

        SELECT  SUBSTR('天不生我李淳罡，剑道万古如长夜！',1,7);  #输出结果：天不生我李淳罡

 （五）、instr：返回子串在字符串中第一次出现的位置，不存在则返回0

        SELECT INSTR('天不生我李淳罡，剑道万古如长夜！李淳罡~','李淳罡')位置; #输出结果5

（六）、trim：去除指定字符，默认去除空格（去前后）

        SELECT LENGTH(TRIM('   李淳罡    '))AS  out_put;  #输出结果9（一个字符三个字节）

        SELECT  TRIM('a'  FROM  'aaaaaaa张aaaaaa翠山aaaaa')  AS  out_put  #输出结果张aaaaaa翠山

（七）、

lapd用指定的字符实现左填充指定字符

rapd用指定的字符实现右填充指定字符

（八）、replace：替换函数

        SELECT  REPLACE('李淳罡李淳罡张三丰张雪峰','张三丰','李淳罡')   

                                                               #输出结果：李淳罡李淳罡张雪峰        

二、数学函数

（一）、round：四舍五入

        select round（-1.55）；绝对值取整再添符号

        select round（-1.55，1）；绝对值取整再添符号（保留1位）

（二）、ceil向上取整，返回大于等于该参数的最小整数

（三）、floor向下取整

（四）、truncate截断

        SELECT    TRUNCATE('1.666666',1);   #输出结果1.6

（五）、mod取余：  mod(a,b)=a-a/b*b

        SELECT    MOD(10,3);  #输出结果1

        SELECT    10%3;   #输出结果1

三、日期函数

字母表示含义

字符	含义	字符	含义
%Y	四位的年份	%H	小时（24小时制）
%y	两位的年份	%h	小时（12小时制）
%m	月份（01，03…12）	%i	分钟（00，01，02…）
%c	月份（1，2，3……12）	%s	秒（00，01.。）
%d	日（01，02）

（一）、now返回当前系统时间+日期

select   now()

（二）、curdate返回当前系统的日期不包括时间

（三）、curtime返回当前系统的时间不包括日期

（四）、获取指定的部分，年、月、日、时、分、秒

Select  year(now()) 年；

Select  year（‘1988-1-1’）年；

Select  year（hiredate）年  from  employees；

Select  month(now()) 月；

Select  monthname(now()) 月；英文状态的日期

Select  day(now())日；

Select  hour(now())小时；

Select  minute(now())分钟；

Select  second(now())秒；

（五）、datediff(日期1，日期2)返回日期1与日期2相差天数

（六）、格式转换

1.str_to_date：将日期格式的字符转换成指定格式的日期

str_to_date(‘9-13-1999’,’%m-%d-%Y’)               1999-09-13

date_format(‘2018/6/6’,’%Y年%m月%d日’)       2018年6月日

案例：查询入职日期为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’)

2.date_format：将日期转换成指定的格式

select   DATE_FROMAT(now(),’%y年%c月%d日’)

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

Select last_name,,date_format(hiredate,’%c/%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

when  常量2   then  要显示的常量2或语句2

…

Else  要显示的值n或者语句n

End

---

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

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

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

部门编号=50，显示的工资为1.3倍

其他部门显示工资为原工资

Select  salary 原工资，department_id,

case department_id

when 30 then saraly*1.1

when 40 then saraly*1.2

when 50 then saraly*1.3

else salary

end as 新工资

from  employees；

使用二：

case

when  条件1   then  要显示的常量1或语句1

when  条件2   then  要显示的常量2或语句2

…

Else  要显示的值n或者语句n

End

---

案例：查询员工的工资情况

如果工资>20000，显示A级别

如果工资>15000，显示B级别

如果工资>10000，显示C级别

Else 显示D级别

Select  case

When salary >20000 then ‘A’

When salary >20000 then ‘B’

When salary >20000 then ‘C’

Else ‘D’

End  as  工资级别

from  employees;

---

练习：

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

2.查询员工号，姓名以及工资提高20%后的结果（new salary）

3.将员工的姓名按照其首字母排序，并写出姓名的长度

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

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

5.使用CASE-WHEN，按照下面的条件：

job                        grade

AD_PRES            A

ST_MAN              B

IT_PROG             C

---

答案：

1.SELECT NOW();

2.SELECT employee_id,last_name,salary*1.2 'new salary' FROM employees;

3.（按首字母排序和按照姓名排序不一样）

SELECT last_name,LENGTH(last_name) 姓名长度
FROM employees
ORDER BY last_name ;

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

5.

SELECT job_id AS job,
CASE job_id
WHEN 'AD_PRES'  THEN 'A'
WHEN 'ST_MAN'   THEN 'B'
WHEN 'IT_PROG'  THEN 'C'
WHEN 'SA_REP'   THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS grade
FROM employees;

---