#进阶二(条件查询)
/*
语法:select 字段
from 表名
where 刷选条件
分类:
按条件表达式:
1.>,<.=(c语言中的==),<>(c语言!=),<=,>=
按逻辑表达式:作用:连接条件表达式
2.and or not(c语言中&&,||, !)
按模糊查询:
3.like ,between and ,in, is null
*/
use myemployees;
#案例1:查询员工工资大于12000的员工信息
select * from employees where salary >12000;
#案例2:查询部门编号不等于90的员工名和部门编号
select last_name,department_id from employees where department_id <> 90;
#案例3:查询工资在10000到20000之间的员工名,工资以及奖金
select last_name,salary,commission_pct from employees where salary>=10000 and salary<=20000;
#案例4:查询部门编号不是在90到110之间,或则工资高于15000的员工信息
select * from employees where (department_id <90 or department_id >110) or(salary>=15000 ) ;
#模糊查询
/*
like,特点:一般会与通配符搭配使用,
通配符:
%表示任意多个字符。
_表示任意单个字符,
between and
is null
*/
#案例1:查询员工名中带有的‘a’的所有员工信息
select * from employees where Last_name like '%a%';#注意:%是通配符
#案例2:查询员工名中第三个字符为e第五个字符为a的员工名和工资
select last_name ,salary from employees where last_name like "__e_a%";
#案例3:查询员工名第二个字符为下划线的员工信息
select * from employees where last_name like '_\_%';#注意使用转义符\
select * from employees where last_name like '_$_%' escape '$';#相同作用
#案例:查询员工id在100到120的员工信息
select * from employees where employee_id >=100 and employee_id <=120;
#in的好处就是判断字段的值是否在in列表中选项中。简便
#案例:查询员工工种编号为AD_PRES,AD_VP,IT_PROG的员工信息
select * from employees where job_id in('AD_PRES','AD_VP','IT_PROG');
#is null 用来判断字段值是否为NULL,其他条件运算符不能用来NULL
#案例:查询没有奖金的员工名和员工id
select last_name ,employee_id from employees where commission_pct is null;
#案例:查询有奖金的员工名和员工id
select last_name ,employee_id from employees where commission_pct is not null;
#安全等于 <=> 既可以判断NULL又可以判断普通的数值
#案例:查询工资为12000的员工信息
select *from employees where salary <=>12000;
select * from employees where commission_pct <=> NULL;
//----------------------------------------------------------
#作业1:查询工资大于12000的员工姓名和工资
select last_name ,salary from employees where salary >=12000;
#作业2:查询员工号为176的员工姓名和部门号和年薪
select last_name ,salary,department_id from employees where employee_id=176;
#作业3:查询工资不在5000到12000的员工姓名和工资
select last_name,salary from employees where Not(salary between 5000 and 12000);
#作业4:选择部门编号20或50的员工姓名和部门号
select department_id ,last_name from employees where department_id in (20,50);
#作业5:查询没有奖金,且工资小于18000的salary 和last_name
select salary ,last_name from employees where salary<18000 and commission_pct is null;
#作业6:查询emplyee表,job_id 不为‘IT’或者工资为12000的员工信息
select * from employees where job_id not like'IT%' or salary =12000;
#作业7:查看departments表结构;
desc departments;
#作业8:查询部门departments表中涉及了那些位置编号
select distinct location_id from departments;