mysql 内连接、外链接以及全连接的实现

建表

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;