MySQL-模糊查询

between and 关键字

案例1:查询员工编号在100到120之间的员工信息

SELECT
         *
FROM 
      employees
WHERE
      employee_id >=100 AND employee_id<=120;

使用between and

SELECT
         *
FROM 
      employees
WHERE
      employee_id BETWEEN  100  AND 120;

注意事项

案例:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

select
         last_name,
         job_id
from 
      employees
where
      job_id='IT_PROT' or job_id='AD_VP' or job_id='AD_PRES';

用in关键字

select
         last_name,
         job_id
from 
      employees
where
      job_id in ('IT_PROT' ,'AD_VP' , 'AD_PRES');

注意事项：

查询没有奖金的员工名和奖金率

SELECT
         last_name,
         commission_pct
FROM 
      employees
WHERE
      commission_pct IS NULL;

查询有奖金的员工名和奖金率

SELECT
         last_name,
         commission_pct
FROM 
      employees
WHERE
      commission_pct IS NOT NULL;

安全等于 <=>

用于判断等于

SELECT
         last_name,
         commission_pct
FROM 
      employees
WHERE
      commission_pct <=> NULL;

案例二：查询工资为12000的员工信息

SELECT
         last_name,
         salary
FROM 
      employees
WHERE
      salary <=> 12000;

PK

查询没有奖金，且工资小于18000的salary，last_name

SELECT
         salary,
         last_name
FROM 
      employees
WHERE
      salary < 18000
AND
      commission_pct IS NULL;

查询部门departments表中涉及到了哪些位置编号（涉及另一个表）

其中desc是用于去重

经典面试题

复习