MYSQL实现连表查询

一.创建相应的表
1）创建一张学生信息的表，包含 id（自增），学号，姓名，性别，身高，体重，备注

CREATE TABLE t_student (
id int(25) PRIMARY KEY auto_increment,
number int(25) UNIQUE not null,
name varchar(25) not null ,
gender varchar(9) not null,
height int(25) default 0,
weight int(25) default 0,
comment varchar(9) default ' '
);
insert into t_student values(null,2018001,'李四','男',167,50,'');
insert into t_student values(null,2018002,'李内','男',173,58,'');
insert into t_student values(null,2018003,'蒙牛','男',172,70,'');

±—±--------±-------±-------±-------±-------±--------+
| id | number | name | gender | height | weight | comment |
±—±--------±-------±-------±-------±-------±--------+
| 1 | 2018001 | 李四 | 男 | 167 | 50 | |
| 2 | 2018002 | 李内 | 男 | 173 | 58 | |
| 3 | 2018003 | 蒙牛 | 男 | 172 | 70 | |
±—±--------±-------±-------±-------±-------±--------+
2）创建一张成绩表，包含 成绩ID ，学号，科目（语数英），年级，分数
根据设计的目的这个时候，要考虑到两张表之间的关系，学生信息表的学号成为成绩表的外键，确保学号的一致性,代码如下：
需要注意的是，在下表中不能再将number学号再设置为唯一键了，因为要存放多个重复的值。

create table t_grade (
id int(26) primary key auto_increment,
number int(26) not null ,
subject varchar(10) not null ,
class varchar(10) not null,
score float(10,2) default 0.0 ,
foreign key (number) REFERENCES t_student(number));

insert into t_grade values(null,2018001,'数学','高一上',89);
insert into t_grade values(null,2018001,'语文','高一上',93);
insert into t_grade values(null,2018001,'英语','高一上',79);

insert into t_grade values(null,2018002,'数学','高一上',84);
insert into t_grade values(null,2018002,'语文','高一上',95);
insert into t_grade values(null,2018002,'英语','高一上',45);

insert into t_grade values(null,2018003,'数学','高一上',84);
insert into t_grade values(null,2018003,'语文','高一上',99);
insert into t_grade values(null,2018003,'英语','高一上',78);

±—±--------±--------±----------±------+
| id | number | subject | class | score |
±—±--------±--------±----------±------+
| 1 | 2018001 | 数学 | 高一上 | 89.00 |
| 2 | 2018001 | 语文 | 高一上 | 93.00 |
| 3 | 2018001 | 英语 | 高一上 | 79.00 |
| 4 | 2018002 | 数学 | 高一上 | 84.00 |
| 5 | 2018002 | 语文 | 高一上 | 95.00 |
| 6 | 2018002 | 英语 | 高一上 | 45.00 |
| 7 | 2018003 | 数学 | 高一上 | 84.00 |
| 8 | 2018003 | 语文 | 高一上 | 99.00 |
| 9 | 2018003 | 英语 | 高一上 | 78.00 |
±—±--------±--------±----------±------+

以上的难点在于处理两个表之间的关系。

二.进行连表查询
1）查询高一上学期，所有人的总成绩，并且从高到低顺序

select name,student.number,class,sum(score) from t_student as student,t_grade as grade 
where student.number=grade.number and class = '高一上' 
GROUP BY grade.number ORDER BY SUM(score) ASC ;

±-------±--------±----------±-----------+
| name | number | class | sum(score) |
±-------±--------±----------±-----------+
| 李内 | 2018002 | 高一上 | 224.00 |
| 李四 | 2018001 | 高一上 | 261.00 |
| 蒙牛 | 2018003 | 高一上 | 261.00 |
±-------±--------±----------±-----------+

首先，分析上面的语句，先选则要查询的字段，注意到number字段在两个表中都有，所以可以用来做，相等判断，保持名字和学号在两个表中的一致性。
两个表中都有number字段，指定其中一个即可。
还有是根据number分组的，所以查询选择分组很重要。
2）查询高一上所有女同学的总成绩
±—±--------±-------±-------±-------±-------±--------+
| id | number | name | gender | height | weight | comment |
±—±--------±-------±-------±-------±-------±--------+
| 1 | 2018001 | 李四 | 男 | 167 | 50 | |
| 2 | 2018002 | 李内 | 男 | 173 | 58 | |
| 3 | 2018003 | 蒙牛 | 男 | 172 | 70 | |
| 4 | 2018004 | 慧萍 | 女 | 167 | 50 | |
| 5 | 2018005 | 流水 | 女 | 173 | 58 | |
| 6 | 2018006 | 蒙香 | 女 | 172 | 70 | |
±—±--------±-------±-------±-------±-------±--------+
±—±--------±--------±----------±------+
| id | number | subject | class | score |
±—±--------±--------±----------±------+
| 1 | 2018001 | 数学 | 高一上 | 89.00 |
| 2 | 2018001 | 语文 | 高一上 | 93.00 |
| 3 | 2018001 | 英语 | 高一上 | 79.00 |
| 4 | 2018002 | 数学 | 高一上 | 84.00 |
| 5 | 2018002 | 语文 | 高一上 | 95.00 |
| 6 | 2018002 | 英语 | 高一上 | 45.00 |
| 7 | 2018003 | 数学 | 高一上 | 84.00 |
| 8 | 2018003 | 语文 | 高一上 | 99.00 |
| 9 | 2018003 | 英语 | 高一上 | 78.00 |
| 10 | 2018004 | 数学 | 高一上 | 89.00 |
| 11 | 2018004 | 语文 | 高一上 | 93.00 |
| 12 | 2018004 | 英语 | 高一上 | 79.00 |
| 13 | 2018005 | 数学 | 高一上 | 84.00 |
| 14 | 2018005 | 语文 | 高一上 | 95.00 |
| 15 | 2018005 | 英语 | 高一上 | 45.00 |
| 16 | 2018006 | 数学 | 高一上 | 84.00 |
| 17 | 2018006 | 语文 | 高一上 | 99.00 |
| 18 | 2018006 | 英语 | 高一上 | 78.00 |
±—±--------±--------±----------±------+
查询代码如下：

select name,gender,students.number,class,SUM(score) from t_student as students,t_grade as grade 
where students.number=grade.number and class= '高一上' and students.gender='女'
GROUP BY grade.number ORDER BY SUM(score) ASC;

 

关键点还是一样，要确保两表的数据一致性，students.number=grade.number 添加这句就是要名字和学号相对应，如若不加的话就会默认匹配一条记录而已，会出错的。