[Mysql] LIKE与通配符

练习案例数据

DROP TABLE IF EXISTS contact_info; 
CREATE TABLE contact_info( 
employee_id VARCHAR(8), 
employee_name VARCHAR(8), 
email VARCHAR(32) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
contact_info (employee_id,employee_name,email) 
VALUE ('e001','小王','12345@qq.com') 
,('e002','张杰','88888@163.com') 
,('e003','王菲菲','wangfeifei@163.com') 
,('e004','李云','liyun001@qq.com')
,('e005','小王王','67891@qq.com');

contact_info表(员工联系方式表)

employee_id:员工号   employee_name:员工姓名   email:个人邮箱

---

在搜索数据库中的数据时，SQL通配符可以替代一个或多个字符(通配符是用来匹配值的一部分的特殊字符)

为在搜索子句中使用通配符，必须与LIKE操作符一起使用

可使用以下通配符：

通配符	描述
百分号(%)	替代0个、1个或多个字符
下划线(_)	仅替代一个字符

使用通配符要记住的技巧:

1.不要过度使用通配符

如果其他操作符能达到相同的目的，应该使用其他操作符

2.在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处

把通配符置于搜索模式的开始处，搜索起来是最慢的

3.仔细注意通配符的位置

如果放错地方，可能不会返回想要的数据

1.百分号(%)通配符

查询时表示在该位置可以是任意个(0 - n 个)任意字符，必须和LIKE操作符一起使用

注意NULL 

虽然%通配符可以匹配任何东西，但有一个例外，即NULL

即使是WHERE 字段名 LIKE '%'也不能匹配用值NULL作为该字段名的行

案例1:查询使用163邮箱的员工信息

SELECT * FROM contact_info WHERE email LIKE'%@163.com';

结果展示:

案例2:查询个人邮箱中包含"8"这个数字的员工信息

SELECT * FROM contact_info WHERE email LIKE'%8%';

结果展示:

"%8%"这种写法代表8可以出现在个人邮箱的任何位置，即个人邮箱中包含8

案例3: 查询姓"王"的员工信息

SELECT * FROM contact_info WHERE employee_name LIKE'王%';

结果展示:

2.下划线(_)通配符

查询时表示在该位置有且只有一个字符，字符的内容不限，必须和LIKE操作符一起使用

下划线(_)的用途与%一样，但下划线只匹配单个字符而不是多个字符

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少

案例1:查询姓"张"且姓名为两个字的员工信息

SELECT * FROM contact_info WHERE employee_name LIKE'张_';

结果展示:

案例2:查询姓"王"且姓名为三个字的员工信息

SELECT * FROM contact_info WHERE employee_name LIKE'王__';

结果展示:

使用两个下划线"__"匹配两个字符

案例3:查询名字中第二个字是"王"并且名字一共是两个字的学生信息

SELECT * FROM contact_info WHERE employee_name LIKE'_王';

结果展示:

3.扩展练习案例

导入数据

DROP TABLE IF EXISTS `world`;
CREATE TABLE `world`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `capital` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `world` VALUES (1, 'China', 'Beijing');
INSERT INTO `world` VALUES (2, 'Singapore', 'Singapore City');
INSERT INTO `world` VALUES (3, 'Germany', 'Berlin');
INSERT INTO `world` VALUES (4, 'Canada', 'Ottawa');
INSERT INTO `world` VALUES (5, 'Mexico', 'Mexico City');
INSERT INTO `world` VALUES (6, 'New Zealand', 'Wellington');
INSERT INTO `world` VALUES (7, 'Yemen', 'Sana\'a');
INSERT INTO `world` VALUES (8, 'Luxembourg ', 'Luxembourg');
INSERT INTO `world` VALUES (9, 'Monaco', 'Monaco-Ville');
INSERT INTO `world` VALUES (10, 'Italy', 'Rome');

world表(部分国家名与首都表)

country:国名   capital:首都

1.找出以Y为开头的国家信息

SELECT country, capital
FROM world
WHERE country LIKE'Y%';

结果展示:

2.找出以Y为结尾的国家信息

SELECT country, capital
FROM world
WHERE country LIKE'%Y';

结果展示:

注意:"%Y"、"%y" 查询的结果都一样，说明LIKE模糊查询不区分大小写

3.找出上述所有国家信息,其国家名包括字母x

SELECT country, capital
FROM world
WHERE country LIKE'%x%';

结果展示:

4.找出上述所有国家信息,其国家名以land作结尾

SELECT country, capital
FROM world
WHERE country LIKE'%land';

结果展示:

5.找出上述所有国家信息,其国家名以C开头,na结尾

SELECT country, capital
FROM world
WHERE country LIKE'C%na';

结果展示:

6.找出上述所有国家信息,其首都名包括字母tt

SELECT country, capital
FROM world
WHERE capital LIKE'%tt%';

结果展示:

7*.找出上述所有国家信息,其国家名包括三个或以上的a

SELECT country, capital
FROM world
WHERE country LIKE'%a%a%a%';

结果展示:

8.找出上述所有国家信息,其国家名以t作第二个字母

SELECT country, capital 
FROM world 
WHERE country LIKE '_t%';

结果展示:

9.找出上述所有国家信息,其国家名都有两个字母o,被另外三个字母相隔着

SELECT country, capital 
FROM world 
WHERE country LIKE '%o___o%';

结果展示:

10.找出上述所有国家信息,其首都名都是由4个字母组成

SELECT country, capital 
FROM world 
WHERE capital LIKE '____';

结果展示:

11.找出上述所有国家信息,其首都和国家名字是相同的

SELECT country, capital 
FROM world 
WHERE country = capital;

结果展示:

12*.找出上述所有国家信息,其首都是国家名字加上"City"

SELECT country, capital 
FROM world 
WHERE capital = CONCAT(country,' City');

结果展示:

13*.找出上述所有国家信息,首都要有国家的名字出现

SELECT country, capital 
FROM world 
WHERE capital LIKE CONCAT('%',country,'%');

结果展示:

14*.找出上述所有国家信息,首都是国家名字的延伸，例如你应显示Mexico City,因它比其国家名字Mexico长;你不应显示Luxembourg,因它的首都和国家名字是相同的

SELECT country, capital 
FROM world 
WHERE capital LIKE CONCAT('%',country,'%') AND capital != country;

结果展示:

15*.首都名"Monaco-Ville"由国家名字"Monaco"和延伸词"-Ville"组合而成，如首都是国家名字的延伸，显示国家名字，及其延伸词

SELECT country,REPLACE(capital,country, '')AS '延伸词'
FROM world 
WHERE capital LIKE CONCAT('%',country,'%') AND REPLACE(capital,country, '') != '';

结果展示: