二、多表查询及聚合函数

多表查询及聚合函数

 

/*查询实例*/
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

select * from emp left outer join dept on emp.dno=dept.did left outer join com on dept.cno=com.cid

--多表左连接

/*右连接*/
select * from com right outer join dept on com.cid=dept.cno
--右表中的所有数据，左表中的匹配数据，左表中不能和右表匹配的数据‘不显示’，
--右表中的数据如果在左表中找不到匹配数据，会在对应左表位置显示为NULL

/*内连接*/
create table employee
(
eid int primary key,
ename varchar(10),
reportto int references employee(eid)
)
insert into employee select 1001,'rose',null
union select 1002,'will',1001
union select 1003,'yao',1001
union select 1004,'gigi',1002
select * from employee e inner join employee m on m.eid=e.reportto
--显示员工与其直接上级的对应关系

/*聚合函数*/
create table goods
(
gid int,
gname varchar(10),
price money
)
insert into goods select 1002,'accp',3456
union select 1002,'bccp',56
union select 1003,'cccp',456

错误事例*/
select gid ,avg(price) as avgp from goods
/*正确事例*/
select gid ,avg(price) as avgp from goods group by gid
--单行与聚合不能一起使用，除非一句单行进行分组

/*

/*错误事例*/
select * from goods where price=max(price)
/*正确事例*/
select * from goods where  price=(select max(price) from goods)
--聚合函数不能出现在where指令中