13.联表查询-左联

今天开始讲一点高级点的内容。我们的数据库，在生产环境中肯定不是只有一张表，有可能有用户表，商品表，订单表等等等等。这里我们有这样一个场景，有一个班级表，班级表里有班级id，班级名称，还有一个学生表，学生表里有学生id，学生姓名，学生性别，学号，班级id(这个主要用于将班级和学生关联起来)。

CREATE DATABASE coupletDemo;
USE coupletDemo;
CREATE TABLE class(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    PRIMARY KEY(id)    
);
INSERT INTO class (name) VALUES('妖怪班');
INSERT INTO class (name) VALUES('神仙班');

CREATE TABLE student(
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(5) NOT NULL,
   gender VARCHAR(1) NOT NULL,
   stu_no VARCHAR(10) NOT NULL,
   class_id INT NOT NULL,
   PRIMARY KEY(id)
);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('大妖怪','男','00001',1);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('二妖怪','女','00002',1);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('三妖怪','男','00003',1);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('四妖怪','女','00004',1);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('小妖怪','男','00005',1);

INSERT INTO student (name,gender,stu_no,class_id) VALUES('神仙1','男','00006',2);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('神仙2','女','00007',2);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('神仙3','男','00008',2);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('神仙4','女','00009',2);
INSERT INTO student (name,gender,stu_no,class_id) VALUES('神仙5','男','00010',2);

运行上述SQL打开表：


在这些数据的前提下，有个需求，查询所有学生信息以及每个学生所在的班级。这个需求可以用左联实现：

SELECT s.id sid, s.name sname, s.gender sgender,s.stu_no sno,c.name cname FROM student s LEFT JOIN class c ON s.class_id = c.id;

结果就是这样。
这条sql看起来这么长是有点恐怖，其实也很简单，左联的基本语法是：

• SELECT 要查询的内容 FROM 表1 LEFT JOIN 表2 ON 联表条件。

在上述的表结构中设计中，我们可以把student表中的class_i当做是一个外键(当然，这并不是mysql上真正的外键)，这个字段主要是用来存放用户所在班级class的id，这样就相当于将班级和学生关联起来了，这种表与表之间关系叫做一对多，班级是一的一方，学生是多的一方。

通俗来讲，联表就是把两张表通过特定的字段进行关联然后合并成一张大表。其大概过程是这样：

上图仅显示了妖怪班的查询结果，其查询语句应该是：
SELECT c.name, s.name FROM class c LEFT JOIN student s ON c.id = s.class_id WHERE c.name=’妖怪班’;

• 左联查询的定义：首先取出表1（LEFT JOIN左边的表格）中所有数据和表2（LEFT JOIN右边的表格）中与表1所取数据相对应的所有数据，合成新的表格，没有数据则显示NULL。表1的数据为主，并显示在合成表格的左边。
• 如上面合成表，左联是可以带上条件查询的，更多例子比如:

  SELECT s.id sid, s.name sname, s.gender sgender,s.stu_no sno,c.name cname FROM student s LEFT JOIN class c ON s.class_id = c.id WHERE s.gender = '男';
  

  SELECT s.id sid, s.name sname, s.gender sgender,s.stu_no sno,c.name cname FROM student s LEFT JOIN class c ON s.class_id = c.id WHERE s.gender = '男' AND c.name = '神仙班';
  

  当然也可以在此用上前面的排序、分组，以及以后要学到的分页等等。