mysql 联表查询

mysql 联表查询

JOIN 对比

hUenNdKt1JTl9fg.png CMpvwOWTiPaHK9D.png

思路:

1.分析需求，分析查询的字段来自哪些表（连接查询）

2.确定使用哪种连接查询：7种

确定交叉点（这两个表中那个数据是相同的）

判断的条件

SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT <limit_number>

join 连接方式

内连接

1.png

SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key

左连接

2.png

 SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

右连接

3.png

 SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

只有A

4.png

  SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL

只有B

5.png

 SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

全连接

6.png

 # MySQL没有FULL OUTER语法。
 SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
 union
 SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

A,B 各自独有

7.png

# MySQL没有FULL OUTER语法。
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

光学不练假把式，先看个例子，新建几张表

class 表：班级

cid	caption
1	三年二班
2	三年三班
3	一年二班
4	二年九班

cource 表：课程

cid	techer_id	cname
1	1	生物
2	2	物理
3	3	语文
4	4	数学

score 表：成绩

sid	student_id	cource_id	num
1	1	1	20
2	1	2	30
3	1	3	80
4	1	4	60
5	2	1	55
6	2	2	70
7	2	3	65
8	2	4	80
9	3	1	60
10	3	2	63
11	3	3	68
12	3	4	80
13	4	1	89
14	4	2	95
15	4	3	86
16	4	4	99
17	5	1	78
18	5	2	67
19	5	3	66
20	5	4	80
21	6	1	50
22	6	2	77
23	6	3	82
24	6	4	96
25	7	1	80
26	7	2	90
27	7	3	88
28	7	4	76
29	8	1	65
30	8	2	90
31	8	3	85
32	8	4	68

student 表：学生

sid	gender	class_id	sname
1	男	1	李四
2	女	1	张三
3	男	1	王五
4	男	2	张二
5	女	2	张翼
6	男	2	王大锤
7	女	3	莉莉
8	女	3	韩梅梅
9	男	3	李达

teacher 表：老师

tid	tname
1	张磊
2	刘海燕
3	朱海
4	李杰

• 查询所有的课程的名称以及对应的任课老师姓名

select cname,tname from teacher inner join course on course.teacher_id = teacher.tid

• 查询所有学生的成绩（学生姓名、科目、成绩）

select sname,cname,num from student as s right join score as sc on sc.student_id = s.sid
inner join course as c on c.cid = sc.course_id 

• 查询所有学生的成绩（学生姓名、科目、成绩）以及对应的老师

select sname,cname,tname,num from student as s right join score as sc on sc.student_id = s.sid
inner join course as c on c.cid = sc.course_id
inner join teacher as t on t.tid = c.teacher_id

• 查询所有学生的成绩（学生姓名、科目、成绩）以及对应的老师和班级

select sname,cname,tname,num from student as s 
right join score as sc on sc.student_id = s.sid
inner join course as c on c.cid = sc.course_id
inner join teacher as t on t.tid = c.teacher_id

• 查询没有考试的学生

select sname from student left join score on score.student_id = student.sid where num is null

• 查询平均成绩大于八十分的同学的姓名和平均成绩

首先查询出平均成绩大于80分的学生（学生id和平均成绩）

select student_id,avg(num) as avg_score from score group by student_id having avg(num) >80

再根据studen_id,查询出学生的姓名

select sname,k.avg_score from student 
inner join (select student_id,avg(num) as avg_score from score group by student_id having avg(num) >80) as k on k.student_id = student.sid

• 查询挂科超过两门（包括两门）的学生姓名和班级

首先查询出两门成绩小于60分的学生（学生id）

select student_id from score where num < 60 group by student_id having count(student_id) >=2

再查询出学生的班级id

select sname,class_id from student where sid in
(select student_id from score where num < 60 group by student_id having count(student_id) >=2)

最后查询出学生的班级

select caption,k.sname from class inner join (select sname,class_id from student where sid in
(select student_id from score where num < 60 group by student_id having count(student_id) >=2)) as k on k.class_id = class.cid