MySql 在cmd下的学习笔记 —— 有关多表查询的操作（内连接，外连接，交叉连接）

mysql> create table test5(
    -> id int,
    -> sname varchar(20)
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> create table test6(
    -> cat_id int,
    -> cname varchar(20)
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test5
    -> values
    -> (1, '甲'),
    -> (2, '乙'),
    -> (3, '丙');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test6
    -> values
    -> (95, '丁'),
    -> (96, '戊');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

先插入两个表，便于之后的学习

 交叉连接

建立一张小的goods表方便练习

mysql> create table minigoods like goods;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into minigoods
    -> select * from goods limit 3;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

交叉相乘（行数为两表行数相成，列数为两表列相加）

 

 字段列表中的“Cat_ID”列不明确。

 两张表都存在cat_id列，某一列名，在两张表内都出现，需要指定表名.列名

mysql> select goods_id, minigoods.cat_id,goods_name, category.cat_id, cat_name
    -> from minigoods, category;
+----------+--------+-------------------------------+--------+----------------+
| goods_id | cat_id | goods_name                    | cat_id | cat_name       |
+----------+--------+-------------------------------+--------+----------------+
|        1 |      4 | KD876                         |      1 | 手机类型              |
|        2 |      8 | vivo充电器                     |      1 | 手机类型              |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      1 | 手机类型              |
|        1 |      4 | KD876                         |      2 | CDMA手机          |
|        2 |      8 | vivo充电器                     |      2 | CDMA手机          |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      2 | CDMA手机          |
|        1 |      4 | KD876                         |      3 | 智能手机              |
|        2 |      8 | vivo充电器                     |      3 | 智能手机              |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      3 | 智能手机              |
|        1 |      4 | KD876                         |      4 | 4G手机            |
|        2 |      8 | vivo充电器                     |      4 | 4G手机            |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      4 | 4G手机            |
|        1 |      4 | KD876                         |      5 | **手机            |
|        2 |      8 | vivo充电器                     |      5 | **手机            |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      5 | **手机            |
|        1 |      4 | KD876                         |      6 | 手机配件              |
|        2 |      8 | vivo充电器                     |      6 | 手机配件              |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      6 | 手机配件              |
|        1 |      4 | KD876                         |      7 | 充电器               |
|        2 |      8 | vivo充电器                     |      7 | 充电器               |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      7 | 充电器               |
|        1 |      4 | KD876                         |      8 | 耳机               |
|        2 |      8 | vivo充电器                     |      8 | 耳机               |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      8 | 耳机               |
|        1 |      4 | KD876                         |      9 | 电池               |
|        2 |      8 | vivo充电器                     |      9 | 电池               |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |      9 | 电池               |
|        1 |      4 | KD876                         |     11 | 内存卡和读卡器           |
|        2 |      8 | vivo充电器                     |     11 | 内存卡和读卡器           |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机     |     11 | 内存卡和读卡器           |
|        1 |      4 | KD876                         |     12 | 充值卡              |
|        2 |      8 | vivo充电器                     |     12 | 充值卡              |
|        3 |      8 | 兰士顿D4 重低音四核双动圈耳机      |     12 | 充值卡

如果嫌太长了 也可以选择用别名的方式

 但在查询中，明显看出有很多数据冗余

如：

 

这三行只说明了这三个商品的cat_id分别是4，8，8 以及在category表中cat_id = 1的cat_name = '手机类型'

这样的数据我们一般认为意义不大

我们需要的数据一般为minigoods的cat_id和category的cat_id相等的数据

mysql> select minigoods.cat_id, goods_id, goods_name, category.cat_id, cat_name
    -> from minigoods, category
    -> where minigoods.cat_id = category.cat_id;
+--------+----------+-------------------------------+--------+----------+
| cat_id | goods_id | goods_name               | cat_id | cat_name |
+--------+----------+-------------------------------+--------+----------+
|      4 |      1   | KD876                    |      4 | 4G手机      |
|      8 |      2   | vivo充电器                |      8 | 耳机         |
|      8 |      3   | 兰士顿D4 重低音四核双动圈耳机 |      8 | 耳机         |
+--------+----------+-------------------------------+--------+----------+
3 rows in set (0.00 sec)

这样的数据就不会大量冗余

但是交叉连接，n*m行，再删除，效率不高

建立两张小表，方便比较各种连接的不同

mysql> create table test7(
    -> id int not null default 0,
    -> name varchar(20) not null default ''
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> create table test8(
    -> id int not null default 0,
    -> name varchar(20) not null default ''
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test7
    -> values
    -> (1, 't1'),
    -> (2, 't2'),
    -> (3, 't3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test8
    -> values
    -> (1, 't1'),
    -> (2, 't2'),
    -> (4, 't4');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

内连接

显示的是左右两边都有的id记录

左连接

 

在左连接中可以看到

test7的记录都显示出来了，而id = 3时，匹配不到test8中id，

所以出现了第三行的结果

（左连接是最少显示左边的记录数）

右连接

 

而右外接则显示的是全部的右边的信息，如果左边没有补null

（右连接时最少显示右边的行数）