黑猴子的家：mysql 查询练习四（模糊查询）

1、模糊查询总结

1.like
一般搭配着通配符使用，用作字符型的判断
通配符：
% 任意多个字符
_ 任意单个字符

2.between and
特点：在...之间
（1）等价于 使用  字段>=值1 and 字段<=值2
（2）两个临界值是包含的
（3）两个临界值不能颠倒顺序

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

4.is null/is not null

2、like

案例1：查询姓名中包含字符 e的员工信息

SELECT * FROM employees
WHERE last_name LIKE '%e%';

案例2：查询姓名中第二个字符为e，第四个字符为a的员工信息

SELECT * FROM employees
WHERE last_name LIKE '_e_a%';

案例3：查询姓名中第三个字符为_的员工信息

SELECT * FROM employees
WHERE last_name LIKE '__\_%';

ESCAPE 自定义 转移符

SELECT * FROM employees
WHERE last_name LIKE '__$_%' ESCAPE '$';

UPDATE employees SET last_name='' WHERE employee_id=100;

3、between and

案例1：显示出表employees部门编号在80-100之间 的姓名、职位

SELECT last_name,job_id,department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

等价于

SELECT last_name,job_id,department_id
FROM employees
WHERE department_id<=100 AND department_id>=80;

4、in

案例1：显示出表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;

案例2：查询 job_id 为AD_VP或ST_MAN或SA_MAN

SELECT last_name,job_id,manager_id
FROM employees
WHERE job_id IN('AD_VP','S_\_MAN');# ×

5、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;