MySQL - three tables (student, course, score) to query the name, number and score of students whose course is mathematics

The evening wind blows 2022-06-24 07:21:21 阅读数:753


One 、 Design table

1.student surface

 Insert picture description here

2.subject surface

 Insert picture description here

3.score surface

 Insert picture description here

Two 、 Query data

demand : Query the names of students whose courses are mathematics , Number , achievement

Method 1 :

where Statement query

select student.sno,student.sname,score.score from student,subject,score where student.sno = score.sno and subject.cno = score.cno and subject.subject_name=" mathematics ";

Query results :
 Insert picture description here

Method 2 :

Use internal connections
select surface 1. Field , surface 2. Field , surface 3. Field from surface 1 inner join surface 2 on surface 1. Field = surface 2. Field inner join surface 3 on surface 1. Field = surface 3. Field where Conditions ;

select s.sno,s.sname,c.score from student s inner join score c on s.sno=c.sno inner join subject u on c.cno=u.cno where u.subject_name=" mathematics "

 Insert picture description here

analysis :
1、 View the three tables separately , Find the associated field
2、 Look at the table and you can see ,student Tabular sno And subject Tabular sno Can match ,subject And of table score Tabular cno matching , So you can filter the data through these two fields
3、 Add a designated account : mathematics , You can find the corresponding data

copyright:author[The evening wind blows],Please bring the original link to reprint, thank you.