mysql中的自连接查询

一、什么是自连接

自连接查询，顾名思义，就是自己和自己比较。

例如：分数表中，科目1有学生考试不及格，不及格的学生补考了，这个的话，同一个学生，同一个科目出现了2笔成绩，如果查询出成绩高的那一笔记录的话，需要分数表自己和自己比较。

二、数据准备

create database stuMS default charset=’utf8′;

use stuMS;

— 表1：学生信息表 Students

create table Students(

Scode int not null PRIMARY key auto_increment,

SName  char(10) not null,

SAddress  varchar(50) default ‘湖南长沙’,

SGrade  int,

SEmail  varchar(50) CHECK(SEmail like ‘%@%’),

SSex    int CHECK(ssex=0 or ssex=1),

CreateTime datetime default  NOW()

);

— 表2: 科目表 Course

create table Course(

CourseID  int not null primary key,

CourseName   varchar(50) not null

);

— — 表3：成绩表 Score

create table Score(

ScoreID  int not null PRIMARY key,

CourseID  int not null,

Studentid  int not null,

Score   smallint,

CONSTRAINT score_Studentid_fk foreign key(Studentid)

references Students(Scode),

CONSTRAINT score_Courseid_fk foreign key(CourseID)

references Course(CourseID)

);

— 4.  表1学生信息表插入数据

insert into Students(SName,saddress,ssex) VALUES(‘张三’,’北京’,1);

insert into Students(SName,saddress,ssex) VALUES(‘李四’,’上海’,0);

insert into Students(SName,saddress,ssex) VALUES(‘王五’,’广州’,1);

insert into Students(SName,saddress,ssex) VALUES(‘赵六’,’深圳’,0);

insert into Students(SName,saddress,ssex) VALUES(‘田七’,’长沙’,1);

— 5.  表2 学生课程表中输入数据

insert into Course VALUES(1,’语文’);

insert into Course VALUES(2,’数学’);

insert into Course VALUES(3,’英语’);

insert into Course VALUES(4,’测试’);

— 6.  表3 成绩表中插入课程1的成绩，要求有人不及格

INSERT into score VALUES(1,1,1,100);

INSERT into score VALUES(2,1,2,88);

INSERT into score VALUES(3,1,3,50);

INSERT into score VALUES(4,1,4,70);

INSERT into score VALUES(5,1,5,46);

— 7.  表3 成绩表中插入课程1的补考成绩，补考后成绩及格

INSERT into score VALUES(6,1,3,80);

INSERT into score VALUES(7,1,5,82);

三、分步练习

–练习目标: 使用自身查询，查询科目1 的成绩，如果重考，取分数高的那一笔

select  * from score where courseid=1;

— 练习1：找出重考分数高的成绩

select a.*

from score a,score b

where a.studentid=b.studentid

and a.courseid=b.courseid

and a.score > b.score

and a.courseid=1;

— 练习2：找出科目1不及格的分数

select a.*

from score a,score b

where a.studentid=b.studentid

and a.courseid=b.courseid

and a.score < b.score

and a.courseid=1;

— 练习3：找出需要排除的数据的scoreid

select a.scoreid

from score a,score b

where a.courseid=b.courseid

and a.studentid=b.studentid

and a.score<b.score

and a.courseid=1;

–练习4：最后的结果

select *

from score

where scoreid not in   — 排除的数据的不显示，其他数据显示

(select a.scoreid

from score a,score b

where a.courseid=b.courseid

and a.studentid=b.studentid

and a.score<b.score

and a.courseid=1

)and courseid=1;

如果有对软件测试感兴趣的小伙伴可以加群了解更多：点击进群