联结(join) : 一种机制,在数据检索查询中执行,将表与表之间关联起来
说到将表关联起来,就有了主外键 ,为啥会有来,因为只用一个表很难做成一个系统,当然也可以,这样的话这个表肯定不符合建表的三大范式,所以一般都是尽量满足三大范式,让数据库不冗余,表也很清爽,就向软件中的高内聚低耦合,不容易崩,还有就是当你走了,下一个在来他的理解成本也会降低,(其实各有各的好处吧,毕竟查一个表是最简单的),因此分开后,表与表之间的关联就要靠主外键,
外键(foreign key): 外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
主键(primary key): 能够唯一区分表中每一个行的一个列
像 供应商与产品,他们之间肯定会靠主外键互相关联,如果放在一个表里
1、同一个供应商生产的每个产品它的供应商信息都是相同的,重复的浪费时间也浪费空间
2、如果供应商的信息改变,之前的存储的商品也要改,不够灵活,容错性小,维护成本高
3、如果有重复数据。很难保证每次输入该数据的方式都相同, 使用不方便,数据可能重复,出现不一致
还有就是,关系型数据库设计的基础,就是避免出现重复数据,关系表的的设计就是把信息分解成多个表,一类数据一个表,各表通过某些常用的值相互关联,关系数据可以有效的存储和方便地处理,因此关系数据库的可伸缩性远比非关系数据库要好。
可伸缩性(scale):能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)
如上所述,分解数据为多个表可以更有效的存储,更方便地处理,并且具有更大的可伸缩性,那么如何使用一条语句进行查询这多表的数据来,你可能想到了之前将的子查询,但是本篇的文章可是联结,所以这种用来在一条select 语句中关联表的机制称为联结,在运行时要使用正确的行(常用的就是主外键)
注意:
1、联结不是物理实体,他在实际数据表中并不存在,联结由mysql根据需要建立,存在于查询的执行中
2、要维护引用的完整性,如商品表的供应商要是供应商表中的数据
1、简单使用
用到的表,供应商与商品表时一对多的关系,一个供应商有多条产品记录
使用联结查询时 两个表使用 ,号分隔,查询使用完全限定将两个表的共有属性作为查询条件
--查询vendors,products 两个表,将两个表中vend_id 相同的检索出来
select vend_name,prod_name from vendors,products where products.vend_id = vendors.vend_id order by vend_name,prod_name;
注意:在引用的列中可能出现二义性,必须使用完全限定,(表名,列名),没有使用会报错
ERROR 1052 (23000): Column 'vend_id' in where clause is ambiguous
还有就是要注意:检索的字段也不要具有二义性,不然也会报错,
就像下面一样,vend_id 是两个表的共有字段,你不指定他就蒙了
!!!
为啥要用where 子句建立连接:在使用一条SQL 语句连接几个表时,相应的关系是在运行中构造的,在数据库表的定义中不存在能指示MySQL 如何对表进行连接东西,你必须要自己做这件事,在联结两个表时,你实际上做的是将第一个表中的每一行与第二个表中的每一行配对,where 子句作为过滤条件,它指包含那些匹配给定条件(即联结条件)的行。没有where 子句,第一个表中的每个行将与第二个表中的每个行配对,而不管他们是否可以配对在一起。这个现象也叫笛卡尔积(cartesian product):由没有联结条件的表返回的结果为笛卡尔积,检索出的行的数目将是第一个表中的行数乘以第二表中的行数。
(小知识:叉联结(coss join):笛卡尔积的一种联结类型。)
如下图,一共有 6*14=84 行
select vendors.vend_id,vendors.vend_name,products.prod_name from vendors,products order by vend_name,prod_name;
所以,应保证所有联结都有where 子句否则mysql 将返回比想要的数据多的多的数据,也要保证where 子句正确不然返回的也不对
如果你要调皮,就是不想用where 子句那么可以使用下面这种 内联结
2、内部联结
等值联结(equijion):基于两个表之间的相等测试,也称为内部联结。就是一种特殊的语法实现上面的要求
关键字: inner join , on
--select 字段 from 表1 inner join 表2 on 表1.共有字段 = 表2.共有字段
select vendors.vend_id,vendors.vend_name,products.prod_name from vendors inner join products on vendors.vend_id = products.vend_id order by vend_name;
注意:ANSI SQL 规范首先 INNER JOIN(内部联结) 语法。因为这样专业,而且对性能有所提升,而且不能浪费一个语法
方法有多种,那么你有没有想到,用之前学到的 where 子句进行实现呢?
是不觉得,就这,直接列出是不是不好呀,哎呀呀感觉有点长了,,,,,,,
反正就是越复杂的sql 查询的效率就会相应的变慢,你觉得书写方便查询也会比较慢向 * ,like 语句,至于count(*) 感觉还好,貌似是mysql 有所优化。
3、联结多个表
SQL 对一条select 语句中可以联结的表的数目没有限制。而且,创建联结的基本规则也基本相同,先列出表然后定义表之间的关系
来个小例子
用到的三个表
将三个表联结起来 prodcuts 表就是中间粘合剂,注意sql 语法就行
--查找这三个表中符合vendors.vend_id = products.vend_id and products.prod_id = orderitems.prod_id 条件的行
select vendors.vend_id,vend_name,products.prod_id,products.vend_id,orderitems.prod_id,orderitems.item_price from orderitems,products,vendors where vendors.vend_id = products.vend_id and products.prod_id = orderitems.prod_id order by vendors.vend_id;
注意:mysql 在运行是关联指定的每个表以处理联结,这种处理是非常消耗资源的,所以不要联结过多的表,表越多性能下降越厉害
是不是你又想到了什么,之前的where 子查询的多表是不是也可以使用这里面的联结实现;
select cust_name,cust_contact fromcustomers,orders,orderitemswhere customers.cust_id = orders.cust_id and orderitems.order_num =orders.order_numand prod_id = 'TNT2';
注意:
1、完成一件事,不只有一个方法。
2、很少有绝对正确或绝对错误的方法。社会是灰色的
3、性能可能会受操作类型、表中数据量、是否存在索引、键以及其他一些条件方法
4、建议可以对不同的选择机制进行实验,找出相对适合具体条件的方法,不怕困难才能顽强生长,长势良好
5、联结是SQL 中最重要最强大的特性,想要随手写出优美的sql,还需要对关系性数据库设计有所了解