MySQL：函数

提醒： 设定下面的语句是在数据库名为 db_book里执行的。
创建user_info表

注意：pwd为密码字段，这里使用了VARCHAR(128)类型，为了后面方便对比，开发项目里一般使用char(32)，SQL语句里使用MD5加密函数

USE db_book;
CREATE TABLE user_info(
	id INT NOT NULL AUTO_INCREMENT,
	userName VARCHAR(30) DEFAULT NULL,
	age INT,
	sex VARCHAR(4) DEFAULT NULL,
	moblie VARCHAR(11) DEFAULT NULL,
	pwd VARCHAR(128) DEFAULT NULL,
	birthday DATE DEFAULT NULL,
	avatar  VARCHAR(100) DEFAULT NULL,
	num INT,
	PRIMARY KEY(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

为user_info表里添加多条数据

USE db_book;
INSERT INTO user_info(userName,age,sex,moblie,pwd,birthday,avatar,num)VALUES('用户1',20,'男',12345678901,123456,'2024-03-01',NULL,1),('用户2',22,'男',13345678902,'123456adc','2023-10-01',NULL,2),('用户3',23,'女',14345678903,'2423adbc','2022-12-08','https://test.avatar.1234abc',3),('Test4',24,'男',13345678902,'123456adc4','2021-04-02',NULL,4123400),('test5',25,'女',15345678902,'123456adc5','2020-11-22',NULL,51234666);
1
2

查询user_info表数据

一、日期和时间函数

1. CURDATE() 返回当前日期
   只包含年、月、日部分，格式为YYYY-MM-DD；
2. CURTIME() 返回当前时间
   只包含时、分、秒部分，格式为HH:MM:SS
3. MONTH（d）返回日期d中的月份值，范里是1~12
4. NOW()函数 返回当前日期和时间
   包含年、月、日、时、分、秒，格式为YYYY-MM-DD HH:MM:SS
5. UNIX_TIMESTAMP(date)函数
   将date转化为UNIX时间戳

SELECT CURDATE(),CURTIME(),NOW(),UNIX_TIMESTAMP(NOW()),MONTH(birthday)FROM db_book.user_info;/*日期和时间函数*/
1

执行结果

UNIX_TIMESTAMP 函数示例
在终端里执行: SELECT UNIX_TIMESTAMP(now());

CURDATE()函数示例
在终端里执行：SELECT CURDATE();


其他几个日期函数执行操作和上面一样

使用AS为列名设置别名

SELECT CURDATE(),CURTIME(),NOW(),MONTH(birthday) AS m FROM db_book.user_info;
1

执行结果

二、字符串函数

1. CHAR_LENGTH(s) 计算字符串s的字符数；
2. UPPER(s) 把所有字母变成大写字母；
3. LOWER(s) 把所有字母变成小写字母；

SELECT userName,CHAR_LENGTH(userName),UPPER(userName),LOWER(userName)FROM db_book.user_info;/*字符串函数*/
1

执行结果

三、数学函数

1. A BS（x）求绝对值
2. SQRT（x）求平方根
3. MOD（x.y）求余

SELECT num,ABS(num) FROM db_book.user_info;/*求绝对值*/
1

3.1 求绝对值

执行结果

SELECT SQRT(num) FROM db_book.user_info;/*对num求平方根*/
1

执行结果

3.2 求平方根

SELECT SQRT(4);/*对4求平方根*/
1

终端里执行结果


MySQL Workbench执行结果

3.3 求余

SELECT MOD(9,2);/*求余*/
1

终端执行结果

四、加密函数

1. PASSWORD（Str） 一般对用户的密码加密 不可逆
   注意：mysql8.0 已经弃用PASSWORD（）函数了，可以使用MD5函数代替
2. MD5（str）普通加密 不可逆
3. ENCODE（SLr，pswd_str）加密函数，结果是一个二进制数，必须使用BLOB 类型的字段来保存它；
   注意：mysql8.0 已经找不到ENCODE（）函数了，可以使用MD5函数代替
4. DECODE（crypt_str，pswd_str）解密函数；
   注意：mysql8.0 已经找不到DECODE（）函数了，可以使用MD5函数代替
5. AES_ENCRYPT 加密
   注意:使用AES加密，MySQL 8.0 版本新增的加密函数
   使用aes_encrypt（）加密后数据为二进制类型，
   需要将对应字段类型设置为二进制类型（blod类型）
   INSERT INTO XXX VALUES(’ AES_ENCRYPT(‘需要加密的数据’,‘秘钥key’)');
6. AES_DECRYPT解密
   注意:使用AES解密，MySQL 8.0 版本新增的解密密函数
   SELECT AES_DECRYPT(列名称,‘秘钥key’) FROM XXX;

4.1 PASSWORD

注意：mysql8.0 已经弃用PASSWORD（）函数了，可以使用MD5函数代替
在mysql8.0以下版本执行下面SQ语句

INSERT INTO db_book.user_info(userName,age,sex,moblie,pwd,birthday,avatar,num)VALUE('用户6',26,'男',16345678906,PASSWORD('123456abcd'),'2024-03-01',NULL,20666);
1

4.2 MD5

INSERT INTO db_book.user_info(userName,age,sex,moblie,pwd,birthday,avatar,num)VALUES('用户7',27,'男',17645678907,MD5('123456abcd'),'1996-06-21',NULL,20670);
1

执行结果


查询user_info表数据

4.3 ENCODE

注意：mysql8.0 已经找不到ENCODE（）函数了，可以使用MD5函数或其他函数代替
在mysql8.0以下版本执行下面SQ语句

INSERT INTO db_book.user_info(userName,age,sex,moblie,pwd,birthday,avatar,num)
VALUES('用户8',28,'男',17645678907,MD5('23456ad'),'1995-06-21',ENCODE('https://abcd.avatar','ak'),20680);
1
2

4.4 DECODE

注意：mysql8.0 已经找不到DECODE（）函数了，可以使用MD5函数或其他代替
在mysql8.0以下版本执行下面SQ语句

SELECT DECODE(avatar,'ak') FROM db_book.user_info WHERE num = 20680;
1

4.5 AES_ENCRYPT

给user_info表新增pwd2字段

ALTER TABLE db_book.user_info ADD pwd2 blob;
1

给user_info表里添加一条数据

INSERT INTO db_book.user_info(userName,age,sex,moblie,pwd,birthday,avatar,num,pwd2)
VALUES('用户8',28,'男',17645678907,MD5('23456ad'),'1995-06-21',NULL,20680,AES_ENCRYPT('数据abcd','ak123'));
1
2

查询user_info表数据

注意：当字段存储的是二进制数据时，MySQL会将其显示为BLOB

下面修改MySQL Workbench默认设置，将其可以直接查看blob类型的值


由于在自己的mac上面的修改设置后未生效, 下面在终端里执行查询

4.6 AES_DECRYPT

查询user_info表数据，并使用AES_DECRYPT对pwd2列值进行解密

SELECT userName,age,sex,moblie,pwd,birthday,avatar,num,pwd2, AES_DECRYPT(`pwd2`,'ak123') FROM db_book.user_info;
1

执行结果

推荐

MySQL：常用的SQL语句

欢迎关注我的公众号，不定期推送优质的文章，
微信扫一扫下方二维码即可关注。