#条件查询
/*
条件查询
语法:
select 查询列表 from 表名
where 筛选条件;
筛选条件:
-按照条件表达式筛选
简单的条件运算符:> < = <>(等同于!=) >= <=
-按照逻辑表达式筛选
作用:用与连接条件表达式
逻辑运算符:&& || ! SQL可以用,但不推荐
SQL语法: and ro not
-模糊查询
like
between and
in
is null
*/
# 按照条件表达式筛选
# 案例一:查询工资大于12000的员工信息
use myemployees;
select * from employees
where salary > 12000;
# 按照逻辑表达式筛选
# 案例:查询工资在10000到20000之间的员工名、工资和奖金
use myemployees;
select first_name,salary,salary * 12 * (1 + commission_put) as '奖金'
from employees
where salary > 10000 and salary < 20000;
# 案例:查询部门编号不是在90到110之间或者工资高于15000的员工信息
use myemployees;
select * from employees
where (department_id < 90 or department_id > 119) or salary > 15000;
#这样写更强
select * from employees
where not(department_id >= 90 and department_id <= 110) or salary > 15000;
# 模糊查询
/*
like / not like
特点:
-一般和通配符搭配使用
通配符:
% 任意多个字符,包括0个字符
_ 任意单个字符
*/
# 案例:查询员工名中包含字符a的员工信息
select * from employees
where last_name like '% a %';
# 案例:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
use myemployees;
select last_name,salary from enployees
where last_name like '__e_a';
# 案例:查询员工名中第二个字符为_的员工名
# escape 设置指定的内容为转义字符
use myemployees;
select last_name from employees
where last_name like '_a_%' escape 'a';
/*
between...and... / not between...and...
功能:判断某个字段的值是否介于xxx和xxx之间
包含临界值的
使用between and可以提高语句的简洁度
两个临界值从小到大写,不要调换顺序
*/
# 案例:查询员工编号在100到120之间的员工信息
select * from employees
where employee_id between 100 and 120;
#案例:查询年薪不是100000到200000之间的员工姓名,工资,年薪。要求:如果奖金率为null,需要转换为0进行计算
select last_name,salary,salary * 12 *(1 + ifnull(commission_put,0)) as '年薪'
from employees
where '年薪' not between 100000 and 200000;
# 案例:查询员工的工种编号是IT_PROG,AD_VP,AD_PRE其中一种的员工姓名和工种编号
use myemployees;
select concat(last_name,first_name) as '姓名',job_id
from employees
where job_id = 'IT_PROG' or 'AD_VP' or 'AD_PRE';
/*
in
含义:判断某字段的值是否属于in列表中的某一段
使用in可以提高语句的简洁度
in列表的值类型必须一致或兼容
in列表中不支持通配符
语法:
字段 in(常量1,常量2,......)
字段 not in(常量1,常量2,......)
*/
select last_name,job_id
from employees
where job_id in('IT_PROG','AD_VP','AD_PRE');
# 案例:查询工种编号不是SH_CLERK或IT_PROG的员工信息
use myemployees;
select * from employees
where job_id not in('SH_CLERK','IT_PROG');
/*
=或<>不能用于判断null,只能判断数值
is null 或is not null 不能用于判断数值,只能用来判断null值
<=> 安全等与 既可以判断普通内容,又可以判断null值
*/
# 案例:查询没有奖金的员工名和奖金率
use myemployees;
# 方式1
select last_name,commission_put
from employees
where commission_put is null;
# 方式2
select last_name,commission_put
from employees
where commission_put <=> null;
# 练习
# 1.查询工资大于12000的员工姓名和工资
use myemployees;
select concat(last_name,first_name) as '姓名',salary
from employees
where salary > 12000;
# 2.查询员工号为176的员工的姓名和部门号和年薪
use myemployees;
select concat(last_name,first_name) as '姓名',
department_id,
salary * 12 *(1 + commission_put) as '年薪'
from employees
where job_id = 176;
# 3.选择工资不在5000到12000的员工的姓名和工资
select concat(last_name,first_name) as '姓名',salary
from employees
where not(salary >= 5000 and salary <= 12000);
# 4、选择在20或50号部门工作的员工姓名和部门号
select concat(last_name,first_name) as '姓名',department_id
from employees
where employee_id in ('20','50');
# 5.选择公司中没有管理者的员工姓名及job_id
select concat(last_name,first_name) as '姓名',job_id
from employees
where manager_id is null;
# 6.选择公司中有奖金的员工姓名,工资和奖金级别
select concat(last_name,first_name) as '姓名',salary
from employees
where not salary * 12 *(1 + commission_put);
# 7.选择员工姓名的第三个字母是a的员工姓名
select last_name
from employees
where last_name like '__a%';
# 8.选择姓名中有字母a和e的员工姓名
select last_name
from employees
where last_name like '%a%'
and last_name like '%e%';
# 9.显示出表 employees表中 first_name以e结尾的员工信息
select * from employees
where first_name like '%e';
# 10.显示出表 employees部门编号在80-100之间的姓名、职位,部门编号
select last_name,job_id,department_id
from employees
where department_id between 80 and 100;
# 11.显示出表 employees的 manager_id是100,101,110的员姓名、职位
select last_name
from employees
where manager_id = '100' and '101' and '110';