#进阶二:条件查询
/*
语法:
select
查询列表
from
表名
where
筛选条件
分类:
1.按条件表达式筛选 < > = <> >= <=
2.按逻辑表达式筛选 逻辑运算符 && || !
and or not
3.模糊查询
*/
#1.条件表达式筛选
SELECT * FROM employees WHERE salary >= 12000;
# 部门编号不等于90的员工名和部门编号
SELECT first_name , department_id FROM employees WHERE department_id <> 90;
#2.逻辑表达式筛选
#查询工资在10000到20000之间
SELECT first_name FROM employees WHERE salary >= 10000 AND salary <= 20000;
#查询部门编号不再90到110,或者gongzi高于15000的员工信息
SELECT * FROM employees WHERE salary > 15000 OR department_id <= 90 OR department_id >= 110;
#3.模糊查询
/*
like : 和通配符搭配使用 %: 任意多个字符,包含零个字符 _任意单个字符
between and
in
isnull | is not null
*/
#like
#查询员工名包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
#查询员工名中第三个字符为n第五个为l
SELECT * FROM employees WHERE last_name LIKE "__n_l%";
#查询第二个字符为_员工名
#使用\将_转义
#或者自定义转义字符使用ESCAPE
SELECT * FROM employees WHERE last_name LIKE "_\_%";
SELECT * FROM employees WHERE last_name LIKE "_$_%" ESCAPE '$';
#between and
/*
1.使用简便
2.包含临界值
3**.不可以调换顺序**
*/
#查询员工编号在100到120之间的员工信息:between 100 and 120
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
#in查询员工的工种编号是 IT_PROG, AD_VP,AD_PRES的员工名和公众编号
/*
in
*/
SELECT last_name, job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP');
# is 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;
# 安全等于 <=>:可以判断null
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT last_name, commission_pct,salary FROM employees WHERE salary <=> 12000;
# is null 和 <=>
IS NULL :只可以判断NULL值,可读性较高
<=> 既可以判断NULL值,也可以判断普通的数值,可读性较低
练习:
# 2.查询员工号为176的员工姓名和部门号和年龄
SELECT last_name,department_id, ((salary + salary*IFNULL(commission_pct,0)) * 12) AS 年薪 FROM employees WHERE employee_id = 176;
# 3.查询没有奖金且工资小于18000的salary,姓名
SELECT last_name, salary FROM employees WHERE commission_pct <=> NULL AND salary < 18000;
# 4.查询employee表中,job_id不为'IT'或者工资为12000的员工信息
SELECT * FROM employees WHERE job_id NOT IN ('IT') OR salary = 12000;
# 5.查看departments表的结构,
DESC departments;
# 6.查询部门departments表中涉及到哪些位置编号 使用distinct
SELECT DISTINCT location_id FROM departments;