网站字号,域名解析查询站长工具,深圳优化seo,网站cms是什么1.连接简介 1.1.连接的本质 为了故事的顺利发展#xff0c;我们先建立两个简单的表并给它们填充一点数据#xff1a;
mysql CREATE TABLE t1 (m1 int, n1 char(1));
mysql CREATE TABLE t2 (m2 int, n2 char(1));
mysql INSERT INTO t1 VALUES(1, a), (2, b), (…1.连接简介 1.1.连接的本质 为了故事的顺利发展我们先建立两个简单的表并给它们填充一点数据
mysql CREATE TABLE t1 (m1 int, n1 char(1));
mysql CREATE TABLE t2 (m2 int, n2 char(1));
mysql INSERT INTO t1 VALUES(1, a), (2, b), (3, c);
mysql INSERT INTO t2 VALUES(2, b), (3, c), (4, d);连接 的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。所以我们把 t1 和 t2 两个表连接起来的过程如下图所示 在 MySQL 中连接查询的语法也很随意只要在 FROM 语句后边跟多个表名就好了比如我们把 t1 表和 t2 表连接起来的查询语句可以写成这样mysql SELECT * FROM t1, t2;
1.2.连接过程简介 如果我们乐意我们可以连接任意数量张表但是如果没有任何限制条件的话这些表连接起来产生的 笛卡尔积 可能是非常巨大的。比方说3个100行记录的表连接起来产生的 笛卡尔积 就有 100×100×1001000000 行数据所以在连接的时候过滤掉特定记录组合是有必要的在连接查询中的过滤条件可以分成两种 (1). 涉及单表的条件 这种只设计单表的过滤条件我们之前都提到过一万遍了我们之前也一直称为 搜索条件 比如 t1.m1 1是只针对 t1 表的过滤条件 t2.n2 ‘d’ 是只针对 t2 表的过滤条件。 (2). 涉及两表的条件 这种过滤条件我们之前没见过比如 t1.m1 t2.m2 、 t1.n1 t2.n2 等这些条件中涉及到了两个表。
下边我们就要看一下携带过滤条件的连接查询的大致执行过程了比方说下边这个查询语句SELECT * FROM t1, t2 WHERE t1.m1 1 AND t1.m1 t2.m2 AND t2.n2 d;在这个查询中我们指明了这三个过滤条件 (1). t1.m1 1 (2). t1.m1 t2.m2 (3). t2.n2 ‘d’
那么这个连接查询的大致执行过程如下 (1). 首先确定第一个需要查询的表这个表称之为 驱动表 。怎样在单表中执行查询语句我们在前一章都唠叨过了只需要选取代价最小的那种访问方法去执行单表查询语句就好了就是说从const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询。此处假设使用 t1 作为驱动表那么就需要到 t1 表中找满足 t1.m1 1 的记录因为表中的数据太少我们也没在表上建立二级索引所以此处查询 t1 表的访问方法就设定为 all 吧也就是采用全表扫描的方式执行单表查询。关于如何提升连接查询的性能我们之后再说现在先把基本概念捋清楚哈。所以查询过程就如下图所示 我们可以看到 t1 表中符合 t1.m1 1 的记录有两条。 (2). 针对上一步骤中从驱动表产生的结果集中的每一条记录分别需要到 t2 表中查找匹配的记录所谓 匹配的记录 指的是符合过滤条件的记录。因为是根据 t1 表中的记录去找 t2 表中的记录所以 t2 表也可以被称之为 被驱动表 。上一步骤从驱动表中得到了2条记录所以需要查询2次 t2 表。此时涉及两个表的列的过滤条件 t1.m1 t2.m2 就派上用场了 a. 当 t1.m1 2 时过滤条件 t1.m1 t2.m2 就相当于 t2.m2 2 所以此时 t2 表相当于有了 t2.m2 2 、 t2.n2 ‘d’ 这两个过滤条件然后到 t2 表中执行单表查询。 b. 当 t1.m1 3 时过滤条件 t1.m1 t2.m2 就相当于 t2.m2 3 所以此时 t2 表相当于有了 t2.m2 3 、 t2.n2 ‘d’ 这两个过滤条件然后到 t2 表中执行单表查询。
所以整个连接查询的执行过程就如下图所示 也就是说整个连接查询最后的结果只有两条符合过滤条件的记录 从上边两个步骤可以看出来我们上边唠叨的这个两表连接查询共需要查询1次 t1 表2次 t2 表。当然这是在特定的过滤条件下的结果如果我们把 t1.m1 1 这个条件去掉那么从 t1 表中查出的记录就有3条就需要查询3次 t2 表了。也就是说在两表连接查询中驱动表只需要访问一次被驱动表可能被访问多次。
1.3.内连接和外连接 为了大家更好理解后边内容我们先创建两个有现实意义的表。
CREATE TABLE student (number INT NOT NULL AUTO_INCREMENT COMMENT 学号,name VARCHAR(5) COMMENT 姓名,major VARCHAR(30) COMMENT 专业,PRIMARY KEY (number)
) EngineInnoDB CHARSETutf8 COMMENT 学生信息表;CREATE TABLE score (number INT COMMENT 学号,subject VARCHAR(30) COMMENT 科目,score TINYINT COMMENT 成绩,PRIMARY KEY (number, score)
) EngineInnoDB CHARSETutf8 COMMENT 学生成绩表;我们新建了一个学生信息表一个学生成绩表然后我们向上述两个表中插入一些数据为节省篇幅具体插入过程就不唠叨了插入后两表中的数据如下
mysql SELECT * FROM student;mysql SELECT * FROM score;现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了因为 score 中没有姓名信息所以不能单纯只查询 score 表。连接过程就是从 student 表中取出记录在 score 表中查找 number 相同的成绩记录所以过滤条件就是 student.number socre.number 整个查询语句就是这样
mysql SELECT * FROM student, score WHERE student.number score.number;字段有点多哦我们少查询几个字段
mysql SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number s2.number;从上述查询结果中我们可以看到各个同学对应的各科成绩就都被查出来了可是有个问题 史珍香 同学也就是学号为 20180103 的同学因为某些原因没有参加考试所以在 score 表中没有对应的成绩记录。那如果老师想查看所有同学的考试成绩即使是缺考的同学也应该展示出来但是到目前为止我们介绍的 连接查询 是无法完成这样的需求的。我们稍微思考一下这个需求其本质是想驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加入到结果集。为了解决这个问题就有了 内连接 和 外连接 的概念 (1). 对于 内连接 的两个表驱动表中的记录在被驱动表中找不到匹配的记录该记录不会加入到最后的结果集我们上边提到的连接都是所谓的 内连接 。 (2). 对于 外连接 的两个表驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加入到结果集。
在 MySQL 中根据选取驱动表的不同外连接仍然可以细分为2种 (1). 左外连接 选取左侧的表为驱动表。 (2). 右外连接 选取右侧的表为驱动表。
可是这样仍然存在问题即使对于外连接来说有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。 把过滤条件分为两种来解决这个问题 (1). WHERE 子句中的过滤条件 WHERE 子句中的过滤条件就是我们平时见的那种不论是内连接还是外连接凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。 (2). ON 子句中的过滤条件 对于外连接的驱动表的记录来说如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录那么该记录仍然会被加入到结果集中对应的被驱动表记录的各个字段使用 NULL 值填充。
需要注意的是这个 ON 子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的所以如果把 ON 子句放到内连接中 MySQL 会把它和 WHERE 子句一样对待也就是说内连接中的WHERE子句和ON子句是等价的。
一般情况下我们都把只涉及单表的过滤条件放到 WHERE 子句中把涉及两表的过滤条件都放到 ON 子句中我们也一般把放到 ON 子句中的过滤条件也称之为 连接条件 。
左外连接和右外连接简称左连接和右连接。
1.3.1.左外连接的语法 左外连接的语法还是挺简单的比如我们要把 t1 表和 t2 表进行左外连接查询可以这么写SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];
其中中括号里的 OUTER 单词是可以省略的。对于 LEFT JOIN 类型的连接来说我们把放在左边的表称之为外表或者驱动表右边的表称之为内表或者被驱动表。所以上述例子中 t1 就是外表或者驱动表 t2 就是内表或者被驱动表。需要注意的是对于左外连接和右外连接来说必须使用 ON 子句来指出连接条件。
再次回到我们上边那个现实问题中来看看怎样写查询语句才能把所有的学生的成绩信息都查询出来即使是缺考的考生也应该被放到结果集中 SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number s2.number; 从结果集中可以看出来虽然 史珍香 并没有对应的成绩记录但是由于采用的是连接类型为左外连接所以仍然把她放到了结果集中只不过在对应的成绩记录的各列使用 NULL 值填充而已。
1.3.2.右外连接的语法 右外连接和左外连接的原理是一样一样的语法也只是把 LEFT 换成 RIGHT 而已SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];
只不过驱动表是右边的表被驱动表是左边的表具体就不唠叨了。
1.3.3.内连接的语法 内连接和外连接的根本区别就是在驱动表中的记录不符合 ON 子句中的连接条件时不会把该记录加入到最后的结果集我们最开始唠叨的那些连接查询的类型都是内连接。不过之前仅仅提到了一种最简单的内连接语法就是直接把需要连接的多个表都放到 FROM 子句后边。其实针对内连接MySQL提供了好多不同的语法我们以 t1 和 t2 表为例瞅瞅SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];
也就是说在 MySQL 中下边这几种内连接的写法都是等价的 a. SELECT * FROM t1 JOIN t2; b. SELECT * FROM t1 INNER JOIN t2; c. SELECT * FROM t1 CROSS JOIN t2;
上边的这些写法和直接把需要连接的表名放到 FROM 语句之后用逗号 , 分隔开的写法是等价的SELECT * FROM t1, t2;
由于在内连接中ON子句和WHERE子句是等价的所以内连接中不要求强制写明ON子句。
我们前边说过连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说由于凡是不符合 ON 子句或 WHERE 子句中的条件的记录都会被过滤掉其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去所以对于内连接来说驱动表和被驱动表是可以互换的并不会影响最后的查询结果。但是对于外连接来说由于驱动表中的记录即使在被驱动表中找不到符合 ON 子句连接条件的记录也不会踢出去所以此时驱动表和被驱动表的关系就很重要了也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。
