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 添加这句就是要名字和学号相对应，如若不加的话就会默认匹配一条记录而已，会出错的。

---

今天去面试，连最基本的这些都没答好，捂脸，奈何不是科班出身，自己累的要命，东补西补的。
还是要写东西才有印象。完成一篇，是不是要点激励的话结束，?，加油，明天。