英语单词
interval 间隔
7. 函数
7.1 日期函数
函数名称 | 描述 |
---|---|
current_date() | 当前日期 |
current_time() | 当前时间 |
current_timestamp | 当前时间戳 |
date(datetime) | 返回datetime 参数的日期部分 |
date_add(date, interval, d_value_type) | 在date 中添加日期或时间interval 后面的数值可以是:year minute second day |
date_sub(date, interval d_value_type) | 在date 中减去日期或时间 interval 后的数值单位可以是: year minute second day |
datediff(date1, date2) | 两个日期的查,单位是天 |
now() | 当前日期时间 |
# date 日期 datetime 日期加时间 timestamp 自动更新的日期和时间
MariaDB [scott]> select current_time();
+----------------+
| current_time() |
+----------------+
| 11:54:05 |
+----------------+
1 row in set (0.00 sec)
MariaDB [scott]> select date_add('2022-12-30', interval 44 day);
+-----------------------------------------+
| date_add('2022-12-30', interval 44 day) |
+-----------------------------------------+
| 2023-02-12 |
+-----------------------------------------+
1 row in set (0.00 sec)
# 当然我们也可以加上一个负数
MariaDB [scott]> select date_add('2022-12-30', interval -44 day);
+------------------------------------------+
| date_add('2022-12-30', interval -44 day) |
+------------------------------------------+
| 2022-11-16 |
+------------------------------------------+
1 row in set (0.00 sec)
# 计算两个日期中间间隔的天数
MariaDB [scott]> select datediff('1949-10-1', '2022-12-30');
+-------------------------------------+
| datediff('1949-10-1', '2022-12-30') |
+-------------------------------------+
| -26753 |
+-------------------------------------+
1 row in set (0.00 sec)
接下来创建一个表演示一下
MariaDB [table_function]> create table if not exists tmp(
-> id int unsigned primary key auto_increment,
-> birthday date not null);
Query OK, 0 rows affected (0.01 sec)
MariaDB [table_function]> desc tmp
-> ;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| birthday | date | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 我们既可以手动写入values的值
MariaDB [table_function]> insert into tmp (birthday) values ('1980-09-09');
Query OK, 1 row affected (0.00 sec)
#也可以使用函数的返回值
MariaDB [table_function]> insert into tmp (birthday) values ('current_date()');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [table_function]> insert into tmp (birthday) values (current_date());
Query OK, 1 row affected (0.01 sec)
MariaDB [table_function]> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 1980-09-09 |
| 2 | 0000-00-00 |
| 3 | 2022-12-30 |
+----+------------+
3 rows in set (0.00 sec)
# 使用函数插入不需要加'' 不然就会变成2这样
# 构建一个信息列表
MariaDB [table_function]> create table msgl( id int unsigned primary key auto_increment, content varchar(30) not null, sendtime datetime comment '日期 + 时间' );
Query OK, 0 rows affected (0.02 sec)
MariaDB [table_function]> desc msgl;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| content | varchar(30) | NO | | NULL | |
| sendtime | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [table_function]> insert into msgl (content, sendtime) values ('tw是中国不可分隔的一部分!', '2022-01-01 12:34:43');
Query OK, 1 row affected (0.00 sec)
MariaDB [table_function]> insert into msgl (content, sendtime) values ('tw是中国不可分隔!', now());
Query OK, 1 row affected (0.00 sec)
MariaDB [table_function]> select * from msgl;
+----+--------------------------------------+---------------------+
| id | content | sendtime |
+----+--------------------------------------+---------------------+
| 1 | tw是中国不可分隔的一部分! | 2022-01-01 12:34:43 |
| 2 | tw是中国不可分隔! | 2022-12-30 12:21:34 |
+----+--------------------------------------+---------------------+
2 rows in set (0.00 sec)
MariaDB [table_function]> create table if not exists msg2( id int unsigned primary key auto_increment, content varchar(40) not null, sendtime timestamp);
Query OK, 0 rows affected (0.01 sec)
MariaDB [table_function]> desc msg2;
+----------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| content | varchar(40) | NO | | NULL | |
| sendtime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+------------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
MariaDB [table_function]> insert into msg2 (content) values ('first message');
Query OK, 1 row affected (0.00 sec)
# timestamp类型会给我们自动添加时间日期信息
MariaDB [table_function]> select * from msg2;
+----+---------------+---------------------+
| id | content | sendtime |
+----+---------------+---------------------+
| 1 | first message | 2022-12-30 14:33:47 |
+----+---------------+---------------------+
1 row in set (0.00 sec)
查询两分钟内发布的贴子
MariaDB [table_function]> insert into msg2 (content) values ('third message');
Query OK, 1 row affected (0.01 sec)
MariaDB [table_function]> select * from msg2 where date_add(sendtime, interval 2 minute) >= now();
+----+---------------+---------------------+
| id | content | sendtime |
+----+---------------+---------------------+
| 3 | third message | 2022-12-30 14:40:09 |
+----+---------------+---------------------+
1 row in set (0.00 sec)
7.2字符串函数
函数 | 描述 |
---|---|
charset(str) | 返回字符串字符集 |
concat(string2 [, ...]) | 连接字符串 |
instr(string, substring) | 返回substring 在string 中出现的未知 |
ucase(string2) | 转换大写 |
lcase(string2) | 转换成小写 |
left(string2, length) | 从string2 中左边起取length 个字符 |
length(string) | string 的长度 |
replace(str, search_str, replace_str) | 在str 中用replace_str 替换search_str |
strcmp(string1, string2) | 逐个字符比较字符串大小 |
substring(str, position[, length]) | 从str 的postion 开始,取length 个字符 |
ltrim(string) rtrim(string) trim(string) | 取出前空格和后空格 |
案例:
-
获取
msg2
表中content
字段的字符集MariaDB [table_function]> select charset(content) from msg2; +------------------+ | charset(content) | +------------------+ | utf8 | | utf8 | | utf8 | +------------------+ 3 rows in set (0.00 sec)
-
拼接字符串
MariaDB [table_function]> select concat('你好', '世界') msg; +--------------+ | msg | +--------------+ | 你好世界 | +--------------+ 1 row in set (0.00 sec)
-
求学生表中学生姓名占用的字节数
select length(name) from students;
-
替换字符串
select replace('hello world', 'world', 'linux'); +------------------------------------------+ | replace('hello world', 'world', 'linux') | +------------------------------------------+ | hello linux | +------------------------------------------+ 1 row in set (0.00 sec)
-
以首字母小写的方式显示所有员工的姓名
select concat(lcase(substring(ename,1,1)), substring(ename,2)) from EMP;
7.3数学函数
函数 | 描述 |
---|---|
abs(number) | 绝对值函数 |
bin(decimal_number) | 十进制转换二进制 |
hex(decimalNumber) | 十进制转换十六进制 |
conv(number,from_base, to_base) | 进制转换 |
ceiling(number) | 向上取整 |
floor(number) | 向下取整 |
format(number,decimal_places) | 格式化,保留小数位数 |
hex(decimalNumber) | 转化成十六进制 |
rand() | 返回随机浮点数,范围[0.0. 1.0) |
mod(number,denominator) | 取模,求余 |
7.4其它函数
-
user()
查询当前用户 -
md5(str)
对一个字符串进行md5
摘要,摘要后得到一个32位字符串 -
database()
显示当前正在使用的数据库 -
password()
MySQL使用这个函数对数据库进行加密 -
ifnull(val1, val2)
如果val1 == null
返回val2
否则返回val1