【数据库】6、MySQL函数（字符串、数值、日期、流程、分组函数）

最新推荐文章于 2024-07-06 08:52:29 发布

渐若窥宏大

最新推荐文章于 2024-07-06 08:52:29 发布

阅读量250

点赞数

分类专栏：数据库文章标签：数据库 MySQL 函数字符串分组

本文链接：https://blog.csdn.net/qq_41569732/article/details/106391466

版权

数据库专栏收录该内容

16 篇文章 1 订阅

订阅专栏

函数

文章目录

函数
1、单行函数
2、分组函数

两种SQL函数

单行函数

多行函数

1、单行函数

只对一行进行变换，每行返回一个结果
可以嵌套
参数可以是一字段或一个表达式或一个值

只对一行进行变换每行返回一个结果

可以嵌套

参数可以是一字段或一个表达式或一个值

1.1 字符串函数

字符串函数

/*
字符串函数
(1)length(xx)：求字节数，和字符集有关
(2)char_length(xx)：求字符数
(3)CONCAT(str1,str2,...)
(4)CONCAT_WS(分隔符,str1,str2,...)  分隔符连接起来的
(5)UPPER(xx)；转大写
(6)LOWER(xx)：转小写
(7)LEFT(s,n)：返回最左边的3个字符
   RIGHT(s,n)：返回最右边的3个字符
(8)LTRIM(s)：去掉左边的空格
   RTRIM(s)：去掉右边的空格
    TRIM(s)：去掉前后空白符
   TRIM(【BOTH 】s1 FROM s) ：从s中去掉前后的s1
   TRIM(【LEADING】s1 FROM s)：从s中去掉开头的s1
   TRIM(【TRAILING】s1 FROM s)：从s中去掉结尾的s1
(9)SUBSTRING(s,index,len)   
*/
#求字符串的长度
#例如：查询员工的姓名的字符的个数
SELECT ename AS "姓名", CHAR_LENGTH(ename) AS "姓名的字数" FROM t_employee;

#求字符串的拼接
SELECT 'hello' + 'world'; #在mysql中，+只表示求和，不表示拼接
SELECT CONCAT('hello','world','java');
SELECT CONCAT_WS('-','hello','world','java');

#转大小写
SELECT UPPER('hello'),LOWER('HELLO');

SELECT LEFT('hello',3);

SELECT CONCAT('[',LTRIM('    hello    world    '),']');

SELECT CONCAT('[',TRIM('    hello    world    '),']');

SELECT TRIM(BOTH '#' FROM '###hello  world###');
SELECT TRIM(LEADING '#' FROM '###hello  world###');
SELECT TRIM(TRAILING '#' FROM '###hello  world###');

SELECT SUBSTRING('hello',2);#从第二个字符，不是下标的概念

函数	用法
CONCAT(S1,S2,…,Sn)	连接S1,S2,…,Sn为一个字符串
CONCAT_WS(s, S1,S2,…,Sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上s
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

举例1：大小写控制函数

函数	结果
LOWER(‘SQL Course’)	sql course
UPPER(‘SQL Course’)	SQL COURSE

这类函数改变字符的大小写。

举例2：字符控制函数

函数	结果
CONCAT(‘Hello’,‘World’)	HelloWorld
SUBSTR(‘HelloWorld’,1,5)	Hello
LENGTH(‘HelloWorld’)	10
INSTR(‘HelloWorld’,‘W’)	6
*LPAD(salary,10,’’)**	*****24000
*RPAD(salary,10, ‘’)**	24000*****
TRIM(‘H’ FROM ‘HelloWorld’)	elloWorld
REPLACE(‘abcd’,‘b’,‘m’)	amcd

1.2 数值函数/数学函数

/*
数学函数：
*/
SELECT CEIL(2.3),FLOOR(2.3),ROUND(2.3)
SELECT CEIL(2.6),FLOOR(2.6),ROUND(2.6)

SELECT RAND() #[0,1)的随机值

#ROUND(x,y)，保留小数点后y位，看第y+1位，决定四舍五入
#TRUNCATE(x,y)，保留小数点后y位，y位后直接截掉
SELECT ROUND(2.12345,4),TRUNCATE(2.12345,4)

SELECT ename,salary FROM t_employee;
SELECT ename, ROUND(salary,0) FROM t_employee;

数值函数/数学函数

函数	用法
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模
RAND()	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

举例1：ROUND:四舍五入

ROUND(45.926, 2)     -->  45.93

举例2：TRUNCATE:截断

TRUNCATE(45.926)      --> 45

举例3：MOD:求余

MOD(1600, 300)	 --> 100

1.3 日期函数

/*
日期时间函数
(1)系统时间
NOW()和SYSDATE()

CURRENT_DATE()：只有日期
CURRENT_TIME()：只有时间
CURRENT_TIMESTAMP()：日期时间
(2)求出年等
year(x)
month(x)
day(x)
(3)DATEDIFF(x,y)：间隔天数
(4)格式化
DATE_FORMAT：把日期转字符串
STR_TO_DATE：把字符串转日期
*/
#系统时间
SELECT NOW()
SELECT SYSDATE()

SELECT CURRENT_DATE()
SELECT CURRENT_TIME()
SELECT CURRENT_TIMESTAMP()

SELECT ename,birthday,YEAR(birthday),MONTH(birthday),DAY(birthday) FROM t_employee
SELECT ename,YEAR(NOW()) - YEAR(birthday) AS "年龄" FROM t_employee;

SELECT ename, YEAR(NOW()) - YEAR(hiredate) + 1 AS "入职年数" FROM t_employee

SELECT ename,birthday, DAYOFWEEK(birthday) FROM t_employee;
SELECT DAYOFWEEK(NOW())

SELECT birthday,DATEDIFF(NOW(), birthday) FROM t_employee;

SELECT DATEDIFF(NOW(),'2019-05-13');

SELECT DATE_FORMAT(birthday,'%Y年%m月%d日') FROM t_employee;

日期函数

函数	用法
CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
WEEK(date) / WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY…SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

其中：

（1）DATE_ADD(datetime,INTERVAL expr type)

表达式类型：

参数类型	参数类型
YEAR	YEAR_MONTH
MONTH	DAY_HOUR
DAY	DAY_MINUTE
HOUR	DAY_SECOND
MINUTE	HOUR_MINUTE
SECOND	HOUR_SECOND
	MINUTE_SECOND

举例：

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号

（2）DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,…）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，…）	%c	数字表示月份（1,2,3,…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
%e	数字形式表示月中的天数（1,2,3,4,5…）
%H	两位数字表示小数，24小时制（01,02…）	%h和%I	两位数字表示小时，12小时制（01,02…）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4…）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，…）
%w	以数字表示周中的天数(0=Sunday,1=Monday…)
%j	以3位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

1.4 流程函数

流程函数

/*
流程控制函数
（1）if(value,x,y)：如果value为true,返回x,否则y
（2）ifnull(value,x)：如果value是null值，就用x代替，否则用value自己的值
在mysql中null值是非常特殊
①判断null值用：<=>或is null ,is not null，不能用=和!=
②计算null值，所有运算符，遇到null，运算结果都是null
（3）case 字段/表达式 
	when 常量值  then 值/表达式
	when 常量值  then 值/表达式
	when 常量值  then 值/表达式
	else 值/表达式
    end
    
    类似于Java的switch...case
（4）case 
     when 条件1  then  值/表达式
     when 条件2  then  值/表达式
     when 条件3  then  值/表达式
     else 值/表达式
     end
    类似于Java的if..else if...
*/
SELECT IF(FALSE,1,2)

#求员工的姓名和实发工资
#实发工资 = 薪资 + 奖金 = 薪资 + 薪资 * 奖金比例
SELECT ename,salary + salary * commission_pct FROM t_employee;

SELECT ename,salary + salary * IFNULL(commission_pct,0) FROM t_employee;

SELECT ename,job_id, CASE job_id
  WHEN 1 THEN '教学总监'
  WHEN 2 THEN '讲师'
  WHEN 3 THEN '助教'
  ELSE '其他'
  END AS "职位说明"
FROM t_employee;

SELECT ename, salary, CASE 
	WHEN salary >= 20000 THEN '高富帅'
	WHEN salary >= 15000 THEN '潜力股'
	WHEN salary >=  8000 THEN '屌丝'
	ELSE '土鳖'
	END AS "情况"
FROM t_employee;

函数	用法
IF(value,t ,f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END	相当于Java的switch…case…

举例1：

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"
FROM employees;

举例2：

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "实发工资"
FROM   employees;

举例3：

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END  "描述"
FROM employees;

1.5 其他函数

/*
其他函数
*/

#加密函数
INSERT INTO t_user VALUES('liuawen','123456');
INSERT INTO t_user VALUES('admin',PASSWORD('123456'));
INSERT INTO t_user VALUES('awen',MD5('123456'));

SELECT * FROM t_user WHERE username = 'liuawen' AND `password` = '123456';
SELECT * FROM t_user WHERE username = 'admin' AND `password` = PASSWORD('123456');
SELECT * FROM t_user WHERE username = 'awen' AND `password` = MD5('123456');

其他函数

函数	用法
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登录用户名
password(str)	返回字符串str的加密版本，41位长的字符串
md5(str)	返回字符串str的md5值，也是一种加密方式

2、分组函数

什么是分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

/*
分组函数
(1)count(*)：统计记录数
count(*)和count(常量)结果是一样的
count(字段名)统计非null值
(2)sum(x)：总和
(3)max(x)：最大值
   min(x)：最小值
(4)avg(x)：平均值   
*/
#查询员工总数
SELECT COUNT(*) FROM t_employee;
SELECT COUNT(1) FROM t_employee;
SELECT COUNT(100) FROM t_employee;
SELECT COUNT(commission_pct)  FROM t_employee;

#查询男员工数量
SELECT COUNT(*) FROM t_employee WHERE gender = '男';

#查询薪资大于20000的女员工的数量
SELECT COUNT(*) FROM t_employee WHERE gender ='女' AND salary > 20000;

#查询每个月看要给所有员工发的实发工资的总和是多少
#select sum(实发工资) from t_employee;
SELECT SUM(salary + salary*IFNULL(commission_pct,0)) FROM t_employee;
#保留到分，后面四舍五入
SELECT ROUND(SUM(salary + salary*IFNULL(commission_pct,0)),2) FROM t_employee;

#查询最高工资和最低工资
SELECT MAX(salary),MIN(salary) FROM t_employee;

#查询年龄最大的员工的年龄
SELECT MAX(YEAR(NOW()) - YEAR(birthday)) FROM t_employee;

#查询入职最早的一个员工的入职日期
SELECT MIN(hiredate) FROM t_employee;

#查询平均薪资
SELECT AVG(salary) FROM t_employee;

#查询员工的平均年龄
SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday)),0) FROM t_employee;

什么是分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

组函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- **COUNT() **

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

COUNT(*)count(1返回表中记录总数,适用于任意数据类型。

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

•COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

问题：用count(*)，count(1)谁好呢?

其实，对于myisam引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb引擎的表用count(*)直接读行数，效率很低，因为innodb真的要去数一遍。

渐若窥宏大

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【数据库】6、MySQL函数（字符串、数值、日期、流程、分组函数）

函数文章目录函数1、单行函数1.1 字符串函数1.2 数值函数/数学函数1.3 日期函数1.4 流程函数1.5 其他函数2、分组函数两种SQL函数单行函数多行函数1、单行函数只对一行进行变换，每行返回一个结果可以嵌套参数可以是一字段或一个表达式或一个值只对一行进行变换每行返回一个结果可以嵌套参数可以是一字段或一个表达式或一个值1.1 字符串函数字符串函数/*字符串函数(1)length(xx)：求字节数，和字符集有关(2)char_length(xx)：求字符数(
复制链接

扫一扫

专栏目录