/*
like
特点:与通配符搭配使用
通配符:
%任意多个字符,包含0个字符
_任意单个字符
between and
in
is null
is not null
*/
1.like
案例1:查询员工名中包含字符a的员工信息
select*from
employees
where
last_name like'a%';
案例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资alter
SELECT
last_name, salary
FROM
employees
WHERE
last_name LIKE'__e_a%';
案例3:查询员工名中第二个字符为下划线的员名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE'_\_%';
随意指定转义字符$,(escape子句)
SELECT
last_name
FROM
employees
WHERE
last_name LIKE'_$_%'ESCAPE'$';
2.between and
案例1:查询员工编号100到120之间的员工信息
/*
1,使用between and 可以提高语句的简洁度
2,包含临界值
3,两个临界值不能调换顺序
*/#等价语句SELECT*FROM
employees
WHERE
employee_id >=100AND employee_id <=120;#-------------------------SELECT*FROM
employees
WHERE
employee_id BETWEEN100AND120;
3.in
/*
含义:判断某字段的值是否属于in列表中的某一项
特点:
1,使用in提高语句简洁度
2,in列表的值同类型必须一致或兼容
3,
*/#案例:查询员工的工种编号IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号alterSELECT
last_name, job_id
FROM
employees
WHERE
job_id ='IT_PROG'OR job_id ='AD_VP'OR job_id ='AD_PRES';#---------------SELECT
last_name, job_id
FROM
employees
WHERE
job_id IN('IT_PROG','AD_VP','AD_PRES');
4.is null
/*
=或<>不能用于判断null值
is null 或is not null 可以判断null值
*/#案例1:查询没有奖金的员工名和奖金率SELECT
last_name, commission_pct
FROM
employees
WHERE
commission_pct ISNULL;#-----------------#案例2:查询有奖金的员工名和奖金率SELECT
last_name, commission_pct
FROM
employees
WHERE
commission_pct ISNOTNULL;#-----------以下为错误,is搭配null使用select*from
employees
where
salary is12000;
安全等于 <=>
案例1:查询没有奖金的员工名和奖金率
SELECT
last_name, commission_pct
FROM
employees
WHERE
commission_pct <=>NULL;
案例2:查询工资为12000的员工信息
SELECT*FROM
employees
WHERE
salary <=>12000;
is null pk <=>
/*
is null:仅仅可以判断null值,可读性较高,建议使用
<=>:既可以判断null值,又可以判断普通的数值,可读性较低
*/
2. 查询员工号为176的员的姓名和部门号和年薪
SELECT
last_name,
department_id,
salary *12*(1+ IFNULL(commission_pct,0))AS 年薪
FROM
employees;
三、模糊查询/*like特点:与通配符搭配使用通配符:%任意多个字符,包含0个字符_任意单个字符between andinis null is not null%为通配符*/1.like案例1:查询员工名中包含字符a的员工信息select*fromemployeeswherelast_name like 'a%';案例2:查询员工名中第三个字...