一、统计函数（上篇已讲）
二、字符串函数
#演示字符串相关函数的使用-- 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.0SELECT 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 mesVALUES(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 mesWHERE DATE_ADD(sent_time,INTERVAL 10 MINUTE) >=NOW();-- 从sent_time加10分钟>=现在的时间，说明在10分钟内SELECT * FROM mesWHERE 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的有哪些用户，以及登录的ipSELECT 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 users3WHERE `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,否则返回expr5SELECT CASEWHEN 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 CASEWHEN job='CLERK' THEN '职员'WHEN job='MANAGER' THEN '经理'WHEN job='SALESMAN' THEN '销售'ELSE job END) AS jobFROM emp;
