几种常见的MySQL查询命令以及例子

#2、in
/*
功能：查询某字段的值是否属于指定的列表之内
a in(常量值1，常量值2，常量值3，...)
a not in (常量值1，常量值2，常量值3，...);


*/
#a案例一：查询部门编号是30/50/90的员工名和部门编号
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

#方式二：
SELECT last_name,department_id
FROM employees
WHERE department_id =30 OR department_id=50 OR department_id=90;
#案例二:查询工种编号不是的员工信息
SELECT *
FROM employees
WHERE job_id NOT IN ('sh_clerk','it_prog');

#3/between and/not between and
/*判断某个字段的值是否介于什么什么之间
*/
#案例一：部门编号是30-90之间的部门编号员工姓名
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND department_id<=30;

#案例二：查询年薪不是10000-200000之间的员工姓名、工资、年薪
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))NOT BETWEEN 100000 AND 200000;

#4、is null/is not null
#案例1：查询奖金率为空的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;



#= 只能判断普通的内容
#isnull 只能判断null值
#<=>安全等于，既能判断普通内容，又能判断null值
SELECT *
FROM employees
WHERE commission_pct <=> 0.3;
练习：
SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)),department_id
FROM employees
WHERE employee_id=176;

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 30 AND 50;

SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

SELECT job_id 
FROM employees
WHERE department_id BETWEEN 80 AND 100;