MySQL多表联查

复习:单表查询的语法
  select .... from 表名 [where] ...[ group by]..[having]...[order by  asc/desc];

两表查询的92标准
 语法  :select ...from 表名1,表名2  where  表名1.列=表名2.列
  举例:select * from emp e,dept d where d.deptno= e.deptno;

 

--查询部门名称为sales的员工的编号，姓名，职位
select * from dept;
select empno,ename,job,dname from emp e, dept d 
where e.deptno=d.deptno and dname='sales';
--查询每个部门的员工的平均薪水
select dname,avg(sal) from emp e ,dept d where e.deptno=d.deptno group by d.dname
having avg(sal)>2000 order by avg(sal) asc;

 

--多表连查询 ，要求，N张表做连接查询，连接条件为n-1
--多表连查询 ，要求，N张表做连接查询，连接条件为n-1
create table student(
   stuno int(4) primary key auto_increment,
   stuname varchar(20) not null,
   clazzid int(4) 
);
create table clazz(
   clazzno int(4) primary key auto_increment,
   clazzname varchar(20),
   place int (4)
);
create table school(
        schoolid int(4) primary key auto_increment,
  schoolname varchar(20)
);
--外键关系
alter table clazz add constraint fk_place foreign key (place)
REFERENCES school (schoolid);
alter table student add constraint fk_clazzid foreign key(clazzid)
REFERENCES clazz(clazzno);
--添入测试数据
insert into school (schoolname) values ('京南校区');
insert into school (schoolname) values ('西三旗校区');
insert into school (schoolname) values ('东三旗校区');
insert into school (schoolname) values ('上海校区');
insert into school (schoolname) values ('琛圳校区');
select * from school;
insert into clazz (clazzname,place) values ('零基础java',1);
insert into clazz (clazzname,place) values ('java大数据就业',2);
insert into clazz (clazzname,place) values ('H5前端',1);
insert into clazz (clazzname,place) values ('人工智能',2);
insert into clazz (clazzname,place) values ('python',1);
select * from clazz;
insert into student (stuname,clazzid) values ('张三',1);
insert into student (stuname,clazzid) values ('李四',2);
insert into student (stuname,clazzid) values ('王五',1);
insert into student (stuname,clazzid) values ('陈六',1);
insert into student (stuname,clazzid) values ('麻小七',3);
select * from student;
--查询学生的姓名，所学课程名称及所在的校区名称
select stuname,clazzname,schoolname from student s, clazz c,school SH
where s.clazzid=c.clazzno and c.place=SH.schoolid;