分别清空学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course 数据:
TRUNCATE TABLE student;
TRUNCATE TABLE teacher;
TRUNCATE TABLE course;
TRUNCATE TABLE student_course;
INSERT INTO student (name,age,id_number) VALUES ('赵小明',21,'420117199303036666'), ('王小红',22,'420117199303037777'), ('张小虎',18,'420117199303038888'), ('李小平',19,'420117199303039999'), ('刘美丽',21,'420117199303035555'), ('周杰',22,'420117199303034444'), ('秦小贤',19,'420117199303033333'), ('马笑',23,'420117199303032222'), ('艾伦',22,'420117199303031111'), ('包小天',20,'420117199303030000');
INSERT INTO teacher (name,age,id_number,email) VALUES ('Tom',29,'420117202006040000','tom@qq.com'), ('Jack',30,'420117202006041111','jack@qq.com'), ('Mary',31,'420117202006042222','mary@qq.com'), ('Timo',35,'420117202006043333','timo@qq.com'), ('Faker',38,'420117202006044444','faker@qq.com'), ('Bob',35,'420117202006045555','bob@qq.com'), ('kelly',40,'420117202006046666','kelly@qq.com'), ('Rose',42,'420117202006047777','rose@qq.com'), ('Hale',55,'420117202006048888','hale@qq.com'), ('John',49,'420117202006049999','john@qq.com'), ('Amy',55,'42011720200604888X','amy@qq.com'), ('Judy',49,'42011720200604999X','judy@qq.com');
因为表 course 有 teacher_id 字段,所以插入数据之前,需要获取 teacher 表数据:
SELECT * FROM teacher;
对应 course 表 teacher_id,按照上面 id 可插入相关课程信息:
INSERT INTO course (course_name,teacher_id) VALUES ('高等数学',1), ('英语',2), ('政治',3), ('信息论',4), ('数据结构和算法',5), ('体育',6), ('模拟电路',7), ('数字电路',8), ('通信原理',9), ('信号系统',10), ('概率论',11), ('光学原理',12);
INSERT INTO student_course (student_id,course_id) VALUES (1,1), (1,2), (2,3), (2,4), (3,5), (3,6), (4,7), (4,8), (5,9), (5,10), (6,1), (6,2), (7,3), (7,4);
以课程表 course 和 teacher 左连接为例:
SELECT c.id AS course_id,c.*,t.* FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id;
c.id AS course_id 表示将 course表 中 id 字段重命名为 course_id 展示,其目的是为了防止和 teacher表 中 id 字段混淆;
c.* 表示 course 表所有字段数据;
t.* 表示 teacher 表字段所有数据;
ON 后面跟着是连接表的条件;
course c 表示将 course 简写为 c, teacher t 表示将 teacher 简写为 t;
LEFT JOIN 为左连接,是以左边的表为’基准’,若右表没有对应的值,用 NULL 来填补。
同样以表 course 和 teacher 内连接为例:
SELECT c.id AS course_id,c.*,t.* FROM course c INNER JOIN teacher t ON c.teacher_id=t.id;
INNER JOIN 为内连接,展示的是左右两表都有对应的数据。
SELECT c.id AS course_id,c.*,t.* FROM course c RIGHT JOIN teacher t ON c.teacher_id=t.id;
RIGHT JOIN 为右连接,是以右边的表为’基准’,若左表没有对应的值,用 NULL 来填补。
以本小节所有数据全部连接查询为例:
SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id RIGHT JOIN course c ON b.course_id=c.id INNER JOIN teacher d ON c.teacher_id=d.id;
多表混合连接查询时,后面可以把前面执行的结果集整体当成一个表
ON 后面的表示对连表条件,并且还能对表连接查询的结果集用 WHERE 进行筛选,例如:
SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id RIGHT JOIN course c ON b.course_id=c.id INNER JOIN teacher d ON c.teacher_id=d.id WHERE a.age > 18 AND d.age < 55;
参考资料: http://www.imooc.com/wiki/mysqllesson/mysqljoin.html