MySQL（四）——正则表达式查询、插入数据、删除数据

一、正则表达式查询

1、查询以特定字符或字符串开头的记录

字符 ^ 用来匹配以特定字符或字符串开头的记录。

//在 user_test 表中，查询 name 字段以“C”开头的记录
SELECT * FROM user_test WHERE name REGEXP '^C';
//在 user_test 表中，查询 name 字段以“Ch”开头的记录
SELECT * FROM user_test WHERE name REGEXP '^Ch';
1
2
3
4

2、查询以特定字符或字符串结尾的记录

字符$用来匹配以特定字符或字符串结尾的记录。

//在 user_test 表中，查询 name 字段以“n”结尾的记录
SELECT * FROM user_test WHERE name REGEXP 'n$';
//在 user_test 表中，查询 name 字段以“en”结尾的记录
SELECT * FROM user_test WHERE name REGEXP 'en$';
1
2
3
4

3、替代字符串中的任意一个字符

字符.用来替代字符串中的任意一个字符。

//在 user_test表中，查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录
SELECT * FROM user_test WHERE name REGEXP 'a.y';
1
2

4、匹配多个字符

字符和+都可以匹配多个该符号之前的字符。不同的是，+表示至少一个字符，而可以表示 0 个字符。

//在 user_test 表中，查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录
SELECT * FROM user_test WHERE name REGEXP '^Th*';
//在 user_test 表中，查询 name 字段值包含字母“T”，且“T”后面至少出现“h”一次的记录
SELECT * FROM user_test WHERE name REGEXP '^Th+';
1
2
3
4

5、匹配指定字符串

正则表达式可以匹配字符串。当表中的记录包含这个字符串时，就可以将该记录查询出来。指定多个字符串时，需要用|隔开。只要匹配这些字符串中的任意一个即可。

//在 user_test 表中，查询 name 字段值包含字符串“an”的记录
SELECT * FROM user_test WHERE name REGEXP 'an';
//在 user_test 表中，查询 name 字段值包含字符串“an”或“en”的记录
SELECT * FROM user_test WHERE name REGEXP 'an|en';
1
2
3
4

注意：字符串与|之间不能有空格。因为，查询过程中，数据库系统会将空格也当作一个字符，这样就查询不出想要的结果。

6、匹配指定字符串中的任意一个

使用方括号[ ]可以将需要查询的字符组成一个字符集合。只要记录中包含方括号中的任意字符，该记录就会被查询出来。例如，通过“[abc]”可以查询包含 a、b 和 c 等 3 个字母中任意一个的记录。

//在 user_test 表中，查询 name 字段值包含字母“i”或“o”的记录
SELECT * FROM user_test WHERE name REGEXP '[io]';
//在 user_test 表中，查询 name 字段值中包含 1、2 或 3 的记录
SELECT * FROM user_test WHERE name REGEXP '[123]';
1
2
3
4

方括号[ ]还可以指定集合的区间。例如，“[a-z]”表示从 a~z 的所有字母；“[0-9]”表示从 0~9 的所有数字；“[a-z0-9]”表示包含所有的小写字母和数字；“[a-zA-Z]”表示匹配所有字符。

7、匹配指定字符以外的字符

[^字符集合]用来匹配不在指定集合中的任何字符。

//在 user_test 表中，查询 name 字段值包含字母 a~t 以外的字符的记录
SELECT * FROM user_test WHERE name REGEXP '[^a-t]' ;
1
2

八、使用{n,}或者{n,m}来指定字符串连续出现的次数
字符串{n,}表示字符串连续出现 n 次；字符串{n,m}表示字符串连续出现至少 n 次，最多 m 次。
如：a{2,} 表示字母 a 连续出现至少 2 次，也可以大于 2 次；a{2,4} 表示字母 a 连续出现最少 2 次，最多不能超过 4 次。

//在 user_test 表中，查询 name 字段值出现字母‘e’ 至少 2 次的记录
SELECT * FROM user_test WHERE name REGEXP 'e{2,}';
//在 user_test 表中，查询 name 字段值出现字符串“i” 最少 1 次，最多 3 次的记录
SELECT * FROM user_test WHERE name REGEXP 'i{1,3}';
1
2
3
4

二、插入数据、修改数据、删除数据

基本的插入数据、修改数据、删除数据这里不再赘述，因为平常运用特别常见，看得多了自然熟悉了，这里举例一些比较不太常见的插入数据、修改数据、删除数据语句

1、使用 INSERT INTO…FROM 语句复制表数据

INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。
SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

//从 users_test 表中查询所有的记录，并将其插入 users_test_new  表中
INSERT INTO users_test_new 
    (user_id,user_name,user_grade,user_info)
    SELECT user_id,user_name,user_grade,user_info
    FROM users_test;
1
2
3
4
5

2、清空表记录

TRUNCATE 关键字用于完全清空一个表

//格式如下
TRUNCATE [TABLE] 表名
1
2

eg：

TRUNCATE TABLE tb_student_course;
1

3、TRUNCATE 和 DELETE 的区别

从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。

• DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
• DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
• DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
• DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
• DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
• DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。

4、注意

当不需要该表时，用 DROP；
当仍要保留该表，但要删除所有记录时，用 TRUNCATE；
当要删除部分记录时，用 DELETE。