中国建设的网站,企业网页建设公司24小时接单,无为网页定制,青岛网站建设加王道下拉文章目录 日期函数获取日期日期计算 字符串函数charsetconcatlengthsubstringreplaceinstrstrcmpltrim, rtrim, trim 数学函数absbin, hexconvceiling, floorrandformatmod 其他函数user() 查询当前用户密码加密md5()password() database() 查看当前数据库ifnull() 日期函数
函… 文章目录 日期函数获取日期日期计算 字符串函数charsetconcatlengthsubstringreplaceinstrstrcmpltrim, rtrim, trim 数学函数absbin, hexconvceiling, floorrandformatmod 其他函数user() 查询当前用户密码加密md5()password() database() 查看当前数据库ifnull() 日期函数
函数说明current_date()当前日期current_time()当前时间current_timestamp()当前时间戳date(datetime)返回 datetime 参数的日期部分date_add(date, interval d_value_type)在 date 中添加日期或时间interval 后的数值单位可以是year minute second daydate_sub(date, interval d_value_type)在 date 中减去日期或时间interval 后的数值单位可以是year minute second daydatediff(date1, date2)两个日期的差单位为天now()当前日期时间
获取日期
例
MariaDB [(none)] select current_date();
----------------
| current_date() |
----------------
| 2023-04-18 |
----------------
1 row in set (0.00 sec)MariaDB [(none)] select current_time();
----------------
| current_time() |
----------------
| 22:47:01 |
----------------
1 row in set (0.00 sec)MariaDB [(none)] select current_timestamp();
---------------------
| current_timestamp() |
---------------------
| 2023-04-18 22:55:14 |
---------------------
1 row in set (0.00 sec)创建一个生日表
CREATE TABLE birthday (birth_date DATE,create_time TIMESTAMP
);MariaDB [test_db] desc birthday;
-----------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------------------------------
| birth_date | date | YES | | NULL | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
-----------------------------------------------------------------------------------
2 rows in set (0.00 sec)插入数据
-- 手动插入一个指定的日期
MariaDB [test_db] insert into birthday (birth_date) values (1919-08-10);
Query OK, 1 row affected (0.00 sec)-- 使用函数插入当前日期
MariaDB [test_db] insert into birthday (birth_date) values (current_date); -- current_date可不加圆括号
Query OK, 1 row affected (0.00 sec)-- 查看结果
MariaDB [test_db] select * from birthday;
---------------------------------
| birth_date | create_time |
---------------------------------
| 1919-08-10 | 2023-04-18 23:04:12 |
| 2023-04-18 | 2023-04-18 23:06:07 |
---------------------------------
2 rows in set (0.00 sec)date() 获取 datetime 类型的日期部分
MariaDB [test_db] select date(now());
-------------
| date(now()) |
-------------
| 2023-04-18 |
-------------
1 row in set (0.00 sec)日期计算
日期时间
MariaDB [test_db] select date_add(1919-08-10, interval 10 day);
-----------------------------------------
| date_add(1919-08-10, interval 10 day) |
-----------------------------------------
| 1919-08-20 |
-----------------------------------------
1 row in set (0.00 sec)日期-时间
MariaDB [test_db] select date_sub(1919-08-10, interval 10 day);
-----------------------------------------
| date_sub(1919-08-10, interval 10 day) |
-----------------------------------------
| 1919-07-31 |
-----------------------------------------
1 row in set (0.00 sec)日期-日期
MariaDB [test_db] select datediff(now(), 1919-08-10);
-------------------------------
| datediff(now(), 1919-08-10) |
-------------------------------
| 37872 |
-------------------------------
1 row in set (0.00 sec)例
create table msg (id int unsigned primary key auto_increment,content varchar(100) not null,sendtime datetime
);字符串函数
函数说明charset(str)返回字符串字符集concat(str1, str2, ...)连接字符串instr(str, substr)返回 substr 在 str 中出现的位置没有则返回0ucase(str)(MySQL 特有) upper(str)(标准 SQL 定义)转换转换为大写lcase(str)(MySQL 特有) lower(str) (标准 SQL 定义)转换转换为小写left(str, len); right(str, len)从 str 左起取 len 个字符; 从 str 右起取 len 个字符length(str)str 的长度replace(str, search_str, replace_str)在 str 中用 replace_str 替换 search_strstrcmp(str1, str2)比较两个字符串substring(str, pos[, len])从 str 的 pos 位置下标从 1 开始开始取 len 个字符ltrim(str) rtrim(str) trim(str)去除前空格或后空格
例
charset
MariaDB [(none)] select charset(abc);
----------------
| charset(abc) |
----------------
| utf8 |
----------------
1 row in set (0.03 sec)MariaDB [scott] select charset(ename) from emp;
----------------
| charset(ename) |
----------------
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
----------------
14 rows in set (0.04 sec)concat
MariaDB [scott] select concat(Hello, , world, 123); -- 也可以传入数字会被函数看作字符串
------------------------------------
| concat(Hello, , world, 123) |
------------------------------------
| Hello world123 |
------------------------------------
1 row in set (0.00 sec)MariaDB [scott] select concat(My name is , ename) from emp;
------------------------------
| concat(My name is , ename) |
------------------------------
| My name is SMITH |
| My name is ALLEN |
| My name is WARD |
| My name is JONES |
| My name is MARTIN |
| My name is BLAKE |
| My name is CLARK |
| My name is SCOTT |
| My name is KING |
| My name is TURNER |
| My name is ADAMS |
| My name is JAMES |
| My name is FORD |
| My name is MILLER |
------------------------------
14 rows in set (0.00 sec)length
MariaDB [scott] select length(ename) from emp;
---------------
| length(ename) |
---------------
| 5 |
| 5 |
| 4 |
| 5 |
| 6 |
| 5 |
| 5 |
| 5 |
| 4 |
| 6 |
| 5 |
| 5 |
| 4 |
| 6 |
---------------
14 rows in set (0.00 sec)注意 length 返回的长度是按字节为单位的
MariaDB [scott] select length(你好);
------------------
| length(你好) |
------------------
| 6 |
------------------
1 row in set (0.00 sec)substring
substring(str, pos[, len])str 是要提取子字符串的原始字符串pos 是子字符串的起始位置len 是要提取的子字符串的长度。
如果省略 len 参数则将返回从 pos 位置开始到原始字符串的末尾的所有字符。如果 pos 参数为负数则 SUBSTRING 函数将从字符串的末尾开始计数。
MariaDB [(none)] select substring(123456, 2);
------------------------
| substring(123456, 2) |
------------------------
| 23456 |
------------------------
1 row in set (0.00 sec)MariaDB [(none)] select substring(123456, 2, 2);
---------------------------
| substring(123456, 2, 2) |
---------------------------
| 23 |
---------------------------
1 row in set (0.00 sec)replace
MariaDB [(none)] select replace(abcxyz1234, xyz, ddd);
-------------------------------------
| replace(abcxyz1234, xyz, ddd) |
-------------------------------------
| abcddd1234 |
-------------------------------------
1 row in set (0.00 sec)把所有人名首字母转小写
MariaDB [scott] select concat(lower(left(ename, 1)), substring(ename, 2)) result from emp;
--------
| result |
--------
| sMITH |
| aLLEN |
| wARD |
| jONES |
| mARTIN |
| bLAKE |
| cLARK |
| sCOTT |
| kING |
| tURNER |
| aDAMS |
| jAMES |
| fORD |
| mILLER |
--------
14 rows in set (0.00 sec)instr
注instr 查找忽略大小写的
MariaDB [scott] select instr(abcxyz1234, xyz);
----------------------------
| instr(abcxyz1234, xyz) |
----------------------------
| 4 |
----------------------------
1 row in set (0.00 sec)-- 查询名字里有 th 的人
MariaDB [scott] select ename from emp where instr(ename, th);
-------
| ename |
-------
| SMITH |
-------
1 row in set (0.00 sec)-- 此方法等价于使用 like
MariaDB [scott] select ename from emp where ename like %th%;
-------
| ename |
-------
| SMITH |
-------
1 row in set (0.00 sec)注意
聚合函数不可以在where字句中使用而上述的日期函数字符串函数可以。
strcmp
MariaDB [scott] select strcmp(abcd, abcd), strcmp(aab, abcd), strcmp(abcd, aab);
----------------------------------------------------------------------
| strcmp(abcd, abcd) | strcmp(aab, abcd) | strcmp(abcd, aab) |
----------------------------------------------------------------------
| 0 | -1 | 1 |
----------------------------------------------------------------------
1 row in set (0.00 sec)ltrim, rtrim, trim
ltrim: 去掉前缀空格
rtrim: 去掉后缀空格
trim: 去掉前后缀空格。
例
MariaDB [scott] select ltrim( Hello world ) res;
-----------------
| res |
-----------------
| Hello world |
-----------------
1 row in set (0.00 sec)MariaDB [scott] select rtrim( Hello world ) res;
-----------------
| res |
-----------------
| Hello world |
-----------------
1 row in set (0.00 sec)MariaDB [scott] select trim( Hello world ) res;
-------------
| res |
-------------
| Hello world |
-------------
1 row in set (0.00 sec)数学函数
函数说明abs(N)绝对值bin(N)十进制转二进制hex(N)十进制转十六进制conv(N, from_base, to_base)进制转换ceiling(N)向上取整floor(N)向下取整format(N, D)格式化为带有千位分隔符的字符串保留 D 位小数rand()返回随机浮点数范围 [ 0.0 , 1.0 ) [0.0, 1.0) [0.0,1.0)mod(N, M)N 除以 M 的余数
abs
MariaDB [scott] select abs(-10);
----------
| abs(-10) |
----------
| 10 |
----------
1 row in set (0.00 sec)bin, hex
MariaDB [scott] select bin(10);
---------
| bin(10) |
---------
| 1010 |
---------
1 row in set (0.01 sec)MariaDB [scott] select hex(10);
---------
| hex(10) |
---------
| A |
---------
1 row in set (0.00 sec)conv
conv(N, from_base, to_base)N 是要进行进制转换的数字
from_base 是原数值的进制
to_base 是目标数值的进制。
MariaDB [scott] select conv(1A, 16, 10);
--------------------
| conv(1A, 16, 10) |
--------------------
| 26 |
--------------------
1 row in set (0.00 sec)MariaDB [scott] select conv(10, 3, 10);
-----------------
| conv(10, 3, 10) |
-----------------
| 3 |
-----------------
1 row in set (0.00 sec)MariaDB [scott] select conv(10, 3, 2);
----------------
| conv(10, 3, 2) |
----------------
| 11 |
----------------
1 row in set (0.01 sec)ceiling, floor
MariaDB [scott] select ceiling(3.1);
--------------
| ceiling(3.1) |
--------------
| 4 |
--------------
1 row in set (0.00 sec)MariaDB [scott] select ceiling(-3.1);
---------------
| ceiling(-3.1) |
---------------
| -3 |
---------------
1 row in set (0.00 sec)MariaDB [scott] select floor(3.1);
------------
| floor(3.1) |
------------
| 3 |
------------
1 row in set (0.00 sec)MariaDB [scott] select floor(-3.1);
-------------
| floor(-3.1) |
-------------
| -4 |
-------------
1 row in set (0.00 sec)rand
MariaDB [scott] select rand();
--------------------
| rand() |
--------------------
| 0.3416242546789574 |
--------------------
1 row in set (0.00 sec)format
MariaDB [scott] select format(rand()*10000, 1);
-------------------------
| format(rand()*10000, 1) |
-------------------------
| 2,683.2 |
-------------------------
1 row in set (0.00 sec)mod
MariaDB [scott] select mod(10, 3);
------------
| mod(10, 3) |
------------
| 1 |
------------
1 row in set (0.00 sec)其他函数
user() 查询当前用户
MariaDB [scott] select user();
----------------
| user() |
----------------
| rootlocalhost |
----------------
1 row in set (0.00 sec)密码加密
md5()
MD5 是一种哈希函数用于将任意长度的字符串转换为固定长度的散列值。它可以用于安全地存储和比较密码等敏感数据因为通过计算哈希值即使在数据库中存储的是哈希值也可以在需要验证密码时进行比较而无需直接存储密码本身。
MariaDB [scott] select md5(hello world);
----------------------------------
| md5(hello world) |
----------------------------------
| 5eb63bbbe01eeed093cb22bb8f5acdc3 |
----------------------------------
1 row in set (0.00 sec)例
在数据库中像密码这样的敏感数据不可以明文存储否则一旦数据库泄漏用户的账户和密码就都泄漏了。所以数据库中的密码往往是加密过的。
创建一个包含用户名和密码字段的表并向其中插入6条数据每次插入数据时都要使用 MD5 函数对密码进行哈希加密以确保其安全性
CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,username VARCHAR(50) NOT NULL,password VARCHAR(32) NOT NULL,PRIMARY KEY (id)
);INSERT INTO users (username, password) VALUES (张小凡, MD5(b3UyDnT7));
INSERT INTO users (username, password) VALUES (王小磊, MD5(RvSgFpK4));
INSERT INTO users (username, password) VALUES (李婷婷, MD5(XcNjZmH9));
INSERT INTO users (username, password) VALUES (赵丽丽, MD5(qLwEeT2G));
INSERT INTO users (username, password) VALUES (刘伟东, MD5(a6fVdP8S));
INSERT INTO users (username, password) VALUES (陈冬梅, MD5(t4JkMxY1));MariaDB [test_db] select * from users;
-------------------------------------------------
| id | username | password |
-------------------------------------------------
| 7 | 张小凡 | 1329ebf6352bb67d4e2f6e2024d2f72c |
| 8 | 王小磊 | 6e49be674cee12dc2f333296c675db49 |
| 9 | 李婷婷 | 135abaf36b2e5803c97a6db4c1fe83c6 |
| 10 | 赵丽丽 | 45791c398b4dab808a8c1ae2bf8caaac |
| 11 | 刘伟东 | a0aada9b4933edd0f5da12c3194b6d5f |
| 12 | 陈冬梅 | b410ad83ff45a3f9ec91bb8ddbdb4096 |
-------------------------------------------------
6 rows in set (0.00 sec)查询李婷婷和她的密码 XcNjZmH9 是否匹配
MariaDB [test_db] select * from users where username李婷婷 and passwordmd5(XcNjZmH9);
-------------------------------------------------
| id | username | password |
-------------------------------------------------
| 9 | 李婷婷 | 135abaf36b2e5803c97a6db4c1fe83c6 |
-------------------------------------------------
1 row in set (0.00 sec)这样即使数据库泄漏攻击者也无法轻易地解密密码。
注意
由于 MD5 散列算法已经被证明存在一些安全漏洞不再建议将其用于新的安全应用程序中。如果你需要更高的安全性建议使用 SHA-256 或 SHA-512 等更强大的哈希算法。
password()
在 MySQL 中PASSWORD 通常用于加密用户的密码。这个函数使用 MySQL 自己的哈希算法可以将一个字符串转换成一个不可逆的字符串通常用于存储用户密码的安全散列值。
MariaDB [test_db] select password(hello world);
-------------------------------------------
| password(hello world) |
-------------------------------------------
| *67BECF85308ACF0261750DA1075681EE5C412F05 |
-------------------------------------------
1 row in set (0.00 sec)password() 的安全性比 md5() 略高一些。
database() 查看当前数据库
查看当前正在使用的数据库名称。
如果当前没有连接到任何数据库调用该函数将返回 NULL 值。
MariaDB [test_db] select database();
------------
| database() |
------------
| test_db |
------------
1 row in set (0.00 sec)在这种情况下没什么用因为我的命令行提示符已经显示了当前正在使用的数据库名称
ifnull()
语法
IFNULL(expr1, expr2)expr1 是要判断的表达式expr2 是默认值。如果 expr1 不为 NULL则返回 expr1否则返回 expr2。
例
假设你有一个包含商品价格的表但某些商品的价格尚未确定因此为 NULL你可以使用 IFNULL 函数来将这些商品的价格设置为默认值如下所示
SELECT product_name, IFNULL(price, 0) AS price
FROM products;在上面的查询中如果 price 字段不为 NULL则返回 price 字段的值否则返回 0。
