2020-07-25

模糊查询

案例1：查询姓名中包含字符 e的员工信息

select * from employees where first_name like ‘%e%’;select * from employees where first_name like ‘__e%’;

查询姓名中第二个字符为e，第四个字符为a的员工信息

select * from employees where first_name like ‘_e_a%’

查询姓名中第三个字符为_的员工信息

select * from employees where first_name like ‘___’

显示出表employees部门编号在80-100之间 的姓名、职位

select first_name,job_id from employees where department_id between 80 and 100;

显示出表employees的manager_id 是 100,101,110 的员工姓名、职位

select first_name,job_id from employees where manager_id in (100,101,110);

查询 job_id 为AD_VP或ST_MAN或SA_MAN

select * from employees where job_id in (‘AD_VP’,‘ST_MAN’,‘SA_MAN’)

查询没有奖金的员工select * from employees where commission_pct is null;

查询有奖金的员工

select * from employees where commission_pct is not null;

查询工资大于12000的员工姓名和工资

select first_name,salary from employees where salary > 12000;

查询员工号为176的员工的姓名和部门号和年薪

select first_name,department_id,salary12(1+IFNULL(commission_pct,0)) from employees where employee_id = 176;