mysql_16_常用函数
时间：2021-07-01 10:21:17
帮助过：5人阅读
CREATE DATABASE db_test;
2 USE db_test;
3
4 CREATE TABLE t_t(
5 id
int PRIMARY KEY NOT NULL auto_increment,
6 birthday date
7 )
8
9 INSERT INTO t_t
VALUES(
1,"
1994-08-21");
10 INSERT INTO t_t
VALUES(
2,"
1995-04-15");
11 SELECT * FROM t_t;
12
13 -- 第十章:mysql常用函数
14 -- 第一节:日期和时间函数
15 -- 1.CURDATE()返回当前日期;
16 -- 2.CURTIME()返回当前时间;
17 -- 3.MONTH(date)返回日期date中的月份,范围是:1~12;
18 SELECT CURDATE()
AS 当前日期,CURTIME()
AS 当前时间,
MONTH(birthday)
AS 生日月份
from t_t;
19
20 -- 第二节:字符串数据
21 -- 1.CHAR_LENGTH(str)计算字符串str的字符数;
22 -- 2.upper(str)把所有字母变成大写字母;
23 -- 3.LOWER(str)把所有字母变成小写字母;
24 alter table t_t
add userName
varchar(
20) after birthday;
25 UPDATE t_t
SET userName
="CaoFang"
WHERE id
=1;
26 UPDATE t_t
SET userName
="NiMei"
WHERE id
=2;
27 SELECT * FROM t_t;
28
29 SELECT userName
AS 原姓名,CHAR_LENGTH(userName)
AS 姓名字符数,
UPPER(userName)
AS 大写姓名,
LOWER(userName)
AS 小写姓名
FROM t_t;
30
31 -- 第三节:数学函数
32 -- 1.ABS(X)求绝对值
33 -- 2.SQRT(X)求平方根
34 -- 3.MOD(N,M)求余
35 alter table t_t
add num
int after userName;
36 UPDATE t_t
SET num
=2 WHERE id
=1;
37 UPDATE t_t
SET num
=-3 WHERE id
=2;
38 SELECT * FROM t_t;
39
40 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;
41
42 -- 第四节:加密函数
43 alter table t_t
add password
varchar(
100) after num;
44 SELECT * FROM t_t;
45
46 -- 1.PASSWORD(str)一般对用户的密码加密 不可逆
47 INSERT INTO t_t
VALUES (
NULL,"
2017-08-09","lisi",
4,PASSWORD("
1"));
48
49 -- 2.MD5(str)普通加密 不可逆
50 INSERT INTO t_t
VALUES (
NULL,"
2017-08-09","lisi",
4,MD5("
1"));
51
52 -- 3.ENCODE(str,pass_str) 加密函数,结果是一个二进制数。必须使用BLOB类型的字段来保存它;
53 alter table t_t
add pp BLOB after password;
54
55 INSERT INTO t_t
VALUES (
NULL,"
2017-08-09","lisi",
4,
NULL,ENCODE("
123","aa"));
56 -- 4.DECODE(crypt_str,pass_str)解密函数;
57 SELECT pp
FROM t_t
WHERE id
=5;
58
59 SELECT DECODE(pp,"aa")
FROM t_t
WHERE id
=5;
mysql_16_常用函数
标签：table upper 函数 用户 保存 密码 from 加密 nbsp
