继上文查询相关语句的一些案例

#安全等于：<=>
#案例：查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=> NULL;

#案例2：查询工资为12000的员工信息
SELECT
	last_name,
	salary
FROM
	employees
WHERE
	salary <=> 12000;

# is null pk <=>
/*
IS NULL:紧紧可以判断null值，可读性较高，建议使用 
<=>：既可以判断null值，又可以判断普通的数值，可读性较低
*/
#测试
#1.查询工资大于12000的员工姓名和工资
SELECT
	salary
FROM
	employees
WHERE
	salary > 12000;

#2.查询员工号为176的员工的姓名和部门号和年薪
SELECT
	last_name,
	department_id,
	salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
	employees;

#3.选择工资不在5000到12000的员工的姓名和工资
SELECT
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	NOT (salary < 5000 AND salary > 12000);

#----------------------------------------------------
SELECT
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	salary NOT BETWEEN 5000
AND 12000;

#4.选择在20或50号部门工作的员工姓名和部门号
SELECT
	first_name,
	last_name,
	department_id
FROM
	employees
WHERE
	department_id = 20
OR department_id = 50;

#5.选择公司中没有管理者的员工姓名及job_id
SELECT
	first_name,
	last_name,
	job_id
FROM
	employees
WHERE
	manager_id IS NULL;

#6.选择公司中有奖金的员工姓名、工资和奖金级别
SELECT
	first_name,
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	commission_pct <> 'NULL' #7.选择员工姓名的第三个字母是a的员工姓名
	SELECT
		last_name
	FROM
		employees
	WHERE
		last_name LIKE '__a%';

#8.选择姓名中有字母a和c的员工姓名
SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '%a%'
AND last_name LIKE '%c%';

#9.显示出来表中first_name以'e'结尾的员工信息
SELECT
	first_name
FROM
	employees
WHERE
	first_name LIKE '%e';

#10.显示出部门编号在80-100之间的姓名、职位
SELECT
	first_name,
	last_name
FROM
	employees
WHERE
	department_id BETWEEN 80
AND 100;

#11.显示manager_id是100,101,110的员工姓名、职位
SELECT
	first_name,
	last_name,
	job_title
FROM
	employees
JOIN jobs
WHERE
	manager_id = 100
OR manager_id = 101
OR manager_id = 110;

#查询没有奖金，且工资小鱼18000的salary，last_name
SELECT
	salary,
	last_name
FROM
	employees
WHERE
	commission_pct IS NULL
AND salary < 18000;

#查询表中job_id不为'it'或者工资为12000的员工信息
SELECT
	*
FROM
	employees
WHERE
	job_id <> 'IT'
OR salary = 12000;

#查看部门deparments表的结构
DESC departments;

#查询部门departments表中设计到了那些位置编号
SELECT DISTINCT
	location_id
FROM
	departments;