MySQL函数

MySql常见的函数

使用MySql函数的目的

为了简化操作，MySql提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用now()函数）

函数可以出现的位置

插入语句的values()中，更新语句中，删除语句中，查询语句及其子句中。

环境准备

-- 用户表
CREATE TABLE t_user (
  id int(11) NOT NULL AUTO_INCREMENT,
  uname varchar(40) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

insert  into t_user values (null,'zs',18,1);
insert  into t_user values (null,'ls',20,0);
insert  into t_user values (null,'ww',23,1);
insert  into t_user values (null,'zl',24,1);
insert  into t_user values (null,'lq',15,0);
insert  into t_user values (null,'hh',12,0);
insert  into t_user values (null,'wzx',60,null);
insert  into t_user values (null,'lb',null,null);

if相关函数

if(expr1,expr2,expr3)  true  1 : 真  ，0 ： 假

​ 说明： 如果 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3。if() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

示例

练习1：获取用户的姓名、性别，如果性别为1则显示1，否则显示0；要求使用if函数查询：

SELECT uname, IF(sex, 1, 0) FROM t_user;

ifnull函数

ifnull(expr1,expr2)

说明：假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。ifnull()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

示例

练习1：获取用户的姓名、性别，如果性别为null则显示为1；要求使用ifnull函数查询：

SELECT uname, IFNULL(sex, 1) FROM t_user;

字符串函数

字符串连接函数主要有2个：

函数或操作符	描述
concat(str1, str2, …)	字符串连接函数，可以将多个字符串进行连接
concat_ws(separator, str1, str2, …)	可以指定间隔符将多个字符串进行连接；

练习1：使用concat函数显示出 你好,uname 的结果

SELECT CONCAT('你好,' , uname) FROM t_user;

练习2：使用concat_ws函数显示出 你好,uname 的结果

SELECT CONCAT_WS(',', '你好', uname) FROM t_user;

字符串大小写处理函数

字符串大小写处理函数主要有2个：

函数或操作符	描述
upper(str)	得到str的大写形式
lower(str)	得到str的小写形式

练习1： 将字符串 hello 转换为大写显示

SELECT UPPER('hello'); -- HELLO

练习2：将字符串 heLLo 转换为小写显示

SELECT LOWER('heLLo'); -- hello

移除空格函数

可以对字符串进行按长度填充满、也可以移除空格符

函数或操作符	描述
trim(str)	将str两边的空白符移除

练习1： 将用户id位8的用户的姓名的两边空白符移除

-- 表中数据是：'      lb   ', 使用trim后是： 'lb'
SELECT TRIM(uname) FROM t_user  WHERE id = 8; 

子串函数

字符串也可以按条件进行截取，主要有以下可以截取子串的函数;

函数或操作符	描述
substr()、substring()	获取子串： 1：substr(str, pos) 、substring(str, pos)； 2：substr(str, pos, len)、substring(str, pos, len)

练习1：获取 hello,world 从第二个字符开始的完整子串

SELECT SUBSTR("hello,world", 2);  -- ello,world

练习2：获取 hello,world 从第二个字符开始但是长度为4的子串

SELECT SUBSTR("hello,world", 2, 4); -- ello

小结

1. 拼接字符串

concat( 字符串1 , 字符串2 , ...)
concat_ws(分隔符, 字符串1 , 字符串2 , ...)

2. 处理大小写的

upper()
lower()

3. 移除空格

trim() # 默认删除前后的空格

4. 截取字符串

substr(字符串 , 数字1 ) # 切割字符串，从数字1开始切割，默认下标从1开始
substr(字符串 , 数字1  , 数字2) # 切割字符串，从数字1开始切割，数字2代表切割字符的个数

substring()

时间日期函数

mysql提供了一些用于获取特定时间的函数：

函数或操作符	描述
current_date()	获取当前日期，如 2019-10-18
current_time()	获取当前时：分：秒，如：15:36:11
now()	获取当前的日期和时间，如：2019-10-18 15:37:17

练习1：获取当前的日期(年月日)

select cruuent_date();

练习2： 获取当前的时间（仅仅需要时分秒）

select current_time();

练习3： 获取当前时间（包含年月日时分秒）

select now();

数值函数

常见的数值相关函数如下表:

函数或操作符	描述
abs(x)	获取数值x的绝对值
ceil(x)	向上取整，获取不小于x的整数值
floor(x)	向下取整，获取不大于x的整数值
pow(x, y)	获取x的y次幂
rand()	获取一个0-1之间的随机浮点数

练习1： 获取 -12 的绝对值

select abs(-12);

练习2： 将 -11.2 向上取整

select ceil(-11.2);

练习3： 将 1.6 向下取整

select floor(1.6);

练习4： 获得2的32次幂的值

select pow(2, 32);

练习5： 获得一个在0-100之间的随机数

select rand()*100;

MySQL函数练习

# if 函数  IF(字段, aa , bb) 如果字段为真，则显示aa, 否则显示bb

# select if(sex , 1 , 0 )from t_user;
SELECT uname , IF(sex , '男' , '女') AS  '性别' FROM t_user;



# ifnull 函数 ifnull(字段, aa) ,如果字段不为null，就返回它自己，如果字段为null 则返回aa
#select uname ,ifnull(age , 100) from t_user;
SELECT uname ,IFNULL(sex , '未知性别') FROM t_user;


# -------------------------字符串函数-----------------------------

# concat 拼接字符串 CONCAT(字符串1 , 字符串2, ...) 后面还可以拼接很多
SELECT CONCAT('aa' , '123' , '456');

# concat_ws 拼接字符串 , 可以传递分隔符
SELECT CONCAT_WS('#' , 'aa' , 'bb', 'cc');
SELECT CONCAT_WS(',' , '你好' , uname) FROM t_user;


#大小写变化函数 upper  lower
SELECT UPPER(uname) FROM t_user;
SELECT LOWER(uname) FROM t_user;


# 去除字符串的前后空格 问： 中间的空格能去掉吗？？ 默认去掉前后的空格
SELECT TRIM(uname) FROM t_user;


SELECT TRIM(LEADING uname) FROM t_user;

# 切割字符串 substr(字符串 , 数字) 数字， 数字表示从什么位置开始切割，位置从1开始
# 默认切割刀字符串的末尾
SELECT SUBSTR('abc' , 2) ;

# 第二个数字代表从什么位置开始切割
# 第三个数字代表的是切割的字符长度(个数)
SELECT SUBSTR('abc123', 1 , 2);

SELECT SUBSTRING(uname , 1, 1) FROM t_user;




# 时间函数

-- 显示日期
select CURRENT_DATE;
-- 显示时间
select CURRENT_TIME;
-- 显示日期和时间
select now();


-- 数值函数
-- abs(x)   获取数值x的绝对值            
select abs(-10)

-- ceil(x)  向上取整，获取不小于x的整数值
select ceil(-1.1)

-- floor(x) 向下取整，获取不大于x的整数值
select floor(-1.1)


-- pow(x, y)获取x的y次幂   
select pow(2,0)

-- random
-- rand()   获取一个0-1之间的随机浮点数  
select rand() * 10

练习1： 获取 -12 的绝对值
select abs(-12)