MySQL(四）：函数

一、统计函数（上篇已讲）

二、字符串函数

#演示字符串相关函数的使用

-- 1\使用charset(str)返回字串字符集
SELECT CHARSET(ename) FROM emp;

-- concat(str2,[, ...]连接字串
SELECT CONCAT(ename,'工作是',job) FROM emp;

-- INSTR('str','substr') 返回substr 在str中出现的位置，没有返回0
-- DUAL 亚元表，系统表 可以作为测试表使用
SELECT INSTR('hanshunping','ping')FROM DUAL;

-- UCASE(str) 转大写，LCASE(str) 转小写
SELECT UCASE(ename) FROM emp;
SELECT LCASE(ename) FROM emp;

-- LEFT(string2,length) 从string2中的左边起取length个字符 从右边取：RIGHT(str,length);
SELECT LEFT(ename,2) FROM emp;

-- LENGTH(str) str长度
SELECT LENGTH(ename) FROM emp;


-- REPLACE(str,search_str,replace_str) 在str中用replace_str替换search_str
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;

-- STRCMP(str1,str2) 逐字比较两个字符大小,相等为0，不相等为1
SELECT STRCMP('hsp','asp') FROM DUAL;

-- SUBSTRING(str,position [,length]) 从str的position开始（从1开始计算】，取length个字符
SELECT SUBSTRING(ename,1,2) FROM emp;/*从ename的第一个位置取2个字符*/

-- LTRIM（str1) RTRIM(str1) TRIM(str)  去除前端空格或后端空格
SELECT LTRIM('  韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平  ') FROM DUAL;
SELECT TRIM('  含  ') FROM DUAL;


-- 练习:以首字母小写的方式显示所有员工emp的姓名str.sql;
SELECT REPLACE(ename,LEFT(ename,1),LCASE(LEFT(ename,1))) FROM emp;
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) AS new_name FROM emp;

三、数学函数

#演示数学相关函数
-- ABS(num)  绝对值
SELECT ABS(-10) FROM DUAL;


-- BIN(decimal_number) 十进制转二进制

SELECT BIN(10) FROM DUAL;

-- CEILING(num) 向上取整，得到比num大的最小整数

SELECT CEILING(-2.5) FROM DUAL;

-- CONV(num2,from_base,to_base)  进制转换
SELECT CONV(16,16,2) FROM DUAL;
  -- 把10从10进制转为2进制

-- floor(num) 向下取整。得到比num2小的最大整数
SELECT FLOOR(1.5) FROM DUAL;

-- fromat(nums,decimal_places)  保留小数位数  四舍五入
SELECT FORMAT(AVG(sal),1) AS avg_sal FROM emp;
SELECT FORMAT(1.2347242,3) FROM DUAL;
-- HEX(decinalNumber) 转十六进制
SELECT HEX(11) FROM DUAL;

-- LEAST(num1,num2,..) 求最小值
SELECT LEAST(1,10,3) FROM DUAL;
 -- mod(numerator,denominator)  求余
 SELECT MOD(10,3) FROM DUAL;
 
 -- RAND([seed]) 返回随即数，0<v<1.0
 SELECT RAND() FROM DUAL;
 SELECT RAND(3) FROM DUAL;
	-- 使用RAND()每次返回不同的随机数，在【0，1.0】范围内
	-- 使用RAND(seed)返回【0，1.0】随机数，seed不变，该随机数也不变。

四、时间日期

#演示时间函数

SELECT CURRENT_DATE() FROM DUAL;  /*当前日期*/

SELECT CURRENT_TIME() FROM DUAL;  /*当前时间*/

SELECT CURRENT_TIMESTAMP()  FROM DUAL; /*当前时间戳 年月日 时分秒*/


-- 创建测试表
CREATE TABLE mes(
	id INT,
	content VARCHAR(30),
	sent_time DATETIME);
	

INSERT INTO mes
	VALUES(1,'北京时间',CURRENT_TIMESTAMP()),
	(2,'上海新闻',NOW()),
	(3,'广州新闻',NOW());
SELECT * FROM mes;
DELETE FROM mes 
	WHERE `sent_time`='2024-08-05 12:24:38';

SELECT NOW() FROM DUAL;

-- 1\显示所有新闻信息，发布时间只显示日期，不显示时间
SELECT id,content,DATE(sent_time) FROM mes;

-- 2\请查询在10分种内发布的新闻
SELECT * FROM mes
	WHERE DATE_ADD(sent_time,INTERVAL 10 MINUTE) >=NOW();
		-- 从sent_time加10分钟>=现在的时间，说明在10分钟内
	
SELECT * FROM mes
	WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE) <=sent_time;
		-- 从now()-10分钟<=发送的时间，说明在10分钟内
	
-- 求2011-11-11到1990-1-1 相差多少天

SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;
-- 我活了多少天
SELECT DATEDIFF(NOW(),'2001-5-27') FROM DUAL;

-- 能活90，还剩是多少天
-- year :可以是年月日 时分秒
-- 前面一项也可以是time datetime,date
SELECT DATEDIFF(DATE_ADD('1986-11-11',INTERVAL 90 YEAR),NOW()) FROM DUAL;

SELECT TIMEDIFF('10:11:11','6:11:11') FROM DUAL;


-- YEAR｜ＭＯＮＴＨ｜DAY|DATE(datetime) 只显示年|月|日|日期
SELECT YEAR(NOW()) FROM DUAL;

-- unix_timestamp():返回1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP()/(24*3600*365) FROM DUAL;
SELECT UNIX_TIMESTAMP() FROM DUAL;

-- from_unixtime():可以啊unix_timestamp秒数转成指定格式的日期
-- 意义：在开发中，可以存放一个整数，通过from_unixtime()转成时间
SELECT FROM_UNIXTIME(1722845546,'%Y-%m-%d %H:%i:%S') FROM DUAL;

五、加密函数

#演示加密函数和系统该函数

-- USER()擦汗寻用户
	-- 查询登录到mysql的有哪些用户，以及登录的ip

SELECT USER() FROM DUAL;

-- datebase() 查询当前数据库名称
SELECT DATABASE();

-- MD5(str)  为字符串算出一个MD5 32的字符串，常用（用户密码）加密
-- root 密码是123456-> 加密md5 ->在数据库中存放的是加密后的密码
SELECT MD5('123456') FROM DUAL;
SELECT LENGTH(MD5('123456')) FROM DUAL;

-- 演示用户表，存放密码时，是md5
CREATE TABLE users3(
	`id` INT,
	`name` VARCHAR(32) NOT NULL DEFAULT ' ',
	`pwd` CHAR(32) NOT NULL DEFAULT ' ');
INSERT INTO users3 VALUES(1,'Alice',MD5('Alice'));
SELECT * FROM users3;

SELECT * FROM users3
	WHERE `name`='Alice' AND pwd=MD5('Alice');
	
-- PASSWORD(str) 加密函数，mysql数据库的用户密码就是PASSWORD函数加密的
-- select password('css01') from dual; -- 数据库的密码 $A$005$-%AjIyt,81EeU;bk5DqTFCW9UH/oalNCfwYrpKeHb72ChqXuEVtZtSczhJD


-- select * from mysql.user \G 从原文密码str计算并返回密码字符串
-- 通常用于对mysql 数据库的用户密码加密
-- mysql.user 表示数据库.表
SELECT * FROM mysql.user;

六、流程控制

#演示流程控制函数

-- IF（expr1,expr2,expr3)  如果expr1为TRue,则返回expr2,否则返回expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL;

-- IFNULL(expr1,expr2) 如果expr1不为空NULL，则返回expr1,否则返回expr
SELECT IFNULL('北京','上海') FROM DUAL;

-- select case when expr1 then expr2
--	when expr3 then expr4 else expr5 end;
-- 如果expr1为真返回expr2,如果expr3为真，返回expr4,否则返回expr5

SELECT CASE
	WHEN TRUE THEN 'jack'
	WHEN FALSE THEN 'Tom' 
	ELSE 'Alice' END;

#案例
-- 1\查询emp表，如果comm是null,则显示0.0
-- IF判断是否为NULL，用is NULL,是否为空 用is not
SELECT ename,IFNULL(comm,0.0) FROM emp;
SELECT ename,IF(comm IS NULL,0.0,comm) FROM emp;

-- 2\如果emp表的job是clerk则显示职员，如果是manager则显示经历，如果是salesman则显示销售，其他正常显示
SELECT ename,(SELECT CASE
	WHEN job='CLERK' THEN '职员'
	WHEN job='MANAGER' THEN '经理'
	WHEN job='SALESMAN' THEN '销售'
	ELSE job END) AS job
	FROM emp;