mysql_16_常用函数

CREATE DATABASE db_test;
USE db_test;

CREATE TABLE t_t(
id int PRIMARY KEY NOT NULL auto_increment,
birthday date
)

INSERT INTO t_t VALUES(1,"1994-08-21");
INSERT INTO t_t VALUES(2,"1995-04-15");
SELECT * FROM t_t;

-- 第十章:mysql常用函数
-- 第一节:日期和时间函数
-- 1.CURDATE()返回当前日期;
-- 2.CURTIME()返回当前时间;
-- 3.MONTH(date)返回日期date中的月份,范围是:1~12;
SELECT CURDATE() AS 当前日期,CURTIME() AS 当前时间,MONTH(birthday) AS 生日月份 from t_t;

-- 第二节:字符串数据
-- 1.CHAR_LENGTH(str)计算字符串str的字符数;
-- 2.upper(str)把所有字母变成大写字母;
-- 3.LOWER(str)把所有字母变成小写字母;
alter table t_t add userName varchar(20) after birthday;
UPDATE t_t SET userName="CaoFang" WHERE id=1;
UPDATE t_t SET userName="NiMei" WHERE id=2;
SELECT * FROM t_t;

SELECT userName AS 原姓名,CHAR_LENGTH(userName) AS 姓名字符数,UPPER(userName) AS 大写姓名,LOWER(userName) AS 小写姓名 FROM t_t;

-- 第三节:数学函数
-- 1.ABS(X)求绝对值
-- 2.SQRT(X)求平方根
-- 3.MOD(N,M)求余
alter table t_t add num int after userName;
UPDATE t_t SET num=2 WHERE id=1;
UPDATE t_t SET num=-3 WHERE id=2;
SELECT * FROM t_t;

SELECT num,ABS(num) AS 绝对值,SQRT(num) AS 平方根,MOD((SELECT num FROM t_t WHERE id=1),(SELECT num FROM t_t WHERE id=2)) AS 余数 FROM t_t;

-- 第四节:加密函数
alter table t_t add password varchar(100) after num;
SELECT * FROM t_t;

-- 1.PASSWORD(str)一般对用户的密码加密        不可逆
INSERT INTO t_t VALUES (NULL,"2017-08-09","lisi",4,PASSWORD("1"));

-- 2.MD5(str)普通加密        不可逆
INSERT INTO t_t VALUES (NULL,"2017-08-09","lisi",4,MD5("1"));

-- 3.ENCODE(str,pass_str) 加密函数,结果是一个二进制数。必须使用BLOB类型的字段来保存它;
alter table t_t add pp BLOB after password;

INSERT INTO t_t VALUES (NULL,"2017-08-09","lisi",4,NULL,ENCODE("123","aa"));
-- 4.DECODE(crypt_str,pass_str)解密函数;
SELECT pp FROM t_t WHERE id=5;

SELECT DECODE(pp,"aa") FROM t_t WHERE id=5;