Mysql表关系 连接查询

• 查询简单多表

语法格式如下：

select  字段1,字段2... from 表1,表2... [where 条件]  

e.g. select c.name,c.score,h.hobby from class as c,hobby as h  where c.name=h.name;  select name,salary,dname from person,dept where person.dept_id = dept.id;  select name,salary,dname from person,dept  where person.dept_id = dept.id and salary>=20000; 

笛卡尔积现象是将A表的每一个记录与B表的每一个记录强行拼在一起。因此，如果A表有n个记录，B表中有m条记录，笛卡尔积的结果会产生n*m条记录。

select * from class,hobby; 

内部连接查询只能找到合格的记录。事实上，结果与表相关查询相同。官方建议使用内部连接查询。

SELECT 字段列表     FROM 表1  INNER JOIN  表2 ON 表1.字段 = 表2.字段;  

select name,salary,dname from person inner join dept on person.dept_id = dept.id where salary>=20000; 

SELECT 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;

e.g. 
select name,salary,dname
from person left join dept
on person.dept_id = dept.id
where salary>=20000;

-- 查询每个部门员工人数
select dname,count(name) from dept left join person on dept.id=person.dept_id group by dname;

SELECT 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

e.g.
select dname,count(name)
from person right join dept
on person.dept_id = dept.id
group by dname;

注意：我们尽量使用数据量大的表作为基准表,放在前面。

综合查询练习

create table class(cid int primary key auto_increment,caption char(4) not null);
                  
create table teacher(tid int primary key auto_increment,tname varchar(32) not null);
                    
create table student(sid int primary key auto_increment,
                    sname varchar(32) not null,
                    gender enum('male','female','others') not null default 'male',
                    class_id int,
                    foreign key(class_id) references class(cid) 
                     on update cascade on delete cascade);
                    
create table course(cid int primary key auto_increment,
                   cname varchar(16) not null,
                   teacher_id int,
                   foreign key(teacher_id) references teacher(tid)
                    on update cascade on delete cascade);
                   
create table score(sid int primary key auto_increment,
                  student_id int,
                  course_id int,
                  number int(3) not null,
                  foreign key(student_id) references student(sid)
                   on update cascade on delete cascade,
                   foreign key(course_id) references course(cid)
                   on update cascade on delete cascade);
                   
insert into class(caption) values('三年一班'),('三年二班'),('三年三班');
insert into teacher(tname) values('魏老师'),('祁老师'),('小泽老师');
insert into student(sname,gender,class_id) values('钢蛋','female',1),('铁锤','female',1),('山炮','male',2),('彪哥','male',3),('虎子','male',3),('妞妞','female',2),('建国','male',2);
insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2);
insert into score(student_id,course_id,number) values(1,1,60),(1,2,59),(2,2,100),(3,2,78),(4,3,66),(2,3,78),(5,2,77),(6,1,84),(7,1,79),(5,3,80),(3,1,59);

1. 查询每位老师教授的课程数量
2. 查询各科成绩最高和最低的分数,形式 : 课程ID  课程名称 最高分  最低分
3. 查询平均成绩大于85分的所有学生学号,姓名和平均成绩
4. 查询课程编号为2且课程成绩在80以上的学生学号和姓名
5. 查询各个课程及相应的选修人数
6. 查询每位学生的姓名，所在班级和各科平均成绩



1. 查询每位老师教授的课程数量
select tname,count(cname)
from teacher left join course
on teacher.tid = course.teacher_id
group by tname;

2. 查询各科成绩最高和最低的分数,形式 : 课程ID  课程名称 最高分  最低分
select cid as 课程ID,cname as 课程名称,
max(number) as 最高分,min(number) as 最低分
from course left join score
on course.cid = score.course_id
group by cid,cname;

3. 查询平均成绩大于85分的所有学生学号,姓名和平均成绩
select student.sid,sname,avg(number)
from student left join score
on student.sid = score.student_id
group by student.sid,sname
having avg(number) > 85;

4. 查询课程编号为2且课程成绩在80以上的学生学号和姓名
select student.sid,sname,number
from student left join score
on student.sid = score.student_id
where course_id=2 and number>80;

5. 查询各个课程及相应的选修人数
select cname,count(course_id)
from course left join score
on course.cid = score.course_id
group by cname;

6. 查询每位学生的姓名，所在班级和平均成绩
select sname,caption,avg(number)
from student left join class
on student.class_id = class.cid
left join score
on student.sid = score.student_id
group by sname,caption;