模糊查询
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';