MySQL学习(三)---使用join进行连接查询/内连接/左外连接/右外连接/自连接/排序和分页/子查询

MySQL学习(三)—使用join进行连接查询/内连接/左外连接/右外连接/自连接/排序和分页/子查询

01 连接查询

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

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

---

02 内连接

# 语法：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;

---

02 外连接

• 外连接分为左外连接和右外连接

# 左外连接 
# 语法：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

# 结果：以右表为基准，右表为参加考试的同学的信息。因此可以完成查询。

---

03 自连接

# 自连接：本质是把一张表当成两张表来使用。
# 自连接的查询效率较高

# 示例
# 创建一个表，该表中的id列属性并不一致，有的id为课题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。

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

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

---

04 排序和分页

• 排序

# 语法：ORDER BY
# ASC代表升序 DESC代表降序

# 示例：查询学员及所属的年级(学号,学生姓名,年级名) 按学号升序

select StudentNo,StudentName,GradeName
from student as s
inner join grade as g
on s.GradeId=g.GradeID
order by StudentNo ASC  ;

• 分页

# 语法SELECT * FROM table LIMIT 起始页码，但也显示数量

# 示例：查询学员及所属的年级(学号,学生姓名,年级名) 按学号升序，从第6条数据开始，显示十条查询结果。

select StudentNo,StudentName,GradeName
from student as s
inner join grade as g
on s.GradeId=g.GradeID
order by StudentNo ASC
limit 6,10;

---

05 子查询

子查询：在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句，嵌套查询可由多个子查询组成,求解的方式是由里及外

# 示例：查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)

select r.studentNo,s.StudentName,r.StudentResult
from result as r
inner join student as s
on r.StudentNo=s.StudentNo
where r.SubjectNo=(select su.SubjectNo
                   from subject as su
                   where su.SubjectName='C语言-1' )
order by r.StudentResult desc
limit 0,5;

---