通过表关联实现查询

建数据库students (创建三个表格 分别存储学生，教师，课程信息)

mysql->create table student

     ->(

     ->key_student int(5) not null auto_increment, student_name varchar(20) not null,primary key (key_student)

     ->);

mysql->create table teacher

     ->（key_teacher int(5) not null auto_increment,teacher_name varhcar(20) not null,primary key (key_teacher)

     ->);

mysql->create table course ( key_course int(5) not null auto_increment,key_student int(5) not null,key_teacher int(5) not null,course_name varchar(20) not null,primary key (key_course)

     ->);

向三个表格添加数据

mysql->insert into student (student_name) values (‘Tom’);

mysql->insert into student (student_name) values (‘Mary’);

mysql->insert into student (student_name) values (‘Bob’);

mysql->insert into teacher (teacher_name) values (‘Thomas Yang’);

mysql->insert into teacher (teacher_name) values (‘Jonna Farmer’);

mysql->insert into teacher (teacher_name) values (‘Christ Miller’);

mysql->insert into course (course_name,key_student,key_teacher) values (‘Mathematics’,2,1);

mysql->insert into course (course_name,key_student,key_teacher) values (‘Physics’,1,3);

mysql->insert into course (course_name,key_student,key_teacher) values (‘Physics’,4,2);

insert into course (course_name,key_student,key_teacher) values (‘Biology’,3,4);

通过表的关联来查询信息，例：列出所有课程的任课教师及选修该课的学生：

mysql->select course.course_name,teacher.teacher_name,student.student_name from course,teacher,student where course.key_student=student.key_student and course.key_teacher=teacher.key_teacher;

可以把上表通过关联生成的虚拟表，虚拟表通过where子句将三个不同的表格连接在一起

mysql->select

     ->course.course_name, teacher.teacher_name

    ->from course,teacher;

mysql->select

     ->course.course_name, teacher.teacher_name

->from course,teacher where course.key_teacher=teacher.key_teacher;