MySQL进阶2 sql选择语句 where

与SQL语句一致

#进阶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 '%%';