数据准备
我们需要两个表
student
和student_score
CREATE TABLE `student` (`student_id` int NOT NULL,`name` varchar(45) NOT NULL,PRIMARY KEY (`student_id`) );CREATE TABLE `student_score` (`student_id` int NOT NULL,`subject` varchar(45) NOT NULL,`score` int NOT NULL );
然后插入数据
INSERT INTO `student` (`student_id`, `name`) VALUES (1,'Tim'),(2,'Jim'),(3,'Lucy');INSERT INTO `student_score` (`student_id`, `subject`, `score`) VALUES (1,'English',90),(1,'Math',80),(2,'English',85),(2,'Math',88),(5,'English',92);
表结构
交叉连接(CROSS JOIN)
交叉连接返回两个集合的笛卡尔积。也就是两个表中的所有的行的所有可能的组合。这相当于内连接没有连接条件或者连接条件永远为真。
如果一个有
m
行的表和另一个有n
行的表,它们交叉连接将返回m * n
行数据。假如有a b c三个数据 交叉连接 e f g三个数据 就是a分别连e f g,b分别连e f g,c分别连e f g;
示例:
SELECT student.*,student_score.* FROM student CROSS JOIN student_score;
平时SELECT后面可以加列,但是现在我们多表联查,总要区分是哪个表的列不是,所以是表名.列名表示列
同时简写的话就是
SELECT student.*,student_score.* FROM student CROSS JOIN student_score;
运行结果:
内连接(INNER JOIN)
内连接将第一个表的每一行与第二个表的每一行进行比较,如果满足给定的连接条件,则将两个表的行组合在一起作为结果集中的一行。
SELECT student.*,student_score.* FROM student INNER JOIN student_score ON student.student_id = student_score.student_id;
内连接基于连接条件组合两个表中的数据。内连接相当于加了过滤条件的交叉连接。
所以这行代码也等价于
SELECT student.*,student_score.* FROM student,student_score WHERE student.student_id = student_score.student_id;
如果我们只需要相同列名进行等值匹配也可以用USING
SELECTstudent.*,student_score.* FROMstudentINNER JOIN student_score USING(student_id);
外连接分为左外连接和右外连接
左连接
左连接是左外连接的简称,左连接需要连接条件。
两个表左连接时,第一个表称为左表,第二表称为右表。例如
A LEFT JOIN B
,A
是左表,B
是右表。左连接以左表的数据行为基础,根据连接匹配右表的每一行,如果匹配成功则将左表和右表的行组合成新的数据行返回;如果匹配不成功则将左表的行和 NULL 值组合成新的数据行返回。
SELECTstudent.*,student_score.* FROMstudentLEFT JOIN student_scoreON student.student_id = student_score.student_id;
首先啊 这些行不是一一对应的关系,以student表为主体,那么每条student表的行可以匹配多行student_score的数据,用自然思维考虑嘛! score表中有多个student_id和student中的student_id相等,你说咱能要哪条不要哪条吗?那肯定全要啊
我们可以看到 student_score中的student_id有一个5 这个5在student表中没有对应的 所以是NULL填充的
这个ON仍可以用USING替换(在查询列名相同的时候),所以说这个替换针对的是ON而非具体某种连接
右连接
逻辑和左连接相同,只是这次以右边的参数为主体而已
甚至说 A LEFT JOIN B 等价于 B RIGHT JOIN A