【狂神】MySQL - 连表查询 Join On 详解_mysql join on

1. 连表查询详解

 市面上有 7 种连表查询, 总共归为三大类 : 

• 左查询 LEFT JOIN    -- 以左表为基准
• 右查询 RIGHT JOIN   --  以右表为基准
• 交叉查询 INNER JOIN  -- 查询两表都有的数据

【测试数据】

1. 创建并使用 school 数据库.

CREATE DATABASE if NOT EXISTS `school`;
 
USE DATABASE;

2. 创建 grade 表 (年级表 ) 并构造数据.

DROP TABLE IF EXISTS `grade`;
 
CREATE TABLE `grade`(
    `GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号', 
    `GradeName` VARCHAR(50) NOT NULL COMMENT '年纪名称',
    PRIMARY KEY (`GradeID`)
) ENGINE = INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
 
INSERT INTO grade(GradeID,GradeName) VALUES(1,'大一');
INSERT INTO grade(GradeID,GradeName) VALUES(2,'大二');
INSERT INTO grade(GradeID,GradeName) VALUES(3,'大三');
INSERT INTO grade(GradeID,GradeName) VALUES(4,'大四');
INSERT INTO grade(GradeID,GradeName) VALUES(5,'预科班');

3.  创建 result 表 (成绩表) 并构造数据.

DROP TABLE IF EXISTS `result`;
 
CREATE TABLE `result`(
    `StudentNo` INT(4) NOT NULL COMMENT '学号',
    `SubjectNo` INT(4) NOT NULL COMMENT '课程编号',
    `ExamDate` DATETIME NOT NULL COMMENT '考试时间',
    `StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
    KEY `SubjectNo` (`subjectNo`)
)ENGINE=INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2023-06-25 09:00:00',85),
(1000,2,'2013-06-26 09:00:00',70),
(1000,3,'2013-06-27 09:00:00',68),
(1000,4,'2013-06-28 16:00:00',98),
(1000,5,'2013-06-29 16:00:00',68),
(1001,6,'2013-06-30 16:00:00',78),
(1002,7,'2013-07-01 16:00:00',88);

4. 创建 student 表 (学生表) 并构造数据.

DROP TABLE IF EXISTS `student`;
 
CREATE TABLE student(
    `StudentNo` INT(4) NOT NULL COMMENT '学号' ,
    `Loginpwd` VARCHAR(20) DEFAULT NULL COMMENT '密码',
    `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `Sex` TINYINT DEFAULT NULL COMMENT '性别，0/1',
    `Gradeid` INT(11) DEFAULT NULL COMMENT '年纪编号',
    `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，不允许为空', 
    `Address` VARCHAR(255) NOT NULL COMMENT '地址，不允许为空', 
    `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间', 
    `Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,不允许为空',
    `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号', 
    PRIMARY KEY (`StudentNO`),  -- 主键
    UNIQUE KEY `IdentityCard` (`IdentityCard`), -- 唯一索引
    KEY `Email` (`Email`)        -- index索引
) ENGINE=INNODB DEFAULT CHARSET=utf8;
 
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456434334011234'),
(1001,'123456','郭德',0,2,'13832343234','北京朝阳','1980-1-1','text123@qq.com','123456194001011234'),
(1002,'123456','李振',0,2,'13854325234','北京朝阳','1980-1-1','text123@qq.com','123456123001011234'),
(1003,'123456','刘强',0,2,'12130021234','北京朝阳','1980-1-1','text123@qq.com','123456193141011234'),
(1004,'123456','马云',0,2,'13832131234','北京朝阳','1980-1-1','text123@qq.com','123454332001011234'),
(1005,'123456','赵青',0,2,'13545441234','北京朝阳','1980-1-1','text123@qq.com','123456142101011234'),
(1006,'123456','赵强',1,3,'13805445222','广东深圳','1990-1-1','text111@qq.com','123132131301011233');

5. 创建 subject 表 (学科表)  并构造数据.

CREATE TABLE `subject`(
    `SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
    `GradeID` INT(4) DEFAULT NULL COMMENT '年纪编号',
    PRIMARY KEY(`SubjectNo`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;
 
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

2. 连表查询示例

1. 查询参加了考试的同学 (查询列 : 学号, 姓名, 科目编号, 分数) -- 两表

【思路】

1. 分析查询的字段来自哪些表.

2. 确定使用哪种连接查询. 

3. 确定交叉点 (这两个表中哪些数据是相同的).

4. 写判断条件 : 学生表中的 StudentNo = 成绩表中的 StudentNo.

5. 根据需求增加 where 等值查询. (可有可无)

• 上述字段分别来自 student 表 (学号,姓名) 和 result 表 (科目编号,分数)
• 因为需要查询参加考试的同学, 而学生表中有些学生是没有参加考试的, 所以以成绩表为基准, 所以使用右连接查询.

【SQL 实现】

-- RIGHT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM `student` s
RIGHT JOIN `result` r
ON s.StudentNo = r.StudentNo;

 

 如果使用左查询, 将会把没有参加考试的同学的行也查询出来, 就不符合题目要求 (查询参加考试的同学).

2.  查询缺考的同学 (学号, 姓名, 科目编号, 分数) -- 两表

【思路】

1. 分析查询的字段来自哪些表.

2. 确定使用哪种连接查询. 

3. 确定交叉点 (这两个表中哪些数据是相同的).

4. 写判断条件 : 学生表中的 StudentNo = 成绩表中的 StudentNo.

5. 根据需求增加 where 等值查询 (可有可无).

• 上述字段分别来自 student 表 (学号,姓名) 和 result 表 (科目编号, 分数)
• 因为题目需要查询未参加考试的同学, 而成绩表中只有参加了考试的同学, 所以以左表为基准, 所以使用左连接查询.

【SQL 实现】

-- LEFT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM `student` s
LEFT JOIN `result` r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL;

3. 查询参加考试的同学信息（学号, 学生姓名, 科目名称, 分数）-- 三表

【思路】

1. 分析查询的字段来自哪些表.

2. 确定使用哪种连接查询. 

3. 确定交叉点 (这两个表中哪些数据是相同的).

4. 写判断条件 : 学生表中的 StudentNo = 成绩表中的 StudentNo.  成绩表中的 SubjectNo = 学科表中的 SubjectNo.

5. 根据需求增加 where 等值查询 (可有可无).

• 上述字段分别来自 student 表 (学号,姓名) ,result 表 (分数) 和 subject 表 (科目名称)
• 先查 student , rersult 两张表, 再连 subject 第三张表.
• 对于 student 和 result 表, 要查询参加考试的同学, 使用右连接, 而对于这两张表查询出来的结果和 subject 进行连表查询时, 没有以哪张表为基准, 所以使用 inner join 就可以了.

【SQL 实现】

1. 先查 student 表 和 result 表

SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo;

2. 再拿着查询结果与 subject 表进行连表查询

完整 SQL :

SELECT s.StudentNo '学号',StudentName '姓名',
	SubjectName '科目名称',StudentResult '考试成绩'
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON sub.SubjectNo = r.SubjectNo;

【总结】

整体查询思路 ： 

1. 我要查询哪些数据  -> SELECT ...

2. 需要从哪几张表中查数据  -> FROM 表  XXX Join 连接的表 on  交叉条件.

3. 假设存在多张表 (> 2) 的查询,  先连接两张表进行查询, 再慢慢增加其他表.