MySQL_常用函数_笔记

一、常用函数分类

• 数学函数
• 字符串函数
• 日期和时间函数
• 流程控制函数
• 其他函数

二、数学函数

1、四舍五入函数

round(x,y)：

• y=0：round(x)，四舍五入为整数
• y>0：四舍五入到小数点右边的第y位
• y<0：从小数点左边的第|y|位进行四舍五入

示例：

select round(455.55),round(455.55,0),round(455.55,1),round(455.55,-1),round(455.55,-3),round(555.55,-3) from dual;

将小数123456.488965四舍五入到百位

select round(123456.488965,-2) from dual;

2、截断函数

truncate(x,y)：

• y=0：截断为整数
• y>0 ：截断到小数点右边的第y位
• y<0：从小数点左边的第|y|位进行截断

示例：

select truncate(455.55,0),truncate(455.55,1),truncate(455.55,-1),truncate(455.55,-3) from dual;

note：此处truncate(455.55,0)，0不可省略，与四舍五入函数不同

3、求余

mod(x,y)：

select mod(3,2) from dual;

select mod(3,0) from dual;

练习1

1.写一个查询，分别计算100.456 四舍五入到小数点后第2位，第1位，整数位的值。

select round(100.456,2),round(100.456,1),round(100.456,0) from dual;

2.写一个查询，分别计算100.456 从小数点后第2位，第1位，整数位截断的值。

select truncate(100.456,2),truncate(100.456,1),truncate(100.456,0) from dual;

附：

• abs(x):返回x的绝对值;
• sqrt(x)：返回非负数x的平方根; PI():返回圆周率;
• mod(x,y)或%:返回x被y除的余数;
• ceil(x)、ceiling(x)：返回大于或者等于x的最小整数值；
• floor(x)：返回小于或者等于x的最大整数值;
• round(x,y)：返回保留小数点后面y位，四舍五入的整数；
• truncate(x,y):返回被舍弃的小数点后y位的数字x；
• rand()：每次产生不同的随机数； sign(x)：返回参数的符号；
• pow(x,y)和power(x,y)： 返回x的y次乘方的结果值；
• exp(x)：返回以e为底的x乘方后的值；
• log(x)：返回x的自然对数，x相对于基数e的对数；
• log10(x)：返回x的基数为10的对数；
• radians(x)：将参数x由角度转化为弧度；
• degrees(x)：将参数x由弧度转化为度。
• sin(x)：返回x正弦，其中x为弧度值；
• asin(x)返回x的反正弦，即正弦为x的值；
• cos(x)：返回x的余弦；
• acos(x)：返回x反余弦
• tan(x)：返回x的正切；
• atan(x)返回x的反正切；

三、字符串函数

1、length(str)：求字符串的长度

select length('abcabcabcabc') from dual;

查询员工的姓名长度为5的员工姓名、工资

select ename,sal from emp where length(ename)=5;

或者

select ename,sal from emp where ename like '_____';

其中  '_____'为5个占位符。

2、转大写，转小写

select lower(ename),upper(ename) from emp;

查询员工姓名为smith的员工信息

select * from emp where lower(ename)='smith';

select lower('ksdkDHKFLmdmmkKLJNJj'),upper('JJKKKhxjkcjjsJkkMm') from dual;

3、补足函数

lpad(str,len,str1)：判断str的长度是否等于len，如果小于len，在str左边补充str1使其长度等于len为止。如果大于len，将str截断使其长度等于len

rpad(str,len,str1)：判断str的长度是否等于len，如果小于len，在str右边补充str1使其长度等于len为止。如果大于len，将str截断使其长度等于len

select lpad('hello',20,'abc$') from dual;

select lpad('hello world',10,'abc') from dual;

select rpad('hello',20,'abc$') from dual;

select rpad('hello world',10,'abc') from dual;

4、trim()

去空格

select length(trim('     abc       '));

去除字符串左右的某个字符

select trim('a' from 'abcdefg'),trim('a' from 'aaaaaaaaabcdefg') from dual;

select trim('tt' from 'ttttttttestttttttt') from dual;

select trim('tt' from 'tttttttttestttttttt') from dual;

select trim('tt' from 'tttttttttesttttttttt') from dual;

5、替换函数

replace(s,s1,s2)：将s中的s1替换为s2

将员工姓名中的A替换为a

select ename,replace(ename,'A','a') from emp;

select replace('abc aaa abc bbb abc ccc','abc','###'),replace('abc aaa abc bbb abc ccc','abc','#') from dual;

6、截取子串

substring(str,start,length)

截取到字符串的尾部，省略第3个参数

select substring('hello world',3) from dual;

如果要从字符串的第1位开始截取，起始位置必须为1，不能为0

select substring('hello world',1,5) from dual;

起始位置必须为1，不能为0，结果是截取为空串
select substring('hello world',0,5) from dual;

练习2

1.显示所有员工姓名的前三个字符

select substring(ename,1,3) from emp;

2.显示正好为5个字符的员工的姓名，工资，部门号

select ename,salary,department_id from emp where length(ename)=5;

练习3

1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用length、substr）

select concat(upper(substring(ename,1,1)),lower(substring(ename,2))) 名称,length(ename) 长度 
from emp
where upper(substring(ename,1,1)) in ('J','A','M') order by ename;

或者

select concat(upper(left(ename,1)),lower(right(ename,length(ename)-1))) 名称,length(ename) 长度

from emp

where upper(left(ename,1)) in ('J','A','M')  order by ename

2.查询员工姓名中包含大写或小写字母A的员工姓名。

select ename from emp where locate('A',upper(ename))>0

3.显示所有员工的姓名,用a替换所有"A"

select replace(ename,'A','a') from emp

4.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度

select ename,length(ename) from emp where deptno in(10,20) and hiredate>'1981-05-01' and locate('A',ename)>0

5.查询每个职工的编号,姓名，工资

要求将查询到的数据按照一定的格式合并成一个字符串.

前10位：编号,不足部分用*填充,左对齐

中间10位：姓名，不足部分用*填充,左对齐

后10位：工资，不足部分用*填充,右对齐

select concat(rpad(empno,10,'*'),rpad(ename,10,'*'),lpad(sal,10,'*')) from emp

 

 

附：

• char_length(str)：返回字符串str的所包含字符个数;
• length(str):返回字符串str的长度；
• concat(s1,s2,...)： 字符串连接；
• concat_ws(x,s1,s2,…)：字符串连接, x是其它参数的分隔符;
• insert(s1,x,len,s2) ：返回字符串s1，s1中插入字符串s2；
• lower (str) | lcase (str)：将字符串全部转换成小写字母；
• upper(str) | ucase(str)：将字符串全部转换成大写字母；
• left(s,n)：返回最左边指定长度的字符；
• right(s,n)：返回最右边指定长度的字符；
• lpad(s1,len,s2) | rpad(s1,len,s2) ：填充字符串函数；
• trim(s1 from s) | ltrim(s) | rtrim(s):删除空格函数；
• repeat(s,n)：重复生成字符串函数；
• space(n)：返回一个由n个空格组成的字符串；
• replace（s，s1，s2）：字符串替换函数；
• strcmp(s1,s2)：比较字符串大小函数；
• substring(s,n,len)：获取子串函数；
• locate(str1,str) | position(str1in str) | instr(str, str1)：匹配子串开始位置函数;
• reverse(s)：将字符串s反转；
• elt(N,字符串1,字符串2,字符串3,…)：返回指定位置函数;

四、日期函数

1、查询当前日期

now():

select now(),curdate(),curtime() from dual;

2、两个日期之间相差的天数

select datediff(now(),'1990-02-03') from dual;

3、获取日期中的部分元素值

select extract(year from now()) from dual;

select extract(month from now()) from dual;

select extract(day from now()) from dual;

查询在前半年入职的员工信息
select * from emp where extract(month from hiredate) <=6;

4、date? +  interval? expr? unit

      date? -  interval? expr?  unit

select now() - interval 1 day from dual;

练习四

1.查询服务器当前时间

select now() from dual;

2.查询部门10,20的员工截止到2000年1月1日，工作了多少个月，入职的月份。

select ceil(datediff('2000-01-01',hiredate)/30),monthname(hiredate)from emp where deptno in(10,20);

3.如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期，转正日期，入职日期是第多少月，第多少周

select ename,hiredate,date_format(date_add(hiredate,interval 6 month),'%y-%m-%d')转正期,month(hiredate),weekofyear(hiredate) from emp where job<>'MANAGER';

或者

select ename,hiredate,date_format(hiredate+interval 6 month,'%y-%m-%d') 转正日期,month(hiredate),weekofyear(hiredate)

from emp where job<>'MANAGER';

附：

CURDATE()和CURRENT_DATE() ：获取当前日期函数;

NOW()：返回服务器的当前日期和时间；

CURTIME(）：返回当前时间，只包含时分秒；

UTC_DATE(）：返回世界标准时间日期函数；

UTC_TIME()：返回世界标准时间函数；

TIMEDIFF(expr1, expr2)：返回两个日期相减相差的时间数；

DATEDIFF(expr1, expr2)：返回两个日期相减相差的天数；

DATE_ADD(date,INTERVAL expr type):日期加上一个时间间隔值；

DATE_SUB(date,INTERVAL expr type):日期减去一个时间间隔值；

DATE(date)、TIME(date)、YEAR（date）：选取日期时间的各个部分：

EXTRACT(unit  FROM  date)：从日期中抽取出某个单独的部分或组合；

DAYOFWEEK(date) 、DAYOFMONTH(date) 、DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天

DAYNAME、MONTHNAME：返回日期的星期和月份名称；

DATE_FORMAT(date,format)：格式化日期；

根据format 指定的格式显示日期或者时间值。 date 参数是合法的日期 format 规定日期/时间的输出格式

TIME_FORMATE(time,formate）：格式化时间；

其中 可使用的格式：

五、流程控制函数

 

六、其他函数

 

未完，待续。。。