#进阶2:条件查询
/*
语法:select
查询列表 第三步执行
from
表名 第一步执行
where
筛选条件; 第二步执行
分类:
一、按条件表达式筛选
条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:&& || ! a
and or not
三、模糊查询
like
between and
in
is null | is not null
*/
#一、按条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id != 90 ;
#二、按逻辑表达式筛选
#案例1:查询工资在10000到20000之间的员工名、工资以及奖金
SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;
#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT * FROM employees WHERE NOT (department_id>=90 AND department_id<=110 ) OR salary>15000;
#三 、模糊查询
/*
like:
1一般和通配符搭配使用
between and
in
is null | is not null
*/
#案例1:查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
#案例2:查询员工名中第三个字符为n,第五个字符为l的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';
#案例3:查询员工名中第二个字符为_的员工名
#方法1
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
#方法2
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
#2.between and
#案例1:查询员工编号在100到120之间的员工信息
SELECT *FROM employees WHERE employee_id BETWEEN 100 AND 120 ;
#3.in
#案例:查询员工的工种编号是 IT_PROG 、 AD_VP 、AD_PRES 的一个员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
#4、isnull
/*
= 或<>不能用于判断null值
is null 或 is not null 可以判断null值
*/
#案例1:查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
#案例2:查询有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
#安全等于 <=>
#案例1:查询没有奖金的员工名和奖金率
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值,又可以判断普通的数值,可读性较低
#2.查询员工号为176的员工的姓名和部门号和年薪
SELECT last_name,department_id,salary * 12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE employee_id = 176;
#注意:
SELECT * FROM employees ;
#和
SELECT *FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%';
#执行结果是不一样的,因为commission_pct 中有null 值,如果字段中没有null值,则执行结果都一样。
排序查询
#进阶3:排序查询
/*
语法: select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点: 1.asc 升序 desc 降序
不写 默认为升序
2、order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by 子句一般是放在查询语句的最后面,limit子句除外
*/
#案例:查询员工信息,要求工资从高到底
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary ;
#案例2:查询步迷们编号>=90的员工信息,按入职时间的先后进行排序
SELECT * FROM employees WHERE department_id>90 ORDER BY hiredate ASC ;
#案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例4:按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
#案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#案例6:查询员工信息,要求先按工资升序,再按员工编号降序【按多个字段排序】
#当工资相同时,再按员工编号降序。
SELECT *FROM employees
ORDER BY salary ASC , employee_id DESC ;
#查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC ;
#选择工资不在8000到17000的员工的姓名和工资、按工资降序
SELECT last_name ,salary
FROM employees
WHERE NOT salary BETWEEN 8000 AND 17000
ORDER BY salary DESC ;
SELECT last_name ,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC ;
#查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT * FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;