# 进阶2:条件查询
/*
语法:
select
查询列表
from
表名
where
筛选条件;
分类:
一、按条件表达式筛选
简单的条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符
作用:用于连接条件表达式
&& || !
and or not
&&或and:两个条件都为true,结果为true,反之为false
||或or: 只要有一个条件为true,结果为true,反之为false
!或not:如果连接的条件本身为flase,结果为true,反之为false
三、模糊查询
lick
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;
# 二、按逻辑表达式筛选
# 案例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 not(department_id >= 90 and department_id <= 110) or salary > 15000;
# 三、模糊查询
/*
lick
特点:
1、一般和通配符搭配使用
通配符:
% 任意多个字符,包含0个字符
_ 任意单个字符
between and
in
is null
*/
# 1. lick
# 案例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 '_\_%';
# --------------------
select last_name from employees where last_name like '_$_%' escape '$';
# 2. between and
/*
1、使用between and 可以提高语句的简洁度
2、包含临界值
3、两个临界值不要调换顺序
*/
# 案例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列表中的某一项
特点:
1、使用in提高语句简洁度
2、in列表的值类型必须一致或者兼容
*/
# 案例1:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
select last_name,job_id from employees where job_id = 'IT_PROG' or job_id = 'AD_VP' or job_id = 'AD_PRES';
# --------------------
select last_name,job_id from employees where job_id in ('IT_PROG','AD_VP','AD_PRES');
# 4. is null
/*
=或<>不能用于判断null值
is null 或 is not null 可以判断null值
*/
# 案例1: 查询没有奖金的员工和奖金率
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;
# 安全等于: <=>
# 案例1: 查询没有奖金的员工和奖金率
select last_name,commission_pct from employees where commission_pct <=> null;
# 案例2:查询工资为12000的员工信息
select * from employees where salary <=> 12000;
# 2. 查询员工号为176的员工的姓名和部门号和年薪
select concat(first_name,' ',last_name),department_id,salary*12*(1+ifnull(commission_pct,0)) as '年薪' from employees where employee_id='167';
# 测试
# 一、查询没有奖金,且工资小于18000的salary,last_name
select last_name, salary from employees where commission_pct is null and salary < 18000;
# 二、查询employees表中,job_id不为‘IT’或者工资为120000的员工信息
select * from employees where job_id <> 'IT' or salary = 12000;
# 三、查询部门departments表的结构
desc departments;
# 四、查询部门departments表中涉及哪些位置编号
select distinct location_id from departments;
# 五、经典面试题
/*
试问:select * from employees; 和 select * from employees where commission_pct like "%%" and last_name like "%%;
结果是否一样,并说明原因
*/
select * from employees;
select * from employees where commission_pct like '%%' and last_name like '%%';
# 答 不一样 如果判断的字段有null值