MySQL学习总结（3）--- 数据库查询之join连接查询（左/内/右连接）、自连接、等值连接、自查询

MySQL学习总结（3）— 数据库查询之join连接查询（左/内/右连接）、自连接、等值连接、自查询

1. join连接查询

如果需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询，称为连接查询。

连接查询的分类：

• 内连接 inner join：
• 外连接 outer join
  • 左外连接 left join
  • 右外连接 right join

连接查询的关系图：

1.1 内连接

语法：inner join

查询两个表中的结果集中的交集。

# 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)

SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

#查询参加了考试的同学信息(学号,学生姓名,科目名,分数) 要使用内连接嵌套，通过科目编号查询科目名

SELECT s.StudentNo,StudentName,SubjectName,StudentResult
from student as s
inner join result as r
on s.StudentNo=r.StudentNo
inner join subject su
on r.SubjectNo=su.SubjectNo;

1.2 外连接

• 左连接 left join

  以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充。

  # 查询所有同学的学号,学生姓名,科目名,分数
  # 由于student中是所有同学，result中是参加考试的同学，因此选用左外连接 这里的left是指以左表为基准，而不是将left join后面的表作为左表
  
  SELECT s.studentno,studentname,subjectno,StudentResult
  FROM student s
  LEFT JOIN result r
  ON r.studentno = s.studentno
  
  # 结果：不考试的也会查询出来，考试成绩和考试科目编号被null值填充
  

• 右连接 right join

  以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充。

  #查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
  
  SELECT s.studentno,studentname,subjectno,StudentResult
  FROM student s
  RIGHT JOIN result r
  ON r.studentno = s.studentno
  
  # 结果：以右表为基准，右表为参加考试的同学的信息。因此可以完成查询。
  

2. 自连接

自连接的本质：一张表当两张表使用。查询效率高。

需求： 从一个包含栏目ID , 栏目名称和父栏目ID的表中，查询父栏目名称和其他子栏目名称。

# 创建一个表
CREATE TABLE `category` (
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课题id',
  `typeId` INT(10) NOT NULL COMMENT '所属分类id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT '名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

# 插入数据
INSERT INTO `category` (`categoryid`, `typeId`, `categoryName`) 
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

分析：

该数据表的含义：categoryId中有分类ID也有课题ID，当分类ID对应的typeId为1时，对应的categoryName为分类名字，如果是课题ID，对应typeId!=1，此时，对应的categoryName为课题名字。因此在做自连接查询时，只要使用categoryId=typeId就可以找到课题ID对应的分类ID。

# 相当于联合下表进行查询：
categoryid	categoryName	
	2		   信息技术		  
	3		   软件开发	
	4		   数据库
    5		   美术设计			
	6		   Web开发		
	7		   PS技术			
	8	       办公信息	
 typeId     categoryName
	1		   信息技术       
	1		   软件开发
	3		   数据库
	1		   美术设计
	3		   Web开
	5		   PS技术
	2		   办公信息

进一步等于查询两个表。

# 自连接查询：查询课题对应的分类
SELECT a.categoryName AS '课题',b.categoryName AS '分类'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`typeId`

3. 等值连接

等值连接：适用于两个表或者是多个表有关系的情况，where子句中使用=等号为限定条件。

非等值连接：where子句中使用模糊匹配为限定条件。

# 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

4. 子查询

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句。

嵌套查询可由多个子查询组成，求解的方式是由里及外。

子查询返回的结果一般都是集合，故而建议使用IN关键字。

# 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
#方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r 
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

#方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject` 
    WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;