连接查询（关于inner join、left join和right join）

2024-03-29 15:43:58

关于多表连接查询：

测试>>>首先创建school数据库然后创建下面4个表：

CREATE TABLE `student`(
`studentno` int(4) not null comment '学号',
    `loginpwd` varchar(20) default null,
    `studentname` varchar(20) default null comment '学生姓名',
    `sex` tinyint(1) default null comment '性别，0或1',
    `gradeid` int(11) default null comment '年级编号',
    `phone` varchar(50) not null comment '联系电话，允许为空',
    `address` varchar(255) not null comment '地址，允许为空',
    `borndate` datetime default null comment '出生时间',
    `email` varchar (50) not null comment '邮箱账号允许为空',
    `identitycard` varchar(18) default null comment '身份证号',
    primary key (`studentno`)
)engine=INNODB default charset=utf8;

create table `grade`(
	`gradeid` int(11) not null auto_increment comment '年级编号',
  `gradename` varchar(50) not null comment '年级名称',
    primary key (`gradeid`)
)engine=INNODB default charset=utf8;

create table `subject`(
	`subjectno`int(11) not null auto_increment comment '课程编号',
    `subjectname` varchar(50) default null comment '课程名称',
    `classhour` int(4) default null comment '学时',
    `gradeid` int(4) default null comment '年级编号',
    primary key (`subjectno`)
)engine=INNODB default charset=utf8;

create table `result`(
	`studentno` int(4) not null comment '学号',
    `subjectno` int(4) not null comment '课程编号',
    `examdate` datetime not null comment '考试日期',
    `studentresult` int (4) not null comment '考试成绩',
    primary key (`studentno`)
)engine=INNODB default charset=utf8;

下面对这四个表插入数据：

insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东','1990-1-1','text111@qq.com','123456199001011233'),
(1002,'123456','刘强',1,2,'13800054222','广东','1990-1-1','text111@qq.com','123456199001011233'),
(1005,'123456','刘强东',1,1,'13856002222','深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1003,'123456','王久',1,3,'13809902222','深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1004,'123456','欣欣',1,2,'13800004222','深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1007,'123456','张磊',1,2,'13800062222','广圳','1990-1-1','text111@qq.com','123456199001011233'),
(1008,'123456','刘鑫',1,2,'13800004222','广圳','1990-1-1','text111@qq.com','123456199001011233'),
(1009,'123456','甘文',1,1,'13805002222','广圳','1990-1-1','text111@qq.com','123456199001011233'),
(1011,'123456','望山',1,3,'13800602222','广圳','1990-1-1','text111@qq.com','123456199001011233');

INSERT INTO`result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES('3','2','2008-12-12','66'),
VALUES('4','3','2008-12-12','87'),
VALUES('5','3','2008-12-12','87'),
VALUES('6','1','2008-12-12','96'),
VALUES('7','1','2008-12-12','77'),
VALUES('8','2','2008-12-12','86');

INSERT INTO`grade`(`gradename`)VALUES('大二'),
INSERT INTO`grade`(`gradename`)VALUES('大二'),
INSERT INTO`grade`(`gradename`)VALUES('大二'),
INSERT INTO`grade`(`gradename`)VALUES('大二'),
INSERT INTO`grade`(`gradename`)VALUES('大二'),
INSERT INTO`grade`(`gradename`)VALUES('大二')

insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

连接查询如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询内连接 inner join 查询两个表中的结果集中的交集外连接 outer join 左外连接 left join (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充) 右外连接 right join (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充) inner join：

SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
inner JOIN result r
ON s.studentno=r.studentno

left join：无论是真是假返回左表全部信息，右表没有则用null代替

SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno

right join：无论是真是假返回右表全部信息，左表没有则用null代替

SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT  JOIN result r
ON s.studentno=r.studentno

使用inner join：先查询student表和result表，再用结果与subject表进行inner join

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数) 
SELECT s.studentno,studentname,subjectname,StudentResult 
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno 
INNER JOIN `subject` sub 
ON sub.subjectno = r.subjectno

码农公寓

相关文章