MySQL修炼之联结与集合浅析

吾爱主题阅读：117 2024-04-02 08:06:16 评论：0

联结查询

联结查询就是指两张或者以上的表之间进行匹配查询，一般称之为水平操作，就是最终结果会包含这几个表中所有的列，mysql中有三种联结操作，交叉联结、内联结、外联结。

交叉联结叫做cross join，他对两个表执行笛卡儿积，他将返回两个表中所有列的组成，比如左表中有n条数据，右表中有m条数据，那么最终结果就是n*m条，但是也可以自己与自己联结，那么最终结果就是n*n条，比如下面语句。

1 2 3 4 5 6 7 8 9 10 11 12 select * from orders as a cross join orders as b; + ---------+--------+---------+--------+ | orderid | userid | orderid | userid | + ---------+--------+---------+--------+ | 10007 | 2 | 10001 | 1 | | 10006 | 4 | 10001 | 1 | ..... | 10002 | 1 | 10007 | 2 | | 10001 | 1 | 10007 | 2 | + ---------+--------+---------+--------+ 49 rows in set (0.01 sec)

由于orders表中有7条数据，那么最后将生成49条数据，还有一种写法是下面这样的。

1	`mysql>` `select` `*` `from` `orders` `as` `a ,orders` `as` `b;`

他们的结果都相同，只是写法不同，上面的写法是在1989年，美国国家标准学会对sql进行的规范，称之为ansi sql 89标准，而第一种写法是92年规定的。

交叉联结有个用处就是快速生成重复数据，比如下面语句。

1	`insert` `orders` `select` `a.orderid,a.userid` `from` `orders` `as` `a ,orders` `as` `b limit 10;`

下面就是内联结，分为两部，首先产生笛卡儿积，然后在根据后面on的过滤条件来过滤，他产生两个表中具有相同记录的值。

在on后面除了等于运算符 (=) 之外，还可以使用其他运算符，例如大于 ( >)、小于 ( <) 和不等于 ( <>) 运算符来构成连接条件。

最后一个是outer join，可以按照一些过滤条件来匹配表之间的数据，与inner join不同的是，在通过outer join添加的保留表中存在未找到的匹配数据，mysql支持left outer join、right outer join，写的时候可以省略outer 。

下面是left join所示图，left join返回左表 (table1) 中的所有记录，以及右表 (table2) 中的匹配记录。

下面是right join所示图，right join返回右表 (table2) 中的所有记录，以及左表 (table1) 中的匹配记录。

集合操作

mysql中有个union运算符，用于组合2个或多个select结果集，并删除select语句之间的重复行，使用他时候要遵循以下基本规则：

所有select语句中出现的列数和顺序必须相同。
列也必须具有相同的数据类型。

如现在有以下表

1 2 3 4 5 6 7 8 9 10 create table t1 ( id int primary key ); create table t2 ( id int primary key ); insert into t1 values (1),(2),(3); insert into t2 values (2),(3),(4);

执行以下sql

1 2 3 select id from t1 union select id from t2;

最终结果是这样的。

1 2 3 4 5 6 7 8 9 + ----+ | id | + ----+ | 1 | | 2 | | 3 | | 4 | + ----+ 4 rows in set (0.00 sec)

union语句默认会从结果行集中删除重复的数据，但是，可以使用union all获取重复记录。

1 2 3 select id from t1 union all select id from t2;

结果如下

1 2 3 4 5 6 7 8 9 10 11 + ----+ | id | + ----+ | 1 | | 2 | | 3 | | 2 | | 3 | | 4 | + ----+ 6 rows in set (0.00 sec)