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;