查询操作/模糊查询day02

模糊查询

---

1.like

一般搭配着通配符使用，用作字符型的判断

通配符：

% 任意多个字符

_ 任意单个字符  1 

2.between and

特点：在...之间

①等价于 使用  字段>=值1 and 字段<=值2

②两个临界值是包含的  [1,10] 

③两个临界值不能颠倒顺序

3.in

特点：判断某个字段的值是否在某个列表内

in(值1，值2，...)

等价于  or

4.is null/is not null

---

#1.LIKE的使用

#案例1.查询姓名张包含字符e的员工信息
SELECT*FROM employees
where last_name LIKE '%e%';

#案例2：查询姓名中第二个字符为e，第四个字符为a的员工信息
SELECT*FROM employees
where last_name like'_e_a%';

#案例3：查询姓名中第三个字符为_的员工信息
SELECT * FROM employees
WHERE last_name LIKE '__\_%';

斜线                         \  ->   window  斜线

                                /  ->    Linux 斜线

 反斜线

#ESCAPE自定义 转移符
SELECT * FROM employees
WHERE last_name like'__$_%' ESCAPE '$';

ESCAPE '$';  这句话是 让$ 拥有转义字符的形式和

#2.BETWEEN AND

#案例1：显示出表employees部门编号在80-100之间的姓名、职位
方法一：SELECT last_name,job_id,department_id FROM employees
where department_id>=80 AND department_id<=100;

方法二：SELECT last_name,job_id,department_id FROM employees
WHERE department_id BETWEEN 80 AND 100;

#3.in

#案例1：显示出表employees的manager_id是100，101，110的员工姓名、职位
SELECT last_name,job_id FROM employees
WHERE manager_id IN(100,101,110);

#案例2：查询job_id为AD_VP或ST_MAN或SA_MAN
我:SELECT last_name,job_id,department_id FROM employees
WHERE job_id IN('AD_VP','ST_MAN','SA_MAN');

老师：SELECT last_name,job_id,manager_id
FROM employees
WHERE job_id IN('AD_VP','S_\_MAN');

#4.is null/is not NULL

#案例1:查询没有奖金的员工
SELECT * FROM employees
WHERE commission_pct is NULL;

#案例2：查询有奖金的员工
方法一：SELECT * from employees
WHERE commission_pct is not null;

方法二：SELECT * FROM employees
WHERE NOT (commission_pct is null);

模糊查询练习

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

SELECT last_name,salary FROM employees
where salary>12000;

#2.查询员工号为176的员工姓名和部门号和年薪
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
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 last_name,department_id FROM employees
WHERE department_id=20 OR department_id=50;

方法二：SELECT last_name,department_id 
FROM employees
WHERE department_id IN(20,50);

#5.选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id FROM employees
WHERE manager_id is null;

#6.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct is not NULL;

#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';