多表查询及聚合函数

?

/*查询实例*/
create table com
(
cid int primary key,
cname varchar(10),
ctel varchar(20)
)--公司表

create table dept
(
did int primary key,
dname varchar(10),
dtel varchar(20),
cno int references com(cid)
)--

部门表

create table emp
(
eid int primary key,
ename varchar(10),
etel varchar(10),
dno int references dept(did)
) --

员工表

/*

插入数据*/
insert into com select 1001,'sun','120'
union select 1002,'IBM','130'
union select 1003,'top','140'
union select 1004,'MS','150'--union 集合运算符，批量操作
insert into dept select 2001,'财务部','110',1001
union select 2002,'行政部','120',1001
union select 2003,'组织部','130',1001
union select 2004,'人事部','140',1001
insert into emp select 3001,'rose','110',2001
union select 3002,'jack','120',2002
union select 3003,'tom','130',2003
union select 3004,'mike','140',2004
union select 3005,'wilin','150',2002

/*

多表连接查询*/
select * from dept left outer join emp on emp.dno=dept.did

--左外连接，左表的所有数据以及右表的匹配数据，不匹配显示NULL