Select之多表查询

前面我们讲解的mysql表的查询都是对一张表进行查询，链接为这里写链接内容

今天来学习多表查询。
我们用一个简单的公司管理系统，有三张表EMP ,DEPT,SALGRADE来演示如何进行多表查询。
表内容如下：
创建部门表
drop table if exists dept;
create table if not exists dept
(
deptno INT(2) zerofill not null comment ‘部门编号’,
dname VARCHAR(14) comment ‘部门名称’,
loc VARCHAR(13) comment ‘部门所在地点’
);
创建雇员表
drop table if exists emp;
create table if not exists emp
(
empno INT(6) zerofill not null comment ‘雇员编号’,
ename VARCHAR(10) comment ‘雇员姓名’,
job VARCHAR(9) comment ‘雇员职位’,
mgr INT(4) zerofill comment ‘雇员领导编号’,
hiredate DATETIME comment ‘雇佣时间’,
sal DECIMAL(7,2) comment ‘工资月薪’,
comm DECIMAL(7,2) comment ‘奖金’,
deptno INT(2) zerofill comment ‘部门编号’
);

创建薪资等级表
drop table if exists salgrade;
create table if not exists salgrade
(
grade INT comment ‘等级’,
losal INT comment ‘此等级最低工资’,
hisal INT comment ‘此等级最高工资’
);

插入部门数据
insert into dept (deptno, dname, loc) values (10, ‘ACCOUNTING’, ‘NEW YORK’);
insert into dept (deptno, dname, loc) values (20, ‘RESEARCH’, ‘DALLAS’);
insert into dept (deptno, dname, loc) values (30, ‘SALES’, ‘CHICAGO’);
insert into dept (deptno, dname, loc) values (40, ‘OPERATIONS’, ‘BOSTON’);
插入雇员数据
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17’, 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01’, 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19’, 3000, null, 20);