MySQL联表查询

1、JOIN关键词

联表查询JOIN
这是student表

这是result表

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

/*思路：
1.分析需求，分析查询的字段来自哪些表(连接查询)
2.确定使用哪种连接查询？七种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件：学生表中studentNo = 成绩表中的studentNo
*/
-- INNER JOIN 就类似取两个表的交集部分
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM student AS s INNER JOIN result AS r ON s.studentNo = r.studentNo;

RIGHT JOIN

SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM student AS s right join result AS r ON s.studentNO = r.studentNo;

如果对连接查询这块不懂可以看看这篇帖子：
MySQL连接查询
在没有看这篇帖子之前，我一直以为左连接查询和左外连接查询是两个东西，这是一种错误，看了这篇帖子后，立即纠正了这种错误思维。
LEFT JOIN

SELECT s.studentNo,`StudentName`,`SubjectNo`,`StudentResult` FROM student AS s LEFT JOIN result AS r ON s.studentNo = r.studentNo;

操作	描述
INNER JOIN	如果表中至少有一个匹配，就返回行
LEFT JOIN	会从左标中返回所有的值，即使右表中没有匹配
RIGHT JOIN	会从右标中返回所有的值，即使左表中没有匹配

查询缺考的同学

SELECT s.studentNo,`StudentName`,`SubjectNo`,`StudentResult` FROM student AS s LEFT JOIN result AS r ON s.studentNo = r.studentNo WHERE StudentResult IS null;

JOIN (连接的表) ON （判断的条件）连接查询
where 指定结果需满足的条件

自连接

自己的表和自己的表连接，核心：一张表拆分为两张一样的表即可


父类表

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

子类表

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

操作：查询父类表对应子类表的关系

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

-- 查询父子信息：把一张表看为两个一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目' 
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`;

查询学生所属的年级(学号，学生姓名，年级名称)

SELECT `StudentNo`,`StudentName`,`GradeName`
FROM `student` AS s
INNER JOIN `garde` AS g
ON s.`GradeId` = G.`GradeId`;

查询科目所属的年级(科目名称，年级名称)

SELECT `SubjectName`,`GradeName`
FROM `subject` AS sub
INNER JOIN `grade` AS g
ON sub.`Gradeid` = g.`Gradeid`;

查询参加科目为数据库结构-1考试的学生信息(学号，学生姓名，科目名，分数)

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`studentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE sub.`SubjectName` = '数据库结构-1';

2、分页和排序

分页LIMIT和排序ORDER BY

-- 排序：升序ASC，降序DESC
-- 查询参加科目为数据库结构-1考试的学生信息(学号，学生姓名，科目名，分数)的成绩降序排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`studentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE sub.`SubjectName` = '数据库结构-1'
ORDER BY `StudentResult` DESC;

排序

-- 假如有100万条数据，怎么在浏览器进行展现？
-- 环节数据库压力，用户体验更好，瀑布流
-- 查询参加科目为数据库结构-1考试的学生信息(学号，学生姓名，科目名，分数)的成绩倒数后五名
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`studentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE sub.`SubjectName` = '数据库结构-1'
ORDER BY `StudentResult` ASC
LIMIT (0,5);
-- 语法：LIMIT((n-1)*pageSize，pageSize)
-- pageSize：页面大小，n：当前页
-- 数据总数/页面大小+1 = 总页数

语法:limit(当前页-1)*页面数据大小,页面数据大小

查询Java第一学年课程成绩排名前十的学生并且分数要大于80的学生信息(学号，姓名，课程名称，分数)

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentNo = r.studentNo
INNER JOIN `subject` AS sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE r.`SubjectName` = 'Java第一学年' AND StudentResult >= 80
ORDER BY SbujectResult DESC
LIMIT (0,10);

3、子查询

在where语句中嵌套一个子查询语句

-- 1.查询 数据库结构-1 科目的所有考试结果(学号，科目编号，成绩)，降序排序
-- 方式一：使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE SubjectName = '数据库结构-1'
ORDER BY StudentResult DESC;

-- 方式2：使用子查询
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE SubjectNo = (
	SELECT SubjectNo FROM `subject` 
	WHERE SubjectName='数据库结构-1'
)ORDER BY StudentResult DESC;

查询分数不小于80分的学生的学号和姓名

SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo
WHERE `StudentResult`>=80;

查询科目编号为高等数学-2的分数不小于80分的学生的学号和姓名

SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo
WHERE `StudentResult`>=80 AND `SubjectNo` = (
	SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-2'
);