一、了解3种连表

mysql 自关联查询慢_mysql 自关联查询慢

操作

描述

例子

左连表：left join

返回左表中所有的值，即使右表中没有匹配

比如 student left join scoreOfStudent 即使学生没有参见考试，也会返回学生数据，但是 考试成绩为 null

右连表:right join

返回全部右表的值，(类推)

左表null(类推）

中心表：inner join

返回全部左表和右表的值

不匹配的左表和右表为null

二、案例练习.

1. 建表

1.学生表

CREATE TABLE `student` (
  `id` INT(11) DEFAULT NULL COMMENT '学生学号',
  `name` VARCHAR(100) DEFAULT NULL COMMENT '学生姓名'
) ENGINE=INNODB DEFAULT CHARSET=utf8

插入数据如下

mysql 自关联查询慢_mysql 自关联查询慢_02


2.学生考试分数表

CREATE TABLE `studentexam` (
  `name` varchar(100) DEFAULT NULL COMMENT '学生姓名',
  `testSubject` varchar(20) DEFAULT NULL COMMENT '考试科目',
  `score` varchar(2) DEFAULT NULL COMMENT '分数等级'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据如下：

mysql 自关联查询慢_数据_03


3.分数对应是否合格表

CREATE TABLE `ispass` (
  `score` varchar(2) DEFAULT NULL COMMENT '分数',
  `isPass` enum('合格','不合格') NOT NULL DEFAULT '不合格' COMMENT '是否及格'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入表数据如下：

mysql 自关联查询慢_数据_04

2. (练习1）

1.查看 学生本次考试的 id name 考试科目(testSubject) 和 考试分数 (score)

SELECT `id`,`student` .`name`,`testSubject`,`score`
FROM `student` 
inner JOIN `studentExam`
ON `student` .NAME = `studentExam`.name;

mysql 自关联查询慢_sql_05

3.（练习2）

1.要求：如果 张三 没有参加考试考试，查看 参加了考试的学生id name 考试科目(testSubject) 和 考试分数 (score)
2. 分析，只需要把参加考试的表 studentExam 表作为基础表就可以了，保证基础表，数据不丢失.

3.代码和效果

SELECT `id`,`student` .`name`,`testSubject`,`score`
FROM `student` 
RIGHT JOIN `studentExam`
ON `student` .NAME = `studentExam`.name;

mysql 自关联查询慢_数据_06

4. （练习3）

1.要求：如果 张三 没有参加考试考试，查看 所有学生的考试成绩id name 考试科目(testSubject) 和 考试分数 (score)
2. 分析，只需要把参加学生表 student 表作为基础表就可以了，保证基础表，数据不丢失.
3.代码和效果

SELECT `id`,`student` .`name`,`testSubject`,`score`
FROM `student` 
left JOIN `studentExam`
ON `student` .NAME = `studentExam`.name;

多了一个张三null.

mysql 自关联查询慢_sql_07

5.（练习4–关联3表）

1.要求 id 、学生的名称(student.name) 、考试科目（testSubject）、考试成绩（studentExam.score）、是否合格（isPass）
2、分析：先关联 student表和studentExam表，再关联 isPass 表**（一步一步来.）**
3.实现

SELECT `id`,`student` .`name`,`testSubject`,`studentexam`.`score`,`isPass`
FROM `student` 
INNER JOIN `studentExam`
ON `student` .NAME = `studentExam`.name
INNER JOIN `ispass`
ON `ispass`.`score` = `studentexam`.`score`;

mysql 自关联查询慢_mysql_08

三、自关联.

1.(练习1）

1.要求：

假如你有一个这样的表. summer表

mysql 自关联查询慢_sql_09

你想知道 多少分才算合格（就是下面的表 isPass表)

mysql 自关联查询慢_sql_10


2.分析使用自关联

3.代码

SELECT DISTINCT a.`score`,b.`isPass`
FROM 
`summer` AS a,
`summer` AS b
WHERE a.`score` = b.`score`;
2. （自关联2）

父表和子表

拓展：where 和 on 可以互换，建议使用on更规范

where 万金油.

因为当两张表的数据量比较大，又需要连接查询时，应该使用FROM table1 JOIN table2 ON xxx的语法，避免使用FROM table1,table2 WHERE xxx的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销