条件查询

语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件;

1. 按条件表达式筛选
   简单条件运算符：< > = (!= <>) >= <=
2. 按逻辑表达式筛选
   逻辑运算符： AND OR NOT
3. 模糊查询
   LIKE BETWEEN AND IN IS NULL

按条件表达式筛选

#1. 查询工资 > 12000 的员工信息
SELECT * FROM employees WHERE salary > 12000;

#2. 查询部门编号不等于 90 的员工名和部门编号
SELECT
	CONCAT(last_name, " ", first_name) AS 员工名,
	department_id
FROM
	employees
WHERE
	department_id <> 90; # 不等于

按逻辑表达式筛选

AND

#1. 查询工资在 10000 到 20000 之间的员工名，工资和奖金
SELECT
	CONCAT(last_name, " ", first_name) AS 员工名,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary >= 10000
AND salary <= 20000;

OR / NOT

#2. 查询部门编号不是在 90 到 110 之间，或者工资高于 15000 的员工信息
SELECT
	*
FROM
	employees
WHERE
	NOT (department_id >= 90 AND department_id <= 110)
    OR salary > 15000;

模糊查询

LIKE

• 一般和通配符搭配使用
• 通配符：%：任意多个字符，_：任意单个字符

#1. 查询员工名中包含字符“a”的员工信息
SELECT * FROM employees WHERE last_name LIKE "%a%";

#2. 查询员工名中第三个字符为"n",第五个字符为"l"的员工名和工资
SELECT last_name,salary 
FROM employees where last_name LIKE "__n_l%";

#3. 查询员工名中第二个字符为“_”的员工名
# (使用转义字符 \ 或 任意字符 使用 ESCAPE)
SELECT last_name 
FROM employees WHERE last_name LIKE "_$_%" ESCAPE "$";

BETWEEN AND

• 包含左右临界值

#1. 查询员工编号在 100 到 120 之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

IN

#1. 查询员工的工种编号是 AD_VP,IT_PROG,AD_PRES中的一个的员工名和工种编号
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN (
		"AD_VP",
		"IT_PROG",
		"AD_PRES");

IS NULL 和 IS NOT NULL

#1. 查询没有奖金的员工名和奖金率
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;

<=>：安全等于

• 可以判断 NULL 和 普通值

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