页次: 1
mysql 连接查询有两个类型 内连接和外连接
内连接 格式
select tb1.f1, tb1.f2 ... , tb2.f1 ... from tb1, tb2 [where 条件];
返回得是笛卡尔积。 也是说 表一有5个结果, 表二有3个结果, 一共就是15个结果。
内连接也可以用 select tb1.f1, tb1.f2 ... , tb2.f1 ... from tb1 inner join tb2 [on 条件]
外连接分为左右
left join 左连接
right join 右连接
左连接返回左表中的所有的记录和右表中连接字段相等的记录。
示例
比如两个表:
mysql> select * from vendor; +------+-----------+ | v_id | v_name | +------+-----------+ | 1 | zhangshan | | 2 | lisi | | 3 | wangwu | +------+-----------+ 3 rows in set (0.00 sec)
mysql> select * from goods; +------+--------+------+---------+ | g_id | g_name | v_id | g_price | +------+--------+------+---------+ | 1 | aaa | 1 | 1.10 | | 2 | aaa | 2 | 2.10 | | 3 | aaa | 3 | 3.10 | | 4 | bbb | 1 | 4.10 | | 5 | bbb | 2 | 5.10 | | 6 | bbb | 3 | 6.10 | | 7 | ccc | 1 | 7.10 | | 8 | ccc | 2 | 8.10 | | 9 | ccc | 3 | 9.10 | +------+--------+------+---------+ 9 rows in set (0.00 sec)
内连接
mysql> select count(*) from vendor, goods; +----------+ | count(*) | +----------+ | 27 | +----------+ 1 row in set (0.00 sec)
可以看到一共是 27条记录
可以可以查看所有的结果
mysql> select * from vendor, goods;
这两条是等价的
select * from vendor inner join goods; mysql> select * from vendor, goods;
这两个也是等价的
mysql> select * from vendor inner join goods on vendor.v_id = goods.v_id; mysql> select * from vendor , goods where vendor.v_id = goods.v_id; +------+-----------+------+--------+------+---------+ | v_id | v_name | g_id | g_name | v_id | g_price | +------+-----------+------+--------+------+---------+ | 1 | zhangshan | 1 | aaa | 1 | 1.10 | | 2 | lisi | 2 | aaa | 2 | 2.10 | | 3 | wangwu | 3 | aaa | 3 | 3.10 | | 1 | zhangshan | 4 | bbb | 1 | 4.10 | | 2 | lisi | 5 | bbb | 2 | 5.10 | | 3 | wangwu | 6 | bbb | 3 | 6.10 | | 1 | zhangshan | 7 | ccc | 1 | 7.10 | | 2 | lisi | 8 | ccc | 2 | 8.10 | | 3 | wangwu | 9 | ccc | 3 | 9.10 | +------+-----------+------+--------+------+---------+
外连接
右连接,
mysql> select * from vendor right outer join goods on vendor.v_id = goods.v_id; +------+-----------+------+--------+------+---------+ | v_id | v_name | g_id | g_name | v_id | g_price | +------+-----------+------+--------+------+---------+ | 1 | zhangshan | 1 | aaa | 1 | 1.10 | | 1 | zhangshan | 4 | bbb | 1 | 4.10 | | 1 | zhangshan | 7 | ccc | 1 | 7.10 | | 2 | lisi | 2 | aaa | 2 | 2.10 | | 2 | lisi | 5 | bbb | 2 | 5.10 | | 2 | lisi | 8 | ccc | 2 | 8.10 | | 3 | wangwu | 3 | aaa | 3 | 3.10 | | 3 | wangwu | 6 | bbb | 3 | 6.10 | | 3 | wangwu | 9 | ccc | 3 | 9.10 | +------+-----------+------+--------+------+---------+ 9 rows in set (0.00 sec)
左连接
mysql> select * from vendor left outer join goods on vendor.v_id = goods.v_id; +------+-----------+------+--------+------+---------+ | v_id | v_name | g_id | g_name | v_id | g_price | +------+-----------+------+--------+------+---------+ | 1 | zhangshan | 1 | aaa | 1 | 1.10 | | 2 | lisi | 2 | aaa | 2 | 2.10 | | 3 | wangwu | 3 | aaa | 3 | 3.10 | | 1 | zhangshan | 4 | bbb | 1 | 4.10 | | 2 | lisi | 5 | bbb | 2 | 5.10 | | 3 | wangwu | 6 | bbb | 3 | 6.10 | | 1 | zhangshan | 7 | ccc | 1 | 7.10 | | 2 | lisi | 8 | ccc | 2 | 8.10 | | 3 | wangwu | 9 | ccc | 3 | 9.10 | +------+-----------+------+--------+------+---------+ 9 rows in set (0.00 sec)
ipbbs.net
离线
页次: 1