SQL——比较运算符

练习：查询员工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);