9.1 union联合查询
UNION关键字用于连接两个以上的SELECT语句的结果组合到一个结果集合中
使用union ，mysql会把结果集中重复的记录删掉
使用union all，mysql会把所有的记录返回，且效率高于union
mysql> select * from stu; +-------+-------+ | stuId | name | +-------+-------+ | 4 | frank | | 5 | Tom | +-------+-------+ 2 rows in set (0.01 sec) mysql> mysql> select * from eatery; +----+----------+-------+ | id | money | stuId | +----+----------+-------+ | 0 | 999.0000 | 5 | | 1 | 20.5000 | NULL | | 2 | 78.6000 | 4 | | 3 | 99.9000 | NULL | | 4 | 748.4000 | 4 | | 5 | 748.4000 | NULL | | 6 | 999.0000 | 5 | | 7 | 345.0000 | 4 | +----+----------+-------+ 8 rows in set (0.01 sec) mysql> select stuId from eatery union all select stuId from stu; +-------+ | stuId | +-------+ | NULL | | NULL | | NULL | | 4 | | 4 | | 4 | | 5 | | 5 | | 4 | | 5 | +-------+ 10 rows in set (0.00 sec) mysql> select stuId from eatery union select stuId from stu; +-------+ | stuId | +-------+ | NULL | | 4 | | 5 | +-------+ 3 rows in set (0.00 sec)
9.2 inner join内联查询
inner join与 join 是相同的
图片引用自菜鸟教程
mysql> select * from stu; +-------+-------+ | stuId | name | +-------+-------+ | 4 | frank | | 5 | Tom | +-------+-------+ 2 rows in set (0.01 sec) mysql> select * from eatery; +----+----------+-------+ | id | money | stuId | +----+----------+-------+ | 0 | 999.0000 | 5 | | 1 | 20.5000 | NULL | | 2 | 78.6000 | 4 | | 3 | 99.9000 | NULL | | 4 | 748.4000 | 4 | | 5 | 748.4000 | NULL | | 6 | 999.0000 | 5 | | 7 | 345.0000 | 4 | +----+----------+-------+ 8 rows in set (0.01 sec) mysql> select eatery.money, stu.name from eatery inner join stu on eatery.stuId = stu.stuId; +----------+-------+ | money | name | +----------+-------+ | 999.0000 | Tom | | 78.6000 | frank | | 748.4000 | frank | | 999.0000 | Tom | | 345.0000 | frank | +----------+-------+ 5 rows in set (0.00 sec) mysql> select id,name,money from eatery inner join stu on stu.stuId=eatery.stuId; +----+-------+----------+ | id | name | money | +----+-------+----------+ | 0 | Tom | 999.0000 | | 2 | frank | 78.6000 | | 4 | frank | 748.4000 | | 6 | Tom | 999.0000 | | 7 | frank | 345.0000 | +----+-------+----------+ 5 rows in set (0.00 sec)
9.3 inner join注意事项
必须指定id，例如eatery.stuId = stu.stuId
如果还要连接两张以上的表，可以继续添加Inner join，但是不建议这样操作
9.4 left join 外连接
在某些数据库中，left out称为left outer join
图片引用自菜鸟教程
9.5 rigth join 外连接
在某些数据库中，left out 称为right outer join
图片引用自菜鸟教程
9.6 cross join 交叉连接
cross join 返回笛卡尔积，使用时须谨慎，可能产生非常大的表
mysql> select * from eatery cross join stu; +----+----------+-------+-------+-------+ | id | money | stuId | stuId | name | +----+----------+-------+-------+-------+ | 0 | 999.0000 | 5 | 4 | frank | | 0 | 999.0000 | 5 | 5 | Tom | | 1 | 20.5000 | NULL | 4 | frank | | 1 | 20.5000 | NULL | 5 | Tom | | 2 | 78.6000 | 4 | 4 | frank | | 2 | 78.6000 | 4 | 5 | Tom | | 3 | 99.9000 | NULL | 4 | frank | | 3 | 99.9000 | NULL | 5 | Tom | | 4 | 748.4000 | 4 | 4 | frank | | 4 | 748.4000 | 4 | 5 | Tom | | 5 | 748.4000 | NULL | 4 | frank | | 5 | 748.4000 | NULL | 5 | Tom | | 6 | 999.0000 | 5 | 4 | frank | | 6 | 999.0000 | 5 | 5 | Tom | | 7 | 345.0000 | 4 | 4 | frank | | 7 | 345.0000 | 4 | 5 | Tom | +----+----------+-------+-------+-------+ 16 rows in set (0.00 sec)
可以通过交叉连接实现内连接
mysql> select id,name,money from eatery inner join stu on stu.stuId=eatery.stuId; +----+-------+----------+ | id | name | money | +----+-------+----------+ | 0 | Tom | 999.0000 | | 2 | frank | 78.6000 | | 4 | frank | 748.4000 | | 6 | Tom | 999.0000 | | 7 | frank | 345.0000 | +----+-------+----------+ 5 rows in set (0.00 sec) mysql> select eatery.id,name,eatery.money from eatery cross join stu where eatery.stuId = stu.stuId; +----+-------+----------+ | id | name | money | +----+-------+----------+ | 0 | Tom | 999.0000 | | 2 | frank | 78.6000 | | 4 | frank | 748.4000 | | 6 | Tom | 999.0000 | | 7 | frank | 345.0000 | +----+-------+----------+ 5 rows in set (0.00 sec)
9.7 natural join
自然连接是在两张表中寻找那些数据类型和列名都相同的字段
自动地将他们连接起来，并返回所有符合条件按的结果
mysql> select * from t_1; +----------+----------+ | number_1 | number_2 | +----------+----------+ | 2.1 | 2.23 | | 2.9 | 2.78 | | 3.0 | 3.00 | +----------+----------+ 3 rows in set (0.00 sec) mysql> select * from t_2; +-----------------------+ | number | +-----------------------+ | 9.1111111111111100000 | +-----------------------+ 1 row in set (0.00 sec) mysql> select * from t_1 natural join t_2; +----------+----------+-----------------------+ | number_1 | number_2 | number | +----------+----------+-----------------------+ | 2.1 | 2.23 | 9.1111111111111100000 | | 2.9 | 2.78 | 9.1111111111111100000 | | 3.0 | 3.00 | 9.1111111111111100000 | +----------+----------+-----------------------+ 3 rows in set (0.00 sec) mysql> select * from eatery natural join stu; +-------+----+----------+-------+ | stuId | id | money | name | +-------+----+----------+-------+ | 5 | 0 | 999.0000 | Tom | | 4 | 2 | 78.6000 | frank | | 4 | 4 | 748.4000 | frank | | 5 | 6 | 999.0000 | Tom | | 4 | 7 | 345.0000 | frank | +-------+----+----------+-------
9.8 无公共同名字段的自然返回笛卡尔积
mysql> select * from t_1 natural join t_5; +----------+----------+--------+ | number_1 | number_2 | gender | +----------+----------+--------+ | 2.1 | 2.23 | man | | 2.9 | 2.78 | man | | 3.0 | 3.00 | man | | 2.1 | 2.23 | woman | | 2.9 | 2.78 | woman | | 3.0 | 3.00 | woman | +----------+----------+--------+ 6 rows in set (0.00 sec)
9.9 using
using相当于join操作中的on
根据id字段关联，以下命令的等价的，注意使用using时前面不要加on
on eatery.stuId = stu.stuId
using(stuId)
mysql> select id,money,name from eatery inner join stu on eatery.stuId = stu.stuId; +----+----------+-------+ | id | money | name | +----+----------+-------+ | 0 | 999.0000 | Tom | | 2 | 78.6000 | frank | | 4 | 748.4000 | frank | | 6 | 999.0000 | Tom | | 7 | 345.0000 | frank | +----+----------+-------+ 5 rows in set (0.00 sec) mysql> select id,money,name from eatery inner join stu using(stuId); +----+----------+-------+ | id | money | name | +----+----------+-------+ | 0 | 999.0000 | Tom | | 2 | 78.6000 | frank | | 4 | 748.4000 | frank | | 6 | 999.0000 | Tom | | 7 | 345.0000 | frank | +----+----------+-------+ 5 rows in set (0.00 sec)
9.10 哪一个实用？
看业务需求，实际情况是把查询条件的公共字段写全，用inner join增强可读性
