金山老师的笔记

Appearance

16.函数 ​

16.1 函数介绍 ​

MySQL提供了很多功能强大、方便易用的函数，在进行数据库管理以及数据的查询和操作时，帮助我们提高对数据库的管理效率

  1. 单行函数
  2. 多行函数

16.2 单行行数 ​

单行函数对单行操作 每行返回一个结果 有可能返回值与原参数数据类型不一致 单行函数可以写在SELECT、WHERE、ORDER BY子句中 有些函数没有参数，有些函数包括一个或多个参数 函数可以嵌套

语法: 函数名[(参数1，参数2,…)] 其中的参数可以是以下之一： 变量 列名 表达式

16.3数学函数 ​

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的反正切；

sql
-- 1 数学运算
-- ABS(x):返回x的绝对值;
SELECT  ABS(100) , ABS(-100) 
-- Oracle  不查询表数据 select * from dual  


-- SQRT(x)：返回非负数x的平方根;

SELECT  SQRT(4),SQRT(16)


-- PI():返回圆周率;
SELECT PI()

-- MOD(x,y)或%:返回x被y除的余数;

SELECT MOD(10,3) AS mody ,10%3 AS '%Y'



-- CEIL(x)、CEILING(x)：返回大于或者等于x的最小整数值；
SELECT  CEIL(3.1),CEIL(3.2),CEIL(3.3),CEIL(3.4),CEIL(3.5),CEIL(3)

-- FLOOR(x)：返回小于或者等于x的最大整数值;
SELECT  FLOOR(3.1),FLOOR(3.2),FLOOR(3.3),FLOOR(3.4),FLOOR(3.5),FLOOR(3)


-- ROUND(x,y):返回保留小数点后面y位，四舍五入的整数；
SELECT ROUND(10.4546,3), ROUND(10.4543,3);

-- TRUNCATE(x,y):返回被舍弃的小数点后y位的数字x； 截断
SELECT TRUNCATE(10.4546,3), TRUNCATE(10.4543,3);


-- RAND()：每次产生不同的随机数； 产生0到1之间的随机数小数 double
SELECT  RAND() ,RAND() ,RAND() ,RAND() ;

-- SIGN(x)：返回参数的符号；
SELECT SIGN(-10),SIGN(10),SIGN(0),SIGN(-0) ;



-- POW(x,y)和POWER(x,y)： 返回x的y次乘方的结果值；
SELECT POW(2,2),POW(2,3),POW(2,4);



-- EXP(x)：返回以e为底的x乘方后的值；
SELECT  EXP(2) ,EXP(3) ;

-- LOG(x)：返回x的自然对数，x相对于基数e的对数；
SELECT LOG(10)
/**
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的反正切；;
*/

16.3.1 练习 ​

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

16.4 字符串函数 ​

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(str1 IN str)|INSTR(str, str1)：匹配子串开始位置函数;

REVERSE(s)：将字符串s反转；

ELT(N,字符串1,字符串2,字符串3,…)：返回指定位置函数;

sql
-- CHAR_LENGTH(str)：返回字符串str的所包含字符个数;

SELECT ename, CHAR_LENGTH(ename) FROM emp;

-- LENGTH(str):返回字符串str的长度；
-- utf 8 
SELECT ename, LENGTH(ename), LENGTH('中国'), CHAR_LENGTH('中国')FROM emp;

-- CONCAT(s1,s2,...)： 字符串连接；

SELECT empno,ename,job ,  CONCAT(empno,'--',ename,'--',job ) AS info  FROM emp;

-- CONCAT_WS(x,s1,s2,…)：字符串连接, x是其它参数的分隔符;
SELECT empno,ename,job ,  CONCAT_WS('--&--',empno,ename,job ) AS info  FROM emp;


-- INSERT(s1,x,len,s2) ：返回字符串s1，s1中插入字符串s2；
-- s1: abcdef 原始字符串
-- x:   1 position  开始插入新字符串的位置
-- len  3 len 覆盖几位(插入几位)
-- s2 : mnoqrs 标识使用次此字符串插入
SELECT INSERT('abcdef',3,2,'-mnopqrs-'),  INSERT('abcdef',5,1,'-mnopqrs-');

  -- LOWER (str)|LCASE (str)：将字符串全部转换成小写字母；

  -- UPPER(str)|UCASE(str)：将字符串全部转换成大写字母；
  
  SELECT ename ,LOWER(ename) ,UPPER(ename) , LOWER('I Am Jshand My Age 18') ,UPPER('I Am Jshand My Age 18'), LOWER('中国') FROM emp  ;

  -- LEFT(s,n)：返回最左边指定长度的字符；

  -- RIGHT(s,n)：返回最右边指定长度的字符；

  -- LPAD(s1,len,s2)| RPAD(s1,len,s2) ：填充字符串函数；
  
  SELECT ename 名字 ,
	LEFT(ename,3) 名字开头3个字母 ,RIGHT(ename,4) 名末尾4个字母,RIGHT(ename,5) 名末尾5个字母 , 
	
	LPAD(ename,10,'*') 名字开头10个字母	,RPAD(ename,10,'*') 名字开头10个字母
	
	FROM emp 
  
  
  

 --  TRIM(s1 FROM s)|LTRIM(s）|RTRIM(s):删除空格函数
 SELECT 
 TRIM(LEADING '，' FROM '，，barxxx'),
 TRIM(LEADING ' '  FROM  '  abc def  ' )
 
 
 
SELECT CHAR_LENGTH('  abc def  ' ) len ,
	CONCAT('-','  abc def  ' ,'-') str  ,
	CONCAT('-',TRIM('  abc def  ' ),'-') TRIM ,
	CONCAT('-' , TRIM(LEADING ' '  FROM  '  abc def  ' )  ,'-') lead ,
	CONCAT('-',LTRIM('  abc def  ' ),'-') LTRIM,
	CONCAT('-',RTRIM('  abc def  ' ),'-') LTRIM
 

 -- REPEAT(s,n)：重复生成字符串函数；
SELECT REPEAT("abc-",3) 
 

-- SPACE(n)：返回一个由n个空格组成的字符串；
  SELECT  CONCAT('start-',SPACE(10),'-end')

 -- REPLACE（s，s1，s2）：字符串替换函数；
SELECT 'my name is xxx '   ,REPLACE('my name is xxx ','m','M')
  
  

-- STRCMP(s1,s2)：比较字符串大小函数；
-- a b c d e f g h i j k --ASCII   (compare java) 
SELECT STRCMP('abc','abe'), STRCMP('bbc','abe'),  STRCMP('abe','abc'),STRCMP('abc','abc')


-- SUBSTRING(s,n,len)：获取子串函数；
SELECT   'abcdef' ,
	SUBSTRING('abcdef',3),
	SUBSTRING('abcdef',3,4)


-- LOCATE(str1,str)|POSITION(str1 IN str)|INSTR(str, str1)：匹配子串开始位置函数;
-- mysql 单引号   Java 定义sql  String sql = “   select ‘’  ”
SELECT POSITION("cd" IN "abcdef") AS MatchPosition; 

SELECT 'abcdef' ,
	LOCATE('cd','abcdef') ,
	LOCATE('cd','abcdef',1) ,
	LOCATE('cd','abcdef',2) ,
	LOCATE('cd','abcdef',3) ,
	 LOCATE('cd','abcdef',5) ,
	 POSITION('cd' IN 'abcdef'),
	INSTR('abcdef','cd')


  -- REVERSE(s)：将字符串s反转
  SELECT  'abcdef' ,REVERSE('abcdef')
  
  
  

-- ELT(N,字符串1,字符串2,字符串3,…)：返回指定位置字符串函数;

SELECT ELT(3,'这','是','一a','个','测','试');

16.4.1 练习 ​

  1. 显示所有员工姓名的前三个字符
  2. 显示正好为5个字符的员工的姓名，工资，部门号
  3. 写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用length、substr）
  4. 查询员工姓名中中包含大写或小写字母A的员工姓名。 显示所有员工的姓名,用a替换所有"A"
  5. 查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度
  6. 查询每个职工的编号,姓名，工资 要求将查询到的数据按照一定的格式合并成一个字符串. 前10位：编号,不足部分用*填充,左对齐 中间10位：姓名，不足部分用*填充,左对齐 后10位：工资，不足部分用*填充,右对齐
sql
-- 1. 显示所有员工姓名的前三个字符
SELECT ename , SUBSTRING(ename,1,3) FROM emp


-- 2. 显示正好为5个字符的员工的姓名，工资，部门号
SELECT ename , sal  工资, deptno  部门号 ,LENGTH(ename) FROM emp WHERE LENGTH(ename) = 5

-- 3. 写一个查询,
-- 用首字母大写，其它字母小写显示雇员的 ename，
-- 显示名字的长度，
-- 并给每列一个适当的标签，
SELECT 
  ename 名字,
  CONCAT(
    UPPER(SUBSTRING(ename, 1, 1)),
    LOWER(SUBSTRING(ename, 2))
  ) 名字2,
  UPPER(SUBSTRING(ename, 1, 1)) FIRST,
  LOWER(SUBSTRING(ename, 2)) other,
  LENGTH(ename) 名字长度 
FROM
  emp 
  WHERE UPPER(SUBSTRING(ename, 1, 1)) IN ('J','A','M')
  ORDER BY ename



-- 4. 查询员工姓名中中包含大写或小写字母A的员工姓名。
-- 显示所有员工的姓名,用a替换所有"A"
SELECT ename , REPLACE(ename,'A','a') FROM emp WHERE LOWER(ename) LIKE '%a%'

   
-- 5. 查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，
-- 员工姓名长度
SELECT * FROM emp  WHERE deptno IN (10,20) AND hiredate > '1981-05-01' AND ename LIKE '%A%' 


-- 6. 查询每个职工的编号,姓名，工资
   要求将查询到的数据按照一定的格式合并成一个字符串.
  
   前10位：编号,不足部分用\*填充,左对齐
   中间10位：姓名，不足部分用\*填充,左对齐
   后10位：工资，不足部分用\*填充,右对齐
   
SELECT 
CONCAT( 
  LPAD(empno,10,'*') ,
  RPAD(ename ,10,'*') ,
  RPAD(sal,10,'*')  ) 
FROM
  emp ;

16.5 日期和时间函数 ​

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)：格式化日期；

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

sql
CURDATE()和CURRENT_DATE() ：获取当前日期函数;
NOW()：返回服务器的当前日期和时间；
CURTIME(）：返回当前时间，只包含时分秒；

SELECT NOW() , CURDATE() ,CURTIME()
UTC_DATE(）：返回世界标准时间日期函数；
UTC_TIME()：返回世界标准时间函数；


-- UTC_DATE
SELECT UTC_DATE() ,UTC_TIME() 

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

SELECT  DATEDIFF('2020-11-10','2020-10-10')
SELECT  TIMEDIFF('15:05:01','16:05:01')

-- DATE_ADD(date,INTERVAL expr type):日期加上一个时间间隔值；
-- select DATE_ADD(now(),'INTERVAL  1 Day')

SELECT NOW(), DATE_ADD(NOW(),INTERVAL 2 DAY), DATE_ADD(NOW(),INTERVAL 2 YEAR)


-- DATE_FORMAT 
SELECT  DATE_FORMAT(NOW(),'%Y')

16.6 流程控制函数 ​

IF(表达式？ true（<>0 <> null） ，false)

SELECT sal , IF(sal>=3000,'高薪','码农') FROM emp ORDER BY sal

CASE 分支

sql
SELECT sal ,CASE sal 
	WHEN 800 THEN '第一档'
	WHEN 2000 THEN '第2档'
	WHEN 4000 THEN '第3档'
	ELSE '中间的薪水'
      END 
     FROM emp  ORDER BY sal;
     
     
SELECT sal ,CASE  
	WHEN sal>=800 AND sal<2000 THEN '第一档'
	WHEN sal>=2000 AND sal<4000  THEN '第2档'
	ELSE '第3档'
      END 
     FROM emp  ORDER BY sal;

IFNULL IFNULL(expr1,expr2)，如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。

SELECT ename ,sal, comm , IFNULL(comm,0) FROM emp

NULLIF(expr1,expr2)，如果expr1=expr2成立，那么返回值为null，否则返回值为expr1的值。

SELECT ename ,sal, comm , IFNULL(comm,0) ,NULLIF(comm,0) , FROM emp

16.7 其他函数 ​

sql
SELECT DATABASE(),VERSION(),USER();

-- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
SELECT PASSWORD('root');


-- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
SELECT * FROM mysql.user ;

-- Md5
SELECT MD5('root');

SELECT LENGTH('900150983cd24fb0d6963f7d28e17f72')

Released under the MIT License.

Copyright © 2024-present 金山老师