mysql学习笔记之模糊查询（进阶三）

USE myemployees;
#进阶三：模糊查询
/*
like (一般和通配符搭配（%任意多个字符和_任意单个字符）使用)
between and
in
is null
*/

#案例一：查询员工中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE ‘%a%’;
#案例二：查询名中第一三五个为字符为a的员工信息
SELECT * FROM employees WHERE last_name LIKE ‘a_a_a%’;
#案例三：特殊：查询名中第二个为字符为_的员工信息(下面的\是转义字符)
#方式一
SELECT * FROM employees WHERE last_name LIKE ‘__%’;
#方式二：指定任意字符为转义字符
SELECT * FROM employees WHERE last_name LIKE ‘q%’ ESCAPE ‘q’;

#between and 案例：查询员工编号在100-200的员工信息
#包含临界值100 200 不可以颠倒顺序（between 200 and 100）
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;
#3. in

#案例：查询员工的工种编号 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id IN (‘IT_PROT’,‘AD_VP’,‘AD_PRES’);
#等价于
SELECT last_name,job_id FROM employees WHERE job_id =‘IT_PROT’ OR job_id =‘AD_VP’ OR job_id = ‘AD_PRES’;

is null (=或<>不能判断null值，得用is null或s not null
#案例：查询没有奖金的员工的员工名和奖金率

SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NULL ;

#注意：不等价于
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct = ‘null’ ;
#安全等于<=>
#案例：查询没有奖金的员工的员工名和奖金率

SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct <=> NULL ;
#查询工资为12000
SELECT
*
FROM
employees
WHERE salary <=> 12000 ;
#is null PK <=>
/*
is null仅仅可以判断null值，但可读性比较低，建议使用
<=> 可读性比较低
*/