MySQL学习day4

#2.BETWEEN AND
#案例1：查询员工编号在100到120之间的员工信息
SELECT 
*
FROM
employees
WHERE
employee_id>=100 AND employee_id<=120;
#使用between and 提高语句简洁度，包含临界值，数字顺序不要写反
SELECT 
*
FROM
employees
WHERE
employee_id between 100 AND 120;
#3.in,提高语句简洁度，in列表的值类型一致或者兼容
#案例：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名字和工种编号
SELECT 
last_name,
job_id
FROM
employees
WHERE
job_id='IT_PROG' or job_id='AD_VP' or job_id='AD_PRES';
#---------
SELECT 
last_name,
job_id
FROM
employees
WHERE
job_id in('IT_PROG','AD_VP','AD_PRES');
#4.is null
#案例：查询没有奖金的员工名和奖金率
SELECT 
last_name,
commission_pct
FROM
employees
WHERE
commission_pct is null;
#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;

进阶3：排序查询

/*
语法：
select 查询列表
from表
（where 条件）
order by 排序列表 （asc|desc）
1.asc升序，默认升序
*/
#案例：查询员工信息，工资从高到低排序
select*from employees ORDER BY salary DESC;
select*from employees ORDER BY salary ASC;
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)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) desc;
SELECT*,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 desc;
#案例5：按姓名的长度显示员工姓名和工资（按函数排序）
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) desc;
#案例5：查询员工信息，要求先按工资升序，再按员工编号降序（多字段排序）
SELECT*
FROM employees
ORDER BY salary ASC,employee_id desc;