SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
案例:
查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ... ON ...
表结构: emp , dept
连接条件: emp.dept_id = dept.id
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
表的别名:
tablea as 别名1 , tableb as 别名2 ;
tablea 别名1 , tableb 别名2 ;
注意事项:
一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。
4. 外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
案例:
查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
查询dept表的所有数据, 和对应的员工信息(右外连接)
由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d
on e.dept_id = d.id where e.age >40 ;
查询所有员工的工资等级
表: emp , salgrade
连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s
where e.salary >=s.losal and e.salary <= s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s
where e.salarybetween s.losal and s.hisal;
查询 "研发部" 所有员工的信息及工资等级
表: emp , salgrade , dept
连接条件 : emp.salary between salgrade.losal and salgrade.hisal ,
emp.dept_id = dept.id
查询条件 : dept.name = '研发部'
select e.* , s.grade from emp e , dept d , salgrade s
where e.dept_id = d.id
and (e.salary between s.losal and s.hisal ) and d.name = '研发部';
查询 "研发部" 员工的平均工资
表: emp , dept
连接条件 : emp.dept_id = dept.id
select avg(e.salary) from emp e, dept d
where e.dept_id = d.id and d.name = '研发部';
查询工资比 "灭绝" 高的员工信息。
①. 查询 "灭绝" 的薪资
select salary from emp where name = '灭绝';
②. 查询比她工资高的员工数据
select * from emp
where salary > ( select salary from emp where name = '灭绝' );
查询比平均薪资高的员工信息
①. 查询员工的平均薪资
select avg(salary) from emp;
②. 查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );
查询低于本部门平均工资的员工信息
①. 查询指定部门平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;
②. 查询低于本部门平均工资的员工信息
select * from emp e2
where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );
查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;