Mysql中模糊查询

#模糊查询
/*
1.like
一般搭配着通配符使用，用作字符型的判断
通配符：
% 任意多个字符
_ 任意单个字符 1

2.between and
特点：在…之间
①等价于 使用 字段>=值1 and 字段<=值2
②两个临界值是包含的 [1,10]
③两个临界值不能颠倒顺序

3.in
特点：判断某个字段的值是否在某个列表内
in(值1，值2，…)

等价于 or

4.is null/is not null
*/

#1.like

#案例1：查询姓名中包含字符 e的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘%e%’;

#案例2：查询姓名中第二个字符为e，第四个字符为a的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘_e_a%’;

#2.between and

#案例：显示出表employees部门编号在80-100之间 的姓名、职位
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
WHERE department_id BETWEEN 100 AND 80;

#等价于
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id<=100 AND department_id>=80;

#3.in
#案例：显示出表employees的manager_id 是 100,101,110 的员工姓名、职位

SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id IN(100,101,110);

#等价于
SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id=100 OR manager_id=101 OR manager_id = 110;

#4.is null / is not null

#案例1：查询没有奖金的员工
SELECT * FROM employees
WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工
SELECT * FROM employees
WHERE commission_pct IS NOT NULL;

SELECT * FROM employees
WHERE NOT(commission_pct IS NULL);

SELECT * FROM employees WHERE last_name IS NULL;