8.条件查询（selectfromwhere）#进阶⼆：条件查询
/*
语法:
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 >=10000AND salary <=20000;
#查询部门编号不再90到110，或者gongzi⾼于15000的员⼯信息
SELECT*FROM employees WHERE salary >15000OR department_id <=90OR 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 BETWEEN100AND120;
#in查询员⼯的⼯种编号是 IT_PROG, AD_VP,AD_PRES的员⼯名和公众编号
/*
in
*/
select distinct fromSELECT 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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。