MySQL学习笔记：条件查询、排序查询练习

# 条件查询
/*
select 查询列表 from 表名 where 筛选条件;
分类：
1、按照条件筛选：<,>,=,<>,<=，...
2、按照逻辑表达式筛选：&&，||，！  and or not
3、模糊查询：；like,between and, in is null

*/
# 查询薪资>12000的员工信息
use myemployees;
-- select * from employees;
-- select first_name, last_name from employees where salary>12000;
-- select * from employees where salary>12000;
-- 
-- # 查询部门编号不等于90号的员工名和部门编号
-- select concat(first_name,',',last_name) as employee_name,department_id from employees where department_id<>90;
# 逻辑判断符运用
-- select first_name,last_name,salary,commission_pct from employees where salary<=20000 and salary>=10000;
# 查询部门编号不是在90和110之间，或者工资高于15000的员工信息
select * from employees where not(department_id>=90 and department_id <=110) or salary>=15000;
# 模糊查询
-- like：一般和通配符搭配使用： %(任意多个字符，包含0个字符)； _(任意单个字符)
-- between and：特点：简洁，包含临界值， >= a <=, 不能颠倒顺序，值是同类型（或者类型兼容）
-- in：判断某字段的值是否属于in列表中的某一项，特点：简单，值是同类型（或者类型兼容，‘123’=>123），不支持通配符
-- is null 
-- is not null

# like 
select * from employees where department_id not between 90 and 110 or salary>=15000;
# 查询员工名中包含字符a的员工信息 
# like：应用于不精确匹配=模糊匹配查询时候，注意字符型查询的需要加''， % 代表任一个字符， 默认不区分字母大小写
select * from employees where first_name like '%a%' or last_name like '%a%';
# 查询员工名中第三个字符号为e，第五个字符为a的员工名和工资：
select last_name,salary from employees where last_name like 'a_e%';
# 查询员工名中第二个字符为_的员工名,用\对——进行转义
select last_name from employees where last_name like '_\_%'; 
# 加一个$+escape表示该符号为转义字符
select last_name from employees where last_name like '_$_%' escape '$'; 

# between and 
# 查询员工编号在100到120之间的员工信息
select * from  employees where employee_id between 100 and 120;

# in 
# 查询员工工种编号job_id是 IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
select last_name,job_id from employees where job_id in  ('IT_PROG','AD_PRES','AD_PRES');
-- select last_name,job_id from employees where job_id in  ('IT_PROG','AD_%');   错误
# 相当于
# select last_name,job_id from employees where job_id ='IT_PROG' or job_id = 'AD_VP' or job_id = 'AD_PRES';

# is null
# 查询没有奖金的员工名和奖金率
select last_name, commission_pct from employees where commission_pct is null;
-- select last_name, commission_pct from employees where commission_pct = null; 错误，=不可判断null值

# 查询有奖金的员工名和奖金率
select last_name, commission_pct from employees where commission_pct is not null;

# 安全等于 <=> : 可判断null值和普通数值, 可读性较差
select last_name, commission_pct from employees where commission_pct <=> null;
select last_name, commission_pct,salary from employees where salary <=> 12000;

# 条件查询案例讲解：
# 查询员工号为176的员工的姓名、部门编号和年薪
select last_name,department_id,salary*12*(1+ifnull(commission_pct,0)) as annual_salary 
from employees where employee_id = 176;
# 选择工资不在5000到12000的员工的姓名和工资
select last_name,salary from employees where salary not between 5000 and 12000;
# 选择在20号或50号部门工作的员工姓名和部门号；
select last_name, department_id from employees where department_id = 20 or department_id = 50 ;
select last_name, department_id from employees where department_id in (20,50) ;
# 选择公司中没有管理者的员工姓名及job_id;
select last_name,job_id from employees where manager_id is null;
select last_name,job_id from employees where manager_id <=> null;

# 选择公司中有奖金的员工姓名、工资和奖金级别，????
select * from job_grades;
select * from employees;
select last_name,salary*(ifnull(commission_pct,0)) as reward from employees where commission_pct is not null;
# 选择员工信工的第三个字母是a的员工姓名
select last_name from employees where last_name like '__a%';
# 查询没有奖金，且工资小于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;
# 查看部门department表的结构， 可知表中有哪些参数
desc departments;
# 查询部门departments表中哪些位置编号, distinct 关键字
select distinct location_id from departments;

# 区别select * from employees 和 select * from emplyees where commission_pct like '%%' and last_name like '%%' 是否一样？为什么？
# 并不一样，如果判断的字段有null值就不一样，因为commission_pct中有null值

# 区别select * from employees 和 select * from emplyees where commission_pct like '%%' or last_name like '%%' 是否一样？为什么？
# 一样，如果判断的字段有null值就不一样，因为last_name中无null值

 

# 进阶3：排序查询: 按照某种参数的某种特定顺序进行排序
/*
排序查询语法：
select 查询列表 from 表名 【where ...】 order by 排序列表 【asc/ desc】 ； asc 表示升序， desc表示降序
特点：
如果不写 asc, desc 默认asc
可按照多个字段排序
order by 放在查询语句的最后面，limit除外
*/

use myemployees;
select last_name,salary from employees order by salary asc;
desc employees;
select last_name,salary from employees order by salary desc;
# 条件+排序
# 查询部门编号>=90 , 并按照入职时间升序排列的员工信息
desc employees; 
select * from employees where department_id >= 90 order by hiredate asc;

# 表达式排序,表达式过长起别名
# 按年薪高低显示员工信息
select * from employees order by salary*12*(1+ifnull(commission_pct,0)) asc;
select *,salary*12*(1+ifnull(commission_pct,0)) as annual_salary from employees order by annual_salary asc;

# 函数排序，根据last_name字节长度进行升序排序,length()
select last_name,salary from employees order by length(last_name) asc;

# 按照多个字段排序：查询员工信息，先按工资排序，再按员工编号排序
select last_name,salary,employee_id from employees order by salary asc, employee_id desc;

# 测试题
# 查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
select last_name, department_id, salary*12*(1+ifnull(commission_pct,0)) as annual_salary from employees 
order by annual_salary desc, last_name asc;

# 选择工资不在8000到17000的员工的姓名和工资，按工资降序
select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc;

# 查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按照部门号升序
desc employees;
select last_name,email,department_id from employees where email like '%e%' order by length(email) desc, department_id asc;

 

参考资料：https://www.bilibili.com/video/BV12b411K7Zu?p=40&spm_id_from=pageDriver