MySQL 的多表查询

表的数据准备

create table score
(
    id    int auto_increment
        primary key,
    grade int          null,
    code  varchar(100) null
)
    charset = utf8mb4;
INSERT INTO test.score (id, grade, code) VALUES (1, 11, '200001');
INSERT INTO test.score (id, grade, code) VALUES (2, 22, '200002');
INSERT INTO test.score (id, grade, code) VALUES (3, 33, '200003');
INSERT INTO test.score (id, grade, code) VALUES (4, 44, '200004');
​
create table student
(
    id    int auto_increment
        primary key,
    name  varchar(50) null,
    code  int         null,
    level int         null
)
    charset = utf8mb4;
INSERT INTO test.student (id, name, code, level) VALUES (1, '崩铁', 200001, 10);
INSERT INTO test.student (id, name, code, level) VALUES (2, '原神', 200002, 13);
INSERT INTO test.student (id, name, code, level) VALUES (3, '王者', 200003, 34);
INSERT INTO test.student (id, name, code, level) VALUES (4, '崩崩崩', 200004, 32);
INSERT INTO test.student (id, name, code, level) VALUES (5, 'ZZZ', 200005, 53);
​

内连接 JOIN / INNER JOIN

SELECT * FROM score JOIN student ;#不加限制条件会出现笛卡尔积
SELECT * FROM score JOIN student WHERE student.id=score.id;
#下面两个sql等价
SELECT * FROM score JOIN student ON student.id=score.id;
SELECT * FROM score INNER JOIN student ON score.code=student.code;

ON 和 WHERE 的区别

ON：用于定义连接条件，控制表之间行的匹配方式，从而达到不符合条件的行就不连接

WHERE：先将表进行连接,再过滤查询结果，控制最终显示哪些行

自然连接 NATURAL JOIN

NATURAL会自动比较两张表中名称相同的列,返回列名一样的前提下数据内容一样的一整行 自动省略 ON

SELECT * FROM student NATURAL JOIN score ;

左连接 LEFT JOIN / LEFT OUTER JOIN

查询左表中的所有行和sql中连接右表用上的行,需要注意的是左连接即便在右表中没有查出数据这一行也会展现并且赋值为null

SELECT * FROM student LEFT JOIN score ON score.code=student.code;

右连接 RIGHT JOIN / RIGHT OUTER JOIN

和左连接一样,只是换成了展示右表中的所有行加上sql中用于连接的左表数据,同样查询不到会赋值为null

SELECT * FROM score RIGHT JOIN student ON score.code=student.code;

全连接 UNION

会返回两张表中的所有数据(即两张表中的列进行和并,不一样的表的相同名字的列会被加以区分),另外一张表中没有的数据用null取代

SELECT * FROM score LEFT JOIN student ON score.code=student.code UNION SELECT * FROM score RIGHT JOIN student ON score.code=student.code;

交叉连接 CROSS JOIN

等价于没有加ON的JOIN 当A表中有m个数据 B表中有n个数据的时候 利用交叉查询出来的结果就是m*n个 即返回笛卡尔积（所有可能的组合）在我这两张表中即是20条数据

SELECT * FROM student CROSS JOIN score;

自连接

自连接即操作一张表,用于比较一张表中的不同数据

#查询比王者同学的level要低的同学的所有信息
SELECT s2.* FROM student s1,student s2 WHERE s1.name='王者' AND   s1.level>s2.level;