字符函数
length 获取字符串长度
select length('huyongjian') length;
+--------+
| length |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)
concat 连接字符串
select concat(id,'_',name) as new_name from student;
+--------------+
| new_name |
+--------------+
| 1_张三 |
| 2_王五 |
| 3_李四 |
| 4_小红 |
| 5_小花 |
| 6_huyongjian |
| 7_Join |
+--------------+
7 rows in set (0.00 sec)
upper 转换成大写
select upper('huyongjian') upper;
+------------+
| upper |
+------------+
| HUYONGJIAN |
+------------+
1 row in set (0.00 sec)
lower 转换成小写
select lower('HuYongJian') upper;
+------------+
| upper |
+------------+
| huyongjian |
+------------+
1 row in set (0.00 sec)
substr 截取字符串
select substr('小红是一个小学生',6,3) as substr;
+-----------+
| substr |
+-----------+
| 小学生 |
+-----------+
1 row in set (0.00 sec)
instr 查找子字符串位置
select instr('小红今年18岁','18') instr;
+-------+
| instr |
+-------+
| 5 |
+-------+
1 row in set (0.00 sec)
trim 去除字符串前后空格
select trim(' huyongjian ') as out_put;
+------------+
| out_put |
+------------+
| huyongjian |
+------------+
1 row in set (0.00 sec)
lpad 用指定字符左填充
select lpad('胡勇健',5,'*') as out_put;
+-------------+
| out_put |
+-------------+
| **胡勇健 |
+-------------+
1 row in set (0.00 sec)
rpad 用指定字符右填充
select rpad('胡勇健',5,'*') as out_put;
+-------------+
| out_put |
+-------------+
| 胡勇健** |
+-------------+
1 row in set (0.00 sec)
replace 用指定字符串替换查找的字符串
select replace('小红是男生','男生','女生') as out_put;
+-----------------+
| out_put |
+-----------------+
| 小红是女生 |
+-----------------+
1 row in set (0.00 sec)
md5 字符串md5加密
select md5('huyongjian') as ciphertext;
+----------------------------------+
| ciphertext |
+----------------------------------+
| 4d8b497cedde8a5bcc039637b6da1710 |
+----------------------------------+
1 row in set (0.00 sec)
sha 字符串sha加密
select sha('huyongjian') password;
+------------------------------------------+
| password |
+------------------------------------------+
| eb08a98ebc630c80683c5d259bc22598cb62008e |
+------------------------------------------+
1 row in set (0.00 sec)
数学函数
round 四舍五入取整或保留指定小数位
select round(1.45,1) as out_put;
+---------+
| out_put |
+---------+
| 1.5 |
+---------+
1 row in set (0.00 sec)
ceil 向上取整
select ceil(3.446) as out_put;
+---------+
| out_put |
+---------+
| 4 |
+---------+
1 row in set (0.00 sec)
floor 向下取整
select floor(3.5) as out_put;
+---------+
| out_put |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
truncate 截断指定小数位
select truncate(5.35,1) as out_put;
+---------+
| out_put |
+---------+
| 5.3 |
+---------+
1 row in set (0.00 sec)
mod 取余
select mod(4,3) as out_put;
+---------+
| out_put |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
日期函数
now 获取当前时间
select now() as date_time;
+---------------------+
| date_time |
+---------------------+
| 2021-07-08 09:39:45 |
+---------------------+
1 row in set (0.00 sec)
curdate 获取当前日期
select curdate() as date;
+------------+
| date |
+------------+
| 2021-07-08 |
+------------+
1 row in set (0.00 sec)
curtime 获取当前时间
select curtime() as time;
+----------+
| time |
+----------+
| 09:40:53 |
+----------+
1 row in set (0.01 sec)
year 获取年份
select year(now()) 年;
+------+
| 年 |
+------+
| 2021 |
+------+
1 row in set (0.01 sec)
month 获取月份
select month(now()) 月;
+------+
| 月 |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
str_to_date 时间字符串转成日期时间
select str_to_date('2021-07-07','%Y-%m-%d') as date;
+------------+
| date |
+------------+
| 2021-07-07 |
+------------+
1 row in set (0.01 sec)
统计函数
avg 获取平均数
select avg(age) as 平均年龄 from student;
+--------------+
| 平均年龄 |
+--------------+
| 21.1429 |
+--------------+
1 row in set (0.01 sec)
count 获取总数
select count(id) as 总条数 from student;
+-----------+
| 总条数 |
+-----------+
| 7 |
+-----------+
1 row in set (0.01 sec)
max 获取最大值
select max(age) from student;
+----------+
| max(age) |
+----------+
| 30 |
+----------+
1 row in set (0.01 sec)
min 获取最小值
select min(age) as 最小年龄 from student;
+--------------+
| 最小年龄 |
+--------------+
| 18 |
+--------------+
1 row in set (0.00 sec)
sum 获取数据相加总和
SELECT sum(age) from student;
+----------+
| sum(age) |
+----------+
| 148 |
+----------+
1 row in set (0.00 sec)
格式化函数
format 格式化数据
select format(34.343,2) format;
+--------+
| format |
+--------+
| 34.34 |
+--------+
1 row in set (0.00 sec)
date_format 格式化时间
select date_format(now(),'%Y-%m-%d') as date;
+------------+
| date |
+------------+
| 2021-07-08 |
+------------+
1 row in set (0.00 sec)
inet_aton ip转成数值
select inet_aton('192.168.1.100') as ip_num;
+------------+
| ip_num |
+------------+
| 3232235876 |
+------------+
1 row in set (0.00 sec)
inet_ntoa ip数值转成IP
select inet_ntoa(3232235876) as ip;
+---------------+
| ip |
+---------------+
| 192.168.1.100 |
+---------------+
1 row in set (0.00 sec)
系统函数
version 获取系统版本
select version();
+-----------+
| version() |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
database 获取当前数据库名
select database();
+-------------+
| database() |
+-------------+
| school_info |
+-------------+
1 row in set (0.00 sec)
user 获取当前用户
select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)