MySQL单行函数

字符函数

1. 字符串拼接

concat(str1,str2,...)：将str1，str2...等多个字符串拼接成一个长字符串，如果有任何一个参数为NULL，则返回值为NULL

concat_ws(separator, str1, str2, ...)：指定分隔符，将多个字符串拼接成一个长字符串，如果有任何一个参数（包括分隔符）为NULL，则返回值为NULL

group_concat(distinct 要连接的字段 order by 排序字段 separator '分隔符')：搭配group by使用，将分组后产生的，属于同一个分组中的值连接起来，返回一个长字符串结果（f分隔符默认为逗号）

# 准备测试数据 --student表
CREATE TABLE `student` (
   `id` int(11) NOT NULL AUTO_INCREMENT,   #--id
   `stuName` varchar(22) DEFAULT NULL,     #--学生姓名
   `course` varchar(22) DEFAULT NULL,      #--学习科目
   `score` int(11) DEFAULT NULL,           #--学分
   PRIMARY KEY (`id`)                      #--设置主键
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
# 插入数据
INSERT INTO `student`(stuName,course,score) VALUES ('张三', '语文', '91');
INSERT INTO `student`(stuName,course,score) VALUES ('张三', '数学', '90');
INSERT INTO `student`(stuName,course,score) VALUES ('张三', '英语', '87');
INSERT INTO `student`(stuName,course,score) VALUES ('李四', '语文', '79');
INSERT INTO `student`(stuName,course,score) VALUES ('李四', '数学', '95');
INSERT INTO `student`(stuName,course,score) VALUES ('李四', '英语', '80');
INSERT INTO `student`(stuName,course,score) VALUES ('王五', '语文', '77');
INSERT INTO `student`(stuName,course,score) VALUES ('王五', '数学', '81');
INSERT INTO `student`(stuName,course,score) VALUES ('王五', '英语', '89');
# 浏览数据
mysql> select * from student;
+----+---------+--------+-------+
| id | stuName | course | score |
+----+---------+--------+-------+
| 10 | 张三    | 语文    |    91 |
| 11 | 张三    | 数学    |    90 |
| 12 | 张三    | 英语    |    87 |
| 13 | 李四    | 语文    |    79 |
| 14 | 李四    | 数学    |    95 |
| 15 | 李四    | 英语    |    80 |
| 16 | 王五    | 语文    |    77 |
| 17 | 王五    | 数学    |    81 |
| 18 | 王五    | 英语    |    89 |
+----+---------+------- +-------+
# concat_ws()函数和group_concat()搭配使用
select 
stuName as ‘姓名’,group_concat(concat_ws(':',course,score) order by score separator ',') as '成绩'
from student
group by stuName;
# 查询返回结果
+--------------+-------------------------------+
| ‘姓名’        | 成绩                          |
+--------------+-------------------------------+
| 张三          | 英语:87,数学:90,语文:91        |
| 李四          | 语文:79,英语:80,数学:95        |
| 王五          | 语文:77,数学:81,英语:89        |
+--------------+-------------------------------+

2. 字符串替换

insert(str,x,y,instr)：将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串instr

replace(str,x,y): 将字符串str中的x替换成y

mysql> select insert('aaaaab',2,5,1),replace('aaaaab','a',1);
+------------------------+-------------------------+
| insert('aaaaab',2,5,1) | replace('aaaaab','a',1) |
+------------------------+-------------------------+
| a1                     | 11111b                  |
+------------------------+-------------------------+

3. 字符串填充

lpad(str,x,instr)：向字符串str的最左侧填充字符串instr，直到字符串长度达到x

rpad(str,x,instr)：向字符串str的最右端填充字符串instr，直到字符串长度达到x

如果当原字符串的长度>给定的填充后的字符串长度，返回结果则是从左往右取x位字符

mysql> select lpad('aa',6,'b'),rpad('aa',6,'b'),lpad('aaaaaa',2,'b'),rpad('aaaaaa',2,'b');
+------------------+------------------+----------------------+----------------------+
| lpad('aa',6,'b') | rpad('aa',6,'b') | lpad('aaaaaa',2,'b') | rpad('aaaaaa',2,'b') |
+------------------+------------------+----------------------+----------------------+
| bbbbaa           | aabbbb           | aa                   | aa                   |
+------------------+------------------+----------------------+----------------------+

4. 字符串去空格/字符

trim(leading/trailing/both instr from str)：去除字符串str最左端/最右端/两端的字符串instr，或者空格

ltrim(str)：去除字符串左端空格

rtrim(str)：去除字符串右端空格

mysql> select trim(leading 'a' from 'abca'),trim(leading 'ab' from 'abca');
+-------------------------------+--------------------------------+
| trim(leading 'a' from 'abca') | trim(leading 'ab' from 'abca') |
+-------------------------------+--------------------------------+
| bca                           | ca                             |
+-------------------------------+--------------------------------+

5. 返回字符串的最左/右端字符串

left(str,x)：返回字符串str最左端x个字符

right(str,x)：返回字符串str最右端x个字符

mysql> select left('abc',1),right('abc',1);
+---------------+----------------+
| left('abc',1) | right('abc',1) |
+---------------+----------------+
| a             | c              |
+---------------+----------------+

6. 截取字符串

substring(str,x,y)：截取字符串str，从x开始，截取y位

mysql> select substring('abcdef',2,3)：
+-------------------------+
| substring('abcdef',2,3) |
+-------------------------+
| bcd                     |
+-------------------------+

7. 查找字符串位置

instr(str,instr)：查找字符串instr第一次出现的位置

locate(instr,str,pos)：从pos开始查找字符串instr第一次出现的位置

position(instr in str)：查找字符串str第一次出现的位置

mysql> select instr('abc','b'),locate('b','abc',1),position('b' in 'abc');
+------------------+---------------------+------------------------+
| instr('abc','b') | locate('b','abc',1) | position('b' in 'abc') |
+------------------+---------------------+------------------------+
|                2 |                   2 |                      2 |
+------------------+---------------------+------------------------+

8. 返回字符串的字符长度/字节长度

length(str)：返回字符串str的字节长度。不同的字符集编码下，字符和字节的对应关系不同，在utf8规则下，一个汉字字符 =3 个字节，gbk：一个汉字字符 = 2个字节。

char_length(str)：返回字符串str的字符长度。一个汉字，数字，字母或者符号就是一个字符　　

# 字符集编码为utf8
mysql> select length('数据库'),char_length('数据库');
+---------------------+--------------------------+
| length('数据库')    | char_length('数据库')    |
+---------------------+--------------------------+
|                   9 |                        3 |
+---------------------+--------------------------+

9. 比较两个字符的大小

strcmp(str1,str2)：比较两个字符的ascii值大小，如果str1>str2,则返回1；str1<str2,返回-1；str1=str2，返回0；

mysql> select strcmp('a','9'),strcmp('z','您');
+-----------------+-------------------+
| strcmp('a','9') | strcmp('z','您')  |
+-----------------+-------------------+
|               1 |                -1 |
+-----------------+-------------------+
1 row in set (0.00 sec)

日期与时间函数

1. 当前时间，当前日期

curdate()，current_date()：返回当前系统的日期值

curtime()，current_time()：返回当前系统的时间值

now()，sysdate()：返回当前系统的日期与时间值

2. date_add()，date_sub()，adddate()，和subdate()

date_add(date,interval value type)，adddate(date,interval value type)：函数功能相同，都是向日期添加指定的日期间隔

date_sub(date,interval value type)，subdate(date,interval value type)：函数功能相同，都是向日期减去指定的日期间隔

间隔类型：

YEAR 年

MONTH 月

WEEK 周

DAY 日

HOUR 时

MINUTE 分

SECOND 秒

YEAY_MONTH 年_月

DAY_HOUR 日-时

DAY_MINUTE 日-分

DAY_SECOND 日-秒

HOUR_MINUTE 时-分

HOUR_SECOND 时-秒

MINUTE_SECOND 分-秒

mysql> select date_add('2022-01-01 00:00:00',interval '1 1:30:30' day_second);
+-----------------------------------------------------------------+
| date_add('2022-01-01 00:00:00',interval '1 1:30:30' day_second) |
+-----------------------------------------------------------------+
| 2022-01-02 01:30:30                                             |
+-----------------------------------------------------------------+

3. date_format()

date_format(date,'format')：按照给定的格式，返回日期中的值

格式：

%Y　　四位数字的年份
%m　两位数字的月份
%d　　两位数字的天数
%H　　两位数字的小时
%i 　　两位数字的分钟
%s　　两位数字的秒
%T　　24小时制的时间形式
%w　　以数字表达的周
%j　　一年中的第几天
%p　　上下午

mysql> select date_format(now(),'%Y-%m-%d %H:%i:%s %j %W');
+-----------------------------------------------------+
| date_format('2022-01-01','%Y-%m-%d %H:%i:%s %j %W') |
+-----------------------------------------------------+
| 2024-01-09 11:20:48 001 Saturday                    |
+-----------------------------------------------------+

4. datediff(）

datediff(date1,date2)：返回两个日期之间相差的天数

mysql> select datediff('2023-01-01','2022-01-01');
+-------------------------------------+
| datediff('2023-01-01','2022-01-01') |
+-------------------------------------+
|                                 365 |
+-------------------------------------+

5. dayofyear()，dayofmonth()，dayofweek()

dayofyear(date)：返回日期是一年中的第几天。功能等同于date_format(date,'%j')

dateofmonth(date)：返回日期是一个月的第几天。功能等同于day()函数

dateofweek(date)：返回指定日期在一周内的索引值

mysql> select dayofyear('2022-12-12'),dayofmonth('2022-12-12'),dayofweek('2022-12-12');
+-------------------------+--------------------------+-------------------------+
| dayofyear('2022-12-12') | dayofmonth('2022-12-12') | dayofweek('2022-12-12') |
+-------------------------+--------------------------+-------------------------+
|                     346 |                       12 |                       2 |
+-------------------------+--------------------------+-------------------------+

posted @ 2023-07-07 11:34 ISPEAKER 阅读(197) 评论(0) 收藏举报

刷新页面返回顶部

ISPEAKER

MySQL单行函数

公告