锐客网

  1. 首页 > 睿知 > it技术 > >

mysql等值怎么连接 mysql等值查询

IT技术连接

mysql等值连接索引的问题应该是整条语句共返回一条记录，除非a表中只有一个id出现于b表里，否则a表不可能只扫描一行记录 。
b表扫描了八十万行很正常，因为语句除了对等连接限制外并没有其它筛选条件，符合连接条件的记录都得检索出来，b表对等的a_id越多扫描的行数越多，b表如果所有的a_id都与a表对等那么即使利用了索引也会扫描所有的行 。其实题主的语句已经优化了 ， 如果没有利用到索引I/O次数会更多 。如果题主需要进一步减少扫描行数可以考虑在连接限制的基础上再增加筛选条件，例如...and b.a_id1000等，这样就可以利用索引进一步减少对表记录行的扫描数 。
如何利用MySQL实现等值连接 ， 左连接和右连接三种连接的语法
为便于更多的技友快速读懂、理解，我们只讨论2张表对象进行连接操作的情况，大于2张表对象进行的连接操作原理也是一样的 。
【mysql等值怎么连接 mysql等值查询】1.左连接（LEFT JOIN ）
SELECT M.columnname……,N.* columnname…..
FROM left_table M LEFT JOIN right_table N ON M.columnname_join=N.columnname_join AND N.columnname=XXX
WHERE M.columnname=XXX…..
MySQL简单介绍——换个角度认识MySQL1、InnoDB存储引擎
Mysql版本=5.5 默认的存储引擎 ， MySQL推荐使用的存储引擎 。支持事务，行级锁定，外键约束 。事务安全型存储引擎 。更加注重数据的完整性和安全性 。
存储格式 : 数据 ， 索引集中存储，存储于同一个表空间文件中 。
InnoDB的行锁模式及其加锁方法： InnoDB中有以下两种类型的行锁：共享锁（读锁： 允许事务对一条行数据进行读?。┖?互斥锁（写锁： 允许事务对一条行数据进行删除或更新），对于update，insert，delete语句，InnoDB会自动给设计的数据集加互斥锁，对于普通的select语句，InnoDB不会加任何锁 。
InnoDB行锁的实现方式： InnoDB行锁是通过给索引上的索引项加锁来实现的 ， 如果没有索引 ， InnoDB将通过隐藏的聚簇索引来对记录加锁 。InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样 。
（1）在不通过索引条件查询时 ， InnoDB会锁定表中的所有记录 。
（2）Mysql的行锁是针对索引加的锁，不是针对记录加的锁 ， 所以虽然是访问不同行的记录 ， 但是如果使用相同的索引键，是会出现冲突的 。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，但都是通过行锁来对数据加锁 。
优点：
1、支持事务处理、ACID事务特性；
2、实现了SQL标准的四种隔离级别（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（Isolation ）和持续性（Durability ））；
3、支持行级锁和外键约束；
4、可以利用事务日志进行数据恢复 。
5、锁级别为行锁，行锁优点是适用于高并发的频繁表修改，高并发是性能优于 MyISAM 。缺点是系统消耗较大 。
6、索引不仅缓存自身，也缓存数据，相比 MyISAM 需要更大的内存 。
缺点：
因为它没有保存表的行数，当使用COUNT统计时会扫描全表 。
使用场景：
(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁 ， 并且表锁定的机会比较大的情况 。
2、 MyISAM存储引擎
MySQL= 5.5 MySQL默认的存储引擎 。ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统 。擅长与处理，高速读与写 。
功能：
（1）支持数据压缩存储,但压缩后的表变成了只读表 ， 不可写；如果需要更新数据，则需要先解压后更新 。
（2）支持表级锁定，不支持高并发；
（3）支持并发插入 。写操作中的插入操作 ， 不会阻塞读操作（其他操作）；
优点：
1.高性能读?。?
2.因为它保存了表的行数 ， 当使用COUNT统计时不会扫描全表；
缺点：
1、锁级别为表锁，表锁优点是开销?。?加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低 ， 这个引擎适合查询为主的业务 。
2、此引擎不支持事务 ， 也不支持外键 。
3、INSERT和UPDATE操作需要锁定整个表；
使用场景：
(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务 。
InnoDB和MyISAM一些细节上的差别：
1、InnoDB不支持FULLTEXT类型的索引，MySQL5.6之后已经支持（实验性） 。
2、InnoDB中不保存表的 具体行数 ， 也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可 。注意的是，当count()语句包含 where条件时，两种表的操作是一样的 。
3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引 。
4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除 。
5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表 ， 但是对于使用的额外的InnoDB特性（例如外键）的表不适用 。
6、另外，InnoDB表的行锁也不是绝对的 ， 如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表 。
1．索引概述
利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引 。索引的关键字一定是排序的 。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法 。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢 。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源 。
2．索引种类
从索引的定义方式和用途中来看：主键索引 ， 唯一索引，普通索引 ， 全文索引 。
无论任何类型 ， 都是通过建立关键字与位置的对应关系来实现的 。索引是通过关键字找对应的记录的地址 。
以上类型的差异：对索引关键字的要求不同 。
关键字：记录的部分数据（某个字段 ， 某些字段，某个字段的一部分） 。
普通索引,index：对关键字没有要求 。
唯一索引,unique index：要求关键字不能重复 。同时增加唯一约束 。
主键索引,primary key：要求关键字不能重复，也不能为NULL 。同时增加主键约束 。
全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词 。
PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个 。主键索引肯定是唯一索引，但唯一索引不一定是主键索引 。
3.索引原则
如果索引不遵循使用原则，则可能导致索引无效 。
（1）列独立
如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧 。否则索引不会用到索引, 例如这条sql就不会用到索引:select * from A where id 1=10;
（2）左原则
Like：匹配模式必须要左边确定不能以通配符开头 。例如:select * from A where name like '%小明%' ，不会用到索引，而select * from A where name like '小明%' 就可以用到索引（name字段有建立索引），如果业务上需要用到'%小明%'这种方式,有两种方法:1.可以考虑全文索引,但mysql的全文索引不支持中文；2.只查询索引列或主键列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 这三种情况都会用到name的索引;
复合索引：一个索引关联多个字段，仅仅针对左边字段有效果，添加复合索引时 ， 第一个字段很重要 ， 只有包含第一个字段作为查询条件的情况才会使用复合索引(必须用到建索引时选择的第一个字段作为查询条件,其他字段的顺序无关),而且查询条件只能出现and拼接,不能用or,否则则无法使用索引.
（3）OR的使用
必须要保证 OR 两端的条件都存在可以用的索引 ， 该查询才可以使用索引 。
（4）MySQL智能选择
即使满足了上面说原则，MySQL也能弃用索引，例如：select * from A where id1;这里弃用索引的主要原因：查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销 ， 还要大 。
4.索引的使用场景
（1）索引检索：检索数据时使用索引 。
（2）索引排序: 如果order by 排序需要的字段上存在索引 ， 则可能使用到索引 。
（3）索引覆盖: 索引拥有的关键字内容 ， 覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可 。覆盖就是直接在索引区获取内容，而不需要在数据区获取 。例如: select name from A where name like '小明%';
建立索引索引时，不能仅仅考虑where检索 ， 同时考虑其他的使用场景 。（在所有的where字段上增加索引，就是不合理的）
5.前缀索引
前缀索引是建立索引关键字一种方案 。通常会使用字段的整体作为索引关键字 。有时，即使使用字段前部分数据，也可以去识别某些记录 。就比如一个班级里，我要找王xx ， 假如姓王的只有1个人，那么就可以建一个关键字为'王'的前缀索引 。语法：Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引 。
6.索引失效
（1） 应尽量避免在 where 子句中使用 != 或操作符，否则将引擎放弃使用索引而进行全表扫描；
（2） 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引 ， 一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；
（3） 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；
（4）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描；如select id from t where num/2 = 100；
（5） 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描；如：select id from t where substring(name,1,3) = ’abc’ ；
（6）应尽量避免在where子句中对字段进行类型转换，这将导致引擎放弃使用索引而进行全表扫描； 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,如select id from t where id = 1；如果id字段在表设计中是varchar类型 ， 那么即使id列上存的是数字，在查询时也一定要用varchar去匹配，sql应改为select id from t where id = '1'；
（7）应尽量避免在where子句中单独引用复合索引里非第一位置的索引；
join 的两种算法：BNL 和 NLJ
NLJ（Nested Loop Join）嵌套循环算法；以如下 SQL 为例：
select * from t1 join t2 on t1.a=t2.a
SQL 执行时内部流程是这样的：
1. 先从 t1（假设这里 t1 被选为驱动表）中取出一行数据 X；
2. 从 X 中取出关联字段 a 值，去 t2 中进行查找，满足条件的行取出；
3. 重复1、2步骤，直到表 t1 最后一行循环结束 。
这就是一个嵌套循环的过程，如果在被驱动表上查找数据时可以使用索引，总的对比计算次数等于驱动表满足 where 条件的行数 。假设这里 t1、t2都是1万行，则只需要 1万次计算 ， 这里用到的是Index Nested-Loops Join（INLJ ， 基于索引的嵌套循环联接） 。
如果 t1、t2 的 a 字段都没有索引，还按照上述的嵌套循环流程查找数据呢？每次在被驱动表上查找数据时都是一次全表扫描，要做1万次全表扫描，扫描行数等于 1万 1万*1万，这个效率很低 ， 如果表行数更多，扫描行数动辄几百亿，所以优化器肯定不会使用这样的算法，而是选择 BNL 算法；
BNLJ（Block Nested Loop Join）块嵌套循环算法；
1. 把 t1 表（假设这里 t1 被选为驱动表）满足条件的数据全部取出放到线程的 join buffer 中；
2. 每次取 t2 表一行数据，去 joinbuffer 中进行查找，满足条件的行取出，直到表 t2 最后一行循环结束 。
这个算法下，执行计划的 Extra 中会出现 Using join buffer(Block Nested Loop) ， t1、t2 都做了一次全表扫描 ， 总的扫描行数等于 1万 1万 。但是由于 joinbuffer 维护的是一个无序数组 ， 每次在 joinbuffer 中查找都要遍历所有行，总的内存计算次数等于1万*1万 。另外如果 joinbuffer 不够大放不下驱动表的数据，则要分多次执行上面的流程，会导致被驱动表也做多次全表扫描 。
BNLJ相对于NLJ的优点在于，驱动层可以先将部分数据加载进buffer，这种方法的直接影响就是将大大减少内层循环的次数，提高join的效率 。
例如：
如果内层循环有100条记录，外层循环也有100条记录，这样的话，每次外层循环先将10条记录放到buffer中，内层循环的100条记录每条与这个buffer中的10条记录进行匹配 ， 只需要匹配内层循环总记录数次即可结束一次循环（在这里，即只需要匹配100次即可结束），然后将匹配成功的记录连接后放入结果集中，接着，外层循环继续向buffer中放入10条记录 ， 同理进行匹配，并将成功的记录连接后放入结果集 。后续循环以此类推 ， 直到循环结束，将结果集发给client为止 。
可以发现，若用NLJ，则需要100 * 100次才可结束，BNLJ则需要100 / block_size * 100 = 10 * 100次就可结束,大大减少了循环次数 。
JOIN 按照功能大致分为如下三类：
JOIN、STRAIGHT_JOIN、INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录 。
LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录 。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录 ， 即是左表（table1）并无匹配对应记录 。
注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join 。
mysql 多表连接查询方式，因为mysql只支持NLJ算法,所以如果是小表驱动大表则效率更高；反之则效率下降；因此mysql对内连接或等值连接的方式做了一个优化,会去判断join表的数据行大小,然后取数据行小的表为驱动表 。
INNER JOIN、JOIN、WHERE等值连接和STRAIGHT_JOIN都能表示内连接，那平时如何选择呢？一般情况下用INNER JOIN、JOIN或者WHERE等值连接，因为MySQL 会按照"小表驱动大表的策略"进行优化 。当出现需要排序时 ， 才考虑用STRAIGHT_JOIN指定某张表为驱动表 。
两表JOIN优化
a.当无order by条件时 ， 根据实际情况 ， 使用left/right/inner join即可，根据explain优化 ；
b.当有order by条件时，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解释语句；
1）如果第一行的驱动表为a ， 则效率会非常高，无需优化；
2）否则，因为只能对驱动表字段直接排序的缘故 ， 会出现using temporary ， 所以此时需要使用STRAIGHT_JOIN明确a为驱动表，来达到使用a.col上index的优化目的；或者使用left join且Where条件中不含b的过滤条件，此时的结果集为a的全集 ， 而STRAIGHT_JOIN为inner join且使用a作为驱动表 。注：使用STRAIGHT_JOIN虽然不会using temporary，但也不是一定就能提高效率，如果a表数据远远超过b表，那么有可能使用STRAIGHT_JOIN时比原来的sql效率更低，所以怎么使用STRAIGHT_JOIN，还是要视情况而定 。
在使用left join（或right join）时 ， 应该清楚的知道以下几点：
(1). on与 where的执行顺序
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行 。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用 。仅在匹配阶段完成以后，WHERE 子句条件才会被使用 。它将从匹配阶段产生的数据中检索过滤 。
所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行 。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的数据不满足ON后面的条件,也会在结果集拼接一条为NULL的数据行,但WHERE后面的条件不一样,右表不满足WHERE的条件,左表关联的数据也会被过滤掉 。
(3).尽量避免子查询，而用join
往往性能这玩意儿 ， 更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询 。
（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in：select id from t where num between 1 and 3很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
（2）Update 语句，如果只更改1、2个字段，不要Update全部字段 ， 否则频繁调用会引起明显的性能消耗，同时带来大量日志 。
（3）join语句,MySQL里面的join是用小表去驱动大表,而由于MySQL join实现的原理就是做循环，比如left join就是对左边的数据进行循环去驱动右边的表,左边有m条记录匹配,右边有n条记录那么就是做m次循环,每次扫描n行数据,总扫面行数是m*n行数据 。左边返回的结果集的大小就决定了循环的次数,故单纯的用小表去驱动大表不一定的正确的,小表的结果集可能也大于大表的结果集,所以写join的时候尽可能的先估计两张表的可能结果集,用小结果集去驱动大结果集.值得注意的是在使用left/right join的时候,从表的条件应写在on之后,主表应写在where之后.否则MySQL会当作普通的连表查询；
（4）select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的；
（5）select * from t 这种语句要尽量避免，使用具体的字段代替* ， 更有实际意义，需要什么字段就返回什么字段；
（6）数据量大的情况下，limit要慎用，因为使用limit m，n方式分页时，mysql每次都是查询前m n条 ， 然后舍弃前m条，所以m越大，偏移量越大 ， 性能就越差 。比如：select * from A limit 1000000,20这钟，查询效率就会非常低，当分页的页数大于一定的数量之后 ， 就可以换种方式来分页：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;
MySQL连接5种方式SQL中mysql等值怎么连接的 join 可以根据相应条件把指定的表给结合起来并将数据返回 。
内连接是基于连接谓词将俩张表（如A和B）的列组合到一起产生新的结果表mysql等值怎么连接 ， 在表中存在至少一个匹配时mysql等值怎么连接，INNER JOIN 关键字返回行
左外连接Left join关键字会从左表那里返回所有的行，即使是在右表中没有匹配到的行
右外连接关键字Right join会从右表那里返回所有的行 ， 即使是在左表中没有匹配到的行
全连接的关键字Full join，只要其中某个表中存在匹配 ， Full join 就会返回行
交叉连接一般使用的比较少，交叉连接又称笛卡尔连接或者叉乘连接 ， 如果，A和B是俩个集合，他们的交叉连接就是A*B
MySQL连接查询Hellomysql等值怎么连接，写的语言格式有些丑
练习题目：
3、多表连接（等值连接）
①案例1：查询员工名、部门名
②为表起别名
#③添加筛选条件
#案例：查询工资5000的工种名和员工名、工资
④添加分组和筛选
#01案例：查询每个部门的员工个数和部门名
⑤排序
#01案例：查询每个部门的员工个数和部门名
⑥三表连接
#案例：查询员工名、部门名和所在城市
4、多表连接（等值连接）练习
传统模式的多表连接
1.显示所有员工的姓名mysql等值怎么连接，部门号和部门名称 。
2.查询90号部门员工的job_id和90号部门的location_id
3.选择所有有奖金的员工的last_name, department_name , location_id , city
-----------三表连查
4.选择city在Toronto工作的员工的
last_name, job_id , department_id , department_name-----------三表连查
5.查询每个工种、每个部门的部门名、工种名和最低工资-----------三表连查
6.查询每个国家下的部门个数大于2的国家编号
5、非等值查询
2.非等值连接
#案例1：查询员工的工资以及对应的工资级别
#案例2：查询名字中第三个字符为a ， 第五个字符为e的员工的工资以及对应的工资级别
6、内连接
#案例1 ：查询员工名、部门名
案例2：查询有奖金的员工名、部门名
案例3：查询城市名、员工名和部门名
9、练习
一、查询编号3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
#二、查询哪个城市没有部门
三、查询部门名为SAL或IT的员工信息
#四、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
/*
employeesEmp#managerMgr#
kochhar101king
100
*/
10、单行子查询
案例1：谁的工资比Abel高
①查询Abel的工资
②查询员工的信息满足工资①的结果
案例2：题目：返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资
①查询141的job_id
②查询143的salary
③查询姓名，job_id 和工资，满足job_id=①并且salary②
案例3：返回公司工资最少的员工的last_name,job_id和salary
①查询最低工资
②查询员工的last_name,job_id和salary满足 salary=①
案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门的最低工资
②查询每个部门的最低工资
③筛选最低工资①
11、多行子查询
二、多行子查询
案例1：返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门编号
②查询department_id满足①结果的员工姓名
案例2：返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员
工号、姓名、job_id 以及salary
①查询job_id为‘IT_PROG’部门工资
②返回其它部门中，工资any ①的结果
题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
#的员工号、姓名、job_id 以及salary
12、子查询练习题
#1.查询和Zlotkey相同部门的员工姓名和工资
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资 。
#①查询公司平均工资
② 查询工资①的员工的员工号，姓名和工资 。
#3.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
①查询各部门的平均工资
②查询员工的员工号, 姓名和工资，满足本部门并且工资①
4.查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
①查询姓名中包含字母u的员工的部门
② 部门=①的员工的员工号和姓名
5.查询在部门的location_id为1700的部门工作的员工的员工号
①查询loaction_id =1700的部门编号
② 查询员工号，满足部门号=①
#6.查询管理者是King的员工姓名和工资
①查询员工名是king的编号
#② 查询员工姓名和工资，领导的编号=①
#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名
①查询最高工资
②查询姓名 ， 工资=①
14、子查询巩固练习
# 1、查询工资最低的员工信息
#①查询公司的最低工资
②查询员工信息 ， 满足 salary=①
2.查询平均工资最低的部门信息
①查询每个部门的平均工资
②查询①结果中avg(salary)字段中的最低值
#③查询部门编号，满足平均工资=②结果
④查询部门信息，满足department_id=③
3*.查询平均工资最低的部门信息和该部门的平均工资
4.查询平均工资最高的 job信息
①查询每个job的平均工资
②查询①结果中的 avg(salary)的最高值
③查询每个工种的平均工资，满足 平均工资=②
④工种表和③连接，查询平均工资最高的 job信息
# 5.查询平均工资高于公司平均工资的部门有哪些?
#①查询公司的平均工资
②查询每个部门的平均工资，并且平均工资①
6.查询平均工资最高的部门的manager的详细信息:
①查询平均工资最高的部门编号
②查询部门编号=①的manager的详细信息
MySQL 中的各种 JOIN 本文主要介绍 SQL 标准中定义的各种连接的意义和区别mysql等值怎么连接，例如 ， 交叉连接（ CROSS JOIN ）、内连接（ INNER JOIN ）、外连接（ OUTER JOIN ）、自然连接（ NATURAL JOIN ）等 ， 并结合例子讲解这些连接在 MySQL 中的语法和表现 。
从网上的资料看 ， JOIN更多翻译为连接 ， 本文中凡是出现中文“连接”的地方都是指JOIN。
本文中用到的所有例子涉及两张表——customers用户表和orders订单表，其中订单表中的cust_id字段表示用户的唯一 ID，也就是用户表的主键cust_id。两张表的数据如下mysql等值怎么连接：
注：两张表都经过了简化，实际业务中这两张表肯定还包括其他字段 。
英文维基百科JOIN词条 对连接的定义如下：
翻译过来就是，“连接可以根据一张（自连接）或多张表中的共同值将这些表的列数据合并为一个新的结果集，标准 SQL 定义了五种连接：内连接、左外连接、右外连接、全外连接和交叉连接 。”
也就是说，连接是 SQL 标准中定义的一种组合多张表的方式，当然一张表自身也可以和自身组合，称为自连接 。连接后得到的结果集的每一列其实都来自用于连接的多张表，不同的连接类型只是区分了这些列具体从哪张表里来，列里填充的是什么数据 。
其实英文维基百科的JOIN词条已经把各种连接的类型解释地非常清楚了，非常值得去看一下 。
我们来看一下 SQL 标准中定义的各种连接类型 ， 理解各种连接最好的方法就是把需要连接的表想象成集合 ， 并画出可以反映集合的交与并的情况的图——韦恩图，例如下图就画出了 SQL 中定义的几种主要连接 。
请先仔细查看一下图中的内容，mysql等值怎么连接你可以从中归纳出几种连接类型呢？
虽然图中画了 7 种集合的交并情况，但是总结起来，主要是两种连接类型在起作用——内连接（ INNER JOIN ）和外连接（ OUTER JOIN ），其中外连接又分为了左外连接（ LEFT OUTER JOIN ）、右外连接（ RIGHT OUTER JOIN ）和全外连接（ FULL OUTER JOIN ） 。
下面先简单介绍一下 SQL 标准中各种连接的定义，然后在「MySQL 中的连接」一节再用例子来演示 MySQL 中支持的各种连接 。
连接既然是用来合并多张表的，那么要定义一个连接就必须指定需要连接的表，并指定可选的连接条件 。例如，一个典型的 SQL 连接语句如下：
我们用表 A 和表 B 指代需要连接的两张表 ， 经过 内连接 后得到的结果集 仅 包含所有满足 连接条件 的数据mysql等值怎么连接；而经过 外连接 后得到的数据集 不仅 包含满足 连接条件 的数据，还包含其他数据，具体的差别是：
在上面「SQL 标准定义的主要连接」一图中并没有列出交叉连接，交叉连接会对连接的两张表做笛卡尔积 ， 也就是连接后的数据集中的行是由第一张表中的每一行与第二张表中的每一行配对而成的，而不管它们 逻辑上 是否可以搭配在一起 。假设交叉连接的两张表分别有 m 和 n 行数据，那么交叉连接后的数据集就包含 m 乘以 n 行数据 。
连接根据连接的条件不同 ， 又可以区分为等值连接和非等值连接，「SQL 标准定义的主要连接」图中画出的连接的连接条件都是比较两个字段是否相等，它们都是等值连接 。
自然连接是等值连接的一种特殊形式，自然连接会自动选取需要连接的两张表中字段名相同的 所有 列做相等比较，而不需要再指定连接条件了 。
注：以下内容全部基于 MySQL 5.7 版本，所有例子只保证在 MySQL 5.7 上是可以正确执行的 。
MySQL 中支持的连接类型和关键字如下：
上面的表示方法摘自 MySQL 5.7 版本 官方文档，其中|表示两者皆可出现，[]表示的是可选的 ， {}表示的是必选的，例如NATURAL LEFT JOIN和NATURAL JOIN都是合法的 。
可以看到，除了全外连接（ FULL OUTER JOIN ）以外， MySQL 基本支持了 SQL 标准中定义的各种连接 。在 MySQL 中全外连接可以通过UNION合并的方式做到，当然前提是mysql等值怎么连接你知道自己为什么需要这么做 ， 具体参见： Full Out Join in MySQL。
MySQL 语法中还支持一个并不在 SQL 标准中的STRAIGHT_JOIN，它在 表现上 和内连接或者交叉连接并无区别 ， 只是一种给 MySQL 优化器的一个提示，STRAIGHT_JOIN提示 MySQL 按照语句中表的顺序加载表，只有在你明确清楚 MySQL 服务器对你的JOIN语句做了负优化的时候才可能用到它 。
还有一点需要说明的是，根据 官方文档 ，在 MySQL 中 ， JOIN 、 CROSS JOIN和INNER JOIN实现的功能是一致的，它们在语法上是等价的 。从语义上来说，CROSS JOIN特指无条件的连接（没有指定ON条件的JOIN或者没有指定WHERE连接条件的多表SELECT ），INNER JOIN特指有条件的连接（指定了ON条件的JOIN或者指定了WHERE连接条件的多表SELECT ） 。当然，如果你非要写... CROSS JOIN ... ON ...这样的语法，也是可以执行的 ， 虽然写着交叉连接，实际上执行的是内连接 。
下面我们就用例子来看一看 MySQL 中支持的几种连接的例子 。
注：下面的例子都没有指定ORDER BY子句，返回结果的顺序可能会因为数据插入顺序的不同而略有不同 。
MySQL 的交叉连接或内连接有两种写法 ， 一种是使用JOIN并用ON或者USING 子句指定连接条件的写法，一种是普通的SELECT多表 ， 并且用WHERE子句指定连接的键的写法 。
下面的例子是一个交叉连接：
上面的写法等价于：
当然，第二种写法中如果将CROSS JOIN替换成JOIN或者INNER JOIN也是可以正确执行的 。上面两条语句的执行结果如下：
可以看到共返回了 30 行结果，是两张表的笛卡尔积 。
一个内连接的例子如下：
上面的写法等价于：
在连接条件比较的字段相同的情况下，还可以改用USING关键字，上面的写法等价于：
上面三条语句的返回结果如下：
可以看到只返回了符合连接条件customers.cust_id = orders.cust_id的 6 行结果，结果的含义是所有有订单的用户和他们的订单 。
左外连接和右外连接的例子如下，其中的OUTER关键字可以省略：
其中右外连接的返回与内连接的返回是一致的（思考一下为什么），左外连接的返回结果如下：
可以看到一共返回了 8 行数据，其中最后两行数据对应的order_id的值为NULL，结果的含义是所有用户的订单，不管这些用户是否已经有订单存在了 。
根据前面介绍的自然连接的定义，自然连接会自动用参与连接的两张表中 字段名相同 的列做等值比较，由于例子中的customers和orders表只有一列名称相同，我们可以用自然连接的语法写一个与上面的内连接的例子表现行为一样的语句如下：
可以看到，使用自然连接就不能再用ON子句指定连接条件了，因为这完全是多余的 。
当然，自然连接同样支持左外连接和右外连接 。
下面用一个customers表自连接的例子再来说明一下自然连接，语句如下：
因为是自连接，因此必须使用AS指定别名 ， 否则 MySQL 无法区分“两个”customers表，运行的结果如下：
可以看到结果集和customers表完全一致，大家可以思考一下为什么结果是这样的 。
文章之前也提到了，MySQL 还支持一种 SQL 标准中没有定义的“方言”，STRAIGHT_JOIN ， STRAIGHT_JOIN支持带ON子句的内连接和不带ON子句的交叉连接，我们来看一个STRAIGHT_JOIN版本的内连接的例子：
返回结果与前面内连接的例子是一致的，如下：
STRAIGHT_JOIN的表现和JOIN是完全一致的 ， 它只是一种给 MySQL 优化器的提示，使得 MySQL 始终按照语句中表的顺序读取表（上面的例子中，MySQL 在执行时一定会先读取customers表 ， 再读取orders表），而不会做改变读取表的顺序的优化 。关于 MySQL 优化器的话题这里不做展开，需要说明的是除非你非常清楚你在做什么 ， 否则不推荐直接使用STRAIGHT_JOIN。
你能理解上面的语句是在检索什么数据吗？
本文主要介绍了 SQL 标准里定义的各种连接的概念，以及 MySQL 中的实现 ， 并通过各种例子来介绍了这些连接的区别 。这些连接不一定都能在实际开发中用到，但是做到心中有知识也还是很有必要的 。
那么，现在再回忆一下 ， 什么是内连接、外连接、自连接、等值连接和自然连接？他们的区别是什么？
最后，给大家留一个思考题，为什么 MySQL 中没有左外连接或者右外连接版本的STRAIGHT_JOIN ？
mysql等值怎么连接的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql等值查询、mysql等值怎么连接的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：如何让app与服务器互联？ app怎么连服务器

    下一篇：平板电脑怎么连u盘，平板电脑怎么连u盘视频