多表查询-学生表，班级表，课程表，班级课程表

原创 于 2021-08-03 16:00:13 发布 · 4.1k 阅读 · 26 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql

本文介绍了一个包含学生表、班级表、课程表及班级课程表的多表查询案例，通过具体的SQL语句展示了如何进行内连接、左连接和右连接等关联查询，并提供了统计每个班级学生数量的方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

多表查询：
学生表、班级表、课程表、班级课程表
 
 
-- 多对多
-- 班级表
CREATE TABLE banji(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
SELECT * FROM banji;
-- 学生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    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);
-- Cannot add or update a child row: a foreign key constraint fails (`java1812`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
INSERT INTO student(`name`,age,gender,banji_id) 
VALUES('张三',20,'男',3);
SELECT * FROM student;
-- 课程表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    credit INT COMMENT '学分'
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);
SELECT * FROM course;
-- 班级课程表
CREATE TABLE banji_course(
    -- id int PRIMARY KEY AUTO_INCREMENT,
    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,3),(2,1),(2,2),(2,3);
SELECT * FROM banji_course;
-- 子查询：嵌套查询，一个查询语句是另一个查询语句的条件
-- 查询班级是java1812班所有学生信息
SELECT * FROM student WHERE banji_id=2;
SELECT id FROM banji WHERE `name`='java1812';
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='java1812');
-- 班级是java1807班或者java1812班所有学生信息
SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
SELECT * FROM student WHERE banji_id IN(1,2);
SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812'; -- 1,2
SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812');
-- "="：要求子查询只有一个结果。 "in"：子查询可以有多个结果
关联查询：inner join
 
多表查询：
学生表、班级表、课程表、班级课程表
 
 
-- 多对多
-- 班级表
CREATE TABLE banji(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
SELECT * FROM banji;
-- 学生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    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);
-- Cannot add or update a child row: a foreign key constraint fails (`java1812`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
INSERT INTO student(`name`,age,gender,banji_id) 
VALUES('张三',20,'男',3);
SELECT * FROM student;
-- 课程表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    credit INT COMMENT '学分'
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);
SELECT * FROM course;
-- 班级课程表
CREATE TABLE banji_course(
    -- id int PRIMARY KEY AUTO_INCREMENT,
    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,3),(2,1),(2,2),(2,3);
SELECT * FROM banji_course;
-- 子查询：嵌套查询，一个查询语句是另一个查询语句的条件
-- 查询班级是java1812班所有学生信息
SELECT * FROM student WHERE banji_id=2;
SELECT id FROM banji WHERE `name`='java1812';
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='java1812');
-- 班级是java1807班或者java1812班所有学生信息
SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
SELECT * FROM student WHERE banji_id IN(1,2);
SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812'; -- 1,2
SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812');
-- "="：要求子查询只有一个结果。 "in"：子查询可以有多个结果
关联查询：inner join
 
 
-- 列出所有学生学习的课程名称
-- 学生姓名   班级名称   课程名称    学分
 
 
 
-- inner join on 只有左右两个表有关联的才查询出来
-- left join on 左表中都显示出来，右表没有显示空
-- right join on 右表都显示，左表没有显示空
 
  SELECT * 
FROM student as s INNER JOIN banji as b
on s.banji_id=b.id;
SELECT * 
FROM student as s LEFT JOIN banji as b
on s.banji_id=b.id;
 
SELECT * 
FROM student as s RIGHT JOIN banji as b
on s.banji_id=b.id;
 
-- 没有分配课程也显示出来。
-- 班级名称   课程名称   学分
SELECT b.`name` AS '班级名称',c.`name` as '课程名称',c.credit as '学分'
FROM banji AS b LEFT JOIN banji_course AS bc
ON b.id=bc.banji_id
LEFT JOIN course as c
ON bc.course_id=c.id;
 
总结：多表查询主要是注意下面两点
1、整个查询涉及到几张表，涉及到几张表就连接这几张表。
2、如果涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键建立的关系）
-- 统计每个班有多少学生
-- 学生数量
SELECT COUNT(id) as '学生数量' 
FROM student GROUP BY banji_id;
-- 班级名称    数量
SELECT * 
FROM student as s 
INNER JOIN banji as b
ON s.banji_id=b.id;
 
把inner join之后查询的结果当成一张表来使用， 在这个结果集里面根据班级id统计每个班级下面学生数量。
 
-- 列出所有学生学习的课程名称
-- 学生姓名   班级名称   课程名称    学分
 
 
 
-- inner join on 只有左右两个表有关联的才查询出来
-- left join on 左表中都显示出来，右表没有显示空
-- right join on 右表都显示，左表没有显示空
 
  SELECT * 
FROM student as s INNER JOIN banji as b
on s.banji_id=b.id;
SELECT * 
FROM student as s LEFT JOIN banji as b
on s.banji_id=b.id;
 
SELECT * 
FROM student as s RIGHT JOIN banji as b
on s.banji_id=b.id;
 
-- 没有分配课程也显示出来。
-- 班级名称   课程名称   学分
SELECT b.`name` AS '班级名称',c.`name` as '课程名称',c.credit as '学分'
FROM banji AS b LEFT JOIN banji_course AS bc
ON b.id=bc.banji_id
LEFT JOIN course as c
ON bc.course_id=c.id;
 
总结：多表查询主要是注意下面两点
1、整个查询涉及到几张表，涉及到几张表就连接这几张表。
2、如果涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键建立的关系）
-- 统计每个班有多少学生
-- 学生数量
SELECT COUNT(id) as '学生数量' 
FROM student GROUP BY banji_id;
-- 班级名称    数量
SELECT * 
FROM student as s 
INNER JOIN banji as b
ON s.banji_id=b.id;
 
把inner join之后查询的结果当成一张表来使用， 在这个结果集里面根据班级id统计每个班级下面学生数量。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值