很多刚毕业的大学生对于数据库的掌握有的是比较凌乱的,其实单表增删改查还是比较好的,可是对于多表的查询很迷茫,不知道到底是怎样的,接下来举个例子:
比如说有两张表关联,teacher表以及student表,一个学生可以对应多个老师,相反一个老师对应于多个学生,所以属于多对多查询,那么对于一个具体的老师到底教了多少学生呢,我们接下来讲一下:
建表语句:
CREATE TABLE teacher(
id int PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(30)
);
CREATE TABLE student(
id int PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(30)
);
CREATE TABLE teacher_student(
t_id int,
s_id int
);
INSERT INTO teacher VALUES(null,'zhangsan');
INSERT INTO teacher VALUES(null,'lisi');
INSERT INTO student VALUES(null,'red');
INSERT INTO student VALUES(null,'yellow');
INSERT INTO student VALUES(null,'blue');
INSERT INTO student VALUES(null,'green');
INSERT INTO student VALUES(null,'black');
INSERT INTO student VALUES(null,'pink');
INSERT INTO teacher_student VALUES(1,2);
INSERT INTO teacher_student VALUES(1,3);
INSERT INTO teacher_student VALUES(1,1);
INSERT INTO teacher_student VALUES(2,2);
INSERT INTO teacher_student VALUES(1,5);
INSERT INTO teacher_student VALUES(2,2);
老师表:
学生表:
关系表:
两种查询方式:
第一种:
SELECT
s.*
FROM
teacher_student AS ts,
student AS s
WHERE
ts.s_id = s.id
AND ts.t_id = 1;
截图查询出来相关的学生:
第二种(也是很多公司比较看重的一种可能是因为显得高大上吧,或者是性能好一点)
SELECT
s.*
FROM
teacher_student AS ts
LEFT JOIN student s ON ts.s_id = s.id
WHERE
ts.t_id = 1
截图查询出来相关的学生: