MySQL多表查询

• 数据库中数据表有一对一关系和多对多关系（一对多、多对多统称为多对多）。

一、一对多

• 处理MySQL中数据表的一对多关系，通常使用外键（foreign key）。

班级和学生是一对多的关系，因此可以将班级表的主键设置为学生表的外键。先创建班级表，学生表使用班级表的主键为外键。

1、创建班级表

-- 班级表
CREATE TABLE banji(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` CHAR(10) NOT NULL
);

INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
SELECT * FROM banji;

2、创建学生表

-- 学生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` CHAR(10),
    age INT,
    gender CHAR(1),
    banji_id INT,
    FOREIGN KEY(banji_id) REFERENCES banji(id)
);

INSERT INTO student (`name`,age,gender,banji_id) 
VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);
SELECT * FROM student;

3、创建的学生表和班级表之间的关系

二、多对多

• 处理MySQL中数据表之间的多对多关系，通常另外建立一张表，该表以两张数据表的主键作为外键（foreign key），通过这两个主键建立联合主键（primary key）。

班级和课程之间是多对多的关系，因此可以另外创建一张表，该表中只有班级表和课程表的主键两个信息，并且新表是以两张表的主键作为外键，将两个外键作为联合主键。

1、创建课程表

-- 课程表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` CHAR(10) NOT NULL,
    credit INT COMMENT '学分'
);

INSERT INTO course(`name`,credit) VALUES('Java',5),('H5',4),('UI',4);
SELECT * FROM course;

2、创建班级-课程表

-- 主键的作用：唯一确定表里的一条记录（unique key + not null）
-- 班级课程表
CREATE TABLE banji_course(
    banji_id INT,
    course_id INT,
    PRIMARY KEY(banji_id,course_id), -- 联合主键
    FOREIGN KEY(banji_id) REFERENCES banji(id), -- banji_id既是联合主键又是外键
    FOREIGN KEY(course_id) REFERENCES course(id) -- course_id既是联合主键又是外键
);

INSERT INTO banji_course(banji_id,course_id) VALUES (1,1),(1,2),(2,1),(2,2),(2,3);
SELECT * FROM banji_course;

3、创建的四张表之间的关系

三、子查询（嵌套查询）

1、“=”

• 要求子查询只有一个结果。

-- SELECT * FROM student WHERE banji_id = 2;
SELECT * FROM student WHERE banji_id = (SELECT id FROM banji WHERE `name` = 'java1812');

2、“in”

• 子查询可以由多个结果。

SELECT * FROM student WHERE banji_id IN 
(SELECT id FROM banji WHERE `name` = 'java1807' OR `name` = 'java1812');
-- "=":要求子查询只有一个结果  "in":子查询可以有多个结果

3、inner join

• 关联查询，把两张表连接在一起，通过ON限制条件筛选。

-- inner join  关联查询 把两张表连接在一起，ON限制条件筛选
SELECT * FROM student AS s INNER JOIN banji AS b;
-- 去除多余的信息
SELECT * FROM student AS s INNER JOIN banji AS b ON s.banji_id = b.id;

SELECT s.id AS '学生id',s.name AS '姓名',s.age AS '年龄',s.gender AS '性别',b.name AS '班级名称'
-- 指定输出信息
FROM student AS s INNER JOIN banji AS b ON s.banji_id = b.id;

-- 列出所有学生学习的课程名称
-- 学生姓名 班级名称 课程名称 学分
SELECT s.name AS '学生姓名',b.name AS '班级名称',c.name AS '课程名称',c.credit AS '学分'
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
INNER JOIN banji_course AS bc
ON bc.banji_id = s.banji_id
INNER JOIN course AS c
ON c.id = bc.course_id;

4、left join

• 左表中的都显示出来，右表没有显示空

-- left join
INSERT INTO student(id,NAME,age,gender) VALUES(4,'赵六',21,'男');
SELECT * FROM student;

SELECT *
FROM student AS s LEFT JOIN banji AS b ON s.banji_id=b.id;

5、right join

• 右表中的都显示出来，左表没有显示空

-- right join
INSERT INTO banji(`name`) VALUES('java1810');
SELECT * FROM banji;

SELECT *
FROM student AS s RIGHT JOIN banji AS b ON s.banji_id=b.id;

-- 班级名称 学生数量
SELECT b.name AS '班级名称',COUNT(*) AS '学生数量'
FROM student AS s
INNER JOIN banji AS b
ON s.banji_id=b.id
GROUP BY b.id
ORDER BY COUNT(*);

• inner join、left join、right join的区别