mysql连接多表查询语句_Mysql-查询语句之多表查询（等值连接）

数据准备

create table courses(

courseNo int(10) unsigned primary key auto_increment,

name varchar(10)

)

insert into courses values

("1","数据库"),

("2","qtp"),

("3","linux"),

("4","系统测试"),

("5","单元测试"),

("6","测试过程");

create table scores(

id int(10) unsigned primary key auto_increment,

courseNo int(10),

studentno varchar(10),

score tinyint(4)

)

insert into scores values

("1","1","001","90"),

("2","1","002","75"),

("3","2","002","98"),

("4","3","001","86"),

("5","3","003","80"),

("6","4","004","79"),

("7","5","005","96"),

("8","6","006","80");

存入到excel表中，更直观，方便查询

image.png

等值连接(取交集)

语法

select * from 表1，表2 where 表1.列=表2.列

-- 查询学生信息及学生成绩

select * from students as stu,scores as sc

where stu.studentNo=sc.studentno

image.png

内连接

语法

select * from 表1 inner join 表2 on 表1.列=表2.列

-- 查询学生信息及学生成绩

select * from students as stu

inner join scores as sc

on stu.studentNo=sc.studentno

image.png

三个表交叉连接

-- 查询学生信息及学生的课程对应的成绩

select * from students,scores,courses

where students.studentNo=scores.studentno and scores.courseNo=courses.courseNo

select * from students

inner join scores on students.studentNo=scores.studentno

inner join courses on scores.courseNo=courses.courseNo

结果

image.png

练习：查询王昭君的数据库成绩，要求显示姓名，课程名，成绩

image.png