数据库-条件查询
• 进阶2:条件查询
• 语法:
◇ select
查询列表
from
表名
where
筛选条件;
• 分类:
一、按条件表达式筛选
条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:
&& || !
and or not
三、模糊查询
like
between and
in
is null
SELECT
*
FROM
employees
WHERE
salary > 12000;
SELECT
`last_name`,
`department_id`
FROM
employees
WHERE
`department_id` <> 90;
SELECT
`last_name`,
`salary`,
`commission_pct`
FROM
employees
WHERE
salary >= 10000 AND salary <= 20000;
SELECT
*
FROM
employees
WHERE
`department_id` > 110 OR `department_id` < 90 OR `salary` > 15000;
SELECT
*
FROM
employees
WHERE
last_name LIKE ‘%a%’;
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__n_l%';
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN ('IT_PROG','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;
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
employees;
WHERE
`employee_id` = 176;
• 经典面试题:
select * from employees 和 select * from employees where commission_pct like ‘%%’ and last_name like ‘%%’。
结果是否一样?
答案:不一样,如果判断有null值,就不一样,否则一样。如果中间用or连起来所有的字段,则一样。