bitscn.com
mysql学习足迹记录13--联结表--inner join...on
学数据库联结表这一节内容几乎吐血，看书上的介绍和百度到的内容，总感到一脸茫然.....也许是我理解能力太差了吧。但我是一个不会轻易放弃的人，研究了半小时，终于搞明白了联结的原理，自我总结如下：关键在于“select语句，如果没有where子句限定，将返回两个表的笛卡尔积.”
example: 表一： kemu+-----+--------+| kno | kname |+-----+--------+| b | yuwen || b1 | shuxue |+-----+--------+表二： chenji；+------+------+---------+| sno | kno | chengji |+------+------+---------+| 1 | a | 10 || 2 | a1 | 20 |+------+------+---------+2 rows in set (0.00 sec)*重点：select语句，如果没有where子句限定，将返回两个表的笛卡尔积.mysql> select * from kemu,chenji;+-----+--------+------+------+---------+| kno | kname | sno | kno | chengji |+-----+--------+------+------+---------+| b | yuwen | 1 | a | 10 || b1 | shuxue | 1 | a | 10 || b | yuwen | 2 | a1 | 20 || b1 | shuxue | 2 | a1 | 20 |+-----+--------+------+------+---------+4 rows in set (0.00 sec)
tips:select 语句后面的所有表将会合成一个大表，所以，select后所跟的各种子句就相当于在一个表中筛选数据了。
1.外键：某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系
2. 联结不是物理实体，它在实际的数据库表中不存在，它存在与查询执行当中
3.创建联结
example: mysql> select vend_name,prod_name,prod_price -> from vendors,products -> where vendors.vend_id = products.vend_id #where子句指示mysql匹配vendors表的 -> order by vend_name,prod_name; #vend_id和products表中的vend_id.+-------------+----------------+------------+| vend_name | prod_name | prod_price |+-------------+----------------+------------+| acme | bird seed | 10.00 || acme | carrots | 2.50 || acme | detonator | 13.00 || acme | safe | 50.00 || acme | sling | 4.49 || acme | tnt (1 stick) | 2.50 || acme | tnt (5 sticks) | 10.00 || anvils r us | .5 ton anvil | 5.99 || anvils r us | 1 ton anvil | 9.99 || anvils r us | 2 ton anvil | 14.99 || jet set | jetpack 1000 | 35.00 || jet set | jetpack 2000 | 55.00 || lt supplies | fuses | 3.42 || lt supplies | oil can | 8.99 |+-------------+----------------+------------+14 rows in set (0.01 sec)
tips:
*where联结两个表时，实际是将第一个表中的每一行和第二个表中的每一行配对.并过滤不符合条件的行.
4.内部联结inner join .... on
example: mysql> select vend_name,prod_name,prod_price -> from vendors inner join products #使用明确的联结语法能狗确保不会忘记联结条件 -> on vendors.vend_id = products.vend_id #联结条件用特定的on子句， -> order by vend_name,prod_name; #传递的实际条件与传递给where的相同+-------------+----------------+------------+| vend_name | prod_name | prod_price |+-------------+----------------+------------+| acme | bird seed | 10.00 || acme | carrots | 2.50 || acme | detonator | 13.00 || acme | safe | 50.00 || acme | sling | 4.49 || acme | tnt (1 stick) | 2.50 || acme | tnt (5 sticks) | 10.00 || anvils r us | .5 ton anvil | 5.99 || anvils r us | 1 ton anvil | 9.99 || anvils r us | 2 ton anvil | 14.99 || jet set | jetpack 1000 | 35.00 || jet set | jetpack 2000 | 55.00 || lt supplies | fuses | 3.42 || lt supplies | oil can | 8.99 |+-------------+----------------+------------+14 rows in set (0.00 sec)
5.联结多个表
*sql对一条select语句中可以联结的表的数目没有限制。
example: mysql> select prod_name,vend_name,prod_price,quantity -> from orderitems,products,vendors -> where products.vend_id=vendors.vend_id -> and orderitems.prod_id=products.prod_id -> and order_num=20005;+----------------+-------------+------------+----------+| prod_name | vend_name | prod_price | quantity |+----------------+-------------+------------+----------+| .5 ton anvil | anvils r us | 5.99 | 10 || 1 ton anvil | anvils r us | 9.99 | 3 || tnt (5 sticks) | acme | 10.00 | 5 || bird seed | acme | 10.00 | 1 |+----------------+-------------+------------+----------+4 rows in set (0.00 sec)
bitscn.com