执行一下代码

DROP TABLE IF EXISTS student;DROP TABLE IF EXISTS teacher;-- 创建老师表create TABLE teacher(	id int primary key auto_increment,	name char(20));-- 创建学生表create TABLE student(	id int primary key auto_increment,	name char(20),	teacher_id INT,	FOREIGN KEY keyStudent (teacher_id) REFERENCES teacher(id));-- 插入老师INSERT INTO teacher VALUES (null,"张老师");INSERT INTO teacher VALUES (null,"王老师");INSERT INTO teacher VALUES (null,"赵老师");INSERT INTO teacher VALUES (null,"李老师");INSERT INTO teacher VALUES (null,"方老师");-- 插入学生 关联老师 张老师INSERT INTO student VALUES (null,"杨晓为",1);INSERT INTO student VALUES (null,"郭金红",1);INSERT INTO student VALUES (null,"李小庄",1);-- 插入学生 关联老师 王老师INSERT INTO student VALUES (null,"江大红",2);INSERT INTO student VALUES (null,"胡作为",2);-- 插入学生 关联老师 赵老师INSERT INTO student VALUES (null,"吴婷平",3);INSERT INTO student(name) VALUES("牛阿郎");INSERT INTO student(name) VALUES("虽无恐");

 1. 测试left JOIN

SELECT stu.id AS "学生序号" ,stu.name AS "学生姓名",tea.id AS "老师序号",tea.name AS "老师姓名" FROM  

student stu left JOIN teacher tea ON (stu.teacher_id=tea.id)

 得到结果集:

可以看出 left join 查出的数据 按照  from 后的表 为 主表 会把主表条件满处的数据全部查出

主表有的数据才能查出,难怕查出主表连接的那张表没数据,主表依然会查出数据

2.测试right JOIN 

SELECT stu.id AS "学生序号" ,stu.name AS "学生姓名",tea.id AS "老师序号",tea.name AS "老师姓名" FROM 

student stu right JOIN teacher tea ON (stu.teacher_id=tea.id)

得到 测试结果集

right join 与 left join 刚刚反 ,他把join后面的表当作主表

3.测试inner JOIN 

SELECT stu.id AS "学生序号" ,stu.name AS "学生姓名",tea.id AS "老师序号",tea.name AS "老师姓名" FROM  

student stu inner JOIN teacher tea ON (stu.teacher_id=tea.id)

得到 inner join 结果集

这个好,只要一方条件 没达到 ,那么 也查不出数据

 个人总结

  join 连表 

   如 left join 做例子 我们可以把 from 后的表当作 主表 数据库先查主表 得到数据集 在与 被连接的表进行匹配(on 后面的条件)，如果 匹配成功 那么就 得到 被连接表的数据,如果 匹配不成功 那么 主表已经查出 被连接的表 没有数据 那么直接 给被连接的表的值就为空