目录
背景
最近时不时都要去弄MySQL数据库,不仅仅工作要用到,接的私活里面,也有很多时候要使用MySQL数据库。
在此记录下这个left join的功能,因为十分有用。
使用的景场
在数据库里面,因为某些表存在外键的关联,比如一个人住了某个房子,这个房子里面还有很多信息,这个时候。
如果要查询,某个这个人的信息,以及他所居住的房子的信息,以一列来显示,那么使用left join将会是一个很好的查询方式;
代码及演示
如下的数据库结构,代码将在本文的末尾给出:
这里面每一个表都有一个id,是主键。
其中borrow表如下:
他的studentID是外键,内容为student的主键
他的bookID是外键,内容为books的主键
下面来看下role和student中的内容:
这里为什么要把stauts管理role的外键呢?如果role要进行增加,并且还有其他表要使用,那么这种方式,只需增加或修改role表,其他的表将不会收到什么影响;
如果现在有这样的功能,查询所有的学生,并且把他们的职位也显示上去,如果是这样的SQL将会有个问题:
SELECT student.id, student.name, student.sex, role.name FROM student, role
从中可以看到,student表中所有数据,和role表中的所有数据对应了,造成了出现了7*4=28个结果;
这样是不对的,应该使用左链接,顾名思义,使用左链接就可以把status换成指定职位,SQL如下:
SELECT student.id, student.name, student.sex, role.name FROM student LEFT JOIN role ON role.id=student.status
这种才是想要的结果;
比如现在再来个例子,这里有个借书表,内容如下:
其中studentID为student的主键,bookID为books的主键。
其中books内容如下:
这里在刚刚sql语句的基础上,提出一个新的需求,显示出学生的信息和职位,以及接过书的信息
SQL如下:
SELECT student.id, student.name, student.sex, role.name, borrow.id FROM student LEFT JOIN role ON role.id=student.status LEFT JOIN borrow ON borrow.studentID=student.id
运行截图如下:
这里因为books和student是通过borrow表链接的,并不是直接左相连。所以并不能直接用左链接!
可能也和自己的水平有关;
【注意,此处可以使用左链接进行查询,本人已经弄出来了,在另外一篇博文中有说明:2019-05-20 14:01:41】
如果要查询小红借的书的信息;
SELECT student.id, student.name, student.sex, role.name, borrow.id FROM student LEFT JOIN role ON role.id=student.status LEFT JOIN borrow ON borrow.studentID=student.id WHERE student.id=3;
运行截图如下:
SQL代码如下:
/*
SQLyog Ultimate v11.24 (32 bit)
MySQL - 5.5.47 : Database - leftjointest
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`leftjointest` /*!40100 DEFAULT CHARACTER SET gbk */;
USE `leftjointest`;
/*Table structure for table `books` */
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`id` int(11) NOT NULL,
`bookName` varchar(50) NOT NULL,
`author` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*Data for the table `books` */
insert into `books`(`id`,`bookName`,`author`) values (0,'十万个为什么','嘉良传媒'),(1,'淘气包马小跳漫画版','杨红樱'),(2,'丁丁历险记','本书编写组'),(3,'不可思议的事件簿6','雷欧幻像'),(4,'神奇校车桥梁书版','乔安娜·柯尔');
/*Table structure for table `borrow` */
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
`id` int(11) NOT NULL,
`studentID` int(11) NOT NULL,
`bookID` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `studentID` (`studentID`),
KEY `bookID` (`bookID`),
CONSTRAINT `borrow_ibfk_1` FOREIGN KEY (`studentID`) REFERENCES `student` (`id`),
CONSTRAINT `borrow_ibfk_2` FOREIGN KEY (`bookID`) REFERENCES `books` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*Data for the table `borrow` */
insert into `borrow`(`id`,`studentID`,`bookID`) values (0,3,0),(1,3,1),(2,3,4),(3,1,4),(4,1,2);
/*Table structure for table `role` */
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*Data for the table `role` */
insert into `role`(`id`,`name`) values (1,'普通学生'),(2,'三好学生'),(3,'扛把子'),(4,'总扛把子');
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` varchar(8) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `status` (`status`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`status`) REFERENCES `role` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;
/*Data for the table `student` */
insert into `student`(`id`,`name`,`sex`,`status`) values (1,'小明','男',1),(3,'小红','女',2),(4,'小黄','男',1),(5,'小刚','男',3),(6,'小丁','男',1),(7,'小紫','女',1),(8,'大刚','男',4);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;