数据库----条件查询学习笔记代码

#进阶2:条件查询
语法：
select
查询列表 3
from
表名 第一步1
where
筛选条件; 2

分类：
一，按条件表达式筛选
条件运算符：> < = != <> >= <=
二，按逻辑表达式筛选
逻辑运算符：&& || ！and or not
三，模糊查询
like
between and
in
is null

#一，按条件表达式筛选
#条件运算符：> < = != <> >= <=
#1，查询工资12000的员工
SELECT 
  * 
FROM
  employees 
WHERE salary > 12000 ;

#2,查询部门编号不等于90的员工名和部门编号
SELECT 
  last_name,
  department_id 
FROM
  employees 
WHERE department_id!=90;
#WHERE department_id<>90;

#二，按逻辑表达式筛选
#逻辑运算符：&& || ！and  or not
#查询工资10000，20000之间的员工名，工资，奖金
SELECT 
  `first_name` , `salary` , `commission_pct` 
FROM
  employees 
WHERE salary > 10000 
  AND salary < 20000 ;
#查询部门编号不是再90到110之间，或者工资高于15000的员工信息
SELECT 
  * 
FROM
  employees 
WHERE `department_id` < 90 
  OR `department_id` > 110 
  OR `salary` > 15000 ;
  
SELECT 
  * 
FROM
  employees 
WHERE NOT(`department_id` >= 90 
  AND `department_id` <= 110 )
  OR `salary` > 15000 ;

#三，模糊查询
#1 like 
#查询员工名中包含字符a的员工信息
SELECT 
  * 
FROM
  employees 
WHERE last_name LIKE '%a%' ;#%代表任意一个字符，说明a前面，后面都有字符
#查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE last_name LIKE '%_a__n%';

#2 between and 
#查询员工编号在100到120之间的员工信息
SELECT 
  * 
FROM
  employees 
WHERE employee_id>=100 AND employee_id<=120;


SELECT 
  * 
FROM
  employees 
WHERE employee_id BETWEEN 100 AND 120;#包含临界值，不能颠倒临界值

#3 in 
#用于判断莫字段的值是否属于in表中的一项
#查询员工的工种编号是IT_PROG,AD_VD,AD_PRES中的一个员工名和工种编号
SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id='IT_PROG' OR job_id='AD_VD' OR job_id='AD_PRES';

#------------------------
SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id IN ('IT_PROG','AD_VD' ,'AD_PRES');

#4 is null
#=或者<>不能用于判断null
#查询没有奖金的员工名和奖金率
SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct =NULL;#不能查询
#----------------------------------
SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NULL;
#查询有奖金的
SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NOT NULL;

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

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

#is null pk <=>
#is null :仅仅可以判断null值，可读性较高建议使用
#<=>:既可以判断null值，又可以判断普通的数值，可读性较低