2022-08-18-第八组----MySQL常用函数

目录

一、聚合函数

1、count：计数

2、avg：平均值

3、sum：求和

4、min：最小值

5、max:最大值

二、数值型函数

1、ceiling(x)

2、floor(x)

3、round(x)

4、truncate(x,y)

5、Pl

6、rand

7、abs

 8、取余

三、字符串型函数

1、length(s):字符串的长度

2、concat(1.2....)：合并字符串

3、lower(str):将字母转成小写

4、upper(str):将字母转成大写

5、left(str,x):返回字符串str的左边的x个字符

6、right(str,x):返回字符串str右边的x个字符

7、trim:去掉左右两边的空格

8、replace:替换

9、substring:截取

10、reverse: 反转

四、日期和时间函数

1、获取时间和日期

2、时间戳和日期转换函数

3、根据日期获取年月日的数值

（1）获取当前月份

（2）获取当前月份的名称

（3）获取当前星期几

（4）获取当前星期几在星期中是第几天

（5）获取指定日期是年里的第几周

（6）获取指定日期在当前月是第几天

（7）获取当前年份

4、时间日期的计算

（1）日期加法

（2）日期减法

（3）时间间隔

（4）日期格式化

5、日期格式对应表

五、 加密函数

六、流程控制函数 

1、基本语法

（1）if(test,t,f)

（2）ifnull(arg1,arg2)

（3）nullif(arg1,arg2)

2、案例

（1）对一系列的值进行判断

（2）行转列（面试时问得多）

---

一、聚合函数

1、count：计数

效率：count（*）≈count（1）>count（主键）

（1）count（*）：MySQL对count（*）底层优化，count(*)是MySQL数据库特有的，在其他数据库比如Oracle数据库，就不好使。

（2）count（1）

（3）count（主键）

（4）count（字段名）

2、avg：平均值

3、sum：求和

4、min：最小值

5、max:最大值

二、数值型函数

主要是对数值型进行处理。

1、ceiling(x)

向上取整

select CEILING(4.1)

2、floor(x)

向下取整

select FLOOR(1.1)

3、round(x)

四舍五入

select ROUND(-4.4)

4、truncate(x,y)

返回数字x截断为y位小数的结果

select TRUNCATE(2.33999999,2);

5、Pl

圆周率, π

6、rand

返回0到1的随机数

select RAND(),RAND(),RAND()

7、abs

绝对值

select ABS(-4) 4的绝对值,ABS(-1.1);

 8、取余

select MOD(60,11);

三、字符串型函数

1、length(s):字符串的长度

2、concat(1.2....)：合并字符串

3、lower(str):将字母转成小写

4、upper(str):将字母转成大写

5、left(str,x):返回字符串str的左边的x个字符

select LEFT('abcdefg',2);

6、right(str,x):返回字符串str右边的x个字符

select RIGHT('abcdefg',2);

7、trim:去掉左右两边的空格

8、replace:替换

select REPLACE('abcdefg','abc','x');

9、substring:截取

10、reverse: 反转

select REVERSE('hijklmn');

四、日期和时间函数

date,time,datetime,timestamp,year.

1、获取时间和日期

（1）[curdate]和[current_ date] , 返回当前的系统日期。

select CURRENT_DATE();

（2）[curtime]和[current_ time] , 返回当前的系统时间。

select CURTIME();

（3）[now]和[sysdate] , 返回当前的系统时间和日期。

select now();

2、时间戳和日期转换函数

（1）[UNIX_ TIMESTAMP] 获取unix时间戳函数

select UNIX_TIMESTAMP();

（2）[FROM_ UNIXTIME]将时间戳转换为时间格式

select FROM_UNIXTIME(1660785720);

3、根据日期获取年月日的数值

（1）获取当前月份

select MONTH(SYSDATE());

（2）获取当前月份的名称

select MONTHNAME (SYSDATE());

（3）获取当前星期几

select DAYNAME (SYSDATE();

（4）获取当前星期几在星期中是第几天

select DAYOFWEEK(SYSDATE());

（5）获取指定日期是年里的第几周

select WEEK(SYSDATE());

（6）获取指定日期在当前月是第几天

select DAYOFMONTH(SYSDATE();

（7）获取当前年份

select YEAR(SYSDATE());

4、时间日期的计算

（1）日期加法

获取当前日期，当前日期加上【DATE_ADD】70天，得到相加后的日期。

select DATE_ADD(SYSDATE(),INTERVAL 70 DAY);

（2）日期减法

获取当前日期，当前日期加上【DATE_SUB】10天，得到相减后的日期。

select DATE_ SUB (SYSDATE(),INTERVAL 10 DAY);

（3）时间间隔

获取两个日期的时间间隔[DATEDIFF]

select DATEDIFF( ' 2023-01-01' , SYSDATE());

（4）日期格式化

获取当前日期转化成自己想要的格式【DATE_FORMAT】

select DATE_FORMAT(SYSDATE(),'%W %M %D %Y');

5、日期格式对应表

五、 加密函数

 把传入的参数的字符串按照【md5算法】进行加密，md5算法是不可逆的，加密之后不可以还原回去，得到一个32位的16进制的字符串

select MD5( '123456');

六、流程控制函数 

1、基本语法

可以进行条件判断，用来实现SQL语句的逻辑。

（1）if(test,t,f)

如果test是真，则返回t,否则返回f

 

（2）ifnull(arg1,arg2)

如果arg1不是空，返回arg1,否则返回arg2

（3）nullif(arg1,arg2)

如果arg1=arg2返回nul,否则返回arg1

2、案例

（1）对一系列的值进行判断

创建一个学生表

INSERT into mystudent (user_name,course,score)
VALUES
( "张三", "数学",34),
( "张三", "语文",58),
( "张三", "英语",58),
( "李四", "数学",69),
( "李四", "语文",70),
( "李四", "英语",55),
( "王五", "数学",85),
( "王五", "语文",77),
( "王五", "英语",64)

 

需求：-- 输出学生的各科的成绩，以及评级，60以下D，60-70是C，71-80是B，80以上是A

SELECT
	*,
CASE
		WHEN score < 60 THEN 'D' 
		WHEN score >= 60 AND score < 70 THEN 'C' 
		WHEN score >= 70 AND score < 80 THEN 'B' 
		WHEN score >= 80 THEN 'A' 
		END AS '评级' 
	FROM
	mystudent;

 

（2）-- 行转列（面试时问得多）

SELECT
	user_name,
	max( CASE course WHEN '数学' THEN score ELSE 0 END ) '数学',
	max( CASE course WHEN '语文' THEN score ELSE 0 END ) '语文',
	max( CASE course WHEN '英语' THEN score ELSE 0 END ) '英语' 
FROM
	mystudent 
GROUP BY
	user_name