MySql之联表查询

 

联表查询

---

JOIN概述（重点）

mysql中join的7中类型

 

本篇只讲解join联接的三种基本类型，左连接（Left Join），右联接（Right Join），等值联接（Inner Join）

知识点总结

1. 判断左右联接

• 左联接–from a left join b ----以表a为基准
• 右联接–from a right join b ----以表b为基准

2. 解题思路

1. 分析查询的字段来自那些表，
2. 确定使用那种连接查询
3. 判断交叉点(这两个表中哪个数据是相同的）。

假设存在多表查询，先进行两张表查询，再慢慢增加

3. 关于where和on的区别

• 参照链接：on和where条件的放置详解

4. 关于三种联接

操作	描述
inner join	两表交集（如果表中至少有一个匹配,即返回该行）
left join	会从左表中返回所有的值,即使右表中没有匹配
right join	会从右表中返回所有的值,即使左表中没有匹配

例题

现有三张表

表名	属性
student（学生表）	studentNo,studentName,email
result（考试成绩表）	studentNo,subjectNo,studentResult
subject（科目表）	subjectNo,subjectName,

• 问题一：查询参加了考试的同学(学号，姓名，科目编号，分数)

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
或：
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`

 

若采用左连接，则会返回student表中所有学生的信息，即使在result表中不存在该学生的信息。

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`

 

• 问题二：查询缺考的学生

--  AND r.`studentresult` IS NULL
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE studentresult IS NULL

 

• 思考题： 查询参加考试的同学信息：学号，学生姓名，科目名，分数

-- 先查询两张表
SELECT s.`studentno`,`studentname`,`studentresult`,`subjectno`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`

-- 增加第三张
SELECT s.`studentno`,`studentname`,`studentresult`,`subjectname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` AS sub
ON sub.`subjectno` = r.`subjectno`

 

自连接

---

一张表拆为两张表

案例

已知表：
categoryId表示科目id，pid表示科目等级

categoryId	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

---->可拆分为

父类（科目等级=1的科目）：

categoryId	categoryName
2	信息技术
3	软件开发
5	美术设计

子类：

pid	categoryId	categoryName
3	4	数据库
3	6	web开发
5	7	ps技术
2	8	办公信息

求连接表：

父类	子类
软件开发	数据库
软件开发	web开发
美术设计	ps技术
信息技术	办公信息

实现语句：

SELECT f.`categoryName` AS '父类科目' ,s.`categoryName` AS '子类科目'
FROM `category` AS f,`category` AS s
WHERE f.categoryId = s.pid