8.2 查询员工名中第三个字符为 e, 第五个字符为 a 的员工名和工资(like用法)
# select last_name, salary from employees where last_name like "__e_a%";
8.3 查询员工名中第二个字符为 _ 的员工名
# select last_name from employees where last_name like "_\_%";(\为转义字符)
# select last_name from employees where list_name like "_$_%" escape "$";(定义转义字符为 $)
8.4 between and 在某个范围(1) 使用between and 可以提高语句的简洁度(2)包含临界值(3) 两个临界值不能调换位置
8.5 查询员工编号在100到120之间的员工信息
# select * from employees where employee_id >=100 and employee_id <=200;
# select * from employees where employee_id between 100 and 200;
8.6in 包含某个
8.3.1 查询员工的工种编号是 IT_PROG,AD_VP,AD_PRES中的一个员工名的工种编号
# select last_name, job_id from employees where job_id="IT_PROT" or job_id="AD_VP" or job_id="AD_PRES";
# select last_name, job_id from employees where job_id in("IT_PROT","AD_VP","AD_PRES")
8.7 is null 判断是否为 null(筛选出值为null的数据)8.4.1 查询没有奖金的员工名和奖金率
# select last_name, commission_pct from employees where commission_pct is null;
8.8 is not null 判断不是 null(筛选出值不是 null 的数据)8.5.1 查询有奖金的员工名和奖金率
# select last_name, commission_pct from employees where commission_pct is not null;
8.9<=> 安全等于 既可以判断 null等于, 又可以判断数字等于
# select last_name, commission_pct from employees where commission_pct <=>null;
# select last_name, commission_pct from employees where commossion_pct <=>12000;
9.1排序查询:(select 查询列表 from 表名 [where 筛选条件] order by 排序列表 [asc | desc])
(1)查询员工信息, 要求工资从高到低排序
# select * from employees order by salary desc;// 可以省略desc, 默认从高到低
9.2 查询部门编号 >=90 的员工信息, 按照入职时间的先后进行排序
# select * from employees where department_id >=90 order by hiredate asc;
9.3 按年薪的高低显示员工的信息和年薪[按表达式排序]
# select *, salary*12*(1+ifnull(commission_pct,0))as 年薪 from employees order by salary*12(1+ifnull(commission_pct,0)) desc;
# select *, salary*12*(1+ifnull(commission_pct,0))as 年薪 from employees oder by 年薪 desc;
9.4 按姓名的长度显示员工的姓名和工资[按函数排序]
# select length(last_name)as 字节长度, last_name, salary from employees order by 字节长度 desc;
9.5 查询员工信息, 要求先按工资升序, 再按员工编号降序[按多个字段排序]
# select * from employees order by salary asc, employees_id desc;
9.6排序查询:(select 查询列表 from 表名 [where 筛选条件] order by 排序列表 [asc | desc])(1) asc 代表的是升序, desc 代表的是降序, 如果不写,默认是升序(2) order by 子句中可以支持单个字段, 多个字段, 表达式, 函数,别名(3) order by 子句中一般是放在查询语句的最后面, limit子句除外
9.7 查询员工的姓名和部门号的年薪, 按年薪降序, 按姓名升序
# select last_name, department_id, salary*12*(1+ifnull(commission_pct,0))as 年薪 from employees order by 年薪 desc, last_name asc;
9.8 选择工资不在 8000 到 17000 的员工的姓名和工资, 按工资降序
# select last_name, salary from employees where salary not between 8000 and 17000 order by salary desc;
9.9 查询邮箱中包含 e 的员工信息, 并先按邮箱的字节数降序, 再按部门号升序.
# select *,length(email) from employees where email like "%e%" order by length(email) desc, department_id asc;
14.2case 函数的使用(1)
case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2...else 要显示的值n或语句n
end
14.3 查询员工的工资要求:部门号=30, 显示的工资为1.1倍,部门号=40, 显示的工资为1.2倍;部门号=50,显示的工资为1.3倍; 其他部门, 显示的工资为原工资
select salary as 原工资, department_id,case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.4else salary
end as 新工资 from employees;
14.4case 函数的使用(2)
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2....else 要显示的值n或语句n
end
14.5 查询员工的工资的情况
如果工资 >20000, 显示 A 级别
如果工资 >15000, 显示 B 级别
如果工资 >10000, 显示 C 级别
否则, 显示 D 级别
select salary,case
when salary >20000 then "A"
when salary >15000 then "B"
when salary >10000 then "C"else"D"
end as 工资级别
from employees;
25.6 将员工的姓名按首字母排序, 并写出姓名的长度
# select length(last_name)as 长度,substr(last_name,1,1)as 姓首字母, last_name from employess order by 首字母;
25.7 做一个查询, 产生下面的结果
(2) <last_name> earns <salary> monthly but wants <salary*3>eg: King earns 24000 monthly but wants 72000
# select concat(last_name,"earns", salary,"monthly but wants", salary*3) from employess when salary =24000;
25.8 做一个查询, 产生下面的结果
(1)last_name job_id Grade
kind AD_PRESA
# select job_id as job,case job_id
when "AD_PRES" then "A"
when "ST_MAN" then "B"....
end as Grade
from employees
when job_id ="AD_PRES"