MySQL连接查询
MySql表连接查询,两个表之间的连接查
tb_order表
orderId | orderNo | personId |
1 | 12345 | 3 |
2 | 23456 | 3 |
3 | 34567 | 1 |
4 | 45678 | 1 |
5 | 56789 | 2 |
tb_person表
personId | last_name | first_name | address | city |
1 | hh | aa | ee | dd |
2 | ww | ee | rr | |
3 | yy | ii | kk | nn |
4 | tr | rt | hg | mn |
5 | sdf | sd | sd | ds |
1.内连接
内连接(inner join)是应用程序中用的普遍的"连接"操作,它一般都是默认连接类型。
mysql> select a.*,b.* from tb_person as a inner join tb_order as b;+----------+-----------+------------+---------+------+---------+---------+----------+| personId | last_name | first_name | address | city | orderId | orderNo | personId |+----------+-----------+------------+---------+------+---------+---------+----------+| 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;+----------+-----------+------------+---------+------+---------+---------+----------+| personId | last_name | first_name | address | city | orderId | orderNo | 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;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 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;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 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;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 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;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 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;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 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====