数据准备
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