#03-过滤数据
SELECT last_name,salary,hire_date
FROM employees
#where department_id = 90;
WHERE last_name = 'King';
#where hire_date = '1989-09-21';
#在查询中使用字符串、日期型变量时,需要使用一对''表示
#使用一对''表示的字符串本身,在sql标准中是区分大小写的。但是mysql中不区分。
DESC employees;
#1.常见的比较运算符: = > >= < <= <> !=
SELECT last_name,salary,hire_date
FROM employees
WHERE salary >= 6000;
#2. between 下界 and 上界 (包含边界)
SELECT last_name,salary
FROM employees
#where salary between 6000 and 8000;
#where salary <= 8000 and salary >= 6000;
WHERE last_name BETWEEN 'Ernst' AND 'Urman';
#3. in (set)
SELECT last_name,salary,department_id
FROM employees
#where department_id in (50,60,70);
WHERE department_id = 50 OR department_id = 60 OR department_id = 70;
#4. 模糊查询:like
#练习:查询姓名中包含字符a
# %:表示0个,1个或多个字符
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#练习:查询姓名中包含字符a且包含字符e
SELECT last_name
FROM employees
#WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#练习:查询第3个字符是a
# _:表示一个字符
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
#练习:查询第2个字符是_且3个字符是a
SELECT last_name
FROM employees
#WHERE last_name LIKE '_\_a%'; # "刘昊\"好帅\""
WHERE last_name LIKE '_$_a%' ESCAPE '$';
#4. is null:查询空值
SELECT employee_id,last_name,commission_pct
FROM employees
#where commission_pct is null;
WHERE commission_pct IS NOT NULL;
# 5. 逻辑运算符:and && or || not xor
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
&& job_id LIKE '%MAN%';
# 6. 算术运算符: + - * / %