MySQL条件查询：模糊查询：like，between and，in，

模糊查询：
关键字：like
特点：①一般结合通配符使用：
% 任意数量字符，包含0个
_（下划线）任意单个字符
案例1.查询员工名中含字符a的员工信息

   mysql> select *from 
    -> employees
    -> where last_name like '%a%'; 

案例2.查询员工名中第三个字符为n，第五个字符为l的员工名和工资。

mysql> select last_name,salary
    -> from employees
    -> where last_name like '__n_l%';
+-----------+---------+
| last_name | salary  |
+-----------+---------+
| Hunold    | 9000.00 |
+-----------+---------+
1 row in set (0.04 sec)

案例3.查询员工名中第二个字符为_的员工名。需要用到转义符（\）

mysql> select last_name
    -> from employees
    -> where last_name like '_\_%';
+-----------+
| last_name |
+-----------+
| K_ing     |
| K_ing     |
+-----------+

或者直接用ESCAPE定义转义符

mysql> select last_name
    -> from employees
    -> where last_name like '_$_%' escape '$';
+-----------+
| last_name |
+-----------+
| K_ing     |
| K_ing     |
+-----------+

关键字：between and
特点：①包含连接值，如100<=值<=120,输出的值包含100和120
案例1.查询员工编号在100到120之间的员工信息

mysql> select *from
    -> employees
    -> where employee_id between 100 and 120;

使用运算符方法较为繁琐

mysql> select *from 
    -> employees
    -> where employee_id>=100 and employee_id<=120;

关键字：in类似于or或,格式是:in(?,?,?..);
含义：判断某字段的值是否属于in列表中的某一项
特点：in列表的值类型必须一致或兼容
案例1.查询员工的工种编号是IT_PROG、AD_VP、AD_pres中的一个的员工名和工种编号

mysql> select last_name,job_id
    -> from employees
    -> where job_id in('IT_PROT','AD_VP','AD_preS');
+-----------+---------+
| last_name | job_id  |
+-----------+---------+
| K_ing     | AD_PRES |
| Kochhar   | AD_VP   |
| De Haan   | AD_VP   |
+-----------+---------+

关键字：is null 和 is not null

案例1.查询没有奖金的员工名和奖金率
=和<>不能判断是否为null

mysql> select last_name ,commission_pct
    -> from employees
    -> where commission_pct is null;
+-------------+----------------+
| last_name   | commission_pct |
+-------------+----------------+
| K_ing       | NULL           |
| Kochhar     | NULL           |
| De Haan     | NULL           |

案例2.查询有奖金的员工名和奖金率

mysql> select last_name ,commission_pct
    -> from employees
    -> where commission_pct is not null;
+------------+----------------+
| last_name  | commission_pct |
+------------+----------------+
| Russell    |           0.40 |
| Partners   |           0.30 |
| Errazuriz  |           0.30 |