mysql链接网页进行查询_MySQL连接查询 - xxggy的个人页面 - OSCHINA - 中文开源技术交流社区...

最新推荐文章于 2024-04-16 08:43:07 发布

杨松圣

最新推荐文章于 2024-04-16 08:43:07 发布

阅读量332

点赞数

文章标签： mysql链接网页进行查询

本文链接：https://blog.csdn.net/weixin_30066287/article/details/113593748

版权

MySQL连接查询

MySql表连接查询，两个表之间的连接查

tb_order表

orderId

orderNo

personId

12345

23456

34567

45678

56789

tb_person表

personId

last_name

first_name

address

city

sdf

1.内连接

内连接(inner join)是应用程序中用的普遍的"连接"操作，它一般都是默认连接类型。

mysql> select a.*,b.* from tb_person as a inner join tb_order as b;

+----------+-----------+------------+---------+------+---------+---------+----------+

+----------+-----------+------------+---------+------+---------+---------+----------+

| 1 | hh | aa | ee | dd | 1 | 12345 | 3 |

| 2 | qq | ww | ee | rr | 1 | 12345 | 3 |

| 3 | yy | ii | kk | nn | 1 | 12345 | 3 |

| 4 | tr | rt | hg | mn | 1 | 12345 | 3 |

| 5 | sdf | sd | sd | ds | 1 | 12345 | 3 |

| 1 | hh | aa | ee | dd | 2 | 23456 | 3 |

| 2 | qq | ww | ee | rr | 2 | 23456 | 3 |

| 3 | yy | ii | kk | nn | 2 | 23456 | 3 |

| 4 | tr | rt | hg | mn | 2 | 23456 | 3 |

| 5 | sdf | sd | sd | ds | 2 | 23456 | 3 |

| 1 | hh | aa | ee | dd | 3 | 34567 | 1 |

| 2 | qq | ww | ee | rr | 3 | 34567 | 1 |

| 3 | yy | ii | kk | nn | 3 | 34567 | 1 |

| 4 | tr | rt | hg | mn | 3 | 34567 | 1 |

| 5 | sdf | sd | sd | ds | 3 | 34567 | 1 |

| 1 | hh | aa | ee | dd | 4 | 45678 | 1 |

| 2 | qq | ww | ee | rr | 4 | 45678 | 1 |

| 3 | yy | ii | kk | nn | 4 | 45678 | 1 |

| 4 | tr | rt | hg | mn | 4 | 45678 | 1 |

| 5 | sdf | sd | sd | ds | 4 | 45678 | 1 |

| 1 | hh | aa | ee | dd | 5 | 56789 | 2 |

| 2 | qq | ww | ee | rr | 5 | 56789 | 2 |

| 3 | yy | ii | kk | nn | 5 | 56789 | 2 |

| 4 | tr | rt | hg | mn | 5 | 56789 | 2 |

| 5 | sdf | sd | sd | ds | 5 | 56789 | 2 |

+----------+-----------+------------+---------+------+---------+---------+----------+

25 rows in set

内连接使用ON子句指定两个表的连接条件，WHERE子句来指定条件子句。

mysql> select a.*,b.* from tb_person as a inner join tb_order as b on a.personId=b.personId;

+----------+-----------+------------+---------+------+---------+---------+----------+

+----------+-----------+------------+---------+------+---------+---------+----------+

| 3 | yy | ii | kk | nn | 1 | 12345 | 3 |

| 3 | yy | ii | kk | nn | 2 | 23456 | 3 |

| 1 | hh | aa | ee | dd | 3 | 34567 | 1 |

| 1 | hh | aa | ee | dd | 4 | 45678 | 1 |

| 2 | qq | ww | ee | rr | 5 | 56789 | 2 |

+----------+-----------+------------+---------+------+---------+---------+----------+

5 rows in set

上面sql语句的作用就是查询出所有人的订单。其实上面那条查询等价于：

mysql> select * from tb_person a,tb_order b where a.personId=b.personId;

2.左外连接

使用ON子句指定两个表的连接条件，WHERE子句来指定条件子句

LEFT JOIN 关键字会从左表那里返回所有的行，即使在右表中没有匹配的行。

查询personId为1的人的名字和所有的订单号

mysql> select a.orderNo,b.last_name from tb_order as a left join tb_person as b on a.personId=b.personId where b.personId=1;

+---------+-----------+

| orderNo | last_name |

+---------+-----------+

| 34567 | hh |

| 45678 | hh |

+---------+-----------+

2 rows in set

左外连接不加查询条件，这条sql语句作用：查询所有人的订单

mysql> select a.orderNo,b.last_name from tb_order as a left join tb_person as b on a.personId=b.personId;

+---------+-----------+

| orderNo | last_name |

+---------+-----------+

| 12345 | yy |

| 23456 | yy |

| 34567 | hh |

| 45678 | hh |

| 56789 | qq |

+---------+-----------+

5 rows in set

3.右外连接

使用ON子句指定两个表的连接条件，WHERE子句来指定条件子句。

mysql> select * from tb_order as a right join tb_person as b on a.personId=b.personId;

+---------+---------+----------+----------+-----------+------------+---------+------+

+---------+---------+----------+----------+-----------+------------+---------+------+

| 3 | 34567 | 1 | 1 | hh | aa | ee | dd |

| 4 | 45678 | 1 | 1 | hh | aa | ee | dd |

| 5 | 56789 | 2 | 2 | qq | ww | ee | rr |

| 1 | 12345 | 3 | 3 | yy | ii | kk | nn |

| 2 | 23456 | 3 | 3 | yy | ii | kk | nn |

| NULL | NULL | NULL | 4 | tr | rt | hg | mn |

| NULL | NULL | NULL | 5 | sdf | sd | sd | ds |

+---------+---------+----------+----------+-----------+------------+---------+------+

7 rows in set

可以看到左边的表中的记录都被查询出来了

sql-1

mysql> select * from tb_order as a right join tb_person as b on a.personId=b.personId and a.orderId=1;

+---------+---------+----------+----------+-----------+------------+---------+------+

+---------+---------+----------+----------+-----------+------------+---------+------+

| NULL | NULL | NULL | 1 | hh | aa | ee | dd |

| NULL | NULL | NULL | 2 | qq | ww | ee | rr |

| 1 | 12345 | 3 | 3 | yy | ii | kk | nn |

| NULL | NULL | NULL | 4 | tr | rt | hg | mn |

| NULL | NULL | NULL | 5 | sdf | sd | sd | ds |

+---------+---------+----------+----------+-----------+------------+---------+------+

5 rows in set

通过该结果我们可以猜想到这条sql语句是这样工作的：

从右表中读出一条记录，选出所有与on匹配的右表纪录(n条)进行连接，但没有符合连接条件(a.personId=b.personId and a.orderId=1)的记录，所以匹配为空记录。当personId = 3 时，根据连接条件有一条记录匹配。。

sql-2

mysql> select * from tb_order as a right join tb_person as b on a.personId=b.personId and a.orderId=1 where b.personId=1;

+---------+---------+----------+----------+-----------+------------+---------+------+

+---------+---------+----------+----------+-----------+------------+---------+------+

| NULL | NULL | NULL | 1 | hh | aa | ee | dd |

+---------+---------+----------+----------+-----------+------------+---------+------+

1 row in set

这条语句比上一条语句多了一个限定条件。

4.完全连接

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

注：MySQL lacks support for FULL OUTER JOIN。

5.交叉连接

使用ON子句指定两个表的连接条件

sql-1

mysql> select * from tb_order as a cross join tb_person as b on a.personId=b.personId;

+---------+---------+----------+----------+-----------+------------+---------+------+

+---------+---------+----------+----------+-----------+------------+---------+------+

| 3 | 34567 | 1 | 1 | hh | aa | ee | dd |

| 4 | 45678 | 1 | 1 | hh | aa | ee | dd |

| 5 | 56789 | 2 | 2 | qq | ww | ee | rr |

| 1 | 12345 | 3 | 3 | yy | ii | kk | nn |

| 2 | 23456 | 3 | 3 | yy | ii | kk | nn |

+---------+---------+----------+----------+-----------+------------+---------+------+

5 rows in set

sql-2

mysql> select * from tb_order as a cross join tb_person as b where a.personId = b.personId;

+---------+---------+----------+----------+-----------+------------+---------+------+

+---------+---------+----------+----------+-----------+------------+---------+------+

| 3 | 34567 | 1 | 1 | hh | aa | ee | dd |

| 4 | 45678 | 1 | 1 | hh | aa | ee | dd |

| 5 | 56789 | 2 | 2 | qq | ww | ee | rr |

| 1 | 12345 | 3 | 3 | yy | ii | kk | nn |

| 2 | 23456 | 3 | 3 | yy | ii | kk | nn |

+---------+---------+----------+----------+-----------+------------+---------+------+

5 rows in set

交叉连接时使用where语句过滤结果。

====END====

杨松圣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql链接网页进行查询_MySQL连接查询 - xxggy的个人页面 - OSCHINA - 中文开源技术交流社区...

MySQL连接查询MySql表连接查询，两个表之间的连接查tb_order表orderIdorderNopersonId11234532234563334567144567815567892tb_person表personIdlast_namefirst_nameaddresscity1hhaaeedd2qqwweerr3yyiikknn4trrthgmn5sdfsdsdds1.内连接内连接(in...
复制链接

扫一扫