mysql DQL语言条件查询

一、语法
select 查询列表
from 表名
where 筛选条件

select * from jobs where job_id='AC_ACCOUNT';

二、筛选条件的分类
1、简单条件运算符
> < = <> != >= <= <=>安全等于

不等于
select * from jobs where max_salary <> 16000;
安全等于
select * from jobs where max_salary <=> 16000;

2、逻辑运算符
&& and

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

|| or

SELECT
    last_name,
    job_id
FROM
    employees
WHERE
    job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';

! not

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

3、模糊查询
like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符

select
    last_name,
    salary
FROM
    employees
WHERE
    last_name LIKE '__n_l%';

between and

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

in

SELECT
    last_name,
    job_id
FROM
    employees
WHERE
    job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

is null /is not null：用于判断null值

SELECT
    last_name,
    commission_pct
FROM
    employees
WHERE
    commission_pct IS NULL;


#案例1：查询有奖金的员工名和奖金率
SELECT
    last_name,
    commission_pct
FROM
    employees
WHERE
    commission_pct IS NOT NULL；

is not null is null is和null进行搭配 不可以和普通类型数值搭配
<=> 安全等于 可判断null ，也可以当等于用 可读性较低

is null PK <=>
            普通类型的数值 null值       可读性
is null     ×             √           √
<=>         √             √           ×