26.MySQL中的内连接INNER JOIN
1.简介
内连接是一种常见的连接查询,它根据匹配条件返回第一个表与第二个表所有匹配成功的记录。
解释:将两个以上的表以一定的连接条件连接起来,从中检索出满足条件的数据。
语法:
SELECT 查询字段 FROM 表1[INNER] JOIN 表2 ON 匹配条件;
ON用于指定内连接的查询条件。
在不设置ON时,与交叉连接等价。
关键字ON与WHERE的异同点:
相同点:都用于完成条件的限定。
不同点:WHERE是限定已全部查询出来的记录,数据量大的情况下,会浪费很多性能,所以此处推荐使用ON实现内连接的条件匹配。
2.准备
CREATE TABLE school(
id INT(4) PRIMARY KEY,
name VARCHAR(36)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO scholl VALUES (1,'A学校');
INSERT INTO scholl VALUES (2,'B学校');
CREATE TABLE grade(
id INT(4) PRIMARY KEY,
name VARCHAR(36),
sid INT(4)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO grade VALUES (1,'一班',1);
INSERT INTO grade VALUES (2,'二班',1);
INSERT INTO grade VALUES (3,'一班',2);
INSERT INTO grade VALUES (4,'二班',2);
CREATE TABLE student(
id INT(4) PRIMARY KEY,
name VARCHAR(36),
gid INT(4)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO student VALUES (1,'a1',1);
INSERT INTO student VALUES (2,'a2',2);
INSERT INTO student VALUES (3,'a3',3);
INSERT INTO student VALUES (4,'a4',4);
INSERT INTO student VALUES (5,'a5',1);
INSERT INTO student VALUES (6,'a6',2);
INSERT INTO student VALUES (7,'a7',3);
INSERT INTO student VALUES (8,'a8',4);
school:
grade:
student:
3.SELECT … FROM A, B的用法
查询结果:行数是A和B的乘积,列数是A和B之和,形成的就是笛卡尔乘积。
A,B位置前后顺序交换输出的查询结果本质上是一样的,只是看起来A,B表的前后顺序不一样而已。
SELECT * FROM school,grade;
SELECT * FROM school,grade,student;
SELECT COUNT(*) FROM school,grade,student;-- 64
4.SELECT…FROM A,B WHERE…的用法(SQL92标准)
SELECT * FROM school,grade WHERE school.id=grade.sid;
查询每个学校的班级下的学生:
SELECT * FROM school,grade,student
WHERE school.id=grade.sid AND grade.id = student.gid;
-- 等价于
SELECT * FROM school s,grade g,student t
WHERE s.id=g.sid AND g.id = t.gid;
5.SELECT… FROM A JOIN B ON …(sql99标准)
查询每个学校的班级:
SELECT *
FROM school
JOIN grade ON school.id=grade.sid;
-- 等价于
SELECT *
FROM school s
JOIN grade g ON s.id=g.sid;
查询每个学校的班级下的学生:
SELECT * FROM school JOIN grade,student
ON school.id=grade.sid AND grade.id = student.gid;
-- 等价于
SELECT * FROM school s JOIN grade g,student t
ON s.id=g.sid AND g.id = t.gid;