练习：查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;
练习：查询表中commission_pct为null的数据有哪些
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL
练习：查询表中commission_pct不为null的数据有哪些
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS Not NULL
LEAST() / GREATEST() 求最小/最大
SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;
SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;
查询工资在6000 到 8000的员工信息
BETWEEN 条件下界1 AND 条件上界2 （查询条件1和条件2范围内的数据，包含边界）
SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= 6000 AND salary <= 8000;
查询工资不在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#where salary < 6000 or salary > 8000;
IN / NOT IN 查询部门为10,20,30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20 or department_id = 30;
WHERE department_id IN (10,20,30);
查询工资不是6000,7000,8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);
LIKE: 模糊查询
% : 代表不确定个数的字符 （0个，1个，或多个）
_ ：代表一个不确定的字符
查询last_name中包含字符’a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
查询last_name中以字符’a’开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';
查询第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%';
REGEXP \ RLIKE :正则表达式
SELECT 'shkstart' REGEXP '^shk', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;
SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'
FROM DUAL;
选择工资不在5000到12000的员工的姓名和工资
SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
选择在20或50号部门工作的员工姓名和部门号
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (20,50);
选择公司中没有管理者的员工姓名及job_id
SELECT first_name, last_name, job_id
FROM employees
WHERE manager_id IS NULL;
选择公司中有奖金的员工姓名，工资和奖金级别
SELECT first_name, last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
选择员工姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
选择姓名中有字母a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' OR last_name LIKE '%k%';
显示出表 employees 表中 first_name 以 'e’结尾的员工信息
SELECT *
FROM employees
WHERE first_name LIKE '%e';
显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name, job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 110);
