/*
语法：select 查询列表 from 表名 where 筛选条件
1.按条件表达式筛选
条件运算符：> < = != <> >= <=
2.按逻辑表达式筛选
逻辑运算符：用于连接条件表达式
&& || !
and or not
3.模糊查询
like:一般和通配符搭配使用
%：任意多个字符（包含0个） _:任意单个字符
between and in is null
*/

1.按条件表达式筛选

案例：查询工资大于12000的员工信息

select
        *
from 
        employees
where
        salary>=12000;

案例2：查询部门编号不等于90的员工名和部门编号

select last_name,department_id from employees where department_id !=90;
select last_name,department_id from employees where department_id <>90;

2.按逻辑表达式筛选

案例1：工资在10000-20000之间的员工名、工资以及奖金

select last_name,salary,commission_pct from employees where 
salary>=10000 and salary <= 20000

案例2：查询部门编号不是在90-110之间，或者工资高于15000的员工信息

select * from employees where department_id<90 or department_id>110 or salary>15000;
select * from employees where not(department_id>=90 and department_id<=110) or salary>15000;

3.模糊查询

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

select * from employees where last_name like '%a%';

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

select last_name,salary from employees where last_name like '__n_l%';

案例3：查询员工名中第二个字符为_的员工名

select last_name from employees where last_name like '_\_%';

使用escape指定任意字符进行转义

select last_name from employees where last_name like '_a_%' escape('a');

2.between and(包含临界值，两个临界值不要调换顺序)

案例1：查询员工编号在100到120之间的员工信息

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

3. in关键字

/*
含义：用于某字段的值是否属于in列表中的某一项
特点：使用in提高语句简度 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';
#------------
select last_name,job_id from employees where 
job_id in('it_prot','ad_vp','ad_pres');

4. is null

案例：查询没有奖金的员工名和奖金率

select last_name,commission_pct from employees where commission_pct is null;
select last_name,commission_pct from employees where commission_pct is not null;

5.安全等于<=>

案例一：查询没有奖金的员工名和奖金率

select last_name,commission_pct from employees where commission_pct <=> null;

案例2：查询工资为12000的员工信息

select * from employees where salary <=> 12000;

/*
is null PK <=>
is null :仅仅能判断null值
<=>即能判断null值，也能判断数值型字符
*/

查询员工号为176的员工姓名和部门号和年薪

select employee_id,concat(last_name,first_name) as 姓名,department_id,salary*12*
(1+ifnull(commission_pct,0)) as 年薪 from employees where employee_id <=> 176;

测试

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

select salary,last_name from employees where commission_pct is null and salary<18000;

2.查询employees表中，job_id不为‘it’或者工资为12000的员工信息

select * from employees where job_id <>'it'