非等值连接
select
salary "工资",
grade_level "等级编号"
from
employees e join job_grades j
where
salary between lowest_sal and highest_sal;
非等值连接加条件筛选
- 案例1. 查询员工的工资和工资级别,要求只显示级别为A的
select salary "工资",
grade_level "级别编号"
from
employees e join job_grades j
where
salary between lowest_sal and highest_sal and grade_level = 'A';
自连接
select
e.last_name "员工名称",
m.last_name "领导名称"
from
employees e join employees m
where
e.manager_id = m.employee_id;
练习题
select
max(salary) "最大工资",
avg(salary) "工资平均值"
from
employees;
- 查询员工表的employee_id,job_id,last_name,按department_id降序,salary升序
select
employee_id,
job_id,
last_name
from
employees
order by
department_id desc,
salary asc ;
- 查询员工表的job_id中包含 a和e的,并且a 在e的前面
select
job_id
from
employees
where
job_id like '%a%e%';
//获取当前系统时间
select now();
//去掉前后空格
select trim(" wewew ");
//去掉指定字符 我的
select TRIM("我的" from "我的爱人")
//截取下标为2到末尾的字符为结果
select substr("我的爱人",2);
//截取下标从1到2的数据为结果
select substr("我的爱人",1,2);
连接查询练习题
select
e.last_name,
e.department_id,
d.department_name
from
employees e join departments d
where
e.department_id = d.department_id;
- 2.查询90号部门员工的job_id和90号部门的location_id
select
job_id,
location_id,
d.department_id
from
employees e join departments d
where
e.department_id=d.department_id
and e.department_id = 90;
select
last_name,
department_name,
l.location_id,
city
from
employees e join departments d join locations l
where
e.department_id = d.department_id
and d.location_id = l.location_id
and e.commission_pct is not null ;
select
last_name,
job_id,
d.department_id,
department_name
from
employees e join departments d join locations l
where
e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Toronto';
- 5.查询每个工种,每个部门的部门名,工种名和最低工资,按最低工资升序。
select
department_name "部门名",
job_title "工种名",
min(salary) "最低工资"
from
employees e join departments d join jobs j
where e.department_id = d.department_id
and e.job_id = j.job_id
group by
department_name, job_title
order by min(salary) asc ;
select
country_id "国家编号",
count(*) "个数"
from
locations l join departments d
where
l.location_id = d.location_id
group by
country_id
having count(*)>2;