MuSQL-增删改查操作（2）

KYumii

于 2024-09-28 16:19:24 发布

阅读量556

点赞数 20

文章标签： mysql

本文链接：https://blog.csdn.net/2301_80418043/article/details/142603050

版权

1.Update修改

1.1语法

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
 SET assignment [, assignment] ...
 [WHERE where_condition]
 [ORDER BY ...]
 [LIMIT row_count]

1.2示例

将孙悟空同学的数学成绩变更为80分

# 查看原始数据
mysql> select * from exam where name = '孙悟空';
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 孙悟空 |      87 |   78 |      77 |
+----+--------+---------+------+---------+
1 row in set (0.00 sec)

# 更新操作
mysql> update exam set math = 80 where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 查看结果，数学成绩更新成功
mysql> select * from exam where name = '孙悟空';
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 孙悟空 |     87 |    80 |      77 |
+----+--------+---------+------+---------+
1 row in set (0.00 sec)

将曹孟德同学的数学成绩变更为60分，语文成绩变更为为70分

# 查看原始数据
mysql> select name, math, chinese from exam where name = '曹孟德';
+--------+------+---------+
| name | math | chinese |
+--------+------+---------+
| 曹孟德 | 84 |       82 |
+--------+------+---------+
1 row in set (0.00 sec)

# 更新操作
mysql> update exam set math = 60, chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 查看结果
mysql> select name, math, chinese from exam where name = '曹孟德';
+--------+------+---------+
| name | math | chinese |
+--------+------+---------+
| 曹孟德 | 60 |       70 |
+--------+------+---------+
1 row in set (0.00 sec)

将总成绩倒数前三的3位同学的数学成绩加上30分

# 查看原始数据
mysql> select name, math,chinese + math + english as 总分 from exam where
chinese + math + english is not null order by 总分 asc limit 3;
+--------+------+------+
| name | math | 总分 |
+--------+------+------+
| 宋公明 | 65 | 170 |
| 刘⽞德 | 85 | 185 |
| 曹孟德 | 60 | 197 |
+--------+------+------+
3 rows in set (0.00 sec)

# 更新操作
mysql> update exam set math = math +30 where chinese + math + english is not
null order by chinese + math + english asc limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

# 查看结果
mysql> select name, math, chinese + math + english as 总分 from exam where name 
in ('宋公明','刘⽞德','曹孟德');
+--------+------+------+
| name | math | 总分 |
+--------+------+------+
| 曹孟德 | 90 | 227 |
| 刘⽞德 | 115 | 215 |
| 宋公明 | 95 | 200 |
+--------+------+------+
3 rows in set (0.00 sec)

# 修改后总成绩倒数前三的 3 位同学和数据成绩
mysql> select name, math,chinese + math + english as 总分 from exam where
chinese + math + english is not null order by 总分 asc limit 3;
+--------+------+------+
| name | math | 总分 |
+--------+------+------+
| 宋公明 | 95 | 200 |
| 刘⽞德 | 115 | 215 |
| 唐三藏 | 98 | 221 |
+--------+------+------+
3 rows in set (0.00 sec)

1.3Update注意事项

以原值的基础上做变更时，不能使⽤math += 30这样的语法

不加where条件时，会导致全表数据被列新，谨慎操作

2.Delete 删除

2.1语法

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

2.2示例

删除孙悟空同学的考试成绩

# 查看原始数据
mysql> select * from exam where name = '孙悟空';
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 孙悟空 |     174 |   80 |     77 |
+----+--------+---------+------+---------+
1 row in set (0.00 sec)

# 删除操作
mysql> delete from exam where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)

# 查看结果
mysql> select * from exam where name = '孙悟空';
Empty set (0.00 sec)

删除整张表数据

# 准备测试表
mysql> CREATE TABLE t_delete (
 id INT,
 name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)

# 插⼊测试数据
mysql> INSERT INTO t_delete (id, name) VALUES (1, 'A'), (2, 'B'), (3, 'C');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

# 查看测试表
mysql> select * from t_delete;
+------+------+
| id | name |
+------+------+
| 1  |    A |
| 2  |    B |
| 3  |    C |
+------+------+
3 rows in set (0.00 sec)

# 删除整张表中的数据
mysql> delete from t_delete;
Query OK, 3 rows affected (0.00 sec)

# 查看结果
mysql> select * from t_delete;
Empty set (0.00 sec)

2.3Delete注意事项

执行Delete时不加条件会删除整张表的数据，谨慎操作

3.插入查询结果

3.1语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

3.2示例

删除表中的重复记录，重复的数据只能有一份

# 创建测试表，并构造数据
mysql> CREATE TABLE t_recored (id int, name varchar(20));

# 插⼊测试数据
INSERT INTO t_recored VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

# 查看结果
mysql> select * from t_recored;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 100 | aaa |
| 200 | bbb |
| 200 | bbb |
| 200 | bbb |
| 300 | ccc |
+------+------+
6 rows in set (0.00 sec)

实现思路：原始表中的数据⼀般不会主动删除，但是真正查询时不需要重复的数据，如果每次查询

都使⽤DISTINCT进⾏去重操作，会严重效率。可以创建⼀张与 t_recored 表结构相同的表，把

去重的记录写⼊到新表中，以后查询都从新表中查，这样真实的数据不丢失，同时⼜能保证查询效

率

# 创建⼀张新表，表结构与t_recored相同
mysql> create table t_recored_new like t_recored;
Query OK, 0 rows affected (0.02 sec)

# 新表中没有记录
mysql> select * from t_recored_new;
Empty set (0.00 sec)

# 原表中的记录去重后写⼊到新表
mysql> insert into t_recored_new select distinct * from t_recored;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

# 查询新表中的记录，实现去重
mysql> select * from t_recored_new;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)

# 新表与原来重命名
mysql> rename table t_recored to t_recored_old, t_recored_new to t_recored;
Query OK, 0 rows affected (0.02 sec)

# 查询重命名后表中的记录，实现需求且原来中的记录不受影响
mysql> select * from t_recored;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t_recored_old;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 100 | aaa |
| 200 | bbb |
| 200 | bbb |
| 200 | bbb |
| 300 | ccc |
+------+------+
6 rows in set (0.00 sec)

4.聚会函数

4.1常用函数

函数	说明
COUNT（[DISTINCT] expr）	返回查询到的数据的数量
SUM（[DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DESTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

4.2示例

4.2.1COUNT

统计exam表中有多少记录

# 使⽤ * 做统计
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

# 使⽤常量做统计
mysql> select count(1) from exam;
+----------+
| count(1) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

统计有多少学⽣参加数学考试

# # 使⽤指定列做统计
mysql> select count(math) from exam;
+-------------+
| count(math) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

统计有多少学⽣参加英语考试

mysql> select * from exam;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 1 | 唐三藏 |    134 |    98 |      56 |
| 3 | 猪悟能 |    176 |    98 |      90 |
| 4 | 曹孟德 |    140 |    90 |      67 |
| 5 | 刘⽞德 |    110 |   115 |      45 |
| 6 | 孙权 |      140 |    73 |      78 |
| 7 | 宋公明 |    150 |    95 |      30 |
| 8 | 张⻜ |       54 |     0 |    NULL | # 张⻜没有参加英语考试
+----+--------+---------+------+---------+
7 rows in set (0.00 sec)

# NULL 的数据不会计⼊结果
mysql> select count(english) from exam;
+----------------+
| count(english) |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

统计语⽂成绩⼩于50分的学⽣个数

# 加⼊where条件
mysql> select count(chinese) from exam where chinese < 10;
+----------------+
| count(chinese) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

4.2.2SUM

统计所有学生数学成绩总分

mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
|       569 |
+-----------+
1 row in set (0.01 sec)

统计所有学生英语成绩总分

# 值为NULL的列不参与统计
mysql> select sum(english) from exam;
+--------------+
| sum(english) |
+--------------+
|          366 |
+--------------+
1 row in set (0.00 sec)

不能统计⾮数值的列

mysql> select sum(name) from exam;
+-----------+
| sum(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 7 warnings (0.01 sec) # 警告信息，可以使⽤show warnings查看

4.2.3 AVG

统计英语成绩的平均分

# NULL值不参与统计
mysql> select avg(english) from exam;
+--------------+
| avg(english) |
+--------------+
|           61 |
+--------------+
1 row in set (0.00 sec)

4.2.4 MAX

查询英语最高分

mysql> select max(english) from exam;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)

4.2.5 MIN

查询>70分以上的数学最低分

mysql> select min(math) from exam where math > 70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.00 sec)

5.Group by分组查询

GROUP BY ⼦句的作⽤是通过⼀定的规则将⼀个数据集划分成若⼲个⼩的分组，然后针对若⼲个

分组进⾏数据处理，⽐如使⽤聚合函数对分组进⾏统计。

5.1 语法

SELECT {col_name | expr} ,... ,aggregate_function (aggregate_expr)
 FROM table_references
 GROUP BY {col_name | expr}, ... 
 [HAVING where_condition]

• col_name | expr：要查询的列或表达式，可以有多个，必须在 GROUP BY ⼦句中作为分组的依据

• aggregate_function：聚合函数，⽐如COUNT(), SUM(), AVG(), MAX(), MIN()

• aggregate_expr：聚合函数传⼊的列或表达式，如果列或表达式不在 GOURP BY ⼦句中，必须包含中聚合函数中

5.2示例

准备测试表及数据职员表emp，列分别为：id(编号)，name(姓名)，role(⻆⾊)，salary(薪⽔)

drop table if exists emp;
create table emp (
 id bigint primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary decimal(10, 2) not null
);

insert into emp values (1, '⻢云', '⽼板', 1500000.00);
insert into emp values (2, '⻢化腾', '⽼板', 1800000.00);
insert into emp values (3, '鑫哥', '讲师', 10000.00);
insert into emp values (4, '博哥', '讲师', 12000.00);
insert into emp values (5, '平姐', '学管', 9000.00);
insert into emp values (6, '莹姐', '学管', 8000.00);
insert into emp values (7, '孙悟空', '游戏⻆⾊', 956.8);
insert into emp values (8, '猪悟能', '游戏⻆⾊', 700.5);
insert into emp values (9, '沙和尚', '游戏⻆⾊', 333.3);

select * from emp;

统计每个角色的人数

mysql> select role, count(*) from emp group by role;
+----------+----------+
| role | count(*) |
+----------+----------+
| ⽼板 |         2 |
| 讲师 |         2 |
| 学管 |         2 |
| 游戏⻆⾊ |      3 |
+----------+----------+
4 rows in set (0.00 sec)

统计每个⻆⾊的平均⼯资，最⾼⼯资，最低⼯资

mysql> select role, ROUND(avg(salary),2) as 平均⼯资, ROUND(max(salary),2) as 最
⾼⼯资, ROUND(min(salary),2) as 最低⼯资 from emp group by role;
+----------+------------+------------+------------+
| role | 平均⼯资 | 最⾼⼯资 | 最低⼯资 |
+----------+------------+------------+------------+
| ⽼板 | 1650000.00 | 1800000.00 | 1500000.00 |
| 讲师 | 11000.00 | 12000.00 | 10000.00 |
| 学管 | 8500.00 | 9000.00 | 8000.00 |
| 游戏⻆⾊ | 663.53 | 956.80 | 333.30 |
+----------+------------+------------+------------+
4 rows in set (0.00 sec)

5.3 having子句

使⽤GROUP BY 对结果进⾏分组处理之后，对分组的结果进⾏过滤时，不能使⽤ WHERE ⼦句，⽽要使⽤ HAVING ⼦句

显⽰平均⼯资低于1500的⻆⾊和它的平均⼯资

mysql> select role, avg(salary) from emp group by role having avg(salary) < 
1500;
+----------+-------------+
| role | avg(salary) |
+----------+-------------+
| 游戏⻆⾊ | 663.533333 |
+----------+-------------+
1 row in set (0.00 sec)

5.4 Having与Where的区别

• Having ⽤于对分组结果的条件过滤

• Where ⽤于对表中真实数据的条件过滤

6.内置函数

6.1日期函数

函数	说明
CURDATE()	返回当前⽇期，同义词 CURRENT_DATE ， CURRENT_DATE()
CURTIME()	返回当前时间，同义词 CURRENT_TIME ， CURRENT_TIME([fsp])
NOW()	返回当前⽇期和时间，同义语 CURRENT_TIMESTAMP ， CURRENT_TIMESTAMP
DATE(data)	提取date或datetime表达式的⽇期部分
ADDDATE(date,INTERVAL expr unit)	向⽇期值添加时间值(间隔)，同义词 DATE_ADD()
SUBDATE(date,INTERVAL expr unit)	向⽇期值减去时间值(间隔)，同义词 DATE_SUB()
DATEDIFF（expr1，expr2）	两个⽇期的差，以天为单位，expr1 - expr2

6.1.1示例

获取当前日期

mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2024-09-27 |
+------------+
1 row in set (0.00 sec)

获取当前时间

mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 10:26:33  |
+-----------+
1 row in set (0.00 sec)

获取当前日期和时间

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-09-27 10:26:47 |
+---------------------+
1 row in set (0.00 sec)

提取指定datatime的⽇期部分

mysql> select date('2024-08-27 10:26:47');
+-----------------------------+
| date('2024-09-27 10:26:47') |
+-----------------------------+
| 2024-09-27                  |
+-----------------------------+
1 row in set (0.00 sec)

在给定⽇期的基础上加31天

mysql> select ADDDATE('2024-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2024-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2024-02-02                             |
+----------------------------------------+
1 row in set (0.00 sec)

在给定⽇期的基础上减去1⽉

mysql> select SUBDATE('2024-08-02', INTERVAL 1 MONTH);
+-----------------------------------------+
| SUBDATE('2024-08-02', INTERVAL 1 MONTH) |
+-----------------------------------------+
| 2024-07-02                              |
+-----------------------------------------+
1 row in set (0.00 sec)

计算两个⽇期之间相差多少天

# 在计算时只使⽤⽇期部分
mysql> SELECT DATEDIFF('2024-12-31 23:59:59','2024-12-30');
+----------------------------------------------+
| DATEDIFF('2024-12-31 23:59:59','2024-12-30') |
+----------------------------------------------+
| 1                                            |
+----------------------------------------------+
1 row in set (0.00 sec)

# 表达式1表⽰的⽇期早于表达式2表⽰的⽇期时返回负数
mysql> SELECT DATEDIFF('2024-11-30 23:59:59','2024-12-31');
+----------------------------------------------+
| DATEDIFF('2024-11-30 23:59:59','2024-12-31') |
+----------------------------------------------+
| -31                                          |
+----------------------------------------------+
1 row in set (0.00 sec)

6.2字符串处理函数

函数	说明
CHAR_LENGTH(str)	返回给定字符串的⻓度，同义词CHARACTER_LENGTH()
LENGTH(str)	返回给定字符串的字节数，与当前使⽤的字符编码集有关
CONCAT(str1,str2,...)	返回拼接后的字符串
CONCAT_WS(separator,str1,str2,...)	返回拼接后带分隔符的字符串
LCASE(str)	将给定字符串转换成⼩写，同义词 LOWER()
UCASE(str)	将给定字符串转换成⼤写，同义词 UPPER()
HEX(str), HEX(N)	对于字符串参数str, HEX()返回str的⼗六进制字符串表⽰形式，对于数字参数N, HEX()返回⼀个⼗六进制字符串表⽰形式
INSTR(str,substr）	返回substring第⼀次出现的索引
INSERT(str,pos,len,newstr )	在指定位置插⼊⼦字符串，最多不超过指定的字符数
SUBSTR( str, pos) SUBSTR(str FROM pos FOR len)	返回指定的⼦字符串，同义词 SUBSTRING( str, pos) ， SUBSTRING(str FROM pos FOR len)
REPLACE(str,from_str,to_str)	把字符串str中所有的from_str替换为to_str，区分⼤⼩写
STRCMP(expr1,expr2)	逐个字符⽐较两个字符串，返回 -1， 0 ， 1
LEFT（str,len), RIGHT(str,len)	返回字符串str中最左/最右边的len个字符
LTRIM(str) ， RTRIM(str) ， TRIM(str)	删除给定字符串的前导、末尾、前导和末尾的空格
TRIM([{LEADING \| TRAILING \| BOTH } [ remstr] FROM] str)	删除给定符串的前导、末尾或前导和末尾的指定字符串

6.2.1示例

显示所有参加考试的学生姓名，姓名字符数和字节长度

mysql> select name, char_length(name), length(name) from exam;
+-----------+-------------------+--------------+
| name | char_length(name) | length(name) |
+-----------+-------------------+--------------+
| 唐三藏 |                3 |            6 |
| 猪悟能 |                3 |            6 |
| 曹孟德 |                3 |            6 |
| 刘⽞德 |                3 |            6 |
| 孙权   |                2 |            4 |
| 宋公明 |                3 |            6 |
| 张⻜   |                2 |            4 |
+-----------+-------------------+--------------+
7 rows in set (0.00 sec)

显⽰学⽣的考试成绩，格式为 "XXX的语⽂成绩：XXX分，数学成绩：XXX分，英语成绩：XXX分"

mysql> select concat(name, '的语⽂成绩：', chinese, '分，数学成绩：', math, '分，英
语成绩：', english, '分') as 分数 from exam;
+------------------------------------------------------------------------------
----+
| 分数 
 |
+------------------------------------------------------------------------------
----+
| 唐三藏的语⽂成绩：134分，数学成绩：98分，英语成绩：56分 |
| 猪悟能的语⽂成绩：176分，数学成绩：98分，英语成绩：90分 |
| 曹孟德的语⽂成绩：140分，数学成绩：90分，英语成绩：67分 |
| 刘⽞德的语⽂成绩：110分，数学成绩：115分，英语成绩：45分 |
| 孙权的语⽂成绩：140分，数学成绩：73分，英语成绩：78分 |
| 宋公明的语⽂成绩：150分，数学成绩：95分，英语成绩：30分 |
| NULL 
 |
+------------------------------------------------------------------------------
----+
7 rows in set (0.00 sec)

拼接后的字符串⽤逗号隔开

mysql> select CONCAT_WS(',',name,chinese,math,english) 分数 from exam;
+----------------------+
| 分数 |
+----------------------+
| 唐三藏,134,98,56 |
| 猪悟能,176,98,90 |
| 曹孟德,140,90,67 |
| 刘⽞德,110,115,45 |
| 孙权,140,73,78 |
| 宋公明,150,95,30 |
| 张⻜,54,0 |
+----------------------+
7 rows in set (0.00 sec)

将给定字符串转换成⼩写

mysql> select lcase('ABC');
+--------------+
| lcase('ABC') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)

将给定字符串转换成大写

mysql> select ucase('abc');
+--------------+
| ucase('abc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)

转换为十六进制

# 字符串
mysql> select HEX('Hello MySQL');
+------------------------+
| HEX('Hello MySQL') |
+------------------------+
| 48656C6C6F204D7953514C |
+------------------------+
1 row in set (0.00 sec)

# 数字
mysql> select HEX(15);
+---------+
| HEX(15) |
+---------+
| F |
+---------+
1 row in set (0.00 sec)

⼦字符串第⼀次出现的索引

mysql> select instr('Hello MySQL', 'sql');
+-----------------------------+
| instr('Hello MySQL', 'sql') |
+-----------------------------+
| 9 | 
+-----------------------------+
1 row in set (0.00 sec)

指定位置插⼊⼦字符串

# 在指定位置插⼊
mysql> select insert('Hello, Database', 8, 0, 'MySQL ');
+-------------------------------------------+
| insert('Hello, Database', 8, 0, 'MySQL ') |
+-------------------------------------------+
| Hello, MySQL Database |
+-------------------------------------------+
1 row in set (0.00 sec)

# 覆盖20位，如果从写⼊点往后不⾜20位相当于删除后⾯所有字符
mysql> select insert('Hello, Database', 8, 20, 'MySQL');
+-------------------------------------------+
| insert('Hello, Database', 8, 20, 'MySQL') |
+-------------------------------------------+
| Hello, MySQL |
+-------------------------------------------+
1 row in set (0.00 sec)

返回指定的⼦字符串

# 从'Hello, MySQL'的第⼋个字符开始截取
mysql> select SUBSTR('Hello, MySQL', 8);
+---------------------------+
| SUBSTR('Hello, MySQL', 8) |
+---------------------------+
| MySQL |
+---------------------------+
1 row in set (0.00 sec)

# 从'Hello, MySQL'的第⼋个字符开始截取1个字符
mysql> select SUBSTR('Hello, MySQL' FROM 8 FOR 1);
+-------------------------------------+
| SUBSTR('Hello, MySQL' FROM 8 FOR 1) |
+-------------------------------------+
| M |
+-------------------------------------+
1 row in set (0.00 sec)

# 从'Hello, MySQL'的第⼋个字符开始截取10个字符，不⾜10个读到整个字符串结尾
mysql> select SUBSTR('Hello, MySQL' FROM 8 FOR 10);
+--------------------------------------+
| SUBSTR('Hello, MySQL' FROM 8 FOR 10) |
+--------------------------------------+
| MySQL |
+--------------------------------------+
1 row in set (0.00 sec)

替换字符串

# 把Database替换成MySQL, 区分⼤⼩写
mysql> select REPLACE('Hello Database', 'Database', 'MySQL');
+------------------------------------------------+
| REPLACE('Hello Database', 'Database', 'MySQL') |
+------------------------------------------------+
| Hello MySQL |
+------------------------------------------------+
1 row in set (0.00 sec)

比较两个字符串

# 观察返回的结果
mysql> select STRCMP('text', 'text1');
+-------------------------+
| STRCMP('text', 'text1') |
+-------------------------+
| -1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select STRCMP('text', 'text');
+------------------------+
| STRCMP('text', 'text') |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)

mysql> select STRCMP('text1', 'text');
+-------------------------+
| STRCMP('text1', 'text') |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

返回字符串str中最左/最右边的len个字符

# 最左边的5个字符
mysql> select LEFT('Hello MySQL', 5);
+------------------------+
| LEFT('Hello MySQL', 5) |
+------------------------+
| Hello |
+------------------------+
1 row in set (0.00 sec)

# 最右边的5个字符
mysql> select RIGHT('Hello MySQL', 5);
+-------------------------+
| RIGHT('Hello MySQL', 5) |
+-------------------------+
| MySQL |
+-------------------------+
1 row in set (0.00 sec)

删除给定字符串的前导、末尾、前导和末尾的空格

mysql> select ' ABC', LTRIM(' ABC'), 'ABC ', RTRIM('ABC '), ' ABC 
', TRIM(' ABC ');
+--------+-----------------+--------+-----------------+-----------+------------
-------+
| ABC | LTRIM(' ABC') | ABC | RTRIM('ABC ') | ABC | TRIM(' 
ABC ') |
+--------+-----------------+--------+-----------------+-----------+------------
-------+
| ABC | ABC | ABC | ABC | ABC | ABC 
 |
+--------+-----------------+--------+-----------------+-----------+------------
-------+
1 row in set (0.00 sec)

删除给定符串的前导、末尾或前导和末尾的指定字符串

# 删除前后指定的字符串，BOTH可以省略
mysql> select TRIM(BOTH 'xxx' FROM 'xxxABCxxx');
+------------------------------+
| TRIM('xxx' FROM 'xxxABCxxx') |
+------------------------------+
| ABC |
+------------------------------+
1 row in set (0.00 sec)

# 删除前导的指定字符串
mysql> select TRIM(LEADING 'xxx' FROM 'xxxABCxxx');
+--------------------------------------+
| TRIM(LEADING 'xxx' FROM 'xxxABCxxx') |
+--------------------------------------+
| ABCxxx |
+--------------------------------------+
1 row in set (0.00 sec)

# 删除末尾的指定字符串
mysql> select TRIM(TRAILING 'xxx' FROM 'xxxABCxxx');
+---------------------------------------+
| TRIM(TRAILING 'xxx' FROM 'xxxABCxxx') |
+---------------------------------------+
| xxxABC |
+---------------------------------------+
1 row in set (0.00 sec)

6.3数学函数

函数	说明
ABS(X)	返回X的绝对值
CEIL(X)	返回不⼩于X的最⼩整数值，同义词是 CEILING(X)
FLOOR(X)	返回不大于X的最大整数值
CONV(N,from_base,to_base)	不同进制之间的转换
FORMAT(X,D)	将数字X格式化为“#，###，###”的格式。##'，四舍五⼊到⼩数点后D 位，并以字符串形式返回
RAND([N])	返回一个随机浮点值，取值范围[0.0,1.0)
ROUND(X), ROUND(X,D)	将参数X舍入到小数点后D位
CRC32(expr)	计算指定字符串的循环冗余校验值并返回⼀个32位⽆符号整数

6.3.1示例

返回-3.14的绝对值

mysql> select ABS(-3.14);
+------------+
| ABS(-3.14) |
+------------+
| 3.14 |
+------------+
1 row in set (0.00 sec)

返回不⼩于20.36的最⼩整数值

mysql> select CEIL(20.36);
+-------------+
| CEIL(20.36) |
+-------------+
| 21 |
+-------------+
1 row in set (0.00 sec)

返回不⼤于11.32的最⼩整数值

mysql> select FLOOR(11.32);
+--------------+
| FLOOR(11.32) |
+--------------+
| 11 |
+--------------+
1 row in set (0.00 sec)

10进制转为16进制

mysql> select CONV(15, 10, 16);
+------------------+
| CONV(15, 10, 16) |
+------------------+
| F |
+------------------+
1 row in set (0.00 sec)

格式化1234567.654321

mysql> select FORMAT(1234567.654321, 5);
+---------------------------+
| FORMAT(1234567.654321, 5) |
+---------------------------+
| 1,234,567.65432 |
+---------------------------+
1 row in set (0.00 sec)

返回⼀个随机浮点值

mysql> select RAND();
+--------------------+
| RAND() |
+--------------------+
| 0.9555353624332095 |
+--------------------+
1 row in set (0.00 sec)

舍弃到⼩数点后6位

mysql> select ROUND(RAND(), 6);
+------------------+
| ROUND(RAND(), 6) |
+------------------+
| 0.612703 |
+------------------+
1 row in set (0.00 sec)
# ⽣成⼀个6位数的随机数
mysql> select ROUND(RAND(), 6) * 1000000;
+----------------------------+
| ROUND(RAND(), 6) * 1000000 |
+----------------------------+
| 982956 |
+----------------------------+
1 row in set (0.00 sec)

字符串的循环冗余校验

mysql> select CRC32('Hello MySQL');
+----------------------+
| CRC32('Hello MySQL') |
+----------------------+
| 1944582821 |
+----------------------+
1 row in set (0.00 sec)

6.4其他常用函数

函数	说明
version()	显⽰当前数据库版本
database()	显⽰当前正在使⽤的数据库
user()	显⽰当前⽤⼾
md5(str)	对⼀个字符串进⾏md5摘要，摘要后得到⼀个32位字符串
ifnull(val1, val2)	如果val1为NULL，返回val2，否则返回 val1

6.4.1示例

显⽰当前数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.39 |
+-----------+
1 row in set (0.00 sec)

显⽰当前正在使⽤的数据库

# 没有选择数据库时
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)

# 选择数据库
mysql> use java01
Database changed

# 选择数据库后
mysql> select database();
+------------+
| database() |
+------------+
| java01 |
+------------+
1 row in set (0.00 sec)

显⽰当前⽤⼾

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

对⼀个字符串进⾏md5加密

# 对hello world进⾏md5加密
mysql> select md5('hello world');
+----------------------------------+
| md5('hello world') |
+----------------------------------+
| 5eb63bbbe01eeed093cb22bb8f5acdc3 |
+----------------------------------+
1 row in set (0.01 sec)

ifnull函数

# 第⼀个参数不为NULL， 返回第⼀个参数的值
mysql> select ifnull('database', 'MySQL');
+-----------------------------+
| ifnull('database', 'MySQL') |
+-----------------------------+
| database                    |
+-----------------------------+
 1 row in set (0.00 sec)

 # 第⼀个参数为NULL， 返回第⼆个参数的值
 mysql> select ifnull(NULL, 'MySQL');
 +-----------------------+
 | ifnull(NULL, 'MySQL') |
 +-----------------------+
 | MySQL                 |
 +-----------------------+
 1 row in set (0.00 sec)