刘正的主页

帮趣游戏     进入个人中心
新浪微博

刘正

evan

fighting, 拼了!

http://bangqu.com/evan

职业: 软件开发

现居: 江苏省无锡市

  • 浏览 682次
  • 感谢 0人
  • 收益 ¥20.0元

数据库主要Join操作

数据库的Join操作还是比较麻烦的事情,今天花了点时间好好研究了一下,其实数据库主要有三类Join操作:inner join,left outer join以及right outer join。

下面以Mysql数据库为例解释这几种操作。

Mysql数据库的Creat SQL语句如下:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `cityinfo`
-- ----------------------------
DROP TABLE IF EXISTS `cityinfo`;
CREATE TABLE `cityinfo` (
  `id` int(11) NOT NULL,
  `CityCode` int(11) NOT NULL,
  `CityName` varchar(255) NOT NULL,
  `ProvCode` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of cityinfo
-- ----------------------------
INSERT INTO `cityinfo` VALUES ('1', '10001', '北京', '1');
INSERT INTO `cityinfo` VALUES ('2', '20001', '上海', '2');
INSERT INTO `cityinfo` VALUES ('3', '30001', '南京', '3');
INSERT INTO `cityinfo` VALUES ('4', '30002', '无锡', '3');
INSERT INTO `cityinfo` VALUES ('5', '30003', '苏州', '3');
INSERT INTO `cityinfo` VALUES ('6', '40001', '杭州', '4');
INSERT INTO `cityinfo` VALUES ('7', '40002', '绍兴', '4');
INSERT INTO `cityinfo` VALUES ('8', '50001', '巢湖', '5');

-- ----------------------------
-- Table structure for `provinfo`
-- ----------------------------
DROP TABLE IF EXISTS `provinfo`;
CREATE TABLE `provinfo` (
  `ProvId` int(11) NOT NULL,
  `ProvCode` int(11) NOT NULL,
  `ProvName` varchar(255) NOT NULL,
  PRIMARY KEY (`ProvId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of provinfo
-- ----------------------------
INSERT INTO `provinfo` VALUES ('1', '1', '北京');
INSERT INTO `provinfo` VALUES ('2', '2', '上海');
INSERT INTO `provinfo` VALUES ('3', '3', '江苏');
INSERT INTO `provinfo` VALUES ('4', '4', '浙江');
INSERT INTO `provinfo` VALUES ('5', '5', '安徽');
INSERT INTO `provinfo` VALUES ('6', '6', '广东');
INSERT INTO `provinfo` VALUES ('7', '7', '湖南');

 

数据库图片如下所示:

                             

 

cityInfo表中包含四个字段:id,CityCode,CityName,ProvCode

provInfo表中包含三个字段:ProvId,ProCode,ProvNmae

这两张表通过键ProvCode连接。

 

 (1) Inner Join

Mysql数据库Inner Join采用的SQL语句如下:

select * from cityinfo inner join provinfo on cityinfo.ProvCode=provinfo.ProvCode;
select * from provinfo inner join cityinfo on cityinfo.ProvCode=provinfo.ProvCode;

其中on后所加的条件是没有先后之分的,两条命令对应的结果如下所示:

                        

 

 

从图中可以看出:

Inner Join相当于左表用自己的每一条记录依次与右表的每一条记录比对on后的条件,只有满足条件的被记录下来,不满足on后条件的则被抛弃

不难看出其特点有以下几个:

I、显示的总记录数 <= (小于等于) 两表中最大的行数;<>

II、如果左表与右表是1:N的关系,那么第一种结果显示的主键是唯一的,第二种结果主键不唯一;

III、如果是N:N的关系,则显示结果的主键都不唯一。

注意:这里的关系笔者认为不包括遗漏的记录,城市到省份的映射是1:N的,而教师与学生的映射是N:N。

因为这里涉及到一个问题,有兴趣的朋友可以尝试在cityInfo表中插入一条 (9,60001,天津,8)的记录,最后做Inner Join的显示结果是不变的,第一种结果主键唯一。

但是这里是漏记了一个省份,而不代表他们的关系是N:N的。

应用场景:将存在多关系的引用表放在左表,将存在一关系的被引用表放在右表,通过对右表设定on过滤条件,选出主键唯一的左表记录

 

(2) Left outer Join

首先Left outer Join = Left Join,只是一个简写罢了。

Mysql数据库Left Outer Join采用的SQL语句如下:

select * from cityinfo left outer join provinfo on cityinfo.ProvCode=provinfo.ProvCode;
select * from provinfo left outer join cityinfo on cityinfo.ProvCode=provinfo.ProvCode;

              

     

从图中可以看出:

Left outer Join相当于左表用自己的每一条记录依次与右表的每一条记录比对on后的条件,满足的与inner方式一致即Join一下显示,而找不到满足条件的会填写上Null值依旧显示给用户

其特点有以下几个:

I、显示的总记录数可能大于两表中最大的行数; 

这里是最关键的地方,Outer Join就是显示了那些在Inner Join中显示不了的连接信息,其机制是自动填Null。

II、如果左表与右表是1:N的关系,那么第一种结果显示的主键是唯一的,第二种结果主键不唯一;

III、如果是N:N的关系,则显示结果的主键都不唯一。

 

(3) Right outer Join

 

select * from cityinfo right outer join provinfo on cityinfo.ProvCode=provinfo.ProvCode;
select * from provinfo right outer join cityinfo on cityinfo.ProvCode=provinfo.ProvCode;

结果如图所示:

                

 

 

目的是将右表的所有记录列出,左表中只要符合on条件的,与右表记录相拼合,不符合条件的,填以Null值

分享时间: