建表
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;
mysql不支持全连接全连接,使用union连接左连接和右连接,得到全连接
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;