社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
CREATE TABLE`学生`(
`id` int(11)DEFAULT NULL,
`student_name` VARCHAR(10)DEFAULT NULL,
`teacher_id` VARCHAR(10)DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
创建表`老师`(
`id` int(11)DEFAULT NULL,
`teacher_name` VARCHAR(10)DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
插入学生
值(1, “张三”,1),(2, “李四”,1),(3, “王五”,2),(4, “刘刚”,3),(5, “赵云” ,4);
插入老师
值(1, “李老师”),(2 “刘老师”),(3, “贾老师”);
SELECT * FROM student;
SELECT * FROM老师;
SELECT s.student_name,t.teacher_name FROM student s,teacher t WHERE s.teacher_id = t.id;
SELECT s.student_name,t.teacher_name FROM student s JOIN teacher t ON s.teacher_id = t.id;
SELECT s.student_name,t.teacher_name FROM student INNER JOIN teacher t ON s.teacher_id = t.id;
左连接以左表为基础
SELECT s.student_name,t.teacher_name FROM student s LEFT JOIN teacher t ON s.teacher_id = t.id;
SELECT s.student_name,t.teacher_name FROM student s LEFT OUTER JOIN teacher t ON s.teacher_id = t.id;
右连接以右表为基础
SELECT s.student_name,t.teacher_name FROM student s RIGHT JOIN teacher t ON s.teacher_id = t.id;
SELECT s.student_name,t.teacher_name FROM student s RIGHT OUTER JOIN teacher t ON s.teacher_id = t.id;
SELECT s.student_name,t.teacher_name FROM student s LEFT JOIN teacher t ON s.teacher_id = t.id
联盟
SELECT s.student_name,t.teacher_name FROM student s RIGHT JOIN teacher t ON s.teacher_id = t.id;
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!