select
last_name,
department_id,
salary *12*(1+isnull(commission_pct,0))as 年薪
from
employees;#案例:查询没有奖金,且工资小于18000,last_nameselect
salary,last_name
from
employees
where
commission_pct isnulland salary <18000;#案例:查询employees表中,job_id不为'IT'或者工资为12000的员工信息select*from employees
where
job_id <>'IT'or salary=12000;#案例:查看部门departments表的结构desc departments;#案例:查询部门departments表中涉及到了哪些位置编号selectdistinct location_id
from departments;#案例:select*from employees;select*from employees where commission_pct like'%%'and last_name like'%%';select*from employees where commission_pct like'%%'or last_name like'%%'or employee_id like'%%';
排序查询 语法: select 查询列表 from 表 where 筛选条件 order by 排序列表 [asc|desc] 特点: asc代表的是升序,desc代表的是降序,如果不写,默认是升序 order by子句中可以支持单个字段、多个字段、表达式、函数、别名 order by子句一般是放在查询语句的最后面,limit子句除外
#案例1:查询员工信息,要求工资从高到低排序select*from employees orderby salary desc;select*from employees orderby salary;#案例2:查询部门编号>=90的员工信息,按入职时间的先后进行排序[添加筛选条件]select*from employees
where department_id>=90orderby hiredate asc;#案例3:按年薪的高低显示员工的信息和年薪[按表达式排序]select*,salary*12*(1+ifnull(commission_pct,0)) 年薪
from
employees
orderby
salary *12*(1+ ifnull(commission_pct,0))desc;#案例4:按年薪的高低显示员工的信息和年薪[按别名排序]select*,salary*12*(1+ifnull(commission_pct,0)) 年薪
from
employees
orderby 年薪 desc;#案例5:按姓名的长度显示员工的姓名和工资[按函数排序]select
length(last_name) 字节长度,last_name,salary
from
employees
orderby
length(last_name)desc;#案例6:查询员工信息,要求先按工资升序,再按员工编号降序[按多个字段排序]select*from employees
orderby salary asc,employee_id desc;
案例
#案例1:查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序select last_name,department_id,salary*12*(1+ifnull(commission_pct,0)) 年薪
from employees
orderby 年薪 desc, last_name asc;#案例2:选择工资不在8000到17000的员工的姓名和工资,按工资降序select last_name,salary
from employees
where salary notbetween8000and17000orderby salary desc;#案例3:查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序select*,length(email)from employees
where email like'%e%'orderby length(email)desc,department_id asc;