📜 MySQL的一些数据过滤和正则匹配
🥰 每天都要进步呐

---

1.MySQL的数据过滤

使用where子句

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.5;

使用where子句操作符

💡"="、">"、"<"、">="、"<="、"<>"或者"!="(不等于)、"between"

• 价格小于10元的产品

  SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
  

  

• 价格小于等于10元的产品

  SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
  

  

• 不是供应商1003制造的产品

  SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
  

  

• 价格在5元与10元之间的产品 (范围检查)

  // 使用between
  SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
  // 单纯使用and
  SELECT prod_name, prod_price FROM products WHERE prod_price <= 10 AND prod_price >= 5;
  

  

• 空值检查

  SELECT * FROM customers WHERE cust_email IS NULL;
  

  

使用and、or、not操作符

• and 和 or 操作符 (多个条件)

  SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1002 or vend_id = 1003;
  

  

• 计算次序

  and的优先级是高于or的

  SELECT vend_id, prod_id, prod_price, prod_name FROM products WHERE vend_id = 1002 or vend_id = 1003 and prod_price >= 10;
  SELECT vend_id, prod_id, prod_price, prod_name FROM products WHERE (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
  

  

• in操作符

  SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1002 or vend_id = 1003;
  SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id IN (1002, 1003);
  

  

  结果是一样的，并且支持多个

  

• not操作符

  SELECT vend_id, prod_id, prod_price, prod_name FROM products WHERE vend_id NOT IN (1002, 1003, 1001);
  

  

使用通配符

• like通配符

  SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
  

  

  💡"*"和"%"都代表任何字符，匹配时不区分大小写

  SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
  

  

• 搜索模式

  搜索prod_name以s开头，e结尾

  SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 's%e';
  

  

• 下划线通配符 (按字符匹配)

  SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 's%e';
  

  

  💡"%"和"_"的区别

  

  💡两个下划线

  

2.正则表达式搜索

1.基本字符匹配

⚠️REGEXP正则

2.匹配000的产品信息

⚠️"."表示任何一个字符

这样的结果也是相同的

SELECT prod_name FROM products WHERE prod_name REGEXP '.000';
SELECT prod_name FROM products WHERE prod_name LIKE 'JetPack _000';

3.使用or进行匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000';

4.范围匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '[12] ton';

⚠️括号中的每个字符都可以进行匹配

5.范围匹配结合or匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1|2 ton';

⚠️注意：这么匹配为"1"或"2 ton", 所以结果会有问题，所以用[1|2]括号括起来，和上述是一样的效果[12]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1|2] ton';
SELECT prod_name FROM products WHERE prod_name REGEXP '[12] ton';

6.排它符

SELECT prod_name FROM products WHERE prod_name REGEXP '[^3] ton';

SELECT prod_name FROM products WHERE prod_name REGEXP '[^345] ton';

⚠️括号里面的字符都过滤掉

7.特殊字符匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '[123456789] ton';
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-9a-z] ton';

⚠️第二句是指可以匹配1-9和a-z所有的某个字符

8."."的使用

SELECT prod_name FROM products WHERE prod_name REGEXP '\\.';

\\f 换页
\\n 换行
\\r 回车键 
\\t 横向tab键
\\v 纵向tab键
\\\ 斜杠

⚠️使用\进行转译

9.匹配多个实例

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)';

⚠️s后面加个？表示s可以不出现或者出现至少一次

10.字符类

匹配4个数字

SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]';

或者

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}';

⚠️[:digit:]证明里面是数字，{}中间写的是出现次数

11.定位符

// 以"."作为开头
SELECT prod_name FROM products WHERE prod_name REGEXP '^[\\.]';
// 以"."作为结尾
SELECT prod_name FROM products WHERE prod_name REGEXP '[\\.]$';

⚠️匹配以"."开头的，"^"在[]里面是排他，在[]外面是指以某某开头

---