#进阶2: 条件查询

/*

  语法

    select 查询列表  #3

    from 表名        #1

    where 筛选条件;  #2

  分类:

    1.按条件表达式进行筛选 > < = != >= <=

    2.按逻辑表达式筛选  逻辑运算符 : and or not

    3.模糊查询  : like , between and ,in ,is null ,not like

*/

USE myemployees;

#1. between A and B ,包含区间端点A和B ,要求A<=B,不可颠倒

SELECT *

FROM employees

WHERE employee_id >= 100 AND employee_id <=120;

SELECT *       #等价的  between A and B

FROM employees

WHERE employee_id BETWEEN 100 AND 120;

#2. in  : 判断某字段的值是否属于in()列表中的某一项,

    #案例:查询员工的工种编号: job_id : AC_MGR /AD_ASST /AD_PRES

SELECT job_id

FROM employees

WHERE job_id IN ('AC_MGR','AD_ASST','AD_PRES'); #不支持'%'/'_' 等通配符,等价于 job_id ='AC_MGR' and ...

SELECT job_id

FROM employees

WHERE job_id IN ('AC_MGR','AD_%','AD_PRES');

#3. is null

/*

    =或者!= 不可用于判断控制

    is null 或 is not null 可以判断null值

*/

#案例1:查询没有奖金的员工名字 和奖金率

SELECT last_name,commission_pct

FROM employees

WHERE commission_pct IS NULL;   #结果72行

#4.安全等于 <=>

    #既可以判断NULL值,也可以判断普通的数值,可读性较差,不建议用

SELECT last_name,commission_pct ,salary

FROM employees

WHERE commission_pct <=> NULL;  

#---------------------------------------------

#案例(一致!!):

    #1.查询没有奖金,且工资小于18000的salary ,last_name

SELECT * FROM employees;

SELECT * FROM employees

WHERE first_name LIKE '%%' AND last_name LIKE '%%' AND employee_id LIKE '%%';